MySQL04-彻底搞懂索引

1 前言：

在日常工作中会经常接触到， 比如某一个SQL查询比较慢，分析完原因之后， 你可能就会说“给某个字段加个索引吧”之类的解决方案。 但到底什么是索引，索引又是如何工作的呢？本文就此展开探索！

2 索引究竟是什么？

一本500页的书， 如果你想快速找到其中的某一个知识点， 在不借助目录的情况下， 那我估计你可得找一会儿。 同样， 对于数据库的表而言， 就像书的目录一样， 索引其实就是“书的目录”。它的出现其实就是为了提高数据查询的效率。

3 索引的常见模型（数据结构）

索引的出现是为了提高查询效率， 但是实现索引的方式却有很多种，好的数据存储结构能够提高查询效率，所以这里也就引入了索引模型的概念。 可以用于提高读写效率的数据结构很多，先学习三种常见的数据结构， 它们分别是哈希表、 有序数组和搜索树。

1. 哈希表

哈希表是一种以键-值（key-value） 存储数据的结构， 我们只要输入待查找的值即key， 就可以找到其对应的值即Value。

哈希的思路
把值放在数组里， 用一个哈希函数把key换算成一个确定的位置， 然后把value放在数组的这个位置。不可避免地， 多个key值经过哈希函数的换算， 会出现同一个值的情况。 处理这种情况的一种方法是， 拉出一个链表

评价：
相同的hash值的value会直接添加在对应下标位置的链表尾部上，这样的好处是增加元素的速度快，但缺点是由于不是有序的，所以哈希索引做区间查询的速度很慢，所以， 哈希表这种结构适用于只有等值查询的场景。

2. 有序数组

有序数组的实现思路
假设身份证号为元素在数组中的下标，没有重复， 这个数组就是按照身份证号递增的顺序保存的

评价
这种索引结果用来做范围查询很快，但是更新数据的时候就很麻烦，往中间插入一个记录就必须挪动后面所有的记录，成本太高。

3. 二叉搜索树

二叉搜索树的特点

• 每个节点的左儿子小于父节点， 父节点又小于右儿子。

评价

• 按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2这个路径得到。 这个时间复杂度是O(log(N))
• 当然为了维持O(log(N))的查询复杂度， 你就需要保持这棵树是平衡二叉树。 为了做这个保证， 更新的时间复杂度也是O(log(N))
• 当数据量很大的时候，二叉树的高高度过高将会导致查询时间太长，所以我们一般使用N叉树

4 索引的实战部分

在MySQL中， 索引是在存储引擎层实现的（因为数据是存储在存储层的）， 所以并没有统一的索引标准， 即不同存储引擎的索引的工作方式并不一样。 而即使多个存储引擎支持同一种类型的索引， 其底层的实现也可能不同。 由于InnoDB存储引擎在MySQL数据库中使用最为广泛， 所以下面我就以InnoDB为例， 和你分析一下其中的索引模型。

4.1 InnoDB的索引模型

在InnoDB中， 表都是根据主键顺序以索引的形式存放的， 这种存储方式的表称为索引组织表。

InnoDB使用了B+树索引模型， 所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。

4.2 例子1

假设， 我们有一个主键列为ID的表， 表中有字段k， 并且在k上有索引。这个表的建表语句是：

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、 (200,2)、 (300,3)、 (500,5)和(600,6)， 两棵树（主键ID索引树，非主键K索引树）的示例示意图如下。

主键索引的叶子节点存的是整行数据。 在InnoDB里， 主键索引也被称为聚簇索引（clusteredindex）

非主键索引的叶子节点内容是主键的值。 在InnoDB里， 非主键索引也被称为二级索引（secondaryindex）。

4.3 例子2

执行下面操作，需要执行几次树的搜索，会扫扫描多少行

执行SQL

 select * from Twhere k between 3 and 5，

数据库表的建立

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

查看所索引树

