✨快速理解✨MySQL索引下推

大家好，我是半夏之沫 😁😁 一名金融科技领域的JAVA系统研发😊😊
我希望将自己工作和学习中的经验以最朴实，最严谨的方式分享给大家，共同进步👉💓👈
👉👉👉👉👉👉👉👉💓写作不易，期待大家的关注和点赞💓👈👈👈👈👈👈👈👈
👉👉👉👉👉👉👉👉💓关注微信公众号【技术探界】 💓👈👈👈👈👈👈👈👈

---

前言

什么你竟然只知道覆盖索引不知道索引下推。

本文以一个短小精美的例子，五分钟帮助你完成对索引下推的理解。

MySQL版本：8.4.0

正文

创建老朋友student表。

CREATE TABLE student (
  id INT PRIMARY KEY auto_increment,
  stu_name VARCHAR ( 255 ) NOT NULL,
  stu_age INT NOT NULL,
  stu_phone VARCHAR ( 20 ) DEFAULT NULL ,
  stu_sex VARCHAR( 20 ) NOT NULL,
  stu_des VARCHAR( 4096 ) NOT NULL
);

插入下面几条数据。

INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bill", 20, "18888888888", "male", "Able to endure hardship" );
INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bob", 25, "17777777777", "male", "There will be endless bitterness to eat" );
INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Mary", 21, "15555555555", "female", "Able to endure hardship" );
INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bill", 22, "13333333333", "male", "There will be endless bitterness to eat" );

（注意到了每位同学都在说能吃苦就有吃不完的苦）

此时我们的主键索引（也就是数据表）以表格形式展示如下。

现在为stu_name, stu_age和stu_phone字段添加组合索引。

ALTER TABLE student ADD INDEX name_age_phone_index ( stu_name, stu_age, stu_phone );

此时辅助索引name_age_phone_index以表格形式展示如下。

现在我们执行下面一条查询语句。

SELECT
  stu_name,
  stu_age,
  stu_phone
FROM
  student
WHERE
  stu_name = "Bill"
AND
  stu_age = 20
AND
  stu_phone = "18888888888";

最标准的覆盖索引场景，现在解释一下该条SQL，结果如下。

+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | name_age_phone_index | name_age_phone_index | 834     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+

Extra是Using index，表示通过索引就能够获取到查询的数据，不需要回表的，这就是典型的覆盖索引的应用。

现在修改SQL语句，如下所示。

SELECT
  stu_name,
  stu_age,
  stu_phone
FROM
  student
WHERE
  stu_name = "Bill"
AND
  stu_age = 20
AND
  stu_sex = "male";

解释一下该条SQL，结果如下。

+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | name_age_phone_index | name_age_phone_index | 771     | const,const |    1 |    25.00 | Using where |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------------+

解释信息中的Extra是Using where，表明通过索引进行了回表，这是因为虽然查询条件stu_name和stu_age符合name_age_phone_index索引的最左匹配原则，但第三个查询条件不在name_age_phone_index索引中，所以需要回表后再通过第三个查询条件进行过滤。

整个过程MySQL的行为是下面这样的。

1. 基于（"Bill", 20）在name_age_phone_index索引中获取到需要回表的数据；
2. 回表查询到对应数据再通过WHERE的第三个条件进行过滤。

图示如下。

最后来修改一下SQL，使其发生索引下推行为，修改如下。

SELECT
  stu_name,
  stu_age,
  stu_phone
FROM
  student
WHERE
  stu_name = "Bill"
AND
  stu_phone = "18888888888"
AND
  stu_sex = "male";

解释一下该条SQL，结果如下。

+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | student | NULL       | ref  | name_age_phone_index | name_age_phone_index | 767     | const |    2 |    25.00 | Using index condition; Using where |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+------------------------------------+

解释信息中的Extra是Using index condition; Using where，表明进行了索引下推和回表。这里就结合上面这条SQL来解释一下，什么是索引下推。

上面这条SQL，查询条件里只有stu_name符合name_age_phone_index索引的最左匹配原则，所以首先会基于（"Bill"）去name_age_phone_index索引中获取到需要回表的数据，此时会获取到两条，如果没有索引下推，那么这两条数据都会回表，然后再基于stu_phone和stu_sex进行条件过滤，图示如下。

当开启索引下推时，尽管第二个查询条件stu_phone不满足name_age_phone_index索引的最左匹配原则，但MySQL为了减少回表次数，会在回表前提前将要回表的数据通过stu_phone="18888888888" 过滤一下，不满足stu_phone="18888888888" 的数据就不回表了，从而减少回表次数，提高查询效率，图示如下。

总结

索引下推就是在通过辅助索引找到要回表做进一步条件过滤的数据时，提前根据存在于辅助索引中但是没有作为索引被使用的查询条件来过滤出那些不需要回表的数据，以尽量降低回表次数，提高查询效率。

---

大家好，我是半夏之沫 😁😁 一名金融科技领域的JAVA系统研发😊😊
我希望将自己工作和学习中的经验以最朴实，最严谨的方式分享给大家，共同进步👉💓👈
👉👉👉👉👉👉👉👉💓写作不易，期待大家的关注和点赞💓👈👈👈👈👈👈👈👈
👉👉👉👉👉👉👉👉💓关注微信公众号【技术探界】 💓👈👈👈👈👈👈👈👈