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

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

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

摘要: QUBE甚至可以在设计程序之前就开始使用,只需要简单地知道数据库一定会处理的最差输入场景即可事实上,直到估算结果令人满意时再开始设计程序是非常明智的做法。有时,我们可能还必须设计较为复杂的程序结构以满足性 ...

我们将用另一个例子再次展示在索引设计过程中如何使用这两种技术,这次是一个稍微复杂一些的SELECT

范围事务的BQQUBE

在这个例子中,我们像之前一样假设CITY的最大过滤因子是10%,但现在LNAME列在一个范围谓词中,我们假设该列有一个比之前更大的过滤因子,比如10%。现在,结果集的大小最大将会是1 000 000×10%×10%,即10 000

图1展示了客户表的现有索引。现在有两个索引应当被考虑即(CITY)(LNAME,FNAME),所以CURSOR56被展示了两次(见SQL 5.6ASQL 5.6B),分别对应一个索引。需要注意的是,这两个索引都不能避免排序。

 

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

 

这两个索引都不是半宽索引,所以两者都不满足BQQUBE的结果显示出二者的性能都非常差,无论使用哪个索引其结果都是相同的:两个索引的匹配扫描过程都只能使用1MC,都需要进行排序,并且都做不到只需访问索引。一颗星也没有,相应地,性能也相当差。

 

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

  CITY

  CUST          TR = 100 000       TS = 0

提取 10 000 × 0.1 ms

 

LRT                  TR = 100 001       TS = 100 000

                     100 001 × 10 ms 100 000 × 0.01 ms

                        1000 s + 1 s + 1 s 17 min

 

在这样的索引条件下,很多优化器实际上会选择多索引访问的方式(将在第10章中讨论),在访问表之前会读取两个索引片,(LNAME,FNAME)(CITY)。这一方式的时间开销会比17min少得多,但仍然很慢。

该事务的最佳索引

最佳索引很容易得到,候选方案A是:

 

(CITY, LNAME, FNAME, CNO)

 

该索引只有两颗星,因为需要排序(ORDER BY列跟在范围谓词列的后面)。因此,候选方案B一定是:

 

(CITY, FNAME, LNAME, CNO)

 

它也只有两颗星,不过它具有的是第二颗星而不是第一颗。通过QUBE很容易判断出哪一个才是最佳索引。

 

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

提取 10 000 × 0.1 ms

 

候选方案ALRT                  TR = 1      TS = 10 000

                                1 × 10 ms  10 000 × 0.01 ms

                                10 ms + 0.1 s + 1 s 1 s

 

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

提取 10 000 × 0.1 ms

 

候选方案BLRT              TR = 1        TS = 100 000

                             1 × 10 ms 100 000 × 0.01 ms

                                10 ms + 1 s + 1 s 2 s

 

最佳索引显然是候选方案A(CITY,LNAME,FNAME,CNO)LRT1s,但即便是最佳索引,事务的性能也一般。

我们早先提出过,如果只是为了比较不同的访问路径,FETCH的处理可以被忽略,因为它在所有的情况下都是相同的。但是这么做之后,在决定选取哪个索引时要小心一点。例如,在这个案例中,如果忽略FETCH的处理,那么候选方案ALRT 0.1s)的成本仅为候选方案BLRT 1s)成本的1/10,而实际的优势比例要小得多(21)。

在这个例子中,候选方案A所需要的排序成本与候选方案B所节省的1秒相比是微不足道的,如前所述,事实上排序成本已经被包含在FETCH的开销中了。候选方案B的问题在于我们使用的索引片更厚(10%),从而产生了大量的TS

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

在两个现有索引的末端添加缺少的谓词列可以消除大量的随机表访问,因为这样做能够引入索引过滤过程。只有在确定索引行中同时包含所需的CITYLNAME值时,表中的行才会被访问。

在现有索引的基础上,我们可以使用的两个半宽索引是:

 

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

 

