Mysql之慢了怎么办？

Mysql

Mysql之慢了怎么办？

一、慢查询日志（记录是谁慢了）

1、查询慢日志开关

mysql> show variables like '%slow_query_log%';

slow_query_log ：慢日志状态关闭
slow_query_log_file：慢日志文件位置/var/lib/mysql/mysql8-slow.log

2、开启slow_query_log

临时方式

#开启慢sql日志记录
mysql > set global slow_query_log='ON';

#关闭慢sql日志记录
mysql >SET GLOBAL slow_query_log=OFF;

永久方式

在my.ini/my.cnf中修改

[mysqld]
slow_query_log=ON
#slow_query_log=OFF

3、修改long_query_time阈值

mysql > show variables like '%long_query_time%';

执行时间超过10s的sql才会记录到日志文件中

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置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%';

4、查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

二、慢查询日志分析工具（找出慢SQL）

1、mysqldumpslow命令

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间（默认方式）
- ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

示例

常用参考

在命令行中执行，xxx为slow_query_log_file的位置，可通过上面的show variables like '%slow_query_log%';在mysql客户端中查询

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

2、`SHOW PROFILE`查看SQL执行成本

查看配置开关

mysql > show variables like 'profiling';

打开配置开关

mysql > set profiling = 'ON';

查询当前会话有哪些 profiles

整体的sql执行情况

mysql > show profiles;

要查看查询的开销

#查看最近一次的
mysql > show profile;

#查看制定Query_ID的
mysql > show profile for query 2;

#制定展示的其他参数
mysql> show profile cpu,block io for query 2;

show profile的常用查询参数：

ALL：显示所有的开销信息。
BLOCK IO：显示块IO开销。
CONTEXT SWITCHES：上下文切换开销。
CPU：显示CPU开销信息。
IPC：显示发送和接收开销信息。
MEMORY：显示内存开销信息。
PAGE FAULTS：显示页面错误开销信息。
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS：显示交换次数开销信息。

三、EXPLAIN分析查询语句（sql为啥慢了）

1、基本语法

mysql > EXPLAIN SELECT select_options
或者
mysql > DESCRIBE SELECT select_options

2、基本组成

type字段特殊说明

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

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

ken_len特殊说明

代表的是具体使用索引字段的长度，在某些情况优化器会自动使用某些组合索引，但是可能使用组合索引的部分字段，就需要通过该字段计算出具体使用的索引列

int：4字节，为null的话5个字节，因为null也需要1个字节存储

varchar：指定的长度+是否为null（1个字节）+可变类型（2个字节），例如utf-8编码下，varchar(100）not null，则长度为100*3(utf-8字符集下一个字符三个字节)+0(不为null，不用存储)+2（记录可变类型）=102

3、EXPLAIN四种输出格式

EXPLAIN可以输出四种格式： 传统格式 ， JSON格式 ， TREE格式 以及 可视化输出 。

语法

# 传统格式
EXPLAIN  SELECT ....

# JSON格式
EXPLAIN FORMAT=JSON SELECT ....

# TREE格式
EXPLAIN FORMAT=tree SELECT ....

可以通过MySQL Workbench可视化查看MySQL的执行计划

4、SHOW WARNINGS的使用

在explain之后使用使用show warninings可以查看优化器优化之后的sql结果

四、trace分析优化器执行计划（整个sql的执行过程是怎么样的）

1、开启trace分析优化器

#开启优化器堆栈记录，并且指定记录格式为json
SET optimizer_trace="enabled=on",end_markers_in_json=on;
#修改优化器堆栈内存，避免内存移除
set optimizer_trace_max_mem_size=1000000;

2、测试

select * from student where id < 10;

select * from information_schema.optimizer_trace\G；

五、MySQL监控分析视图

1、索引情况

#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' ;

2、表相关

# 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';

3、语句相关

#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;

4、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;

5、Innodb 相关

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

六、优化MySQL的参数