如何让mysql索引更快一点

我不是女神ヾ 2023-10-06 03:59 18阅读 0赞

> 后端开发，公众号内容包括但不限于 python、mysql、数据结构和算法、网络协议、Linux。技术人怎能只有技术和代码，如果你对投资理财、保险，英语学习、读书写作有兴趣，都欢迎来公众号【谭某人】与我交流，你总会有些收获。

在 InnoDB 中，从二级索引回到主键索引查询数据，这个过程称作**回表**过程，而且这个回表过程是可以被优化的，这个优化就是利用**覆盖索引**。

先说结论，**如果一个索引的字段包含了所有要查询的字段，这个索引就称作覆盖索引，覆盖索引可以减少回表过程，能有效提高查询效率**。

前面我们有说过，在 InnoDB 中数据都是保存在 B+ 树上，主键索引保存了整行记录，二级索引保存了主键的值。

一次查询操作，要么是遍历主键索引，要么是遍历二级索引，要么就是先遍历二级索引得到主键 id 的值，然后再到主键索引上通过主键 id 查找满足要求的记录。

如果只遍历一次 B+ 树就能获取到我们要的数据，即没有回表过程，这个效率显然是不错的，这就是覆盖索引的优势。下面看个具体的例子。

mysql> create table user(
    id int(11) primary key, 
    name varchar(20) not null, 
    age int(11),
    sex int(11),
    index (age)) engine=InnoDB;

依然是新建一个表，创建索引，插入一些测试数据，注意这里只是为了解释说明覆盖索引，并不表示 mysql 的真实执行方式，因为会涉及到 mysql 的优化器机制，这里暂且不说了，以后再写。

1    bob 16  1
    2    kom 19  0
    3    gum 18  1
    4    tt  20  1
    5    yy  25  1

创建一个 user 表，给 age 字段添加一个二级索引，并插入上面五条数据，然后看下面这条查询语句。

select name from user where age between 18 and 21

我们来分析下这条 sql 的执行过程：

**1、**age 字段上有索引，mysql 会先到 age 字段的 B+ 树上找到满足条件的第一个叶子节点（age=19），这个叶子节点上保存了对应主键 id 的值 2，然后再到主键索引上找到 id 为 2 的这条记录，同时把 name 字段拿出来。

**2、**重复第一步的操作，继续从 age 索引上的叶子节点往后遍历找出满足条件的第二个叶子节点，同样回到主键上拿出 name 字段的值，直到遍历到不满足条件的叶子节点（age=25）。

也就是说，这条 sql 语句虽然用到了索引，但是 age 索引上并没有要查询的 name 字段，所以只能回表到主键索引上查出 name 字段，所以这个过程其实是遍历了个两个 B+ 树。

那么我们删除 age 这个单列索引，创建一个覆盖索引 (age,name)， 把要查询的 name 字段也添加到索引中来。

#删除原索引
    drop INDEX age on USER
    #新建覆盖索引
    ALTER TABLE USER add index age_name(age,name)

由于现在这个覆盖索引上的字段包含了要查询的 age 和 name 字段，免去了到主键索引上查询数据的过程，其实也就是只遍历了一个 B+ 树，可以大大提升查询效率。

添加索引虽然能提升查询效率，但索引也是需要占用额外空间的，而且索引还需要维护成本，所以通常加不加索引需要根据实际需求来权衡。

总之，在设计索引或者优化 sql 语句的时候，要尽量避免回表操作，所以使用覆盖索引是一种常用的 sql 优化手段。

所以我们平时写 sql 语句的时候，select 后面只写查询需要用到的字段，去掉不需要的字段，避免回表操作。

【End】

听说你对现在的工作不是特别满意？别担心，老王给你准备一份「**Java最常见200+面试题全解析**」，助力大家找到更好的工作，这份面试题包含的模块：

*  Java、JVM 最常见面试题解析
 *  Spring、Spring MVC、MyBatis、Hibernate 面试题解析
 *  MySQL、Redis 面试题解析
 *  RabbitMQ、Kafka、Zookeeper 面试解析
 *  微服务 Spring Boot、Spring Cloud 面试解析

扫描下面二维码付费阅读

![format_png][]

关注下方二维码，订阅更多精彩内容。

![format_png 1][]

转发朋友圈，是对我最大的支持。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9zWnlBSnRpYWVzV2NWd1pubUczb3V1V3N6aWFRaDVmeFNabUJkaWFpY3lmQWNRbVJ0WUtpY3BoZlZma1FDVDVsMFlZZ245WHdvcUdSaHpqNXN1QzVad2dsUGV3LzY0MA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9zWnlBSnRpYWVzV2RLVkVBN3pzaGlhMGliVEtuaWIwbTUyNTZJMU81OWxudVlIcTl4elRiSTE0UTl3RkF5UnBUb0JwMW1YZlY3cW1HV0JpYm1JbW1weGd2d0lBLzY0MA?x-oss-process=image/format,png