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='用戶舉報表' |

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反而變多

驗證

• 問題sql查詢完全卡住，怕影響服務(wù)，強行終止
• 優(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é)果相反？？？

再補一個

取值少的字段加上索引反而拖慢查詢速度的例子：

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)行范圍檢索。