数据库索引设计与优化（笔记）：第5章 前瞻性索引设计（主要是QUBE，快速上限估算法）

这一章就比较复杂了

 

 

基本问题法（BQ）

对每个SELECT语句，按照下列步骤来考虑

 

问题：是否有一个已存在的或者计划中的索引包含了WHERE子句所引用的所有列（一个半宽索引）？

 

如果答案是否，则首先考虑将缺少的谓词列加到一个现有索引上去。这将产生一个半宽索引，尽管索引的等值匹配过程不令人满意（1星的问题，如果之前有范围查询，则新加入的索引无法做匹配），但索引过滤可以确保回表访问只发生在所有查询条件都满足的时候。

 

如果这还没有达到足够的性能，则将所有列都加到索引上，以使访问路径只需要访问索引。这将产生一个避免所有表访问的宽索引。

 

如果SELECT依然很慢，就应当使用前面的两个候选索引算法，来设计一个新的索引。

 

如何确定哪个方案好？

 

如果可以访问生产库或类似生产库的测试库，我们可以每次创建一个索引，用最差输入来测试响应时间。必须把缓冲池考虑进来，观察每个事物的缓冲池命中率。

 

为了获取有代表性的响应时间，每个索引方案都应该在进行过预热事务之后再开始。

1. 第一个事务：没有缓存页
2. 第一个访问索引的事务必须要等待文件被打开
3. 如果变量的输入值保持不变，那么第二个访问该索引的事务会获得100%的命中率
4. 可以通过传入一些典型的输入值（不是最差值）来打开文件，并将大部分非叶子索引页加载到缓冲池
5. 这样，再用最差输入值的事务就会有一个比较有代表性的响应时间了。

 

注意：对于BQ的一个肯定回答并不能保证足够的性能。BQ目的只是确保至少可以通过索引过滤来最小化对表的访问---除此之外没有其他作用。看个例子：

WHERE B=:B AND C=:C

索引使(A,B,C)，按照BQ第一个问题，答案是“是”。但实际上无法使用索引，只能表扫描。

 

BQ方法用在单表比较容易。但在连接情况下，就很复杂了（后面讨论，第8章）

 

快速上限估算法（QUBE）

1. 它假设对每个谓词所用的最差过滤因子都非常接近实际的最差情况过滤因子值。所以，它是悲观（上限）的，有时候会误报警，但不会漏掉。
2. 其目的是：在非常早的阶段，将潜在的慢访问路径问题都暴露出来。
3. 算法输出结果是本地响应时间（LRT），即在数据库服务器中的耗时。--执行耗时，把网络、任务队列排队等都排除在外。

本地响应时间
服务时间	排队时间
CPU时间	CPU排队
磁盘服务时间 同步读 同步写 异步读（非重叠部分）	磁盘排队 锁等待 其他等待

 

服务时间：简单场景下（IO时间和CPU时间不重叠），服务时间等于CPU时间+排除磁盘驱动排队的随机读时间

1. LRT计算

比较：LRT=TR*10ms+TS*0.01ms

绝对值：LRT= TR*10ms+TS*0.01ms+F*0.1ms

 

LRT：本地响应时间

TR：随机访问数量

TS：顺序访问数量

F：有效FETCH数量

 

1. QUBE假设

所有表、索引都是以最理想的顺序组织的。根据QUBE的定义，扫描索引或一个表的片段，只需要一次随机访问。

 

 

下面都是通过例子一层层递进来分析的。需要不断的熟悉才行。以下例子都跟着推导过。

 

QUBE例子1：主键索引访问

SELECT CNO,LNAME,FNAME

         FROM CUST

         WHERE  CNO=:CNO

 

         索引CNO    TR=1 

         表CUST      TR=1

         提取F 1*0.1ms

   

LRT = 2*10ms(TR)+1*0.1ms ，约为20ms

 

聚簇索引访问

SELECT CNO, LNAME, FNAME

FROM CUST

WHERE ZIP=:ZIP

AND

LNAME=:LNAME

ORDER BY FNAME

 

设索引为(ZIP,LNAME,FNAME)

 

假设有1000个符合条件的，表按照CNO排序（聚簇）

 

索引(ZIP,LNAME,FNAME)     TR=1  TS=1000

表CUST                   TR=1  TS=1000

提取F 1000*0.1ms

 

