乐趣区

排查一次sql-order-by未使用到索引utf8mb4unicode

jiezi

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 测试

province type
Encoding Collation Encoding Collation order by province,type,time_created order by time_created
1 utf8mb4 utf8mb4_unicode_ci utf8mb4 utf8mb4_unicode_ci Using where; Using index; Using filesort Using where; Using index;
2 utf8mb4 utf8mb4_unicode_ci utf8 utf8mb4_unicode_ci Using where; Using index; Using filesort Using where; Using index;
3 utf8 utf8mb4_unicode_ci utf8 utf8mb4_unicode_ci Using where; Using index; Using where; Using index;
4 utf8mb4 utf8mb4_general_ci utf8 utf8mb4_unicode_ci Using where; Using index; Using where; Using index;
5 utf8mb4 utf8mb4_general_ci utf8mb4 utf8mb4_general_ci Using where; Using index; Using where; Using index;
6 utf8mb4 utf8mb4_general_ci utf8mb4 utf8mb4_unicode_ci Using where; Using index; Using filesort Using where; Using index;

4 结论

从测试结果，可以看出在以下条件下，会导致 order by 不走索引
1.character_set_connection = utf8mb4
2.order by 索引有常数项，且 Collation =utf8mb4_unicode_ci
经过优化查询时间由 13090ms 降为 5ms
经测试。该问题在最新版 mysql 中依然存在

退出移动版