这是我参与「第五届青训营」伴学笔记创作活动的第 8 天

1 前言

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

注：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。

2 索引创建

2.1 普通索引

这是最基本的索引，它没有任何限制。

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

2.2 唯一索引

与普通索引不同，唯一索引的列值必须唯一，允许为null。

ALTER TABLE `table_name` ADD UNIQUE index_name (`column`);

2.3 主键索引

主键索引是一种特殊的唯一索引，并且一张表只有一个主键，不允许为null。

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

2.4 联合索引

联合索引是同时在多个字段上创建索引，查询效率更高。

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

2.5 全文索引

全文索引主要用来匹配字符串文本中关键字。

当需要字符串中是否包含关键字的时候，我们一般用like，如果是以%开头的时候，则无法用到索引，这时候就可以使用全文索引了。

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

3 适合建立索引的字段

频繁查询的字段适合创建索引
在where和on条件出现的字段优先创建索引
区分度高的字段适合创建索引：比如对于一张用户表来说，生日比性别的区分度更高，更适合创建索引。
有序的字段适合创建索引：修改索引的时候不会出现"页裂"问题

4 不适合建立索引的字段

频繁更新的字段不适合创建索引
过长的字段不适合创建索引
无序的字段不适合创建索引

5 创建索引的其他注意事项

优先使用联合索引

查询的时候，联合索引比普通索引能更精准的匹配到所需数据。

过长字符串可以使用前缀索引

比如在匹配用户地址的时候，如果乡镇已经能区分大部分用户了，就没必要精确到街道小区了。

创建普通索引的时候，指定索引长度，就可以创建前缀索引了。

ALTER TABLE `user` ADD INDEX idx_address (address(3));

排序和分组字段也尽量创建索引

在order by和group by中的字段也尽量创建索引，避免使用文件排序，可以使用索引排序提供性能。

避免创建过多索引

6 一些命令

// 查询一个表的所有索引
show index from user

6.1 explain

使用mysql提供的explain命令来查询sql语句的执行计划:

`id`：标识符

select识别符，这是select的查询序列号。
SQL执行的顺序的标识，SQL从大到小的执行。
1，id相同时，执行顺序由上至下。
2，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
3，id如果相同，可以任务是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

`select_type`：表示查询的类型

simple （它表示简单的select，没有union和子查询）
primary （最外面的select，在有子查询的语句中，最外面的select查询就是primary）
union （union语句的第二个或者是后面的select语句）
dependent union （union中的第二个或者后面的select语句，取决于外面的查询）
union result （union的结果，union语句中第二个select开始后面所有的select）

table：输出结果集的表
partitions：匹配的分区
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较
rows：扫描出的行数（估算的行数）
filtered：按表条件过滤的行百分比
Extra：执行情况的描述和说明

using filesort：当extra中出现using filesort时说明语句性能不好，需要优化。using filesort是一种速度很慢的外部排序。
using index（using index condition）：只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。
using temporary：为了解决查询，mysql需要创建一个临时表来容纳结果，典型情况如查询包含客户以按不同情况列出列的group by和order by子句时，出现using temporary就说明语句需要优化。

MySQL索引简历规范 ｜ 青训营笔记