MySQL性能优化1-MySQL索引机制（B+ Tree）

目录

索引是谁实现的

什么是索引

B+ Tree

MySQL B+ Tree的实现（主要InnoDB MyISAM中)

MyISAM

InnoDB

索引知识补充

列的离散性

最左匹配原则

联合索引

覆盖索引

小结

---

索引是谁实现的

 存储引擎是在表上实现的，一个库中的表可以使用不同的存储引擎。索引就是由存储引擎来实现的。

什么是索引

正确的创建合适的索引是提升数据库查询性能的基础。

索引：索引是为了加速对标中数据行的检索而创建的一种分散存储数据结构。

如上图中，如果现在有一条sql语句select * from teacher where id = 101，在没有索引的情况下，我们要找到这条记录，需要进行全表扫描，匹配id=101的数据。如果有了索引，我们就可以快速的通过索引找到101所对应的行记录在磁盘中的位置，再根据给定的地址取出对应的数据。

使用索引的好处：

1. 索引能极大的减少存储引擎需要扫描的数据量
2. 索引可以把随机IO变成顺序IO
3. 索引可以帮助我们在进行分组，排序等操作时，避免使用临时表。

B+ Tree

首先，复习一下各种树的定义

满二叉树：除最后一层无任何子节点外，每一层上的所有结点都有两个子结点的二叉树。

完全二叉树：如果一个二叉树的深度为h，其它各层（1~h-1）的节点个数都达到最大个数，第h层所有的节点都连续集中在最左边，这就是完全二叉树。

二叉搜索树：对于一个节点，若其左子树不为空，则其左子树的值均小于这个根节点的值，若其右子树不为空，则其右子树的值均大于这个根节点的值。左、右子树也分别为二叉搜索树。没有键值相等的节点。

AVL（平衡二叉树）：AVL是一种自平衡二叉查找树，节点的子节点的高度差不能超过1.平衡是通过左旋转和右旋转来实现的。

红黑树：红黑树是一种自平衡二叉查找树。节点是红色或黑色；根节点是黑色的；每个红色节点的两个子节点都是黑色的。（从每个叶子到根的所有路径上不能有两个连续的红色节点）；从任意节点到其每个叶子的所有路径都包含相同数目的黑色节点。

首先红黑树是不符合AVL树的平衡条件的，即每个节点的左子树和右子树的高度最多差1的二叉查找树。但是提出了为节点增加颜色，红黑是用非严格的平衡来换取增删节点时候旋转次数的降低，任何不平衡都会在三次旋转之内解决，而AVL是严格平衡树，因此在增加或者删除节点的时候，根据不同情况，旋转的次数比红黑树要多。所以红黑树的插入效率更高！！！AVL的查询效率更高。

多路查找树：每一个节点的孩子树可以多于两个，且每一个节点处可以存储多个元素。由于它是查找树，所以元素之间存在某种特定的排序关系。

B Tree：B Tree是一颗多路查找树，B Tree是一颗绝对平衡树（对于任意一个节点，左右子树高度相等）。2-3树是B树的特列，节点最大的孩子数目称为B树的阶，2-3树是3阶B树。

B+ Tree：B+ Tree是B树的变种，有着比B树更高的查询性能。

• 有m个子树的节点包含有m个元素（B Tree中是m-1）
• 根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中
• 所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或者最小元素
• 叶子节点会包含所有的关键字，以及记录指向数据记录的指针，并且叶子节点本身是根据关键字的大小从小到大顺序连接

接着，为什么选用B+ Tree作为索引？

看一下二叉树

缺点：当数据分布不均衡的时候，可能退化为一个链表，查找效率低。

看一下AVL树：

缺点：它太深了：数据处的深（高）度决定着他的IO次数，IO操作耗时大。理解：每次读取一个节点信息都要将其从磁盘加载到内存中。它太小了：每一个磁盘块保存的节点数量比较少，数据量就小；没有很好的利用操作磁盘的交换特性；也没有利用好磁盘IO的预读能力（空间局部原理），从而带来频繁的IO操作。交换特性：磁盘每次能和内存交换4k大小的数据，但是一个节点的信息明显比4k小的多。预读能力：当从磁盘读取信息的时候，如果先读了4K，就会认为接下来将使用相邻的4k数据，将进行读取。

看一下B Tree

   B Tree的优点：相同的深度，由于是多路平衡的查找树，节点存储的信息更多，IO次数就较少。很好的利用了磁盘的交换特性和预读能力。关键字=最大的路数减1

最后看B+ Tree

与B Tree的不同：

