数据库索引（3）- 为表连接设计索引-摩杜云开发者社区

表的连接方及表的访问顺序对索引影响也很大。在一个连接查询中有两类谓词：本地谓词和连接谓词（重要的是把本地谓词设置索引），只用于访问一张表的谓词称为本地谓词，定义了表和表之间的连接关系的谓词称为连接谓词。连接谓词大部分是基于主键\外键这一条件，这也是最快的连接方式了。在连接查询时DBMS有三种扫描方式：

循环嵌套：首先在外层表中找到一行满足本地谓词的记录，然后再从内层表中查找与这一行数据相关的记录，并检其中哪些符合内层表的本地谓词体件。
合并扫描：以临时表的方式替代回表随机读；
哈希连接：哈希连接本质上是用哈希算法代替排序算法的合并扫描连接，加快了排序过程；

循环嵌套连接

//查询20条数据
select cname, ctype, ino, ieur from cust, invoice where cust.cno =? and cust.cno = invoice.cno

简单估算下时间：

--TR=随机读， TS=顺序读
索引CUST.CNO TR=1
表CUST  TR=1 
索引INVOIC.CNO TR=1 TS=20
表INVOIC TR=20
LRT 23*10MS+0.2+0.2=232MS

这个例子中CUST是外层表，INVOICE是内层表，处理过程：第一步，在CUST表上使用主键进行查询：第二步，在第二个SELECT语句中使用同一个客户号通过一个游标进行查询，获取20条记录。这种方式相当于用程序写的一种嵌套循环，先循环cust表，再循环invoice表。

表访问顺序影响

比如，一家跨国公司拥有80万国内客户以及分布在其他100多个国家的20万客户。这100万客户的数据都存储在CUST表中。NVOICE表有20万条记录。不论国内客户还是国外客户，平均每人都拥有20张发票。现在需要实现一个新的查询，该查询需要通过NVOICE表上的IEUR列查出某个外国国家的高额发票，并将结果按降序排列。用户的输入包括一个发票总额的下限值和一个国家的代码。假设cctry是一个非聚簇索引，可能有三种SQL语句的写法，前两种采用不同的顺序，第三种采用简单连接：

select cno, cname, ctype from cust where cctry = ?
select ino, ieur from invoice where ieur>? and cno=?

第一条语句：大概1000s；
第二条语名：大概2050s;

select cno, ino, ieur from invoice where ieur>?
select cno, cname, ctype from cust where cno = ?

第一条语句：大概200s；
第二条语名：大概400s;

select cname, ctype, ino, ieur from cust, invoice where ieur>? and cctry=? and cust.cno = invoice.cno

所以在选择连接时应该把ieur做为第一个查询条件，做为外层表。即为最优访问路径使用理想索引，也有可能产生不可接受的响应时间。主要问题在于潜在的对内层表（或其索引）的大量随机访问。我们所需要的数据时才会去访问它，这样基本连接问题BQ就变成了：是否有一个已经存在的或者计划添加的索引，包含了所有本地谓词对应的列？这里是指包含了涉及的所有表的本地谓词。于是，能够消除对内层表或者索引的大量随机访问的唯一方法就是，依照这一原则添加冗余的列，使一张表上包含所有的本地谓词。但这种方式对写程序不太友好。

这个表的访问顺序主要从随机读的角度来分析，这种SQL的写法也受限于此，顺序不同其性能可能会差出几十倍。所以有时需要用explain进行分析时可以辅助分析其执行顺序。

预测表的访问顺序

访问顺序可能会对性能产生巨大的影响，索引类似。而在未确定表的最佳访问顺序之前，是无法设计理想索引的。在大部分情况下，可以使用以下经验法则来预测最佳的表访问顺序：将包含最低数量本地行的表作为外层表。本地行的数量是指最大过滤因子过滤本地谓词之后所剩余的行数。但这种预测方法没有考虑到排序、很小的表、聚簇比例。

关键在于，在为连接查询设计索引的时候，要把所假设的连接方法和表访问顺序记在心里（或者记录下来）。然后，我们就能设计索引了，一种常见的错误是在没有涉及连接方式和表访问顺序的确定的访问路径假设的情况下进行索引设计。仅仅在连接谓词和本地谓词上有索引是不够的，这往往会导致一些冗余的索引。另外，在一个嵌套循环连接中，内层表通常需要有好的宽索引，且以连接谓词列作为前导列。

合并扫描

