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服务器突然负载飙升，DBA同学找出上面的问题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发现没有feedback_time索引，走了主键索引，扫描类型为index，仅优于all全表扫描，尝试改进

优化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 |
+----+-------------+-------+-------+---------------+---------------+---------+------+-------+-------------+

优化后走了feedback_time索引，索引类型变为range，在where查询出的子集中扫描，奇怪的是rows反而变多

验证

问题sql查询完全卡住，怕影响服务，强行终止
优化后的sql，50 rows in set (0.01 sec)

分析

由两次explain可以看出，id没出现在where子句中，所以根据id列进行排序时会使用id索引，相当于全表扫描（千万级别），取够50条为止。优化后在where查询出的集合中扫描（6万），所以要避免排序索引字段不在查询条件中。
奇怪的是为什么explain出来的rows和结果相反？？？

再补一个

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

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 两个索引的搜索结果求交集。很明显，type取值就4种，能过滤掉的结果也就很少了，所以 type 索引的结果集会比较大，在这个字段加索引反而起到了副作用。

优化

select * from some_table where company_id=1 and type like "t1";

不走 type 索引，当然允许改表结构的情况下，删掉索引是最好的。优化后，sql执行时间从0.5s降至0

SQL性能优化的目标

至少要达到 range级别，要求是ref级别，如果可以是consts最好。

说明：

1）consts单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

2）ref指的是使用普通的索引（normal index）。

3）range对索引进行范围检索。

记一次sql优化

记一次sql优化

DDL

SQL

Explain

优化SQL

Explain

验证

分析

再补一个

DDL

SQL

Explain

优化

SQL性能优化的目标