水手辛巴德

V1

2022/10/03阅读:24主题:橙心

程序员八股文之MySQL篇

数据库事务

事务内的SQL语句,要么全部执行成功,要么全部执行失败。例如:银行转账情况。

四大特性

原子性(Atomicity)

  • 整个事务的所有操作要么全部成功,要么全部失败

一致性(consistency)

  • 数据的前后结果一致。例如账户A给账户B转账100元,那么账户A余额一定是少了100元,账户B余额一定是多了100元的

隔离性(isolation)

  • 事务内所做的操作在未提交以前,对其他事务是不可见的

持久性(durability)

  • 事务一旦提交后,其所做的操作(insert/update/delete)就会永久保存在数据库中;即使系统崩溃,修改的数据也不会丢失。

隔离级别

读未提交(Read uncommited)

  • 级别最低,任何情况都不能保证

读已提交(Read committed)

  • 避免脏读

可重复读(Repeatable read)

  • 避免了脏读、不可重复读
  • Innodb存储引擎在可重复读事务隔离级别下使用的是Next-Key Lock(临建锁)算法,因此可避免幻读的产生。
  • Next-Key Lock:可理解为一种特殊的间隙锁;不仅会锁定(左开右闭)查询涉及的行,同时还会对索引中的间隙进行锁定,防止幻影行的插入

可串行化(Serializable)

  • 级别最高。脏读、不可重复读、幻读都可避免。因为串行处理,故性能较低

总结如下

隔离级别 脏读 不可重复读(update) 幻读(insert/delete)
读未提交(Read uncommited)
读已提交(Read committed)
可重复读(Repeatable read)
串行化(Serializable)

binlog和redolog?

前言

为什么会有两份日志呢?

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司 以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。 ——《极客时间·MySQL实战45讲》

binlog(归档日志)

特性

  • Server(服务器)层面
  • 逻辑日志,记录的是SQL执行语句
  • 非循环使用。在写满/重启后,会生成新的binlog文件
  • 写入磁盘时间点是在事务提交完成后写入的

使用场景

  • 主从复制
  • 数据恢复,被删库后就可以使用binlog恢复数据
  • 审计,对binlog数据进行审计,判断是否存在安全问题,如:SQL注入

刷盘时机

通过在my.cnf配置文件中配置sync_binlog参数控制binlog刷盘时间,取值范围是0-N

  • 0:不强制要求,由系统自动判断何时可写入磁盘
  • 1:每次事务的binlog都将写入磁盘,建议设置成1,这样能保证MySQL异常重启后binlog不丢失
  • N:开启组提交(group_commit),例如,若N=3,那么MySQL会在收集到3个binlog后再将这3个binlog一次性同步到磁盘中

binlog日志格式

格式 statement row mixed
复制方式 基于SQL语句复制,
排除select语句外都会被记录到binlog中
基于行的复制
需要记录每行的修改逻辑
基于statement+row模式的混合
优点 无需记录每行变化,减少binlog的日志量;
减少IO,提升性能
调用系统函数时,不存在数据不一致情况 一般复制采用的是statement
缺点 调用系统函数sysdate()时,导致数据不一致 产生大量日志;如:alter table — —

redlog(重做日志)

产生背景

  • 拥有的crash-safe能力
  • Innodb引擎是以“页”为单位进行磁盘交互的,一般一个事务只是修改1/多个数据页中的几个字节数据,若将完整的数据刷到磁盘的话,非常耗费性能

概念

  • Innodb存储引擎层面
  • WAL(Write-ahead logging),先写日志,再写磁盘
  • crash-safe能力(崩盘恢复),即使数据库发生异常重启数据也不会丢失

特性

  • 物理日志
  • 可循环使用
  • 在事务进行中不断的写入日志
  • 可做异常宕机、介质故障的数据恢复使用
  • 持久性

工作原理

redolog3
redolog3

redolog和binlog对比

