慢查询日志

MySQL 的动态参数,可以随关随停

慢查询优化

优化策略:

  • 调优 TOP10, 之后迭代…
  • 业务扩展,用户流量增加,进一步调优

慢查询日志的性能剖析工具:汇总一些信息,自动排序

查看慢日志:

正常的格式

explain

EXPLAIN 关键字模拟优化器执行 SQL 查询语句

http://img.janhen.com/202103072224361551841270019.png

1
2
3
4
5
explain select 
star,
evaluator_no
from indicator_evaluate
order by evaluator_no desc;
  • id: 代表执行的顺序
  • type: 找到数据行的方式, 数据的访问类型,取值为如下
    • all: 全表扫描
    • index: ALL 和 INDEX 都读全表,INDEX 从索引读, ALL 从硬盘读
    • range: between, in, >, < 等的查询,无需扫全表
    • ref:非唯一性索引扫描,返回匹配..
    • eq_ref: 唯一性索引扫描,对于每一个索引键,表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
    • const: 表示通过索引一次就找到了,const 用于比较为 primary key(主键索引)或者是 unique 索引,因为只匹配一行,所以很快,若将主键作为 where 条件,MySQL 就会把该查询作为一个常量
    • system :表中只有一条记录(等同于系统表) 这是 const 特例,平时不会出现。
1
2
3
4
# 最好达到 ref OR range 级别
system>const>eq_ref>
ref>range>
index>all
  • extra: Using where, Using index, Using filesort

    最重要的 extra 的取值是前三个 filesort、 using temporary(性能差的sql语句) 、use index(性能好的 sql语句);

    是查询优化器进行选择索引的一个参数,及排序的规则;extra 中出现下面两项意味着 MySQL 不能使用索引,效率会有很大的影响,需尽早优化

    • Using filesort: 外部索引排序,不是从表中按索引次序读取相关内容,可能在内存或磁盘上排序,MySQL 无法利用索引完成的排序。
    • Using temporary: 使用临时表,在 order by 和 group by 中常见
  • select_type: 查询的类型 有以下六种取值

    • simplye: 表示简单查询 ,不包含子查询以及 union
    • primary: 若查询中包含了任何的子查询,最外层的主查询标识为 primary
    • subquery: 标识为子查询
    • derived: 派生的,在 from 子查询的结果会被放入为衍生虚表(临时表)
    • union: 若第二个 select 出现在 union 之后,则会标记为 union(若 union 包含在 from 子句的子查询中,外层的的 select 标识为 derived)
    • union result:从 union 表中获取数据的 select 标识
  • key:实际上使用的索引,为 null 表示为索引失效

  • possiable_keys: 理论上可能使用到的索引,若在查询的时候使用了覆盖索引,那么该索引就不会出现在 possible_keys 中,而只会出现在 key 列中。

  • key_len:表示索引在使用的字节数,可以通过该列计算查询中使用的索引的长度,长度越小越好 key_len 显示的值为索引字段的最大可能长度,而非使用长度,及key_len 是根据表定义计算而得,不是通过表内检索出来的。

  • ref: 表示索引的哪一列被使用,如果可能的话是一个常数,哪些列或者常量被用于索引列上的查找。

  • rows: 根据表信息统计,估算出大约要扫描的行数。

优化:

&手动优化

① 修改SQL: 在业务允许的情况下使得语句走对应的索引;

1
EXPLAIN SELECT evaluator_no, course_id from evaluator;

② 添加索引:对于无法通过修改 SQL 满足业务的情况下,而此 SQL 又进行多次的查询,对其进行添加索引处理

1
alter table <table> add index idx_name(<col>);

&查询优化器优化

不使用密集索引,稀疏索引为二级索引不存放对应的全行信息;

查询的不需要整体信息;

记住抽样统计,同时查询是否排序、是否使用临时表进行索引的选择;

http://img.janhen.com/202103072224511551841656997.png

1
2
3
EXPLAIN SELECT COUNT( id ) FROM person_info_large;
-- 测试使用什么索引所以更好
<sql> FORCE INDEX(<index>);

mysqldumpslow

mysql 自带的工具,较为简单

按序显示执行的 SQL 情况

1
mysqldumpslow --verbose slow-query.log
  • -c: 总次数
  • -t: 时间
  • l: 锁的时间
  • r: 总数据行
  • at,al,ar: 平均数 按照时间排序的前10个查询
1
2
3
4
mysqldumpslow -s t -t 10 slow-query.log
mysqldumpslow -s c -t 10 slow-query.log
mysqldumpslow -s l -t 10 slow-query.log
mysqldumpslow -s r -t 10 slow-query.log

pt-query-digest

percona toolkit 中提供

功能增强的慢查询分析工具,具体使用见 这里

1
2
3
pt-query-digest \\
--type slowlog \\
slow-query.log

