炼数成金 门户 图书 查看内容

使用满足需求的成本最低的索引还是所能达到的最优索引:示例1

2015-8-5 15:17| 发布者: 博文视点| 查看: 866| 评论: 0|原作者: 【美】Tapio Lahdenmaki(塔皮奥•拉赫登迈奇),Michael Leach(迈克尔•利奇)|来自: 《数据库索引设计与优化》

摘要: 本文将通过举例来展示如何使用这两种技术(BQ和QUBE)来完成整个索引设计过程。


我们已经通过一些例子展示了如何运用QUBE来方便地评估一些比较重要的访问方式,现在我们将展示如何使用这两种技术(BQQUBE)来完成第4章中讨论的整个索引设计过程。

5.7展示了客户表的当前索引。用户会先从弹出窗口中选择姓(LNAME)和城市名称(CITY),然后再开启事务,这会触发程序打开CURSOR55(如SQL 5.5所示)。

 

图1  使用满足需求的成本最低的索引还是所能达到的最优索引:示例1A

看起来(LNAME,FNAME)是唯一有用的索引。下面我们就来分析一下SQL游标使用此索引时,性能是否满足需求。

我们将使用BQ方法来确定索引(LNAME,FNAME)能否给CURSOR55(参见SQL 5.5和图5.8)提供足够的性能。若有必要,我们将接着评估一个FETCH完整结果集的事务的本地响应时间。评估过程基于以下假设:

 

1.    表中有100万条记录。

2.    (LNAME,FNAME)是唯一合适的索引。

3.    谓词LNAME = :LNAME的最大过滤因子是1%

4.    谓词CITY = :CITY的最大过滤因子是10%

5.    结果集最多包含1000行记录(1 000 000×1%×10%)。

6.    表中的记录按照CNO列的大小顺序存储[主键索引(CNO)是聚簇索引,或者表经常按照CNO进行排序重组]。

 

2 使用满足需求的成本最低的索引还是所能达到的最优索引:示例1B

该事务的基本问题

BQ方法所提出的问题是:

是否有一个现有的或者计划中的索引包含了WHERE子句中所涉及的所有列?

换句话说,我们是否现在或者即将有一个半宽索引?答案显然是没有,至少从现有的索引情况看是这样。这并不一定意味着存在问题,性能还是有可能令人满意的。但至少这是一个警示信号,我们应该去思考是否真的存在问题,如果是并且我们决定忽略它,那么它会变得多严重?我们是否需要将那些不在索引中的谓词列加到索引中?这么做将给BQ一个肯定的答案,但我们应该牢记这仍然并不能确保良好的性能。

对该事务上限的快速估算

DBMS选择使用索引(LNAME,FNAME)的时候,结果行是按照所请求的顺序(ORDER BY FNAME)被获取的,即不需要排序。因此,DBMS将不会进行早期物化OPEN CURSOR动作不会产生对表或索引的访问,而是会在每次FETCH的时候访问索引和表,其中的一些访问会将记录行返回给FETCH,另一些(大部分)则不会。整个过程将引起一次索引扫描,匹配列只有一个,该列的过滤因子是1%。根据QUBE,执行如下SQL

 

则事务的本地响应时间将会是:

 

索引 LNAME, FNAME        TR = 1            TS = 1% × 1 000 000

  CUST          TR = 10 000        TS = 0

提取 1000 × 0.1 ms

 

LRT                  TR = 10 001        TS = 10 000

                     10 001 × 10 ms  10 000 × 0.01 ms

                        100 s + 0.1 s + 0.1 s 100 s

 

很明显,这里有一个很大的问题,即数据库必须读取10 000条不相邻的表记录。这需要耗费很长的时间,将近2分钟

          QUBE是一个对上限的估算法:结果为100 s意味着本地响应时间最多会达到100 s。真实的LRT可能比这个值小,特别是当内存和读缓存的大小对数据库来说非常充足时。这种情况下,许多随机访问将不再需要从磁盘驱动器上读取,随机访问的平均响应时间也可能比10 ms低。不管怎么说,这不是一个错误的告警。对表的随机访问次数太高了,这个话题我们会在第15章展开讨论。

 

由于QUBE是一个非常粗略的公式,因此在结果中显示多于一位的有效数字是具有误导性的,尽管我们在本书中为了清楚起见会这么做。在真实的报告中,应当这样阐述结论:根据快速估算,被提议的索引能将最差情况的响应时间从2 min降至1 s

 

使用满足需求的成本最低的索引还是所能达到的最优索引

设计索引并不只是单纯地为了最小化磁盘I/O的总量,而是为了设法让所有程序都运行得足够快,同时还能做到不使用过量的磁盘存储、内存和读缓存,且不使磁盘超载。所有的这些问题已经在第4章中详细讨论了。

我们甚至可以将这种决策以数字的方式来表达,比如这样问:“将一个每月运行50 000次的事务的响应时间从2s ~ 10s降低至0.1s ~ 0.5s,同时节省1000秒的CPU运算时间,为达到这样的目的每个月支付10美元是否值得?”

通过BQQUBE(或者其他方法),我们已经发现了这样一个事实:CURSOR55会由于没有合适的索引而执行得非常慢。现在,我们面临着一个困难的问题:我们应该给这个语句设计一个所能达到的最优索引,还是为其设计一个成本最低的满足性能需求的索引?又或者是介于两者之间的某种索引?我们并没有一个硬性的公式去解答这个问题,但我们在第4章中讨论的思路应该能够帮助我们设计一个合适的索引。

该事务的最佳索引

使用第4章中描述的算法,我们可以发现针对候选方案A,有两个三星的索引:

 

(LNAME, CITY, FNAME CNO)(CITY, LNAME, FNAME, CNO)

 

