到目前为止，我们讨论的都是相对简单的SQL语句。在我们讨论如关联查询，子查询，合并查询，星型查询和多索引访问时，有必要先讨论一下如何监视SQL，并从被动式响应的角度考虑索引设计中需要考虑的问题。

被动式的方法与莱特兄弟创造守架飞机的经历非常相似。本质上就是把查询放在一起，推下悬崖，然后看他能否起飞。换句话说，就是为应用设计一个没有索引的原型，然后开始运行一些查询。又或者，创建原始索引集，然后通过运行应用来看那些索引被用到，那些没有被用到。即使是一个小型的数据库系统，运行速度慢的查询也会被很快凸显出来。

被动式调优的方法也被用来理解和调优一个性能没有满足预期的已有应用。

在产品投入大规模使用后才对应用的大部分索引进行调优工作，就像是将一个载满乘客的大型客机推下悬崖。这可能会有一个好的结果，但是数据库员工必须做好快速响应的准备。如果没有人在应用开发阶段关注索引设计的话，一些程序可能会在应用投入后变得非常慢。

我们将会讨论在应用投入生产环境后的最初几天需要用到的性能工具和技术。这些工具和技术对于之后能够轻松地调优也是有用的，以在用户觉察之前发现性能问题，或者至少在问题变得令人无法忍受之前发现他们。在这种场景下，我们实际上是主动的而非被动的。

explain描述了所选择的访问路径

识别可疑的访问路径是相当容易的，尤其是当explain的输出结果被存储在表中的情况下，因为这使得获取解决过变得容易。这就是为什么对于优化器选择了可以访问路径的SQL，分析过程通常从索引优化开始。通过explain能够快读发现以下这些性能警示信号。

全表扫描或全索引扫描

对结果集排序

除了全表扫描和全索引扫描，结果集的排序就是最有用的警示信号了。引起排序的原因可能有以下两种

1. 没有可使查询语句避免排序的索引
2. 优化器所选择的访问路径包含了一次多余的排序

对于第一种情况，可以通过优化索引来避免，第二种后面再讨论。

通常，排序是没有害处的。例如，某应用可能有以前个不同的查询语句，其中几百个在他们的访问路径中都有一次排序，这几百个语句中的90%可能都有非常出色的性能，排序对其响应时间值贡献了很不明显的一部分。因此，大量错误的警告可能会使这种检查方法有些无用。

有很多数据库顾问将排序视为敌人。我们认为，哪些强调随机I/O带来致命影响的顾问更值得信任。

成本估算

一些数据库管理系统的explain功能显示了优化器对所选访问路径的本地响应时间的估算，或至少显示了对CPU时间的估算。有些产品，如SQL Server 2000，显示了对访问路径中每一步的CPU及I/O时间估值。在一个简单的场景中，可能会有如下几步 :

1. 检索并读取索引行
2. 对指针进行排序
3. 检索并读取表行
4. 对结果进行排序

经验显示，过分的依赖优化器的成本估算是危险的。毕竟优化器生成成本估算仅仅是为了选择最快的访问路径。优化器生成的估值有时会给出错误的警示信号，而且还不会显示所有的警示信号，但这是一个用起来简单且快速的工具，他使得早期检查称为可能。因此，哪些成本估值异常高的SQL语句--可能是一个新应用中估值最高的前20个语句，应该一一检查一下，很可能不合适的索引使用或优化器问题就能被发现。

不幸的是，以下两个严重问题限制了使用成本估算方法的价值

1. 优化器所做出的的本地响应时间估算可能与实际相差很大
2. 当谓词使用绑定变量时(显然这是很普遍的)，优化器对过滤因子的估算是基于平均输入值的，或更差情况下，基于默认值。为了获取更有价值的最差情况估值，explain中的绑定变量必须用最差情况下的输入值来代替。这是一个需要应用知识的累人操作。

explain是分析优化器问题的一个不可或缺的工具。因为这正是存在的理由，而不仅仅被用于索引设计。就像机场安检人员不仅是在机场检查可疑乘客。

问题制造者和问题受害者

一个涵盖一周中高峰时段的尖刺报告可能包含100多个不同领域模块的数千个尖刺。在应用开发者的些许帮助下，一个数据库专家也许每周分析并修复5-10个模块。由于程序变更或不同用户输入，下一周的尖刺报告可能会显示新的问题模块。为了高效地利用有限的时间，仔细地对所分析的模块排定优先级是很重要的，事务的响应时间及执行频率并不是唯一的评定标准。

我们首先要区分的是问题制造者和受害者。如果一个事务独占了资源(也许是因为使用了不合适的索引)，那么会对其他事务造成明显的负面影响，进而导致这些事务也与独占资源的事务一同出现在异常报告中。

问题制造者所使用的资源会被包含进服务时间，而问题受害者会因为需要等待这些资源而受到影响，即排队时间受影响。着手优化的合理方式是考虑如何解决问题制造者导致的问题，而非受害者的问题

有优化空间的问题制造者和无优化空间的问题制造者

在决定将注意力从受害者转移到问题制造者上之后，我们需要区分的第二项就是有优化空间的问题制造者和无优化空间的问题制造者。不过我们可能没有时间去分析所有的问题制造者。有优化空间的问题制造者是指哪些能够通过改进所有来获得大幅性能提升的事务。

有优化空间的问题制造者有两个特征

1. 磁盘服务时间长。
2. 磁盘度大多是是对表页的读取。

若在同步读上耗费的时间占了本地响应时间的绝大部分，且有超过100个表页是从磁盘服务器上读取的，那么索引改进很可能会带来不错的效果。同样，如果对于索引页的同步读大于100次也不是一个好消息。

为了减少对索引的随机读次数，可以减少所需访问的索引数量，也可以减少所需访问的表的数量，这可以通过向表添加冗余字段的方式实现。这些改变并不比创建一个新的索引或像现有索引列上添加列更容易或更可控。

关机随机访问有一个例外，就是对一个厚索引片扫描可能会由于叶子页的分裂而变慢。这个文件很容易解决也相对容易避免，将在后面介绍。

有优化空间的问题制造者

分析有优化空间的问题制造者通常会读取许多表页。我们知道有三种读取表页的方式 :

1. 事务中的随机读(同步读 : SR)
2. 跳跃式顺序读
3. 顺序读(顺序预读)

通过分析见此报告，我们能够腿短出有优化空间的问题制造者主要是有用这三种方式中的那种方式读取表页的。

同步读(方式1)不与CPU的时间重叠，应用程序会停下来，等待锁清秋的页从磁盘服务器返回。尖刺报告会将该事务同步读取的页数连同这些同步读的平均等待时间一起显示出来。

异步读(方式2和方式3)是预读取，他的I/O时间与程序所花费的CPU时间是重叠的。在数据库管理系统向磁盘服务器请求后几页的同时，程序任然在处理数据库缓冲池中的页。程序可能会遇到没有页可供处理的情况，也可能不会遇到。如果发生了这种情况，那么这个等待时间会被记录进预读等待的计数器中。如果程序从未与发哦需要等待预读取页的情况，那么CPU时间便决定了程序的性能。于是，在这种情况下，预读等待的值为0.顺序扫描所花费的时间一部分被记录进SQL的CPU时间，一部分被记录进其他等待(对于任何CPU排队来说)。

在当前硬件下，以跳跃式顺序读的方式访问表页时，响应时间可能还是取决于本地响应时间中最主要的组成部分，因为这意味着包含满足条件的页彼此相隔很远。

而以顺序读方式访问表页时，在当前硬件条件下，CPU或I/O时间的主导地位基本各占一半，有时访问每页所花费的CPU时间比I/O时间长一些，有时又短一些。QUBE算法假设，I/O时间与CPU时间中较大者的上线为0.01ms每行。如果I/O时间决定了顺序读的时间，那么顺序扫描所花费的时间就等于尖刺报告中CPU时间与预读等待部分的总和。

