数据库索引设计与优化03-影响索引设计过程的因素

I/O时间估算的验证

在前面我们引入了以下I/O时间 :
随机读取 10ms(页的大小为4KB或8KB)
顺序读取 40MB/s

这些数据是假定系统使用当前硬件并在一个合理的负载下运行时的值。一些系统可能运行的更慢或处理超负荷状态,所以,进行下面这些检验可能是有用的。为此,我们需要建一张表,该表有100万行记录,每行记录的平均长度为400字节。根据假定的顺序I/O时间计算,以顺序I/O读取400字节长度的行所花费的时间为 400byte / 40MB/s = 0.01ms。

于是,进行如下扫描所消耗的时长便是确定的(括号中的数据库是根据在前面使用的估算数据来预测的)

  • 由一个所有行都不满足条件的select查询引起一次全表扫描(1 10ms + 1000000 0.01ms ≈ 10s)
  • 一次涉及1000次fetch调用的索引扫描,使用如(lname,fname)这样的索引,导致1000行索引片扫描和1000次对表的同步读(1000 0.01ms + 1000 10ms = 10ms + 10s ≈ 10s)。

我们有必要以这样的方式进行测量,即保证在程序刚开始的时候,查询所需要的页不在内存或者读缓存中。如果系统运行的相当繁忙,那么两次测量之间间隔几个小时可能就达到这一目的了。

如果测量出的时间比预计的时间长很多,那么便需要明确造成这一后果的主要原因 : 磁盘设备或路径较慢?还是磁盘超负载运行?

无论这些测出的数字是乐观或悲观,作为相对性能指标,他们是有用的。

多个窄索引片

通常情况下,我们为一个select语句设计一个索引(而不是反过来),但有时候为了让数据库管理系统能够以最高效的方式使用一个已经存在的索引,对一个SQL语句进行重新设计也是可取的。因为优化器并不知道一切!

在where子句包含两个范围谓词时,一个完美的优化器能够构造出一个由多个窄索引片组成的访问路径。如下

select cno, fname
from cust
where lname between :lname1 and :lname2
      and
      city between :city1 and :city2
order by fname

因为一个范围谓词是索引匹配过程中的最后一个匹配字段,所以让数据库管理系统读取多个窄索引片(有lname和city共同定义)需要额外的程序逻辑。谓词条件lname between :lname1 and :lname2必须被修改为lname = :lname。这可以通过一个触发器维护的辅助表来完成,该表包含客户表中lname列的所有不同的值。当用户输入字符串JO时,程序便从辅助表中每次读取一个满足条件的lname,并用每个lname值打开下面的sql。

select cno, fname
from cust
where lname = :lname
      and
      city between :city1 and :city2
order by fname

现在,即使是一个没有完美优化器的数据库管理系统也将会从索引(lname, city, fname, cno)中读取多个窄片。响应时间将取决于索引片的数量以及每一个索引片中的行数。

使用如下假设对比这两种选择

  • cust表中有100万行记录
  • lname最坏情况下的过滤因子为1%
  • city最坏情况下的过滤因子为10%

被第一条sql扫描的索引片由10000行索引记录组成(MC = 1;1000000的1%为10000),其中只有10%的city值(1000行)是满足需要的。

同时假设此索引片中有50个不同的lname值,用第二个sql优化过的程序将会读取50个索引片(平均每个索引片由20行记录),这1000行中的每一行都是满足查询条件的city。

根据QUBE(鉴于只是单纯的做比较,我们忽略fetch所带来的时间开销,两个游标的时间开销为)
cursor 1 1 * 10ms + 10000 * 0.01 ≈ 0.1s
cursor 2 50 * (1 * 10ms + 20 * 0.01) ≈ 0.5s

这有些让人吃惊,但却是合理的。顺序处理的确有时比跳跃处理要来得高效,原因是每一次跳跃都可能以为着一次随机访问。当数字不同时,用优化后的sql有可能会快很多。

