MySQL 索引使用的原则

在 MySQL 中，主要有四种类型的索引，分别为： B-Tree 索引， Hash 索引， Fulltext 索引和 R-Tree 索引。

Innodb 存储引擎的 B-Tree 索引使用的存储结构实际上是 B+Tree。本文总结的几个索引使用原则主要基于 Innodb 存储引擎，原理便是依托于 B+ 树的特性。

最左前缀原则

联合索引

• 建立联合索引的时候，区分度最高的字段在最左边
• 如果在 (a,b,c) 三个字段上建立联合索引，那么相当于建立 a| (a,b) | (a,b,c) 组索引
• 遇到范围查询（>、<、between、like）就会停止匹配，不能使用索引中范围条件右边的列（范围列可以用到索引），范围列之后列的索引全失效

存在非等号和等号混合判断条件，在建立索引时，把等号条件的列前置。
如：where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列

利用索引最左原则

select uid, login_time from user where login_name=? and passwd=?

可以建立 (login_name, passwd) 的联合索引。业务上几乎没有 passwd 的单条件查询需求，而有很多 login_name 的单条件查询需求。

范围查询失效

对于索引：index(a,b,c)

查询 where a=3 and b like 'abc%' and c=4 时，a能用，b能用，c不能用

LIKE 模糊查询

like 模糊查询也有最左前缀原则，使用通配符开头 %str 匹配将不使用索引。

ORDER BY、GROUP BY 的场景

如果有 order by、group by 的场景，注意利用索引的有序性。

1. order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
   例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
2. 如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，无法利用索引 (a,b) 排序。

ORDER BY

索引：name, age, pos

正常使用索引：

select * from user where name = 'zhangsan' and age = 20 order by age,pos;

导致额外的文件排序（会降低性能）：

-- 违反最左前缀法则
select name,age from user where name = 'zhangsan' order by pos;
-- 违反最左前缀法则
select name,age from user where name = 'zhangsan' order by pos,age;
-- 含非索引字段
select * from user where name = 'zhangsan' and age = 20 order by created_time,age;

GROUP BY

分组之前必排序（排序同 order by）。

以下正常使用索引排序，非正常使用会导致产生临时表（降低性能）。

select name,age from user where name = 'zhangsan' group by age;

不要在索引列上面做任何操作（计算、函数）

例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：

select * from doc where YEAR(create_time) <= '2016';

可优化为值计算，如下：

select * from doc where create_time <= '2016-01-01';

尽可能的考虑建立联合索引而不是单列索引

多个字段在一个索引上，那么将会节约很大磁盘空间，修改数据的操作效率也会提升。

限制每张表上的索引数量，单张表索引不超过 5 个

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间。

在常用于查询的条件上加索引

1. 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
2. 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
3. 多表 join 的关联列

不要将符合 1 和 2 中的字段的列都建立一个索引，将 1、2 中的字段建立联合索引效果更好。

对于频繁的查询优先考虑使用覆盖索引

包含了所有查询字段（where,select,order by,group by 包含的字段），避免 Innodb 表进行索引的二次查询。

在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。

引用

1. 优化技巧来写 SQL
2. MySQL索引原理及慢查询优化
3. MySQL性能优化[实践篇]-索引合并与复合索引
4. MySQL索引