物理数据库设计与调优：索引选择与性能优化

# 物理数据库设计与调优：索引选择与性能优化 ## 1. 数据库设计与调优概述 在数据库的设计与管理过程中，需要关注查询和更新操作中的选择和连接条件，以及这些条件的选择性。查询和更新通常带有参数，这些参数的值决定了选择和连接条件的选择性。更新操作包含用于查找目标元组的查询组件，良好的物理设计和索引的存在有助于这一组件的执行，但同时更新操作也需要额外的工作来维护被修改属性上的索引。因此，索引对查询总是有益的，但对更新操作可能有加速或减速的影响，设计者在创建索引时需权衡利弊。 ### 1.1 物理设计和调优决策 物理数据库设计和调优过程中，有以下重要决策： 1. **索引创建选择** - 确定对哪些关系创建索引，以及选择哪些字段或字段组合作为索引搜索键。 - 决定每个索引是聚簇索引还是非聚簇索引。 2. **概念模式调优** - **替代规范化模式**：通常有多种方式将模式分解为所需的范式（如 BCNF 或 3NF），可根据性能标准进行选择。 - **反规范化**：重新考虑概念模式设计过程中为规范化进行的模式分解，以提高涉及多个先前分解关系属性的查询性能。 - **垂直分区**：在某些情况下，进一步分解关系以提高仅涉及少数属性的查询性能。 - **视图**：添加一些视图以向用户隐藏概念模式的变化。 3. **查询和事务调优**：重写频繁执行的查询和事务，使其运行更快。 在并行或分布式数据库中，还需考虑其他选择，如是否在不同站点之间对关系进行分区，或是否在多个站点存储关系的副本。 ### 1.2 数据库调优的必要性 在系统的初始设计阶段，准确详细的工作负载信息可能难以获取。因此，在数据库设计和部署后进行调优非常重要，需要根据实际使用模式改进初始设计，以获得最佳性能。数据库设计和调优之间的界限有些模糊，我们可以将初始概念模式设计完成并做出一组索引和聚簇决策后视为设计过程结束，之后对概念模式或索引的任何更改视为调优；也可以将概念模式的一些细化（以及受此细化影响的物理设计决策）视为物理设计过程的一部分。 ## 2. 索引选择指南 选择索引时，从查询列表（包括作为更新操作一部分的查询）开始。只有被某些查询访问的关系才应考虑作为索引的候选对象，索引属性的选择由工作负载中查询的 WHERE 子句中的条件指导。合适的索引可以显著改善查询的评估计划。 ### 2.1 索引选择方法 一种索引选择方法是依次考虑最重要的查询，对于每个查询，确定在当前（待创建）索引列表下优化器会选择的计划。然后考虑是否可以通过添加更多索引来获得更好的计划，如果可以，这些额外的索引就是候选索引。 ### 2.2 不同类型索引的适用场景 - **范围检索**：B + 树索引通常更适合范围检索。 - **精确匹配检索**：哈希索引更适合精确匹配检索。 - **聚簇**：聚簇对范围查询有益，如果多个数据条目包含相同的键值，对精确匹配查询也有益。 ### 2.3 索引选择的具体指南 1. **是否创建索引（指南 1）**：除非某些查询（包括更新操作的查询组件）能从索引中受益，否则不要创建索引。尽可能选择能加速多个查询的索引。 2. **搜索键的选择（指南 2）**：WHERE 子句中提到的属性是索引的候选对象。精确匹配选择条件建议考虑在所选属性上创建索引，理想情况下是哈希索引；范围选择条件建议考虑在所选属性上创建 B + 树（或 ISAM）索引，通常 B + 树索引更优，ISAM 索引在关系不常更新时可考虑，但为简单起见，通常选择 B + 树索引。 3. **多属性搜索键（指南 3）**：在以下两种情况下考虑具有多属性搜索键的索引： - WHERE 子句包含对关系多个属性的条件。 - 它们能够为重要查询实现仅索引评估策略（即避免访问关系）。创建多属性搜索键的索引时，如果预计有范围查询，要注意搜索键中属性的顺序以匹配查询。 4. **是否聚簇（指南 4）**：一个关系最多只能有一个聚簇索引，聚簇对性能影响很大，因此聚簇索引的选择很重要。一般来说，范围查询最能从聚簇中受益。如果对一个关系有多个涉及不同属性集的范围查询，在决定哪个索引应聚簇时，要考虑查询的选择性和它们在工作负载中的相对频率。如果一个索引能为其旨在加速的查询实现仅索引评估策略，则该索引无需聚簇。 5. **哈希索引与树索引（指南 5）**：B + 树索引通常更优，因为它支持范围查询和相等查询。哈希索引在以下情况下更好： - 索引用于支持索引嵌套循环连接，被索引的关系是内关系，且搜索键包含连接列。 - 有一个非常重