MySql-索引和数据存储结构筑基

索引的本质

索引是帮助MySql高效获取数据的排好序的数据结构
从这句话可以看出，索引本质是排好序的数据结构，且能高效的查询。那么是怎样的排好序的结构，又是怎样高效呢？

索引数据结构分类

• 二叉树
• 红黑树
• Hash表
• B-Tree

众所周知，mysql采用的是B+树。这就会引申一些问题。（1）什么是B-Tree，（2）B+Tree和B-Tree的区别，（3）为什么采用B+Tree，而不采用其他的。

B-Tree

是一种多路自平衡搜索树，它类似普通的二叉树，但是B树允许每个节点有更多的子节点。
特点：

• 叶子节点具有相同的深度，叶子节点的指针为空
• 所有索引元素都不重复
• 节点中的数据索引从左到右递增排列
  
  注：因为数据是存放在叶子叶子节点中的，所以每一页能存放的索引是不定的，这也是mysql不是使用他的原因之一。
  原因二：叶子节点相互之间不连接，无法直接通过最下层叶子节点顺序查找。

B+Tree

她是B-Tree的的一个变种。
特点

• 非叶子节点不存储data，只存储索引（冗余），便于存放更多的索引，同时保持非叶子节点的节点页的大小。
• 叶子节点包含所有索引字段
• 叶子节点用指针链接，提高区间访问的性能。
  

Hash

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

引擎

MyISAM

MyISAM索引文件和数据文件是分离的（非聚集）

InnoDB

innoDB索引实现（聚集）

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶子节点包含了完整的数据记录
• 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
• 为什么非主键索引结构叶子节点存储的是主键值（一致性和节省存储空间）
  

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
1、如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。

2、如果表使用自增主键
那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页

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

为什么非主键索引结构叶子结点存储的是主键值？
减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要修改聚簇索引就可以了，不需要重新构建二级索引）
聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。
非聚簇索引（普通索引）的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）

引用自：https://blog.csdn.net/weixin_41699562/article/details/104139458

联合索引底层存储结构

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