mysql 索引 触发器 mysql索引机制

MySQL 的索引基础

什么是索引的

索引是为了加速对表中数据行的检索而创建的一种分散存储的（不连续的）数据结构，硬盘级的。

索引意义：索引能极大的减少存储引擎需要扫描的数据量，索引可以把随机IO变成顺序IO。索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。正确的创建合适的索引是提升数据库查询性能的基础。

为什么选择B+Tree

hash表索引结构

缺点：

1. 利用hash存储的话，需要将所有的数据文件添加到内存，比较耗费内存空间
2. 如果所有的查询都是等值查询，那么hash确实快，但是在企业或者实际工作中范围查找的数据更多，而不是等值，因此hash就不太合适了

二叉树索引结构

性质：

1. 若任意节点的左子树不空，则左子树上所有节点的值均小于它的根节点的值；
2. 若任意节点的右子树不空，则右子树上所有节点的值均大于它的根节点的值；
3. 任意节点的左、右子树也分别为二叉查找树；
4. 没有键值相等的节点。

缺点：

1. 我们每次在插入新的节点的时候，数据都比上一个插入的节点大，则如同链表，造成 io 次数过多

红黑树索引结构

性质：

1. 节点是红色或黑色。
2. 根节点是黑色。
3. 每个叶节点( NIL节点)是黑色的。
4. 每个红色节点的两个子节点都为黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)
5. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。
6. 最长路径不超过最短路径的2倍

缺点：

1. 同样会因为树的深度而造成 io 次数变多，影响数据的读取效率

B树索引结构

性质：

1. 所有键值分布在整颗树中
2. 搜索有可能在非叶子结点结束，在关键字全集内做一次查找，性能逼近二分查找，每个节点最多拥有m个子树
3. 根节点至少有2个子树
4. 分支节点至少拥有m/ 2颗子树(除根节点和叶子节点外都是分支节点)
5. 所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

实例说明：

每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在的磁盘地址，两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域，以根节点为例，关键字为16和34，p1指针指向的子树的数据范围小于16，p2指针指向的子树的数据范围为16~34，p3指针指向的子树的数据范围为大于34.

关键字查找过程：

1. 根据关键字查找到磁盘块1，读入内存。（磁盘I/O操作1次）
2. 比较关键字28在区间（16，34）找到磁盘块 1 的指针p2
3. 根据p2指针找到磁盘块3，读入内存。（磁盘I/O操作2次）
4. 比较关键字28在区间（25，31），找到磁盘块3的指针p2
5. 根据p2指针找到磁盘块8，读入内存。（磁盘I/O操作3次）
6. 在磁盘块8中找到关键字28

缺点：

1. 每个节点都有key,同时也包含data,而每个页存储空间是有限的，如果data比较大的话会导致每个节点存储的key数量变小
2. 当存储的数据量很大的时候会导致深度较大，增大查询时磁盘io次数，进而影响性能

B+树索引结构

性质：

1. B+树每个节点可以包含更多的节点，这样做主要有两个原因，第一个原因降低树的高度，第二个原因将数据范围变为多个区间，区间越多，数据检索越快
2. 非叶子节点存储key,叶子节点存储key和数据
3. 叶子节点两两指针互相链接（符合磁盘的预读性），顺序查询性能更高

InnoDB 的索引结构

注意：

1. InnoDB 是通过B+Tree 结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，会选择唯一键，如果没有唯一键，会生成一个6位的row_id来作为主键
2. 如果创建索引的的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表

MyISAM 的索引结构

注意：

1. MyISAM 是通过B+Tree 结构对主键创建索引，但是叶子节点存储的不是主键id而是地址