Mysql分析sql语句
1.简述
在开发完成后,随着数据量的增加我们会遇到一些MySQL的性能问题。要想解决性能优化的问题,首先要想办法发现哪些SQL有性能问题。通过下面这几个手段可以比较准确的定位到有问题的SQL进行分析优化。
2.通过explain查询
大部分的性能分析都需要使用到该命令,可以用来查看SQL语句的执行效果,可以帮助选择更好地索引和优化语句。
语法如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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执行过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
//可以使用命令检查是否支持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