《数据库索引设计与优化》笔记二

第八章 为表连接设计索引

基本问题BQ:是否存在或者计划设计一个索引,使它包含所有被 where 子句引用的列。BQ 背后的原理是:保证只有知道 是必须被访问的表行时,才去访问该表。

基本连接问题 BJQ:是否有一个已经存在或计划添加的索引,包含了所有本地谓词对应的列?在表连接中指包含了涉及的所有表的本地谓词。

合并扫描连接和哈希连接

合并扫描连接执行过程如下:

  • 执行表或索引扫描以找出满足本地谓词的所有行。
  • 随后可能会进行排序,如果这些扫描未按所要求的顺序提供结果集。
  • 对前两步生成的结果集进行合并。

在以下情况中,合并扫描连接会比嵌套循环连接快:

  • 用于连接的字段上没有可用的索引。这种情况下,使用嵌套循环,内层表可能需要被扫描很多次。
  • 结果表很大。这种情况下使用嵌套循环会导致相同的页被不断重复访问。
  • 连接查询中不止一张表的本地谓词的过滤因子很低。嵌套循环可能导致对内层表(或者内层表索引)的大量随机访问。

第十章 多索引访问

多索引访问(’索引与’和’索引或’):许多数据库管理系统支持从一张表的多个索引处收集指针,或是从单个索引的几个索引片处收集,然后比较这些指针集并访问满足 where 语句中所有谓词条件的数据行。

多索引访问的执行步骤:

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

如果在执行计划中发现索引与,应当考虑用一个宽索引将其替代。因为使用上述机制进行索引与操作存在三个严重的缺陷:

  1. 当一个简单谓词有一个较高的过滤因子(选择性低)时,顺序访问的量可能会过多。
  2. 即便多个索引片都是从一个符合查询排序顺序的索引上读取的, order by 子句仍会引起一次排序,因为对指针集的排序操作破坏了从索引继承而来的原始顺序。
  3. 如果从索引片上仅仅收集指向表行的指针,那么只需访问索引的执行计划是无法实现的。

第十一章 索引和索引重组

B 树索引物理结构

索引行被存储在叶子页上。索引行的长度一般为索引列的总长度加上 10 个字节的控制信息。对于非唯一索引,每个索引键值后可能会存有多个指针,这些指针以其值的顺序来存储。

DBMS 如何查找索引行

当数据库需要读取一个索引片时,会先读取第一个索引行。第一个索引行有一个指针指向下一个索引行,DBMS 会一直沿着这个指针链访问。所有的索引行都通过索引键值 链在一起。(按这个链的顺序进行扫描即可得到有序的数据,从而减少排序。)

插入一行数据时会发生什么

主页:记录(应当)所属的表页。

当一张表有一个聚簇索引,那么 DBMS 会根据聚簇索引的键值尝试将插入的记录放在它所属的表页(主页)中。如果这行记录在主页里放不下,或者当前页被锁住,那么 DBMS 将会检查临近的页。在最坏的情况下,新的行会被插入到表的最后一页。

当许多表行未能放在主页中时,如果表行的顺序很重要,则需要对这个表进行重组(对于那些涉及多张大表的大规模批处理程序通常需要这样做)。

当一个索引有一个不断增长的键值时,新行将被添加到索引页的最后,索引页可能永远也不会进行分裂。

第十六章 组织索引设计过程

设计出色索引的 9 个步骤:

  1. 当表结构第1版设计(主键、外键、表行顺序)完成时,就开始创建第 0 版的索引:主键索引、外键索引及候选键索引。
  2. 对第1版表结构设计的性能表现进行检查:使用 QUBE 评估一些重负载事务和批处理程序在理想索引下的响应时间。若评估结果无法满足要求,则将那些具有 1:11:C (1对0或对1)关系的表进行合并,同时将冗余数据添加至有 1:M (一对多)关系的依赖表中。
  3. 当表结果基本稳定后,可以开始添加一些明显需要的索引–基于对应用系统的理解。
  4. 若一个表的变化频率很高(如每秒有50次的插入、更新或删除),那么应该用 QUBE 评估一下该表最多容许有多少个索引。
  5. 知道一个程序的数据库处理模式(事务型或批处理型)后,就需要用最新的数据库版本进行最坏输入下的 QUBE 计算。
  6. SQL 语句被编写后,应使用基础问题(BQ),后者用基础连接问题(BJQ)对其进行评估。
  7. 当应用程序发布到生产环境后,有必要进行一次快速的 EXPLAIN 检查:对所有引起全表扫描或索引全扫描的 SQL 调用进行分析。这一检查过程也许能发现不合适的索引或优化器问题。
  8. 当生产系统正式投入使用后,需要针对首个高峰时段生成一个 LRT 级别的异常报告(尖刺报告或类似报告)。若一个长响应时间问题并非由排队或优化器问题引起,应该使用第5步中的方法进行处理。
  9. 至少每周期生成一个 LRT 级别的异常报告。

欢迎关注我的微信公众号: coderbee笔记,可以更及时回复你的讨论。

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据