mysql用limit取数慢_mysql – LIMIT 1非常慢,对于特定的记录,使用不同的键

我正在诊断间歇性慢查询,并在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只有I和事件G的6个记录.

>其他单位还有更多.

>单位1234只有32,000日志是典型的.

>数据本身是正常的,没有更大或更大.

>数据库中有大约3000个“单位”,表示设备记录的东西. cl_unit_id是它们唯一的PK(尽管没有约束).

基本信息

>总共有30万条记录,约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)数据本身如何影响关键策略呢？数据的哪个方面,看起来像索引的数量和传播似乎是典型的.