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
