慢SQL-mysql in + order by id + limit 导致的慢SQL

背景

有通知表，建表SQL如下：

CREATE TABLE `perf2_notice_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `notice_type` varchar(32) DEFAULT NULL COMMENT '通知类型: SMS、EMAIL',
  `deal_status` varchar(32) DEFAULT 'NOT_START' COMMENT '处理状态: NOT_START、FAIL、SUCCESS、AUTO_CANCEL',
  `is_deleted` varchar(1) DEFAULT 'n' COMMENT '是否删除 y  n',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_notice_type_deal_status` (`notice_type`(20),`deal_status`(20)) COMMENT '查询通知消息索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='通知信息表';

其他字段省略，业务中有根据处理状态查询未成功的记录，SQL如下：

SELECT * FROM perf2_notice_info
WHERE is_deleted = 'n' AND notice_type = 'SMS' AND deal_status IN ('NOT_START','FAIL')
ORDER BY id asc 
LIMIT 2000;

耗时竟然长达13秒：

本来以为这个查询语句使用到了列notice_type和deal_status，所以给这两列创建了联合索引，但却如此之慢，为什么呢？

当前数据库的数据分布情况：

分析

执行explain发现，根本没有使用索引（idx_notice_type_deal_status），
type 是 index ，说明扫描了整个索引树，
key 是 PRIMARY，则说明使用了主键索引，

解决方案

1、force index

SELECT * FROM perf2_notice_info
force INDEX (idx_notice_type_deal_status)
WHERE is_deleted = 'n' AND notice_type = 'SMS' AND deal_status IN ('NOT_START','FAIL')
ORDER BY id DESC 
LIMIT 2000;

效果：

2、修改SQL使用正确的索引

2.1 改为 order by gmt_create

SELECT * FROM perf2_notice_info
WHERE is_deleted = 'n' AND notice_type = 'SMS' AND deal_status IN ('NOT_START','FAIL')
ORDER BY `gmt_create`  asc 
LIMIT 2000;

错误使用主键索引，首先怀疑的是因为order by id asc 导致的，其实之所以需要排序，是因为通知是有先后顺序的，可以改为通过gmt_create排序来保证通知的处理顺序。效果：

索引正确:

所以：order by id 并不是排序的万能钥匙！当时考虑到通知需要有序处理时，直接没考虑就使用了id，现在看来是欠考虑了

2.2 去掉limit

order by 和 limit 结合使用时，如果where和order by字段都有索引，那么有limit时会优先选择order by字段所在的索引，没有limit会使用where 条件的索引；

虽然可以正确使用索引，但检索行太多，排序代价高，性能也很差，不能解决慢SQL问题

2.3 去掉in，改为两次查询后再合并排序

SELECT * FROM perf2_notice_info
WHERE is_deleted = 'n' AND notice_type = 'SMS' AND deal_status = 'NOT_START'
ORDER BY id DESC 
LIMIT 2000;

SELECT * FROM perf2_notice_info
WHERE is_deleted = 'n' AND notice_type = 'SMS' AND deal_status = 'FAIL'
ORDER BY id DESC 
LIMIT 2000;

效果：


索引正确:

为了保证有序性，两次查询结果需要合并后再进行一次排序。

为什么优化器会错误使用索引呢？

1.notice_type和deal_status两列的区分度太低
使用in会使得优化器估算匹配行太多，如果使用联合索引，回表代价高

PS: 那么为什么会估算错误呢？这个待大神解答以下
in的列表元素是2个，而数据库eq_range_index_dove_limit是20，那么应该是精确计算到符合in条件的条数是0呀！

2.order by id 根据id排序，如果使用联合索引，还需要filesort，如果行数太多，那么排序的代价会很高
(虽然notice_type和deal_status有序，但是索引上关联的id是无序的)

因此两方面导致优化器认为直接扫描聚集索引的性能更好

为什么使用PRIMARY索引会很慢呢？

explain中发现扫描行数有41878行！所以会发生大量的回表和大量的随机磁盘读！实际上该表字段非常多，其中还有一个大字段，导致一个数据页的行数少，更加剧了磁盘读的次数！其实通过延迟读取就可以大大提高性能：

而索引还是错误的：

实际表结构如下：

CREATE TABLE `perf2_notice_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `creater` varchar(64) DEFAULT NULL COMMENT '废弃',
  `modifier` varchar(64) DEFAULT NULL COMMENT '修改人',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_deleted` varchar(1) DEFAULT 'n' COMMENT '是否删除 y  n',
  `business_id` varchar(128) DEFAULT NULL COMMENT '业务ID,比如docId',
  `scene_code` varchar(128) DEFAULT NULL COMMENT '场景编码',
  `notice_type` varchar(32) DEFAULT NULL COMMENT '通知类型',
  `operate_type` varchar(32) DEFAULT NULL COMMENT '操作类型',
  `deal_status` varchar(32) DEFAULT 'NOT_START' COMMENT '处理状态',
  `error_msg` text COMMENT '错误信息',
  `server_id` varchar(1024) DEFAULT NULL COMMENT '服务端能识别的id',
  `receiver` text COMMENT '接收人',
  `sender` varchar(64) DEFAULT NULL COMMENT '发起人',
  `title` varchar(1024) DEFAULT NULL COMMENT '标题',
  `title_en` varchar(1024) DEFAULT NULL COMMENT '英文标题',
  `url` varchar(1024) DEFAULT NULL COMMENT '跳转链接',
  `notify_scene_id` varchar(64) DEFAULT NULL COMMENT '消息中心场景ID',
  `content_map` text COMMENT '消息中心模板变量值',
  `trigger_time` datetime DEFAULT NULL COMMENT '消息中心发送时间',
  `batch_code` varchar(1024) DEFAULT NULL COMMENT '消息中心批次号',
  `process_code` varchar(256) DEFAULT NULL COMMENT '工作流流程编码',
  `init_data` text COMMENT '工作流初始化变量',
  `auth_key` varchar(256) DEFAULT NULL COMMENT '工作流授权码',
  `start_activity_id` varchar(256) DEFAULT NULL COMMENT '工作流开始节点',
  `process_id` varchar(64) DEFAULT NULL COMMENT '工作流流程ID',
  `retry_times` int(10) unsigned DEFAULT '0' COMMENT '重试次数',
  `creator` varchar(20) DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`),
  KEY `idx_notice_type_deal_status` (`notice_type`(20),`deal_status`(20)) COMMENT '查询通知消息索引',
  KEY `idx_scene_code_business_id_sender_receiver` (`scene_code`(20),`business_id`(20),`sender`(10),`receiver`(10)),
) ENGINE=InnoDB AUTO_INCREMENT=7304790 DEFAULT CHARSET=utf8mb4 COMMENT='通知信息表'
;