根据 STDIN 进行分析

1
pt-query-digest --query "select * from mysql.user"

配置

配置项:

  • slow_query_log=1: 开启慢查询;
  • long_query_time=0.2: 设置慢查询的时间阈值,设置为 200 毫秒;
  • slow_query_log_file:设置慢查询日志文件目录;
  • log_queries_not_using_indexes=1: 显示没有使用索引的 SQL 语句;
1
2
3
4
SHOW VARIABLES LIKE '%quer%';        --慢查询日志开启、位置、时长,查询缓冲、
SHOW STATUS LIKE '%slow_queries%'; -- 当前慢查询数--
Set slow querySET GLOBAL slow_query_log=on;
SET GLOBAL long_query_time=1; -- 当前会话有效, 修改 my.ini 永久

Binlog

概述

逻辑日志。为归档日志;记录了完整的逻辑记录;属于 Server 层的日志,可作用于任何存储引擎;

包含了一些事件,这些事件描述了数据库的改动,如建表、数据改动等,主要用于备份恢复、回滚操作等。

binlog 有三种格式:Statement, Row 和 Mixed.

  • Statement: 基于 SQL 语句的复制(statement-based replication, SBR) : 对一些函数如 now() 在不同主机上执行结果不同会出现不一致的问题
  • Row: 基于行的复制(row-based replication, RBR): 不会出现某些特定情况下的存储过程,或 function,以及 trigger 的调用和触发无法被正确复制的问题。
    • 基于 Row 的,数据恢复更快
  • Mixed: 混合模式复制(mixed-based replication, MBR),混合 statement 和物理文件,减少 binlog 的大小

生成新的 binlog

1
flush logs

生成新的 binlog 的时机

  • MySQL 服务器停止或重启时执行;
  • 使用 flush logs 命令;
  • 当 binlog 文件大小超过 max_binlog_size 系统变量配置的上限时;

查看相关 查看 binblog

1
2
show binary logs;
show master logs;;

查看 binlog 文件的内容

1
show binlog events in "mysql-bin.000005";

两阶段提交

为了保证 binlog 和 redo log 的一致性使用,如果不使⽤“两阶段提交”,那么数据库的状态就有可能和⽤它的⽇志恢复出来的库的状态不⼀致;

分为 prepare 和 commit 两个阶段;

对于 prepare 状态的事务,参考 binlog,若该事务在 binlog 中存在,则将其提交,不存在,则将其回滚,这样保证主从之间的一致性。

不只是误操作后需要⽤这个过程来恢复数据。当需要扩容的时候,也就是多搭建⼀些备库来增加系统的读能⼒的时候,常⻅的做法是⽤全量备份加上应⽤ binlog 来实现的,这个“不⼀致”就会导致你的线上出现主从数据库不⼀致的情况。 简单说,redo log 和 binlog 都可以⽤于表示事务的提交状态,⽽两阶段提交就是让这两个状态保持逻辑上的⼀致。

用途

数据库恢复

借助 binlog 配合一次全量备份实现指定时间点数据的恢复

一些参数

  • --start-position: 开始位置
  • --stop-porition: 结束位置
  • --start-datetime: 开始时间
  • --stop-datetime: 结束时间
  • --database: 指定数据库
  • -no-defaults: 处理默认字符集问题
1
2
3
4
5
6
mysqlbinlog mysql-bin.000001 | mysql -uroot -proot
mysqlbinlog mysql-bin.000002 | mysql -uroot -proot
.....
mysqlbinlog \\
--start-datetime="2005-12-25 11:25:56"\\
binlog.000003

多个 binlog 的恢复

1
mysqlbinlog binlog.[0-9]* | mysql -u root -p

更改一内容再恢复

1
2
3
4
5
6
mysqlbinlog binlog.000001 > tmpfile
... edit tmpfile ...
mysql -u root -p < tmpfile
mysqlbinlog --no-defaults \\
-v --base64-output=DECODE-ROWS \\
mysql-bin.000009

指定位置进行重新执行

1
2
3
mysqlbinlog --start-position=27284 \\
binlog.001002 binlog.001003 binlog.001004
| mysql --host=host_name -u root -p

与 redo log 比较

binlog 与 redo log 日志的区别:

  • redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使⽤。
  • redo log 是物理⽇志,记录的是“在某个数据⻚上做了什么修改”;binlog是逻辑⽇志,记录的是这个语句的原始逻辑,⽐如“给ID=2这⼀⾏的c字段加1”。
  • redo log 空间固定会⽤完;binlog是可以追加写⼊的。“追加写”是指binlog ⽂件写到⼀定⼤⼩后会切换到下⼀个,并不会覆盖以前的⽇志。

日志比较

实时监控 binlog

