MySQL的覆盖索引与回表和order by

2022-07-10 18:25:29

MySQL的覆盖索引与回表和order by

一、覆盖索引与回表
二、哪些场景可以利用索引覆盖来优化SQL
三、order by使用

一、覆盖索引与回表

1、表结构

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、覆盖索引

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
如何实现索引覆盖

常见的方法是：将被查询的字段，建立到联合索引里去。

3、回表

(1)先通过普通索引定位到主键值；
(2)再通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

二、哪些场景可以利用索引覆盖来优化SQL

1、全表count查询优化

添加索引后

2、列查询回表优化

select id,name,sex … where name=‘张三’;
将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

3、分页查询

select id,name,sex … order by name limit 500,100;
将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

三、order by使用

当排序的数据很大，超过 sort_buffer 的大小，那么就需要文件排序，文件排序涉及到分批排序与合并，很耗时，造成这个问题的根本原因是 sort_buffer 不够用

mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name          | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+

对于 order by 没有用到索引的时候，这时 explain 中 Extra 字段大概是会出现 using filesort 字眼
出现 using filesort 的时候也不用太慌张，如果本身数据量不大，比如也就几十条数据，那么在 sort buffer 中使用快排也是很快的
如果数据量很大，超过了 sort buffer 的大小，那么是要进行临时文件排序的，也就是归并排序，这部分是由 MySQL 优化器决定的
如果查询的字段很多，想要尽量避免使用临时文件排序，可以尝试设置下 max_length_for_sort_data 字段的大小，让其小于所有查询字段长度的总和，这样放入或许可以避免，但是会多一次回表操作
实际业务中，我们也可以给经常要查询的字段组合建立个联合索引，这样既不用回表也不需要单独排序，但是联合索引会占用更多的存储和开销
大量数据查询的时候，尽量分批次，提前 explain 来观察 sql 的执行计划是个不错的选择。

联合索引虽然可以解决这种问题，但是在实际应用中切不可盲目建立，要根据实际的业务逻辑来判断是否需要建立，如果不是经常有类似的查询，可以不用建立，因为联合索引会占用更多的存储空间和维护开销。

码农公寓