mysql索引原理，二叉树、红黑树、Hash、为什么mysql选择B+tree数据结构？

索引是帮助mysql高效获取数据的排好序的数据结构

为什么使用索引，看下图（二叉树举例）？

select * from t where col2 = 89; 

1. 假如没有索引，上边这条语句需要6次磁盘IO才能找到这条记录。这张表现在数据不多，假如现在有2000w数据，最坏情况全表扫描，可想而知查询速度。
2. 假如Col2这一列加上索引（二叉树），每一个节点存的是key、value。col2=89这列，二叉树节点key对应89，value 是这条记录对应磁盘文件指针 0x77。根据二叉树特性，89大于根节点34，第一次IO查找到89对应磁盘文件指针 0x77 ；第二次IO根据0x77直接拿到这条记录完整数据。2次IO解决问题

mysql索引为什么使用B+tree ？

兄弟这边通过二叉树、红黑树、hash 数据结构对比来说明

二叉树：

二叉树的数据结构特点   右边子节点大于父节点，左边子节点小于父节点，如下图

对于特殊的自增索引，二叉树比较高，造成时间复杂度增加（如下图）。

红黑树——单边自增长，随着数据量的增大，树的高度不可控。假如查找叶子节点数据，需要多次IO操作

1. 每个结点或红或黑
2. 根结点是黑色
3. 空叶子结点是黑色
4. 左子树点小于根结点，右子树大于根节点

 

Hash表

1. 对索引列进行一次Hash计算，然后记录hash值与该条记录的磁盘文件指针对应关系
2. 不支持范围查找，例如   select * from t where t.id >6;

为神马使用   B+tree （多路平衡树）     高度可控

• 非叶子节点不存储data，只存储索引，可以放更多的索引
• 叶子节点不存储指针，叶子节点存储完整数据
• 顺序访问叶子节点，提高区间访问的性能
• 叶子节点从左至右依次递增    

 

MyISAM存储引擎（不支持事务）  索引实现

1. MyISAM 索引文件和数据文件是分离的（非聚集索引）
2. 如果各位大哥看过数据库的安装目录的话，可以知道数据存放于磁盘文件中，frm结尾的文件存储的是表结构；MYI结尾的文件存储索引（B+tree）；MYD文件存储所有数据
3. 索引文件叶子节点  data   存放的是该条记录在MYD文件中的  磁盘文件指针
4. 举例说明：select * from t where t.col1 = 49   首先去MYI文件中通过索引查找出49对应的磁盘文件指针（0x90），然后根据 0x90在MYD文件中找出该条记录的完整数据

InnoDB存储引擎（支持事务） 索引实现

1. InnoDB 索引文件和数据文件是一个文件（聚集索引）
2. 和MyISAM存储引擎的区别在于 叶子节点包含完整的数据记录（这条记录的所有列字段）——聚集索引，由上图可看出
3. InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引
   a、如果表定义了PK，则PK就是聚集索引；
   b、如果表没有定义PK，则第一个not NULL unique列是聚集索引；
   c、否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
4. InnoDB普通索引的叶子节点存储主键值。MyISAM的叶子节点存储的是磁盘文件指针（面试题）

 

 

为什么InnoDB表必须有主键，并且推荐使用整型的自增序列？

防止页分裂，页合并。产生多次IO操作，影响性能

为什么非主键索引结构叶子节点存储的是主键值？

1. 保证一致性，更新数据的时候只需要更新主键索引树
2. 节省存储空间

 

索引分类

1. 主键索引：主键是一种唯一性索引，但必须指定为PRIMARY KEY，每个表只能有一个主键
2. 唯一索引：索引列的值只能出现一次且唯一，值可为空
3. 普通索引：值可为空，没有唯一性限值（覆盖索引）
4. 全文索引：MYISAM引擎支持，InnoDB5.6以后版本支持
5. 组合索引：多列组合为一个索引，专门用于组合搜索（最左匹配原则）

索引是不是越多越好？

索引在插入新的值的时候，为了维护索引的有序性，必须要维护，在维护索引的时候分以下几种情况：

1. 如果插入一个比较大的值，直接插入即可，几乎没有成本
2. 如果插入的是中间的某一个值，需要逻辑上移动后续的元素，空出位置
3. 如果需要插入的数据页满了，就需要单独申请一个新的数据页，然后移动部分数据过去，叫做页分裂，此时性能会受影响同时空间使用率下降，除了页分裂之外还有存在页合并（IO操作影响性能）