性能提高20倍！MySQL排序引起的性能问题及解决方案

起因

负责公司的用户收藏服务，收到调用方反馈有read time out的情况，进行排查发现是某用户收藏数量太多引起的（有业务设计上的问题，正常只保留有限时间的收藏或者限制用户收藏的数量），一般用户收藏数是不超过100的，查询耗时是几毫秒，这个用户收藏数2W+，查询耗时接近200毫秒。

排查过程

表结构如下，删减了部分字段，原有20多个字段

CREATETABLE `user_favorite` (  `id` bigint(20)NOTNULL AUTO_INCREMENT BYGROUP COMMENT '自增ID',  `create_user_id` varchar(64)NOTNULL DEFAULT '' COMMENT '用户ID',  `channel_id` bigint(20)NOTNULL DEFAULT '0' COMMENT '渠道ID',  `goods_id` bigint(20)NOTNULL DEFAULT '0' COMMENT '收藏的产品ID',  `create_time` timestampNOTNULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',  `is_delete` tinyint(1)NOTNULL DEFAULT '0' COMMENT '是否删除',  PRIMARY KEY (`id`),  KEY `idx_create_user_id_goods_id` (`create_user_id`,`channel_id`,`goods_id`) USING BTREE
) ENGINE=InnoDB;

查询SQL

select*from user_favorite
where create_user_id ='1234567'and channel_id =1and is_delete =0orderby create_time desclimit0,20;

执行计划（EXPLAIN）

问题分析

上面的explain的key可以看出，命中了表里唯一的索引

重点是Extra：

• Using index condition：使用了索引下推，5.6的新功能，如果索引包含多个条件，索引过滤一遍再回表查询
• Using where：有字段不在索引上，回表过滤
• Using filesort：需要排序，不一定是文件排序也有可能是内存排序

先不管是文件排序还是内存排序（可通过optimizer_trace分析），但可以大致确定的是，是因为需要排序，影响了整体性能。将order by命令去掉，验证得出与数据量少的用户查询耗时一致。

MySQL的排序方式

可以看到sql后面的limit是用于分页的，不是用户的全量数据返回，只取其中的20条，但问题是不排序无法确定取的是哪20条，所以必须是将查询到的所有结果集进行排序后再取其中的20条，这也是为什么MySQL及其他数据库不能深度分页的原因。再者，查询出2W+数据，且字段众多，会使用多个临时文件进行归并排序。

解决方案

因为一定是需要按创建时间排序的，但排序又影响了性能，这个问题看似也没办法解决了，那有没有办法是，查询到的结果集已经不需要排序，可以直接返回呢？

答案是肯定的，按照MySQL常用的B+树索引，索引里面结果已经是排好序的，按照我们的查询条件是create_user_id+channel_id，再加上排序字段create_time，创建联合索引

CREATE INDEX user_favorite_cui_ci_ct_IDX USING BTREE

ON user_favorite (create_user_id,channel_id,create_time);

条件create_user_id+channel_id查询后的结果已经是按照create_time排序好的结果集，至此，问题完美解决，下面看一下添加索引后的执行计划，验证一下我们的猜想。

优化后的执行计划

可以命中了我们新创建的索引，并且已经不需要排序了，耗时也从200毫秒降至10毫秒左右，性能提高20倍