为了证明最后说的观点,我们来考虑一个场景。该场景有一张更大的表(100000000行记录),且第一个索引列拥有较高的过滤因子。如下

select cno, fname
from cust
where lname between :lname1 and :lname2      FF max 10%
and
city between :city1 and city2      FF max 0.01%
order by fname

结果集的大小将会是0.01% 10% 100000000 = 1000行。假设有 一个索引(lname, city, fname, cno)且lname在1000行结果集中有50个不同的值。现在,简单程序(两个between)的时间花费估算结果(一个宽索引片)为
1 * 10ms + 10000000 * 0.01ms ≈ 100s
然而复杂程序(50个索引片)的估算结果仍旧为
50 * (1 * 10ms + 20 * 0.01ms) ≈ 0.5s

简单就是没(和安全)

使用辅助表来保存lname不同值的解决方案是一个典型的将数据完整性和性能进行折中的例子。理论上,使用一个触发器来维护辅助表是没有数据完整性的风险的。然而,触发器是应用程序,是程序就不会是完美的。当 alan jones的记录从客户端删除且表中还有另一个jones的记录时,是否能完全确定触发器不会同时把jones的记录从辅助表中删除呢?只有当量化估算不这样做性能就无法接受时,我们才考虑采用这样的调整手段。有时,这样的调整方式反而会对性能产生负面影响。

困难谓词

定义 : 如果一个谓词字段不能参与定义索引片,即它无法成为一个匹配谓词,那么我们就说它对优化器而言太困难了。

like谓词

数据库管理系统可能因为like谓词不参与匹配过程而扫描整个索引,那么访问路径将会变得很慢。

所以,只有在已知优化器不会造成问题的前提下,才应该使用谓词like

or操作符和布尔谓词

即使是简单的谓词,如果他们与其他谓词之间为or操作,那么对于优化器而言他们也可能是困难的。这样的谓词只有在多索引访问下才有可能参与定义一个索引片。

下面的例子将明确这一点。假设在table上有一个索引(A, B)
SQL 6.4 A

select A, B, C
FROM TABLE
WHERE A > :A
      AND
      B > :B

SQL 6.4 B

select A, B, C
FROM TABLE
WHERE A > :A
      OR
      B > :B

在SQL 6.4 A中,单个索引片将会别扫描,由谓词A > :A定义,因为他是个范围谓词,所以他是唯一的一个匹配字段。B字段因为索引筛选将会在此次扫描中被检查,如果谓词B > :B不满足条件,则索引条目将会被忽略。

在SQL 6.4 B中,由于OR操作符的存在,数据库管理系统不能只读这一个索引片,因为即使不满足谓词A > :A,谓词B > :B的条件也可能会满足。可行的替代方案有全索引扫描,全表扫描,以及多索引访问(如果存在另一个以字段B开始的索引的话)。

在这个例子中,我们不能责怪优化器。有时候一个拥有复杂where子句的游标可能被拆分为多个带有简单where子句的游标来避免此类问题。大体上,假设一个谓词的判定结果为false,而这时如果不检查其他谓词就不能确定地将一行记录排除在外,那么这类谓词对优化器而言就是太过困难的。在SQL 6.4 B中,仅仅因为不满足谓词A > :A或仅仅因为不满足谓词B >:B是无法排除一行记录的。在SQL 6.4 A中,如果一行记录不满足谓词A > :A,那么该条记录就可以被排除而不必检查它是否满足谓词B > :B。

这样的谓词被称为非布尔谓词(non-Boolean term)或被称为非BT谓词(non-BT)。如果像SQL 6.4 A那样在where子句中只有AND操作符,那么所有简单谓词都是BT谓词,就是好的谓词,因为只要任何一个简单谓词被判定为不满足条件,那么这一行就可以被排除。

总结

除非访问路径是一个多索引扫描,否则只有BT谓词可以参与定义索引片。

我们来考虑一个定义身材高大的男人的where子句
SQL 6.5

