如何预估索引性能?
在《DB——数据的读取和存储方式》中,我们介绍了影响数据读取的方式,随机IO和顺序IO,在《Sql优化器究竟帮你做了哪些工作?》介绍了影响查询的一些基本因素,其中介绍了过滤因子、索引片的宽窄与大小以及匹配列和过滤列 是如何应用在sql查询中的。在《什么是三星索引》介绍了索引设计需要主要的因素以及推荐的设计方式。那么这篇,我们将综合运用这些知识,并通过两个方法,快速估算当前的索引的性能,以及查询的效率。从这篇文章你会得到以下知识:BQ—基本问题法QUBE—快速上限估算法
table:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(100) DEFAULT NULL,
`sex` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`c_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
# 索引
KEY `id_name_sex` (`id`,`user_name`,`sex`),
KEY `name_sex_age` (`user_name`,`sex`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
一、基本问题法—BQ
基本问题法,这个方法想必会用数据库的人,都会知道,简单来讲,就是当前select 是否用到了索引,不过BQ方式会更加详尽。
评估的方式:
现有的索引或者计划中的索引是否包含了 WHERE 中使用的全部列,也就是当前查询,是否有一个索引是半宽索引(匹配where后所有谓词列)? 如果没有,那么应该将缺少的谓词列加到现有索引上。
基于前面介绍的知识,我们可以知道,半宽索引可以确保回表访问只发生在所有查询条件都满足的时候。也就是说,条件过滤会在表扫描之前发生,而不是之后发生。虽然不能避免主表的随机访问,但起码可以支持索引表的顺序读取。
不过对于BQ并不能保证足够的性能。比如如下:
上述中where后面的谓词都包含在索引(name,sex,age),但这个查询却是一个全索引或者全表扫描,三星中一个都不占有。具体原因,可以查看《Sql优化器究竟帮你做了哪些工作?》中的介绍。
那么有没有更全面的评估方式?下面的QUBE,就是一种更全面快速的评估方式。
二、快速估算上限法——QUBE
QUBE比BQ更耗时,但它能揭示所有与索引或者表设计相关的性能问题。QUBE会对每个谓词所用的过滤因子都非常接近于实际的最差情况,如此虽然会比较悲观,但不会像BQ那样漏掉发现的某些问题。
QUBE的目的在于在开发期间就能将访问路径缓慢的问题暴露出来,这个估算方法的输出就是本地响应时间(Local Response Time),简称为LRT。
我们先大概了解一下LRT的时间组成。在一个常规的多用户环境中,程序的并发会导致对所需资源的各种竞争,因此不得不排队来获取这些资源。那么QUBE会忽略磁盘驱动排队以外的其他所有类型的排队,已提供一个简单的评估过程。
在排除以上因素之后,我们得到的一个是一个非常简单的估算过程,仅需要以下变量来处理即可。
最终的QUBE公式为:
LRT=TR * 10ms + TS * 0.01ms + F * 0.1ms
我们把随机访问一次的时间定义为10ms,以及把顺序读取一次的时间定义为0.01ms,这个在《DB——数据的读取和存储方式》有介绍,不了解的可以过去看一下。
三、示例
我们假设表test有1000w的数据,同时又如下两个索引,一个是联合主键,一个是辅助索引:
联合主键:PRIMARY KEY (id,user_name,sex)
辅助索引:KEY name_sex_age (user_name,sex,age)
1、示例1—辅助索引访问
select user_name,sex,age,c_date from test where user_name=’test1’ and sex=1
匹配列user_name sex
匹配索引name_sex_age (user_name,sex,age),
过滤因子:
其中user_name 有500个不同的值,过滤因子为0.2%(1/500)
其中sex 有2个不同的值,过滤因子为50%(1/2)
组合过滤因子=0.2% * 50% =0.1%结果集=1000w * 0.2% * 50% =10000
LRT估算:
1、访问索引表
该查询匹配到了索引name_sex_age (user_name,sex,age),过滤因子是0.1%,其需要扫描索引表数据,是10000行,这其中包含1次随机查询(第一次定位page)和9999次顺序查询。
索引LRT:
TR =1 * 10ms=10ms
TS=9999 * 0.01ms=100ms
index_LRT=10ms+100ms=110ms
2、访问主表
因为该索引name_sex_age (user_name,sex,age)是一个辅助索引,而字段’c_date’ 并不在索引中,因此需要一次主表扫描。而此索引不是聚簇索引,所以回表查询,需要10000次的随机IO查询。
表LRT:
TR=10000 *10ms=100000ms
TS=0 *0.01ms=0ms
LRT=100000ms+0ms=100000ms
3、LRT 总耗时
Fetch =10000*0.1ms=1000ms
LRT =索引LRT+表LRT+Fetch=110ms+100000ms+1000ms=101110ms=101s
上述查询我们大概估算出的查询时间为101s左右,真实情况因为磁盘缓存以及表预读取的原因,速度会比这个快。不过我们不考虑这些,我可以通过这个值的预估,了解到当前查询以及索引的性能,以供自己判断和优化。
4、改善&优化
上述查询中,我们可以看到耗时最多的地方,发生在主表回查中,原因是字段’c_date’ 不在辅助索引name_sex_age 中,造成了过多的随机访问。
我们可以在索引中增加匹配字段(user_name,sex,age,c_date) ,如此就可以直接索引表访问,不再进行回表访问。我们可以看到 此时的索引是一个三星索引。如此可以节省100000ms,只剩下1020ms左右。
2、示例2—聚簇索引访问
select user_name,sex,age,c_date from test where user_name='test1' and sex=1 and id between 1000 and 101000;
匹配列id
过滤列user_name sex
匹配索引 PRIMARY KEY (id,user_name,sex)
过滤因子
id between 1000 and 101000,有100000行数据。
user_name 有500个不同的值,过滤因子为0.2%(1/500)
sex 有2个不同的值,过滤因子为50%(1/2)
组合过滤因子=0.2% * 50% =0.1%
结果集=10w * 0.2% * 50% =100
LRT估算:
由于我们这次应用的索引是主键索引(也就是聚餐索引(引擎innodb)),所以我的表LRT访问 时候,是顺序访问,不是随机IO访问。
LRT =索引LRT+表LRT+Fetch=11ms+11ms+10ms=31ms
文档来源:开源中国社区https://my.oschina.net/u/1859679/blog/1592538
页:
[1]