为啥没走我想要的索引-优快云博客

本文链接：https://blog.youkuaiyun.com/shida219/article/details/136440745

最近看报警，发现有慢日志。

现状

表结构是这样的

CREATE TABLE `A` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
  `description` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `space_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '空间ID',
  PRIMARY KEY (`id`),
  KEY `idx_space_id` (`space_id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`),
) ENGINE=InnoDB COMMENT='A表'

慢日志的语句为：

select * from `A` where space_id = ? order by create_time desc limit ?

explain查看了一下

列名	列值
type	`index`
possible_keys	`idx_space_id`
key	`idx_update_time`
key_len	`5`
ref	``
rows	`***`
filtered	`0.06`
Extra	`Using where; Backward index scan`

可以看到最终走的是updatetime的索引，不是spaceid，所以filtered特别低。

原因

选择索引是优化器的工作，优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。但扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

为啥没有走spaceid索引？和sql里的order有关！

如果真的是用了spaceid，那我们来看一下会是什么流程。

explain会显示Using filesort，使用“文件排序”！

因为数据库会初始化sort_buff，查询符合spaceid的信息，将字段放入sort_buffer，然后按照create_time快速排序，按照排序结果取对应行给客户端。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。这样速度就更慢了。