如何预估索引性能?

在《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)和999次顺序查询。

索引LRT:TR =110ms=10msTS=9990.01ms=10msindex_LRT=10ms+10ms=20ms

 

2、访问主表

因为该索引name_sex_age (user_name,sex,age)是一个辅助索引,而字段’c_date’ 并不在索引中,因此需要一次主表扫描。而此索引不是聚簇索引,所以回表查询,需要10000次的随机IO查询。

表LRT:

TR=1000010ms=100000msTS=00.01ms=0msLRT=100000ms+0ms=100000ms

 

3、LRT 总耗时

Fetch =1000*0.1ms=1000msLRT =索引LRT+表LRT+Fetch=20ms+100000ms+1000ms=101020ms=101s

上述查询我们大概估算出的查询时间为101s左右,真实情况因为磁盘缓存以及表预读取的原因,速度会比这个快。不过我们不考虑这些,我可以通过这个值的预估,了解到当前查询以及索引的性能,以供自己判断和优化。

4、改善&优化

上述查询中,我们可以看到耗时最多的地方,发生在主表回查中,原因是字段’c_date’ 不在辅助索引name_sex_age 中,造成了过多的随机访问。

我们可以在索引中增加匹配字段(user_name,sex,agec_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

 

文章来源:

Author:wier
link:https://my.oschina.net/u/1859679/blog/1592538