mysql--索引

尚硅谷

索引

索引是一种排好序的快速查找数据结构。

• 降低数据库的IO成本
• 创建唯一索引保证数据库表中的数据的唯一性
• 加速表之间的连接，对于字表和父表联合查询
• 减少查询中分组和排序的时间
• 缺点：创建维护索引费时，降低更新表的速度，存储需要占据磁盘空间。

CREAT TABLE index_demo(c1 INT, c2 INT, c3 CHAR(1), PRIMARYKEY(c1) );

record_type	next_record	c1 c2 c3	其他

record_type:0:普通记录 用户自定义以及innoDB自己添加的隐藏列 1 目录项记录 只有主键值和编号两个列 2 最小记录 3 最大记录
next_record:表示下一条地址相对于本条记录的地址偏移量
c1 c2 c3: 列值
其他： 其他信息 包括隐藏列的值以及记录的额外信息

B+ Tree

B+树的节点可以分为好多层，规定最低一层为第0层。 通常不会超过4层，存放亿级记录

常见索引

按照物理实现方式，分为聚簇索引，非聚簇索引（二级，辅助）索引

• 聚簇索引是一种数据存储方式，所有的用户记录存储在了叶子节点索引即数据：页内的记录是按照主键的大小顺序排成一个单向链表，用户记录的页也是根据主键大小顺序排成一个双向链表。一个表只能有一个聚簇索引

1. 叶子节点存储完整的用户记录，即所有列值以及隐藏列。
2. 数据访问更快，而且对于主键的排序查找和范围查找快，数据紧密相连，节省IO操作。
3. 插入速度依赖于插入顺序而且更新主键的代价比较高。

• 非聚簇索引使用非主键列：

1. 叶子节点存储的不是完整的用户记录，只是非主键列（c2列）+主键的的列值
2. 回表操作：根据c2列大小排序的B+树只能确定要查找记录的主键值，需要回表到聚簇索引再查一遍，即需要2颗B+树。一张表可由有多个非聚簇索引。
3. 联合索引：可以同时以多个列作为排序顺序（c2 c3），其本质也是二级索引

注意事项

• 根页面位置不同

1. 每当为表建立一个B+树索引时候，都会创建一个根节点页面。最开始表中没有数据时候，B+数组索引对应的根节点既没有用户记录也没有目录项记录
2. 随后向表中插入用户记录，先把用户记录存储到这个根节点中，当根节点中可用空间用完时 此时根据节点中所有记录复制到一个新分配的页，然后对其页分裂得到新页，这时新插入的记录根据键值大小分配到其中，根节点升级为存储目录项记录的页。
3. 一个B+树索引根节点产生后便不再移动。只要建立索引 根节点便会记录到某个地方，需要到索引时候就会取出根节点的页号从而访问这个索引。

• 内节点目录项唯一

1. 索引节点中目录项记录的内容是三部分构成：索引列+主键+页号。这样保证每一层中是唯一的。

• 一个页面至少存储2条记录

MySIAM索引的原理

索引和数据分开存储

1. 表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件，不划分为若干数据页，插入数据时候没有刻意按照主键大小排序，所以不能在这些数据上使用二分查找
2. 存储引擎会把索引信息另外存储到一个索引文件，单独为表的主键创建一个索引，叶子节点存储的不是完整的用户记录，而是主键值+数据记录地址
3. 建立的相当于二级索引，需要回表操作，但是由于是地址偏移量直接到文件中取数据 ，十分快速

hash

1. hash索引仅能满足等值查询，进行范围查询时间复杂度会退化，数据的存储没有顺序，order by 需要重新排序。
2. 联合索引hash值合并后一起算，无法对单独的一个键索引进行查询。如果索引重复值很多，会降低效率。
3. 在键值型数据库中 Redis核心存储是hash表 ； Memory存储引擎支持hash存储，innoDB本身不支持hash索引，但提供自试用hash 索引，如果某个数据经常被访问，当满足一定提哦啊见时候就会放在hash表中

B树 -- B+树

1. B+ 树查询效率更稳定：因为B+树每次只有访问到叶子节点才能找到对应的数据，B树非叶子节点也会存储数据，造成查询效率不稳定
2. B+ 树查询效率高：更矮胖，查询所需要的磁盘IO也会更少，同样的磁盘页大小 可以存储更多的节点关键字
3. B+ 树查询范围上效率高，所有关键字均在叶子节点中，数据递增可以通过指针连接查找。

B+树

1. InnoDB存储引擎中页的大小16kb，一个深度为3 的索引可以维护10亿条记录（嘉定一个数据存储1000条行记录）。实际情况每个节点可能填不满，因此一般高度在2-4层，设计是讲根节点常驻内存，查找某一键值的行记录最多只需要1-3次磁盘IO操作。