mysql利用索引排序使用条件

本以为排序使用索引是很简单的事情，直接加上就行了。结果实际测试下来问题还蛮多的

背景

翻阅项目日志的时候，发现某条sql出现了 Out of sort memory, consider increasing server sort buffer size。

进一步排查发现，是该sql使用了文件排序(user filesort)导致的。

这就很奇怪，本来给排序字段加了索引的，但是没有使用，先临时给一个 force

环境

本次实验使用的环境如下

** mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1 **

使用的表结构如下，已去除部分无关字段

CREATE TABLE `advisory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `column_id` bigint(20) NOT NULL,
  `title` varchar(100) CHARACTER SET utf8mb4 NOT NULL ,
  `content` text CHARACTER SET utf8mb4,
  `state` tinyint(4) NOT NULL ,
  `serial_number` varchar(15) CHARACTER SET utf8mb4 NOT NULL ,
  `create_by` varchar(64) CHARACTER SET utf8mb4 DEFAULT '' ,
  `create_time` datetime DEFAULT NULL COMMENT ,
  `update_time` datetime DEFAULT NULL COMMENT ,
  `remark` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL,
  `comment_num` int(11) NOT NULL DEFAULT '0' ,
  `ic_id` bigint(20) DEFAULT NULL COMMENT ,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_column` (`column_id`) USING BTREE,
  KEY `idx_ic_id` (`ic_id`) USING BTREE,
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1688151 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC 

测试sql如下

SELECT a.id,
       a.author,
       a.author_type,
       a.ic_id,
       a.create_by,
       a.create_time,
       a.update_time,
       a.column_id,
       a.content,
       a.state
FROM advisory a
order by create_time

调试

直接 explain 上面sql，extra直接写上filesort，同时执行的是全表扫描

但是如果给加一个limit，例如 limit 10

filesort就消失不见，同时明确使用了idx_create_time索引。

---

再改造一下sql，去除其他select字段，只保留id字段

SELECT a.id
FROM advisory a
order by create_time

explain 结果如下

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	a		index		idx_create_time			25	10	Using index

这回使用了索引。

原理

这样就能够猜测出一个大致的原因了。

在查询了多余字段，且没有limit的情况下，mysql需要进行回表操作去获取其他字段的数据，这就导致mysql认为全表扫描比使用索引更加直接，全表扫描的副作用就是使用filesort

在加上limit之后，由于回表数量大幅度减少，这时候使用索引就有价值了，也就没有filesort

仅查询 id，也就是主键的情况叫做覆盖索引，意思是能够从索引中直接获取到需要的数据，不需要回表操作。

本例中只能使用id字段，如果加上了别的字段，哪怕是有索引的字段，也会导致filesort

这是因为在每一个索引中，都包括了主键字段和对应的索引字段。idx_create_time包括了id和create_time，而不包括别的字段