MySQL索引下推详细-木庄网络博客

本文整理自网络，侵删。

在没有索引下推的情况下，MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器，服务器再进行WHERE条件的判断，确认是否将数据行加入结果集。
开启索引下推，且WHERE条件部分可以仅使用索引中的列来评估，这时MySQL服务器会将这部分WHERE条件下推到存储引擎，接着存储引擎使用索引条目评估推送的索引条件，仅当满足该条件时才从表中进行读取

索引下推可以减少存储引擎访问数据表的次数以及MySQL服务器访问存储引擎的次数。

是不是还有点懵呢，那就对了，毫无疑问上面这段话理解起来相当费劲，但请不要灰心，我将用最通俗易懂的语言来带你了解索引下推。

总结一下：

最左前缀原则
回表

1、最左前缀原则

MySQL在建立联合索引时会遵循最左前缀原则，比如现在User表建立了联合索引（id，name，age）根据最左前缀原则只有在SQL的条件部分命中(id)、(id，name)或者(id, name, age)时才能使用到这个联合索引。

能使用该索引的情况如下：

SELECT * FROM USER WHERE id = 1

SELECT * FROM USER WHERE id = 1 and name = 'zhangsan'

SELECT * FROM USER WHERE id = 1 and name = 'zhangsan' and age = 18

不能使用该索引的情况如下:

SELECT * FROM USER WHERE name = 'zhangsan'

SELECT * FROM USER WHERE age = 18

SELECT * FROM USER WHERE name = 'zhangsan' and age = 18

对于联合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

2、回表

MySQL在InnoDB引擎下支持两种索引

聚集索引：索引里（B+树的叶子结点上）存储的是数据行（真实的数据）
普通索引：索引里（B+树的叶子结点上）存储的是主键

这里着重说一下聚集索引，官方文档有以下描述

在有主键的表，InnoDB将主键作为聚集索引
没有主键的表，InnoDB使用第一个唯一索引作为聚集索引
即没有主键也没有唯一索引时，MySQL将生成一个隐藏的6字节大小的row ID字段作为聚集索引

MySQL通过普通索引没法一次性将数据拿全的情况下，通过普通索引获取主键值，再通过主键值到聚集索引中定位到记录，这个过程就叫回表。可以通过建立覆盖索引来减少回表，比如现在要通过身份证号查姓名，那就建立身份证号和姓名的联合索引（id，name），当查询时可以通过这个索引直接拿到姓名name得值，不再需要去聚集索引里查找了，这就是覆盖索引。

3、索引下推

首先创建一个用户表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255)  DEFAULT NULL,
  `age` int  DEFAULT 0,
  `class` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_two` (`name`,`age`)
) ENGINE=InnoDB;

//这张表增加一个复合索引
 (`name`,`age`)

给表插入数据

INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 21, '1');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 22, '2');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 23, '3');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 24, '4');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 25, '5');

查询插入的数据如下

阅读剩余部分