我们再一次用QUBE来判断哪个是最好的。

 

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

  CUST          TR = 10 000        TS = 0

提取 10 000 × 0.1 ms

 

LRT                  TR = 10 001        TS = 10 000

10 001 × 10 ms 10 000 × 0.01 ms

100 s + 0.1 s + 1 s 101 s

 

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

  CUST              TR = 10% × 100 000    TS = 0

提取 10 000 × 0.1 ms

 

LRT                      TR = 10 001             TS = 100 000

10 001 × 10 ms      100 000 × 0.01 ms

100 s + 1 s + 1 s = 102 s

 

虽然使用第二个索引会有一个更厚的索引片,但这个因素在很大程度上被大量的表访问掩盖了(尽管已经通过过滤的方式将表访问的次数大大减少了)。看来我们可能需要设计一个宽索引来消除对表的10 000TR了。

宽索引(只需访问索引)

我们在上面评估的第一个半宽索引上再多加两个列,在第二个索引上再多加一个列,两者就变成了宽索引:(CITY,LNAME,FNAME,CNO)(LNAME,FNAME,CITY,CNO)

 

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

提取 10 000 × 0.1 ms

 

LRT                          TR = 1        TS = 10 000

1 × 10 ms 10 000 × 0.01 ms

10 ms + 0.1 s + 1 s 1 s

 

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

提取 10 000 × 0.1 ms

 

LRT                          TR = 1        TS = 100 000

1 × 10 ms 100 000 × 0.01 ms

10 ms + 1 s + 1 s 2 s

 

现在表访问已经被消除了,两个索引的LRT之间的区别就变得很明显了。第一个索引满足第一颗星,即提供了一个薄的索引片,而第二个不满足,所以使用了一个较厚的索引片。

第一个索引当然是候选方案A最佳索引(我们之前已经设计过了),因为它所基于的原始索引只包含CITY列,这是在SELECT中唯一的等值条件。正因为如此,我们才能将它升级成最佳索引,首先添加BETWEEN谓词列(使其变成半宽索引,但仍不够),然后再添加SELECT中引用的其他列(使其变成宽索引)。

1提供了各种索引的性能比较。

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

类型

索引

LRT

维护成本

现有索引

LNAME,FNAMECITY

17 min

半宽索引

CITY,LNAME

101 s

U LNAME + 10–20 ms

半宽索引

LNAME,FNAME,CITY

102 s

U CITY + 10–20 ms

宽索引

CITY, LNAME, FNAME, CNO

1 s

U L & FNAME + 10–20 ms

宽索引

LNAME, FNAME, CITY, CNO

2 s

U CITY + 10–20 ms

二星索引A

CITY, LNAME, FNAME, CNO

1 s

U L & FNAME + 10–20 ms

二星索引B

CITY, FNAME, LNAME, CNO

2s

U L & FNAME + 10–20 ms

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

 

何时使用QUBE

理想情况下,QUBE应当在新方案的设计过程中使用。如果在当前或者计划设计的索引条件下最差输入的本地响应时间过长,比如大于2s,那么就应当考虑进行索引优化了。

尽管批处理任务的告警阀值视具体的任务而定,但检查一下所有批处理程序相邻提交点之间的最大时间间隔是很重要的。这个值也许应当小于1s2s,否则该批处理任务有可能会导致事务池和其他批处理任务中的大量锁等待。

QUBE甚至可以在设计程序之前就开始使用,只需要简单地知道数据库一定会处理的最差输入场景即可。例如,读取A表中10行不相邻的记录,向B表中增加2行相邻的记录,等等。事实上,直到估算结果令人满意时再开始设计程序是非常明智的做法。有时,我们可能还必须设计较为复杂的程序结构以满足性能的要求。

鲜花

握手

雷人

路过

鸡蛋

最新评论

热门频道

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

即将开课

热门文章

     

    GMT+8, 2017-1-17 03:12 , Processed in 0.655504 second(s), 28 queries .