数据库索引设计与优化04-被动式索引设计

到目前为止,我们讨论的都是相对简单的SQL语句。在我们讨论如关联查询,子查询,合并查询,星型查询和多索引访问时,有必要先讨论一下如何监视SQL,并从被动式响应的角度考虑索引设计中需要考虑的问题。

被动式的方法与莱特兄弟创造守架飞机的经历非常相似。本质上就是把查询放在一起,推下悬崖,然后看他能否起飞。换句话说,就是为应用设计一个没有索引的原型,然后开始运行一些查询。又或者,创建原始索引集,然后通过运行应用来看那些索引被用到,那些没有被用到。即使是一个小型的数据库系统,运行速度慢的查询也会被很快凸显出来。

被动式调优的方法也被用来理解和调优一个性能没有满足预期的已有应用。

在产品投入大规模使用后才对应用的大部分索引进行调优工作,就像是将一个载满乘客的大型客机推下悬崖。这可能会有一个好的结果,但是数据库员工必须做好快速响应的准备。如果没有人在应用开发阶段关注索引设计的话,一些程序可能会在应用投入后变得非常慢。

我们将会讨论在应用投入生产环境后的最初几天需要用到的性能工具和技术。这些工具和技术对于之后能够轻松地调优也是有用的,以在用户觉察之前发现性能问题,或者至少在问题变得令人无法忍受之前发现他们。在这种场景下,我们实际上是主动的而非被动的。

explain描述了所选择的访问路径

识别可疑的访问路径是相当容易的,尤其是当explain的输出结果被存储在表中的情况下,因为这使得获取解决过变得容易。这就是为什么对于优化器选择了可以访问路径的SQL,分析过程通常从索引优化开始。通过explain能够快读发现以下这些性能警示信号。

全表扫描或全索引扫描

对结果集排序

除了全表扫描和全索引扫描,结果集的排序就是最有用的警示信号了。引起排序的原因可能有以下两种

  1. 没有可使查询语句避免排序的索引
  2. 优化器所选择的访问路径包含了一次多余的排序

对于第一种情况,可以通过优化索引来避免,第二种后面再讨论。

通常,排序是没有害处的。例如,某应用可能有以前个不同的查询语句,其中几百个在他们的访问路径中都有一次排序,这几百个语句中的90%可能都有非常出色的性能,排序对其响应时间值贡献了很不明显的一部分。因此,大量错误的警告可能会使这种检查方法有些无用。

有很多数据库顾问将排序视为敌人。我们认为,哪些强调随机I/O带来致命影响的顾问更值得信任。

成本估算

一些数据库管理系统的explain功能显示了优化器对所选访问路径的本地响应时间的估算,或至少显示了对CPU时间的估算。有些产品,如SQL Server 2000,显示了对访问路径中每一步的CPU及I/O时间估值。在一个简单的场景中,可能会有如下几步 :

  1. 检索并读取索引行
  2. 对指针进行排序
  3. 检索并读取表行
  4. 对结果进行排序

经验显示,过分的依赖优化器的成本估算是危险的。毕竟优化器生成成本估算仅仅是为了选择最快的访问路径。优化器生成的估值有时会给出错误的警示信号,而且还不会显示所有的警示信号,但这是一个用起来简单且快速的工具,他使得早期检查称为可能。因此,哪些成本估值异常高的SQL语句--可能是一个新应用中估值最高的前20个语句,应该一一检查一下,很可能不合适的索引使用或优化器问题就能被发现。

不幸的是,以下两个严重问题限制了使用成本估算方法的价值

  1. 优化器所做出的的本地响应时间估算可能与实际相差很大
  2. 当谓词使用绑定变量时(显然这是很普遍的),优化器对过滤因子的估算是基于平均输入值的,或更差情况下,基于默认值。为了获取更有价值的最差情况估值,explain中的绑定变量必须用最差情况下的输入值来代替。这是一个需要应用知识的累人操作。

