最近同事做了一次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系统提交工单