如何预估索引性能？

江南才子

在《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