看完这一篇,别在说你学过MySQL了(二)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介: 大家好,我是Leo。目前在常州从事Java后端开发的工作。这篇是MySQL学习整理系列的第二篇。这个系列会与字节,网易,阿里,腾讯,美团,快手的相关朋友一起整理输出。希望帮助更多的朋友早日入大厂!

4. 日志


4.1 错误日志

MySQL错误日志是记录MySQL 运行过程中较为严重的警告和错误信息,以及MySQL每次启动和关闭的详细信息。错误日志的命名通常为hostname.err

通过如下SQL,可以找到错误日志的位置。

show variables like '%log_error%';

错误日志如果不清理或删除,那么它会一直增长。在MySQL 5.5.7之前,可以通过mysqladmin –uroot –p flush-logs命令删除错误日志。MySQL 5.5.7以及之后,只能通过下面方式来归档、备份错误日志

shell> mv host_name.err host_name.err-old
shell> mysqladmin -u root -p flush-logs
shell> mv host_name.err-old backup-directory

错误日志可以任意命名。只需要在/etc/my.cnf配置文件中,添加了参数log_error=/u02/mysql/mysql.err,重新启动MySQL即可。

4.2 查询日志

MySQL的查询日志记录了所有MySQL数据库请求的信息。无论这些请求是否得到了正确的执行。默认文件名为hostname.log。默认情况下MySQL查询日志是关闭的。生产环境,如果开启MySQL查询日志,对性能还是有蛮大的影响的

不常用就不做过多介绍了

4.3 慢日志

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。

long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

慢查询日志涉及的重要参数

  • slow_query_log  :是否开启慢查询日志,1表示开启,0表示关闭。
  • long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。

慢查询日志涉及的重要工具

  • mysqldumpslow

常用指令

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

-s 是表示按照何种方式排序,

c: 访问计数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t  是top n的意思,即为返回前面多少条的数据

后面是目录

more: 另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。

4.4 redolog 重做日志

提到redolog,肯定是要聊到redo log bufferredo log file。前者是日志的缓存,是易失性的。后者是日志文件,是持久性的。

写入机制

redo log buffer 要做的是一个事务在插入一条数据的时候,需要先写入日志。但是又不能在还没有提交事务的时候直接写到redo log文件中。这个日志的临时存放处就是redo log buffer。真正在写入redo log文件的过程是在commit这一步完成的。(执行一个SQL语句也是一个事务)

如果还没等到commit这一步,主要会有两种可能

  1. MySQL宕机了,这份缓冲区日志丢失了也就丢失了,也不会有什么损失。
  2. 持久化到磁盘了!

接着持久化磁盘

image.png

  • redo log buffer:物理上这是MySQL的进程内存
  • FS page cache:写入到磁盘,但是还没有进行持久化。物理上是page cache文件系统。
  • hard disk,这个就是持久化到磁盘了

  • 图中的红色区域是内存操作,不涉及到磁盘IO。所以性能的非常快的。write也是非常快的
  • 图中的黄色部分。fsync的速度就慢了很多。因为持久化到磁盘

写入策略

innodb_flush_log_at_trx_commit

  • 设置为 0 的时候,表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  • 设置为 1 的时候,表示每次事务提交时都将 redo log 直接持久化到磁盘;
  • 设置为 2 的时候,表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程,每隔 1 秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的 page cache,然后调用 fsync 持久化到磁盘。

刷新策略

redo log buffer的刷新策略,是由innodb_log_buffer_size 控制的。

  • redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候,后台线程会主动写盘。

(注意,由于这个事务并没有提交,所以这个写盘动作只是 write,而没有调用 fsync,也就是只留在了文件系统的 page cache。)

  • 并行的事务提交的时候,顺带将这个事务的 redo log buffer 持久化到磁盘

(假设一个事务 A 执行到一半,已经写了一些 redo log 到 buffer 中,这时候有另外一个线程的事务 B 提交,如果 innodb_flush_log_at_trx_commit 设置的是 1,那么按照这个参数的逻辑,事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候,就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。)

组提交机制

日志逻辑序列号,简称LSN。LSN是单调递增的。用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log, LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中,来确保数据页不会被多次执行重复的 redo log。

image.png

如上图所述,

  • trx1是最先到达的,会被选为这组的leader。
  • 等 trx1 要开始写盘的时候,这个组里面已经有了三个事务,这时候 LSN 也变成了 160;
  • trx1 去写盘的时候,带的就是 LSN=160,因此等 trx1 返回时,所有 LSN 小于等于 160 的 redo log,都已经被持久化到磁盘;
  • 这时候 trx2 和 trx3 就可以直接返回了。

