MySQL之EXPLAIN（索引优化）

EXPLAIN关键字

①作用：模拟优化器执行SQL查询语句，分析查询语句或表结构的性能瓶颈
可以看出：表读取顺序、可使用索引、数据读取操作操作类型、实际使用的索引
表之间的引用、每张表的物理查询行数
使用方法：EXPLAIN + SQL查询语句

②关键字段（重点关注字段用*标注）

字段	含义
*id	表名表的读取顺序，相同（执行顺序从上至下），不同（从大到小） id每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好
select_type	查询类型，SIMPLE、PRIMARY（最外层查询）、DERIVED（衍生，临时表子查询） SUBQUERY（SELECT、WHERE子查询、=）、DEPENDENT SUBQUERY（依赖、IN关键字）、UNCACHEABLE SUBQUREY（不可用缓存的子查询，SQL出现系统变量）、UNION（出现在UNION的SELECT）、UNION RESULT（UNION连接查询后的结果表）
table	表名
partitions	分区表的命中情况，非分区表为null
*type	显示查询时使用何种类型 system>const>eq_ref>ref>range>index>ALL 、ALL(全局覆盖）、index（筛选条件未用到索引，使用到了覆盖索引或利用索引进行了排序分组）、 range（范围查询）、index_merge（使用合并索引，OR关键字）、ref_or_null(用于某个字段既需要关联条件，也需要null值）、index_subquery(子查询使用索引）、unique_subquery(唯一子查询） PS：起码优化到range级别
possible_keys	可能使用的索引
*key	实际使用的索引
*key_len	WHERE后筛选条件命中索引的长度（对复合索引，越长越好）（计算长度时：字段允许为空时字节长度加一，varchar动态字节串要加两个字节）
ref	使用索引的字段
*rows	物理扫描的行数（大致行数，有偏差）
filtered	经过server层过滤，剩余满足查询的记录数量的比例
*Extra	Using filesort（ORDER BY未用索引）、Using temporary（GROUP BY（包含一个ORDER BY）未用索引）、sing join buffer （关联字段未用索引）、impossible where（逻辑出错）Using index（使用了索引）、Using where （where 使用了索引） select tables optimized away（使用了优化器）

例：

对于key_len的计算（gbk编码，char为2）：（8*2+1）+（1+1）= 19

索引优化

在进行索引优化之前，需取出表中多余index（非主键），进行删除，再添加合适的索引，由于用户并不能直接操作information_schema.STATISTICS表中数据，所以我们需将该表中除主键索引之外的其余索引名提取出来（对于复合索引，只提取一个名称，使用SEQ_IN_INDEX进行过滤），在相应数据库中进行索引删除。
①提取相关索引
SELECT index_name
FROM information.STATISTICS
WHERE table_name = 表名 AND table_schema = 库名

②可将结果存为游标
DECLARE 游标名 CURSOR FOR SELECT语句 #定义游标
OPEN 游标名
FETCH …（游标名） INTO … #遍历游标取出数据
CLOSE 游标名
PS：一个BGEIN END只能声明一个游标，打开的游标需进行关闭

③预编译（可将字符串预编译为sql语句）
PREPARE 语句名（不需要定义） FROM 字符串变量名
EXECUTE 语句名
每次执行完，需执行DEALLOCATE PREPARE 语句名来释放使用的所有数据库资源
例：删除指定库，指定表的其余索引

CREATE PROCEDURE delIndex(IN dbname varchar(20), IN tabname varchar(20))
BEGIN
	 DECLARE  idxName varchar (20) DEFAULT "";
#提取索引，并用游标存储
	 DECLARE  indCursor  CURSOR
	 FOR SELECT INDEX_NAME
         FROM information_schema.STATISTICS
	 WHERE TABLE_SCHEMA = dbname
	 AND TABLE_NAME = tabname
	 AND INDEX_NAME <> "PRIMARY"
	 AND SEQ_IN_INDEX = 1;
#遍历游标，取出数据，并使用CONCAT函数拼接成sql语句,并进行预编译
	 OPEN indCursor;
	 FETCH indCursor INTO idxName;
		 WHILE idxName <> "" DO
		  SET @deIndex = CONCAT("DROP INDEX ", idxName, " ON ", tabname);
		  PREPARE  my_sql FROM @deIndex;
		  EXECUTE my_sql;
 		 DEALLOCATE PREPARE my_sql;
 		 SET idxName = "";
		  FETCH indCursor INTO idxName;
	 END WHILE;
 CLOSE indCursor;
END$

使用EXPLAIN进行分析

EXPLAIN SELECT SQL_NO_CACHE (标识不走缓存进行分析）…
单表优化：
①出现多个条件字段，使用复合索引，建立时满足最佳左前缀法则（过滤性好字段放前）
②给筛选字段加计算、函数、类型转换会导致索引失效
③范围查询（不包含LIKE）右边字段索引失效，需改变建立复合索引的顺序（范围查询字段放最后）
④不等于<>、 IS NOT NULL、LIKE ‘%fff’（首字母不确定）、类型不匹配索引失效

多表关联优化：
①有一个表（驱动表）是全表扫描（无法避免），另一个表（被驱动表）可以建索引优化
PS：LEFT JOIN 左表为驱动表， INNER JOIN 、MySQL自己选择驱动表
TIPS：当使用INNER JOIN并不能得到想要的查询方式，可使用STRAIGHT_JOIN（左驱，作用同内联）来指定驱动表,明确前后两表数量级确定时使用
②虚拟表无法建立索引，需放在驱动表位置
③尽量不使用子查询（会增加查询的趟数）

子查询优化：
对于NOT IN或NOT EXISETS 可以使用连接且加筛选条件（字段为空）进行替换

其它优化：
ORDER BY（排除using filesort）：
①无过滤不索引：OEDER BY使用索引时必须加过滤条件（WHERE、LIMIT），且排序字段都得添加索引（复合索引）
②顺序错，必排序：由于ORDER BY后字段顺序影响查询结果，因此Optimizer（优化器）并不会调整字段顺寻，所以复合索引的字段顺序有要求
③方向反，必排序：ORDER BY字段排序不一样（包含升、降序）、索引无效
对于无法避免（filesort）的情况，有两种算法：双路排序（两次扫描磁盘），单路排序（在内存中进行排序）
GROUP BY：
没有用到过滤也能使用：索引，其它同ORDER BY

覆盖索引（SELEC和FROM之间查询的列 <=使用的索引列+主键）：
不要使用*，使用具体的字段，可以用到一些索引
PS：MySQL自己会选择最优的索引