合并扫描连接的执行过程如下：1、执行表或索引扫描以找出满足本地谓词的所有行；2、随后可能会进行排序，如果这些扫描未按所要求的顺序提供结果集；3、对前两步生成的临时表进行合并。在以下情况中，合并扫描连接会比嵌套循环快：

用于连接的字段上没有可用的索引，所以要在外键上都设置好索引；
结果表很大。在这种情况下，若使用嵌套循环连接，可能会导致相同的页被不断地重复访问；
连接查询中不止一张表的本地谓词的过滤因子很低，嵌套循环可能导致对内层表（或者内层表索引）的大量的随机访问。

速度快的原因，是因为会生成两张临时表，然后在两张临时表中进行排序，最后再合并两张临时表，返回最终结果，少了回表随机读这个过程。如果外层表已经是按照连接字段的顺序访问了，那么就没有必要创建和排序临时表了。然而，对于内层表，则总是会被放到一个工作文件中。于是，随着外层表的访问，从外层表上提取的记录将会与内层表的临时表进行合并。这个创建临时表的时间是在搜索时同步进行的。

哈希扫描

哈希连接本质上是用哈希算法代替排序算法的合并扫描连接。首先，对较小的结果集用哈希算法计算其连接字段，并将其保存在一个临时表中：然后，再扫描其他的表（或索引片），并通过（计算得到的）哈希值将满足本地谓词条件的每一行记录与临时表中相应的行进行匹配。

二、多索引访问

许多数据库管理系统支持从一张表的多个索引处收集指针，或是从单个索引的几个索引片处收集，然后比较这些指针集并访问满足WHERE语句中所有谓词条件的数据行。这一能力被称为多索引访问，或被称为索引与（索引交集）和索引或（索引并集）。多索引要致力解决以下三个问题：

当一个简单谓词有一个较高的过滤因子时，顺序访问的量可能会过多。
即便多个索引片都是从一个符合查询排序顺序的索引上读取的，ORDER BY子句仍会引起一次排序，因为对指针集的排序操作破坏了从索引继承而来的原始顺序，当ORDER BY 子句有多个列时，排序的原则是先按第一个字段排序，如果存在顺序问题再按第二列排序，依次类推；
如果从索引片上仅仅收集指向表行的指针，那么一个只需访问索引的访问路径是无法实现的。

索引与

select b, c from tx where a=? and b between ? and ?

数据库索引（3）- 为表连接设计索引

理想的执行方式按下拉4部进行:

从索引片①和索引片②上收集所有满足相应谓词条件的索引行指针。
按页号的顺序对两个指针集合进行排序③。
合并这两个已排序的指针集合③。
只针对那些同时满足两个WHERE子句的结果行进行表访问④一每个结果行进行一次表访问：这些表访问将会比传统随机读的方式访问得更快，因为由于指针已经按照页号进行了排序，所以对表页的扫描将通过跳跃式顺序读的方式完成。

上面的执行过程是多索引方式，如果要考虑多索引（A+B）时，（A+B）的效率会更好，因为他会少几次随机读。所以合理的方案是用一个宽索引来代替，但同样也会牺牲存储空间为代价。

索引或

select b, c from tx where a=? or b between ? and ?

数据库索引（3）- 为表连接设计索引

理想的执行方式按下拉4部进行：

采集两个指针集①和②一100次顺序访问A，500次顺序访问B，在最差输入条件下合计共600次访间；
对指针进行排序③一非常快；
去除重复的指针③一非常快；
读取满足条件的表行④一在最差输入且没有重复指针的情况下，共600次随机访问，600×10ms=6s；相较之下，全索引扫描花费的时长为1000000×0.01ms=10s。

在快速EXPLAIN复查的过程中，我们应当对SELECT语句进行多索引访问检查：不合适的索引或者有害的OR可能会被识别出来，这些识别出的问题可以用UNION来替代，或者对游标进行拆分。

索引连接

在单表上使用多个索引的方法是连接两个或多个索引以创建一个覆盖式索引，覆盖式索引是指涵盖给定查询中所有列的索引。如下示例：

