MySQL 复合索引

时间:2019-11-20 08:52:15  来源:igfitidea点击:

简介:在本示例中,您将了解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复合索引示例

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

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

CREATE INDEX name 
ON employees(lastName, firstName);

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

其次,名称索引可用于为lastName和firstName值的组合指定值的查询。

因此,名称索引用于以下查询中的查找:

1)查找姓氏为Patterson的员工

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

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

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

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

这是输出:

2)查找姓氏为Patterson,名字为Steve的员工:

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

在此查询中,lastName和firstName列均用于查找,因此,它使用名称索引。

让我们验证一下:

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

输出为:

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');

输出为:

查询优化器无法将名称索引用于以下查询中的查找,因为仅使用了不是索引最左前缀的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复合索引来加速查询。