SQL分析

从简到繁

 

通过show status了解SQL的执行频率：

SHOW STATUS LIKE 'Com_%';

 

定位执行效率较低的SQL：

这一部分看慢日志查询

 

EXPLAIN分析低效SQL的执行计划：

关注以下几个参数值

select_type：表示SELECT类型，SIMPLE（简单表，不实用表连接或子查询）、PRIMARY（主查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）

table：输出结果集的表

type：mysql在表中找到所需行的方式

type=ALL 全表扫描

type=index 索引全扫描

type=range 索引范围扫描

type=ref 使用非唯一索引扫描或唯一索引的前缀扫描

type=eq_ref 类似ref，区别在使用的索引是唯一索引

type=const/system 根据主键或者唯一索引进行的查询

type=NULL  不用访问表或者索引，直接获得结果。

possible_keys：查询时可能使用的索引

key：实际使用的索引

key_len：索引字段长度

rows：扫描行的数量

 

先执行EXPLAIN EXTENDED + SQL语句，

然后执行SHOW WARNINGS\G;  我们可以看到SQL真正被执行之前优化器做了哪些SQL改写。

 

 

SHOW PROFILE分析SQL

首先要开启profile，set profiling=1

mysql> show profiles;

可以看到执行过的SQL，找到要分析的Query_ID

mysql> show profile for query 4;   

4就是Query_ID

能够看到执行过程中线程的每个状态和消耗的时间

 

 

正餐来了！！！

通过trace分析优化器如何选择执行计划

trace由mysql5.6提供

检查information_schema库中的optimizer_trace表

mysql>SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;

慢慢分析吧。

一般执行计划的流程：

<1> 优化器拿到SQL之后，先对其进行扩展，就是改写SQL，使其更规范

<2> 先分析该SQL的条件，会找到“where”这种关键词，去掉一些不重要的条件（如恒等条件）

<3>然后是表依赖关系

<4>接着分析关键信息，得到搜索的表，条件字段，条件字段的值，等等

<5>计算不同查询方式所查询到的行数和时间消耗，中间会涉及索引分析，条件分析。

最后mysql会根据消耗做出选择

这一部分是关键，需要我们仔细去看mysql如何选择一套执行计划。

比如你明明使用了索引去查询，但是为什么在explain SQL 的时候，type的值没有使用索引。我们可以通过查看mysql的执行计划得到答案。

<6>mysql考虑执行计划，mysql选择了一套消耗低的执行计划

<7>mysql给表加上条件，然后再计算

<8>还有个改善计划

<9>最后执行计划