SELECT OrderDate,ShippedDate,count (*） FROM Orders  GROUP BY OrderDate,ShippedDate

Orders表在OrderDate列和ShippedDate列上都有索引。在本例中，优化器将使用合并连接的方式连接这两个索引从而返回合适的结果集。通过连接这两个索引，SQL Server达到了与拥有一个覆盖索引相同的效果。由于从索引片上收集的不仅有指针，所以索引连接的实现方式避免了多索引访问一不必要的表访问。

设计时注意事项

为子查询设计索引

从性能的角度看，子查询与连接十分相似。实际上，现今的优化器通常会在进行访问路径的选择之前，先将子查询重写为一个连接。若优化器没有进行重写，那么子查询的类型本身可能就决定了表访问顺序。内外层无关联的子查询通常会从最内层的SELECT开始执行。结果集被保存在一张临时表中，等待下一个SELECT的访问。内外层有关联的子查询通常会从最内层的SELECT开始执行。无论是何种情况，同连接一样，应当基于能够形成最快访问路径的表访问顺序进行索引设计。若最佳的表访问顺序未被选中，那么程序开发人员可能需要对语句进行重写，在某些情况下还可能要使用连接。

为UNION语句设计索引

通过UNION或UNION ALL连接的SELECT语句是逐个分别进行优化和执行的。因此，应该为每一个独立的SELECT设计合适的索引。需要注意一点，带ORDER BY的UNION可能会导致提前物化。

冗余数据

将某列拷贝至依赖表（向下反范式化）：可消除大量对内层表或索引的随机读；将汇总数据添加至父表（向上反范式化）：可避免排序；反范式化的成本主要是性能问题，包括为了更新表及索引上冗余字段所带来的I/O时间。在向下反范式化中，这可能需要移动大量的索引行，从而导致一个简单的UPDATE运行得很慢。向上反范式化不太可能因为一次简单的更新操作而引发I/O剧增，不过INSERT、UPDATE和DELETE可能导致父表及其索引上的一些额外I/O。在极端情况下，如每秒1O次以上的NSERT或UPDATE，由这些I/O带来的磁盘负载可能会成为问题。

另外关系表的设计考虑的是性能和存储空间的问题。比如有张基础数据表，其它的表都是在这张表上进行的扩展，这时相当于公用了基础表，会节省大量的磁盘空间，但同时查询时可能从随机1次到需要随机读多次，其响应时间会变长。

索引设计总结

思路总结

数据库索引（3）- 为表连接设计索引

设计步骤

当表结构第1版设计（主键、外键、表行顺序）完成时，就开始创建第0版的索引：主键索引、外键索引及候选键索引（如果有的话）。
对第1版表结构设计的性能表现进行检查：使用QUBE评估一些重负载事务和批处理程序在理想索引下的响应时间。若评估结果无法满足要求，则将那些具有1：1或1：C关系的表进行合并，同时将冗余数据添加至有1：M（一对多）关系的依赖表中。
当表结构基本稳定后，你可以开始添加一些明显需要的索引一基于对应用系统的理解。
若一个表的变化频率很高（如每秒有大于50次的插入、更新或删除），那么你应该用QUBE评估一下该表最多容许有多少个索引。
当知道一个程序的数据库处理模式（事务型或批处理型）后，就需要用最新的数据库版本进行最坏输入下的QUBE计算。若评估出一个事务的本地响应时间超出了应用的警戒值（如2s），则表明当前的数据库版本无法满足该程序。对于一个批处理程序而言，对响应延时的接受度必须针对具体情况逐个评估。不过，为了避免长时间锁等待，相邻两个事务提交点之间耗时的告警阀值应该与本地响应时间的告警阀值相同。一旦超出了告警阀值，你就应当对索引进行改进（半宽索引、宽索引或理想索引）。若在使用了理想索引的情况下评估结果（QUBE）仍不令人满意，或者所需的索引数量超出了第4步中评估的表上所容许的最大索引数，那么你需要根据第15章中所讨论的问题对这些慢查询进行更精确的评估。若评估出的响应时间仍旧过长，那么你必须像第2步中那样修改表的设计。最差情况下，你必须与用户协商调整需求，或者与管理人员协商调整硬件配置。
SQL语句被编写后，开发人员就应使用基础问题（BQ），或者如果可行的话，用基础连接问题（BQ）对其进行评估。
当应用程序发布至生产环境后，有必要进行一次快速的EXPLAIN检查：对所有引起全表扫描或全索引扫描的SQL调用进行分析。这一检查过程也许能发现不合适的索引或优化器问题。
当生产系统正式投入使用后，需要针对首个高峰时段生成一个LRT级别的异常报告（尖刺报告或类似的报告）。若一个长响应时间问题并非由排队或优化器问题引起，那么你应该用第5步中的方法进行处理。
至少每周生成一个LRT级别的异常报告。