（转摘）_《数据库设计入门经典》:构建快速执行的数据库模型_8.4 提高性能的高效索引
8.4 提高性能的高效索引
最重要的是，创建索引要比不创建索引更为慎重。很多数据库都因过度使用索引而变得错综复杂，在很长时间后，之前创建的索引和创建原因已经不得而知。当然不要害怕创建索引。正确的思想是，已经存在的索引并不表示该索引应该存在。
8.4.1 索引类型
有些数据库具有不同类型的索引。关系数据库中最常使用其中一种索引类型，这种索引通常是某种形式的二叉树(B树)索引。其他索引类型则很少见到，并且只适用于一些特殊情况。使用特殊类型的索引，如ISAM索引、散列索引和位图索引时，要注意需求和后果。
提示：
不同数据库引擎会以不同方式来使用索引结构和索引方法。这里列出几个示例。有的数据库引擎允许创建分离的物理ISAM索引，有的数据库则使用ISAM算法来访问BTree索引。有些数据库允许创建散列索引作为分离的物理结构；另一些数据库则只允许对整个表中所有字段使用散列算法。有些数据库引擎允许创建BTree索引作为索引，并作为整个表的排序顺序。这样表及其所有字段都会成为B树索引(称为群集索引或索引组织表)。
上述索引的应用较为特殊且并不常用。同样要注意，对源表进行修改时，这类不常用的索引常常会引发溢出。实际上，不常用的索引类型经常会由于溢出而削弱性能。溢出是指表中数据变化后，为性能而创建的索引结构完全变化且部分索引无法使用的情况。通常此类不常用的索引类型大部分都不适合只读型环境，因此使用这类索引前一般需要事先考虑并谨慎使用。
数据库管理员应该密切关注数据库中的索引。当然平常没有足够的检查时间，但在发布应用程序前最好对所有索引再进行一次检查。开发人员经常会创建很多索引，有时在同一个应用程序中，每个开发人员都会创建自定义的索引组，造成过度索引。表中过多的索引会导致性能问题。实际上，对表执行表更改命令(如INSERT、UPDATE或DELETE命令)时，除表之外，还会对表中所有索引也执行相同的命令。例如在具有4个索引的表中插入1条记录，则实际上包含了5次数据库更改。
8.4.2 实际情况中的索引
在表中创建索引时，一般根据该表的功能不同，有很多种创建索引的方式：
● 无索引——
表中数据呈堆结构(堆状或无组织堆状)。无索引对小型表和大型表都有好处。小型表由于访问的物理空间较少，没有索引就不必同时访问表和索引，因而提高了整体访问性能。大型表的读取性能则完全只与应用程序需求有关。如果以前从不使用索引，就不必使用。有时甚至抛弃一些参照完整性键和索引也有好处。
● 静态数据——
没有索引时，小型静态数据表的性能往往更好。但要注意两个潜在问题：删除外键索引会导致严重的锁定问题，因而会极大地降低性能；即使在小型静态表中，为所有表建立索引(尤其是使用唯一主键索引)往往有利于高度复杂的多表连接。
● 动态数据——
动态数据就是时刻变化的数据(如事务数据)。这种数据的索引会频繁变化，容易出现溢出并要求频繁刷新。对动态数据使用索引要非常谨慎。在特定数据库中，一般为动态数据选择最合适的默认索引类型。默认索引类型通常是某种二叉树索引结构。当数据变化时，其他索引类型包括预计算结构如ISAM、散列表和位图几乎都会立即溢出。
提示：
索引溢出对性能而言是很糟的。当某些索引类型发生索引溢出时，表中数据的一些修改就无法放置到该表原始结构的正确位置。这是由这些索引的工作方式决定的。如果在修改大量数据时发生溢出，就会使磁盘存储器出现大量随机I/O来查找数据。这样会导致非常严重的性能问题。
● 只读报表索引——
与动态数据索引不同，只读数据由于数据不易发生变化，因此索引也更加灵活。在数据仓库中，只读索引经常是专门为只读查询设计的。此类索引类型是容易发生溢出的预建结构，但用于只读I/O活动时的效率很高。只读索引(位图、群集和散列表)这种专用索引类型在高动态环境中的效率很低。
● 唯一索引与非唯一索引——
唯一索引是指一张表中只允许有一个值的索引。要谨慎使用唯一索引，因为对唯一索引字段的插入和更新都需要扫描整个索引空间(验证值的唯一性)。非唯一索引允许多个记录拥有相同值，典型如外键索引。唯一索引能提高性能，一般用于主键。唯一索引提高性能的原因是能够搜索索引子集来查找记录，理论上能够减少I/O活动以及索引结构的遍历。
● 单字段索引与多字段索引——
多字段索引通常也称为复合字段索引。单字段索引比复合多字段索引更为高效。原因很简单，字段越少，搜索范围就越小。同样，字段数较少说明索引远小于其父表。表和索引的尺寸相差越大，索引对I/O活动的减少作用就越强，较大的表中犹为如此。
● 索引的数据类型——
最好使用整数。整数即小数点右边没有数字的数。其他数据类型的长度和内容总是具有更强的可变性。定长字符串的效率不如整数，但如果字符串的字符数较少，也可以用于构造索引，例如使用代号的情况。代号常用于表示结构，如美国各州的代号名称。例如，NY表示纽约州而CA表示加利福尼亚。数字仍然更好一些，因为数字只有10个。按照字母表，字符串会有26种不同变化，加上10种数字(字符串能够同时包含字母与数字)，再加上所有各种标点和其他特殊字符。
● 为性能牺牲参照完整性——
有时这是个好办法，但多数情况下并非如此。删除外键索引会导致严重的锁定问题。参照完整性使用主键和外键验证相关表间记录的关系。如果需要进行大量验证，而表中外键又没有外键索引，那么就会频繁扫描整个子表，这样不仅索引会出现争用，整个表都会出现巨大的争用。
● 辅助索引调整—— 辅助索引通常也称为次索引(secondary
indexing)。辅助索引包括数据库模型中为表创建的索引，且这些索引均不是参照完整性约束的一部分。很多情况下，使用辅助索引其实是数据库模型与应用程序功能需求的不匹配。辅助索引过多意味着数据库模型无法满足应用程序的要求。报表应用程序或数据仓库应用程序需求通常会要求更多地使用辅助索引。
8.4.3 不使用索引的情况
也许最重要的问题是什么时候不应该创建索引？有些情况下索引会有损于性能，因此不应该使用索引。有时(实际上是很常见)，忽略索引并读取整个表反而能提高查询性能。下文解释了避免使用索引的情况。
● 如果表中字段数较少且每次从表中读取的记录百分比较大，那么索引对该表就没有好处。因为创建索引并不能减少全表扫描。注意，删除主键或外键不是好的做法。
●
读取小型静态数据表时，对表的扫描要比先扫描索引再引用表中数据更快。举例说明，操作系统(UNIX、Windows、Linux及其他操作系统)底层I/O活动有时是按块或按页读取的。页的尺寸有很多种，但在数据库层次上，页通常是2KB、4KB、8KB、16KB甚至有时达到32KB。所要阐述的问题是，如果表中记录数足够少，能够放在单个物理页中，那么读取索引再指向表就毫无意义了。读取索引再读取表相当于读取两页，而直接读取表则只有1个页上的1个I/O活动。
● 通常，为报表创建的表或在数据仓库周期性追加(批量更新)期间创建的表都是按要求排序的。
●
通常应该对表中小部分字段创建索引。与原始表相比，大型复合索引可能相对较大。索引与表的相对尺寸是很重要的。索引尺寸与表实际尺寸的比例越大，需要读取的物理空间量就越多，差值越小帮助作用就越小。包含NULL值的字段还可能加剧这种情况。如果表中包含大量NULL值，读取整个表要比读取较大的复合字段索引更快。因此不必创建包含所有字段的复合索引。由于复合字段结构需要用到全部字段，有时可以创建重复值索引。部分索引的效率比无索引高，有时也比复合字段索引的复合字段组高。
这即是索引。索引对数据库建模和数据库总体性能非常重要，某些情况下不使用索引甚至也体现了索引的重要性。正如该用索引而不用会降低性能一样，过度使用索引也会降低性能。
下面介绍视图的用法。
最重要的是，创建索引要比不创建索引更为慎重。很多数据库都因过度使用索引而变得错综复杂，在很长时间后，之前创建的索引和创建原因已经不得而知。当然不要害怕创建索引。正确的思想是，已经存在的索引并不表示该索引应该存在。
8.4.1 索引类型
有些数据库具有不同类型的索引。关系数据库中最常使用其中一种索引类型，这种索引通常是某种形式的二叉树(B树)索引。其他索引类型则很少见到，并且只适用于一些特殊情况。使用特殊类型的索引，如ISAM索引、散列索引和位图索引时，要注意需求和后果。
提示：
不同数据库引擎会以不同方式来使用索引结构和索引方法。这里列出几个示例。有的数据库引擎允许创建分离的物理ISAM索引，有的数据库则使用ISAM算法来访问BTree索引。有些数据库允许创建散列索引作为分离的物理结构；另一些数据库则只允许对整个表中所有字段使用散列算法。有些数据库引擎允许创建BTree索引作为索引，并作为整个表的排序顺序。这样表及其所有字段都会成为B树索引(称为群集索引或索引组织表)。
上述索引的应用较为特殊且并不常用。同样要注意，对源表进行修改时，这类不常用的索引常常会引发溢出。实际上，不常用的索引类型经常会由于溢出而削弱性能。溢出是指表中数据变化后，为性能而创建的索引结构完全变化且部分索引无法使用的情况。通常此类不常用的索引类型大部分都不适合只读型环境，因此使用这类索引前一般需要事先考虑并谨慎使用。
数据库管理员应该密切关注数据库中的索引。当然平常没有足够的检查时间，但在发布应用程序前最好对所有索引再进行一次检查。开发人员经常会创建很多索引，有时在同一个应用程序中，每个开发人员都会创建自定义的索引组，造成过度索引。表中过多的索引会导致性能问题。实际上，对表执行表更改命令(如INSERT、UPDATE或DELETE命令)时，除表之外，还会对表中所有索引也执行相同的命令。例如在具有4个索引的表中插入1条记录，则实际上包含了5次数据库更改。
8.4.2 实际情况中的索引
在表中创建索引时，一般根据该表的功能不同，有很多种创建索引的方式：
● 无索引——
表中数据呈堆结构(堆状或无组织堆状)。无索引对小型表和大型表都有好处。小型表由于访问的物理空间较少，没有索引就不必同时访问表和索引，因而提高了整体访问性能。大型表的读取性能则完全只与应用程序需求有关。如果以前从不使用索引，就不必使用。有时甚至抛弃一些参照完整性键和索引也有好处。
● 静态数据——
没有索引时，小型静态数据表的性能往往更好。但要注意两个潜在问题：删除外键索引会导致严重的锁定问题，因而会极大地降低性能；即使在小型静态表中，为所有表建立索引(尤其是使用唯一主键索引)往往有利于高度复杂的多表连接。
● 动态数据——
动态数据就是时刻变化的数据(如事务数据)。这种数据的索引会频繁变化，容易出现溢出并要求频繁刷新。对动态数据使用索引要非常谨慎。在特定数据库中，一般为动态数据选择最合适的默认索引类型。默认索引类型通常是某种二叉树索引结构。当数据变化时，其他索引类型包括预计算结构如ISAM、散列表和位图几乎都会立即溢出。
提示：
索引溢出对性能而言是很糟的。当某些索引类型发生索引溢出时，表中数据的一些修改就无法放置到该表原始结构的正确位置。这是由这些索引的工作方式决定的。如果在修改大量数据时发生溢出，就会使磁盘存储器出现大量随机I/O来查找数据。这样会导致非常严重的性能问题。
● 只读报表索引——
与动态数据索引不同，只读数据由于数据不易发生变化，因此索引也更加灵活。在数据仓库中，只读索引经常是专门为只读查询设计的。此类索引类型是容易发生溢出的预建结构，但用于只读I/O活动时的效率很高。只读索引(位图、群集和散列表)这种专用索引类型在高动态环境中的效率很低。
● 唯一索引与非唯一索引——
唯一索引是指一张表中只允许有一个值的索引。要谨慎使用唯一索引，因为对唯一索引字段的插入和更新都需要扫描整个索引空间(验证值的唯一性)。非唯一索引允许多个记录拥有相同值，典型如外键索引。唯一索引能提高性能，一般用于主键。唯一索引提高性能的原因是能够搜索索引子集来查找记录，理论上能够减少I/O活动以及索引结构的遍历。
● 单字段索引与多字段索引——
多字段索引通常也称为复合字段索引。单字段索引比复合多字段索引更为高效。原因很简单，字段越少，搜索范围就越小。同样，字段数较少说明索引远小于其父表。表和索引的尺寸相差越大，索引对I/O活动的减少作用就越强，较大的表中犹为如此。
● 索引的数据类型——
最好使用整数。整数即小数点右边没有数字的数。其他数据类型的长度和内容总是具有更强的可变性。定长字符串的效率不如整数，但如果字符串的字符数较少，也可以用于构造索引，例如使用代号的情况。代号常用于表示结构，如美国各州的代号名称。例如，NY表示纽约州而CA表示加利福尼亚。数字仍然更好一些，因为数字只有10个。按照字母表，字符串会有26种不同变化，加上10种数字(字符串能够同时包含字母与数字)，再加上所有各种标点和其他特殊字符。
● 为性能牺牲参照完整性——
有时这是个好办法，但多数情况下并非如此。删除外键索引会导致严重的锁定问题。参照完整性使用主键和外键验证相关表间记录的关系。如果需要进行大量验证，而表中外键又没有外键索引，那么就会频繁扫描整个子表，这样不仅索引会出现争用，整个表都会出现巨大的争用。
● 辅助索引调整—— 辅助索引通常也称为次索引(secondary
indexing)。辅助索引包括数据库模型中为表创建的索引，且这些索引均不是参照完整性约束的一部分。很多情况下，使用辅助索引其实是数据库模型与应用程序功能需求的不匹配。辅助索引过多意味着数据库模型无法满足应用程序的要求。报表应用程序或数据仓库应用程序需求通常会要求更多地使用辅助索引。
8.4.3 不使用索引的情况
也许最重要的问题是什么时候不应该创建索引？有些情况下索引会有损于性能，因此不应该使用索引。有时(实际上是很常见)，忽略索引并读取整个表反而能提高查询性能。下文解释了避免使用索引的情况。
● 如果表中字段数较少且每次从表中读取的记录百分比较大，那么索引对该表就没有好处。因为创建索引并不能减少全表扫描。注意，删除主键或外键不是好的做法。
●
读取小型静态数据表时，对表的扫描要比先扫描索引再引用表中数据更快。举例说明，操作系统(UNIX、Windows、Linux及其他操作系统)底层I/O活动有时是按块或按页读取的。页的尺寸有很多种，但在数据库层次上，页通常是2KB、4KB、8KB、16KB甚至有时达到32KB。所要阐述的问题是，如果表中记录数足够少，能够放在单个物理页中，那么读取索引再指向表就毫无意义了。读取索引再读取表相当于读取两页，而直接读取表则只有1个页上的1个I/O活动。
● 通常，为报表创建的表或在数据仓库周期性追加(批量更新)期间创建的表都是按要求排序的。
●
通常应该对表中小部分字段创建索引。与原始表相比，大型复合索引可能相对较大。索引与表的相对尺寸是很重要的。索引尺寸与表实际尺寸的比例越大，需要读取的物理空间量就越多，差值越小帮助作用就越小。包含NULL值的字段还可能加剧这种情况。如果表中包含大量NULL值，读取整个表要比读取较大的复合字段索引更快。因此不必创建包含所有字段的复合索引。由于复合字段结构需要用到全部字段，有时可以创建重复值索引。部分索引的效率比无索引高，有时也比复合字段索引的复合字段组高。
这即是索引。索引对数据库建模和数据库总体性能非常重要，某些情况下不使用索引甚至也体现了索引的重要性。正如该用索引而不用会降低性能一样，过度使用索引也会降低性能。
下面介绍视图的用法。
