当前位置:   article > 正文

MySQL索引优化原理_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 = l o g m + 1 N h=log_{m+1}N h=logm+1N
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次,大大减少了回表操作。

本文内容由网友自发贡献,转载请注明出处:https://www.wpsshop.cn/w/小惠珠哦/article/detail/1002249
推荐阅读
相关标签
  

闽ICP备14008679号