加入收藏 | 设为首页 | 会员中心 | 我要投稿草根网（https://www.1asp.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!

当前位置：首页 > 教程 > 正文

MySQL 复合索引

发布时间：2022-11-29 12:44:18 所属栏目：教程来源：

导读：　　在 MySQL 中，复合索引也称为组合索引或者多列索引，是多列上的索引。 MySQL 复合索引允许您最多使用 16 个列。

　　MySQL 复合索引语法

　　要创建一个复合索引，请按照如下语法使用 CREATE INDEX 语句

　　在 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              |                                               |
　　mysql索引表_mysql创建表索引语句_删除mysql表索引

　　| 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索引表， 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 优化器的影响。。
　　

（编辑：草根网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

相关内容

推荐文章

站长推荐

热点阅读