MySQL知识体系——日志支持

MySQL知识体系 —— 日志支持

一、SQL 优化简介

1、解释：对于特定的要求，使用更优的 SQL 策略 或索引策略，以达到让结果呈现的时间更短，从而提升操作效率的过程就是 SQL 优化；
2、SQL 优化包含在数据库级别优化中。我们平常所说的 SQL 优化就是指优化 SQL 语句和索引；
3、SQL 优化是伴随着业务而进行优化的，并不是下面的所有操作必须都达到才是最好的优化。

二、常规调优思路（众多解决方案之一）

1、查看 slow-log，分析出查询慢的语句；
2、按照一定优先级，一个一个的排查所有慢语句；
3、分析 top sql，进行 explain 调试，查看语句执行时间；
4、调整索引或语句本身。

三、MySQL 日志支持

1. MySQL 5.7 中日志分类

MySQL 中日志分为四类：错误日志（回滚等）、二进制日志（主从）、通用查询日志（记录查询等信息）、慢查询日志

2. 通用查询日志

1、通用查询日志是记录建立的客户端连接和执行的语句
2、通过 show variables like '%version%' 查看版本信息

3、通过 show variables like '%general%' 查看通用查询日志是否开启
临时开启 / 关闭通用日志（重启失效）

# 开启
set global general_log = on;
# 关闭
set global general_log = off; 

4、通过 show variables like '%log_output%' 查看日志输出格式

输出格式：FILE、TABLE

• FILE 存储在数据库的数据文件中：E:\Java\MySQL\data\LAPTOP-ZhangZiang.log
• TABLE 存储在数据库的表中：mysql/general_log

临时设置输出格式（mysql重启失效）

# FILE
set global log_output = 'file';
# TABLE
set global log_output = 'table';
# 两者都输出
set global log_output = 'file,table';

3. 错误日志

1、MySQL 错误日志记录 MySQL 运行过程中较为严重的警告和错误信息，以及 MySQL 启动和关闭的详细信息。
2、通过 show variables like '%log_error%' 查看错误日志配置信息

• binlog_error_action 错误处理方式（ABORT_SERVER 出现问题终止服务、IGNORE_ERROR 忽略错误）
• log_error 错误日志文件名及路径
• log_error_verbosity 记录级别（取值 1 表示记录警告信息、大于 1 表示所有警告信息都记录）

4. 二进制日志

1、包含所有更新数据（新增、删除、修改、改表、建库等）SQL信息的记录
2、MySQL 主从配置就依赖这个日志文件
3、通过 show variables like '%log_bin%' 查看二进制文件

4、二进制文件不可以通过修改全局参数开启。全局配置文件（my.ini）
5、开启后，在D:\mysql\mysql-8.0.26-winx64\data\目录下，有一个 binlog.index 文件，其中存储了所有二进制文件清单。当重启 MySQL 服务或过一定时间后会自动增加一个二进制文件。增加的二进制文件编号递增，也可以使用 flush logs 命令生成一个新的二进制文件
6、通过命令 show binary logs 查看目录生成的日志文件

7、由于是二进制文件，所以无法直接使用文本编辑器打开，需要借助工具才可以看见

1. 使用 show binlog events in 'binlog.000001' 命令，查看日志文件
   
2. 在日志文件所在文件夹路径中运行 mysqlbinlog 命令工具，也可以在命令中写上日志文件的全路径

mysqlbinlog mysql-bin.000001 > e:/zane.sql

8、binlog 中除了删除表、创建表的 SQL 都是加密的，如果希望看见可以使用下面命令
直接打印到控制台会出现中文乱码
mysqlbinlog --base64-output=decode-rows -v mylogbin.000001 > D:/zane.sql

5. 慢查询日志

1、记录所有执行时间超过 lone_query_time 秒的所有查询或不适用于索引的查询
2、lone_query_time 默认时间为 10 秒。即超过 10s 的查询都认为是慢查询
3、慢查询日志默认名：主机名-slow.log

4、除了查看 my.ini 文件以外，还可以通过 show variables like '%quer%' 查看

1. slow_query_log 表示是否开启慢查询日志（默认开启）
2. slow_query_log_file 慢查询日志文件名

• slow_query_log：表示是否开启慢查询日志（默认开启）
• slow_query_log_file：慢查询日志文件名
• long_query_time：慢查询阈值设置，超过该时间则为慢查询（此值直接设置全局参数可能无效，建议测试时直接修改配置文件）
• log_queries_not_using_indexes：是否记录不适用于索引的查询

5、慢查询日志内容：记录执行的sql，执行时间等