MySQL 复合索引
本文讲述 MySQL 中的复合索引，即建立在多个列之上的索引。
在 MySQL 中，复合索引也称为组合索引或者多列索引，是多列上的索引。 MySQL 复合索引允许您最多使用 16 个列。
MySQL 复合索引语法
要创建一个复合索引，请按照如下语法使用 CREATE INDEX 语句：
CREATE INDEX index_name
ON table_name(column_1, column_2, column_3);
这里，为 column_1, column_2, 和 column_3 3 列创建了一个名为 index_name 的索引。
MySQL 复合索引规则
定义多列索引时，应将 WHERE 子句中常用的列放在索引列列表的开头，将不常用的列放在索引列列表的后面。否则， MySQL 优化器可能不会使用索引。
例如，以下语句在 a, b 和 c 列上定义上了索引：
CREATE INDEX index_name
ON table_name(a, b, c);
上述语法中，MySQL 优化器在以下情况下会考虑使用索引：
WHERE a = v1 and b = v2 and c = v3;
或者
WHERE a = v1 and b = v2;
或者
WHERE a = v1;
但是，在以下情况下不会考虑使用索引：
WHERE c = v3;
或者
WHERE b = v2 and c = v3;
也就说，如果条件列不构成索引的最左前缀，则查询优化器无法使用索引执行查找。
MySQL 多列索引示例
让我们在 Sakila 示例数据库中的 customer 表中演示 MySQL 多列索引。
通过下面的语句查看 customer 表的信息：
DESC customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| email | varchar(50) | YES | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| create_date | datetime | NO | | NULL | |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+通过 SHOW INDEXES 语句查看 customer 表中的索引：
SHOW INDEXES FROM customer;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customer | 0 | PRIMARY | 1 | customer_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_store_id | 1 | store_id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_address_id | 1 | address_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name | 1 | last_name | A | 598 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)在 customer 表中已经存在几个索引，为了演示多列索引，我们先删掉 idx_last_name 索引：
DROP INDEX idx_last_name ON customer;
使用下面的语句在 last_name 和 first_name 列上创建索引：
CREATE INDEX idx_last_name_first_name
ON customer (last_name, first_name);
再次通过下面的语句查看 customer 表的信息：
SHOW INDEXES FROM customer;
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customer | 0 | PRIMARY | 1 | customer_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_store_id | 1 | store_id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_address_id | 1 | address_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name_first_name | 1 | last_name | A | 598 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name_first_name | 2 | first_name | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)我们发现在 在 last_name 和 first_name 列上定义了 customer_last_name_first_name_idx 索引。
通过 EXPLAIN 语句查看以下语句是否使用了索引：
EXPLAIN
SELECT * FROM customer
WHERE last_name = 'A'
AND first_name = 'B';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | ref | idx_last_name_first_name | idx_last_name_first_name | 364 | const,const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)这里，当 SELECT 语句的 WHERE 条件中同时具有 last_name 和 first_name 时， MySQL 优化器选择使用索引。
通过 EXPLAIN 语句查看以下语句是否使用了索引：
EXPLAIN
SELECT * FROM customer
WHERE last_name = 'A';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | ref | idx_last_name_first_name | idx_last_name_first_name | 182 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)这里，当 SELECT 语句的 WHERE 条件中只有 last_name 时， MySQL 优化器选择使用索引。这是因为 last_name 列是索引中的第一列。
通过 EXPLAIN 语句查看以下语句是否使用了索引：
EXPLAIN
SELECT * FROM customer
WHERE first_name = 'B';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 599 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)这里，当 SELECT 语句的 WHERE 条件中只有 first_name 时， MySQL 优化器选择不使用索引。 这是因为 first_name 列不是索引的第一列。
结论
本文讨论了 MySQL 多列索引以及多列索引的顺序对与 MySQL 优化器的影响。
当您定义多列索引时，您应该始终考虑业务上下文以确定哪些列经常用于查找，并在定义索引时将这些列放在列列表的开头。