6.MySQL优化(1)

本章要点

1.慢SQL
2.性能剖析工具

1. 慢SQL

SHOW GLOBAL  VARIABLES LIKE   '%SLOW_QUERY%';
---slow_query_log：慢SQL日志是否开启；- slow_query_log_file：目录地址
set global slow_query_log=on;---开启慢查询
SHOW VARIABLES LIKE 'long_query_time'; --- 耗时多久时间是慢查询，默认10s

2.性能剖析工具

2.1 pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog等

常用命令

• 分析慢查询日志中SQL的统计结果：pt-query-digest /var/lib/mysql/localhost-slow.log | more

• 分析select语句统计结果：pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/localhost-slow.log | more

• 查询指定用户(root)访问的查询：pt-query-digest --filter '($event->{user}) =~ m/^root/i' /var/lib/mysql/localhost-slow.log | more

• 分析所有全表扫描语句的统计结果：pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes")' /var/lib/mysql/localhost-slow.log | more

• 分析所有Full join语句的统计结果：pt-query-digest --filter '(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/localhost-slow.log | more

2.2 SHOW PROFILE

定位到需要优化的单条查询后，可以针对此查询进行进一步分析，获取更多信息，确认为什么会花费这么长时间，以及需要如何去优化。

SHOW  VARIABLES LIKE  '%profiling%'  -- 查看是否开启
SET profiling=ON  --开启profiling
SHOW PROFILES -- 查看开启工具后的每条SQL执行总体情况
SHOW PROFILE FOR QUERY 1;-- 根据query_id查看某个查询的详细时间耗费

2.3 执行计划——EXPLAIN

执行计划中的列

• 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)

连接类型(type)

• ALL:全表扫描,例如： explain select * from actor
• index:按索引顺序进行全表扫描，例如： explain select actor_id from actor
• range:按索引范围查找 ，例如：explain select * from actor where actor_id <10
• ref:是一种索引（普通索引）访问，返回所有匹配某个值的行 ，例如：explain select last_name from actor where last_name ='AKROYD'
• eq_ref:是一种索引访问，一般在关联查询时，例如：explain select * from actor where actor_id in (select actor_id from actor where actor_id <10)
• const\system:使用常量对主键索引或唯一索引进行精准匹配，例如：explain select * from actor where actor_id =10

MySQL解析额外信息(Extra)

• Using index:列数据仅仅使用了索引中的信息而没有读取实际的表
• Using where:MySQL服务器将在存储引擎检索行后，通过Where子句条件进行过滤
• Using temporary:MYSQL需要创建一个临时表来存储结果，用于排序
• Using filesort:MySQL将对结果进行外部排序