记同事 一次sql优化

最近同事做了一次sql 优化 ，感觉很好，纪录学习下

如下所示，该语句查询时间慢，分析过程过下：

1）首先检查实例读写，机器负载均无异常

2）查看channel_materials 表结构索引

CREATE TABLE `channel_materials` (  `mid` varchar(50) NOT NULL DEFAULT '0' COMMENT '',  `mblog_info` varchar(10000) NOT NULL DEFAULT '' COMMENT '',  `mid_create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '',  `status` int(3) NOT NULL DEFAULT '0' COMMENT '',  `reposts` int(11) NOT NULL DEFAULT '0' COMMENT '',  `current_check_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '',  `registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',  `recommend_registration_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '',  `recommend_mblog_label` varchar(200) NOT NULL DEFAULT '' COMMENT '',  `recommend_mblog_info` varchar(10000) NOT NULL DEFAULT '' COMMENT '',  PRIMARY KEY (`mid`),  KEY `index_status` (`status`),  KEY `index_current_check_time` (`current_check_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

查询条件status和current_check_time都建有索引

3) 使用SHOW INDEX查询表索引选择性(Cardinality值代表该索引列预估的存储的唯一值个数,Cardinality/rows_in_table若非常小,需考虑是否有必要建该索引)

    +-------------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+    | Table             | Non_unique | Key_name                 | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |   +-------------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+   | channel_materials |          0 | PRIMARY                  |            1 | mid                | A         |      569037 |     NULL | NULL   |      | BTREE      |         |               |    | channel_materials |          1 | index_status             |            1 | status             | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |    | channel_materials |          1 | index_current_check_time |            1 | current_check_time | A         |      569037 |     NULL | NULL   |      | BTREE      |         |               |   +-------------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  ```    如上可发现,status的基数很低,选择过滤性较差，查询实际数据localhost.recommend>select status,count(*) from channel_materials group by status;

+--------+----------+| status | count(*) |+--------+----------+|    255 |        2 | |    256 |        1 | |   7788 |   557732 | +--------+----------+

99.9%的数据都落在status=7788

4) 查看具体执行计划

explain select mblog_info from channel_materials where (status = 7788) and current_check_time >= 1483816341 and current_check_time <= 1483926441 limit 10;
+—-+————-+——————-+——+—————————————+————–+———+——-+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——————-+——+—————————————+————–+———+——-+——–+————-+
| 1 | SIMPLE | channel_materials | ref | index_status,index_current_check_time | index_status | 4 | const | 284832 | Using where |
+—-+————-+——————-+——+—————————————+————–+———+——-+——–+————-+
如上 该语句查询走status索引 扫描28w数据 全部55w行数据。实际执行要5min左右 使用hint强制走index_current_check_time
select SQL_NO_CACHE mblog_info from channel_materials force index (index_current_check_time) where (status = 7788) and current_check_time >= 1483716341 and current_check_time <= 1483926441 limit 10;
使用index_current_check_time索引能在1s内返回结果

终上所述：
1）确认下业务查询中是否有对status = 255等(即非7788的值查询) 如果没有或很少 建议删除status索引;如果有且较多 建议建status和current_check_time的联合索引
2）在查询SQL中加上上述hint强制走索引，不建议(后端索引变更 程序无感知报错)
3）确认下如下SQL是否分页,没有的话查询间隔较长 返回数据网络开销也较大
4）索引变更请在idb系统提交工单