看这篇就够了!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');
  • 索引结构示意

看这篇就够了!MySQL 索引知识点超全总结

文章插图
 
左边是以主键 ID 建立起的聚集索引,其叶子节点存储了完整的表记录信息;右边是以普通字段 K 建立的普通索引,其叶子节点的值是主键 ID 。
  • Select 语句执行过程
select * from T where k between 3 and 5;执行流程如下:
  1. 在 K 索引树上找到 k=3 的记录,取得 ID=300;
  2. 再到 ID 索引树上查找 ID=300 对应的 R3;
  3. 在 k 索引树取下一个值 k=5,取得 ID=500;
  4. 再回到 ID 索引树查到 ID=500 对应的 R4;
  5. 在 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 结果:
看这篇就够了!MySQL 索引知识点超全总结

文章插图
 
  • SQL 语句 B
explain select emp_no from employees where hire_date > '1990-01-14';explain 结果:
看这篇就够了!MySQL 索引知识点超全总结

文章插图
 
  • 分析
从前后两次 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%';
看这篇就够了!MySQL 索引知识点超全总结

文章插图
 
  • SQL 语句 B
explain select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';
看这篇就够了!MySQL 索引知识点超全总结

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


推荐阅读