MySQL调优学习笔记(二、MySQL调优基础)
目录:
- 如何优化性能
- MySQL慢查询日志
- 如何分析一条SQL
- EXPLAIN执行计划
如何优化性能
性能优化的目标是降低响应时间,那么就需要理解为什么服务器执行查询需要这么多时间,然后减少或者消除那些对获得查询结果来说不必要的工作。
对MySQL查询进行性能剖析有两种方式(从整理到局部):
- 剖析整个数据库服务器,这样可以分析出哪些查询是主要的压力来源。
- 定位具体需要优化的查询后,可以对这些查询进行单独的剖析,分析哪些子任务是影响时间的主要消耗者。
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。
如何分析一条SQL
使用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;
EXPLAIN执行计划
table:对应的表。
type:连接类型(system、const、eq_ref、ref、range、index、all)。
- ALL:全表扫描
- index:按索引顺序进行全表扫描
- range:按索引范围查找
- ref:是一种索引访问,返回所有匹配某个值的行
- eq_ref:是一种索引访问,MySQL知道最多只返回一条符合条件的记录
- const\system:使用常量对主键索引或唯一索引扫描
possible_keys:可能使用的索引。
key:实际使用的索引。
key_len:使用索引长度。
rows:预计扫描行数。
Extra:解析查询的额外信息(using index、using where、using temporary、using filesort)。
- Using index:列数据仅仅使用了索引中的信息而没有读取实际的表
- Using where:MySQL服务器将在存储引擎检索行后,通过Where子句条件进行过滤
- Using temporary:MYSQL需要创建一个临时表来存储结果,用于排序
- Using filesort:MySQL将对结果进行外部排序