许多程序使用上述方式中的两种或三种来访问表页。此时理解尖刺报告就没有那么简单了，不过主要的方法通常也不是那么难推理。访问的表页总数也许能够帮助我们理解报告。访问的表页总数除了同步读次数外，还包括了通过预读读取的页数(方式2和方式3)和缓冲池的命中数(在数据库缓存终止找到的表页数量)。

调优的潜在空间

在投入大量时间分析一个异常事务之前，值得花时间先去评估一下在索引优化之后本地响应时间能降低多少。调优的潜在空间就是指可实现的降低值的上限。

随机读

只读事务的调优潜在空间等同于读取的表页数和同步读的平均时长的乘积。如果通过使用宽索引避免全部表页的读取，那么本地响应时间就能减少以这个量。

跳跃式顺序读

在上述例子中，有些优化器可能会选择跳跃式顺序读。那么数据库管理系统将首先从所有满足条件的索引行中获取指针，然后再根据表页号对指针进行排序。此时，数据库管理系统就有了所有表页排序后的列表，每个表页都至少包含一条符合条件的记录。只有到这时，数据库管理系统才会开始访问表。在这种方式下，访问每个表页的I/O时间自然相对较短，尤其是当一些满足条件的行恰好都在同一个磁轨上时。另外，I/O时间将会与CPU时间重叠，因为数据库管理系统有条件进行预读了。相比同步读，这种方式所节省的时间是非常不确定的，具体取决于读取的页之间的平均距离以及条带的实现方式。这种方式的调优潜在空间即为当前预读等待的值。

顺序读

顺序扫描的响应时间通常取决于CPU时间。

这类问题，我们需要找到一个更窄的索引段或表段的访问路径，以达到调优的目的。

无优化空间的问题制造者

通常来说，对有大量SQL调用(如10000多次)的事务来说，如果CPU时间是本地响应时间的主要组成部分，那么这些事务是很难被优化的。

对于此类事务，调优潜在空间即为能够去除的SQL调用次数乘以每次SQL调用的基本CPU时间(如0.1ms)。

受害者

略

调用级别的一次监视

假设我们执行了一次高峰时段的跟踪，且基于该时间段内耗费时间的长短生成了最差SQL调用列表。现在，我们将着手处理单次调用而非均值。进一步假设我们所使用的工具对每次调用只提供了4个重要测算，他们是 : 耗费时间(ET)，CPU时间(CPU)，从磁盘读取的页数(READS)及数据库页的请求数(PR)。数据库页请求在DB2中被称为读取页，在SQL Server中被称为逻辑读，在Oracle中被称为读取或LIO。为了保持所有事物尽可能简单，我们使用一个能反映所有这些含义的简单术语-页请求(PR)。

术语

子集1(包含子集2和子集3) : ET > 200ms 慢查询
子集2(包含子集3) : ET/PR < 10ms 有可能的问题制造者
子集3 : PR / F < 5 有优化空间的问题制造者

从耗费时间(ET)最长的SQL调用开始，为了通过访问路径调优的方式改进响应时间，我们同样应该试着回答之前在LRT级别讨论过的两个问题。

首先，该SQL调用看起来是不是问题制造者？换句话说，过长的耗费时间是不是由于服务时间过长导致的？有几个方法可以回答这个问题。较高的CPU时间或较高的READS值都意味着较长的服务时间，但是，实际上，PR似乎是服务时间最有用的指示器，因为它与CPU和READS都相关，而且在很大程度上是经得起反复校验的，它不受系统负载或预热的影响。

为了识别那些可能是问题制造者的SQL调用，我们应该忽略那些PR值低的调用。如果一个查询耗费了1s且发送了少于100次页请求，那么它可能就是一个受害者。在可能情况下，每次页请求都会造成一次随机读，如果磁盘排队不过分的话，这将花去100 10ms = 1s。而在实际情况中，许多页请求在数据库缓冲池中就会被满足，另一些则会在磁盘服务器的读缓存中被满足。请记住，报告的PR值包含了非叶子索引页，另外，许多页请求是顺序的，100个页请求的期望I/O时间远比1s短得多，CPU时间的期望值也是如此。总而言之，如果一个包含100次页请求的SQL调用被测得的耗费时间是1s，那么可能它所耗费的排队时间(磁盘驱动排队，CPU排队,锁等待或其他等待)比服务时间长。通常，如果一个慢查询所耗费的时间比“页请求数 10ms”，那么这个查询很可能就是受害者。它属于子集1，但不属于子集2。

子集2是可能的问题制造者，它包含了哪些ET/PR时间不是很长的SQL调用，他们不是很可能的或者明显的受害者。然而，子集2可能也包含了许多受害者，比如进行顺序操作的SQL调用(在每页上所耗费的服务时间较短)会由于过度的CPU排序时间而变得比较慢。如果我们像对待LRT级别的尖刺报告那样关注所有的锁等待或磁盘，CPU排队时间，那么我们是能够过滤掉这些受害者中的绝大部分的。但实际情况是我们只有4个值，所以很不幸，子集2可能会很大。这也是为什么第二个问题很重要 : 该SQL调用是不是一个有优化空间的问题制造者？

为了找出有优化空间的问题制造者，即子集3，我们需要判断结果集的行数。如果读取(F)的数量包含在了异常报告中(或者该值很容易被取得)，那么我们可以把它作为结果行数的近似值。如果PR / F 大于5，那么这个查询很可能是一个优化空间的问题制造者，因为在好的访问路径下，对每张表的一次典型读取只需一或两次以内的页请求。造成PR / F大于5的原因很可能是有大量无用的随机访问，即许多行被访问后就被丢弃掉了。

子集3包含了由于没有半宽索引而引起大量随机读的查询，这些是优化回报率最高的调用。子集3同样包含一些有好的访问路径SQL调用(如使用宽索引对一个级联进行count查询)，但子集中的绝大部分查询可能都存在有趣的访问路径问题。

这些查询应该首先用Basic Question方法分析一下。这样可能立刻揭露出不合适的索引。紧接着下一步是explain一下这些sql调用，看优化器选择了什么路径。这样可能会揭露出一个优化器问题，如一个负复杂谓词(匹配列太少)或者一个较差的过滤因此估算，进而导致错误的索引选择或错误的表访问顺序。

CPU和READS值提供了对访问路径的有效指示器。例如，如果CPU接近ET，那么处理过程大部分是顺序的；如果CPU很低，ET/READS通常与每页的平均I/O时间接近；如果该值明显小于1ms，那么大部分的I/O是顺序的；如果该值为几毫秒，那么大部分I/O可能是随机的。这可能对执行计划所提供的信息进行补充。另外，任何高CPU值的SQL调用(如大于500ms)，都可以被认为是一个有趣的问题制造者。

分析过子集3中排在前几位的调用(至少有一次很长ET的调用和那些经常很慢的调用)后，我们再看一下可能的问题制造者子集2中最慢且调用最频繁的SQL调用。我们可能会找到这样的查询 : 即使使用了半宽索引却仍旧很慢，但能够通过一个宽索引大大提高性能的查询。

当时用更好的索引或者调整优化器的方式优化了一些SQL调用后，应该生成一个新的异常报告。索引的改善可能会帮助许多SQL调用，问题制造者及受害者，但是也有可能在新生成的问题列表中出现一些新的访问路径问题-主要是由于系统负载的改变，或者有时是由于索引的改变(幸运的是这并不经常发生)。