where sex = M
      and
      (weight > 90 or height > 190)

只有一个简单谓词sex='M'是BT谓词。如果weight谓词判定为false,hright谓词的判定结果可能为true也可能相反。这两个谓词都无法仅通过自己的判定结果来排除一行记录。

这就是为什么我们在用理想索引设计方法来设计候选索引A和B时提出了这样的结论 : 只将那些对优化器而言足够简单的谓词添加至索引上。对于SQL 6.5而言,在设计方法的第一步中,只有SEX字段可以被用到。

in谓词

谓词col in (:hv1, :hv2 ...)可能会给优化器制造一些麻烦。例如,在DB2 for z/OS V7中,只有一个IN谓词能够被用来做匹配,即能够参与定义索引片,不过参与匹配的IN谓词中的字段不一定是最后一个匹配字段。因此,当理想索引A被选中时,选择性最高的IN谓词的字段应当在第一步就包含进索引。SQL 6.6提供了一个例子。
SQL 6.6

SELECT A, B, C, D, E
FROM TX
WHERE A = :A
      AND
      B IN (:B1, :B2, :B3)      FF = 0...10%
      AND
      C > 1
      AND
      E IN (:E1, :E2, :E3)      FF = 0...1%

下面的候选A中的数字对应于我们在前面描述的步骤

候选A

  1. 挑选出字段A和E作为索引的起始字段
  2. 加上字段C
  3. 无字段
  4. 加上字段B和D

候选索引A为(A, E, C, B, D)或者以下变化形式中的任何一个

  • (A, E, C, D, B)
  • (E, A, C, D, B)
  • (E, A, C, B, D)

对于SQL 6.6而言,所有这些候选索引都满足MC = 3。数据库管理系统读三个索引片(A, E1, C),(A, :E2, C)和(A, :E3, C),而无需使用其他任何特殊的方式。字段B的IN谓词将参与筛选过程。如果需要有4个匹配字段(即9个索引片),那么我们很可能需要把SELECT语句拆分成三个,每个拆分出的语句只有IN谓词字段列表中的一个字段。第一个SELECT语句包含谓词B = :B1,第二个为B = :B2,第三个为B = :B3。这三个select语句可以通过一个union all再结合为一个游标。对这个游标进行explain将显示三次MC = 4。

候选B

IN谓词的字段不应该在第一步就被选择出来,否则这将破坏结果集的顺序要求,从而必须进行一次排序操作了。

过滤因子隐患

贯穿本书,我们关注于最糟糕的场景,这些场景都是在输入值对应于最大过滤因子时出现的。然而,情况并不总是如此,如下
SQL 6.7

SELECT B
FROM TABLE
WHERE A = :A      FF = 1%
      AND 
      C > :C      FF = 0...10%
ORDER BY B
WE WANT 20 ROWS PLEASE

在这个语句中,只要程序获取了20行记录,屏幕将会把数据展示给用户,而不管整个结果集的大小是多少。

为了解释过滤因子对性能的隐式影响,我们来比较一下过滤因此处理两种极端情况下的QUBE结果。第一种极端情况下提供可能的最小结果集,即0行记录;第二种则提供很大的结果集(1000行记录)。这两种场景下使用都使用索引(A,B,C)。两种场景下,匹配字段A都拥有相同的过滤因子,结果集的大小完全取决于字段C。

先考虑下第二种场景,通常我们认为这将带来最糟糕的性能。一次索引匹配扫描(1 MC,只需要扫描索引而无需访问表)将访问一个包含20 * 10行记录的索引片,因为必须扫描10个索引行才能提供出结果集中的一行记录(字段C的FF为10%)

索引      (A, B, C)            TR = 1      TS = 200
表        TABLE                TR = 0      TS = 0
提取      20 * 0.1ms
LRT                            TR = 1      TS = 200
                               1 * 10ms    200 * 0.01ms
                               10ms + 2ms + 20 * 0.1ms ≈ 14ms

