MySQL 索引性能分析概要

上一篇文章 《MySQL 索引设计概要》 介绍了影响索引设计的几大因素,包括过滤因子、索引片的宽窄与大小以及匹配列和过滤列。在文章的后半部分介绍了 数据库索引设计与优化 一书中,理想的三星索引的设计流程和套路,到目前为止虽然我们掌握了单表索引的设计方法,但是却没有分析预估索引耗时的能力。

在本文中,我们将介绍书中提到的两种分析索引性能的方法:基本问题法(BQ)和快速估算上限法(QUBE),这两种方法能够帮助我们快速分析、估算索引的性能,及时发现问题。

基本问题法

当我们需要考虑对现有的 SELECT 查询进行分析时,哪怕没有足够的时间,也应该使用基本问题法对查询进行评估,评估的内容非常简单:现有的索引或者即将添加的索引是否包含了 WHERE 中使用的全部列,也就是对于当前查询来说,是否有一个索引是半宽索引。

在上一篇文章中,我们介绍过宽索引和窄索引,窄索引 (username) 其实就叫做半宽索引,其中包含了 WHERE 中的全部的列 username,当前索引的对于该查询只有一颗星,它虽然避免了无效的回表查询造成的随机 IO,但是如果当前的索引的性能仍然无法满足需要,就可以添加 age 将该索引变成宽索引 (username, age) 以此来避免回表访问造成的性能影响;对于上图中的简单查询,索引 (username, age) 其实已经是一个三星索引了,但是对于包含 ORDER BY 或者更加复杂的查询,(username, age) 可能就只是二星索引:

在这时如果该索引仍然不能满足性能的需要,就可以考虑按照上一篇文章 MySQL 索引设计概要 中提供的索引设计方法重新设计了。

虽然基本问题法能够快速解决一些由于索引造成的问题,但是它并不能保证足够的性能,当表中有 (city, username, age) 索引,谓词为 WHERE username="draveness" AND age="21" 时,使用基本问题法并不能得出正确的结果。

快速估算上限法

基本问题法非常简单,它能够最短的时间内帮助我们评估一个查询的性能,但是它并不能准确地反映一个索引相关的性能问题,而快速估算上限法就是一种更加准确、复杂的方法了;其目的在于在程序开发期间就能将访问路径缓慢的问题暴露出来,这个估算方法的输出就是本地响应时间(Local Response Time):

本地响应时间就是查询在数据库服务器中的耗时,不包括任何的网络延迟和多层环境的通信时间,仅包括执行查询任务的耗时。

响应时间

本地响应时间等于服务时间和排队时间的总和,一次查询请求需要在数据库中等待 CPU 以及磁盘的响应,也可能会因为其他事务正在对同样的数据进行读写,导致当前查询需要等待锁的获取,不过组成响应时间中的主要部分还是磁盘的服务时间:

QUBE 在计算的过程中会忽略除了磁盘排队时间的其他排队时间,这样能够简化整个评估流程,而磁盘的服务时间主要还是包括同步读写以及异步读几个部分:

在排除了上述多个部分的内容,我们得到了一个非常简单的估算过程,整个估算时间的输入仅为随机读和顺序读以及数据获取的三个输入,而它们也是影响查询的主要因素:

其中数据获取的过程在比较不同的索引对同一查询的影响是不需要考虑的,因为同一查询使用不同的索引也会得到相同的结果集,获取的数据也是完全相同的。

访问

top Created with Sketch.