1.Mysql索引的原理

1.Mysql索引的原理

1.定义

索引：是帮助MySQL高效获取数据的排好序的数据结构；简称排好序的数据结构。

索引的作用是快速获取目标数据。

2.索引数据结构有：

​ 1）二叉树、2）红黑树、3）B-Tree、4）Hash表

​ 数据结构解释：

​ **二叉树：**树形结构的一个重要类型。二叉树的特点是每个节点最多只能有两个子节点，左边的节点小于右边的节点。

​ 红黑树： 一种平衡二叉查找树的变体，它的左右子树高差有可能大于 1，所以红黑树不是严格意义上的平衡二叉树（AVL），但 对之进行平衡的代价较低， 其平均统计性能要强于 AVL ；

B-Tree: 一种平衡的多叉树 ；特点每一层都是从左到右递增。

为什么没有用二叉树作为mysql的索引数据结构？

二叉树结构是：一个主节点：左（小）右（大）两个子节点；例如一个有序增长的列用二叉树存储，这个二叉树只有右节点没有左节点，查找时从根节点开始，一个一个的去比较，直到目标值为止，所以这种数据结构不适合当索引的数据结构。

红黑树：简称会自动平衡的二叉树，同样也是存储大量有序增长的列时，对查询速度提升不明显。

B-Tree:

1)叶节点具有相同的深度，叶节点的指针为空；

2）所有的索引元素不重复；

3）节点中的数据索引从左到右递增排列；

从以上几种数据结构来说，B-Tree更适合索引结构，所以Mysql的索引存储结构是在B-Tree数据结构上做了一些改动的。

B+Tree(B-Tree 变种)：

1）非子节点不存data,只存储索引(冗余)，可以放更多的索引；

2）叶子节点包含所有索引字段；

3）叶子节点用指针连接，提高区间访问的性能；

B+Tree和B-Tree有什么不同 ？ B+Tree非子节没有数据而B-Tree有数据， B+Tree子节点之间双向指向，B-Tree没有。

数据量估算

​ MySQL自己也有逻辑页，大小一般是操作系统中 页 的整倍数，逻辑页的大小可以通过配置进行修改，但是不建议，MySQL经过大量测试，确定了一个相对合适的大小为16KB;

可以通过SQL去查询：

show global status like 'Innodb_page_size'

假如用B+Tree存储一个主键索引，索引值类型为bigint 占8B,挨边的指针占6B，那么这一页（第一层）可以存放16 * 1024/(8+6)=1170，同理第二层也可以存放1170，第三层叶子节点，包含主键值和表的一行数据，假设数据大小为1KB，那么一页能放16个；用B+Tree结构存储一张表的索引假设是三层那么这个索引树能放1170 * 1170 * 16=21902400行数据。

索引查询的过程：

先从磁盘加载第一层索引数据到内存（一次IO）和目标索引值对比，找目标值或相应的范围，在加载底层索引值到内存（有一次IO）,找到目标索引值，在根据目标索引值加载第三层的数据（第三次IO）。

非聚集索引和聚集索引的区别：

索引文件和数据是否在一个文件中，MyISAM是分开的，InnoDB是的是一个文件。

Hash:

1）对索引key进行一次hash计算就可以定位出数据存储的位置

2）很多时候Hash索引要比B+树索引更高效

3）仅能够满足“=”，“IN”,不支持范围查询（因为hash值不是按顺序存储的）

4）hash 冲突问题 ：hash值一样的

InnoDB索引实现（聚集）

1）表数据文件本身就是按B+Tree组织的一个索引结构文件

2）聚集索引-叶节点包含了完整的数据记录

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

答案：数据表如果没有主键，它会找一列不重复的数据作为主键，方便存储，如果找不到会创建一个隐藏列作为表的主键；索引以B+树的结构存储，递增存储方便快，若不是递增需要查找索引应该放的位置，可能还需要分裂，因为是从左到右递增排列，整数大小比较快。

4）为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

二级索引查询的顺序是：先去二级索引找到数据主键索引，在去主键索引中查找数据。

主键存储结构

二级索引存储结构：

联合索引的底层存储结构长什么样子？

从联合索引存储结构上可以看出，联合所以为什么有左列原则。如果不从最左边的列去查，就是不一个有序的结构。