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, bc 列上定义上了索引:

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_namefirst_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_namefirst_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_namefirst_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 优化器的影响。。

当您定义多列索引时,您应该始终考虑业务上下文以确定哪些列经常用于查找,并在定义索引时将这些列放在列列表的开头。