前言

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。

由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘I/O操作。 如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此掌握良好的索引创建原则非常重要！

索引的分类

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度！

物理上(聚集索引&非聚集索引)
逻辑上：

1、普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引(创建新表&已创建表，数量是不限的，一张表的一次sql查询只能用一个索引 where a=1 and b=‘M’)
2、唯一性索引：使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引
3、主键索引：使用Primary Key修饰的字段会自动创建索引(MyISAM, InnoDB)
4、单列索引：在一个字段上创建索引
5、多列索引：在表的多个字段上创建索引 (uid+cid，多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上)
6、全文索引：使用FULLTEXT参数可以设置全文索引，只支持CHAR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度(线上项目支持专门的搜索功能，给后台服务
器增加专门的搜索引擎支持快速高校的搜索 elasticsearch 简称es C++开源的搜索引擎 搜狗的workflow)

索引的优点： 提高查询效率
索引的缺点： 索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O造成CPU负荷太重。

索引的创建和删除

创建表的时候指定索引字段：

示例：这个索引是多列索引

CREATE TABLE index1(id INT,
name VARCHAR(20),
sex ENUM(‘male’, ‘female’),
INDEX(id,name));

在已经创建的表上添加索引：

CREATE [UNIQUE] INDEX 索引名 ON 表名（属性名（length） [ASC | DESC]);

删除索引:

DROP INDEX 索引名 ON 表名;

示例

使用索引查询：

在student表中，uid是索引，那么在执行计划可以看出，整个表只扫描了一条数据（rows== 1)

使用普通字段查询：
在使用name作为where的过滤字段时，由于不是索引，那么需要扫描整个表(rows == 6)

给name字段建立索引：

再次用同样方法查询，发现使用了nameidx索引，就只扫描了一行数据。

使用惯例

1.经常作为where条件过滤的字段考虑添加索引
2.字符串列创建索引时，尽量规定索引的长度，而不能让索引值的长度key_len过长
3.索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了

索引的执行过程

使用explain查看sql的执行计划，分析索引的执行过程，mysql的user权限表示例如下：

可以看到使用了主键索引，共扫描1行，Using index表示直接从索引树上查询到结果，不需要回表。

explain结果字段分析

select_type
simple：表示不需要union操作或者不包含子查询的简单select语句。有连接查询时，外层的查询为simple且只有一个。
primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary且只有一个。
union：union连接的两个select查询，除了第一个表外，第二个以后的表的select_type都是union。
union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null。

table
显示查询的表名；
如果不涉及对数据库操作，这里显示null；
如果显示为尖括号就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生的；
如果是尖括号括起来也是一个临时表，表示这个结果来自于union查询的id为M，N的结果集；

type
const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type就是const。
ref：常见于辅助索引的等值查找，或者多列主键、唯一索引中，使用第一个列之外的列作为等值
查找会出现；返回数据不唯一的等值查找也会出现。
range：索引范围扫描，常见于使用<、>、is null、between、in、like等运算符的查询中。
index：索引全表扫描，把索引从头到尾扫一遍；常见于使用索引列就可以处理不需要读取数据文
件的查询，可以使用索引排序或者分组的查询。
all：全表扫描数据文件，然后在server层进行过滤返回符合要求的记录。

ref
如果使用常数等值查询，这里显示const；
如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；

Extra
using filesort：排序时无法用到索引，常见于order by和group by语句中。
using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。