【MySQL】（三）索引的数据结构及其创建和使用

1.索引概念

1.1.索引的定义和作用

索引是一种用来协助快读查询或更新数据的有序的数据结构。

如何理解这句话呢？
首先，我们数据表中的数据是以文件的形式存放在磁盘上，每一行都有一个磁盘地址。我们在检索数据的时候，需要一行一行的遍历数据表，获取出满足条件的数据行。
如果使用了索引，通过索引的数据结构特性，可以很快的找到存放在索引文件中的，满足条件的数据行的磁盘地址，通过磁盘地址可以很快的定位数据。

如果把数据表比作一本字典，索引就类似于这本字典的目录。我们要查某个字的时候，不可能从第一页开始翻，而是先去查找目录，定位到多少页直接翻过去。

1.2.索引查询快的原因

上面提到了，索引的作用是为了快速的检索数据。索引是怎么做到的快速检索呢？
主要是两个点：索引有序并采用二分查找法。

1.3.索引的数据结构

在说MySQL实际的索引数据结构之前，我么可以先思考一下，什么样的数据结构能满足查询效率高的特点。

1.3.1.有序数组或链表

常用的数据结构中，有序数组的查询效率就很高，但是想在数组中间插入一个数据时，会存在大量的数据挪动，十分影响插入的效率，这种数据结构是不可行的。同样，链表的插入效率虽然高，但查询需要一个一个遍历，效率过低。

1.3.2.二分查找树（Binary Search Trees）

在《数据结构可视化》这个网址中可以查看到数据结构，及其变化过程。
二分查找树的结构如下：

左子树的节点 < 父节点，右子树的节点 > 父节点。既实现了快速查找，又实现了快速插入。
查询的时候，通过查询条件和节点中的索引值比较来确定走向。
插入的时候，插入的值和节点中的索引值比较，较小则插入左边，较大则插入到右边。如果向下走1级后还有子节点，则再比较子节点和插入值的大小。

但是如果按顺序插入，就会形成一个类似链表的结构，叫做斜树。

---

所以，二分查找树的问题在于树的左右子节点深度差距过大，这种情况下并不能起到加快检索效率的效果。

1.3.3.平衡二叉树（AVL Trees）

为了解决上面说的二分查找树的问题，引入了平衡二叉树。所谓的平衡二叉树，就是一种特殊的二叉树，特殊点是在于树中每个节点的左右子树深度差的绝对值不超过1。
按照上面形成斜树的顺序插入数据，会变成下面的结构：

平衡二叉树是通过节点的左旋和右旋实现的平衡。解决了树结构的平衡问题后，接下来的问题是，树结构中每个节点里面到底存放的是什么东西呢？

1.3.3.1.平衡二叉树中存放的数据

如果要把平衡二叉树作为索引，至少应该存以下的三类数据：

• 索引值
• 数据在磁盘中保存位置的地址值
• 左右子树的引用

有了这三类数据，一个基本的索引就形成了。

1.3.3.2.平衡二叉树存在的问题

索引是以文件的形式存在磁盘中的，InnoDB中操作磁盘的最小单位是页（Page），一页的大小是16k。所以一个树节点的大小就是16k，只保存上面的那些数据，几十个字节就够了，一次IO读取一页数据，实际就取用了几十个字节，浪费了大量的空间。
同时，一个节点只保存一个索引值，当数据量增大的时候，树的深度会越来越大，查询一行数据发生的IO次数就越来越多，效率就会减慢。

---

针对这些问题，如何解决呢？

• 每个节点存储更多的数据，解决空间浪费问题。
• 增加每个节点中存放索引值的数量，也就是增加每个节点的子节点数量。

1.3.4.多路平衡查找树（BTree）

先看一下B树的数据结构：

这是三路平衡查找树的演示，相对于平衡二叉树的节点旋转，BTree是同过节点的分裂、合并来实现平衡的。
B树中，路数越多，树就越矮，相对于平衡二叉树大大的增加了检索数据的效率。

---

