MySQL之B+索引结构

磁盘数据页的存储结构

假设没有任何索引，数据库如何根据查询语句搜索数据

全表扫描，针对主键，在每个数据页使用二分查找，因为数据行是按主键有序排列的。 针对普通列，只能遍历链表。

不断插入，引起页分裂

索引运作的一个核心基础就是要求后一个数据页的主键值都大于前面一个数据页的主键值。所以如果后一页不大于就会将主键较大值挪动到新数据页，新插入的挪动到上一个数据页里去，这叫页分裂。

基于主键的索引是如何设计的，如何根据主键索引查询

把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录。二分查找可以找到主键对应的页。

之后根据数据页号找到对应的磁盘文件，就可以读取到该数据页了。

因为数据页很多，主键目录可能很大，于是就将主键目录存放在索引页中。

于是将每个索引页的最小主键再次建立"索引"，如下

这就是B+树。

其实整个索引页加数据页看起来就是一颗B+树，数据页就是叶子节点，我们称这颗B+数索引为聚簇索引。所以，在增删数据时，一方面会更新数据页，另一方面会维护B+树结构的聚簇索引。

二级索引如何运作

二级索引也是B+树，独立于聚簇索引之外，只不过数据页只放二级索引字段和主键。

针对select * from table where name='XX'这样的语句，现根据name字段值在name字段的索引B+树里找，找到叶子节点页仅仅可以找到对应的主键值，还需要“回表”，就是根据主键去聚簇索引根节点开始，一路找到叶子节点，定位到主键对应的完整数据行，才能把select *要的全部字段值拿出来。

插入数据时，索引的维护过程

开始建表时，数据页时空的。然后插入数据，直接在这个数据页插入就可以，没弄什么索引页，这个初始数据页就是一个根页，数据页内部默认有一个基于主键的页目录，此时根据主键来搜索都是ok没问题的，直接在唯一一个数据页里根据页目录找就行了。然后表数据越来越多，新搞了数据页，此时就会独立出一个索引页，随着数据页越来越多，索引页也越来越多，不断抽象为了B+树结构。

对于二级索引name，索引页除了存放页号和最小name字段值以外，每个索引页里还会存放那个最小name字段值对应的主键值。因为对于二级索引来说，不同数据页号的最小name值一样(因为name是可重复)，所以还需要根据主键判断一下应该插入到哪个数据页去。

索引是不是越多越好

索引会占用磁盘，每一课B+树，都要占用磁盘空间。增删改数据的时候，需要不断地维护索引页。

线上数据库不确定性的性能抖动优化案例

可能导致性能抖动的两个原因:

• 执行一个查询语句，需要查询大量的数据到缓存页里去，此时就可能导致内存里大量的脏页需要淘汰出去刷入磁盘上，才能腾出足够的内存空间来执行这条查询语句。所以语句可能出现延迟，因为要等待大量脏页flush到磁盘，然后语句才能执行!
• redo log的日志文件在全部被写满的情况下，也会触发一次脏页的刷新。为什么呢？因为假设第一个日志文件的一些redo log对应的内存里的缓存页的数据没被刷新到磁盘缓存页中，如果mysql崩溃了，也没有 redo log可以重做，所以redo log是非常重要的。

优化方法:

• 使用fio工具测试磁盘最大随机IO速率的，查出来之后，将它赋值给innodb_io_capacity这个参数，尽可能让数据库用最大速率去flush缓存页到磁盘。
• 将innodb_flush_neighbors参数设置为0，禁止刷临近缓存页，这样就把每次刷新的缓存页数量降低到最少。