程序员八股文之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判断