我正在诊断一个间歇性的慢查询,并发现MySQL中有一种奇怪的行为,我无法解释。只有在执行LIMIT 1
时,它才会选择不同的、非最优的键策略,而且只针对一个特定的情况。
表格(为了简洁起见,删除了一些未引用的数据列)
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(虽然没有约束)。
一般信息
- 总计有30m条记录,大约12GB
- mysql 5.1.69-log
- Centos 64bit
- 数据逐渐变化(30m = 3个月的日志),但我不知道这以前是否发生过
我尝试过的解决方法:
去掉
LIMIT 1
- 查询在毫秒级别运行并返回数据。改成
LIMIT 2
或其他组合,如2,3 - 在毫秒内运行。添加索引提示-解决了问题:
FROM `ch_log` USE INDEX (log_type_unit_id)
但是...我不想把这个硬编码到应用程序中。
在主键上添加第二个排序也可以“解决”它:
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)数据本身如何如此影响键策略?而数据的哪个方面,考虑到索引的数量和分布似乎很典型。
cl_unit_id
(cl_unit_id
),INDEXlog_type_unit_id
(cl_unit_id
,cl_type
).... 为什么不只保留第二个? - DrewINDEX(cl_unit_id, ch_log_type, ch_log_event, cl_date)
。(cl_date
必须放在最后;其他可以任意排序。) - Rick James