索引设计与优化-读书笔记-1
聚簇索引
新插入的表行所在的表页的索引是聚簇索引。
若索引行的顺序和表行的索引具有强关联性可说这个索引是聚集的。但是不一定是聚簇索引。
一个表只允许有一个聚簇索引。在某个特定时间可能会有多个索引是聚集的。
索引页和表页
表和索引行都被存储在页中。页的大小一般为4/8 KB. 缓冲池和IO的活动都是基于页的。
页的大小决定了一页可以存储多少个索引行，表行。
索引页和表页都是在页内存储数据，差异是一个存储的是data数据，一个存储的是索引数据。若索引页存储的是非聚簇索引，那么连续的索引页指向的是非连续的data数据页。此时全索引扫描是顺序IO，对指向的数据的扫描是随机Io.
聚簇索引页是连续的所指向的data数据页也是连续的。通过聚簇索引扫描表行是顺序IO.
缓冲区
内存缓存区，通常非常大，可以存储成千上万的页。MySQL缓冲区的目的是将常用的数据缓存起来避免频繁的磁盘IO带来的性能损耗。每一个DBMS会根据对象类型(表和索引)及页的情况拥有多个缓冲区。
磁盘IO
可以分为顺序IO和随机IO
顺序IO: 指读写操作的访问地址连续。在顺序IO访问中，HDD所需的磁道搜索时间显着减少，因为读/写磁头可以以最小的移动访问下一个块。数据备份和日志记录等业务是顺序IO业务。
随机IO:指读写操作时间连续，但访问地址不连续，随机分布在磁盘的地址空间中。
mysql在处理IO的过程中通常都会伴随着预读取，局部预读原理告诉我们，当计算机访问一个地址的数据时候，与他相邻的地址的数据也有较大几率访问到所以一次io会把相邻页的数据也加载到缓存区当中去。
随机IO耗时估算
对一页或者多个连续页一次数据读取我们认为是一次IO. 一次随机IO的耗时大概是10ms。
每次读取数据的时间大致可分为
- 排队等待时间：可能发生的排队时间
- 寻道时间： 是指磁盘 磁臂震动到指定磁道所需要的时间，一般在5ms内
- 半圈旋转：找到指定磁道后还需要旋转到达目标页数据。
- 数据传输耗时: ，将数据从磁盘传输到数据库缓冲区的时间，耗时相对较小 一般在1ms内。
其中寻道时间和旋转时间称为服务时间。同数据库缓冲区一样，磁盘也有缓冲区，若数据存在于磁盘缓冲区，寻道时间和旋转时间均可省略，IO时间将会降低在1ms左右。
顺序IO耗时估算
以下都是顺序IO
- 全表扫描：一般是按顺序读取数据页
- 全索引扫描： 按顺序扫描存储索引数据的页。但是索引页存储的数据地址指针，指向的页可能不连续，索引后对应的数据扫描是随机IO
- 索引片扫描：同2
- 通过聚簇索引扫描表行： 聚簇索引后直接指向数据，且聚簇索引的增长一般是连续的，聚簇索引所指向的地址页也是连续的，是顺序扫描。
一般DBMS会知道哪些索引和表页需要被顺序的读取，且能识别出不在缓冲区的页，然后发出多页的一次IO请求。对于平均4K的表页来说在40MB/s的读取速度下，顺序IO的耗时可能为0.1ms.
且通常伴随着预读，在需要所需数据前将一部分数据读取到缓冲区当中去。
增加索引的代价
响应耗时分析
前提：
假设在一个索引上添加一行需要耗时10ms当前情况不考虑异步写 那么索引的新增需要找到对应的索引页的插入位置，对于非聚簇索引这个位置通常不是最后一个索引页末尾，寻找对于的索引页是一个随机IO过程，可以认为是估算值10ms
问题：
- 在一个事务中向一张有10条索引的表中插入1行数据。
- 在一个事务中向一张有10条索引的表中插入20行数据。
分析以上情况下随机IO次数：
- 10条索引包含9非聚簇索引和1聚簇索引。需要10次随机读。顺序读的第一次也是随机读，需要寻道时间 旋转才能在后续开启顺序读。
- 9个非聚簇索引需要9*20=180次随机读。聚簇第一次随机后续都是顺序读。以供需要181次随机读。
磁盘负载分析
被修改的叶子页最终都会落到磁盘上去，由于数据库的写是异步的，所以写不会影响事务时间，但是写会增加磁盘的负载，如果一张表的插入较高的话，磁盘负载可能会变成限制索引数量的主要问题。
磁盘空间限制
如果一个表中有千万行以上的数据，索引磁盘空间的成本可能会成为一个限制因素，每一次数据的写入都需要增加对应索引的空间。