按照ET值排序，我们从上往下依次处理列表中的问题，那么要往下处理到第几个问题才能停下来呢？在一个操作系统中，响应时间极少超过1s。那么理想情况下，我们应该检查任何响应时间大于200ms的SQL调用。不幸的是，在实际生活中，我们可能永远做不到如此。有太多的SQL调用需要处理，而可用的时间太少了！

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，这时扫描索引片将耗费太长时间以致我们需要创建一个新的索引。对于本例而言这个新的索引就是三星索引。

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

在之前的例子中，我们一直心照不宣地假定结果集为空的情况是导致性能最糟糕的场景。然而，我们应该意识到，一个几乎为空的结果集将导致更糟糕的性能，因为在使用半宽的索引的情况下，这将引起不超过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的过滤因子和符合筛选条件的记录的总条数

前瞻性的索引设计

发现不合适的索引

一旦一个应用的明细方案确定下来，就应该确认当前的额索引对新的应用来说是否合适。为了完成这个工作，可以使用两种简单，快速并且可行的方法

1. 基本问题法(Basic Question, BQ)
2. 快速上线估算法(Quick Upper-Bound Estimate, QUBE)

基本问题法(BQ)

是否已有一个已存在的或者计划中的索引包含了where子句所引用的所有列(一个半宽索引)?

• 如果答案是否，那么我们应该首先考虑将缺少的谓词列加到一个现有的索引上去。这将产生一个半宽索引，尽管索引的等值匹配过程并不令人满意(一星)，但是索引过滤可以保证回表访问只发生在所有查询条件都满足的时候。
• 如果这还没有达到足够的性能，那么下一个选择就是将所有涉及的列都加到索引上，以使访问路径只需要访问索引。这将产生一个避免所有表访问的宽索引。
• 如果select仍然很慢，就应该用前面的候选索引算法来涉及一个新的索引。根据定义，这将是所能实现的最佳索引。

如何确认第一个方案(半宽索引)或第二个(宽索引)方案能否让select在最差输入下仍然运行的足够快?

如果可以访问生产库或者类似生产库的测试库，我们可以每次创建一个索引，用最差输入来测试响应时间。为了确保测得的响应时间接近于在正常生产库上允许的性能表现，我们必须把缓冲池考虑进来，并观察每个事务的缓冲池命中率。测试的第一个事务很可能在缓冲池中没有发现相应的缓存页，所以在最差输入下的磁盘读的指标会比正常环境要高。此外第一个访问索引的事务必须要等待文件被打开。而另一方面，如果变量的输入值保持不变，那么第二个访问改索引的事务将很可能会获得100%的缓冲池命中率(没有磁盘读)。为了获得具有代表性的响应时间，每个索引方案都应在进行过有预热事务后再开始测试，可以通过传入一些典型值(但不是最差情况的值)来打开文件，并将大部分非页子索引页加载到数据库的缓冲池中。这样，使用最差输入值的的事务就会有一个比较有代表性的响应时间了，至少我们已经把CPU和磁盘读的服务时间考虑进来了。

实际上，使用第二种方法，QUBE来评估索引方案将不会那么乏味。

如果模拟测试和QUBE方法都没有实施，那么就应当选择使用宽索引方案，并在应用切换到生成环境后立即启用异常报告来发现那些连宽索引都无法满足的性能要求的场景。如果必要的话，我们需要为那些运行缓慢的查询设计我们所能达到的最佳索引。

注意

对于BQ的一个肯定的回答并不能保证足够的性能。记住，BQ的目的是确保我们至少可以通过索引过滤来最小化对表的访问，除此之外没有其他作用。

举个例子，假设有一个select，谓词是where b = :b and c = :c，唯一有用的索引是(A, B, C)。这个索引包含了该select的所有谓词列，因此BQ方法检查这个select并不会产生告警。然而这个查询的访问路径将会是全表扫描。如果该表有超过100000条记录，那么查询将运行的非常慢。索引过滤本身并不意味着这个索引有三颗星中的任何一颗。

快速上限估算法(QUBE)

这个快速估算法输出的结果是本地响应时间(LRT)，即在数据库服务器中的耗时。在单层环境(指发出SQL调用的应用与数据库部署在同一台机器上)中，一个传统事务的LRT是指用户和数据库服务器之间一次交互的响应时间，不包括所有的网络延迟。在多层环境(客户端/服务器)中。各层之间的通信时间也被排除在外。批量任务的LRT是指执行任务耗时。任何任务队列中的排队时间也被排除在外。

服务时间

在简单的场景下(I/O时间和CPU时间不重叠)，服务时间等于CPU时间加上排除了磁盘驱动排队的随机读时间。如果没有资源竞争，则本地响应时间等于服务时间。

排队时间

在一个常规的多用户环境中，程序的并发会导致对所需资源的各种竞争，因此这些并发的程序不得不排队开获取这些资源。以下资源在LRT的范畴内 :

• CPU排队时间(所有处理器都忙着处理更高优先级的任务)
• 磁盘驱动器排队(包含请求页的驱动器处于繁忙状态)
• 锁等待(请求的表或行被锁定在一个不兼容的级别)
• 多道编程的级别，线程数或其他方面已经达到上限(这些都是为了防止资源过载而设计的系统值)

QUBE会忽略除磁盘驱动排队以外的其他所有类型的排队，以提供一个简单的评估过程，用于评估那些对性能影响特别大的方面。

在排除上述因素后，我们得到的是一个非常简单的估算过程，仅需两个输入变量，TR和TS，必要时还有第三个输入变量，F。这些就可以将SQL的处理及I/O成本考虑进来了，并且他们是影响索引设计的主要因素。如果是使用QUBE来比较多个可选访问路径之间的性能差异，由于FETCH调用的次数是相同的，所以可以忽略FETCH调用次数带来的影响。如果是使用QUBE来确定响应时间，那就需要包含FETCH调用的次数。

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

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

LRT = 本地响应时间
TR = 随机访问的数量
TS = 顺序访问的数量
F = 有效FETCH的数量

基本概念

访问

根据定义，DBMS读取一个索引行或一个表行的成本称为一次访问 : 索引访问或表访问。如果DBMS扫描索引或表的一个片段(被读取的行在物理上是彼此相邻的)，那么第一行的读取即为一次随机访问。对于后续行的读取，每行都是一次顺序访问。在当前的硬件条件下，顺序访问的成本比随机访问的成本低得多。一次索引访问的成本与一次表访问的成本基本上是相同的。

读取一组连续的索引行

物理上彼此相邻是什么意思?

索引上的所有行都通过指针链接在一起，链接的先后顺序由索引的键值严格定义。当几个索引行的键值相同时，就根据索引行存储的指针值进行链接。在传统的索引设计(从某个角度看，是理想化的)中，链表从LP1(叶子页1)开始，随后链接LP2，以此类推。这样(假设每个磁道可以放12个叶子页，当前的硬件通常可以容纳更多)，叶子页就组成了一个连续的文件，LP1至LP12存储在磁盘柱面的第一个磁道，LP13至LP24存储在下一个磁道，如此继续，当第一个柱面存满后，下一组LP就会被存储在下一个柱面的首个磁道上。换句话说，就是叶子页之间没有其他页。

现在，读取一个连续的索引行(即一个索引片，或者包含了单个键值或者一个范围的键值所对应的索引行)就非常快了。一次磁盘旋转会将多个叶子页读取进内存中，而且只有在磁盘指针移到下一个柱面时才需要进行一次短暂的寻址、

不过，这个完美的顺序还是会被打破的，至少有以下三个影响因素 :

