mysql 翻译系列 二十九

10.3.6 多列索引

MySQL可以创建复合索引（即对多个列进行索引）。一个索引最多可由16列组成。对于某些数据类型，可以对列的前缀进行索引（请参见10.3.5节 “列索引”）。

MySQL的多列索引可用于测试索引中所有列的查询，也可用于仅测试第一列、前两列、前三列等的查询。如果在索引定义中按正确顺序指定列，单个复合索引可以加快对同一表的多种查询的速度。

多列索引可被视为一个排序数组，其行包含通过连接索引列的值而创建的值。

注意

作为复合索引的替代方案，可以引入一个基于其他列信息 “哈希” 生成的列。如果此列较短、具有一定的唯一性且被索引，它可能比在多个列上创建的 “宽” 索引更快。在MySQL中，使用这个额外的列非常简单：

SELECT * FROM tbl_name
  WHERE hash_col=MD5(CONCAT(val1,val2))
  AND col1=val1 AND col2=val2;

假设一个表有如下定义：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name) 
);

name索引是对last_name和first_name列的索引。该索引可用于在查询中查找指定last_name和first_name值组合在已知范围内的行。它也可用于仅指定last_name值的查询，因为该列是索引的最左前缀（本节后面会详细介绍）。因此，name索引可用于以下查询中的查找：

SELECT * FROM test WHERE last_name='Jones'; 
SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John'; 
SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon'); 
SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

然而，name索引不适用于以下查询中的查找：

SELECT * FROM test WHERE first_name='John'; 
SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

假设执行以下SELECT语句：

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

如果在col1和col2上存在多列索引，则可以直接获取相应的行。如果在col1和col2上分别存在单列索引，优化器会尝试使用索引合并优化（请参见10.2.1.3节 “索引合并优化”），或者通过判断哪个索引排除的行数更多来找到最具限制性的索引，并使用该索引来获取行。

如果表有多列索引，优化器可以使用该索引的任何最左前缀来查找行。例如，如果在(col1, col2, col3)上有一个三列索引，那么就可以在(col1)、(col1, col2)和(col1, col2, col3)上进行索引搜索。

如果列没有构成索引的最左前缀，MySQL就无法使用该索引进行查找。假设有以下SELECT语句：

SELECT * FROM tbl_name WHERE col1=val1; 
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; 
SELECT * FROM tbl_name WHERE col2=val2; 
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果在(col1, col2, col3)上存在索引，只有前两个查询会使用该索引。第三和第四个查询虽然涉及索引列，但不会使用索引进行查找，因为(col2)和(col2, col3)不是(col1, col2, col3)的最左前缀。