MySQL索引原理

一．索引（数据表中每一条数据都是存在磁盘中，所以每一次查找都会与磁盘进行IO）：

索引的优点：

第一， 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二， 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三， 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四， 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五， 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

索引的缺点：

第一，   创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，   索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，   当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

Hash索引的弊端：1.出现hash碰撞问题 2.若出现范围查找如id>4则用Hash索引无法操作
Hash碰撞：两个不同的原始值在经过哈希运算后得到同样的结果，
这样就是哈希碰撞。

BTREE：1.节点可以存储多个值（根据“度”（节点的数据存储个数，度越大）来决定）

B+TREE:

与BTREE的区别：非叶子节点不存data，只存key，可以增大“度”，
不用红黑树的原因：1.一个节点只能存储一个值 2.数据量大时树的高度就会很高

InnoDB存储引擎索引实现（聚集）

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

主键（Primary Key）索引：B+TREE树的叶子节点中除了包含索引值以外还包含了对应的所有数据

非主键索引：B+TREE树的叶子节点中除了包含索引值以外只包含了主键值，所以该查找过程是先通过非主键索引找出对应的主键，然后根据根据该主键通过主键索引去查找出对应的行数据；所以主键索引的查找速率至少是非主键索引的两倍

基于InnoDB存储引擎的数据表在磁盘中的存储文件如下：

MyISAM存储引擎索引实现（非聚集）

1.MyISAM索引文件和数据文件是分离的

主键（Primary Key）索引：B+TREE树的叶子节点中除了包含索引值以外还包含了对应的存储地址，因为索引文件和数据文件是分离的所以查找的过程是首先通过索引文件（.MYI后缀）查找出对应的地址（文件行的指针）然后到数据文件（.MYD）进行定位从而得到对应的数据

非主键索引：和主键索引原理一致；

基于MyISAM存储引擎的数据表在磁盘中的存储文件如下：

联合索引：
存储结构图：

在B+Tree中的排序规则是先比较第一个字段的大小，若相等则比较第二个字段（字符串是通过ASCII码值比较），以此类推；联合索引中叶子节点除了包含索引值还包含对应所有的行数据；

红黑树：
性质1. 节点是红色或黑色。
性质2. 根节点是黑色。
性质3 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)
性质4. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。