1. 如果一个叶子页没有足够的空间存储新插入的索引行，那么叶子页就必须被分裂。之后链表仍会按照正确的顺序链接索引行，但是这与底层的物理存储顺序就不再一致了，一些按道理应该是顺序的访问就变成随机访问了。不过索引的充足可以再次恢复最理想的顺序。
2. 意向不到的数据增长可能会填满原本连续的空间(区或类似的概念)。操作系统于是就会寻找另外有一个连续的空间，并将它连接到原来空间的后面。这时候从第一个区跨到第二个区访问就会产生一次随机访问，不过这种情况影响不大。
3. RAID 5条带会将前几个叶子页存储在一个驱动器上，将后面的叶子页存放在另外的驱动器上。这就会产生额外的随机读，但实际上条带的积极作用要大过随机读带来的性能恶化，一个智能的磁盘服务器可以将后续的叶子页并行的从多个驱动器上读取至磁盘缓存中，从而大大降低了单个叶子页的I/O时间。此外，在RAID 5条带策略下，一个被频繁访问的索引的不太可能导致某一个磁盘负载过高，因为I/O请求会被均匀低分布到RAID 5阵列内的多个磁盘驱动器。

忽略上述情况，我们仍然假设，如果两个索引行在链表上彼此相邻(或者在唯一索引中，相同键值的行指针意味着彼此相邻)，那么我们就认为这两行在物理上也相邻。这就意味着QUBE认为所有的索引都有最理想的顺序。

读取一组连续的表行

读取一组连续的表行有如下两种情况。

1. 全部扫描
   从TP1(表页1)开始，读取该页上所有的记录，然后再访问TP2，一次类推。按照记录在表页中存储的顺序进行读取，没有其他特殊的顺序。
2. 聚簇索引扫描
   读取索引片上第一个索引行，然后获取相应的表行，再访问第二个索引行，以此类推。如果索引行与对应的表行记录顺序完全一致(聚簇率为100%)，那么除了第一次之外的所有表访问就都是顺序访问。表记录的链接方式跟索引不一样。单个表页中记录的顺序无关紧要，只要访问的下一个表记录在同一个表页或者相邻的下一个表页内就可以了。

同索引一样，存储表的传统方式也是将所有表页保留在一个连续的空间内。引起顺序杂乱或碎片化的因素也和索引中的相似，但又两个地方不同

1. 如果往表中插入的记录在聚簇索引所定义的主页中装不下，则通常不会移动现有的行，而是会将新插入的记录存储到离主页尽可能近的表页中。对第二个页的随机I/O会使聚簇索引扫描变得更慢，但是如果这条记录离主页很近，这些额外的开销就可以被避免，因为顺预读功能会一次性将多个表页装载到数据库缓存中。即使顺序预读功能没有使用，也只有当该页在数据库缓存被覆盖的情况下才会发生额外的随机I/O。
2. 一条记录被更新后，可能因为表行过长导致其无法再存储于当前的表页中。这是DBMA就必须将该行记录迁移至另外一个表页中，同时在原有的表页中存储指向新表页的指针。当该行被访问时，会引入额外的随机访问。

表可以通过重组来还原行记录的顺序，从而减少不必要的随机访问。因此，如果行记录存储在同一个页或者相邻的页当中，QUBE就认为他们在物理上彼此相邻。换句话说，QUBE假设所有的表，所有都是以最理想的顺序组织的。

之前我们做过一些最差场景的的假设，在这种假设下，QUBE足够简单，能够快速且方便的使用。同样的原因，我们也有必要做一些乐观的假设。尽管存在上述问题，根据QUBE的定义，扫描索引或表的一个片段只需进行一次随机访问。数据库专家们需要对重组的必要性进行监控，以保证我们所做的这些乐观假设都是合理的。

计算访问次数

既然访问对于QUBE而言如此重要，我们现在就解释下如何确定索引及表的访问次数，包括随机访问和顺序访问。

随机访问

我们首先思考一下磁盘读与访问的区别。一次磁盘读所访问的对象是一个页，而一次访问的访问对象则是一行。一次随机磁盘读会将一整页(通常会包含很多行)读取至数据库的缓冲池中，但是根据定义，前后两次随机读不太可能会访问到同一个页。因此，QUBE中单次随机访问所消耗的时间与一次磁盘随机读的平均耗时是一样的，都是10ms。虽然随机读是同步的，但是由于现在的处理器速度非常快，所以在估算单次随机访问开销时可以忽略CPU时间，这一CPU时间通常小于0.1ms。

顺序访问

一次顺序访问是指读取物理上连续的下一行，这一行要么存储在同一页中，要么在下一页中。由于顺序读的CPU时间与I/O时间是重叠的(DBMS和磁盘控制器通常会预读一些页)，因此顺序访问的消耗时间就是两者中较大的那个。在QUBE中，一次顺序读所消耗的时间是0.01ms。

当计算访问次数时，为了简单起见，我们会遵循以下规则:

1. 忽略索引的非叶子节点。我们假定他们都在数据库的缓冲池中，或者至少在磁盘服务器的读缓存中。
2. 假设DBMS能够直接定位到索引片的第一行(忽略为了定位索引行的位置而使用二分查找或者其他技术所耗费的时间)。
3. 忽略跳跃使顺序读所节省的任何时间。这可能是一个很悲观的假设。
4. 假设所有的索引和表都处于理想的组织顺序。如上所述，这可能是个很乐观的假设，除非表和索引都被很好的监控着。

当计算索引访问次数时，可以将索引看成一个微表，他的行数与其指向的表包含的行数相同，且按照索引键值排列。

当计算表访问次数时，我们假设表行在表页中是按理想顺序排序的，这个顺序依赖于表的组织方式。我们可以假设一次全表扫描(N行数据)将需要一次随机访问和N-1次顺序访问。

FETCH处理

被接受的行的数量可以通过FETCH调用的次数来确定(除非多行FETCH可用)，这些行将经历更多的处理程序。TS不包含这个额外的处理过程。现在，我们需要将第三个输入变量，LRT组成中的F考虑进来。F的成本比TS大一个数量级，而另一方面，他比TR的成本要小很多。

如果QUBE被用来比较可选的访问路径，比如比较全表扫描与使用特定索引，那么F参数是无关紧要的，因为他的值在两种情况下相等，我们只需要考虑TR和TS。但如果使用QUBE来确定LRT，F参数就可能会比较重要，这取决于FETCH调用的次数。

在处理被接受了的行时，可能会涉及排序操作，排序的整体开销通常与FETCH的行数成正比。大多数情况下，相对于在所接受的行上进行其他处理过程，排序的开销非常小，所以他被隐藏在F的开销内。然后有些场景却不是这样，这个将会在后面讨论。除了这些例外场景外，我们都假定排序开销包含在F参数中。

请注意，在计算FETCH调用次数时，为了避免混淆，我们将忽略“判断没有更多符合条件的行”的那次调用，这纯粹是为了简化计算。例如，一张有10000条计算的表上的一个过滤因子为1%，期望的返回行数是100，那么我们就假设F为100而不是101.

主要访问路径的QUBE示例

示例5.1 : 主键索引访问

SELECT CNO,LNAME,FNAME
FROM
WHERE CNO=:CNO

通过主键索引读取一个表行需要随机访问一次表和随机访问一次索引。

索引 CNO         TR = 1
表   CUST        TR = 1
提取 1 * 0.1ms
LRT        TR = 2
           2 * 10ms + 0.1ms ≈ 20ms

示例5.2 : 聚簇索引访问

SELECT CNO,LNAME,FNAME
FROM CUST
WHERE ZIP = :ZIP AND LNAME = :LNAME
ORDER BY FNAME

假设区号为(ZIP)30103的地区内有1000位名为Joneses的客户，通过这个二星索引的两个匹配列访问了一个薄的索引片。这里不需要排序，因为索引已经提供了所需的顺序。

