MySQL 日志和慢查询

MySQL 日志和慢查询

show variables like "%log%"

1. 日志分类

共6类日志，默认开启：log_error

类型	介绍
错误日志 log_error	记录MySQL服务的启动、运行或停止时出现的问题
查询日志 general_log	记录了数据库执行的所有命令
慢查询日志 slow_query_log	记录所有执行时间超过long_query_time的所有查询或不使用索引的查询
二进制日志 binlog	记录所有更改数据的语句，可以用于数据复制
事务日志 innodb_log	记录数据库更新情况的文件，它可以记录针对数据库的任何操作，并将记录的结果保存到独立的文件中
中继日志 relay_log	主从复制使用

1.1 错误日志 log_error

1.2 查询日志 general_log

影响性能，建议调试时开启

-- 开启
set GLOBAL general_log = on
-- 关闭
set global general_log = off

-- 查询：1.查文件；2.查表
select * from mysql.general_log

1.3 慢查询日志

建议开启，便于优化低效率的SQL

-- 开启：set global slow_query_log = 1/on。重启失效
-- 开启：修改my.cnf，增加slow_query_log = 1/on。永久生效

-- 慢查询时间，单位为s，默认为10s
show variables like 'long_query_time'
-- 修改后需要重启或新开连接生效
set global long_query_time = 1

慢查询记录：1KW数据，执行select count(1) from user

# Time: 2020-11-18T12:14:04.455625Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    19
# Query_time: 85.941469  Lock_time: 0.000214 Rows_sent: 0  Rows_examined: 0
SET timestamp=1605701558;
select count(1) from user;

1.4 二进制日志 binlog

记录mysql内部增删改等对mysql数据库有更新的内容的记录（对数据库的改动），

对数据库查询的语句如show，select开头的语句，不会被binlog日志记录。

binlog日志只要用于数据库的增量恢复，以及主从复制。

格式：statement, row, mixed

statement：记录数据库上执行的原生SQL语句

row: 基于数据的复制

mixed: 默认statement基于语句，如果语句无法精确复制则采用row基于数据的复制

对比：statement节省带宽，但是部分SQL不能被从库正确执行，另外就是必须串行化；而row就是数据复制没问题，但是由于binlog记录实际数据，导致文件过大且不直观

查看SQL

-- 查看binlog文件列表
show binary logs;
-- 查看第一个/指定文件
show binlog events;
show binlog events in 'binlog.000161';
-- 查看当前正写入的binlog
show master status

mysqlbinlog工具

mysqlbinlog --no-defaults ‘binlog.000161’（字符集为utf8mb4，mysqlbinlog不识别，加–nodefaults）

https://blog.51cto.com/6226001001/1672159

1.5 事务日志

顺序IO，预写式日志（保持缓存和磁盘一致性）

redo log

包括日志缓存和磁盘重做日志文件

undo log

log buffer刷到log file：通过变量innodb_flush_log_at_trx_commit控制，默认值为1

0：事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据

1：事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差

2：每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

总结下，就是：0 每秒写入并flush磁盘，1 每事务写入并flush磁盘，2 每事务写入但每秒flush

主从复制，为保证事务的持久性和一致性 ，设置：sync_binlog=1 且 innodb_flush_log_at_trx_commit=1

1.6 中继日志 relay log

relay log是复制过程中产生的日志，很多方面都跟binlog差不多

区别是: relay log是从库服务器I/O线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件，然

后从库的SQL线程会读取relay-log日志的内容并应用到从库服务器上。

2. 慢SQL定位优化

定位：慢查询日志

方案1：慢查询日志

方案2：show processlist.查询正在执行的慢查询

---

前提：

1. 开启慢查询 slow_query_log = on
2. 设置响应时间 long_query_time = 1.0
3. 设置扫描最小记录数 min_examined_row_limit = 0
4. 设置记录不走索引的SQL log_queries_not_using_indexes = on

查看日志：tail -n5 slow.log.只查看最后5行，分别为

发生时间、客户端用户和IP、查询时间 等待表锁的时间 语句返回的行数 语句扫描的行数

分析：explain

本次查询为all，全表扫描（dept_id没有建立索引），然后建立索引explain。数据量1KW

没有建立索引，查询时间为4s+

建立索引后，查询时间为0.002s

type：查询性能从好到差

type	说明
system	查询对象只有一行，存储引擎为MyISAM或Memory
const	基于主键或唯一索引查询，最多返回一条记录
eq_ref	表连接时基于主键或非null的唯一索引
ref	基于普通索引的等值查询、或表间等值连接
fulltext	全文检索
ref_or_null	表连接类型是ref，但进行扫描的索引列可能包含null
index_merge	多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	索引进行范围查询
indedx	全索引扫描
all	全表扫描

优化方法

见参考

---

参考

MySQL日志相关，https://zhuanlan.zhihu.com/p/58011817

主从复制及配置实现，https://segmentfault.com/a/1190000008942618

explain type解释，https://mengkang.net/1124.html

sql优化建议，https://zhuanlan.zhihu.com/p/47155782

https://www.cnblogs.com/yunfeifei/p/3850440.html