为啥没走我想要的索引

2024年 3月 4日 100.6k 0

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

现状

表结构是这样的

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,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。这样速度就更慢了。

解决方案

其实这是一个比较常规的问题,要解决的话只要创建(space_id,create_time)的联合索引即可。

有时候大家在review别人的代码的时候,也需要关注一下这点,防止线上出现这种慢查询问题。

总结

数据库方面除了会使用之外,能够知晓一些底层原理,能够避免很多问题,而且也能解决线上很多问题,推荐

  • 《MySQL技术内幕:InnoDB存储引擎》:系统讲述InnoDB,相对易懂,能帮大家建立MySQL整体认知

  • 《MySQL实战45讲》:知识点讲解清晰、深入,很多知识常用于面试、工作中

最后

大家如果喜欢我的文章,可以关注我的公众号(程序员麻辣烫)

我的个人博客为:shidawuhen.github.io/

往期文章回顾:

  • 设计模式

  • 招聘

  • 思考

  • 存储

  • 算法系列

  • 读书笔记

  • 小工具

  • 架构

  • 网络

  • Go语言

  • 相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论