MySQL 可以创建组合索引（即在多个列上创建索引）。一个索引可以包含最多 16 列。对于某些数据类型，您可以对列的前缀创建索引（参见 第 10.3.5 节，“列索引”）。MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 10.3.5, “Column Indexes”).

MySQL 可以对所有索引列进行测试的查询使用多列索引，也可以对仅测试第一列、前两列、前三列等的查询使用多列索引。如果在索引定义中以正确的顺序指定列，则单个复合索引可以加速同一表的多种查询。MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

多列索引可以被视为一个排序数组，其中的行包含由索引列的值连接而成的值。A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.

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

假设一个表具有以下规格：Suppose that a table has the following specification:

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 index is an index over the last_name以及first_name columns. The index can be used for lookups in queries that specify values in a known range for combinations of last_name以及first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index (as described later in this section). Therefore, the name index is used for lookups in the following queries:The name index is an index over the last_name and first_name columns. The index can be used for lookups in queries that specify values in a known range for combinations of last_name and first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index (as described later in this section). Therefore, the name index is used for lookups in the following queries:

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 索引 不会 用于以下查询中的查找：However, the name index is not used for lookups in the following queries:

SELECT * FROM test WHERE first_name='John';

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

假设你发出以下 SELECT 语句：Suppose that you issue the following SELECT statement:

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

如果在 col1 和 col2 上存在多列索引，可以直接获取相应的行。 如果在 col1 和 col2 上存在单独的单列索引，优化器会尝试使用索引合并优化（参见 第 10.2.1.3 节，“索引合并优化”），或者尝试通过决定哪个索引排除的行更多来找出最具限制性的索引，并使用该索引获取行。If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (see Section 10.2.1.3, “Index Merge Optimization”), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows.

如果表具有多列索引，优化器可以使用索引的任何最左前缀来查找行。例如，如果你在 (col1, col2, col3) 上有一个三列索引，你可以对 (col1)、(col1, col2) 和 (col1, col2, col3) 进行索引搜索。If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

如果列不形成索引的最左前缀，MySQL 将无法使用该索引进行查找。假设你有如下所示的 SELECT 语句：MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

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) 的最左前缀。If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but do not use an index to perform lookups because (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).