BTree结构的索引，不管是插入还是查询的效率都已经很可观了。MySQL中使用的是针对BTree做了进一步改良的B+Tree

1.3.5.B+Tree

MySQL中的B+Tree数据结构如下，从终于有篇看的懂的B树文章了！借了一张图：

B+Tree相对BTree的不同点：

• 根节点和枝节点不保存数据，只保存指针，只有叶子节点才保存数据。不管根节点和枝节点是否命中索引，都需要到叶子节点中获取数据。
• 叶子节点之间加入了顺序连接的指针。

MySQL中的B+Tree相对传统的B+树做了改进：

• 节点中的关键字数量和路数相等

B+Tree的深度

假设使用bigint做索引，一个long 8字节，加上InnoDB中的引用大小6字节，一共14字节。
总共可以保存16 * 1024 / 14 = 1170个索引值，根节点对应1170个枝节点。
那总共对应1170 * 1170 = 1368900 个叶子节点。
每个叶子节点16k，总共可以保存21902400kb的数据，假设一行数据占1kb的情况下，可以保存2000多万的记录。
所以一个B+Tree的索引往往是1到3层。

B+Tree相对BTree的优势

• 相对于BTree，根节点和枝节点可以存储更多的索引值，就有更多的路数。
• 磁盘读写能力更强，一次IO可以加载出更多的关键字。
• 排序能力更强，因为叶子节点中有指针。
• 做范围查询效率更高，只需要在叶子节点中中遍历。
• 查询效率更稳定，IO次数每次查询都是一样的，不会因为数据结构的改变而忽快忽慢。

1.4.索引的类型

• 普通索引：没有限制。
• 唯一索引：有键的唯一性验证。
• 全文索引：针对较大的数据，解决like效率的的问题，只能在文本类型的字段上创建。

1.5.索引方法

• Hash
• BTree

hash索引以KV检索数据，等值查询比较快，但是不支持范围查询。并且重复值较多的时候，容易出现大量的hash冲突。
MySQL中InnoDB默认使用BTree索引。

1.6.索引存储方式

在不同的数据库引擎中，索引的存储方式有所区别：

• MyISAM：有三种文件，分别是.frm,.MYI,.MYD，其中frm是表结构文件，MYI是表索引文件，MYD是表数据文件。
• InnoDB：有两种文件，分别是.frm,.ibd，frm同样是表结构文件，ibd文件中包含了索引和数据。

2.索引的创建

InnoDB默认创建的索引类型是BTree。
InnoDB创建索引的语句：

-- 普通索引
alter table table_name add index idx_cloumn_name(cloumn_name);
-- 唯一索引
alter table table_name add unique uk_cloumn_name(cloumn_name);

2.1.几个索引使用中的概念

聚簇索引（聚集索引）
InnoDB中，索引值和对应的行数据存放到一起，这种索引就是聚簇索引。其它的都是辅助索引。
一张表一定会有一个聚簇索引，一般是主键索引，如果没有显示的指定主键，则会使用第一个不包含null值的唯一索引做聚簇索引。如果没有则使用隐藏的rowId字段做聚簇索引。
辅助索引
也叫做二级索引，InnoDB中的每一张表除了聚簇索引外都是辅助索引，辅助索引中存放的是索引值及对应的主键索引值。
回表
辅助索引检索后获取到对应的主键值，再到主键索引中检索，这种过程叫做回表。
联合索引
一个索引的索引值包含多个字段叫做联合索引。
覆盖索引
一次查询中，所需要的查询的字段值完全包含在索引中，直接从辅助索引返回待查询的字段值，不做回表操作。一般是配合联合索引一同使用。
前缀索引
索引所在字段值较大时，会使检索速度降低，这时候就可以选择一定长度的前缀作为索引值。查询的时候先比较查询条件的前缀与索引值，若比较出多个，再从已筛选的数据中比较完整的查询条件值，相等则返回数据。
索引条件下推
MySQL5.6中加入的优化，将索引相关的条件由MySQL服务下推到存储引擎中做条件检索，减少IO的次数。

