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 (seeSection 8.3.5, “Column Indexes”).

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.

多个列索引可以被视为一个排序array, the rows of which contain values that are created by concatenating the values of the indexed columns.

选择* FROMtbl_nameWHEREhash_col=MD5(CONCAT(val1,val2)) ANDcol1=val1ANDcol2=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) );

Thenameindex is an index over thelast_nameandfirst_namecolumns. The index can be used for lookups in queries that specify values in a known range for combinations oflast_nameandfirst_namevalues. It can also be used for queries that specify just alast_namevalue because that column is a leftmost prefix of the index (as described later in this section). Therefore, thenameindex is used for lookups in the following queries:

SELECT *从测试last_name =“琼斯”;选择* 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, thenameindex isnotused for lookups in the following queries:

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

Suppose that you issue the following选择statement:

选择* FROMtbl_nameWHERE col1=val1AND col2=val2;

If a multiple-column index exists oncol1andcol2, the appropriate rows can be fetched directly. If separate single-column indexes exist oncol1andcol2, the optimizer attempts to use the Index Merge optimization (seeSection 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.

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 cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the选择statements shown here:

选择* FROMtbl_nameWHERE col1=val1; SELECT * FROMtbl_nameWHERE col1=val1AND col2=val2; SELECT * FROMtbl_nameWHERE col2=val2; SELECT * FROMtbl_nameWHERE col2=val2AND col3=val3;

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).