MySQL阅读笔记——4.B+树索引
在MySQL中索引的数据结构都采用B+树结构，在InnoDB中则是根据主键作为聚集索引将数据直接存储为B+树结构（索引即数据），MyISAM中则是将数据和索引分开存储。
B+树是一颗多叉树，可将降低访问磁盘的次数，B+树数据存放在叶子节点并且通过双向链表关联，实现了数据库的范围查询
B树是一个多叉树，每个节点都会存放数据（叶子节点不是完整数据）；B+树叶子节点是有序完整数据（通过链表连接），内节点是每个下层节点的索引；B*树内节点也通过链表连接
主流的存储引擎有三种数据结构：B+树、Hash、LSM tree，MySQL中索引实现了 B+树 和 Hash （但是基本用 B+树，因为 范围查询 性能更好），Hbase，Cassandra，Leveldb，RocksD使用了 LSM tree
4.1 创建索引语句
# 可以在建表时指定
CTEATE TABLE 表名(
列信息...，
PRIMARY KEY 主键列名,
# key和index两者同义
[KEY|INDEX] 索引名(列名....)
);
# 建表以后指定
ALTER TABLE 表名 ADD [KEY|INDEX] 索引名(列名....);
MySQL建表后必然会有主见，即使不指定也会按照一定规则去建立主键。规则是：如果字段有Unique约束则指定为主键，如果没有则使用表中隐藏的唯一列作为主键。
4.2 InnoDB中的B+树索引
聚集索引的叶子节点是真实数据记录，而非聚集索引叶子节点是数据主键。如果索引没有覆盖查询结果则需要通过主键再 回表 查询聚集索引，最终确定数据。B+树的高度一般3~4层，如果太高的话则会频繁IO降低查询效率
使用记录主键值的大小进行记录和页的排序，B+树的叶子节点存储的是完整的用户记录。满足这个条件的索引叫做聚集索引，一个InnoDB表有且只有一个聚集索引，因为InnoDB表的数据存储结构就是聚集索引。B+树本质上就是一个大的多层级目录。
聚集索引建立过程（InnoDB引擎建表默认创建）：
-
建表后创建 根节点 页面，并在数据字典中记录
-
插入记录先记录到 根节点 页面
-
根节点 空间用完，继续插入记录则将根节点所有信息复制到新 页面a 然后对这个新页面进行 页分裂 得到 页面b ,根节点记录每个新页面中的最小主键以及页号（以及其他额外信息）
-
随着插入数据增加，页分裂过程类似
根节点创建后便不会移动，查询时用到索引时候，会先找到 根节点 之后遍历B+树查询数据。
非聚集索引（二级索引）建立过程：
和聚集索引建立过程相同，但是可以建表后再创建索引。非聚集索引的叶子节点数据是主键，建立的过程就是生成一颗B+树的过程。唯一索引 也属于二级索引的一种，插入/删除 唯一索引 过程是立刻执行，插入/删除 普通二级索引 过程先在change buffer中执行异步刷盘
MySQL规定一个数据页至少存储两条记录，因为如果一个叶子页面只存储一条记录（说明表的列信息很大）很快就会把上一层父节点占满，随之存储数据量的增多，B+树的高度会越来越高。这样每次查询数据会产生很多的IO降低效率。因为MySQL数据存储在磁盘按需加载，每次IO会加载一层页面到内存，因此要尽可能减少树的高度，故采用B+树。
B+树相比B树，每一层都采用了双向链表，范围查询更快。B树每个节点会存储用户数据占用空间更大。
4.3 MyISAM中B+树索引
MyISAM中将记录直接按照插入顺序存储在一个 数据文件 中，通过行号定位数据。并且将索引单独存储到一个 索引文件 但是在该文件中存储的是 主键+行号 （聚集索引）。
InnoDB根据聚集索引一次就能查到最终记录，而MyISAM差找聚集索引后也需要根据行号到数据文件中做一次 回表 。也就是MyISAM的索引全部相当于 二级索引 。 MyISAM的行格式如果采取变长模式，则 索引文件 中会存储地址偏移量。
4.4 唯一二级索引和普通二级索引区别
-
查询过程：普通索引 查找到第一个之后会再查找下一个记录，直到不满足检索条件条件，唯一索引 有了唯一约束查找到一条满足条件记录后停止检索
-
更新过程（增删改）：普通索引 更新操作先将操作记录在 change buffer 中，而 唯一索引 在插入过程中需要判断唯一约束，所以需要将对应的页面加入奥内存，故可以直接在内存中更修记录，用不到 change buffer
更新数据页面时，如果数据页在内存则直接更新；如果不在内存则将更新操作缓存到 change buffer 中，下次访问页面时将 change buffer 中操作应用到数据页得到新结果的过程叫 merge，只有 普通索引 可以使用 change buffer
数据页读入内存先读入到 buffer pool 区域，change buffer 也是 buffer pool 区域内容，通过
innodb_change_buffer_max_size设置 change buffer 在 buffer pool 的最大占比尽量选择普通索引，唯一索引用不到change buffer，change buffer 减少将数据页加入内存的随机IO，在一个页面 merge 之前 change buffer 记录的变更越多，收益越大，适用于写多读少的业务，如：历史记录等。
