性能优化的目标是降低响应时间,那么就需要理解为什么服务器执行查询需要这么多时间,然后减少或者消除那些对获得查询结果来说不必要的工作。
对MySQL查询进行性能剖析有两种方式(从整理到局部):
1、查看是否开启慢查询日志,1/on表示开启,0/off表示关闭。
SHOW VARIABLES like ‘slow_query_log‘;
———————————————————————————————————————————————————————
2、未使用索引的查询也被记录到慢查询日志中,on表示开启,off表示关闭
SHOW VARIABLES like ‘log_queries_not_using_indexes‘;
———————————————————————————————————————————————————————
3、慢查询阈值(秒级),当查询时间大于设定的阈值时,记录日志
SHOW VARIABLES like ‘long_query_time‘;
———————————————————————————————————————————————————————
4、慢查询日志存储路径
———————————————————————————————————————————————————————
SHOW VARIABLES like ‘slow_query_log_file‘;
关于慢查询日志的工具有官方和三方的,官方是mysqldumpslow,但并不是很好用,所以需要更精确的分析慢查询的话大多数使用pt-query-digest。
使用SHOW PROFILE命令,该命令是在MySQL5.1以后的版本中引入的,默认是禁用的,通过SET profiling = 1;命令开启。
1、查看开启工具后的每条SQL执行总体情况:SHOW PROFILES;
2、根据query_id查看某个查询的详细时间耗费:SHOW PROFILE FOR QUERY 1;
3、查看cpu、IO等信息:SHOW PROFILE BLOCK IO,CPU FOR QUERY 1;
4、查询哪些开销花费了多少时间 :SELECT state, SUM(duration) AS Total_R, ROUND(100 * SUM(duration) / (SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1), 2) AS Pct_R, COUNT(*) as Calls,SUM(duration) /COUNT(*) AS "R/Call" FROM information_schema.profiling WHERE query_id = 1 GROUP BY state ORDER BY total_r DESC;
table:对应的表。
type:连接类型(system、const、eq_ref、ref、range、index、all)。
possible_keys:可能使用的索引。
key:实际使用的索引。
key_len:使用索引长度。
rows:预计扫描行数。
Extra:解析查询的额外信息(using index、using where、using temporary、using filesort)。
原文:https://www.cnblogs.com/bzfsdr/p/12313983.html