2.2.在哪些字段上创建索引

在使用到条件查询字段、表连接的字段、排序分组字段，可以创建索引,例如where、on、order by、group by。
创建索引需要注意以下的原则：

• 列的离散度不能太低。
• 列的数据不能更新太频繁。
• 不建议使用身份证号、uuid等无序的数据列创建索引。
• 一个表中创建索引的数量不能过多。
• 尽可能的创建联合索引，而不是新增一个索引。
• 字段过长时，使用前缀索引。

列的离散度计算公式

count(distinct(column_name))/count(*);
列中的数据重复值越多，离散度就越低。

更新太频繁的影响

因为MySQL的索引使用B+Tree，频繁的更新会导致索引数据频繁的分裂合并，消耗机器性能。

无序数据做索引

相对于有序的数据，无序数据会加剧B+Tree索引的分裂合并。

2.2.1.创建联合索引

给多个字段创建联合索引，尽可能的将离散度高的字段放在左边。

alter table table_name add index idx_cloumn1_cloumn2(cloumn1,cloumn2);

2.2.2.前缀索引的创建

创建的脚本如下：

alter table table_name add index idx_clounm_pre(cloumn_name(length));

上列的length表示使用前缀索引的截取长度，这个截取的长度多少合适呢？
先看下计算公式：

select 
count(distinct cloumn_name)/count(*) as a, 
count(distinct left(cloumn_name, 10))/count(*) as b, 
count(distinct left(cloumn_name, 20))/count(*) as c,
count(distinct left(cloumn_name, 50))/count(*) as d 
from table_name;

计算前缀的离散度靠近完整字段值的离散度就可以了，在趋近于完整字段值离散度的情况下，length越短越好。

3.索引的使用

3.1.最左匹配原则

3.1.1.联合索引查询

查询条件中的条件值和树节点中索引值是从左到右比较的。
对联合索引来说，节点中的索引值也是从左到右排列的，也就是说，要使联合索引命中，查询条件后必须包括左侧的索引字段。
例如给一张表的name和phone字段创建索引。

alter table table_name add index idx_name_phone(name,phone);

那查询SQL中必须包括name字段。

①select * from table_name where name = '张三';
②select * from table_name where phone = '135xxxxxxxx';
③select * from table_name where name = '张三' and phone = '135xxxxxxxx';
④select * from table_name where phone = '135xxxxxxxx' and name = '张三';

上面①③④情况都是可以命中索引的，第④种情况即使没有按顺序写，优化器也会优化查询顺序从左到右排列。

3.1.2.左模糊匹配

使用左模糊匹配的时候，因为左边的值是不确定的，索引不知道走哪一路，这就是左模糊匹配不走索引的原因。

3.2.数据查询分析（Explain）

通过explain可以分析当前执行的SQL的索引使用情况。在explain的结果中有几个重要的字段需要关注，分别是：

• table：当前查询表的别名，用于确定是SQL查询中的哪一个表。
• type：访问类型，性能由低到高依次为ALL、Index、range、ref、eq_ref、const、system、NULL。
• possible_key:可能使用到的索引。
• key：实际使用到的索引。
• rows：扫描的行数。
• extra：执行情况说明。

---

在type中的几种类型分别代表：

• ALL：全表扫描。
• Index：遍历索引树。
• range：范围查询命中索引。
• ref：普通索引的多表连接查询中的连接条件，或普通索引where查询。
• eq_ref：唯一索引多表连接。
• const：主键或唯一索引命中一行数据。
• system：只有一行数据，多出现于子查询只有一行。

---

在extra中：

• using index：表示使用了覆盖索引。

3.3.索引失效的常见原因

• 对索引列使用了函数。
• 查询条件存在隐式转换。
• 连表查询是，连接条件列的字符集或排序规则不一致。
• 左模糊匹配。
• 优化器认为使用了全表扫描效率更高，常见的有列的离散度过低、数据过少、使用了负向查询（not null, != ,not in)等。