数据库调优：Mysql索引对group by 排序的影响

背景：

随着业务量的增大，数据库单表存储的数据越来越多。
目前单表总量是700W+，某货主下的SKU占比为240W+，在页面查询的时候出现慢查，返回前端超时，导致页面报错。
建立索引的字段通常都是作为查询条件的字段(一般作为WHERE子句的条件)，却容易忽略查询语句里包含order by的场景。其实涉及到排序order by的时候，建立适当的索引能够提高查询效率。本文详解利用索引优化order by的查询语句。

数据库版本：

SELECT VERSION();
5.7.28-log

业务sql语句

 SELECT
	* 
FROM
	basic_sku_detail 
WHERE
	( company_id = 13 ) 
ORDER BY
	update_time DESC 
	LIMIT 50;
1
2
3
4
5
6
7
8
9

数据库建表语句

CREATE TABLE `basic_sku_detail` (
  `sku_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `company_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '货主ID',
  `company_code` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '货主编码',
  `sku_code` varchar(768) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT 'SKU编号',
  PRIMARY KEY (`sku_id`) USING BTREE,
  UNIQUE KEY `uk_company_id_sku_no` (`company_id`,`sku_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4460869 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='商品表';
1
2
3
4
5
6
7
8

默认走的是uk_company_id_sku_no联合索引。速度非常慢,sql查询耗时：6S。
速度慢的原因：
数据库根据索引查询出数据后，又额外进行了Using filesort操作。

优化方案

方案一

针对这种场景添加联合索引index_cpid_uptime("company_id", "update_time")。

sql执行语句

CREATE INDEX inx_company_id_ update_time on basic_sku_detail(company_id, update_time);
1

或者

ALTER TABLE basic_sku_detail ADD INDEX inx_company_id_update_time (company_id, update_time); 
1

sql查询耗时：0.128S。

explain结果：

优化后变快的本质原因：

在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql就要进行文件排序。二级索引的叶子结点已经对uptime字段做了排序操作，可以直接返回，省去了Using filesort操作，所以快。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如 group by 、union 、distinct等。

方案二

只对uptime字段加索引index_uptime("update_time")，,sql查询耗时：0.217S。explain结果：

疑问？

根据联合索引扫描出来的行数为什么不准？有时候是50有时候是1282706？

通常情况下，rows用来表示在SQL执行过程中会被扫描的行数，该数值越大，意味着需要扫描的行数，相应的耗时更长。但是需要注意的是EXPLAIN中输出的rows只是一个估算值，如果数据分布有偏差，并且您声明查看具有不同分布结果的部分数据可能会偏离 10-100 倍甚至更多。可以参考文章《MySQL EXPLAIN limits and errors》。

那怎样获取真正的执行步骤呢？

step1:先正常sql语句。

step2:执行命令SHOW SESSION STATUS LIKE "Handler_read%"

step3:分析结果。

Handler字段说明：

Handler_read_first：索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引（这个值越低越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
1
2
3
4
5
6

三种sql执行的结果：

结论

没有orderBy 条件为索引的时候，耗时最久，因为需要额外的排序操作。

走index_cpid_uptime联合索引的时候，也会排序，但是速度很快了。

走index_uptime单独索引的时候，不排序，速度和index_cpid_uptime相当。

最终选择

index_cpid_uptime联合索引。

页面立竿见影的效果，嗷嗷快～