在第一种场景下,没有记录满足字段C的谓词条件,所以过滤因子为0%。当然,数据库管理系统必须扫描索引行中的每一行,即1000000的1%才会知道这种情况。

索引      (A, B, C)            TR = 1      TS = 10000
表        TABLE                TR = 0      TS = 0
提取      0 * 0.1ms
LRT                            TR = 1      TS = 10000
                               1 * 10ms    10000 * 0.01ms
                               10ms + 100ms + 0 * 0.1ms ≈ 110ms

很明显,对于整个事务而言,最差场景是结果集为空的场景,或者额需要扫描整个索引片才能得出结果集的场景。更糟糕的是,如果这个案例不能做到值扫描索引或者对应的索引不是聚簇索引,那么就需要进行额外的10000次随机读取,而这将消耗近两分钟时间,结果却是发现没有一行满足条件的数据。

如果索引匹配扫描过程中扫描的是一个更厚的索引片,比如字段A的过滤因子为20%,那么即便只需要扫描一个索引,LRT也将达到2s(200000 * 0.01ms)!当查询结果只需要一屏幕的数据时,满足第一颗星(最小化待扫描的索引片厚度)比满足第二颗星(避免排序)更为重要。

当以下三个条件同时满足时,这种过滤因子隐患可能会产生 :

  1. 访问路径中没有排序
  2. 第一屏结果一建立就回应
  3. 不是所有的谓词字段都参与定义带扫描的索引片--换句话说就是,不是所有的字段都是匹配字段。

这个例子满足了所有这三个条件

  • 访问路径中没有排序,因为字段B(Order By字段)紧接在等值谓词字段A后面,并且字段A是索引的第一个字段。
  • 第一屏结果一建立就响应
  • 一个谓词字段(字段C)不参与定义待扫描的索引片

我们应该充分理解这三个条件背后的逻辑

  • 如果条件1和条件2不为真,那么完整的结果集表总是会被数据库管理系统或应用程序(通过进行FETCH操作来物化)。
  • 如果条件3不为真,就不存在索引过滤(因为所有的谓词字段都是匹配字段),索引片中每一个索引行都是满足条件的。即使结果集为空,数据库管理系统也只需要通过一次访问就能判断出这一情况。

过滤因子隐患的例子

在这个例子中,用户在开始查询前首先在一个弹出窗口中选择姓(lname)和城市(city),随后打开SQL 6.8。我们之前分析这个例子时需要的是整个结果集,而在这个例子中,程序发起的FETCH调用不会超过20次,也许刚好够填满一个屏幕。下一个查询事务将显示接下来的20条结果。以此类推。这种方式使程序变得复杂,但当结果集很大时,这种方式提升了第一屏结果的响应速度。和之前一样,我们将最大的过滤因子假设为1%(lname)及10%(city)。
SQL 6.8

SELECT CNO, FNAME
FROM CUST
WHERE LNAME = :LNAME
      AND
      CITY = :CITY
ORDER BY FNAME
WE WANT 20 ROWS PLEASE

表上有三个索引 : (CNO)(聚簇索引),(PNO),(LNAME, FNAME)

是否有一个已经存在的或者计划创建的索引,包含了where子句中涉及的字段(一个半宽索引)?

如果我们决定使用索引(LNAME, FNAME),那么问题将严重到何种程度?最糟情况下的QUBE结果将回答这个问题。

当数据库管理系统选择索引(LNAME, FNAME)时,结果行是按照所要求的顺序获取的(ORDER BY FNAME),也就不需要进行排序操作。因此,数据库管理系统不会做早期的物化 : OPEN CURSOR不会引起表访问,不过FETCH操作将会同时对索引和表进行访问,其中的一些FETCH操作会返回结果行,而另外大部分的FETCH操作则不会。