适用场景:

  • 通过实时对 binlog 进行监控分析,将 binlog 中的数据按照业务进行分开,控制拆分的逻辑
  • 通过实时监控 binlog,根据事件的类型,删除或更改基于数据库的倒排索引,以此来保证索引的有效性
  • 通过实时对 binlog 进行读取,将数据写入到 Kafka 中,交由流式处理工具(如Flink) 做实时的分析

实现方案: 通过第三方库操作(阿里的 canal),因为 mysql 支持通过远程方式下载指定 mysql-server 上的 binlog,从而能够实现借助端口对 MySQL 进行运行情况监控和更改信息的获取。

binlog 清理

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 删除指定日期以前的日志索引中 binlog 日志文件
purge master logs before '2016-09-01 17:20:00';

-- 删除指定日志文件的日志索引中binlog日志文件
-- 将 bin.000022 之前的binlog清掉
purge master logs to 'mysql-bin.000022';

-- 清除master.info文件、relay-log.info文件,以及所有的relay log文件,并重新启用一个新的relaylog文件
-- 使用reset slave之前必须使用stop slave 命令将复制进程停止
reset slave

-- 将删除日志索引文件中记录的所有binlog文件,创建一个新的日志文件,起始值从000001开始。不要轻易使用该命令,这个命令通常仅仅用于第一次用于搭建主从关系的时的主库
reset master:

配置

配置项

  • log-bin:指定 binlog 的文件名
  • innodb_flush_log_at_trx_commit:
  • expire_logs_days:保留 binlog 的天数
  • max_binlog_size: binlog 单个文件的大小,默认 1G,一个事务所产生的所有事件必须记录在同一个 binlog 文件中,所以即使 binlog 文件的大小达到 max_binlog_size 参数指定的大小,也会写入到 binlog 后才能切换。

配置更改

1
2
3
4
5
6
7
[mysqld] 
log-bin = mysql-bin
binlog_format = ROW # can be mixed, decrease
binlog_row_image = minimal
expire_logs_days = 30
sync_binlog = 1 # default 0 affect performance, safe to guarantee replication
innodb_flush_log_at_trx_commit = 2 # defalut 1, per second trx flush 2/0 = perf, 1 = ACID

会话级别更改

1
2
3
# modify
SET SQL_LOG_BIN=0
SET GLOBAL expire_log_days=3;

redolog (物理)

为物理日志。重做日志。InnoDB 在处理更新语句的时候,只做了写⽇志这⼀个磁盘操作。这个⽇志叫作 redo log,在更新内存写完 redo log 后,就返回给客户端,本次更新成功。

作用:

  • 确保事务的持久性
  • 防止在发生故障的时间点,尚有脏页未写入磁盘,在重启 MySQL 服务的时候,根据 redo log 进行重做,从而达到事务的持久性这一特性

只有 InnoDB 有,其他引擎没有;

redolog 确保事务的持久性。

由来: 如果每⼀次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很⾼。

包含两部分,一是内存中的日志缓冲(redo log buffer),该部分日志是易失性的;二是磁盘上的重做日志文件(redo log file),该部分日志是持久的。

WAL

**WAL(Write-Ahead Logging)**: 预写日志。先写⽇志,再写磁盘。利⽤ WAL 技术,数据库将随机写转换成了顺序写,⼤⼤提升了数据库的性能。 但是,由此也带来了内存脏⻚的问题。脏⻚会被后台线程⾃动 flush,也会由于数据⻚淘汰⽽触发 flush,⽽刷脏⻚的过程由于会占⽤资源,可能会让更新和查询语句的响应时间⻓⼀些。

刷新脏页

Q: MySQL “抖” 了一下的原因

A:当内存数据⻚跟磁盘数据⻚内容不⼀致的时候,我们称这个内存⻚为“脏⻚”。

内存数据写⼊到磁盘后,内存和磁盘上的数据⻚的内容就⼀致了,称为“⼲净⻚”。

平时执⾏很快的更新操作,其实就是在写内存和⽇志,⽽ MySQL 偶尔“抖”⼀下的那个瞬间,可能就是在刷脏⻚(flush)。

几种可能的原因:

① InnoDB 的 redo log 写满了。这时候系统会停⽌所有更新操作,把 checkpoint 往前推进,redo log留出 空间可以继续写。

② 系统内存不⾜。当需要新的内存⻚,⽽内存不够⽤的时候,就要淘汰⼀些数据⻚,空出内存给别的数据⻚使⽤。如果淘汰的是“脏⻚”,就要先将脏⻚写到磁盘。

③ MySQL 认为系统“空闲”的时候,即使是“⽣意好”的时候,也要⻅缝插针地找时间,只要有机会就刷⼀点“脏⻚”

④ MySQL正常关闭的情况,这时候,MySQL 会把内存的脏⻚都 flush 到磁盘上,这样下次 MySQL 启动的时候,就可以直接从磁盘上读数据,启动速度会很快。

