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

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. MySQL可以对测试索引中所有列的查询使用多个列索引，或者只测试第一列、前两列、前三列的查询，依此类推。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)
);

The name index is an index over the last_name and first_name columns. name索引是对last_name列和first_name列的索引。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. 索引可用于查询中的查找，这些查询为last_name和first_name值的组合指定一个已知范围内的值。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). 它还可以用于只指定last_name值的查询，因为该列是索引最左侧的前缀（如本节后面所述）。Therefore, the name index is used for lookups in the following queries:因此，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';

However, the name index is not used for lookups in the following queries:但是，name索引不用于以下查询中的查找：

SELECT * FROM test WHERE first_name='John';

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

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

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

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. 如果col1和col2上存在多列索引，则可以直接获取相应的行。If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (see Section 8.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上存在单独的单列索引，则优化器会尝试使用索引合并优化（请参阅第8.2.1.3节，“索引合并优化”），或者通过确定排除更多行的索引并使用该索引来获取行，从而尝试查找限制性最强的索引。

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).例如，如果在(col1, col2, col3)上有一个三列索引，则在(col1)、(col1, col2)和(col1, col2, col3)上有索引搜索功能。

MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. 如果列不构成索引最左边的前缀，MySQL就不能使用索引执行查找。Suppose that you have the SELECT statements shown here:假设您有如下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;

If an index exists on (col1, col2, col3), only the first two queries use the index. 如果(col1, col2, col3)上存在索引，则只有前两个查询使用该索引。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).第三个和第四个查询确实涉及索引列，但不使用索引执行查找，因为(col2)和(col2, col3)不是(col1, col2, col3)最左边的前缀。