首页 > 代码库 > 索引笔记《二》确定需要建立索引的列
索引笔记《二》确定需要建立索引的列
目录
确定需要建立索引的列
主键列和唯一键列的索引
外键列的索引
其他适合创建索引的列
索引指南
小结
确定需要建立索引的列
现在我们把注意力转到应为哪些列建立索引上。对于初学者来说，我们建议对于大多数应用程序，在下列情况下创建索引。
- 为每个表定义主键约束：这导致在主键指定的列上自动创建索引。
- 在要求唯一且不同于主键列的列上创建唯一键约束：每个唯一键约束导致在约束中指定的列上自动创建一个索引。
- 手动创建外键列上的索引：这是为了得到更好的性能，以避免某些特定的锁问题（有关完整的详细信息，请参阅第2章）。
主键列和唯一键列的索引
在大多数情况下，应该为每个表创建主键约束。如果没有为主键列定义索引，那么Oracle会自动为你创建一个B树索引。
同样，对于在表上定义的任何唯一键约束，如果唯一键列上没有已定义的索引，Oracle也将创建一个合适的B树索引，如下面简单的例子所示：
 |
请参阅第2章，了解关于主键和唯一键约束的完整细节以及它们与索引的关系
外键列的索引
Oracle不会自动创建外键列的索引。我们建议在外键列上创建B树索引的原因之一是，外键列经常在WHERE子句中被引用，并因此可以改善这些查询的性能。
当外键列上存在索引时，可以避免或减少锁定问题。也就是说，当插入或删除子表中的记录时，将在父表上放置一个表级别的锁，该锁将阻止其他进程在父表中插入或删除记录。在OLTP数据库中，当有多个进程同时插入和删除父表和子表中的记录时，这可能会成问题。在数据仓库环境中，这个问题的影响更小，因为数据以更加系统化的方式（调度批处理作业）被加载并且数据通常不会被删除。
下面是一个简单的例子，首先创建一个有外键的表，然后手动创建索引：
 |