扫描1000行的索引片需要多长时间?首先，需要进行一次随机访问来找到索引片上的第一条符合条件的索引行，然后DBMS继续向前读取，知道找到列ZIP和LNAME与输入值不匹配的行为止。包括访问的最后一个不匹配的行，索引的访问数是1000。因此，根据QUBE，扫描索引片需要1 10ms + 1000 0.01ms = 20ms。

因为列CNO不在索引中，所以必须从表中读取该列。由于索引是聚簇的，而且我们假设表中的1000行是相邻的，于是扫描表片段的成本将会是1 10ms + 999 0.01ms ≈ 20ms。此外，还有FETCH调用的时间，根据QUBE，总的响应时间为140ms。由于顺序访问的成本非常低，因此表的访问成本是非常低的。最大的开销来自DETCH调用的过程。

索引 ZIP,LNAME,FNAME TR = 1      TS = 1000
表   CUST = 1                    TS = 999
提取 1000 * 0.1ms

LRT     TR = 2      TS = 1999
        2 * 10ms    1999 * 0.01ms
        20ms + 20ms + 100ms = 140ms
        20ms + 20ms + 1000 * 0.1ms
        20ms + 20ms + 100ms = 140ms

示例5.3 : 非聚簇的索引访问

SELECT CNO,LNAME,FNAME
FROM CUST
WHERE ZIP = :ZIP AND LNAME = :LNAME
ORDER BY FNAME

如果表的记录不是通过聚簇索引访问的，那么表的1000次访问就会变成随机访问。

索引      ZIP,LNAME,FNAME      TR = 1      TS = 1000
表        CUST                 TR = 1000   TS = 0
提取      1000 * 0.1ms
LRT                            TR = 1001   TS = 1000
                               1001 * 10ms 1000 * 0.1ms
                               10s + 10ms + 1009 * 0.1ms ≈ 10s

在这种情况下，将列CNO加到索引中让索引称为三星索引会是个好方案，因为这样能使响应时间从10s降低至0.1s

索引      ZIP,LNAME,FNAME      TR = 1      TS = 1000
表        CUST                 TR = 0      TS = 0
提取      1000 * 0.1ms
LRT                            TR = 11   TS = 1000
                               1 * 10ms 1000 * 0.1ms
                               10ms + 10ms + 1009 * 0.1ms ≈ 120ms

示例 5.4 : 使用聚簇索引进行跳跃式顺序表访问

SELECT STREET, NUMBER, ZIP, BORN
FROM CUST
WHERE LNAME = 'JONES' AND FNAME = 'ADAM' AND CITY = 'LONDON'
ORDER BY BORN

5.4的查询将使用聚簇索引(LNAME,FNAME,BORN,CITY)。在这一索引条件下，要找到住在伦敦的所有名为Adam Joneses的客户，需要多少次随机访问和顺序访问呢？
假设LNAME和FNAME匹配之后的索引片大小为5

索引      LNAME,FNAME,BORN,CITY      TR = 1      TS = 4
表        CUST                       TR = 2      TS = 0
提取      2 * 0.1ms
LRT                                  TR = 3      TS = 4
                                     3 * 10ms    4 * 0.1ms
                                     40ms + 0.4ms + 2 * 0.1ms ≈ 30.4ms

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

实例1

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

假设有一张表，结构如下

create table `cust`(
`cno` varchar(8) primary key,
`pno` varchar(8) not null default '',
`fname` varchar(8) not null default '',
`lname` varchar(8) not null default '',
`city` varchar(8) not null default '',
key `idx_pno`(`pno`),
key `idx_lname_fname`(`lname`, `fname`)
)

再假设下其他的条件

1. 表中有100万记录
2. idx_lname_fname是唯一合适的索引
3. 谓词lname = :lname的最大过滤因子是1%
4. 谓词city = :city的最大过滤因子是10%
5. 结果集最多包含10000条记录(1000000 1% 10%)
6. 表中的记录按照cno列的大小顺序存储，主键索引cno是聚簇索引，或者表经常按照cno进行排序重组。

sql语句如下

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

该事务的基本问题

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

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

对事务上限的快速估算

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

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

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

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

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

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

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

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

通过BQ，QUBE(或者其他方法)，我们已经发现了这样一个事实 : 改SQL会由于没有合适的索引执行的非常慢。现在，我们面临一个困难的问题 : 我们应该给这个语句设计一个所能达到的最优索引，还是为期设计一个成本最低的满足性能要求的索引？又或是介于两者之间的某种索引？我们并没有一个硬性公式去解答这个问题。

该事务的最佳索引

使用前面描述的算法可以得到两个三星索引
(lname, city, fname, cno)或(city, lname, fname, cno)
这两个索引都有三颗星，因为他们会扫描一个非常薄的索引片，order by列跟在匹配列之后，从而避免了排序；此外，此查询只需要访问索引而无需回表。

索引      lname, city, fname, cno      TR = 1      TS = 999
表        CUST                         TR = 0      TS = 0
提取      1000 * 0.1ms
LRT                                    TR = 1      TS = 999
                                       1 * 10ms    999 * 0.01ms
                                       10ms + 10ms + 1000 * 0.1ms ≈ 120ms

在性能方面，这两个索引并没有什么区别，他们都极大的降低了成本。不幸的实际，三星索引将意味着额外增加一个索引，因为列(city)添加到现有索引的lname和fname之间可能会对现有的程序造成不利的影响。这就是为什么我们可以考虑一下开销更低的可选方案。

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

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

该索引(lname, fname, city)是满足BQ的，所有谓词列都在索引中。然而，他只有一颗星，所请求的索引行不是连续的，当然，他也不是宽索引。但是这种开销很低的方案是否满足需求了呢?

索引      lname, fname, city      TR = 1      TS = 9999
表        CUST                    TR = 1000   TS = 0
提取      1000 * 0.1ms
LRT                               TR = 1001   TS = 9999
                                  1001 * 10ms 9999 * 0.01ms
                                  10s + 100ms + 1000 * 0.1ms ≈ 10.2s

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

宽索引(只需访问索引)

将一个列添加到半宽索引上，将使改索引变为一个宽索引 : (lname, fname, city, cno)。现在，我们有了两颗星，第二颗和第三颗，同时LRT变为了1s。

索引      lname, fname, city, cno      TR = 1      TS = 9999
表        CUST                         TR = 0      TS = 0
提取      1000 * 0.1ms
LRT                                    TR = 1      TS = 9999
                                       1 * 10ms 9999 * 0.01ms
                                       10ms + 100ms + 1000 * 0.1ms ≈ 210ms

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

1. 插入和删除意味着修改一个叶子页。如果该子叶不在缓冲池或者读缓存中，那么整个叶子页就必须从磁盘驱动器上读取，无论锁添加或删除的索引行的长度是多少，这都将耗费10ms的时间
2. 更新city列会导致响应时间增加10ms或20ms，具体的值取决于是否需要将索引行迁移至另一个叶子页。通过将cust列作为最后一个索引列的方式可以避免这一移动。

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

重要说明

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

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

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

实例2

范围事务的BQ即QUBE

假设有一张表，结构如下

create table `cust`(
`cno` varchar(8) primary key,
`pno` varchar(8) not null default '',
`fname` varchar(8) not null default '',
`lname` varchar(8) not null default '',
`city` varchar(8) not null default '',
key `idx_pno`(`pno`),
key `idx_city`(`city`),
key `idx_lname_fname`(`lname`, `fname`)
)

再假设下其他的条件

1. 表中有100万记录
2. idx_lname_fname是唯一合适的索引
3. 谓词lname = :lname的最大过滤因子是10%
4. 谓词city = :city的最大过滤因子是10%
5. 结果集最多包含10000条记录(1000000 10% 10%)
6. 表中的记录按照cno列的大小顺序存储，主键索引cno是聚簇索引，或者表经常按照cno进行排序重组。

