SQL 索引与执行计划

SQL 索引与执行计划

1. 索引的分类

​ 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。

​ 唯一索引：索引的值必须唯一，但允许有空值（null）。

​ 复合索引：一个索引包含多个列。

注：一张表建立的索引最好不要超过5个。

2. 索引基本语法

1. 创建索引 [UNIQUE] 可以省略

2. 如果只写一个字段就是单值索引，写多个字段就是复合索引
	create [UNIQUE] INDEX indexName ON 表名(字段名(length));
	
3. 删除索引
	DROP INDEX 索引名称 ON 表名;

4. 查看索引
	SHOW INDEX FROM 表名 \G; 加上 \G 就可以以列的形式查看，不加 \G 以表的形式查看。

注：
	查看执行计划：EXPLAIN * FROM 表名;

	查看索引：show index from 数据库名.表名 where column_name like '列名';

	建立普通索引：CREATE INDEX index_name(索引名称) ON 表名 (字段名) ;

	删除索引：drop index index_name(索引名) on 表名 ;
	

2.1 使用索引的注意事项

1. 要在经常搜索的列上加 索引，这样可以加快搜索的速度。
2. 在经常使用使用 where 字句的列上加索引，加快条件的判断速度。
3. 要在经常 排序 的列上加索引，因为索引已经是排好序的，这样查询可以利用索引的排序，加快查询的时间。
4. 对于 中到大型表 索引都是非常有效的，但是对于特大型表的话维护开销很大，不适合建立索引。
5. 索引加在经常用来连接的列上，这些列主要是一些 外键，可以加快连接的速度。
6. 避免where字句中对字段加 函数，这样会造成索引失效，导致无法命中索引。
7. 在使用 InnoDB 时使用与1业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
8. 将打算加索引的列设置为 NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描。
9. 删除长期未使用的索引，不用的索引继续存在的话会造成不必要的性能损耗，MySQL5.7可以通过查询 sys库 的 chema_unused_indexes 视图 来查询哪些索引从未被使用。

3. 查看执行计划

3.1 EXPLAIN

​ SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行 SQL 查询语句，从而知道MySQL是如何处理SQL语句的。

语法：explain + SQL
EXPLAIN SELECT
*
FROM
work_code_st wcs
WHERE
wcs.commit_added_lines > 0;

3.2 使用 EXPLAIN 能够查看的信息

​ 可以查看以下信息：

id：表的读取顺序，id 越大优先级越高，越先被执行。

select_type：数据读取操作的操作类型。

possible_keys：哪些索引可以使用。

key：哪些索引被实际使用。

ref：表之间的引用。

rows：每张表有多少行被优化器查询，根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

type：访问类型排列。从最好到最差依次是：
system > const > eq_ref > ref > range > index > ALL。
除了 ALL 没有用到索引，其他级别都用到索引了。一般来说，得保证查询至少达到 range 级别，最好达到 ref。

    system：
    表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
    
    const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where 列表中，MySQL 就能将该查询转化为一个常量。
    
    eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除 了 system 和 const 类型之外, 这是最好的联接类型。
    
    ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录；显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
    
    range：只检索给定范围的行，一般就是在 WHER E语句中出现了 BETWEEN、< >、in 等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。
    
    index：Full Index Scan，全索引扫描，index和ALL的区别为 index 类型只遍历索引树。
    也就是说虽然 ALL 和 index 都是读全表，但是 index 是从索引中读的，ALL 是从磁盘中读取的。
    
    ALL：Full Table Scan，没有用到索引，全表扫描。