LRT = 2*10ms+2000*0.01+1000ms*0.1ms=20ms+20ms+100ms = 140ms

 

非聚簇索引访问

SELECT CNO, LNAME, FNAME

FROM CUST

WHERE ZIP=:ZIP

AND

LNAME=:LNAME

ORDER BY FNAME

 

索引(ZIP,LNAME,FNAME)，CNO没有按顺序

 

则：

 

索引(ZIP,LNAME,FNAME)     TR=1  TS=1000

表CUST                   TR=1000  TS=0

提取F 1000*0.1ms

LRT = 1001*10ms+1000*0.01ms+1000*0.1ms = 10s+10ms+0.1s，约为10s

 

此时如果把CNO加到索引上，就不需要回表访问，则时间为：

索引(ZIP,LNAME,FNAME)     TR=1  TS=1000

表CUST                   TR=0  TS=0

提取F 1000*0.1ms

LRT = 1*10ms+1000*0.01ms+1000*0.1ms=10ms+10ms+100ms = 120ms

使用聚簇索引进行跳跃式顺序表访问（跳跃，就意味着不连续了）

SELECT STREET, NUMBER,ZIP,BORN

FROM  CUST

WHERE LNAME=’JONES’

AND FNAME=‘ADAM’

AND CITY=’LONDON’

ORDER BY BORN

 

聚簇索引为(LNAME,FNAME,BORN,CITY) 

在LNAME,FNAME范围内扫描，以CITY为过滤。索引还是顺序扫描。

但对于其他信息，需要回表找。但因为CITY只是过滤，所以不连续，在进行表扫描时，虽然顺序，但需要跳着走，表访问算作随机（虽然大范围来看是顺序的）

复杂例子1

SELECT CNO,FNAME

FROM CUST

WHERE LNAME=:LNAME

AND

CITY=:CITY

ORDER BY FNAME

 

索引条件1(LNAME,FNAME)

在以下假设下评估

1. 表中有100万条记录
2. 唯一索引为(LNAME,FNAME)时，
3. LNAME=:LNAME的最大过滤因子是1%
4. CITY=:CITY的最大过滤因子是10%
5. 表中记录CNO是聚簇索引

 

分析：

结果集最大包含：10000000*1%*10%=1000条

 

LNAME为等值谓词，按照FNAME排序，索引可用，所以不需要早起物化。

 

索引访问是一个顺序过程：

索引访问：   TR = 1    TS = 100W*1% = 1W

因为CITY不在索引，是个过滤条件，所以会造成访问表时，是随机的。

 

表：        TR = 1W  TS = 0

 

FETCH：1000

 

LRT = 10001*10ms + 10000*0.01ms +1000*0.1ms = 100s + 100ms + 100ms ，约为100s

 

索引设计部只是单纯的为了最小化磁盘IO的总量，而是为了设法让所有程序都运行得足够快，同事还能做到不使用过量的磁盘存储、内存和读缓存，且不使磁盘超载。

 

 

看上去，上面的索引不太好。

最佳索引

它的三星索引为：

(CITY,LNAME,FNAME,CNO)或者(LNAME,CITY,FNAME,CNO)

ORDER BY跟在匹配列后面，不需要排序；且无需回表访问

 

 

LRT

索引：   TR=1   TS=1000000*1%*10%=1000

表：    TR=0  TS=0

FETCH 1000

LRT = 1*10ms+1000*0.01ms+1000*0.1ms = 10+10+100 =120ms。

 

这里假设是在已有系统改进，所以，在已经有（LNAME,FNAME）索引下，还需要重新创建一个索引（因为如果在原有索引加入CITY，可能会导致已有业务出现问题）。所以开销有点大。

 

接下来考虑次之的方案

 

半宽索引（最大化索引过滤）

将为此CITY加到现有索引（LNAME,FNAME）末端，对原有的不影响，且CITY可以参与过滤。

计算一下LRT

 

索引   TR=1  TS=100W*1%=10000

表     TR=10000*10%（因为CITY可以过滤，因子10%）=1000   TS=0

FETCH=1000*0.1ms

 

LRT = 1000*10ms+10000*0.01ms+1000*0.1ms = 10s+100ms+100ms = 10s

 

比原来取得较大进步，但看到表的TR还很大。如果不回表，会更好。

 

宽索引（只需要访问索引）

索引（LNAME,FNAME,CITY,CNO）

 

LRT

索引  TR=1   TS=10000

