MySQL表250M+行，查询速度慢

Question

我有一个包含如下列的表格:

| seqid          | bigint(20) | NO   | PRI | 0                 |
| Time           | timestamp  | NO   | PRI | CURRENT_TIMESTAMP |
| DevId          | text       | YES  | MUL | NULL              |

该表已按时间列的值启用分区，DevId的索引长度覆盖了每个可能值的全长。

现在该表有 250M+ 行(可能会增长到 400M+)，以及 ~18K 到 ~20K 不同的 DevId。

当我尝试运行如下查询时:

select * from TABLE where DevId='00:1F:23:31:44:48'

查询需要30到90秒，另外30到90秒秒获取约 20000 条记录。解释的输出显示类似这样的内容:

+----+-------------+------+-------------+---------+-------+------+-------------+
| id | select_type | type | key         | key_len | ref   | rows | Extra       |
+----+-------------+------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | ref  | DevID_IDX   | 387     | const |21042 | Using where |
+----+-------------+------+-------------+---------+-------+------+-------------+

有几件事让我想知道:

• 为什么 key_len 是 387？我知道 MySQL 使用更多字节进行 UTF-8 编码表，但 387 对于值来说太长了。 (全部都是17岁数字)

• 为什么MySQL需要30到90秒来获取索引记录？我知道MySQL页大小为16KB，存储二级索引记录在 BTree 的叶节点中，每个页面可能只有 1/2 到 15/16 满。这意味着它可能需要从磁盘中查找 30 或 40 页。90 秒看起来确实太长了。

• innodb 每表一个文件选项有帮助吗？任何其他方法可以改善搜索速度？我们的目标是使用设备 ID 进行单次查询几秒钟和时间限制。

预先感谢您的任何建议。

Answer 1

感谢您的所有回复。是的，DevID 是本例中的 MAC 地址，但也可以是其他任何地址。将其更改为 varchar() 确实有一点帮助，但没有太大帮助，因为在最新的 MySQL 中，短文本列实际上使用了与 varchar() 几乎相同的空间量。

经过深入调查，其实我自己找到了根本原因，其他地方很少提及。我想把我在这里学到的东西分享出来，也征求一下大家的意见。

众所周知，mysql只能使用单个索引来进行此查询。获取时间很长，因为 mysql 将数据存储在主索引叶节点上，在我的例子中是随机数和时间戳。需要包含时间戳，因为我需要对表进行分区。这意味着同一 dev id 的行数据无法顺序存储，从而导致巨大的性能限制。根据我的测试，使用整数来存储 dev id 确实对索引加载有一点帮助，但它对行数据获取没有帮助，不幸的是，这才是缓慢的真正原因。

但是，对于我的应用程序来说，很少会查询同一个 dev id 的长时间数据。大多数情况下，不到一天，1000 到 2000 行之间。如果mysql只需要访问这些行，那实际上是相当快的。问题是，如果我运行一个简单的查询，例如:

select * from TABLE where DevId='00:1F:23:31:44:48' and Time <> (T1, T2);

表中有400M+行，mysql会选择时间索引，这实际上是一个错误的猜测，因为它通常意味着100K+行的范围扫描，有时高达10M行。另一方面，即使选择了 dev id 索引，mysql 也会进行范围扫描并按时间列值进行过滤，这也没有多大帮助。我希望mysql能够足够聪明地利用覆盖索引技术——因为dev id索引(二级索引)包含dev id和主键(在我的例子中是随机数和时间)，mysql应该能够知道哪些行仅通过查看二级索引来获取。

但丑陋的现实是，事实并非如此。所以事实证明我需要使用一个小的连接，例如:

select * from TABLE as a1 join
    (select seqid from TABLE use index (DevIDIndex) 
     where DevID='....' and Time <> (T1,T2)) as a2
on a1.seqid=a2.seqid

对于一天的数据，加载时间从几分钟缩短到几秒钟。