mysql - LIMIT 1 很慢，针对特定的记录，使用不同的key

Question

我正在诊断一个间歇性的慢速查询，并且在 MySQL 中发现了一个我无法解释的奇怪行为。只有在执行 LIMIT 1 时，它才会针对特定情况选择不同的非最佳 key 策略。

表格(为简洁起见删除了一些未引用的数据列)

CREATE TABLE `ch_log` (
    `cl_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `cl_unit_id` INT(11) NOT NULL DEFAULT '0',
    `cl_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `cl_type` CHAR(1) NOT NULL DEFAULT '',
    `cl_data` TEXT NOT NULL,
    `cl_event` VARCHAR(255) NULL DEFAULT NULL,
    `cl_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `cl_record_status` CHAR(1) NOT NULL DEFAULT 'a',
    PRIMARY KEY (`cl_id`),
    INDEX `cl_type` (`cl_type`),
    INDEX `cl_date` (`cl_date`),
    INDEX `cl_event` (`cl_event`),
    INDEX `cl_unit_id` (`cl_unit_id`),
    INDEX `log_type_unit_id` (`cl_unit_id`, `cl_type`),
    INDEX `unique_user` (`cl_user_number`, `cl_unit_id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=419582094;

这是查询，仅针对一个特定的 cl_unit_id 运行缓慢:

EXPLAIN
SELECT *
FROM `ch_log`
WHERE `ch_log_type` ='I' and ch_log_event = 'G'  
AND cl_unit_id=1234
ORDER BY cl_date DESC 
LIMIT 1;

id|select_type|table |type |possible_keys                               |key    |key_len|ref|rows|Extra
1 |SIMPLE     |ch_log|index|cl_type,cl_event,cl_unit_id,log_type_unit_id|cl_date|8      |\N |5295|Using where

对于 cl_unit_id 的所有其他值，它使用更快的 log_type_unit_id 键。

id|select_type|table |type|possible_keys                                           |key             |key_len|ref        |rows|Extra
1 |SIMPLE     |ch_log|ref |ch_log_type,ch_log_event,ch_log_unit_id,log_type_unit_id|log_type_unit_id|5      |const,const|3804|Using where; Using filesort

• 所有查询大约需要 0.01 秒。
• “慢单位”查询需要 10-15 分钟!

我看不出这个“单位”的数据有什么奇怪的:

• 1234 单元只有 6 条类型 I 和事件 G 的记录。
• 其他单位有更多。
• 1234 单元总共只有 32,000 条日志，这很正常。
• 数据本身是正常的，没有变大变老。
• 数据库中大约有 3,000 个“单元”，代表设备日志记录。 cl_unit_id 是他们唯一的 PK(尽管没有限制)。

一般信息

• 总共有3000万条记录，约12GB
• mysql 5.1.69-log
• Centos 64 位
• 数据在逐渐变化(3000 万 = 3 个月的日志)但我不知道以前是否发生过这种情况

我已经尝试过并可以“解决”问题的方法:

1. 删除 LIMIT 1 - 查询以毫秒为单位运行并返回数据。

2. 更改为 LIMIT 2 或其他组合，例如2,3 - 以毫秒为单位运行。

3. 添加索引提示 - 解决问题:

   FROM `ch_log` USE INDEX (log_type_unit_id)
   

   但是...我不想将其硬编码到应用程序中。

4. 在主键上添加第二个 order by 也“解决”了它:

   ORDER BY cl_id, cl_date DESC 
   

   给予解释:

   id|select_type|table |type|possible_keys                                           |key             |key_len|ref        |rows|Extra
   1 |SIMPLE     |ch_log|ref |ch_log_type,ch_log_event,ch_log_unit_id,log_type_unit_id|log_type_unit_id|5      |const,const|6870|Using where
   

   这与提示的类型略有不同，检查了更多记录 (6,000)，但仍然在 10 毫秒内运行。

同样，我可以做到这一点，但我不喜欢使用我不理解的副作用。

所以我认为我的主要问题是:

a) 为什么它只发生在 LIMIT 1 上？

b) 数据本身 怎么会对关键策略产生如此大的影响？以及数据的哪个方面，从指数的数量和分布来看似乎很典型。

Answer 1

Mysql 会选择一个解释计划并使用不同的索引，这取决于它认为在统计上是最佳选择。对于您所有的第一个问题，这就是答案:

1. 删除 LIMIT 1 - 查询以毫秒为单位运行并返回数据。和 -> 是的，检查一下，解释计划很好
2. 更改为 LIMIT 2 或其他组合，例如2,3 - 以毫秒为单位运行。 -> 同样适用。优化器选择了不同的索引，因为突然之间，预期的 block 读取量变成了 LIMIT 1 的两倍(这只是一种可能性)
3. 添加一个索引提示解决它 -> 当然，你强制一个好的解释计划
4. 在主键上添加第二个 order by 也“解决”了它 -> 是的，因为巧合，结果是更好的解释计划

现在，这只回答了一半的问题。

a) why does it only happen for LIMIT 1?

它实际上不仅因为 LIMIT 1 而发生，还因为

• 您的数据统计重新分区(指导优化器的决策)
• 您的 ORDER BY DESC 子句。尝试使用 ORDER BY ... ASC，您可能也会看到改进。

这种现象是众所周知的。请read on .

公认的解决方案之一(文章底部)是强制索引，方法同您一样。是的，有时候，这是有道理的。否则的话，这个提示的东西早就被彻底抹杀了。机器人不可能总是完美的:-)

b) how can the data itself affect the key-strategy so much? And what aspect of the data, seeing as the quantity and spread in the indexes seems typical.

你说的对，差价通常会搞砸。不仅优化器可能会根据准确的统计信息做出错误的决定，而且它也可能因为表上的增量而完全关闭 is right below 1 / 16th of the total row count ...