explain是分析优化器问题的一个不可或缺的工具。因为这正是存在的理由,而不仅仅被用于索引设计。就像机场安检人员不仅是在机场检查可疑乘客。

问题制造者和问题受害者

一个涵盖一周中高峰时段的尖刺报告可能包含100多个不同领域模块的数千个尖刺。在应用开发者的些许帮助下,一个数据库专家也许每周分析并修复5-10个模块。由于程序变更或不同用户输入,下一周的尖刺报告可能会显示新的问题模块。为了高效地利用有限的时间,仔细地对所分析的模块排定优先级是很重要的,事务的响应时间及执行频率并不是唯一的评定标准。

我们首先要区分的是问题制造者和受害者。如果一个事务独占了资源(也许是因为使用了不合适的索引),那么会对其他事务造成明显的负面影响,进而导致这些事务也与独占资源的事务一同出现在异常报告中。

问题制造者所使用的资源会被包含进服务时间,而问题受害者会因为需要等待这些资源而受到影响,即排队时间受影响。着手优化的合理方式是考虑如何解决问题制造者导致的问题,而非受害者的问题

有优化空间的问题制造者和无优化空间的问题制造者

在决定将注意力从受害者转移到问题制造者上之后,我们需要区分的第二项就是有优化空间的问题制造者和无优化空间的问题制造者。不过我们可能没有时间去分析所有的问题制造者。有优化空间的问题制造者是指哪些能够通过改进所有来获得大幅性能提升的事务。

有优化空间的问题制造者有两个特征

  1. 磁盘服务时间长。
  2. 磁盘度大多是是对表页的读取。

若在同步读上耗费的时间占了本地响应时间的绝大部分,且有超过100个表页是从磁盘服务器上读取的,那么索引改进很可能会带来不错的效果。同样,如果对于索引页的同步读大于100次也不是一个好消息。

为了减少对索引的随机读次数,可以减少所需访问的索引数量,也可以减少所需访问的表的数量,这可以通过向表添加冗余字段的方式实现。这些改变并不比创建一个新的索引或像现有索引列上添加列更容易或更可控。

关机随机访问有一个例外,就是对一个厚索引片扫描可能会由于叶子页的分裂而变慢。这个文件很容易解决也相对容易避免,将在后面介绍。

有优化空间的问题制造者

分析有优化空间的问题制造者通常会读取许多表页。我们知道有三种读取表页的方式 :

  1. 事务中的随机读(同步读 : SR)
  2. 跳跃式顺序读
  3. 顺序读(顺序预读)

通过分析见此报告,我们能够腿短出有优化空间的问题制造者主要是有用这三种方式中的那种方式读取表页的。

同步读(方式1)不与CPU的时间重叠,应用程序会停下来,等待锁清秋的页从磁盘服务器返回。尖刺报告会将该事务同步读取的页数连同这些同步读的平均等待时间一起显示出来。

异步读(方式2和方式3)是预读取,他的I/O时间与程序所花费的CPU时间是重叠的。在数据库管理系统向磁盘服务器请求后几页的同时,程序任然在处理数据库缓冲池中的页。程序可能会遇到没有页可供处理的情况,也可能不会遇到。如果发生了这种情况,那么这个等待时间会被记录进预读等待的计数器中。如果程序从未与发哦需要等待预读取页的情况,那么CPU时间便决定了程序的性能。于是,在这种情况下,预读等待的值为0.顺序扫描所花费的时间一部分被记录进SQL的CPU时间,一部分被记录进其他等待(对于任何CPU排队来说)。

在当前硬件下,以跳跃式顺序读的方式访问表页时,响应时间可能还是取决于本地响应时间中最主要的组成部分,因为这意味着包含满足条件的页彼此相隔很远。

而以顺序读方式访问表页时,在当前硬件条件下,CPU或I/O时间的主导地位基本各占一半,有时访问每页所花费的CPU时间比I/O时间长一些,有时又短一些。QUBE算法假设,I/O时间与CPU时间中较大者的上线为0.01ms每行。如果I/O时间决定了顺序读的时间,那么顺序扫描所花费的时间就等于尖刺报告中CPU时间与预读等待部分的总和。

