Mysql 索引使用及Explain使用

添加索引

-- 添加PRIMARY KEY（主键索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column1`) ;
-- 添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE (`column1`);
-- 添加INDEX(普通索引) 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`);
-- 添加FULLTEXT(全文索引) 
ALTER TABLE `table_name` ADD FULLTEXT (`column1`);
-- 添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

删除索引

ALTER TABLE `table_name` DROP index_name ;

ALTER TABLE `table_name` drop primary key ;

使用索引注意事项

1.索引不会包含有NULL的列

   只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

2.使用短索引

   对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

   mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。

4.like语句操作

  一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

5.不要在列上进行运算

6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

7.索引要建立在经常进行select操作的字段上。

   这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

8.索引要建立在值比较唯一的字段上。

9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

10.在where和join中出现的列需要建立索引。

11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

Explain 使用

使用方式

1.EXPLAIN SELECT ……
变体：
2.EXPLAIN EXTENDED SELECT ……
将执行计划"反编译"成SELECT语句，运行SHOW WARNINGS
可得到被MySQL优化器优化后的查询语句
3.EXPLAIN PARTITIONS SELECT ……
用于分区表的EXPLAIN生成QEP的信息

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

explain select t3.age, t2.id from (select age,name from t1 where id in (1,2))t3, t2 where t3.age=t2.age group by t3.age, t2.id order by t2.id;
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref    | rows | Extra                           |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
|  1 | PRIMARY     | t3         | ALL   | NULL          | NULL    | NULL    | NULL   |    8 | Using temporary; Using filesort |
|  1 | PRIMARY     | t2         | ref   | age           | age     | 5       | d1.age |    2 | Using where; Using index        |
|  2 | DERIVED     | t1         | range | PRIMARY       | PRIMARY | 4       | NULL   |    8 | Using where                     |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+		

id      select_type  table    partitions  type    possible_keys                                       key                                                 key_len  ref                                                          rows  filtered  Extra   
------  -----------  -------  ----------  ------  --------------------------------------------------  --------------------------------------------------  -------  ---------------------------------------------------------  ------  --------  --------
     1  SIMPLE       table_1  (NULL)      index   PRIMARY,UK_123ap500f6gas6d2sjnjds678                UK_123ap500f6gas6d2sjnjds678                        258      (NULL)                                                          8    100.00  (NULL)  
     1  SIMPLE       table_2  (NULL)      eq_ref  UK_123yth3fv4heue4evp6hhn678                        UK_123yth3fv4heue4evp6hhn678                        8        datasource1.table_1.id                                          1    100.00  (NULL)  
     1  SIMPLE       table_3  (NULL)      eq_ref  UK_123u950nr5uquwlvymnl4l678                        UK_123u950nr5uquwlvymnl4l678                        8        datasource1.table_1.id                                          1    100.00  (NULL)  
     1  SIMPLE       table_4  (NULL)      eq_ref  UK_123hgiqupi9m5ip2m3hev2678                        UK_123hgiqupi9m5ip2m3hev2678                        8        datasource1.table_1.id                                          1    100.00  (NULL)  
     1  SIMPLE       table_5  (NULL)      ref     UK_123hgiqupi9m5ip2m3hev678u                        UK_123hgiqupi9m5ip2m3hev678u                        258      datasource1.table_1.re_id                                       1    100.00  (NULL)  

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

id

包含一组数字，表示查询中执行select子句或操作表的顺序:
id相同，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

a. SIMPLE：查询中不包含子查询或者UNION
b. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
e. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f. 从UNION表获取结果的SELECT被标记为：UNION RESULT

SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
DEPENDENT意味着select依赖于外层查询中发现的数据。
UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

type	description
all	Full Table Scan， MySQL将遍历全表以找到匹配的行
index	Full Index Scan，index与ALL区别为index类型只遍历索引树
range	只检索给定范围的行，使用一个索引来选择行
ref	表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref	类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const	基于主键或唯一索引查询，最多返回一条结果
system	查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表，这是最好的情况
null	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
ref_or_null	表连接类型是ref，但进行扫描的索引列中可能包含NULL值
fulltext	全文检索
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引

ALL：

Full Table Scan，表示将遍历全表以找到匹配的行

index:

Full Index Scan，index与ALL区别为index类型只遍历索引树

range:

索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

ref:

使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行

eq_ref:

类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system:

当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL:

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值， 即列与索引的比较

rows

根据表统计信息及索引选用情况估算出结果集行数，估算的找到所满足条件的记录所需要读取表中数据的行数

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
 

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL；
key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中；
如果要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX；

key_len

表示索引中使用的字节数，不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的， 可通过该列计算查询中使用的索引的长度

Extra

包含MySQL解决查询的详细信息:

Extra	Description	Example
Using filesort	当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”	explain select * from t1 order by createDate;
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by;	explain select * from t1 order by createDate;
Using index	使用覆盖索引	explain select id from t1 where id=‘1’;
Using where	不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤	explain select * from t1 where createDate =‘2021-01-01 00:00:00’;
Impossible WHERE	这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）	explain select * from t1 where 1<0;
Using jion buffer (Block Nested Loop)	改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。	explain select * from t1 straight join t2 on t1.createDate =t2.createDate;
Using index condition	先条件过滤索引,在查数据	explain select * from t1 where a>100 and a like ‘%1’;
select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行	explain select max(a) from t1;
No tables used	Query语句中使用from dual 或不含任何from子句	explain select now() from dual;

Using index：

该值表示相应的select操作中使用了覆盖索引（Covering Index）
覆盖索引（Covering Index）
MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件
包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）
注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

Using where:

表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：

表示需要使用临时表来存储结果集，常见于分组，排序查询
这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：
1)内部临时表占用的空间超过min(tmp_table_size，max_heap_table_size)系统变量的限制
2)使用了TEXT/BLOB 列

Using filesort：

MySQL中无法利用索引完成的排序操作称为“文件排序”，当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：

改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：

这个值强调了where语句会导致没有符合条件的行即通过收集统计信息不可能存在结果

Select tables optimized away：

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行.

Index merges

当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。
Using sort_union(…)
Using union(…)
Using intersect(…)

No tables used：

Query语句中使用from dual 或不含任何from子句

EXPALIN总结

• 只能解析SELECT操作；
• 无法查看关于存储过程信息、触发器的信息以及用户自定义函数对查询结果的影响；
• 没有考虑各种Cache的情况
• 不显示在执行查询时存储引擎所作的优化工作
• 部分统计信息并非精确值，只是估算值

参考

https://www.cnblogs.com/gomysql/p/3720123.html
https://blog.youkuaiyun.com/weixin_38171468/article/details/105701811