mysql order by 在索引项存在varchar时出现filesort分析

你在学习数据库相关的知识吗？本文《mysql order by 在索引项存在varchar时出现filesort分析》，主要介绍的内容就涉及到MySQL、utf8mb4、Mysql索引、order-by、排错方法，如果你想提升自己的开发能力，就不要错过这篇文章，大家要知道编程理论基础和实战操作都是不可或缺的哦！

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中依然存在

终于介绍完啦！小伙伴们，这篇关于《mysql order by 在索引项存在varchar时出现filesort分析》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布数据库相关知识，快来关注吧！