快乐学习
前程无忧、中华英才非你莫属!

为表连接设计索引

练习

8.1 评估图8.25中所示连接的响应时间,过滤因子使用给定的值。

分析:

A为父表,B为子表,两个表做主外键关联查询,只有主键和外键上有索引,并且A表的主键索引和B表的外键索引为聚簇索引。

以A作为外层表做嵌套循环连接计算响应时间:

第1步:通过聚簇索引AK访问A表

索引 AK TR = 1  TS = 10000000

LTR 1 * 10ms + 10000000 * 0.01ms = 100s

第2步:通过聚簇索引AK访问B表

索引 AK TR = 10000000 * 1% = 100000 TS = 100000 * 5 = 500000

LTR 100000 * 10ms + 100000 * 5 * 0.01ms = 1005s

第3步:提取数据

50000000 * 1% * 0.001% * 0.1ms = 0.5ms


所以以A作为外层表做嵌套循环连接响应时间约为1105(100 + 1005)秒


以B作为外层表做嵌套循环连接计算响应时间:

第1步:通过聚簇索引AK访问B表

索引 AK TR = 1  TS = 50000000

LTR 1 * 10ms + 50000000 * 0.01ms = 500s

第2步:通过聚簇索引AK访问A表

索引 AK TR = 50000000 * 0.001% = 500 TS = 500

LTR 500 * 10ms + 500 * 0.01ms = 5s

第3步:提取数据

50000000 * 1% * 0.001% * 0.1ms = 0.5ms


所以以B作为外层表做嵌套循环连接响应时间约为505(500 + 5)秒


8.2 在不添加冗余字段的前提下,为该连接设计最佳索引并评估响应时间。

分析:

因为B1 > :B1的FF很小,仅为0.001%,所以可以建立以B1为前缀的宽索引(B1,AK,B2)

以B作为外层表做嵌套循环连接计算响应时间:

第1步:通过索引B1访问B表,因为B1是宽索引,所以无需回表访问

索引 B1 TR = 1  TS = 50000000 * 0.001% = 500

LTR 1 * 10ms + 500 * 0.01ms = 15ms

第2步:通过聚簇索引AK访问A表

索引 AK TR = 500 TS = 500

LTR 500 * 10ms + 500 * 0.01ms = 5005ms

第3步:提取数据

50000000 * 1% * 0.001% * 0.1ms = 0.5ms


所以使用B1上的宽索引(B1,AK,B2),以B作为外层表做嵌套循环连接响应时间约为5((15+5005+0.5)/1000)秒。


8.3 CUST表中有三个指向代码表的外键。评估在嵌套循环和最佳表访问顺序下,下述这四表连接的本地响应时间(参见图8.26)

分析:

根据四个表的记录数,最佳表访问顺序为C1->CUST->C3->C2

第1步:通过聚簇索引C1PK访问C1表

LTR 1 * 10ms + 1000 * 0.01ms = 0.02s

第2步:通过索引C1PK访问CUST表

LTR 1000 * 10ms + 1000 * 1000 * 0.01ms = 20s -- 平均每个C1PK对应1000行CUST表的记录

第3步:通过索引C3PK访问C3表

LTR 1000000 * 10ms = 10000s

第4步:通过索引C2PK访问C2表

LTR 1000000 * 10ms = 10000s

第5步:提取数据

1000000 * 0.1ms = 100s


本地响应时间约为 20 + 10000 * 2 + 100 = 20120s


8.4 假设SQL 8.19在一个批任务中会被执行一百万次。我们需要改进索引么?调优的空间有多大?有其他方法可以用来提升该SELECT语句的性能吗?

[sql] 

  1. -- SQL 8.19  

  2. SELECT CNAME, C1TEXT, C2TEXT, C3TEXT  

  3.   FROM CUST, C1, C2, C3  

  4.  WHERE CUST.CNO = :CNO  

  5.    AND CUST.C1PK = C1.C1PK  

  6.    AND CUST.C2PK = C2.C2PK  

  7.    AND CUST.C3PK = C3.C3PK  

分析:

因为CNO为主键聚簇索引,所以SQL 8.19最多返回一行记录,先计算执行一次的时间

索引 CNO TR = 1  TS = 1

索引 C1PK TR = 1 TS = 1

索引 C2PK TR = 1 TS = 1

索引 C3PK TR = 1 TS = 1

提取数据 1 * 0.1ms

LTR 4 * 10ms + 4 * 0.01ms + 0.1ms = 40.14ms

一百万次的执行时间为 1000000 * 40.14ms = 40140s

因为所有的索引访问都是针对主键聚簇索引,所以不需要改进索引。

在CUST表上增加C1TEXT, C2TEXT, C3TEXT三个冗余列,使用触发器维护,这样可以避免表连接,执行时间为:

1000000 * (10ms + 0.01ms + 0.1ms) = 10110s

调优的空间为3/4。

打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » 为表连接设计索引

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!