复合索引 优化和适用范围

作者: Note 分类: mysql学习 发布时间: 2017-04-25 14:20
索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。例如,以下语句创建一个具有两列的 复合索引:

CREATE INDEX name
ON employee (emp_lname, emp_fname)
如 果第一列 不能单独提供较高的选择性,复合索引将会非常有用。例如,当许多雇员具有相同的姓氏时,emp_lname 和 emp_fname 上的复合索引非常有用。因为每个雇员都有唯一的 ID,所以 emp_id 和 emp_lname 上的复合索引可能没有用处,因此列 emp_lname 不会提供任何附加选择性。

利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独 的索引。复合索引的结构与电话簿类似,它首先按姓氏对雇员 进行排序,然后按名字对所有姓氏相同的雇员进行排序。如果您知道姓氏,电话簿将非常有用,如果您知道名字和姓氏,电话簿则更为有用,但如果您只知道名字而 不知道姓氏,电话簿将没有用处。

压缩的 B 树索引方法可显著提高复合索引的性能。

列顺序
在创建复合索引时, 应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

如 果您很可能仅对一个列多次执行搜索,则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索,则应该创建另一个仅包含第二 列的索引。

包含多个列的主键始终会自动以复合索引的形式创建索引,其列的顺序是它们在表定义中出现的顺序,而不是在主键定义中指定的顺 序。您应该考虑将通过主键来执行的搜索,以确定哪一列应该排在最前面。在后面的任何被频繁搜索的主键列上,应该考虑添加额外的索引。

例 如,假设您在两个列上创建一个复合索引。一个列包含雇员的名字,另一个列包含雇员的姓氏。您可以创建一个先包含名字后包含姓氏的索引。或者,您也可以创建 一个先包含姓氏后包含名字的索引。虽然这两个索引以两个列组织信息,但它们具有不同的功能。

CREATE INDEX fname_lname
ON employee emp_fname, emp_lname;
CREATE INDEX lname_fname
ON employee emp_lname, emp_fname;
假设您需要搜索名字 John。唯一有用的索引是在索引的第一列包含名字的索引。由于名字为 John 的雇员会出现在索引中的任意位置,因此先按姓氏再按名字组织的索引没有用处。

如果您认为很可能需要仅按名字或仅按姓氏查找雇员,则应该创 建这两个索引。

或者,您也可以创建两个索引,一个索引仅包含一个列。但是,请注意,Adaptive Server Anywhere 在处理单个查询时只使用一个索引来对任何一个表进行访问。即使您知道名字和姓氏,Adaptive Server Anywhere 也可能需要读取额外的行,以查找包含正确姓氏的行。

当您使用 CREATE INDEX 命令创建索引时(如上例所示),列会按命令中所示的顺序创建。

主键索引和列顺序
列在主键索引中的顺序被强制为与列在表定义中出现 的顺序相同,这与 PRIMARY KEY 约束中指定的列顺序无关。此外,Adaptive Server Anywhere 还将强制一个附加约束:表的主键列必须位于每个行的开头。因此,如果主键被添加到现有表中,服务器就可以重写整个表以确保键列位于每个行的开头。

在 多个列出现在主键中的情况下,您应该考虑所需的搜索类型。如果合适,应切换列在表定义中的顺序,使最常搜索的列排在最前面,或者根据需要为其它列创建单独 的索引。

复合索引和 ORDER BY
缺省情况下,索引的列按升序排列,但您可以选择通过在 CREATE INDEX 语句中指定 DESC 来将这些列按降序排列。

只要 ORDER BY 子句仅包含索引中的列,Adaptive Server Anywhere 就可以选择使用索引来优化 ORDER BY 查询。此外,索引列的排序方式必须与 ORDER BY 子句完全相同或完全相反。对于单列索引,这种排序方式始终会使查询可以得到优化,但复合索引则需要稍微多考虑一些问题。下表显示了一个两列索引的可能性。

索 引列 可优化的 ORDER BY 查询 不可优化的 ORDER BY 查询
ASC、ASC ASC、ASC 或 DESC、DESC ASC、DESC 或 DESC、ASC
ASC、DESC ASC、DESC 或 DESC、ASC ASC、ASC 或 DESC、DESC
DESC、ASC DESC、ASC 或 ASC、DESC ASC、ASC 或 DESC、DESC
DESC、 DESC DESC、DESC 或 ASC、ASC ASC、DESC 或 DESC、ASC

含有两个以上的列的索引遵循与上述规则 相同的一般规则。例如,假设您具有以下索引:

CREATE INDEX idx_example
ON table1 (col1 ASC, col2 DESC, col3 ASC)
在这种情况下,以下查询可以得到优化:

SELECT col1, col2, col3 from table1
ORDER BY col1 ASC, col2 DESC, col3 ASC

SELECT col1, col2, col3 from example
ORDER BY col1 DESC, col2 ASC, col3 DESC
索引不用于优化在 ORDER BY 子句中具有 ASC 和 DESC 的其它任何模式的查询。例如:

SELECT col1, col2, col3 from table1
ORDER BY col1 ASC, col2 ASC, col3 ASC
不 会得到优化。

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

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

− 1 = 3