最先发生的两次访问(一次对索引,一次对表)产生了一行结果行记录,因为第一个JONES生活在LONDON。在第一次FETCH调用满足后程序便发起了第二次FETCH调用。下一个JONES并不生活在LONDON,所以没有产生结果行,于是会继续进行更多的对索引及表的访问,知道生活在LONDON的JONES被找到以满足FETCH调用。因为只有10%的用户生活在LONDON,所以平均一个FETCH调用需要检查10个JONES条目--10次索引访问及10次表访问才能将一行记录加入到结果表中。

索引      LNAME, FNAME            TR = 1      TS = 199
表        CUST                    TR = 200    TS = 0
提取      20 * 0.1ms
LRT                               TR = 200      TS = 199
                                  200 * 10ms    199 * 0.01ms
                                  2s + 1.99ms + 20 * 0.1ms ≈ 2s

根据QUBE得出的本地响应时间是2s,也许还是可以接受的。但这就是最糟糕的情况了么?假定过滤因子CITY = :CITY为10%,LANME = :LNAME为1%已经是最大值了。然而,在这个例子中,产生最大结果集的过滤因子值并不是最糟糕的情况,本例是过滤因子陷阱的另一个例子。过滤因子陷阱的三个条件都适用于索引(LNAME, FNAME)进行处理这个游标

  • 访问路径上没有排序
  • 第一屏结果一建立就返回结果
  • 不是所有谓词都参与了定义待扫描的索引片。

因此,为了创建一条“不存在符合条件的用户”的消息,所花费的时间比返回一个大结果集的第一页数据所花费的时间更长。

让我们考虑一种极端的情况,数据库管理系统必须扫描一个最大的索引片,但最终只产生了一个空的结果集。一个城市中,没有任何一个客户的姓是常见的姓,此时数据库管理系统仍旧必须扫描整个索引片,并对每一个索引进行相应表行的检查。

索引      LNAME, FNAME            TR = 1          TS = 1000000 * 1%
表        CUST                    TR = 10000      TS = 0
提取      0 * 0.1ms
LRT                               TR = 10001      TS = 10000
                                  10001 * 10ms    10000 * 0.01ms
                                  100s + 100ms + 0 * 0.1ms ≈ 100s

将近2分钟而不是2秒钟!索引必须要改善,此时我们将再一次面对那个难题 : 是选择刚好够用的最低成本的改造,还是设计一个最佳索引,又或是设计一个折中的方案?

最佳索引

(LNAME, CITY, FNAME, CNO)

索引      LNAME, CITY, FNAME, CNO            TR = 1          TS = 20
表        CUST                               TR = 0          TS = 0
提取      20 * 0.1ms
LRT                                          TR = 1          TS = 20
                                             1 * 10ms        20 * 0.01ms
                                             10ms + 0.2ms + 20 * 0.1ms ≈ 12ms

以上对应的是在最糟糕场景下使用候选A索引的情况 : 至少有20行结果记录。在使用理想索引的情况下,当整个结果集无法显示在一个屏幕时,建立一个有20行记录的结果表需要21次索引访问。使用索引(LNAME, FNAME)与使用索引(LNAME, CITY, FNAME, CNO)的最糟输入相应时间相差约4个数量级。

如果结果集表为空,那么使用这个三星索引只需要一次索引访问就可以判定出来。从定义上能看出,三星索引不满足陷阱发生条件列表中的第三条。

然而不幸的是,一个三星索引意味着额外增加一个索引,因为在现有的索引中,LNAME和FNAME之间增加一个CITY字段可能会对现有程序造成负面影响。

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

在现有索引(LNAME, FNAME)的最后增加谓词字段CITY可以消除绝大多数的表访问,因为在这个简单的场景中,优化器可以进行索引过滤,仅当CITY值符合条件时才读取表行。

索引(LNAME, FNAME, CITY)能够通过BQ测试 : 所有的谓词都在索引中。然而,他仅仅是一星索引。符合条件的的索引行在物理上并不挨在一起(MC仅为1),并且索引不是宽索引。那这个廉价的解决方案是否能提供可接受的响应时间呢?

