[TOC] ***** # 13.1 索引 当向一个表中插入一行时，数据库服务器不会试图将数据放到表里任何特定的地方。例如，要向 department表增加一行，那么服务器不会依据 dept\_id列的数字顺序或者 name 列的字母顺序存放该行。相反，服务器只是简单地将数据存放在文件中下一个可存放 的位置（服务器为每个表预留了一系列空间）。因此，当查询 department 表时，服务器需要通过检查表中的每一行来完成查询。 **表扫描** 为了寻找所有名字以 A 开头的部门，服务器必须访问 department 表中的每一行并检查name 列的内容。如果部门名以 A 开头，就将该行加入结果集。这种类型的访问称为表扫描。  **索引** 类似英语单词书的索引。 索引是一种以特定顺序保存的表。索引不包含实体中的所有数据，包含用于定位表中行的列和这些行的物理位置信息索引的作用就是便捷化检索表中行和列的子集，不需要检查表中的每行。 ## 13.1.1 创建索引 再回到 department 表，为 name 列添加索引可以加速任何指定全部或者部分部门名字的 查询以及 update 或 delete 操作。  有了索引后，如果索引有利于改善查询，查询优化器（第 3 章中讨论过）就可以选择索引（假如 department 表中只有 3 行，那么优化器选择忽略索引而直接读取整个表可能会更合理）。如果表中的索引不止一个，那么优化器就必须判断对于特定的 SQL 语句使用哪个索引最有利。  **查看索引** 所有数据库服务器都允许读者查看可用索引。 MySQL使用 show 命令查看指定表中的所有索引，例如： ``` SHOW INDEX FROM department ``` 结果显示 department 表共有两个索引：一个是 dept\_id 列的索引 PRIMARY，另一个是 name 的索引 dept\_name\_idx  创建表时， MySQL 自动为主键列(dept\_id)生成索引，生成索引名PRIMARY。  ***** **删除索引** mysql:   ***** **唯一索引** department 表中不能存在 两个相同名字的部门。读者可以通过 [department.name](http://department.name) 创建唯一索引限制出现重复部门名字。这里的唯一索引起了多重作用，除了提供常规索引的所有好处，还作为一种机制限制索引列出现重复值。无论是插入一行还是修改索引列，数据库服务器都会检查唯一索引以判断该值是否已存在于本表的某一行 **创建唯一索引**  ***** **多列索引** 除了上面涉及的单列索引，读者还可以创建跨越多列的索引。例如，使用姓氏和名字查找雇员，读者就可以为这两列一起创建索引  这个索引在两种查询中是有用的：先指定了姓氏，再指定了名字。 * 但它不适合用于只指定客户名字的查询中。查找某个人的 电话号码。如果知道此人的姓名就可以用电话簿快速查到号码，因为电话簿是先依据姓氏顺序，再依据名字顺序组织的；如果只知道此人名字就必须浏览电话簿中每个条目来查找。 * 因此，在创建多列索引时，读者必须仔细考虑哪一列作为第一列，哪一列作为第二列等，这样索引才会尽可能的有用。如果需要保证充分的响应时间，也可以基于不同顺序为同一列集创建多列索引。 ## 13.1.2 索引类型 **B 树索引** MySQL、 Oracle 数据库和 SQL Server 默认都是 B 树索引。 B 树索引以树结构组织，它有一个或多个分支节点，分支节点又指向单级的叶节点。分支节点用于遍历树，叶节点则保存真正的值和位置信息。 **employee.lname 列的 B 树索引**  如果读者发起一个查询，检索所有姓氏以 G 开头的雇员，那么服务器将首先查找顶分支节点（称为根节点），接着顺着指针前进到姓氏以 A 到 M 开始的分支节点，然后服务器会在此分支节点内依次查看直至找到姓氏以 G 到 I 开始的叶节点，最后服务器开始读叶子中的数据直至遇到一个不以 G 开头的值（此时，这个值是 Hawthorne）。 当向 employee 表中插入、更新和删除行时，服务器会尽力保持树的平衡，这样就不会出现根节点的某一侧拥有比另一侧多得多的分支节点/叶节点。服务器通过增加或删除分支节点重新将值分配得更加均匀。通过保持树的均匀，不需要遍历多层分支节点，服务器就能够快速地到达叶节点查找到需要的值。 **位图索引(如书)** 为了能够快速检索某一特定类型（比如支票账户、储蓄账户）的所有账户，读者可能希望为 account.product\_cd 列创建索引，但是，由于总共只有 8 种不同的产品，并且有些产品比其他产品受欢迎的多，所以客户数目的不断增长会使 B 树索引很难继续维持平衡。对于那些包含少量值却占据了大量行（所谓低基数）的列，应该采用不同的索引策略。 为了更有效地处理这个问题， Oracle 数据库引入了位图索引，它为存储在某列中的每个值生成一个位图。 account.product\_cd 中数据的位图索引大致如图 13-2 所示  未完 **文本索引(如书)** # 13.1.3 如何使用索引 服务器通常首先利用索引快速定位特定表中的行，之后再访问相关表提取用户请求的为指定的客户聚合账户余额:  用explain 语句请求MySQL 查询优化器显示查询的执行计划   * 使用索引 fk\_a\_cust\_id 查找 account 表中满足 where 子句的行； * 读取索引后，服务器预计会读取 account 表的所有 24 行以聚合可用余额数据，这是因为它不知道除了 ID 号为 1、 5、 9 和 11 的客户外，还可能有其他客户。 索引 fk\_a\_cust\_id 是由服务器自动生成的又一个索引，但是这次自动生成的原因是外键约束而不是主键约束（本章稍后作更多介绍）。 fk\_a\_cust\_id 是 account.cust\_id 列的索引， 因此服务器先使用索引定位 account 表中的 ID 为 1、 5、 9 和 11 的客户，然后访问这些行，再实现检索和聚合可用余额数据  * 服务器可以使用索引定位关联表中的行。 * 或者只要索引包含查询需要的所有列，服务器可以把索引当做表一样使用。 ## 13.1.4 索引的不足 每个索引事实上都是一个表。因此，每次在对表添加或者删除行时，表中的所有索引必须被修改；当更新行时，受到影响的列的任何索引也必须被修改。因此，索引越多，服务器就需要做越多的工作来保持所有模式对象最新，当然，这将会拖慢服务器处理任务的速度。 仅当出现清晰需求时才添加索引。如果有特殊目的需要索引，比如每月例行维护程序，那么读者可以添加索引，运行程序，然后删除索引，下次需要时再如此重复一遍。对于数据仓库来说，用户在营业期间生成报表和特定查询时，索引至关重要，但是当数据被彻夜装载到数据仓库时，就会出现问题，所以一种常见的做法是：装载数据前删除索引，然后在仓库开放营业前重建它们 **索引策略:** * 确保所有主键列被索引（大部分服务器会在创建主键约束时自动生成唯一索引）。针对多列主键，考虑为主键列的子集构建附加索引，或者以与主键约束定义不同的顺序为所有主键列另外生成索引。 * 为所有被外键约束引用的列创建索引。 服务器在准备删除父行时会查找以确保没有子行存在，为此它发出一个查询在索引中搜索列中的特定值，如果该列没有索引， 那么服务器必须扫描整个表。 * 索引那些被频繁检索的列。除了短字符串（3～50 个字符）列，大多数日期列也是不错的候选。