mysql order by 在索引项存在varchar且被where限定常量时出现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

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

filesort：
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的常量项后，发现

SELECT
	content_id,
	time_created 
FROM
	test 
WHERE
	province = 'shanghai' 
	AND type = 'aa' 
	AND time_created > 12345 
ORDER BY
	type,
	time_created 
	LIMIT 10

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
https://bugs.mysql.com/bug.php?id=88127

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

3 测试

4 结论

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

• 1.character_set_connection = utf8mb4
• 2.order by 索引有常数项，且 Collation =utf8mb4_unicode_ci
  经过优化查询时间由13090ms降为5ms
  经测试。该问题在最新版mysql中依然存在