许多程序使用上述方式中的两种或三种来访问表页。此时理解尖刺报告就没有那么简单了,不过主要的方法通常也不是那么难推理。访问的表页总数也许能够帮助我们理解报告。访问的表页总数除了同步读次数外,还包括了通过预读读取的页数(方式2和方式3)和缓冲池的命中数(在数据库缓存终止找到的表页数量)。

调优的潜在空间

在投入大量时间分析一个异常事务之前,值得花时间先去评估一下在索引优化之后本地响应时间能降低多少。调优的潜在空间就是指可实现的降低值的上限。

随机读

只读事务的调优潜在空间等同于读取的表页数和同步读的平均时长的乘积。如果通过使用宽索引避免全部表页的读取,那么本地响应时间就能减少以这个量。

跳跃式顺序读

在上述例子中,有些优化器可能会选择跳跃式顺序读。那么数据库管理系统将首先从所有满足条件的索引行中获取指针,然后再根据表页号对指针进行排序。此时,数据库管理系统就有了所有表页排序后的列表,每个表页都至少包含一条符合条件的记录。只有到这时,数据库管理系统才会开始访问表。在这种方式下,访问每个表页的I/O时间自然相对较短,尤其是当一些满足条件的行恰好都在同一个磁轨上时。另外,I/O时间将会与CPU时间重叠,因为数据库管理系统有条件进行预读了。相比同步读,这种方式所节省的时间是非常不确定的,具体取决于读取的页之间的平均距离以及条带的实现方式。这种方式的调优潜在空间即为当前预读等待的值。

顺序读

顺序扫描的响应时间通常取决于CPU时间。

这类问题,我们需要找到一个更窄的索引段或表段的访问路径,以达到调优的目的。

无优化空间的问题制造者

通常来说,对有大量SQL调用(如10000多次)的事务来说,如果CPU时间是本地响应时间的主要组成部分,那么这些事务是很难被优化的。

对于此类事务,调优潜在空间即为能够去除的SQL调用次数乘以每次SQL调用的基本CPU时间(如0.1ms)。

受害者

调用级别的一次监视

假设我们执行了一次高峰时段的跟踪,且基于该时间段内耗费时间的长短生成了最差SQL调用列表。现在,我们将着手处理单次调用而非均值。进一步假设我们所使用的工具对每次调用只提供了4个重要测算,他们是 : 耗费时间(ET),CPU时间(CPU),从磁盘读取的页数(READS)及数据库页的请求数(PR)。数据库页请求在DB2中被称为读取页,在SQL Server中被称为逻辑读,在Oracle中被称为读取或LIO。为了保持所有事物尽可能简单,我们使用一个能反映所有这些含义的简单术语-页请求(PR)。

术语

子集1(包含子集2和子集3) : ET > 200ms 慢查询
子集2(包含子集3) : ET/PR < 10ms 有可能的问题制造者
子集3 : PR / F < 5 有优化空间的问题制造者

从耗费时间(ET)最长的SQL调用开始,为了通过访问路径调优的方式改进响应时间,我们同样应该试着回答之前在LRT级别讨论过的两个问题。

首先,该SQL调用看起来是不是问题制造者?换句话说,过长的耗费时间是不是由于服务时间过长导致的?有几个方法可以回答这个问题。较高的CPU时间或较高的READS值都意味着较长的服务时间,但是,实际上,PR似乎是服务时间最有用的指示器,因为它与CPU和READS都相关,而且在很大程度上是经得起反复校验的,它不受系统负载或预热的影响。

