深入理解Mysql索引底层数据结构

索引

定义

索引是数据库管理系统中一个排好序的数据结构；是帮助数据库管理系统高效查询、更新数据库表中的数据。

索引也是一种特殊文件（mysql的InnoDB引擎下的表，索引是表空间的一个组成部分），它包含了数据库表里所有记录的指针

优点

1. 索引可以加快数据的检索，这也是创建索引的主要原因。

2. 使用索引，可以在查询过程中，使用优化隐藏器，提高系统的性能。

索引的分类

聚集索引（聚簇索引）：将数据存储与索引放在了一起，找到索引，也就找到了数据。

非聚集索引（非聚簇索引）：数据与索引分开存储，索引的叶子节点指向了数据对应的地址。

索引数据结构

注：此章节须有数据结构基础，此处讨论的数据结构的存在问题，只是针对数据库数据存储来讨论的，非数据结构本身缺陷。

附：

1.数据结构图动态操作链接 Data Structure Visualization

二叉树

特点：左子树小于右子树，数据成有序状态。有利于数据查询。

存在的问题：当数据成递增状态时，会形成斜二叉树，如下图，此时的二叉树和单链表结构的数据没有什么区别，查找数据时，会逐一扫描对比。

红黑树

特点：红黑树是一种特殊的平衡二叉树，具有二叉树的特点，不通的时，数据进行增删的时候，会进行动态平衡，确保没有一条路径会比其他路径长出2倍。

存在的问题：索引成递增状态时，数据进行增删，需要不停动态平衡索引树，耗费性能；数据量大时，树的高度也会特别高，对查询效率也会有所影响。

Hash 表

特点：

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

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

存在的问题：仅能满足 “=”，“IN”，不支持范围查询 hash冲突问题；

B-Tree

特点：

叶节点具有相同的深度；

叶节点的指针为空 所有索引元素不重复 ；

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

存在的问题：

1. 每个节点都包含了data，占用内存资源，导致每页能存储的索引数据减少；
2.叶子节点没有指针相连接，范围查询时，查询数据效率受到一定影响。

B+Tree(B-Tree变种)

特点：

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

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

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

存在的问题：索引冗余。

Mysql不同引擎下索引数据结构

InnoDB的索引

1.索引数据（一级索引和二级索引）存储在XXX.ibd文件中；

2. 一张表中最多有一个主键索引，主键索引是一个聚集索引，叶子数据存了改记录的所有数据；

可以有多个二级索引；

3.二级索引的叶子节点数据存储的是表主键；

4.通过二级索引查询到数据后，如果所查字段全部命中索引，则不需要回表查询。否则需要回表查询；

主键索引：数据列不允许重复，不能为空，一个表有且只有一个主键索引。

二级索引：一张表可以有多个二级索引。

MyISAM的索引

1.索引数据存储在XXX.MYI文件中,数据存储在XXX.MYD文件内中；

2.主键索引中叶子节点存储的是该主键对应行记录的磁盘地址。

3.通过索引查到数据后，需要加载.MYD文件，加载行数据。

Mysql索引面试问题

1.为什么mysql页文件默认16K？

查看mysql文件页大小（16K）：SHOW GLOBAL STATUS like 'Innodb_page_size’;

假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针) 那么一颗高度为2的B+树能存储的数据为：1170*16=18720条，一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级条）

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

1.如果InnoDB表没有创建主键，引擎会选取一个没有重复值的字段作为主键索引，如果没有找到合适字段，引擎会创建一个隐藏的字段作为主键索引，这样都会占用mysql的资源。

2. 建议用自增主键的原因是，Mysql创建主键索引树时，需要排序；如果新增数据，索引值只进行比较，不会造成分裂；如果主键不是自增的，则在新增时，可能会造成分裂，则一定程度上耗费了性能；

3.推荐使用int作为主键，主要是int方便排序；同时int的大小要比字符串小，一定程度上节约了磁盘空间。

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

1.非主键索引叶子节点存储主键，不是行记录数据，可以节约磁盘空间。

2.为了一致性，当数据更新时，更新主键索引叶子节点的记录数据。