MySQL索引使用原则持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第10天，点击查看活动详情

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第10天，点击查看活动详情

概述

作为程序员对于MySQL的主要优化手段就是创建索引，使得查询的SQL语句能尽量的命中索引，以便提高查询效率。但是索引也是需要占用大量的磁盘空间的，而且在插入数据的时候除了在主键索引上插入数据外，同时还需要维护表中的每一个索引。如果一味增加索引，可能会适得其反。本篇主要是介绍一下在创建索引的时候如何进行选择。

列的离散度

第一个叫做列的离散度，先来看一下列的离散度的公式：

count(distinct(column_name)) : count(*)

列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

id	name	gender	phone
1	张三	0	13100000000
2	李四	1	13111111111
3	王五	0	13122222222
4	赵六	1	13133333333
5	孙七	0	13144444444
6	周八	1	13155555555
7	吴久	0	13166666666

如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

当我们用在 gender 上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在 gender列上面创建一个索引，然后看一下执行计划。

这里需要扫描的行是 X 行。

ALTER TABLE user_innodb DROP INDEX idx_user_gender; 
ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗时比较久 EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0;

show indexes from user_innodb;

而 name 的离散度更高，比如“青山”的这名字，只需要扫描一行。

ALTER TABLE user_innodb DROP INDEX idx_user_name;
ALTER TABLE user_innodb ADD INDEX idx_user_name (name); 
EXPLAIN SELECT * FROM `user_innodb` WHERE name = '青山';

联合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引，举例：查询成绩的时候必须同时输入身份证和考号。单列索引可以看成是特殊的联合索引。比如 user 表给 name 和 phone 建立了一个联合索引。

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。

从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。

这个时候我们使用 where name= 'shahuang' and phone = '131xx '去查询数据的时候， B+Tree 会优先比较 name来确定下一步应该搜索的方向，往左还是往右。如果name相同的时候再比较phone。但是如果查询条件没有name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

什么时候用到联合索引

假设有一个联合索引 index_a_b_c('a','b','c')。

where a and b and c 索引字段全部命中

where a and c 索引命中a
where a and b 索引命中a、b
where b and c 索引未命中
where a and b order by c 索引全部命中

1）使用两个字段，用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= 'sundong' AND phone = '15204661800';

2）使用左边的 name 字段，用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= 'sundong';

3）使用右边的 phone 字段，无法使用索引，全表扫描：

EXPLAIN SELECT * FROM user_innodb WHERE phone = '15204661800

如何创建联合索引

CREATE INDEX idx_name on user_innodb(name);
CREATE INDEX idx_name_phone on user_innodb(name,phone);

当创建一个联合索引的时候，按照最左匹配原则，用左边的字段 name 去查询的时候，也能用到索引，所以第一个索引完全没必要。

相当于建立了两个联合索引(name),(name,phone)。如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：

index(a)
index(a,b)
index(a,b,c)

用 where b=? 和 where b=? and c=? 是不能使用到索引的。

覆盖索引

回表：

非主键索引，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。例如：select * from user_innodb where name = 'qingshan';

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

先来创建一个联合索引：

Version:0.9 StartHTML:0000000105 EndHTML:0000001962 StartFragment:0000000141 EndFragment:0000001922

-- 创建联合索引

ALTER TABLE user_innodb DROP INDEX comixd_name_phone;

ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

这三个查询语句都用到了覆盖索引：

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= 'qingshan' AND phone = ' 13666666666'; 
EXPLAIN SELECT nameFROM user_innodb WHERE name= 'qingshan' AND phone = ' 13666666666'; 
EXPLAIN SELECT phone FROM user_innodb WHERE name= 'qingshan' AND phone = ' 13666666666';

select * ，此处用不到覆盖索引。

假如一个主键索引，其他 3 个字段建立辅助索引，select *也属于覆盖索引。

Extra里面值为“Using index”代表使用了覆盖索引。

索引的创建

1、在用于 where 判断 order 排序和 join 的（on）字段上创建索引

2、索引的个数不要过多。

——浪费空间，更新变慢。

3、过长的字段，建立前缀索引。

4、区分度低的字段，例如性别，不要建索引。

——离散度太低，导致扫描行数过多。

5、频繁更新的值，不要作为主键或者索引。

——页分裂

6、随机无序的值，不建议作为主键索引，例如身份证、UUID。

——无序，分裂

7、组合索引把散列性高（区分度高）的值放在前面

8、创建复合索引，而不是修改单列索引

什么时候用不到索引？

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式计算（+ - * /）：www.runoob.com/mysql/mysql…

explain SELECT * FROM `t2` where id+1 = 4;

2、字符串不加引号，出现隐式转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

explain SELECT * FROM `user_innodb` where name = 136;
explain SELECT * FROM `user_innodb` where name = '136';

3、like 条件中前面带%

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗？为什么？

explain select *from user_innodb where name like 'wang%';
explain select *from user_innodb where name like '%wang';

过滤的开销太大。这个时候可以用全文索引。

4、负向查询 NOT LIKE 不能：

explain select *from employees where last_name not like 'wang'

!= （<>）和 NOT IN 在某些情况下可以：

explain select *from employees where emp_no not in (1)
explain select *from employees where emp_no <> 1

注意跟数据库版本、数据量、数据选择度都有关系。

其实，用不用索引，最终都是优化器说了算。

优化器是基于什么的优化器？基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer），也不是基于语义。怎么样开销小就怎么来。

使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引，什么情况一定不用索引的规则。