对性能的影响:

对于 ① 出现这种情况的时候,整个系统就不能再接受更新了,所有的更新都必须堵住。

对于 ② 这种情况其实是常态。InnoDB ⽤缓冲池(buffer pool)管理内存,缓冲池中的内存⻚有三种状态:第⼀种是,还没有使⽤的; 第⼆种是,使⽤了并且是⼲净⻚; 第三种是,使⽤了并且是脏⻚。

InnoDB 刷脏页的控制策略

要正确地告诉 InnoDB 所在主机的 IO 能⼒,这样 InnoDB 才能知道需要全⼒刷脏⻚的时候,可以刷多快。

可能的问题: MySQL 的写⼊速度很慢,TPS 很低,但是数据库主机的 IO 压⼒并不⼤

1
innodb_io_capacity           # 会告诉 InnoDB 磁盘能⼒,建议设置成磁盘的 IOPS

如果你来设计策略控制刷脏⻚的速度,会参考哪些因素呢?

InnoDB 的刷盘速度就是要参考这两个因素:⼀个是脏⻚⽐例,⼀个是 redo log 写盘速度。

参数 innodb_max_dirty_pages_pct 是脏⻚⽐例上限,默认值是 75%。InnoDB 会根据当前的脏⻚⽐例(假设为M),算出⼀个范围在 0 到 100 之间的数字,计算这个数字的伪代码类似这样: 根据上述算得的F1(M)和F2(N)两个值,取其中较⼤的值记为R,之后引擎就可以按照innodb_io_capacity定义的能⼒乘以R%来控制刷脏⻚的速度。

http://img.janhen.com/202103072225031553991442351.png

配置

  • innodb_flush_log_at_trx_commit
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36

    :指定何时将事务日志刷到磁盘,默认为1。

    - 0: 事务提交时不会将 log buffer 中日志写入到 os buffer,而是每秒写入 os buffer 并调用fsync()
    - 系统崩溃,会丢失 1 秒钟的数据。
    - 1:每次 commit 都会把 redo log 从 redo log buffer 写入到 system,并 fsync 刷新到磁盘文件中。
    - 即使系统崩溃也不会丢失任何数据,但是因为每次提交都写入磁盘,IO 的性能较差。
    - 2: 每次事务提交时 MySQL 会把日志从 redo log buffer 写入到 system,但只写入到 file system buffer,由系统内部来 fsync 到磁盘文件。

    - `innodb_log_buffer_size`: redo 日志的缓冲区,默认为 8M,延迟事务日志写入磁盘

    - `innodb_log_files_in_group`: redo 日志的个数,默认为2,命名为 ib_logfile<N>

    - `innodb_log_file_size`: 事务日志的大小

    - `innodb_log_group_home_dir`: 事务日志组路径,当前目录表示数据目录

    ## undo 日志

    > 属于 InnoDB 存储引擎特有的日志,做事务的处理。

    **提供回滚和多个行版本控制(MVCC)**。

    为逻辑日志,默认存放在共享表空间中,如果配置了 innodb_file_per_table,将会存放在 <table-name>.ibd 中。

    相关的一些问题

    ### 配置

    - innodb_max_undo_log_size:
    - innodb_undo_tablespaces:
    - innodb_undo_log_truncate:
    - innodb_purge_rseg_truncate_frequency:

    ```sql
    show global variables like '%undo%';

其他

relay log

中继日志,是复制过程中产生的日志。

relay log 是从库服务器 I/O 线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件,然后从库的 SQL 线程会读取 relay-log 日志的内容并应用到从库服务器上。

配置

  • relay_log:
  • max_relay_log_size:
  • relay_log_recovery

错误日志

查找 MySQL 错误日志,查看 MySQL 普通的日志

通过在 mysql.cnf 中配置错误日志的地址

  • log_error:on|文件路径 是否启用错误日志,on表示开启,文件路径表示指定自定义日志路径
  • log_warnings: 1|0 是否记录warnings信息到错误日志中
1
show variables like "log_error";

查询日志

  • general_log: on / off
  • general_log_file:文件保存地址
1
2
3
4
-- 查看
show global variables like "%genera%";
-- 开启
set global general_log = on;

Refs

MySQL :: MySQL 8.0 Reference Manual :: 4.6.8 mysqlbinlog - Utility for Processing Binary Log Files

工具 mysqlbinlog 官方文档

[玩转MySQL之八]MySQL日志分类及简介

【MySQL (六) | 详细分析MySQL事务日志redo log】

【MySQL (六) | 详细分析MySQL事务日志redo log】

MySQL实战45讲_MySQL_数据库-极客时间

极客时间-MySQL实战45讲

MySQL中的重做日志(redo log),回滚日志(undo log),以及二进制日志(binlog)的简单总结 - MSSQL123 - 博客园