Mysql索引的认识

目录

一、索引数据结构：

1.1二叉树：

1.2红黑树：

1.3Hash索引

1.4B-TREE

二、聚集和非聚集索引：

2.1 非聚集索引：myisam

2.2聚集索引 InoDB

三、联合索引

---

图上传不了。。。

一、索引数据结构：

笔记目录（性能调优.mysql）

索引是帮助MySQL高效获取数据的排好序的数据结构,类似于书本的目录，通过目录快速定位到页数（资源）。

索引一般有：

1. 二叉树
2. 红黑树
3. Hash表
4. B-Tree

数据结构生成网站： Data Structure Visualization (usfca.edu)

1.1二叉树：

定义： 二叉树 是n(n>=0)个结点的有限集合，该集合或者为空集（称为空二叉树），或者由一个根结点和两棵互不相交的、分别称为根结点的左子树和右子树组成

二叉树特点：

1. 每个结点最多有两颗子树，所以二叉树中不存在度大于2的结点。
2. 左子树和右子树是有顺序的，次序不能任意颠倒。
3. 即使树中某结点只有一棵子树，也要区分它是左子树还是右子树。

左斜树

右斜树

正常二叉树

结论：二叉数在mysql中有一定的效率优化，但是当索引递增（右斜树），效率和全表查询差不多，都是一个一个查，因此mysql不适用二叉树。

1.2红黑树：

平衡二叉树的具体实现，除此之外还有： AVL、 替罪羊树、Treap、伸展树等

结论：当数据量过于庞大，几百万，几千万，此时红黑树高度过高，查询时多次遍历导致的磁盘IO，查询速度不是很理想。

因此mysql不使用红黑树。

1.3Hash索引

1. 对索引的key进行一次hash计算就可以定位出数据存储的位置
2. 很多时候Hash索引要比B+ 树索引更高效
3. 仅能满足 “=”，“IN”，不支持范围查询
4. hash冲突问题

结论：hash索引的结构难以做到范围查询，因此实际中使用的少，适合等值查询。

1.4B-TREE

B树分为B-tree和B+tree。

B-tree：

1. 叶节点具有相同的深度，叶节点的指针为空
2. 所有索引元素不重复
3. 节点中的数据索引从左到右递增排列
4. 节点中的索引中带有数据data。

B+tree

1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2. 叶子节点包含所有索引字段,包含有完成的数据
3. 叶子节点用指针连接，提高区间访问的性能

结论：

1. B+tree叶子节点存放data数据，非叶子节点为叶子节点的冗余，这样就使得非叶子节点能够存放更多的索引信息，因此B+tree的索引数量要远远大于B-tree。（一个节点使用的page为16k，加入data数据为1k，那么B-tree一个节点只能存放16条左右的数据，而B+tree以聚集索引为例子，非叶子节点只用存放索引的key和指针指向，使用空间远远小于B-tree。）
2. mysql的B+tree每个节点之间都有指针互相指向，因此在进行如范围查询，可以直接通过指针达到目标节点，而B-tree节点没有指针指向其他节点，因此范围效率低于B+tree。

二、聚集和非聚集索引：

2.1 非聚集索引：myisam

索引⽂件的数据域存储指向数据⽂件的指针。辅助索引（非主键索引）与主索引基本一致，但是辅索引不⽤保证唯一性。

索引文件和数据文件是分离的(非聚集)，myisam包含三个文件，为索引⽂件（.myd）、表结构⽂件(.fra)、数据⽂件(.myd)。

非聚集索引的B+tree中的叶子节点存储的存储的data为磁盘文件地址，然后去到数据文件（.myd）中去获取具体的数据。

2.2聚集索引 InoDB

1. 表数据文件本身就是按B+Tree组织的一个索引结构文件
2. 聚集索引-叶节点包含了完整的数据记录

问：为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

1、如果设置了主键，那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作的列为主键索引。如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。mysql的资源是宝贵的，不应该将这些操作交给mysql来自己处理。

2、如果使用非自增主键（如果身份证号或学号等）

因为B+tree是有序的，使用自增添加数据，不会因此page（页）的重构。

如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面

问：为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

1. 减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，不需要重新构建二级索引）
2. 非聚簇索引（普通索引）的叶子节点内容是主键的值，节约了空间。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）

三、联合索引

联合索引又叫复合索引。对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 。（ 索引最左前缀）

当最左侧字段是常量引用时，索引就十分有效。

联合索引

问：为什么要遵循最左前缀？

因为B+tree是有序的。排序规则是按照key的顺序建立的。以图联合索引为例子，当不使用name时，

可见age并不是按照顺序列的（28>22）。