如何预估索引性能？

今天药忘吃喽~ 2022-01-16 11:13 286阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

在《[DB——数据的读取和存储方式][DB]》中，我们介绍了影响数据读取的方式，随机IO和顺序IO，在《[Sql优化器究竟帮你做了哪些工作？][Sql]》介绍了影响查询的一些基本因素，其中介绍了过滤因子、索引片的宽窄与大小以及匹配列和过滤列 是如何应用在sql查询中的。在《[什么是三星索引][Link 1]》介绍了索引设计需要主要的因素以及推荐的设计方式。

那么这篇，我们将综合运用这些知识，并通过两个方法，快速估算当前的索引的性能，以及查询的效率。从这篇文章你会得到以下知识: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后所有谓词列）？ 如果没有，那么应该将缺少的谓词列加到现有索引上。

基于前面介绍的知识，我们可以知道，半宽索引可以确保回表访问只发生在所有查询条件都满足的时候。也就是说，条件过滤会在表扫描之前发生，而不是之后发生。虽然不能避免主表的随机访问，但起码可以支持索引表的顺序读取。

![114629_pqDt_1859679.png][]

不过对于BQ并不能保证足够的性能。比如如下:

![114638_xxB5_1859679.png][]

上述中where后面的谓词都包含在索引(name,sex,age)，但这个查询却是一个全索引或者全表扫描，三星中一个都不占有。具体原因，可以查看《Sql优化器究竟帮你做了哪些工作？》中的介绍。

那么有没有更全面的评估方式？下面的QUBE，就是一种更全面快速的评估方式。

# **二、快速估算上限法——QUBE** #

QUBE比BQ更耗时，但它能揭示所有与索引或者表设计相关的性能问题。QUBE会对每个谓词所用的过滤因子都非常接近于实际的最差情况，如此虽然会比较悲观，但不会像BQ那样漏掉发现的某些问题。

QUBE的目的在于在开发期间就能将访问路径缓慢的问题暴露出来，这个估算方法的输出就是本地响应时间（Local Response Time），简称为LRT。

![114702_PObc_1859679.png][]

我们先大概了解一下LRT的时间组成。在一个常规的多用户环境中，程序的并发会导致对所需资源的各种竞争，因此不得不排队来获取这些资源。那么QUBE会忽略磁盘驱动排队以外的其他所有类型的排队，已提供一个简单的评估过程。

在排除以上因素之后，我们得到的一个是一个非常简单的估算过程，仅需要以下变量来处理即可。

![114719_D2qT_1859679.png][]

最终的QUBE公式为：

> LRT=TR \* 10ms + TS \* 0.01ms + F \* 0.1ms

我们把随机访问一次的时间定义为10ms，以及把顺序读取一次的时间定义为0.01ms,这个在《[DB——数据的读取和存储方式][Link 1]》有介绍，不了解的可以过去看一下。

# **三、示例** #

我们假设表`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

![114753_CzMZ_1859679.png][]

### **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左右，真实情况因为磁盘缓存以及表预读取的原因，速度会比这个快。不过我们不考虑这些，我可以通过这个值的预估，了解到当前查询以及索引的性能，以供自己判断和优化。

![114846_hDv2_1859679.png][]

### **4、改善&优化** ###

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

我们可以在索引中增加匹配字段(`user_name`,`sex`,`age`，`c_date`) ，如此就可以直接索引表访问，不再进行回表访问。我们可以看到 此时的索引是一个三星索引。如此可以节省100000ms,只剩下1020ms左右。

![114908_MXOZ_1859679.png][]

## **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

![114926_dE9Z_1859679.png][]

转载于:https://my.oschina.net/u/1859679/blog/1592538

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/dd1fab273f4841d79d271e22f21c3502.png
[DB]: https://my.oschina.net/u/1859679/blog/1581379
[Sql]: https://my.oschina.net/u/1859679/blog/1586098
[Link 1]: https://my.oschina.net/u/1859679/blog/1589575
[114629_pqDt_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114629_pqDt_1859679.png
[114638_xxB5_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114638_xxB5_1859679.png
[114702_PObc_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114702_PObc_1859679.png
[114719_D2qT_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114719_D2qT_1859679.png
[114753_CzMZ_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114753_CzMZ_1859679.png
[114846_hDv2_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114846_hDv2_1859679.png
[114908_MXOZ_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114908_MXOZ_1859679.png
[114926_dE9Z_1859679.png]: https://static.oschina.net/uploads/space/2017/1220/114926_dE9Z_1859679.png