MySQL倒序如何避免filesort_mysql order by 在索引项存在varchar时出现filesort分析

1.背景

由于一个业务表越来越大，大概有550万。突然某天出现了慢查询13s+，排查下来，发现是因为order by 没有走索引，为什么，以及如何处理，请看接下来的分析

1.1 表结构

CREATE TABLE `test` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'row id',

`content_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'contentID',

`type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

`province` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '地区',

`time_created` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',

PRIMARY KEY (`id`),

KEY `idx_province_type_time_created_content_id` (`province`,`type`,`time_created`,`content_id`)

) ENGINE =InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='test表'

1.2 sql语句

select content_id,time_created from test where province = 'shanghai' and type = 'aa' and time_created > 12345 order by province,type,time_created limit 10

1.3 mysql版本

5.6

2 分析

拿到有问题的语句，首先explain一把

id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |

--|-----------|-----|----------|-----|-----------------------------------------|-----------------------------------------|-------|---|----|--------|----------------------------------------|

1|SIMPLE |test | |index|idx_province_type_time_created_content_id|idx_province_type_time_created_content_id|520 | | 1| 100|Using where; Using index; Using filesort|

可以看到由于排序没有走索引，进行了file sort

If an index cannot be used to satisfy an ORDER BY clause, MySQL performs a filesort operation that reads table rows and sorts them. A filesort constitutes an extra sorting phase in query execution.

尝试修改语句，删除掉order by的常量项后，发现

id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |

--|-----------|-----|----------|-----|-----------------------------------------|-----------------------------------------|-------|---|----|--------|------------------------|

1|SIMPLE |test | |index|idx_province_type_time_created_content_id|idx_province_type_time_created_content_id|520 | | 1| 100|Using where; Using index|

竟然走索引了,这是为啥呢，求助强大的google

搜到一个17年有人提出的bug

于是怀疑，是否就是编码的问题，请看下面的测试结果

3 测试

4 结论

从测试结果，可以看出在以下条件下，会导致order by不走索引

1.character_set_connection = utf8mb4

2.order by 索引有常数项，且 Collation =utf8mb4_unicode_ci

经过优化查询时间由13090ms降为5ms

经测试。该问题在最新版mysql中依然存在