现在是会用这个索引的游标落入了过滤因子缺陷的范畴。因此我们必须估算使用索引(LNAME, FNAME, CITY)时过滤因子的极限值。其中一个过滤因子将导致这种访问路径下最糟糕的性能。

最大结果集(例如1000行)

FF (LANE = :LNAME) = 1%
FF (CITY = :CITY) = 10%
FF (LANE = :LNAME and CITY = :CITY) = 0.1%

因为CITY的过滤因子为10%,所以每10个索引行满足一次谓词判定CITY = :CITY。所以为了找出一行满足条件的结果记录,平均需要访问10次索引。

索引      LNAME, FNAME, CITY                 TR = 1          TS = 200
表        CUST                               TR = 20          TS = 0
提取      20 * 0.1ms
LRT                                          TR = 1          TS = 200
                                             21 * 10ms        200 * 0.01ms
                                             210ms + 2ms + 20 * 0.1ms ≈ 214ms

空结果集

FF (LANE = :LNAME) = 1%
FF (CITY = :CITY) = 0%
FF (LANE = :LNAME and CITY = :CITY) = 1%

索引      LNAME, FNAME, CITY                 TR = 1           TS = 10000
表        CUST                               TR = 0           TS = 0
提取      0 * 0.1ms
LRT                                          TR = 1          TS = 10000
                                             1 * 10ms        10000 * 0.01ms
                                             10ms + 100ms + 0 * 0.1ms ≈ 110ms

宽索引(只需访问索引)

向上述半宽再添加一个字段,使得索引变宽 : (LNAME, FNAME, CITY, CNO)

最大结果集(例如1000行)

索引      LNAME, FNAME, CITY, CNO                 TR = 1          TS = 200
表        CUST                                    TR = 0          TS = 0
提取      20 * 0.1ms
LRT                                               TR = 1          TS = 200
                                                  1 * 10ms        200 * 0.01ms
                                                  10ms + 2ms + 20 * 0.1ms ≈ 14ms

空结果集

索引      LNAME, FNAME, CITY, CNO                 TR = 1           TS = 10000
表        CUST                                    TR = 0           TS = 0
提取      0 * 0.1ms
LRT                                               TR = 1          TS = 10000
                                                  1 * 10ms        10000 * 0.01ms
                                                  10ms + 100ms + 0 * 0.1ms ≈ 110ms

在空的结果集的场景下,本地响应时间保持在0.1s。从一个多屏结果集中产生一个屏幕结果集的事务现在变得非常快,因为不再需要对表进行20次TR了。

总结

  1. 性能最差的场景取决于使用的索引

    • 对于原始索引而言,在结果集为空时性能最差(最常见的LNAME减伤最不常见的CITY)
    • 对于半宽索引而言,虽然我们的例子中是结果集表最大时性能最差,但性能最差的场景可能是这两者(结果集为空和结果集表最大)中的任何一个,这取决于表访问和索引片扫描的相对成本。(根据QUEB)20次TR所耗费的是时间(200ms)等于20000次TS所耗费的时间(200ms)。若每屏的行数减少,或索引片更厚,那么结果集为空就可能称为性能最差的场景。
    • 对于宽索引而言,结果集为空时性能最差。
    • 对于三星索引而言,结果集最大时性能最差(结果集为空时只需要进行一次索引访问)。
  2. 原始索引在结果集为空时(而不是结果集最大时)称为一个灾难。这是因为它需要进行大量的表访问来校验CITY字段。
  3. 半宽索引及宽索引都能提供还不错的性能,即使是在结果集为空的情况。两个索引都避免了不必要的表访问,并且索引片扫描的代价也相对较小。随着由字段LNAME定义的索引片的数据增长,本地响应时间的增长也相对缓慢。如果一个公司拥有很多来自South Korea的顾客,那么有一天LEE的索引片可能包含100000索引行。在结果集这么大的情况下,这两个索引依旧能够提供比较不错的响应时间;最差的输入值所带来的响应时间将会是1s(100000*0.01ms)。
  4. 相比使用半宽索引,宽索引的优势在于避免了前20行结果的表访问。
  5. 尽管使用三星索引可能会得到一些好处,尤其是在结果集为空的情况下,但这一好处并不明显,并且会引入由于添加了一个新索引而产生的额外负载。
  6. 如果在LEE的索引片变得非常厚,一个三星索引可能更适合。不过存在一个上限,大约是100000次TS,这时扫描索引片将耗费太长时间以致我们需要创建一个新的索引。对于本例而言这个新的索引就是三星索引。

