MySQL索引学习

资源来自于腾讯视频咕泡学院公开课，整理学习用，侵权立删。
正确的创建合适的索引是提升数据库查询性能的基础

索引是什么

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

数据结构包括索引列和地址列 ，索引列为建立表中建立索引的列，

MySQL索引数据结构探秘

首先，了解几种数据结构
二叉查找树(Binary Sort Tree):又称为二叉查找树.它或者是一种空树,或者是具有以下性质的二叉树：

若他的左子树不空，则左子树上所有节点的值均小于它的根结构的值
若它的右子树不空，则右子树上所有的值均大于它的根节点的值
他的左右子树也分别为二叉排序树
查找方式：类似于二分查找
存在的问题：二叉查找树的根节点是插入的第一个节点，如果第一个节点的值很大或者很小，查找时并没有优势。存在数据组织的问题

平衡二叉树（Self-Balancing Binary Search Tree或Height-Balanced Binary Search Tree）（AVL树）：是一种二叉排序树，其中每一个节点的左子树和右子树的高度差至多等于1。
多路查找树：其每一个节点的孩子书可以多于两个，且每一个节点处可以存储多个元素。有四种形式：2-3树，2-3-4树，B树，B+树。

平衡二叉查找树（balanced binary search tree ）：是一种结构平衡的二叉搜索树，即叶节点高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
结构如下：

索引是一种离散存储的结构，每一个节点数据在一个磁盘块上。
为什么不使用平衡二叉查找树

搜索效率不足：一般来说，在树结构中数据所处的深度决定着它的搜索时的IO次数。
查询次数不稳定：不同位置的节点可能需要不同的查询次数。
节点数据内容太少：操作系统和磁盘的一次数据交换以页为单位，一页数据大小为4KB

• 每一个磁盘块（节点/页）保存的关键字数据量太小了
• 没有很好的利用操作系统和磁盘的数据集的IO特性
• 没有利用好磁盘IO的预读能力（空间局部性原理）

多路平衡查找树(B-Tree)：是一种平衡的多路查找树。2-3树，2-3-4树都是B树的特例。

多路平衡二叉树解决了平衡二叉查找树存在的问题，包括减少了I/O的次数（路多了，每个节点上存储的数据多了，同样的数据量，树的高度变小了），利用了磁盘预读的特性（每个节点的存储的数据量大，能够很好的预读）。

MySQL索引之加强版多路平衡查找树 B+树

B+树和B树的区别

• B+节点关键字采用左闭合区间，认为索引从小向大发展。
• 非叶子节点不保存数据相关信息，只保存关键字和子节点的引用。
• B+关键字对应的数据保存在叶子节点中
• B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

MySQL 为什么选用B+树

• B+树是B树的变种，多路绝对平衡查找树，他拥有B树的优势
• B+树扫库、表能力更强 （B+树只需要遍历子节点就可以达到全局搜索的目的）
• B+树磁盘读写能力更强 （根节点和分支节点不保存数据，一次加载更多关键字）
• B+树的排序能力更强 （叶子节点天然有序，且具有引用关系）
• B+树的查询效率更加稳定 （查询均需要相同的IO次数）

MySQL B+Tree具体落地形式
MySQL引擎作用在表上，一个数据库中可以有多个存储引擎。MySQL5.5之前默认MyISAM，之后默认InnoDB

MyISAM引擎
创建一个MyISAM引擎的表会产生下面三个拓展名的文件
*.MYI 表的索引文件 （B+Tree结构）
*.FRM 表的定义文件
*.MYD 表的数据文件

在查找时（使用索引）在索引文件中查找关键字，在叶子节点找到此关键字所对应的地址，然后到指定地址从数据文件接在值

主键索引和辅助索引是同级的。

InnoDB引擎
以主键为索引来组织数据的存储。
聚集索引：数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。

创建一个InnoDB引擎的表会产生下面两个拓展名的文件
.FRM 表的定义文件
.IBD 索引及数据文件 （B+Tree 关键字为主键）

数据以主键存储，在索引的最末尾，将记录的所有数据保存在主键的叶子节点中。设计初衷为主键才是最主要使用的索引。
辅助索引（非主键），建立索引结构（B+Tree，也存在于.IBD文件中），叶子节点保存主键。使用非主键索引搜索时，需要先遍历辅助索引结构找到主键，然后遍历主键索引查找值。

MySQL索引原则

列的离散性
计算公式：count(distinct col):count(col)
离散性越高，选择性就越好

离散性低的数据选择不唯一，查询优化器很难优化；离散性高的数据选择唯一。

最左匹配原则
对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过。

在索引的匹配过程，如果是字符串，会按照ASCII的顺序，从左向右一次匹配每个字符，命中或者继续向下查找。

like之前加“%前缀 ”索引会失效，直接进行全表扫描

联合索引
单列索引：节点中关键字为某一列
联合索引：节点中关键字为多列

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

联合索引选择原则：从上向下
经常用的列优先【最左匹配原则】
离散度高的列优先【离散度高原则】
宽度小的列优先【最少空间原则】（非叶子节点可以存储更多的索引）

联合索引中如果不是按照索引最左列开始查找，无法使用索引；
联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

覆盖索引
如果查询的列，通过索引项的信息可直接返回，则该索引称之为查询SQL的覆盖索引。
InnoDB二级索引叶子节点存储的为主键的值