
水手辛巴德
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能力(崩盘恢复),即使数据库发生异常重启数据也不会丢失
特性
-
物理日志 -
可循环使用 -
在事务进行中不断的写入日志 -
可做异常宕机、介质故障的数据恢复使用 -
持久性
工作原理

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(多版本并发控制)
简而言之,就是通过维护数据历史版本,从而解决并发访问情况下的读一致性问题!

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