所以,一次组提交里面,组员越多,节约磁盘 IOPS 的效果越好。但如果只有单线程压测,那就只能老老实实地一个事务对应一次持久化操作了。

在并发更新场景下,第一个事务写完 redo log buffer 以后,接下来这个 fsync 越晚调用,组员可能越多,节约 IOPS 的效果就越好。

4.5 binlog 归档日志

写入机制

binlog写入日志这个是比较简单的。提到binlog,必然提到binlog cache。那么binlog cache是什么?

binlog cache是一个二进制日志文件的缓冲区,他是由一个参数 binlog_cache_size 控制大小的缓冲区。

一个事务在执行是时候是不允许被拆开的,因此无论事务多大,都是要一次性保存执行的。那么这个就涉及到了binlog cache 的保存问题。如果所占的内存大小超过了这个binlog_cache_size 参数的设定。就会采用暂存到磁盘。事务在提交的时候,会先把binlog cache里的数据写入到binlog中,并清空binlog cache数据。

image.png

由上图我们可以得知每个binlog cache是由单独的一个线程享有的。也就是说多个线程带着多个binlog cache写入binlog file是非常快的,因为并没有涉及到磁盘IO的开销。

当进行到了fsync的时候,才是将数据持久化到磁盘操作。这个时候才会占用磁盘IO,也就是我们常说的IOPS。

何时write?何时fsync?

主要由sync_binlog控制的。

  • 当它等于0时,每次提交事务都只 write,不 fsync
  • 当它等于1时,每次提交事务都会执行 fsync
  • 当它大于1时, 每次提交事务都 write,但累积 N 个事务后才 fsync

因此,在出现 IO 瓶颈的场景里,将 sync_binlog 设置成一个比较大的值,可以提升性能。在实际的业务场景中,考虑到丢失日志量的可控性,一般不建议将这个参数设成 0,比较常见的是将其设置为 100~1000 中的某个数值。

但是,将 sync_binlog 设置为 N,对应的风险是:如果主机发生异常重启,会丢失最近 N 个事务的 binlog 日志。

组提交

binlog也是可以组提交的。主要分成两部分

  • 先把 binlog 从 binlog cache 中写到磁盘上的 binlog 文件;
  • 调用 fsync 持久化。

image.png

如上图所述,可以看第二步。

如果多个事务都已经write了(也就是说写入到redo log buffer了),再到第四步的时候就可以一起持久化到磁盘了。不是提升IOPS的这个优化过程嘛!

不过通常情况下第 3 步执行得会很快,所以 binlog 的 write 和 fsync 间的间隔时间短,导致能集合到一起持久化的 binlog 比较少,因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 组提交的效果,可以通过设置 binlog_group_commit_sync_delaybinlog_group_commit_sync_no_delay_count 来实现。这两个只要有一个满足条件就会调用 fsync。

  • binlog_group_commit_sync_delay 参数,表示延迟多少微秒后才调用 fsync;
  • binlog_group_commit_sync_no_delay_count 参数,表示累积多少次以后才调用 fsync。

WAL机制主要得益于

  • redo log 和 binlog 都是顺序写,磁盘的顺序写比随机写速度要快;
  • 组提交机制,可以大幅度降低磁盘的 IOPS 消耗。

4.6 undolog 回滚日志

undo log主要有两个作用:回滚和多版本控制(MVCC)

在数据修改的时候,不仅记录了redo log,还记录undo log,如果因为某些原因导致事务失败或回滚了,可以用undo log进行回滚

undo log主要存储的也是逻辑日志,比如我们要insert一条数据了,那undo log会记录的一条对应的delete日志。我们要update一条记录时,它会记录一条对应相反的update记录。

这也应该容易理解,毕竟回滚嘛,跟需要修改的操作相反就好,这样就能达到回滚的目的。因为支持回滚操作,所以我们就能保证:“一个事务包含多个操作,这些操作要么全部执行,要么全都不执行”。【原子性】

因为undo log存储着修改之前的数据,相当于一个前版本,MVCC实现的是读写不阻塞,读的时候只要返回前一个版本的数据就行了。


5. 跳表,回表


5.1 为什么

跳表

跳表 也是为了 快速查找 而提出的一种数据结构

