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)
);
Code language: SQL (Structured Query Language) (sql)

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

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

CREATE INDEX index_name 
ON table_name(c2,c3,c4);
Code language: SQL (Structured Query Language) (sql)

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

(c1)
(c1,c2)
(c1,c2,c3)
Code language: SQL (Structured Query Language) (sql)

例如：

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;
Code language: SQL (Structured Query Language) (sql)

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

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c3 = v3;
Code language: SQL (Structured Query Language) (sql)

MySQL 复合索引示例

我们将使用示例数据库中的employees表进行演示。

以下语句在lastName和firstName列上创建复合索引：

CREATE INDEX name 
ON employees(lastName, firstName);
Code language: SQL (Structured Query Language) (sql)

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

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

因此， name索引用于以下查询中的查找：

1）查找姓氏为Patterson的员工

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';
Code language: SQL (Structured Query Language) (sql)

此查询使用名称索引，因为索引的最左侧前缀（即lastName列）用于查找。

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

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';
Code language: SQL (Structured Query Language) (sql)

这是输出：

2) 查找姓氏为Patterson 、名字为Steve的员工：

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';
Code language: SQL (Structured Query Language) (sql)

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

我们来验证一下：

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';
Code language: SQL (Structured Query Language) (sql)

输出是：

3) 查找姓氏为Patterson 、名字为Steve或Mary的员工：

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');
Code language: SQL (Structured Query Language) (sql)

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

以下语句验证索引的使用情况：

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');
Code language: SQL (Structured Query Language) (sql)

输出是：

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

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Leslie';
Code language: SQL (Structured Query Language) (sql)

同样，查询优化器无法在以下查询中使用名称索引进行查找，因为firstName或lastName列用于查找。

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Anthony' OR
    lastName = 'Steve';
Code language: SQL (Structured Query Language) (sql)

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

本教程有帮助吗？