日志 redo log binlog
文件大小 大小固定 可通过配置参数
max_binlog_size设置每个binlog文件的大小
实现方式 Innodb引擎层实现的,并非所有引擎都有 Server层实现,所有引擎都可以使用binlog日志
记录方式 采用循环写的方式记录,
当写到结尾时,重新从开头循环写日志。
通过追加方式记录,当文件大小>给定大小值时,
后续记录的日志会记录到新的文件上
日志形式 物理日志,
记录的是在某个数据页上做了什么修改
逻辑日志,
记录的是这个语句的原始逻辑
适用场景 crash-safe(崩溃恢复) 主从复制、数据恢复

undo log(回滚日志)

我们知道,当我们进行数据的新增、删除、修改操作时,会写 redo log(解决数据库宕机重启丢失数据的问题)和 binlog(主要用来做复制、数据备份等操作),另外还会写 undo log,它是为了实现事务的回滚操作

作用

  • 保证事务的原子性,若事务处理过程中出现了错误或执行了rollback语句,MySQL可利用undo log中的备份将数据恢复到事务开始之前的状态
  • MVCC(多个行版本控制)保证并发访问的读一致性问题

产生条件

  • insert、update、delete的操作行为

MVCC(多版本并发控制)

简而言之,就是通过维护数据历史版本,从而解决并发访问情况下的读一致性问题!

undo log
undo log

Innodb加锁算法

记录锁

  • 概念:封住的是索引记录,而不是我们真正的数据记录
  • 例子:
    • select * from table where id=1 for update;其中id必须为主键/唯一索引列、查询语句为精确查询,不能为范围/模糊查询
    • update set age=50 where id=1;通过主键索引、唯一索引对数据进行update操作

间隙锁

  • 概念:基于非唯一索引,它锁定的是一段范围的索引记录、它于Next-Key Locking算法的
  • 举例:select * form table where id between 1 and 10 for update;其中会锁住在(1,10)区间的记录行、在[2,9]之间插入数据都会被阻塞

临建锁

  • 每个数据行上的非唯一索引都会存在一把临建锁
  • 通过临建锁可解决幻读问题

共享锁和排他锁

共享锁(读锁)

  • 在查询语句后增加lock in share mode,MySQL会对查询结果中的每行数据都加共享锁
  • 其他事务可以继续加共享锁,但是不能继续加排他锁

排它锁(写锁)

  • 在查询语句后增加for update
  • 一旦加排他锁,其他事务不能加任何锁

MySQL性能如何调优?

调优工具

  • mysqltuner-pl
  • tuning-primer.sh
  • pt-variable-advisor

服务器硬件优化

  • 网络带宽提升
  • CPU性能
  • SSD固态硬盘

开启慢查询

  • 开启慢查询日志,可在MySQL记录下查询超过指定时间的语句
  • 通过慢SQL可以定位分析性能瓶颈

使用explain分析执行计划

关注以下几项即可:

  • id,是select的序列号,若多个select,则会有多个id,并且id的顺序根据select出现的顺序增加
  • table,表示对哪张表的查询
  • type,查询方式或访问类型,如:system > const > eq_ref > ref > fulltext > index_merge > unique_subquery > index_subquery > range > index > all 表示查询性能从优到差。
  • key,表示MySQL实际选择的索引,若没使用索引,则为null。
  • rows,表示MySQL预估要读取检索的行数(数值越小越好),并不是真实的结果集行数。
  • extra,展示与本次查询附加的信息。如:Using filesort: MySQL中无法利用索引完成的排序操作称为“文件排序”

创建索引优化

  • 联合索引要遵循”最左前缀原则“
  • 索引长度尽量短,节省空间
  • 避免创建过多索引(索引在磁盘中是占用空间的)
  • 建立索引的字段不能参与计算,否则索引将失效
  • 模糊查询%在最右,例如:like 'aa%',否则,索引将无意义。

SQL语句优化

  • 避免select *
  • left join替代join。建议使用left join时,以小表关联大表,因为使用join的话,第一张表是必须全扫描的,以少关联多就可以减少这个扫描次数
  • for update悲观锁谨慎用
  • 避免where语句后使用!=或<>操作符,否则索引失效
  • 避免使用in、not in,可以使用exists、not exists替代
  • 减少对字段进行null判断

分类:

后端

标签:

后端

作者介绍

水手辛巴德
V1