6.多列索引

MySQL 可以创建复合索引（即多列上的索引）。一个索引最多可以包含 16 列。对于某些数据类型，可以对列的前缀进行索引。

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 值的查询，因为该列是索引的最左侧前缀。因此，名称索引用于以下查询中的查找：

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';

但是，名称索引不用于以下查询中的查找：

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 上存在单独的单列索引，优化器会尝试使用索引合并优化，或者尝试通过确定哪个索引排除更多行来查找限制最严格的索引并使用该索引来获取行。

如果表具有多列索引，则优化器可以使用索引的任何最左边的前缀来查找行。例如，如果在 (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) 的最左边前缀。