sql语句如下

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

idx_city与idx_lname_fname都不是半宽索引，所以两者都不满足BQ。QUBE的结果显示出二者的性能都非常差，无论使用哪个索引其结果都是相同的 : 两个索引的匹配扫描过程都只能使用1个MC，都需要进行排序

索引      lname, fname或city            TR = 1      TS = 99999
表        CUST                         TR = 100000      TS = 0
提取      10000 * 0.1ms
LRT                                    TR = 100001      TS = 9999
                                       100001 * 10ms 99999 * 0.01ms
                                       1000s + 1s + 10000 * 0.1ms ≈ 1002s

该事务的最佳索引

最佳索引很容易得到，候选方案A是
(city, lname, fname, cno)
该索引只有两颗星，因为需要排序(order by跟在范围谓词列的后面)。因此，候选方案B一定是(city,fname,lname,cno)，他也只有两颗星，不过他具有的是第二颗星而不是第一颗。通过QUBE很容易判断出哪一个才是最佳索引。

候选索引A

索引      city, lname, fname, cno            TR = 1      TS = 9999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 1      TS = 9999
                                             1 * 10ms 9999 * 0.01ms
                                             10ms + 100ms + 10000 * 0.1ms ≈ 1110ms

候选索引B

索引      city, fname, lname, cno            TR = 1      TS = 99999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 1      TS = 99999
                                             1 * 10ms    99999 * 0.01ms
                                             10ms + 1000ms + 10000 * 0.1ms ≈ 2010ms

最佳索引显然是A，(city, lname, fname, cno)，LRT为1s，但即使是最佳索引，事务的性能也一般。

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

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

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

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

在现有索引的基础上，我们可以使用两个半宽索引
(city, lname)或(lname, fname, city)
我们再一次使用QUBE来判断那个是最好的。

索引      city, lname            TR = 1      TS = 9999
表        CUST                   TR = 10000      TS = 0
提取      10000 * 0.1ms
LRT                              TR = 10001      TS = 999
                                 10001 * 10ms 9999 * 0.01ms
                                 100s + 100ms + 10000 * 0.1ms ≈ 101s

索引      lname, fname, city            TR = 1      TS = 99999
表        CUST                          TR = 10000  TS = 0
提取      10000 * 0.1ms
LRT                                    TR = 10001   TS = 99999
                                       10001 * 10ms 99999 * 0.01ms
                                       100s + 1s + 10000 * 0.1ms ≈ 1002s

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

宽索引(只需访问索引)

我们在上面评估的第一个宽索引上再多加两个列，在第二个索引上再多加一个列，两者就都变成了宽索引 : (city, lname, fname, cno)或(lname, fname, city, cno)

索引      city, lname, fname, cno            TR = 1      TS = 9999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 10001  TS = 999
                                             1 * 10ms 9999 * 0.01ms
                                             10ms + 100ms + 10000 * 0.1ms ≈ 1.11s

索引      lname, fname, city, cno            TR = 1      TS = 99999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 11     TS = 99999
                                             10001 * 10ms 99999 * 0.01ms
                                             10ms + 1s + 10000 * 0.1ms ≈ 2.01s

现在表的访问已经消除了，两个索引的LRT之间的区别就变得很明显了。第一个索引满足了第一颗星，即提供了一个薄的索引片，因为它所基于的原始索引只包含city列，这是在select中唯一的等值条件。正因为如此，我们才能将它升级为最佳索引，首先添加between谓词列(使其变为半宽索引，但仍不够)，然后再添加select中引用的其他列(使其变为宽索引)。

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

何时使用QUBE

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

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

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

选择合适的CPU

首先需要弄清楚当前数据库的应用类型。一般而言，可分为两大类 : OLTP(Online Transaction Processing)和OLAP(Online Analytical Processing，在线分析处理)。这是两种截然不同的数据库应用。OLAP多用在数据仓库或数据集市中，一般需要执行复杂的SQL语句来进行查询；OLTP多用在日常的事务处理应用中国，如银行交易，在线商品交易，Blog，网络游戏等。相对于OLAP，数据库的容量较小。

InnoDB一般应用于OLTP的数据库应用，这种应用的特点如下

• 用户操作的并发量大
• 事务处理的时间一般比较短
• 查询的语句较为简单，一般走索引
• 复杂的查询少

可以看出，OLTP应用本身对CPU的要求不是很高，因为复杂的查询可能需要执行比较，排序，连接等非常耗CPU的操作，这些操作在OLTP的数据库应用中较少发生。因此，可以说OLAP是CPU密集型的操作，而OLTP是IO密集型的操作，建议在采购设备时，将更多的注意力放在提高IO的配置上。

此外，为了获得更多内存的支持，用户采购的CPU必须支持64位，否则无法支持64位操作系统的安装.

从InnoDB的设计架构上看，其主要的后台操作都是在一个单独的master thread中完成的，因此并不能很好地支持多核应用。当然，开源社区已经通过多种办法来改变这种局面，而InnoDB 1.0版本在各种测试环境下已经显示出对多核CPU的处理性能有了极大的提高，而InnoDB 1.2版本又支持多个purge线程，以及将刷新操作从master thread中分离出来。因此，如果用户的CPU支持多核，InnoDB的版本应该选择1.1或更高版本。另外，如果CPU是多核的，可以通过修改参数innodb_read_io_threads和innodb_write_io_threads来增大IO的线程，这样也能更充分有效地利用CPU的多核性能。

在当前的MySQL数据库版本中，一条SQL查询语句只能在一个CPU中工作，并不支持多核CPU的处理。OLTP的数据库应用一般操作都很简单，因此对于OLTP的应用影响不是很大。但是，多个CPU或多核CPU对处理大并发量的请求还是会有帮助。

内存的重要性

内存的大小最能直接反映数据库的性能。通过之前的学习，已经了解到InnDB即缓存数据，又缓存索引，并且将他们缓存于一个很大的缓冲池中，即InnoDB Buffer Pool。因此，内存的大小直接影响了数据库的性能。

判断内存是否达到了瓶颈

缓冲命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_ahead + innodb_buffer_pool_reads)
命中率越高，性能越高

平均每次读取的字节数 = innodb_data_read / innodb_data_reads

注意，即使缓冲池的大小已经大于数据库文件的大小，也并不意味着没有磁盘操作。数据库的缓冲池只是用来存放热点的区域，后台的线程还负责将脏页异步的写入到磁盘。此外，每次事务提交时还需要将日志写入到重做日志中。

硬盘对数据库性能的影响

传统机械硬盘

机械硬盘有两个重要的指标 : 寻道时间和转速。传统机械硬盘最大的问题在于读写磁头，读写磁头的设计使硬盘可以不再像磁带一样，只能进行顺序访问，而是可以随机访问。但是，机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找，因此顺序访问的速度远远高于随机访问。传统关系型数据库的很多设计也是在尽量充分地利用顺序访问的特性。

通常来说，可以将多块机械硬盘组成RAID来提高数据库的性能，也可以将数据文件分布在不同硬盘上来达到访问负载的均衡。

固态硬盘

固态硬盘，更准确地说是基于闪存的固态硬盘，是近几年出现的一种新的存储设备，其内部由闪存(Flash Memory)组成。因为闪存的低延迟性，低功耗，以及防震性，闪存设备已经在移动设备上得到了广泛的应用。

不同于传统的机械硬盘，闪存是一个完全的的电子设备，没有传统机械硬盘的读写磁头。因此，固态硬盘不需要像传统机械硬盘一样，需要耗费大量时间的磁头旋转和定位来查找数据，所以固态硬盘可以一致的随机访问时间。

