MySQL 的索引

索引简介

在 MySQL 中，索引（index）也叫做键（key），它是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

索引优化是对查询性能优化最有效的手段。

在 MySQL 中，通常我们所指的索引类型，有以下几种：

普通索引

普通索引（index 或 key）是指索引类型为 normal 的索引，或者创建索引时没有明确指定类型的索引。普通索引是使用最普遍的索引类型。

主键索引

主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

唯一索引

唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

全文索引

全文索引（Full Text），可以提高全文搜索的查询效率，一般使用 Sphinx 替代。但 Sphinx 不支持中文检索，Coreseek 是支持中文的全文检索引擎，也称作具有中文分词功能的 Sphinx。实际项目中，用的是 Coreseek。

外键索引

外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

注意：只有 InnoDB 存储引擎的表才支持外键。外键字段如果没有指定索引名称，会自动生成。如果要删除父表（如类目表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。 创建表的时候，可以给字段设置外键。由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。

复合索引

在 MySQL 中，索引是在存储引擎层实现的，而不是在服务器层。如果没有特别指明类型，那多半说的就是 BTree 索引。不同的存储引擎以不同的方式使用 BTree 索引，性能也各不相同。例如：MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原始的数据格式存储索引。再如 MyISAM 通过数据的物理位置引用被索引的行，而 InnoDB 则根据主键聚集被索引的行。

BTree 对索引列是顺序存储的，因此很适合查找范围数据。它能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。

复合索引也称组合索引、多列索引，是指包含多个字段的索引。

复合索引对多个字段值进行排序的依据是创建索引时列的顺序。

create table people (
    id int unsigned not null auto_increment primary key comment '主键id',
    last_name varchar(20) not null default '' comment '姓',
    first_name varchar(20) not null default '' comment '名',
    birthday date not null default '1970-01-01' comment '出生日期',
    gender tinyint unsigned not null default 3 comment '性别：1男，2女，3未知',
    key last_first_bir(last_name, first_name, birthday)
) engine=innodb default charset=utf8;

插入一些测试数据：

INSERT people(id,last_name,first_name,birthday,gender) 
VALUES 
(0, 'Clinton', 'Bill', '1970-01-01', 3),
(0, 'Allen', 'Cuba', '1970-01-01', 3),
(0, 'Bush', 'George', '1970-01-01', 3),
(0, 'Smith', 'Kim', '1970-01-01', 3),
(0, 'Allen', 'Cally', '1989-06-08', 3);

我们创建了一个复合索引 key last_first_bir(last_name, first_name, birthday)，对于表中的每一行数据，该索引中都包含了姓、名和出生日期这三列的值。索引也是根据这个顺序来排序存储的，如果某两个人的姓和名都一样，就会根据他们的出生日期来对索引排序存储。

Btree 索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀查找。

复合索引生效的几种方式

全值匹配

全值匹配指的是和索引中的所有列进行匹配。例如：查找姓 Allen、名 Cuba、出生日期为 1960-01-01 的人。

select id,last_name,first_name,birthday 
from people 
where last_name='Allen' and first_name='Cuba' and birthday='1970-01-01';

explain 分析的结果如下：

+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_first_bir | last_first_bir | 127     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

说明： 改变 where 子句中的各个参数的顺序，对分析结果没有影响。

匹配复合索引的最左前缀

比如只使用索引的第一列，查找所有姓为 Allen 的人。

select id,last_name,first_name,birthday 
from people where last_name='Allen';

explain 分析结果如下：

mysql> explain select id,last_name,first_name,birthday
    -> from people where last_name='Allen';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | last_first_bir | last_first_bir | 62      | const |    2 |   100.00 | Using index |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

匹配列前缀

比如只匹配索引的第一列的值的开头部分，查找所有姓氏以 A 开头的人。

select id,last_name,first_name,birthday 
from people where last_name like 'A%';

explain 分析结果如下：

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | range | last_first_bir | last_first_bir | 62      | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

匹配范围值

比如范围匹配姓氏在 Allen 和 Clinton 之间的人。

select id,last_name,first_name,birthday 
from people where last_name BETWEEN 'Allen' And 'Clinton';

explain 分析结果如下：

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | range | last_first_bir | last_first_bir | 62      | NULL |    4 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

精确匹配第一列并范围匹配后面的列

比如查找姓 Allenÿ