我们在链表中查询数据的时候,时间复杂度是O(n),为了解决效率问题,跳表就产生了。它本质上是一种多级链表,通过增加数据的冗余来换取查找的时间复杂度,属于空间换时间的思想。不过呢,其实空间也不会消耗太多,因为冗余的只是节点指针。

优点分析

  • 相比红黑树来说,跳表实现简单,你面试的时候是可以手写出来的,而且插入和删除的操作也不难。红黑树里面大量的自旋操作常常让人迷惑。
  • 数据是自排序的,这点和MYSQL里面的B+树很像,默认是从小到大排序的。利用这一点就是快速进行范围查找,而不用真正地排序。

链表,跳表比较

image.png

查询流程

如果要在这里面找 21

  • 链表:过程为 3→ 6 → 7 → 9 → 12 → 17 → 19 → 21 。
  • 跳表:6→9→17→21

跳表的主要思想就是这样逐渐建立索引,加速查找与插入。从最上层开始,如果key小于或等于当层后继节点的key,则平移一位;如果key更大,则层数减1,继续比较。最终一定会到第一层

插入流程

image.png

先确定该元素要占据的层数 K(采用丢硬币的方式,这完全是随机的)。

然后在 Level 1 ... Level K 各个层的链表都插入元素。

用Update数组记录插入位置,同样从顶层开始,逐层找到每层需要插入的位置,再生成层数并插入。

例子:插入 119, K = 2

删除流程

与插入类似

回表

回表这里我们举一个常见的例子。从刚接触代码起,我们就已经开始写登录注册了。那么我们登录的时候账号,密码是如何设置的呢?

数据量小还好,一旦数据量起来的肯定是要添加索引的。问题来了,索引如何建立!

如果只给账号设置索引的话就碰到了回表操作。

MySQL底层是B+树。如果给账号设置索引的话,账号这个字段就成了一个节点树。而我们查询的时候会查询账号+密码。密码不在这颗树上,所以就需要回表去查询密码这个字段然后拼凑在一起。

5.2 如何避免

回表意味着增加磁盘IO的开销,所以避免回表也是优化MySQL的一种方式。还是举登录这个例子,账号密码属于高频查询。给账号+密码创建一个联合索引就可以避免回表了。

剩下的就根据各自的业务场景需求啦。比如软件设计师官网的登录。利用的是身份证+密码。每一种都不一样。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
关系型数据库 MySQL 数据库
MySQL基本知识点
本章主要讲解了MySQL的基础知识,比如事务特性、事务隔离级别
110 1
|
14天前
|
存储 Oracle 关系型数据库
[MySQL]知识点
本文详细介绍了MySQL中int族和char族数据类型的特点、存储范围及使用建议,以及text、blob类型和内置字符处理函数。文章强调了数据类型选择的重要性,并提供了多个实例帮助理解。
30 0
[MySQL]知识点
|
存储 Oracle 关系型数据库
MYSQL知识点
MYSQL知识点
|
关系型数据库 MySQL
MySQL 函数详解 - 小白必看(三)
在MySQL中,聚合函数主要由:count,sum,min,max,avg,这些聚合函数我们之前都学过,不再重复。这里我们学习另外一个函数:**group_concat()**,该函数用户实现行的合并。
144 0
MySQL 函数详解 - 小白必看(三)
|
SQL 存储 前端开发
|
SQL 存储 算法
mysql常见知识点
在mysql的学习中,将一些常用的知识点写出来,句句皆干货
131 0
|
存储 SQL 关系型数据库
MySQL部分知识点总结
MySQL部分知识点总结,索引、事务、锁以及优化等
157 0
MySQL部分知识点总结
|
存储 SQL 缓存
看完这一篇,别在说你学过MySQL了
大家好,我是Leo。目前在常州从事Java后端开发的工作。这篇是MySQL学习整理系列的第一篇。这个系列会与字节,网易,阿里,腾讯,美团,快手的相关朋友一起整理输出。希望帮助更多的朋友早日入大厂!
看完这一篇,别在说你学过MySQL了
|
SQL 算法 安全
看完这一篇,别在说你学过MySQL了(五)
大家好,我是Leo。目前在常州从事Java后端开发的工作。这篇是MySQL学习整理系列总结篇。这个系列会与字节,网易,阿里,腾讯,美团,快手的相关朋友一起整理输出。希望帮助更多的朋友早日入大厂!
看完这一篇,别在说你学过MySQL了(五)
|
存储 关系型数据库 MySQL
终于有人将MySQL的安装讲明白了
终于有人将MySQL的安装讲明白了
165 0
终于有人将MySQL的安装讲明白了