另一方面，闪存中的数据是不可以更新的，只能通过扇区(sctor)的覆盖重写，而在覆盖重写之前，需要执行非常耗时的擦除操作(erase)操作。擦除操作不能在所含数据的扇区上完成，而需要在删除整个被称为删除块的基础上完成，这个擦除块的尺寸大于扇区的大小，通常为128KB或256KB。此外，每个擦除块有擦写次数的限制。已经有一些算法来解决这个问题。但是对于数据库应用，需要认真考虑固态硬盘在写入方面存在的问题。

因为存在上述写入方面的问题，闪存的读写速度是非对称的。读取速度要远快于写入的速度，因此对于固态硬盘在数据库中的应用，应该好好利用其读取的性能，避免过度的写入操作。

由于闪存是一个完全的电子设备，没有读写磁头等移动部件，因此固态硬盘有着较低的访问延时。当主机发布一个读写请求时，固态硬盘的控制机会把I/O命令从逻辑地址映射成实际的物理地址，写操作还需要修改相应的映射表信息。算上这些额外的开销，固态硬盘的访问延时一般小于0.1ms左右。

对于固态硬盘在InnoDB中的优化，可以增加innodb_io_capacity变量的值达到充分利用固态硬盘带来的高IOPS特效。不过这需要用户根据自己的应用进行针对性的调整。在InnoDB 1.2版本中，可以选择关闭邻接页刷新，同样会为数据库的性能带来一定效果的提升。

合理的设置RAID

RAID类型

RAID(Redundant Array of Independent Disks，独立磁盘冗余数组)的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到升值超过一个价格昂贵，容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区，RAI看起来就像一个单独的硬盘或逻辑存储单元，因此操作系统只会把他当做一个硬盘。

RAID的作用是 ：

• 增加数据集成度
• 增加容错功能
• 增加处理量或容量

根据不同磁盘的组合方式，常见的RAID组合方式可分为RAID 0,RAID 1,RAID 5,RAID 10和RAID 50等。

RAID 0: 将多个磁盘合并成一个大的磁盘，不会有冗余，并行I/O，速度最快。 RAID 0亦称为带区集，他将多个磁盘并列起来，使之成为一个大磁盘。在存放数据时，其将数据按磁盘的个数进行分段，同时将这些数据写进这些盘中。所以，在所有级别中，RAID 0的速度是最快的。但是RAID 0没有冗余功能，如果一个磁盘(物理)损坏，则所有的数据都将丢失。理论上，多磁盘的性能等于 : 单一磁盘效能 * 磁盘数，但实际上受限于总线I/O瓶颈及其他因素的影响，RAID效能会随边际递减。也就是说，假设一个磁盘的效能是50MB/s，两个磁盘的RAID 0效能约为96MB/s，三个磁盘的RAID 0也许就是130MB/s而不是150MB/s了。

RAID 1 : 两组以上的N个磁盘相互作为镜像，在一些多线程操作系统中能有很好的读取速度，但写入速度略有降低。除非拥有相同数据的主磁盘和镜像同时损坏，否则只要有一个磁盘正常即可维持运作，可靠性最高。RAID 1就是镜像，其原理为在主硬盘上存放数据的同时也在镜像硬盘上写相同的数据。当主硬盘(物理)损坏时，镜像硬盘则代替主硬盘的工作。因为镜像硬盘做数据备份，所以RAID 1在所有的RAID级别上来说是最好的。但是，无论使用多少磁盘作为RAID 1，仅算·一个磁盘容量，是所有RAID级别中使用率最低的级别。

RAID 5 : 是一种存储性能，数据安全和存储成本兼顾的存储解决方案。它使用的是Disk Striping(硬盘分区)技术。RAID 5至少需要三个硬盘，RAID 5不对存储的数据进行备份，而是将数据和相对应的奇偶校验信息存储于不同的磁盘上。当RAID 5的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。RAID 5可以理解为是RAID 0和RAID 1的折中方案。RAID 5可以为系统提供数据安全保障，但保障程度比镜像低而磁盘利用率比镜像高。RAID 5具有和RAID 0相近似的数据读取速度，只是多了一个奇偶校验信息，写入数据的速度相当慢，若使用Write Back可以让性能改善不少，同时，由于多个数据对应一个奇偶校验信息，RAID 5的磁盘空间利用率要比RAID 1高，存储成本相对较低。

RAID 10和RAID 01 : RAID 10是先镜像再分区数据，将所有硬盘分为两组，视为RAID 0的最低组合，然后将这两组各自视为RAID 1运作。RAID 1-有着不错的读取速度，而且拥有比RAID 0更高的数据保护性。RAID 01则与RAID 10的程序相反，先分区再将数据镜射到两组硬盘。RAID 01将所有的硬盘分为两组，变成RAID 1的最低组合，而将两组硬盘各自视为RAID 0运作。RAID 01比RAID 10有着更快的读写速度，不过也多了一些会让整个磁盘组停止运转的几率，因为只要同一组的硬盘全部损毁，RAID 01就会停止运作，而RAID 10可以在牺牲RAID 0的优势下正常运作。RAID 10巧妙的利用了RAID 0的速度及RAID 1的安全(保护)两种特性，他的缺点的是需要更多的磁盘，因为至少必须有四个以上的偶数磁盘才能使用。

RAID 50 : RAID 50也被称为镜像阵列条带，由至少六块磁盘组成，像RAID 0一样，数据被分区成条带，在同一时间内向多块磁盘写入；像RAID 5一样，也是以数据的校验位来保证数据的安全，且检验条带分布在各个磁盘上，其目的在于提高RAID 50的读写性能。

对于数据库应用来说，RAID 10是最好的选择，它同时兼顾了RAID 1和RAID 0的特性，但是当一个磁盘失效时，性能可能会受到很大的影响，因为条带(strip)会成为瓶颈

RAID Write Back功能

RAID Write Back功能是指RAID控制器能够将写入的数据放入自身的缓存中，并把他们安排到后面再执行。这样做的好处是，不用等待物理磁盘实际写入的完成，因此写入变得更快了。对于数据库来说，这显得十分重要。例如，对重做日志的写入，在将sync_binlog设为1的情况下二进制日志的写入，脏页的刷新等性能都能得到明显的提升。

但是，当操作系统或数据库宕机时，Write Back功能可能会破坏数据库的数据，这是由于已经写入的数据可能还在RAID卡的缓存中，数据可能没有完全写入磁盘，而这时故障发生了。为了解决这个问题，目前大部分的硬件RAID卡都提供了电池备份单元(BBU，Battery Backup Unit)，因此可以放心的开启Write Back的功能

操作系统的选择

Linux是MySQL数据库服务器最常使用的操作系统。与其他操作系统不同的是Linux拥有众多的发行版本，每个用户的偏好可能不尽相同。然而在将Linux作为数据库服务器时需要考虑更多的是操作系统的稳定性，而不是新特性。

除了Linux操作系统外，FreeBSD也是另一个常见的优秀操作系统。之前版本的FreeBSD对MySQL数据库支持不是很好，需要选择单独的线程库进行手动编译。新版的直接安全即可。

Solaris也是非常不错的操作系统，之前是基于SPARC硬件的操作系统，现在已经移植到了X86平台上。Solaris是高性能，高可靠性的操作系统，
。。。略。。。

后面的内容不是这次的目标，就略过了。。。

什么是锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB会在行级别上对表数据上锁，这固然不错。不过InnoDB也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的LRU列表，删除，添加，移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源的并发访问，提供数据的完整性和一致性。

