「MySQL 系列」- 索引的建立与使用

如何创建索引

在创建表的时候创建索引：

其中关键字 INDEX 可以用 KEY 替代，INDEX 前面可以加上索引的类型 UNIQUE | FULLTEXT | SPATIAL | NORMAL，默认是 NORMAL

CREATE TABLE `student` (
  `id` bigint NOT NULL,
  `name` varchar(30),
  PRIMARY KEY (`id`),
  INDEX `idx_name` (`name`)  -- 对 name 字段加上名为 idx_name 的索引
)

对已经存在的表添加索引：

ALTER TABLE student ADD INDEX idx_name (`name`)

创建索引的注意事项

选择合适的字段创建索引

并不是每个字段都适合创建索引，索引的创建也是要消耗磁盘空间的，并不是说索引就越多越好

1. 尽量选择不为 null 的字段建立索引

对于 Mysql 来说，含有空值的列很难进行查询优化，如果频繁查询的值又避免不了为 null，可以考虑使用 0，1 或者空字符这些短字符串进行替代。如果索引的字段可以为 NULL，索引的效率会下降很多，因为它们使得索引、索引的统计信息以及比较运算更加复杂

2. 选择区分度大列作为索引

举个例子，一张学生表里面有个字段是性别 sex， 它里面存的是男和女，各占约百分之五十，那这样即便你建立了索引，用哪个值都是搜出一半来，那这样还不如不建索引

MySQL有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描

惯用的百分比界线是"30%"。（匹配的数据量超过一定限制的时候查询器会放弃使用索引（这也是索引失效的场景之一哦）

3. 被频繁查询字段、作为查询条件

以上字段都可以考虑设计上索引

例：对于学生表（student）和成绩表（grade），假设有这么一条语句被频繁执行：

-- 下班准备去幸福小区，爆破成绩不及格的学生
select name, age from student a
left join grade b on a.id = b.student_id 
where a.address like '%幸福小区%' and b.grade < 60

对于 student 表，可以建立索引的字段有 name、age、address、birthday，对于 grade 表，在 student_id 建立索引，可以大大提高连表查询的速度

4. 被用于 group by，order by 的字段

将 order by后面的字段建立索引，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在B+树中的记录都是排序好的

group by 和 order by 其实是类似，所以将这两个放在一起说了

因为在group by 的时候也要先根据 group by 后面的字段排序，然后在执行聚合操作

如果 group by 后面的字段没有排序，那么这个时候MySQL是需要先进行排序的，这样就会产生临时表，一个排好序的临时表，然后再在临时表中执行聚合操作，这样子当然效率很低了，如果 group by 后面的字段已经建立了索引，那么MySQL 就不需要再去排序，也就不会产生临时表

5. 频繁更新的字段创建索引要慎重

SQL优化中，合理的建立索引算是基本操作了。虽然索引能带来查询上的效率提升，但是索引的成本也是不小的，索引会占用磁盘空间，并且降低添加、删除、和更新的速度。如果一个字段不被经常查询，但是又经常被更新，那么便不应该在这个字段上加索引

创建索引的其他建议

1. 尽量创建联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升

2. 避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引

3. 考虑在字符串类型的字段上使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引

使用索引的注意事项

索引并不是说创建好之后就可不管，良好的索引需要开发人员的长期维护

首先索引对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引，对于小型表来说，查询全部数据的时间可能比遍历索引的时间还要短，不需要创建索引

其次需要删除长期未使用的索引也是有必要的，不使用的索引不仅占用着磁盘空间，而且会造成不必要的性能损耗，Mysql5.7 后可以通过查询 sys.schema_unused_indexes 表来获取长期未使用的索引

会使索引失效的几种情况

1. 在查询条件中对索引列使用函数或进行运算

如果是已经建立好的索引的字段在使用的时候执行了函数操作，那么这个索引就使用不到了

因为MySQL为该索引维护的B+树就是基于该字段原始数据的，如果正在使用过程中加了函数，MySQL就不会认为这个是原来的字段，那肯定不会走索引了

如果非要让函数走索引，可以在创建索引的时候把函数带上

例：

SELECT * FROM student WHERE round(age) = 2;

上面语句会使索引 idx_age 失效，如果非要让这条语句走索引，可以这样创建：

create index idx_age_round on student(round(age)); 

其次，在 where 中对索引列进行了运算，也会使索引失效

2. 在 like 条件中使用 % 开头

select * from student where name like '%bc%'

就上面的语句来说，索引是不生效的，想要索引生效，需要把前缀的 % 去掉

3. 使用了 or 条件

如果想要让 or 条件走索引，需要把 or 条件中的所有字段加上索引

4. 最左匹        配原则

最左匹配原则就是指在联合索引中，如果你的 sql 语句中用到了联合索引中的最左边的索引，那么这条 sql 语句就可以利用这个联合索引去进行匹配，当遇到范围查询(>、<、between、like)就会停止匹配