DDL
CREATE TABLE `sys_user_feedback` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...
`feedback_time` datetime DEFAULT NULL COMMENT '表示反饋時間',
...
PRIMARY KEY (`id`),
KEY `feedback_time` (`feedback_time`),
...
) ENGINE=InnoDB AUTO_INCREMENT=8063893 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用戶舉報(bào)表' |
SQL
select *
from sys_user_feedback as suf
where
...
and suf.feedback_time >= '2019-03-13'
and suf.feedback_time < '2019-03-14'
order by suf.id desc
limit 0,50
- mysql服務(wù)器突然負(fù)載飆升锐膜，DBA同學(xué)找出上面的問題sql，大量卡在sending data
Explain
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | suf | index | feedback_time | PRIMARY | 4 | NULL | 6422 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
- explain發(fā)現(xiàn)沒有feedback_time索引涧衙，走了主鍵索引，掃描類型為index，僅優(yōu)于all全表掃描字柠，嘗試改進(jìn)
優(yōu)化SQL
select *
from sys_user_feedback as suf
where
...
and suf.feedback_time >= '2019-03-13'
and suf.feedback_time < '2019-03-14'
order by suf.feedback_time desc
limit 0,50
Explain
+----+-------------+-------+-------+---------------+---------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+-------+-------------+
| 1 | SIMPLE | suf | range | feedback_time | feedback_time | 9 | NULL | 60606 | Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+-------+-------------+
- 優(yōu)化后走了feedback_time索引，索引類型變?yōu)閞ange狡赐，在where查詢出的子集中掃描窑业，奇怪的是rows反而變多
驗(yàn)證
- 問題sql查詢完全卡住，怕影響服務(wù)枕屉，強(qiáng)行終止
- 優(yōu)化后的sql常柄，50 rows in set (0.01 sec)
分析
由兩次explain可以看出，id沒出現(xiàn)在where子句中，所以根據(jù)id列進(jìn)行排序時會使用id索引西潘，相當(dāng)于全表掃描（千萬級別）卷玉，取夠50條為止。 優(yōu)化后在where查詢出的集合中掃描（6萬）喷市，所以要避免排序索引字段不在查詢條件中相种。
奇怪的是為什么explain出來的rows和結(jié)果相反？品姓？寝并？
再補(bǔ)一個
取值少的字段加上索引反而拖慢查詢速度的例子：
DDL
CREATE TABLE `some_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL COMMENT '所屬公司ID',
`type` enum('t1','t2','t3','t4') NOT NULL,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
KEY `type` (`type`)
);
SQL
select * from some_table where company_id=1 and type="t1";
Explain
+------+-------------+---------------------------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
| 1 | SIMPLE | some_table | index_merge | company_id,type | company_id,type | 4,1 | NULL | 2689 | Using intersect(company_id,type); Using where |
+------+-------------+---------------------------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
可以看到，這個 sql 走了交叉索引腹备，會使用 company_id 和 type 兩個索引的搜索結(jié)果求交集衬潦。很明顯，type取值就4種植酥，能過濾掉的結(jié)果也就很少了镀岛，所以 type 索引的結(jié)果集會比較大，在這個字段加索引反而起到了副作用友驮。
優(yōu)化
select * from some_table where company_id=1 and type like "t1";
不走 type 索引哎媚，當(dāng)然允許改表結(jié)構(gòu)的情況下，刪掉索引是最好的喊儡。優(yōu)化后拨与，sql執(zhí)行時間從0.5s降至0
SQL性能優(yōu)化的目標(biāo)
至少要達(dá)到 range級別，要求是ref級別艾猜，如果可以是consts最好买喧。
說明：
1）consts單表中最多只有一個匹配行（主鍵或者唯一索引），在優(yōu)化階段即可讀取到數(shù)據(jù)匆赃。
2）ref指的是使用普通的索引（normal index）淤毛。
3）range對索引進(jìn)行范圍檢索。
