mysql复合主键怎么创建 mysql复合语句

MySQL 复合索引

简介：在本例中，您将了解MySQL组合索引以及如何使用它来加速查询。

MySQL复合索引简介

复合索引是多列的索引。MySQL允许您创建一个最多包含16列的复合索引。

复合索引也称为多列索引。

查询优化器将复合索引用于测试索引中所有列的查询，或者测试第一列，前两列等的查询。

如果在索引定义中以正确的顺序指定列，则单个复合索引可以在同一个表上加速这些类型的查询。

要在创建表时创建复合索引，请使用以下语句：

CREATE TABLE table_name (
c1 data_type PRIMARY KEY,
c2 data_type,
c3 data_type,
c4 data_type,
INDEX index_name (c2,c3,c4)
);

在此语法中，复合索引由三列c2，c3和c4组成。

或者，您可以使用以下CREATE INDEX语句将复合索引添加到现有表：

CREATE INDEX index_name

ON table_name(c2,c3,c4);

请注意，如果您在(c1，c2，c3)上有复合索引，则您将在以下列组合之一上建立索引搜索功能：

(c1)
(c1,c2)
(c1,c2,c3)
例如：
SELECT
*
FROM
table_name
WHERE
c1 = v1;
SELECT
*
FROM
table_name
WHERE
c1 = v1 AND
c2 = v2;
SELECT
*
FROM
table_name
WHERE
c1 = v1 AND
c2 = v2 AND
c3 = v3;

如果列不形成索引的最左前缀，则查询优化器无法使用索引执行查找。例如，以下查询无法使用复合进行查找：

SELECT
*
FROM
table_name
WHERE
c1 = v1 AND
c3 = v3;
MySQL综合索引示例
我们将使用示例数据库中的employees表进行演示。
+----------------+
| employees |
+----------------+
| employeeNumber |
| lastName |
| firstName |
| extension |
| email |
| officeCode |
| reportsTo |
| jobTitle |
+----------------+
8 rows in set (0.14 sec)
以下语句在lastName和firstName列上创建复合索引：
CREATE INDEX name
ON employees(lastName, firstName);

首先，name索引可用于指定lastName值的查询中的查找，因为lastName列是索引的最左前缀。

其次，name索引可用于指定lastName和firstName值组合的值的查询。

name索引用于在以下的查询查找：

1)查找姓氏为的员工 Patterson
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson';

此查询使用名称索引，因为索引的最左边前缀(即lastName列)用于查找。

您可以通过EXPLAIN在查询中添加子句来验证这一点：

EXPLAIN SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson';

这是输出：

+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | name | name | 152 | const | 3 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
2)查找姓氏Patterson和名字的员工Steve：
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
firstName = 'Steve';

在此查询中，两个lastName和firstName列都用于查找，因此，它使用name索引。

我们来核实一下：

EXPLAIN SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
firstName = 'Steve';

输出是：

+----+-------------+-----------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | name | name | 304 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.08 sec)
3)查找姓氏Patterson和名字是Steve或的员工Mary：
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
(firstName = 'Steve' OR
firstName = 'Mary');

此查询类似于第二个查询，其中两个lastName和firstName列都用于查找。

以下语句验证索引用法：

EXPLAIN SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
(firstName = 'Steve' OR
firstName = 'Mary');

输出是：

+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | name | name | 304 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

查询优化器不能在以下查询中使用name索引进行查找，因为只使用了firstName不是索引最左前缀的列：

SELECT
firstName,
lastName,
email
FROM
employees
WHERE
firstName = 'Leslie';

类似地，查询优化器不能在以下查询中使用名称索引进行查找，因为firstName或者lastName列用于查找。

SELECT
firstName,
lastName,
email
FROM
employees
WHERE
firstName = 'Anthony' OR
lastName = 'Steve';

在本教程中，您学习了如何使用MySQL组合索引来加速查询。