MySQL索引优化原理_mysql索引优化器原理

一、服务接口性能测试相关

TP指标：
如TP50：指在一个时间段内（如5分钟），统计该方法每次调用所消耗的时间，并将这些时间从小到大的顺序进行排序，取第50%的那个值作为TP50值；配置此监控指标对应的报警阈值后，需要保证在这个时间段内该方法所有调用的消耗时间至少有50%的值要小于此阈值，否则系统会报警。
响应时间的其他指标：最大值、 平均值等

QPS概念：
定义：单个进程每秒请求服务器的成功次数

TP指标和QPS的关系：
TP指标反映的服务器接口的耗时，QPS反映的是单个进程每秒请求服务器的成功次数。如果服务器接口的耗时降低，服务器能够支撑的QPS自然就会上升。同样，如果服务器能够支撑的QPS下降了，说明耗时在增加，TP指标在变差。

在开发完服务接口，可利用jmeter压测工具测试服务接口的TP指标，分析服务的响应耗时。

二、mysql索引查询优化原理

2.1 索引原理

数据保存在磁盘上，为了提高性能，可以把部分数据读入内存来计算，因为访问磁盘的成本大概是访问内存的10万倍左右。

传统的机械磁盘读取数据靠机械运动，每次读取所花时间可分为寻道、旋转延迟、传输3部分，寻道时间指磁臂移到指定磁道所需时间，主流磁盘一般不高于5ms；旋转延迟即磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，即1秒能转120次，旋转延迟就是1/120/2=4.17ms；传输时间指将数据写入或读出磁盘的时间，一般在零点几毫秒。综上，一次磁盘IO的时间约9ms，听起来还好，但是一台500MIOS的机器每秒可执行5亿条指令，因为指令依靠的是电的性质，即执行一次IO的时间可以执行约450万条指令，数据库数据量动辄百万乃至千万级，每次9ms，显然是个灾难。

考虑到磁盘IO操作代价高昂，OS做了一些优化，当一次IO时，不只将当前磁盘地址的数据，还将相邻的数据也都读取到内存缓冲区，由局部预读性原理可知，当计算机访问一个地址的数据时，与其相邻的数据也会很快被访问到，每次IO读取的数据我们称之为一页（page）。具体一页有多大跟OS有关，一般为4k或8k，即每读取一页，发生一次IO。

由上可知，需要在每次查找数据时，将磁盘IO次数控制在一个很小的数量级，最好是常量级。一个高度可控的多路搜索树可以满足这样的需求。

上图为一颗b+树，浅蓝色的块是一个磁盘块，每个磁盘块包含几个数据项，用深蓝色表示，和指针，用黄色表示。如磁盘块1包含数据项17和35，包含指针P1、P2和P3，P1表示小于17的磁盘块，P2表示17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即图中的最下面一层。非叶子节点不存在真实数据，只存指引搜索方向的数据项，如17、35并不真实存在于数据表。

例如，如果要查找数据项29，首先会将磁盘块1加载到内存（一次IO）；在内存中用二分查找确定29在17和35之间，锁定指针P2，通过P2的磁盘地址将磁盘块3加载到内存（二次IO）；29在26和30之间，锁定磁盘块3的指针P2，通过P2加载磁盘块8到内存（三次IO），在内存中二分查找找到29，查询结束。真实情况是，3层的b+树可表示上百万条数据，如果上百万的数据只需三次IO，性能将是巨大的提升，如果没有索引，每个数据项都发生一次IO，那么总共需要百万次IO，显然成本太高。

通过上面的分析，可知IO次数取决于b+树的高度h。假设当前数据表的数据为N，每个磁盘块的数据项的数量为m，则有:
$$h=lo{g}_{m+1}N$$
N固定，m越大，h越小。而m=磁盘块size/数据项size，磁盘块大小即一个数据页的大小是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量小，这也是为什么b+树要求把真实的数据放到叶子节点。

当b+树的数据项是复合的数据结构（比如name,age,sex）时，b+树按照从左到右的顺序建立搜索树。比如当（张三，20，F）这样的数据来检索时，b+树会优先确定name，如果name相同再依次比较age和sex，最后得到检索的数据；但当（20，F）这样的数据来检索时，b+树就不知道下一步该查哪个节点，因为建立搜索树时name时第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里。比如当（张三，F）这样的数据来检索时，b+树可用name来指定搜索方向，但因下一字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了。这个性质称为索引的最左匹配特性。

2.2 主键索引和普通索引

每张表都有主键索引，且是根据主键顺序存放数据的，如果建表时未定义主键，则数据库会自己生成rowid当主键。主键索引又被称为聚簇索引。主键索引的b+树的叶子节点存储的是正行数据。

普通索引又称为二级索引或非主键索引，在b+树的叶子节点存储的是主键值。如下面的SQL语句：

select b from t where a=10
1

在执行时，数据库会在a的索引表中查询a='xx’对应到的主键值，再用这个主键到主键索引中查询需要的数据，这个过程叫做回表操作。

2.3 覆盖索引和索引下推

上面提到在执行sql时，会执行回表操作，回表操作多查询一次主键索引树，影响了效率。那有什么办法可以避免呢？答案就是建立一个联合索引INDEX index_a_b (a, b)，这样数据在查询时就直接拿到了需要的b字段的值，不需要再进行回表操作。例如查询主键sql也是不需要回表操作的，这个就是覆盖索引的概念了。

索引下推是MySQL5.6引入的功能，指的是可以在遍历的过程中就对包含的字段先做判断，直接过滤掉不符合条件的数据，减少回表操作。

例如下面这条sql语句：

select * from t where a>10 and a<20 and b='xx'
1

如果没有索引下推功能，这条语句的执行过程是这样的：

1. 判断a是否大于10且小于20
2. 如果步骤1不满足条件，则进行下一条记录。如果步骤1满足条件，则从a的索引树种取得对应的主键进行回表操作
3. 回表操作取得整行数据，取b的值判断是否等于’xx’，如果是，取出数据。重复步骤1和步骤2，直至a大于等于20终止

上面的执行过程中如果满足步骤1的数据有100条，但同时满足t.b='xx’的数据只有10条，数据库却需要回表100次。
引入索引下推后，执行过程如下：

1. 判断a是否大于10且小于20
2. 如果步骤1不满足条件，则进行下一条记录。如果步骤1满足条件，则继续判断b是否等于’‘xx’。如果不满足，则进行下一条记录，如果满足，则从a的索引树中取得对应的主键进行回表操作，取出数据
3. 重复步骤1和步骤2，直至a大于等于20终止

在引入索引下推后，整个过程只需要回表10次，大大减少了回表操作。