SQL有语句的执行流程

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， 不满足条件， 循环结束

可以看到， 这个查询过程读了k索引树的3条记录（步骤1、 3和5） ， 回表了两次（步骤2和4） 。

4.4 基于主键索引和普通索引（非主键索引）的查询有什么区别？

• 如果语句是select * from Twhere ID=500， 即主键查询方式， 则只需要搜索ID这棵B+树；
• 如果语句是select * from Twhere k=5， 即普通索引查询方式， 则需要先搜索k索引树， 得到ID的值为500， 再到ID索引树搜索一次。 这个过程称为**回表**。

也就是说， 基于非主键索引的查询需要多扫描一棵索引树。 因此， 我们在应用中应该尽量使用主键查询

4.5 覆盖索引

覆盖索引（该索引便是要查询的字段数据）
如果执行的语句是select ID from Twhere k between 3 and 5， 这时只需要查ID的值， 而ID的值已经在k索引树上了， 因此可以直接提供查询结果， 不需要回表。 也就是说， 在这个查询里面，索引k已经“覆盖了”我们的查询需求， 我们称为覆盖索引

4.6 联合索引

联合索引（多个字段组成索引）
我们知道， 身份证号是市民的唯一标识。 也就是说， 如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。 而再建立一个（身份证号、 姓名） 的联合索引， 是不是浪费空间？
如果现在有一个高频请求， 要根据市民的身份证号查询他的姓名， 这个联合索引就有意义了。 它可以在这个高频请求上用到覆盖索引， 不再需要回表查整行记录， 减少语句的执行时间。
当然， 索引字段的维护总是有代价的。 因此， 在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

4.7 索引的最左前缀原则

索引的最左前缀原则（多字段索引的顺序问题）
最左前缀：顾名思义，就是最左优先，比如我们建立多列索引id_name_age，由于最左优先原则就相当于建立了id单列索引，id_name组合索引以及id_name_age组合索引，所以，我们在创建多列索引时，根据业务要求，where语句中使用最频繁的一列放在多列索引的最左边，同理第二、第三…等等的列依次存放。

那么多列索引id_name_age的有效组合有哪些？

• id
• id_name
• id_name_age
  利用最左前缀原则我们相当于创建了三个索引，这样大大减少了需要维护的索引数量。

4.8 索引下推

索引下推：（使用联合索引找到记录，无需回表直接判断）
在了解这个概念之前，我们先看一个例子

我们还是以市民表的联合索引（name, age） 为例。 如果现在有一个需求： 检索出表中“名字第一个字是张， 而且年龄是10岁的所有男孩”。 那么， SQL语是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

你已经知道了前缀索引规则， 所以这个语句在搜索索引树的时候， 只能用 “张”， 找到第一个满足条件的记录ID3。然后呢是判断其他条件也就是age>10是否满足。

在MySQL 5.6之前， 只能从ID3开始一个个回表。 到主键索引上找出数据行， 再对比字段值。

而MySQL 5.6 引入的索引下推优化（indexcondition pushdown)， 可以在索引遍历过程中， 对索引中包含的字段先做判断， 直接过滤掉不满足条件的记录， 减少回表次数

看到这里如果还不明白，我们比对一下两个的索引过程

• 没有使用索引下推的索引过程
  
  图3中， 在(name,age)索引里面我特意去掉了age的值， 这个过程InnoDB并不会去看age的值，只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。 因此， 需要回表4次。

• 使用了索引下推的索引过程
  
  InnoDB在(name,age)索引内部就判断了age是否等于10， 对于不等于10的记录， 直接判断并跳过。 在我们的这个例子中， 只需要对ID4、 ID5这两条记录回表取数据判断， 就只需要回表2次。

看到这里，相信你已经明白了，通过索引下推，可以帮我们减少回表的次数，加快查询的速率

4.9 索引维护

基于索引维护过程的案例分析


参考链接：MySQL最左前缀原则