我刚刚制作了第一个 mysql 数据库和表,但对一个简单的 select 语句的缓慢性能感到惊讶。我的表有 4 亿行,我的 select 语句返回大约 10 万行,但花了 14 分钟!不确定是我的设置有误,还是我对mysql的期望太高了。对于一个设计良好的表来说,从 4 亿行表中返回 100,000 行的预期时间是多少?这是我的设置:
CREATE TABLE CALLS (
quote_date DATE,
quote_time TIME,
expiration DATE,
delta decimal(4,3),
mid decimal(8,4)
);
CREATE INDEX idx_quote_date ON CALLS (quote_date);
CREATE INDEX idx_quote_time ON CALLS (quote_time);
CREATE INDEX idx_expiration ON CALLS (expiration);
CREATE INDEX idx_delta on CALLS (delta);
CREATE INDEX covering_index ON CALLS (quote_date, quote_time, expiration, delta);
我的表实际上只是用于使用 select 语句读取数据,因此我在加载所有数据后执行索引。
我的选择是:
select * from CALLS where DELTA BETWEEN 0.4 and 0.6;
问:查询计划显示什么?
答:用
EXPLAIN ANALYZE
来看。我的猜测是它正在扫描大部分(如果不是整个表)来定位您的数据。这是因为以下问题:
SELECT *
是反模式。改进测试用例的方法:
SELECT *
,而是明确列出您想要选择的列。CREATE INDEX idx_delta_expiration_mid on CALLS (delta, expiration, mid);
如果您的查询是SELECT delta, expiration, mid FROM CALLS where DELTA BETWEEN 0.4 and 0.6;
。这将使索引可供使用,理想情况下可以有效地查找您感兴趣的数据,而不是扫描表。该查询可能会使用
INDEX(delta)
您拥有的 。它将执行如下(伪代码):“2a”中的来回成本相当高,特别是当表大于 时
innodb_buffer_pool_size
。不指定
PRIMARY KEY
;是很顽皮的。已为您提供了一份。(此细节不会影响性能。)您的“covering_index”未覆盖(对于此查询),因为
mid
丢失了。复合索引中列的顺序很重要。(但在这个例子中并非如此。)
如果不需要所有列,那么拥有以 delta 开头(因此将使用它)并包含所有需要的列(覆盖)的复合索引会更快。(正如 JD 所指出的。)
有关索引的更多信息: Index Cookbook
一般备注:
你的在哪里
PRIMARY KEY
?在我看来,这些数据似乎是不可变的?我的意思是给定的
call
是历史记录的问题而不是受到多次更新的影响?从这里,我们得到(警告 - 我不确定这就是我们正在处理的问题):
你没有库存标识符 - 这让我困惑 - 当然你应该有一个类似的字段
identifier CHAR(4)
- 或无论你的系统标识符的长度是什么 - 如果它是可变的,请使用VARCHAR(n)
- storage = n + 1 字节。您可以将
DATE
和TIME
一起存储为TIMESTAMP
,它可以保存“1970-01-01 00:00:01”(UTC) 和“2038-01-19 03:14:07”(UTC) 之间的值。如果您不需要超过 1 秒的精度,这将节省空间 - 4 个字节与 6 个字节相比。另外,关于你的PK,从这里开始,如果你的数据变化不大,你不需要代理
PRIMARY KEY
(通常INTEGER
),正如我上面推测的那样,本质上应该是日志表的情况。第一种可能性:
所以,我会这样设计(下面的所有代码都可以在此处的小提琴上找到):
delta
您可以按如下方式建立索引:请注意,如果您这样做,那么
INDEX SCAN
在运行查询时您会得到一个(使用EXPLAIN ANALYZE
):第二种可能性:
现在,如果过期时间是由 中的天数决定的
quote_ts
,您可以将其存储为SMALLINT
(或者即使UNSIGNED TINYINT
它永远不会 > 255 天)并使用该DATE_ADD()
函数并expiry_date
作为存储字段 (VIRTUAL
) - 没有空格,计算是在飞。请参阅小提琴 - 使用您自己的系统进行测试。然后在 上创建相同的索引
delta
,然后再次重新运行查询INDEX SCAN
。尝试一些不同的查询 - 检查表的大小(+/-INDEX
es.每个记录都会有一定的开销 - 记录存储在页面中(同样,开销),就像
INDEX
es - 更多开销。因此,要掌握真实的数据使用情况/记录,您必须加载 1M 条记录并在 fiddle 底部执行查询。有几点需要注意:
我不确定您为什么要从表中检索 400k 条记录 - 您是否正在聚合某些内容?
我不知道增量和中场是如何产生/计算/导出的。如果可以的话
GENERATED
,就有可能减少表的大小 - 从而减少扫描表所需的时间?最好
NOT NULL
在尽可能多的字段上设置 s - 向优化器提供的信息越多,它做好工作的机会就越大。也许这对于在学期过半之前mid
无法输入的字段来说是不可能的?call
(对交易不太了解)。您的两个索引 (
CREATE INDEX covering_index ON CALLS (quote_date, quote_time, expiration, delta);
和CREATE INDEX covering_index ON CALLS (quote_date)
可以替换为PRIMARY KEY
- a 的第一个字段PK
不需要额外的,INDEX
因为它无论如何都是前导字段。BETWEEN
最好使用数学运算符(<
、<=
、>=
、 )而不是>
运算符 - 这些是明确的 - 是BETWEEN
包容性的还是排他性的?无论如何,大多数服务器都会将此运算符转换为其他运算符 - 请参阅此处以了解混淆!使用索引
delta_ix
,它用于基于值的请求记录的查询delta
- 请参阅EXPLAIN ANALYZE
小提琴中的 。中字段的顺序
PK
应取决于您最频繁的查询。对此进行测试。如果您想详细说明字段是如何生成的,那么我们可能有更多机会减少记录大小并加快查询速度?PS 欢迎来到 dba.se!
假设列“delta”是随机分布的,您的查询将从 400M 行表中选择 100k 随机行。
delta 上有一个索引,但它仍然需要从表中读取 100k 随机分布的行。
14min/100k = 每行 8.4 毫秒,这非常接近 7200rpm 硬盘的随机访问时间...嗯...
因此我猜你正在 7200rpm 的硬盘上运行它。这需要一段时间,没有办法解决。驱动头必须移动才能到达数据。
唯一的解决方案是
要么使用具有高随机 IOPS 的快速 NVME SSD,要么在盒子中放置足够的 RAM 以将整个表保存在缓存中。
或者在(delta,其他列)上使用覆盖索引,这会将随机访问变成顺序访问,速度要快得多。但它会占用大量空间,并且需要一段时间才能建造。
另一个解决方案是使用专门处理此类内容的数据库:
“value”列上没有索引,因为它是 MQTT 数据,因此索引位于 (mqtt_topic, timestamp) 上,这也是表顺序和分区键。因此它读取整个表。Clickhouse 将这个 21 亿行表(38 GB)压缩了大约 11 倍,因此它只使用了 3.4GB,NVME SSD 在大约 1 秒内读取,而在便宜的台式电脑上查询在 2.7 秒内完成。这不包括将结果集传输到客户端的时间,在本例中,结果集为 160 万行,因此约为 50MB,在千兆位以太网上需要半秒。