【MySQL高级 MySQL性能分析工具的使用】

---

MySQL性能分析工具的使用

1.统计SQL的查询成本：last_query_cost

SHOW STATUS LIKE 'last_query_cost';

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

1. 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2. 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

2.定位执行慢的SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time的值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

2.1开启慢查询日志参数

1. 开启slow_query_log

set global slow_query_log='ON';

查看下慢查询日志是否开启，以及慢查询日志文件的位置：

show variables like `%slow_query_log%`;

2. 修改long_query_time阈值

show variables like '%long_query_time%';

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 
mysql > set global long_query_time = 1; 
mysql> show global variables like '%long_query_time%'; 

mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

这些都只是临时修改，后面重启这个服务器又变成了配置文件中的配置。
永久修改需要修改配置文件my.cnf

2.2查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

2.2.1 慢查询的条件有两个

• 时间限制：long_query_time>
• 记录限制: min_examined_row_limit

2.3慢查询日志分析工具：mysqldumpslow

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 
#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

2.4关闭慢查询日志

方式1：永久性方式，再重启mysql服务器

[mysqld] 
slow_query_log=OFF
#或注释掉这一行
[mysqld] 
#slow_query_log =OFF

#重启mysql服务
systemctl restart mysqld

方式2：临时性方式，再重启mysql服务器

SET GLOBAL slow_query_log=off;

#重启mysql服务
systemctl restart mysqld

2.5 删除慢查询日志

rm hostname-slow.log

​ 重新恢复慢查询日志 flush-logs 不仅可以重置慢查询日志，其他日志也可以重置

mysqladmin -uroot -p flush-logs slow

3.查看SQL执行成本：SHOW PROFILE

show variables like 'profiling';
#开启
set profiling = 'ON';
#查看
show profiles;
show profile cpu,block io for query 2; #for是指定查看的语句，cpu,block代表查询的模块。

3.1 四种情况代表sql需要优化：

1. converting HEAP to MyISAM 查询结果太大，内存都不够用了，往磁盘上搬了
2. Creating tmp table 创建临时表
   1.拷贝数据到临时表
   2.用完再删除
3. Copying to tmp table on disk 把内存中临时表复制到磁盘，危险操作
4. locked 加锁

4.分析查询语句：EXPLAIN

4.1基本语法

EXPLAIN SELECT select_options 
#或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

4.2 EXPLAIN各列作用

1. table

MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

2. id

​ 一个select代表一个id,但是查询优化器可能对语句进行优化，所以可能两个select只有一个id

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• 关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

3. select_type

1. SIMPLE 简单地select查询，查询中不包含子查询或UNION
2. PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
3. DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生)。MySQL会递归执行这些子查询，把结果放在临时表中 【派生表】
4. SUBQUERY 在SELECT或WHERE列表中包含了子查询【不相关子查询】
5. DEPEDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层 【相关子查询】
6. UNCACHEABLE SUBQUERY 无法使用缓存的子查询
7. UNION 若第二个SELECT出现在UNION之后，则被标记为UNION； 【union all 不去重 无临时表】
   若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
8. UNION RESULT 从UNION表获取结果的SELECT 【去重】
9. DEPEDENT UNION 相关的子查询 带UNION的
10. MATERIALIZED 物化表 select *from table where key in select key from table2 ,第二个select就是物化表

4. partitions

5. type（重点）

(1)system

表只有一行记录（等于系统表），且表的引擎是MyISAM,Memory 等精确统计数据的

(2)const

表示通过索引一次就找到了，const用于比较primary key或者unique索引。

如将主键置于where列表中，与常量做等值匹配

(3)eq_ref

连接查询，where中包含主键或者唯一索引，相匹配的情况。内层的表就是

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gHLunKtT-1671343872342)(C:\Users\hufei\AppData\Roaming\Typora\typora-user-images\image-20221217224318932.png)]

(4)ref

非唯一性索引扫描，返回匹配某个单独值的所有行。

本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

注意：若与常量等值匹配需要保证其类型一致，varchar类型的字段等于整型的数，就是all

(5)intersection/union/sort-union

并集查询： select * from table where k1=1 or k2=3 ;

(6)range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

(7)index

全局搜索索引节点，和all不同的点在于只是搜索索引，没有查询数据。而且这里只是搜索索引却并没有使用索引进行过滤。【不用回表】

（8）all

小结：

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是consts级别。（阿里巴巴开发手册要求）

6. possible_keys和key

possible_key:可能用到的索引

key: 实际用到的索引

**7. key_len（重点）**索引的字节数

key_len的长度计算公式：

varchar(10)变长字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) 

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) 

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

8. ref ：比较的是常量还是对象信息

9. rows（重点）

预估的需要读取的记录条数

越小越好

10. filtered ，越大越好 实际的记录/预估读的记录的百分比

11. Extra、

（1）Using filesort

说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为文件排序。

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

（2）Using temporary

使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。

常见于排序order by 和分组查询group by

5. EXPLAIN的进一步使用

5.1 EXPLAIN四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。

1. 传统格式

2. JSON格式

JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON。用于查看执行成本cost_info

3. TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

4. 可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。

5.2 SHOW WARNINGS的使用

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
# 查看优化后的执行语句
mysql> SHOW WARNINGS\G

6.分析优化器执行计划：trace

# 开启
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
# 设置大小
set optimizer_trace_max_mem_size=1000000;
# 使用
select * from student where id < 10;
select * from information_schema.optimizer_trace\G

7. MySQL监控分析视图-sys schema

7.1 Sys schema视图使用场景

索引情况

#1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

小结：不必要的话，不需要使用sys去查询，因为会消耗大部分的资源去查询相关的信息