1. B+ Tree节点搜索采用左闭合区间
2. B+非叶子节点不保存数据信息，只保存关键字和子节点的引用
3. B+关键字对应的数据保存在叶子节点中
4. B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

为什么选用B+ Tree

1. B+ Tree是B Tree的变种（plus）版多路平衡查找树，他拥有B Tree的优势
2. B+树扫库、表能力更强：除叶子节点外其它节点不存放数据信息，就可以存放更多的节点
3. B+树的磁盘读写能力更强：和其数据存储有关，数据存储在叶子节点。
4. B+树的排序能力更强：其叶子节点是顺序的，且相邻的节点具有顺序引用。保证数据天然具有排序能力。
5. B+树的查询效率更稳定：总是要查找到最后一层才有数据。

MySQL B+ Tree的实现（主要InnoDB MyISAM中)

• MyISAM

在MyISAM中，索引和数据是分开存放的，索引的叶子节点存放的是数据的地址。对于不同的索引，都可以定位到数据的地址。其中，索引文件为MYI，数据文件为MYD

• InnoDB

在InnoDB中以主键为索引来组织数据的存储。

聚集索引（聚簇索引）：数据库表行中的数据的物理地址与键值的逻辑（索引）顺序相同。理解一下：就是数据库中的数据行的物理地址按照索引顺序进行排列。一个数据表只能有一个聚簇索引。就是叶子节点就存放了数据，而不是将数据独立出来。

当一个表没有主键的时候，存储引擎会隐式的创建主键，为一个6位的int类型

非聚簇索引将通过索引查找到其聚簇索引的值，再在主键索引中进行查找。

辅助索引不直接指向数据区：是为了在数据区的元素有变化的时候，比如插入删除，不需要再去维护辅助索引。

对比：

 

索引知识补充

• 索引的优点

1. 大大减少了服务器需要扫描的数据量
2. 可以帮助服务器避免排序和临时表
3. 索引可以将随机IO变为顺序IO

• 可以使用B+ Tree索引的查询类型

全键值、键值范围、键前缀匹配（最左前缀）（分析以以上数据表为例）

• 全值匹配

全值匹配是和索引列中的所有列进行匹配，例如查找姓名为Cuba Allen、出生于1960-01-01的人

• 匹配最左前缀

值用索引的第一列，如查找姓为Allen的人

• 匹配列前缀

也可以只匹配某一列的值的开头部分。例如查找以J开头的姓的人

• 匹配范围值

使用某一列的索引查找在某个范围内的值，例如查找姓在Allen和Barraymore之间的人

• 精确匹配某一列并范围内匹配另外一列

前面一列精确匹配，后面一列在某个范围内查找，查询姓为Allen，名以K开头的人

• 只访问索引的查询

覆盖索引，索引中就能查找到所需要的信息

B+ Tree索引的限制：

• 如果不是按照索引的最左列进行查找，则无法使用索引，例如无法查找名为Bill的人
• 不能跳过索引中的列。例如无法查找姓为Smith并且在某个特定日期出生的人
• 如果查询中的某个为范围查询，则其右边所有的列都无法使用索引优化查找

• 列的离散性

列的离散型越高，选择性就越好。上表中，name离散型最高，sex离散型最低。

• 最左匹配原则

多索引中关键字进行比较（对比），一定要从左到右依次进行，且不可跳过

• 联合索引

• 单列索引

节点关键字[name]

• 联合索引

节点关键字[name,phoneNum]

单列索引是特殊的联合索引

• 联合索引选择列的原则

1. 经常用的列优先【最左匹配原则】
2. 选择性（离散度）高的列优先【离散度原则】
3. 宽度小的列优先【最小空间原则】

• 覆盖索引

如果查询列可以通过索引节点中的关键字直接返回，则该索引称为覆盖索引。

覆盖索引可以减少数据库的IO，将随机IO变为顺序IO，可提高查询性能。

B+ Tree可将随机IO变为顺序IO：主要有两点：数据在聚簇索引叶子节点中顺序存储，相邻的节点也存在顺序引用。第二个就是覆盖索引，查询过程本身就具备顺序性。

小结

https://blog.csdn.net/Abysscarry/article/details/80792876

• 索引列的数据长度能少则少。（能增多节点）
• 索引一定不是越多越好，越全越好，一定是建合适的。（索引冗余会占空间）
• 匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引；（最右前缀）
• Where 条件中 not in 和 <>操作无法使用索引；（无法用到匹配）
• 匹配范围值，order by 也可用到索引；
• 多用指定列查询，只返回自己想到的数据列，少用select *；
• 联合索引中如果不是按照索引最左列开始查找，无法使用索引；
• 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
• 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；