其他适合创建索引的列
在选择创建什么样的索引时，请牢记这一基本原则：根据查询表时使用的列制定索引策略。既可以在一个表上创建多个索引，也可以创建一个包含多个列的索引。如果事先考虑好需要在表上执行什么类型的查询，那么就会做出更好的决策。如果你已经确定了性能较差的SQL查询，也可以考虑为符合以下条件的列创建索引。
为经常用作WHERE子句中谓词的列创建索引，如果在WHERE子句中使用表的多个列，可以考虑使用组合（多列）索引。
为在SELECT子句中使用的列创建覆盖索引。
考虑为在ORDER BY、GROUP BY、UNION或者DISTINCT子句中使用的列创建索引。
Oracle允许创建包含多个列的索引。多列索引被称为组合索引（有时也被称为复合索引）。如果你访问表时经常在WHERE子句中使用多个列，那么这些索引特别有效。在这种情况下，组合索引常常比分别创建多个单列索引更有效。
包含在SELECT和WHERE子句中的列也是索引的候选者。请记得，覆盖索引包括查询返回的所有列。在这种情况下，Oracle可以使用索引结构本身（而不是表）来满足查询的结果。此外，如果列值有足够的选择性，Oracle还可以使用WHERE子句中引用的列的索引来提高查询性能。
还应考虑为在GROUP BY、ORDER BY、UNION或DISTINCT子句中使用的列建立索引。这可能会使经常使用这些SQL构造的查询更高效。
每个表上有多个索引没有关系。但是，在一个表上建立的索引越多，DML语句就会运行得越慢（因为表列值变化时，Oracle有越来越多的索引需要维护）。不要在表中随机添加索引，直到偶然发现索引列的正确组合，这是一个陷阱。相反，需要在生产环境中创建索引之前，验证它的性能。（关于验证性能优势的详细信息，请参阅第7章）。
注意 表中的一列允许在该表的多个索引中出现。然而，Oracle并不允许在一个表的完全相同的列组合上创建多个索引。
索引指南
利用Oracle索引有助于高效访问大型数据集。只有确定了SELECT语句在性能上的改进与索引所消耗的空间成本和更新表时的开销相比是否值得，才能确定是否应该使用索引。表1-2概括了有效使用索引的准则。
表1-2 创建索引的准则
指南 | 论证 |
创建所需数量的索引，但尽量少创 建索引。明智地添加索引。首先要 测试以确定可量化的性能收益 | 索引提高性能，但也消耗磁盘空间和 处理资源。不要添加不必要的索引 |
（续）
指南 | 论证 |
对表执行的查询所需的性能， 应该成为制订索引策略的基础 | 为在SQL查询中使用的列创 建索引将最大限度地提升性能 |
考虑使用SQL调优顾问或SQL访 问顾问获得索引的建议 | 这些工具提供了建议和第二双进 行索引决策的眼睛 |
为所有表创建主键约束 | 这将自动创建一个B树索引 （如果在主键列上还没有建立索引） |
在合适的地方创建唯一键约束 | 这将自动创建一个B树索引 （如果在唯一键列上还没有建立索引） |
为包含外键的列创建索引 | 连接表时，外键列通常包含在 WHERE子句中，从而能提高SQL SELECT 语句的性能。在外键列上创建一个B 树索引，还可以减少在更新和插入 子表时的锁定问题 |
小心地选择和测试小表的 索引（小表少于几千行） | 即使对于小表，有时候访问索引也 可能比全表扫描性能更好 |
使用正确的索引类型 | 正确使用索引能最大限度地提高性能。 参见表1-1了解更多详细内容 |
如果不能证明使用不同类型 索引可获得性能增益，那就使 用基本的B树索引类型 | 对于大多数具有高基数列值的应用程 序，都适合使用B树索引 |
数据仓库环境中，考虑使用位图索引 | 对不经常更新的低基数值列来说， 使用这些索引是理想选择。位图索 引适用于星型模式事实表的外键列， 前提是经常会对事实表运行使用AND 和OR连接条件的查询 |
考虑为索引使用单独的 表空间（与表分离） | 表和索引数据可能有不同的存储、 备份和恢复要求。单独的表空间， 可以把索引和表分开管理 |
让索引从表空间继承它的存储属性 | 这使得它更易于管理和维护索引的存储 |
使用一致的命名标准 | 这使得维护和故障排除更容易 |
不要重建索引，除非有充 分的理由这样做 | 重建索引通常是不必要的，除非索 引损坏或需要把索引移动到不同的表空间 |
监测索引，并删除不被使用的索引 | 这样做能释放物理空间，并提高DML （数据操纵语言）语句的性能 |
删除索引之前，考虑把它标 记为不可用或不可见的 | 这使你在删除索引之前，可以更好 地确定是否有任何性能问题。这些 选项使得可以重建或重新启用 索引而无需用DDL（数据定义语言）来创建语句 |
在创建和管理数据库中的索引时，请参考这些指导原则。这些建议是为了帮助你正确使用索引技术。
小结
索引的存在主要是为了提高查询性能，因此仔细考虑如何实现索引是至关重要的。精心设计的索引策略将会使数据库应用程序的性能优异。相反，欠考虑的计划将导致性能不佳。
索引占用磁盘空间，并与表分开存放。然而，索引定义在表的一个或多个列上，从这个意义上说，索引不能脱离表而单独存在。
Oracle提供了广泛的索引类型和功能。在大多数情况下，使用默认的B树索引就可以了。使用其他类型的索引之前，请确保你了解性能收益。你应该知道Oracle提供的索引有哪些功能，并知道在什么样的情况下，应该使用哪种专门的索引。
我们建议在主键列、唯一键列和外键列上创建索引。这是个很好的起点。然后，对执行缓慢的SQL语句进行分析，观察会用到哪些列。这会为你提供额外的候选索引列。这些索引建议奠定了最大限度地提高SQL查询性能的基础。
索引笔记《二》确定需要建立索引的列