1、通过show status 命令来显示各种sql的执行效率
命令: show session 或者 global status (session表示当前的连接中的统计、global表示子数据库上次启动至今)
例如:
Com_xxx表示的是每个xxx语句执行的次数
- Com_select:表示的是执行select语句的次数
- …
- Innodb_rows_read:select查询返回的行数
- …
- Show_queries:慢查询的次数
2、定位执行效率偏低的语句
全局查询日志
SET GLOBAL general_log=1;#0为关闭 1为开启
SET GLOBAL log_output='Table';
执行:SELECT * FROM mysql.general_log; 将看到执行过的sql信息。
慢查询开启及分析
慢查询日志在查询结束后才记录,可以通过命令:
show processlist
来查看当前的MySQL在执行的线程。
3、通过explain分析低效的sql的执行计划
执行一个sql(sakila数据库):
- select_type: 表示select的类型;SIMPLE 代表简单表,不用表连接或子查询,PRIMRY 主查询(外层查询),UNION UNION中的第二个或者后面的查询语句,SUBQUERY 子查询中的第一个SELECT。
- table:输出结果集的表。
- possible_keys:查询中可能用到的索引。
- key:查询中实际用到的索引。
- key_len 索引的长度
- rows 扫描的行数
- type:访问类型 下面几种:
从左到右,性能由最差到最好
ALL index range ref eq_ref const,system NULL
ALL:全表扫描
index:索引全扫描
range: 索引的范围扫描 用于<,<=,>,>=,between等操作
ref:使用非唯一索引扫描或者唯一索引的前缀扫描
eq_ref:使用唯一索引扫描,多表链接中使用primary key或者unique key作为关联条件
const/system:单表中只有一个匹配行,查询速度快,根据主键或者唯一索引进行的查询
NULL:mysql不用访问表或者索引,直接能够得到结果
有时候通过explain分析执行计划不能很快的定位到sql的问题,这个时候需要选择profile联合分析。
4、通过show profile分析sql
默认profile是关闭的,可以通过set语句在session级别开启profile
select @@profiling;
set profiling =1 ;
通过show profile可以看到每个语句的id,在查看这个sql的执行过程中各个状态消耗的时间。
此外可以通过
SHOW PROFILE cpu,block io FOR QUERY Query_ID;或将 cpu,block io 改为 all
来查看各个查询的cpu 以及 io 的状态消耗时间。
若:Status中出现以下4个中一个必须优化
总结
- 慢查询开启、查看status
- explain+慢sql分析
- show profile查询sql执行细节和生命周期
- 后续调优