mysql中explain 和 profiling详解

explain sql语句：

模拟优化器执行sql语句

MySQL 的 EXPLAIN 是一个用于查询优化的工具，可以显示数据库如何执行查询。它返回一组关于查询执行计划的信息，包括用到的索引，表的连接顺序以及 MySQL 使用的查询类型。

下面是 EXPLAIN 返回的列及其含义：

id：操作顺序（值越大优先级越高，越先被执行）
　　id相同，执行顺序从上到下
　　id不同，从大到小执行
　　id为null最后执行
select_type：表示查询类型
　　simple ：简单查询，无子查询或union等
　　primary：查询中若包含复杂的子部分，最外层则被标记为primary（union左侧的，或者包含子查询的select）
　　union：union右侧的
　　union result：在union语句中，其id为null，不需要参与查询
　　subquery：在select或where中若包含子查询，则该子查询被标记为subquery；（非from后面的子查询）

table：表名

partitions：匹配的分区

type：访问（索引）类型
　　system：系统表仅一条
　　const：主要在主键索引
　　eq_ref：唯一性索引，或主键索引里，多表查询
　　ref：非唯一性索引，普通索引
　　range：索引范围扫描，使用索引查找一定范围内的行
　　index： 扫全表索引
　　all：全表扫描

总结：性能从左到右依次增强
　　       all 　 <　index       <　range     <　ref      <    eq_ref    <   const   <   system
数据全表扫描  < 扫全表索引 < 索引范围 < 普通索引< 唯一索引  < 主键索引 < 系统表仅一条

 

 

possible_keys：索引预估，不一定准

key：实际用到的索引，null表示没用使用索引

key_len：实际使用到的索引的长度

ref：表的引用

rows:：预估查询的数据量，越少越好

filtered：过滤器过滤的行数百分比。

extra：额外的信息，但却十分重要的信息，常见的有如下值：

• Using index：查询中使用了覆盖索引。
• Using where：使用了where子句来过滤数据。
• Using temporary：使用了临时表来存储结果集，常见于 GROUP BY 和 ORDER BY 查询，性能差。
• Using filesort：使用了文件排序
• Using join buffer：使用了 join buffer 来处理连接操作。
• Using sort_union：使用了 UNION 查询中的排序优化。
• Using intersect：使用了 UNION 查询中的交集优化。
• Using index condition：使用了索引中的条件进行过滤，这意味着 MySQL 能够在索引中解决查询条件，而无需查找表中的行。
• Using where with pushed condition：将过滤条件下推到了联接前的表中，这可能会更有效地利用索引。
• Using index for group-by：使用了索引来处理 GROUP BY 分组操作，这通常是一种优化。
• Using filesort for group-by：使用了文件排序来处理 GROUP BY 分组操作
• Using temporary for filesort：使用了临时表来处理 ORDER BY 排序或 GROUP BY 分组操作的结果。
• Using filesort for order by：使用了文件排序来处理 ORDER BY 排序操作

SQL 剖析：profiling

此工具可用来查询 SQL 会执行多少时间，System lock和Table lock 花多少时间等等，对定位一条语句的 I/O消耗和CPU消耗 非常重要。

查看profiling；
　　select @@profiling;
启动profiling:
　　set @@profiling=1 
关闭profiling ：
　　set @@profiling=0;

1.查看所有query_id的profile记录　
　　show profiles;
如下示例结果
　　Duration:耗时；
　　query：执行的sql语句；

2.查看某条查询的详细性能数据，根据query id=17（上图示例结果）查看指定SQL的语句分析
如下示例展示该sql完整生命周期：show profile for query 17;

3.查看 cpu, io, memory, block io 等性能数据，根据query id查看指定SQL的语句分析
　　show profile cpu,block io for query 17;
如下示例