为了识别那些可能是问题制造者的SQL调用,我们应该忽略那些PR值低的调用。如果一个查询耗费了1s且发送了少于100次页请求,那么它可能就是一个受害者。在可能情况下,每次页请求都会造成一次随机读,如果磁盘排队不过分的话,这将花去100 10ms = 1s。而在实际情况中,许多页请求在数据库缓冲池中就会被满足,另一些则会在磁盘服务器的读缓存中被满足。请记住,报告的PR值包含了非叶子索引页,另外,许多页请求是顺序的,100个页请求的期望I/O时间远比1s短得多,CPU时间的期望值也是如此。总而言之,如果一个包含100次页请求的SQL调用被测得的耗费时间是1s,那么可能它所耗费的排队时间(磁盘驱动排队,CPU排队,锁等待或其他等待)比服务时间长。通常,如果一个慢查询所耗费的时间比“页请求数 10ms”,那么这个查询很可能就是受害者。它属于子集1,但不属于子集2。

子集2是可能的问题制造者,它包含了哪些ET/PR时间不是很长的SQL调用,他们不是很可能的或者明显的受害者。然而,子集2可能也包含了许多受害者,比如进行顺序操作的SQL调用(在每页上所耗费的服务时间较短)会由于过度的CPU排序时间而变得比较慢。如果我们像对待LRT级别的尖刺报告那样关注所有的锁等待或磁盘,CPU排队时间,那么我们是能够过滤掉这些受害者中的绝大部分的。但实际情况是我们只有4个值,所以很不幸,子集2可能会很大。这也是为什么第二个问题很重要 : 该SQL调用是不是一个有优化空间的问题制造者?

为了找出有优化空间的问题制造者,即子集3,我们需要判断结果集的行数。如果读取(F)的数量包含在了异常报告中(或者该值很容易被取得),那么我们可以把它作为结果行数的近似值。如果PR / F 大于5,那么这个查询很可能是一个优化空间的问题制造者,因为在好的访问路径下,对每张表的一次典型读取只需一或两次以内的页请求。造成PR / F大于5的原因很可能是有大量无用的随机访问,即许多行被访问后就被丢弃掉了。

子集3包含了由于没有半宽索引而引起大量随机读的查询,这些是优化回报率最高的调用。子集3同样包含一些有好的访问路径SQL调用(如使用宽索引对一个级联进行count查询),但子集中的绝大部分查询可能都存在有趣的访问路径问题。

这些查询应该首先用Basic Question方法分析一下。这样可能立刻揭露出不合适的索引。紧接着下一步是explain一下这些sql调用,看优化器选择了什么路径。这样可能会揭露出一个优化器问题,如一个负复杂谓词(匹配列太少)或者一个较差的过滤因此估算,进而导致错误的索引选择或错误的表访问顺序。

CPU和READS值提供了对访问路径的有效指示器。例如,如果CPU接近ET,那么处理过程大部分是顺序的;如果CPU很低,ET/READS通常与每页的平均I/O时间接近;如果该值明显小于1ms,那么大部分的I/O是顺序的;如果该值为几毫秒,那么大部分I/O可能是随机的。这可能对执行计划所提供的信息进行补充。另外,任何高CPU值的SQL调用(如大于500ms),都可以被认为是一个有趣的问题制造者。

分析过子集3中排在前几位的调用(至少有一次很长ET的调用和那些经常很慢的调用)后,我们再看一下可能的问题制造者子集2中最慢且调用最频繁的SQL调用。我们可能会找到这样的查询 : 即使使用了半宽索引却仍旧很慢,但能够通过一个宽索引大大提高性能的查询。

当时用更好的索引或者调整优化器的方式优化了一些SQL调用后,应该生成一个新的异常报告。索引的改善可能会帮助许多SQL调用,问题制造者及受害者,但是也有可能在新生成的问题列表中出现一些新的访问路径问题-主要是由于系统负载的改变,或者有时是由于索引的改变(幸运的是这并不经常发生)。

按照ET值排序,我们从上往下依次处理列表中的问题,那么要往下处理到第几个问题才能停下来呢?在一个操作系统中,响应时间极少超过1s。那么理想情况下,我们应该检查任何响应时间大于200ms的SQL调用。不幸的是,在实际生活中,我们可能永远做不到如此。有太多的SQL调用需要处理,而可用的时间太少了!

标签: mysql, mysql索引

添加新评论