对于本次分析中我们所考量的各种索引的总结如下所示。

类型索引LRT维护
现有索引LNAME,FNAME100s-
半宽索引LNAME, FNAME, CITY0.2sU CITY + 10-20ms
宽索引LNAME, FNAME, CITY, CNO0.1sU CITY + 10-20ms
三星索引LNAME, CITY, FNAME, CNO0.01sI/D + 10ms U + 10-20ms

在之前的例子中,我们一直心照不宣地假定结果集为空的情况是导致性能最糟糕的场景。然而,我们应该意识到,一个几乎为空的结果集将导致更糟糕的性能,因为在使用半宽的索引的情况下,这将引起不超过20次的表访问。

练习

架设一张表,表中有10000000条记录,表上有索引(A)和(C),(A)为聚集索引
SQL 语句如下,在当前的索引条件下,这个sql执行需要花费一分钟。

SELECT C,E,F
FROM TABLE
WHERE B = :B
      AND
      C > :C
      AND
      D = 1
ORDER BY C

练习 6.1

用两种方案设计最佳的可能索引 : (1)不增加额外的第三个索引,(2)增加第三个索引
假设C的过滤因子为X,结果的行数为Y,则QUBE结果如下,可以看出,主要是回表产生的随机读很多,想办法把这部分消除掉,就能有很大提升

索引      C                 TR = 1                         TS = 10000000 * x
表        CUST              TR = 10000000 * x + 1          TS = 0
提取      Y * 0.1ms
LRT                         TR = 10000000 * x + 2          TS = 10000000 * x
                            (10000000 * x + 2) * 10ms        10000000 * x * 0.01ms
                            (10000000 * x + 2) * 10ms + 10000000 * x * 0.01ms + Y * 0.1ms ≈ 60000ms

(1)不增加额外的第三个索引

增加一个宽索引,相对于原索引,将会减少 100000000 * x + 10 毫秒,随着X(C的过滤因子)的不同,减少的时间会变化,过滤因子越大,优化的百分比越高。

索引      C, B, D, E, F                 TR = 1          TS = 10000000 * x
表        CUST                          TR = 0          TS = 0
提取      Y * 0.1ms
LRT                                     TR = 1          TS = 10000000 * x
                                        1 * 10ms        10000000 * x * 0.01ms
                                        1 * 10ms + 10000000 * x * 0.01ms + Y * 0.1ms ≈ ?
(2)增加第三个索引

增加一个三星索引, 假设B和D的过滤因子分别为Z,H,相对于原索引,随着B,D,C字段的过滤因子的不同,较少的时间会变化,过滤因子越大,优化的百分比越高

索引      B, D, C, E, F                 TR = 1          TS = 10000000 * x * Z * H
表        CUST                          TR = 0          TS = 0
提取      Y * 0.1ms
LRT                                     TR = 1          TS = 10000000 * x * Z * H
                                        1 * 10ms        10000000 * x * Z * H * 0.01ms
                                        1 * 10ms + 10000000 * x * Z * H * 0.01ms + Y * 0.1ms ≈ ?

练习 6.2

假设1s是我们可接受的执行时间,并且我们不愿增加一个新索引。对于上一题中的第一个方案,你需要了解哪些信息来语句它所能带来的性能提升?
需要了解C的过滤因子和符合筛选条件的记录的总条数

标签: mysql, mysql索引

添加新评论