mysql连接工具错误100061_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将对结果进行外部排序