表    TR = 0   TS = 0

FETCH：1000*0.1ms

LRT = 1*10ms+10000*0.01ms+ 1000*0.1ms = 10ms+100ms+100ms = 210ms

 

索引还带来其他成本开销：

插入、删除，意味着修改一个叶子页。如果叶子不在缓冲池，需要读取，无论所添加或删除的索引行长度是多少，消耗10ms

更新CITY列，会导致响应时间增加10ms或20ms，具体取决于是否需要将索引迁移到另一个页。通过将CITY作为最后一个索引可以避免这样的移动。

 

结论：

现有索引和半宽索引都不太合适。三星索引虽然最好，但宽索引也足够好了，且不会引起额外问题。

 

维护成本考虑：

将索引升级为宽索引，理论上应该添加的是CITY、CNO；但实际上，CNO,CITY顺序更好。因为CITY还有可能变换，但这个顺序变化并不影响这个SELECT的运行效果，因为CITY是用来过滤的。

 

同样，当有多个等值谓词做匹配时，经常变化的列尽量放在后面。因此，LNAME,CITY更合适。另一方面，如果是建立新索引，还要考虑新索引对其他SELECT的帮助。已经有一个LNAME开头的索引的话，新的用CITY,LNAME更好。CITY虽然相对LNAME变化频繁，但也实际是很少的。这样就有了多样性。所以，这里有很多的权衡，不是按一个规则做下来就行的。

 

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

复杂例子2

查询语句：

SELECT CNO, FNAME

FROM CUST

WHERE CITY=:CITY

AND LNAME BETWEEN :LNAME AND :LNAME2

ORDER BY FNAME

 

索引1：CITY

索引2：LNAME,FNAME

 

假设

1. CITY的过滤因子是10%
2. LNAME过滤因子也是10%（比上一个例子大了10倍）

 

最大结果集：100W*10%*10% = 1W

 

CITY索引：   TR=1   TS=10000000*10%=10W

表           TR=10W  TS = 0

FETCH 10000*0.1ms

LRT=10W*10ms+ 10W*0.01ms+10000*0.1ms = 1000s+1s+1s=1000s

 

LNAME,FNAME索引   TR=1   TS=10W

表CUST              TR=10W    TS=0

 

LRT = 1000s。

 

两个索引都不咋地。

 

考虑事务的最佳索引

因为有范围谓词，所无法实现三星索引。考虑候选A/B

 

候选A

等值：CITY

范围：LNAME

排序：FNAME

其他SELECT：CNO

 

索引  TR=1   TS=100W*10%*10%=1W

表=   不访问

FETCH  相等，不计算

 

LRT = 1*10ms+1W*0.01ms=10ms+100ms=110ms

 

候选B

等值：CITY

排序：FNAME

其他：LNAME，CNO

 

索引  TR=1   TS=100W*10%=10W

表=   不访问

FETCH  相等，不计算

LRT = 1*10ms+10W*0.01ms=10ms+1s=1s

 

所以，A方案好。

 

但是，因为没计算Fetch，看上去A方案是B方案1/10开销。实际加上FETCH，A方案为1S，B方案为2S，就没那么大优势了。所以，FETCH不算，只能是定性，定量还是要记上的。

 

 

再考虑在现有索引添加，构成最大化过滤（半宽索引）

 

CITY，LNAME

索引    TR=1  TS=100W*10%*10% = 1W

表      TR=1W  TS=0

FETCH 1W*0.1ms

LRT = 1W*10ms +1W*0.01ms+1W*0.1ms = 100s+100ms+1s =101s

 

LNAME，FNAME，CITY

索引    TR=1  TS=100W*10%=10W

表      TR=10W*10%=1W  TS=0

FETCH

LRT=1W*10ms+10W*0.01ms+1s=100s+1s+1s=102s

 

两个差不多，回表都有1W，考虑减少回表

 

再考虑全宽索引（不回表）

 

在半宽上加

 

CITY,LNAME,FNAME CNO

索引  TR=1  TS=100W*10%*10%=1W

FETCH=1s

LRT=1*10ms+1W*0.01ms+1s=10ms+100ms+1s = 1s

 

LNAME,FNAME,CITY CNO

索引  TR=1   TS=100W*10%=10W

FETCH=1s

LRT= 1*10ms+10W*0.01ms+1s = 10ms+1s+1s=2s

第二个的索引片厚。