Mysql分析sql语句

1.简述

　　在开发完成后，随着数据量的增加我们会遇到一些MySQL的性能问题。要想解决性能优化的问题，首先要想办法发现哪些SQL有性能问题。通过下面这几个手段可以比较准确的定位到有问题的SQL进行分析优化。

2.通过explain查询

　　大部分的性能分析都需要使用到该命令，可以用来查看SQL语句的执行效果，可以帮助选择更好地索引和优化语句。

　　语法如下：

explain + SQL语句

##示例
explain select * from tbl_userinfo where level=2 and (userName like '%aaaa%' or nickName like '%aaaa%')

　　执行语句后，可以查看参数说明：

• id：sql语句编号。
• select_type：查询类型，有以下几种类型
• SIMPLE：简单的select查询，不使用union及子查询。
• PRIMARY：最外层的select查询(使用到主键作为查询条件)。
• UNION：UNION中的第二个或随后的select查询，不依赖于外部查询的结果集。
• DEPENDENT UNION：UNION中的第二个或随后的select查询，依赖于外部查询的结果集。
• SUBQUERY：子查询中的第一个select查询,不依赖于外部查询的结果集。
• DEPENDENT SUBQUERY：子查询中的第一个select查询，依赖于外部查询的结果集。
• DERIVED：用于from子句里有子查询的情况，MySQL会递归执行这些子查询，把结果放在临时表里。
• UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估。
• UNCACHEABLE UNION：UNION中的第二个或随后的select查询,属于不可缓存的子查询。
• table：查询针对的表，该值可能是实际的表名或者临时表，derived表示form子查询，null表示无须查表。
• type：访问类型，决定如何查找表中的行，按最优到最差的类型排序
• system：表仅有一行(=系统表)。
• const：通过索引一次就找到，只匹配一行数据，用于常数值比较PRIMARY KEY或者UNIQUE索引。
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描。。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行，和eq_ref的区别是索引是非唯一索引。
• range：索引范围扫描，常用于<、<=、>、>=、between等操作。
• index：索引全扫描，MySQL遍历整个索引来查询匹配行，并不会扫描表。
• all：全表扫描，MySQL遍历全表来找到匹配行。
• possible_keys：查询时使用的索引。
• key：实际使用的索引，如果为NULL，则没有使用索引。
• key_len：索引中使用的字节数，查询中使用的索引的长度(最大可能长度) 并非实际使用长度，理论上长度越短越好。
• ref：显示索引的哪一列被使用。
• rows：估算出找到所需行而要读取的行数。
• Extra：额外信息，但又十分重要，有如下几种
• index：用到了索引覆盖，效率极高。
• using where：仅靠索引无法定位，使用了where。
• using temporary：用了临时表，group by与order by不同列。
• using filesort：文件排序，可能在内存中或磁盘中。

3.使用show profile查看SQL执行过程

//可以使用命令检查是否支持show profile
select @@have_profiling; //返回yes或者no
    
//profiling默认当前session是关闭的：0 关闭  1 开启
select @@profiling; //查询是否开启profiling 
    
//开启profiling
set profiling=1; //这样就算开启了
    
//执行一个查询语句
select count(*) from payment;
    
//执行一个查询之后执行命令
show profiles; //得到一个查询的历史记录
    
//根据历史记录的Query_ID  Duration(时间)  Query(查询的sql)
show profiles for query $Query_ID //获取整个sql语句的使用时间
    
//分析show profiles for query返回结果
//innodb大部分时间花费在Sending data的状态下
    
//设置一个变量
set @query_id=$Query_ID
    
//查看cpu消耗的指标 时间
show profile cpu for query=$Query_ID;
//可以查看指标有下面这些
ALL                 #显示所有的开销信息
BLOCK IO            #显示块IO的开销信息
CONTEXT SWITCHES    #上下文切换开销信息
CPU                 #显示CPU相关开销信息
IPC                 #显示发送和接受相关开销信息
MEMORY              #显示内存相关开销信息
PAGE FAULTS         #显示页面错误相关开销信息
SOURCE              #显示和source_funcation、source_file、source_line相关的开销信息
SWAPS               #显示交换次数相关开销信息

4.使用performance_schema查看SQL执行过程

　　在MySQL5.7中， show profile命令已经开始不推荐使用，MySQL使用performance_schema 中系统表的信息来替代show profile命令。

　　具体可以查看官方文档：https://dev.mysql.com/doc/refman/5.6/en/performance-schema-quick-start.html