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

• 一、覆盖索引与回表
• • 1、表结构
  • 2、覆盖索引
  • 3、回表
• 二、哪些场景可以利用索引覆盖来优化SQL
• • 1、 全表count查询优化
  • 2、 列查询回表优化
  • 3、分页查询
• 三、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  |
+--------------------------+-------+

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

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