看这篇就够了！MySQL 索引知识点超全总结( 三 ) _MySQL

Innodb B+树索引前文介绍了索引的基本数据结构，现在开始我们从 Innodb 的角度了解如何使用 B+树构建索引，索引如何工作和如何使用索引提升查找效率。
聚集索引和非聚集索引数据库中的 B+树索引可以分为聚集索引和非聚集索引。聚集索引和非聚集索引的不同点在于叶子节点是否是完整行数据。
Innodb 存储引擎表是索引组织表，即表中的数据按照主键顺序存放。聚集索引就是按照每张表的主键构造一棵 B+树，叶子节点存放的是表的完整行记录。非聚集索引的叶子节点不包含行记录的全部数据。Innodb 存储引擎的非聚集索引的叶子节点的内容为主键索引值。
若数据表没有主键聚集索引是怎么建立的？在没有主键时 Innodb 会给数据表的每条记录生成一个 6 个字节长度的 RowId 字段，会以此建立聚集索引。
Select 语句查找记录的过程下面例子将展示索引数据的组织形式及 Select 语句查询数据的过程。

建表语句：

create table T (ID int primary key,k int NOT NULL DEFAULT 0,s varchar(16) NOT NULL DEFAULT '',index k(k)) engine=InnoDB DEFAULT CHARSET=utf8;insert into T values(100, 1, 'aa'),(200, 2, 'bb'),(300, 3, 'cc'),(500, 5, 'ee'),(600,6,'ff'),(700,7,'gg');

索引结构示意

文章插图

左边是以主键 ID 建立起的聚集索引，其叶子节点存储了完整的表记录信息；右边是以普通字段 K 建立的普通索引，其叶子节点的值是主键 ID 。

Select 语句执行过程

select * from T where k between 3 and 5;执行流程如下：

在 K 索引树上找到 k=3 的记录，取得 ID=300；
再到 ID 索引树上查找 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束。

上述查找记录的过程中引入了一个重要的概念：回表，即回到主键索引树搜索的过程。避免回表操作是提升 SQL 查询效率的常规思路及重要方法。那么如何避免回表？
注：该例子来自《MySQL 实战 45 讲》
覆盖索引MySQL 5.7,建表语句：

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`),KEY `i_first_name` (`first_name`),KEY `i_hire_date` (`hire_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL 语句 A

explain select * from employees where hire_date > '1990-01-14';explain 结果：

文章插图

SQL 语句 B

explain select emp_no from employees where hire_date > '1990-01-14';explain 结果：

文章插图

分析

从前后两次 explain 的结果可以看到 SQL 语句 A 的 extra 为 using where，SQL 语句 B 的 extra 为 using where;using index 。这说明 A 没有使用索引，而 B 使用了索引。
索引 K 中包含了查询语句所需要的字段 ID 的值，无需再次回到主键索引树查找，也就是“覆盖”了我们的查询需求，我们称之为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能。
最左匹配

SQL 语句 A

explain select * from employees where hire_date > '1990-01-14' and first_name like '%Hi%';

文章插图

SQL 语句 B

explain select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';

文章插图

分析

在上述测试的 SQL 语句 A 使用了极端方式: first_name like '%Hi%'，前后都增加模糊匹配使得 SQL 语句无法使用到索引；当去掉最左边的‘%’后，SQL 语句 B 就使用了索引。最左匹配可以是字符串索引的最左 N 个字符，也可以是联合索引的最左 M 的字段。合理规划、使用最左匹配可以减少索引，从而节约磁盘空间。
索引下推何为索引下推？我们先从下面这组对比测试开始，将在 MySQL5.5 版本和 MySQL5.7 版本中执行同一条 SQL 语句：