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%')
View Code
执行语句后,可以查看参数说明:
- 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 #显示交换次数相关开销信息
View Code
4.使用performance_schema查看SQL执行过程
在MySQL5.7中, show profile命令已经开始不推荐使用,MySQL使用performance_schema 中系统表的信息来替代show profile命令。