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;

如果要查询的列没有满足最左前缀，那么mysql查询优化器就无法使用索引来进行查找数据，下面的查询就不会使用索引：

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c3 = v3;

MySQL复合索引示例

注：使用employees表进行演示

使用以下语句在lastname和firstname上创建复合索引：

CREATE INDEX name ON employees(last_name,first_name);

首先，name索引可以用于条件为lastname的查询，因为lastname列是索引的最左前缀，其次name索引也可以用于那些条件为lastname和firstname的查询。

举个栗子：

找到employees表中last name是Bamford的人的信息：

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    employees
WHERE
    last_name = 'Bamford';

这个查询就会用到name这个索引，因为索引的最左前缀（即lastname这个列）被用于查找

可以使用EXPLAIN查看：

EXPLAIN SELECT first_name,last_name FROM employees WHERE last_name = 'Bamford';
+----+-------------+-----------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | employees | ref  | name          | name | 66      | const |  178 | Using where; Using index |
+----+-------------+-----------+------+---------------+------+---------+-------+------+--------------------------+

找出employees表中 last name是Bamford并且first name 是Aral的人：

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    employees
WHERE
    last_name = 'Bamford' AND
    first_name = 'Aral';

在这个查询中，last_name和first_name列被用来查找，所以也会用到name索引：

mysql> EXPLAIN SELECT first_name,last_name,birth_date FROM employees WHERE last_name = 'Bamford' AND first_name = 'Aral';
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-----------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra                 |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | employees | ref  | name          | name | 124     | const,const |    1 | Using index condition |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

找出employees表中last name是Bamford 并且first name是Aral或Yefim的人：

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    employees
WHERE
    last_name = 'Bamford' AND
    (first_name = 'Aral' OR 
    first_name = 'Yefim');

这个查询和第二个例子差不多，last_name和first_name都会被用来查找。

下面这个例子中MySQL查询优化器就不能使用name索引进行查找了，因为where中仅使用了不是name索引最左前缀的first_name列：

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    employees
WHERE
    first_name = 'Aral';

同样的，下面这个例子也不会使用name索引，因为first_name或last_name列都用于查找

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    employees
WHERE
    first_name = 'Anthony' OR
    last_name = 'Steve';