MySQL之EXPLAIN（索引优化）

小灰灰 2023-07-04 11:06 15阅读 0赞

## EXPLAIN关键字 ##

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

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

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

例：![在这里插入图片描述][20200210120515449.png]  
![在这里插入图片描述][20200210120621503.png]  
对于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自己会选择最优的索引

[20200210120515449.png]: https://img-blog.csdnimg.cn/20200210120515449.png
[20200210120621503.png]: https://img-blog.csdnimg.cn/20200210120621503.png