另一点需要理解的是，虽然现在数据库系统做得越来越类似，但是有多少种数据库就有多少种锁的实现方法。在SQL语法层面，因为有SQL标准的存在，要熟悉多个数据库系统并不是一件难事。而对于锁，用户可能对某个特定的关系数据库系统的锁定模型有一定的经验，但这并不意味着知道其他数据库。不同数据库(如Microsoft SQL Server, Oracle等)，甚至不同的存储引擎(如MyISAM,NDB Cluster)对于锁的实现完全不同。

对于MyISAM，其锁是表锁设计。并发情况下读没有问题，但是并发插入的性能就要差一些了，如果插入是在“底部”，MyISAM存储引擎还是可以有一点的并发写入操作。对于Microsoft SQL Server，在2005版本之前其都是页锁，相对于表锁并发性能有所提升。页锁容易实现，但是对于热点数据页依然无能为力。到2005版本开始支持乐观并发和悲观并发，在乐观并发下开始支持行级锁，但是其实现方式与InnoDB不同。用户会发现在Microsoft SQL Server下，锁是一种稀有的稀缺资源，锁越多开销越大，因此他会有锁升级，在这种情况下，行锁会升级为表锁，这时并发性能就又回到了以前。

InnoDB存储引擎锁的实现和Oracle数据库非常类似，提供一致性非锁定读，行级锁支持，行锁没有相关额外的开销，并可以同时得到并发性和一致性。

lock与latch

在数据库中，lock与latch都可以称为锁。但是两者有着截然不同的含义。

latch一版称为，闩锁(轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch又可以分为mutex(互斥锁)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

lock的对象是事务，用来锁定的是数据库中的对象，如表，页，行。并且一般lock的对象在事务commit或rollback后进行释放(不同的事务隔离级别释放的时间可能不同)。此外，lock，正如在大多数数据库中一样，是有死锁机制的。

对于InnoDB中的latch，可以通过show engine innodb mutex来进行查看，输出结果说明

上述信息比较底层，一版仅供开发人员参考。

InnoDB存储引擎中的锁

锁的类型

InnoDB存储引擎实现如下两种标准的行级锁

• 共享锁(S LOCK) : 允许事务读一行数据
• 排它锁(X LOCK) : 允许事务删除或更新一行数据

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，成这种情况为锁兼容(Lock Compatible)。但若有其他事务T3想要获得行r的排它锁，则必须等待事务T1,T2释放行r上的共享锁，这种情况称为锁不兼容。如下表

从上表可以发现X锁与任何锁都不兼容，而S锁仅和S锁兼容。需要特备注意的是，S锁和X锁都是行锁，兼容是指对同一记录锁的兼容情况。

此外，InnoDB支持多粒度(granular)锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

若将上锁对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度(fine granularity)的对象进行上锁，那么首先需要对粗粒度的对象上锁。如果需要对页上的记录r上X锁，那么分别需要对数据库A，表，页上意向锁IX，最后对记录r上X锁。若其中任何一个部分导致等待，那么改操作需要等待粗粒度锁的完成。举例来说，在对记录r加X锁之前，已经有事务对表1进行了S表锁，那么表1上已存在S锁，之后事务需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。

InnoDB支持意向锁设计比较简练，其意向锁即为表级别的锁，设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。其支持两种意向锁。

• 意向共享锁(IS Lock) : 事务想要获得一张表中某几行的共享锁
• 意向共享锁(IX Lock) : 事务想要获得一张表中某几行的排它锁

由于InnoDB支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫变外的任何请求。意向锁与行级锁的兼容性入校

可以在show engine innodb status的输出结果中搜索lock来查看当前锁的情况

在InnoDB 1.0版本之后，可以在INFORMATION_SCHEMA数据库中通过表INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS来监控锁的使用情况。

INNODB_TRX结构

该表显示了当前运行的InnoDB事务的信息

INNODB_LOCKS结构

该表显示了当前运行的InnoDB事务锁的信息

需要注意的是，lock_data这个值并非是“可信”的值。例如用户运行了一个范围查找时，lock_data可能只返回第一行的主键值。与此同时，如果当前资源被锁住了，若锁住的页因为InnoDB缓冲池的容量，导致该页从缓冲池中被刷出，则查看INNODB_LOCKS表时，该值同样会显示为NULL，即InnoDB不会从磁盘再进行一次查找。

INNODB_LOCK_WAITS结构

该表显示了当前运行的InnoDB事务等待的情况

这个表可以清楚的看到那个事务阻塞了另一个事务

一致性非锁定读

一致性的非锁定读(consistent nonlocking read)是指InnoDB通过行多版本控制(muti versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反的，会取读取行的一个快照数据。

之所以称为非锁定读，是因为不需要等待访问的行上X锁的释放。快照数据是指之前版本的数据，该实现是通过undo端来完成。而undo段用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是无需上锁的，因为没有事务会对历史数据进行修改操作。

非锁定读机制极大提高了数据库的并发性。在InnoDB的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定一致性读。此外，即使都是使用非锁定一致性读，但是对于快照数据的定义也各不同。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。一行数据可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制(Mutil Version Concurrency Control, MVCC)。

在事务隔离级别READ COMMITTED和REPEATABLE READ(InnoDB存储引擎的默认事务隔离级别)下，InnoDB使用非锁定一致性读。然而，对于快照数据的定义确不同。在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新的一份快照数据。而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

一致性锁定读

在某些情况下，用户需要显式的对读取操作加锁以保证数据逻辑的一致性。而要求数据库支持加锁语句，即使是对于select的只读操作。InnoDB对于select语句支持两种一致性锁定读(locking read)操作

• SELECT ... FOR UPDATE
• SELECT ... LOCK IN SHARE MODE

SELECT ... FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。SELECT ... LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以对被锁定的行加S锁，但是如果加X锁，则会被阻塞。

对于一致性锁定读，即使读取的行已经被执行了SELECT ... FOR UPDATE，也是可以进行读取的，这和之前讨论的情况一样。此外，这两个语句必须在事务中执行，当事务提交了，锁也就释放了。因此在使用这两个SQL语句时，务必加上begin，start transaction或者set autocommit=0。

自增长与锁

自增长在数据库中是一种非常常见的属性，是一种首选的主键方式。在InnoDB的内存结构中，对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，使用下面的语句可以得到计数器的值。
SELECT MAX(auto_inc_col) from t for update

插入操作会根据这个自增长的计数器值加1赋予自增长列。这个实现方式成为AUTO-INC Locking。这种锁采用一种特殊的表锁机制，为了提高插入性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成(虽然不用等事务的完成)。其次，对于INSERT ... SELECT的大数据量的插入会影响插入的性能，因为另外一个事务中的插入会被阻塞。

从5.1.22版本开始，InnoDB提供了一种轻量级互斥量的自增长实现机制，这种机制能大大提高自增长值的性能。

在InnoDB存储引擎中，自增长的列必须是索引，同时必须是索引的第一个列。

外键和锁

外键用于引用完整性的约束检查。在InnoDB中，对于一个外键列，如果没有显示的对这个列加索引，InnoDB会自动对其加一个索引，因为这样可以避免表锁。

对于外键的插入或更新，首先需要查找父表中的记录，即select父表。但是对于父表的select操作，不是使用一致性非锁定读的方式，因为这样会产生数据不一致的问题，因此使用的是SELECT ... LOCK IN SHARE MODE方式，即主动对表加一个S锁。如果这是父表上已经加了X锁，子表上的操作会被阻塞。

锁的算法

锁的3中算法

• Record Lock : 单个行记录上的锁
• Gap Lock : 间隙锁，锁定一个范围，单不包含记录本身
• Next-Key Lock : Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何索引，那么InnoDB会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next Lock算法下，InnoDB对于行的查询都是采用这种锁定算法

略略略。。。