这两个索引都有三颗星,因为它们会扫描一个非常薄的索引片(2 MC);ORDER BY列跟在匹配列(它们都使用等值条件)的后面,从而规避了排序;此外,该查询只需访问索引而无须回表。

因为不需要排序,所以没必要考虑候选方案B了。

 

索引  LNAME, CITY, FNAME, CNO    TR = 1         TS = 1% × 10% × 1 000 000

  CITY, LNAME, FNAME, CNO

提取  1000 × 0.1 ms

 

LRT                          TR = 1         TS = 1000

                                1 × 10 ms       1000 × 0.01 ms

                                10 ms + 10 ms + 100 ms = 120 ms

 

在性能方面,这两个索引并没有什么区别,它们都极大地降低了成本。不幸的是,三星索引将意味着额外增加一个索引,因为将列(CITY)添加到现有索引的LNAMEFNAME之间可能会对现有程序造成不利的影响。我们在第4章已经讨论过这个问题了。这就是为什么我们需要先考虑一下开销更低的可选方案。

半宽索引(最大化索引过滤)

将谓词列CITY添加至现有索引(LNAME,FNAME)的末端,可以消除大部分的表访问,因为会引入索引过滤过程。表中的行只有在确定包含所请求的CITY值的情况下才会被访问。

该索引(LNAME,FNAME,CITY)是满足BQ的,所有的谓词列都在索引中。然而,它只有一颗星,所请求的索引行不是连续的(我们只有一个MC),当然,它也不是宽索引。但是,这种开销很低的方案是否满足需求了呢?

 

索引 LNAME, FNAME, CITY    TR = 1            TS = 1% × 1 000 000

  CUST              TR = 10% × 10 000    TS = 0

提取 1000 × 0.1 ms

 

LRT                      TR = 1001     TS = 10 000

                            1001 × 10 ms        10 000 × 0.01 ms

                            10 s + 0.1 s + 0.1 s 10 s

 

借助于这个半宽索引,LRT由原来的近2 min降低到了10 s,这是一个巨大的进步,但还不够(别忘了最佳索引下的运行时间仅为120ms)。我们仍然有太多成本很高的TR,我们需要一个宽索引,以取得更好的性能。

宽索引(只需访问索引)

将一个列添加到半宽索引上,将使该索引变为一个宽索引:(LNAME,FNAME,CITY,CNO)。现在,我们有了两颗星,第二颗和第三颗,同时LRT变成了1s

 

索引 LNAME, FNAME, CITY, CNO    TR = 1        TS = 1% × 1 000 000

  CUST                  TR = 0        TS = 0

提取 1000 × 0.1 ms

 

LRT                          TR = 1        TS = 10 000

                                1 × 10 ms 10 000 × 0.01 ms

                                10 ms + 0.1 s + 0.1 s 0.2 s

 

1提供了各种索引的性能比较,最后一列显示的是由此引起的额外的维护成本。需要注意的是,三星索引将是一个新的索引。对于这些成本提醒如下两点事项:

 

1.    插入和删除意味着修改一个叶子页。如果该叶子页不在缓冲池或者读缓存中,那么整个叶子页就必须从磁盘驱动器上读取。无论所添加或删除的索引行的长度是多少,这都将耗费10 ms的时间。

2.    更新CITY列会导致响应时间增加10ms20 ms,具体的值取决于是否需要将索引行迁移至另一个叶子页。通过将CUST列作为最后一个索引列的方式可以避免这一移动,详见下述说明。

表中的数字显示,现有索引和半宽索引都是不合适的。另外,尽管使用三星索引比使用宽索引要快一倍,但后者已经够用了,且后者不会引起新索引带来的额外性能负载。

表1 示例1在最差输入条件下的各种索引比较

类型

索引

LRT

维护成本

现有索引

LNAME,FNAME

100 s

半宽索引

LNAME,FNAME,CITY

10 s

U CITY + 10 - 20 ms

宽索引

LNAME,FNAME,CITY,CNO

0.2 s

U CITY + 10 - 20 ms

三星索引

LNAME,CITY,FNAME,CNO

0.1 s

I/D + 10 ms U + 10 - 20 ms

LRT是最差输入下的QUBE值,I = 插入,D = 删除,U = 更新

 

重要说明

在这个阶段,我们需要将所有变更的维护成本也考虑进来。比如,在将索引升级为宽索引时,理论上是将CNO添加至索引的末尾,但实际上,将它放在CITY列的前面会更好,即(LNAME,FNAME,CNO,CITY)。对于这个SELECT而言,由于CITY列参与的是索引过滤过程,所以它的位置并不会对运行结果造成影响。

同样,当有多个等值谓词作为匹配列时,我们需要考虑这些列在索引上的先后顺序。经常变化的列应当被尽可能地排在后面(相对LNAME,客户更改地址的可能性更大,因此LNAME,CITY可能是比较合适的顺序)。另一方面,我们需要考虑新索引对于其他SELECT的帮助有多大。在本例中,我们已经有一个LNAME开头的索引了,因此从这个角度看,CITY,LNAME可能更合适。显然,我们需要在这两者之间进行权衡,对于这个案例而言,后者可能更重要,因为CITY并不是一个频繁更新的列。

 

警告

更改现有索引列的顺序与在现有索引列之间添加新列同样危险。在这两种情况下,现有的SELECT的执行速度都可能会急剧下降,因为匹配列的数量减少了,或者引入了排序(导致过早产生结果集)。


鲜花

握手

雷人

路过

鸡蛋

最新评论

热门频道

  • 大数据
  • 商业智能
  • 量化投资
  • 科学探索
  • 创业

即将开课

热门文章

     

    GMT+8, 2017-2-25 12:41 , Processed in 0.703763 second(s), 28 queries .