《MySQL实战45讲》—丁奇大大，学习笔记

图片来自极客时间，如有版权问题，请联系我删除。
扫码加入学习！

01 | 基础架构：一条SQL查询语句是如何执行的？

大体来说，MySQL分为Server层和存储引擎层两部分。

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存：查询请求先访问缓存(key 是查询的语句，value 是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。
分析器：对 SQL 语句做解析，判断sql是否正确。
优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。

02 | 日志系统：一条SQL更新语句是如何执行的？

redo log
MySQL WAL 技术，先写日志，再写磁盘。保证掉电重启，数据不丢失（crash-safe）。
redo log 是 InnoDB 引擎特有的日志。
当记录更新时，Innodb 先记录 redo log 再更新内存，这时更新就算完成。引擎往往会在系统空闲时刷盘。

redo log 是实现了类似环形缓冲区，一个指针 write pos 是当前记录的位置，另一个指针 checkpoint 是当前要擦除的位置，write pos 和checkpoint 之间是空闲部分。如果 write pos 快追上 checkpoint 时，代表缓冲区快满了，需要暂停刷盘。

innodb_flush_log_at_trx_commit参数：
0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

binlog（归档日志）
Server层日志。binlog 日志只能用于归档，没有crash-safe能力。
三个用途:

1. 恢复：利用binlog日志恢复数据库数据
2. 复制：主从同步
3. 审计：通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击

常见格式：

format	定义	优点	缺点
statement	记录的是修改SQL语句	日志文件小，节约IO，提高性能	准确性差，对一些系统函数不能准确复制或不能复制，如now()、uuid()等
row(推荐)	记录的是每行实际数据的变更，记两条，更新前和更新后	准确性强，能准确复制数据的变更	日志文件大，较大的网络IO和磁盘IO
mixed	statement和row模式的混合	准确性强，文件大小适中	有可能发生主从不一致问题

sync_binlog参数：
0：当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
n：在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。

不同点:

1. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
2. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。binlog 文件到一定大小，会切换到下一个文件。

update执行过程：

mysql> update T set c=c+1 where ID=2;

两阶段提交
1 prepare阶段 2 写binlog 3 commit
当在2之前崩溃时
重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。
当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog 。

03 | 事务隔离：为什么你改了我还看不见？

事务ACID、隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

总结：
RR下，事务在第一个Read操作时，会建立read-view
RC下，事务在每次Read操作时，都会建立read-view
不同业务选择不同的隔离级别。

回滚段
rollback segment称为回滚段，每个回滚段中有1024个undo log segment。每个undo操作在记录的时候占用一个undo log segment。
undo log有两个作用：提供回滚和多个行版本控制(MVCC)。
在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

事务隔离的实现
以可重复读（RR）为例，每条记录在更新的时候都会同时记录一条回滚操作。

不同时刻启动的事务会有不同的 read-view。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。
当系统里没有比这个回滚日志更早的 read-view 的时候，回滚日志会被删除。所以要避免长事务。

04 | 深入浅出索引（上）

索引的常见模型
哈希表，不适合做区间搜索。
有序数组，只适合静态数据，插入麻烦。
二叉搜索树，N叉树。
InnoDB 的索引模型
在 MySQL 中，索引是在存储引擎层实现的。
以主键顺序存在B+树中。

主键索引（聚簇索引） 的叶子节点存的是整行数据。主键查询主需要扫描主键索引。
非主键索引（二级索引）的叶子节点内容是主键的值。通过二级索引需要扫描二级索引树，找到主键后再扫描主键索引。该过程称为回表。

索引维护
当插入到索引树最后，只需直接插入。
但当插入到索引树中间，需要逻辑上挪动后面的数据，空出位置，并且当数据页满时，需要申请一个新的数据页，然后挪动部分数据过去（页分裂）。
当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。

自增索引（追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂）
业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
二级索引的叶子节点为主键，业务字段做主键时会占大量存储空间。
什么时候可以使用业务字段做主键？ 只有一个索引；该索引必须是唯一索引。

索引重建

alter table T engine=InnoDB

不推荐drop，再add。并且不论是删除主键还是创建主键，都会将整个表重建。

05 | 深入浅出索引（下）

覆盖索引
当查询值已经在二级索引上时，不需要回表。
最左前缀原则
联合索引合理安排顺序，可以少维护索引，或者减少存储空间。

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

索引ca可以去掉，因为c和主键ab，和ca和主键ab相同。
索引下推
MySQL 5.6 引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

mysql锁大致可以分成全局锁、表级锁和行锁三类
全局锁
全局锁的典型使用场景是，做全库逻辑备份。
FTWRL命令：

Flush tables with read lock;

官方自带的逻辑备份工具是 mysqldump，当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。
但当引擎不支持事务时，只能使用FTWRL 命令了。不推荐不使用 set global readonly=true，readonly会被其他逻辑使用（比如判断主从），readonly发生异常会保持该状态。
表级锁
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write。
MDL不需要显式使用，在访问一个表的时候会被自动加上。
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

当一个长事务还没提交，进行表结构变更操作，会导致后面的事务block。当客户端有重试机制时，新起session请求，会导致库的线程很快就会爆满。

如何安全地给小表加字段？

1. 避免长事务。
2. 在 alter table 语句里面设定等待时间。
   MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

07 | 行锁功过：怎么减少行锁对性能的影响？

行锁
Mysql行锁由引擎层实现
两阶段锁
行锁需要事务结束时才释放，这就是两阶段锁。
所以需要合理安排事务中sql执行顺序，尽量把容易冲突的更新语句放在后面。
死锁和死锁检测

1. 设置超时时间，innodb_lock_wait_timeout。
2. 死锁检测，发现死锁主动回滚某个事务，innodb_deadlock_detect 默认on。
   假设1000个同时更新一行，则死锁检测操作就是 100 万这个量级的。即使没有死锁，检测也会消耗大量的 CPU 资源。

解决方案：

1. 业务不会出现死锁，可以临时关闭。
2. 在客户端控制并发。
3. 修改MySQL 源码，并发进入引擎之前排队。
4. 将一行数据改为多行，如将一个余额账户分为多个，但在数据减少操作时需考虑小于0的情况。

08 | 事务到底是隔离的还是不隔离的？

“快照”在 MVCC 里是怎么工作的？
InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向InnoDB 的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id赋值给这个数据版本的事务 ID，记为 row trx_id。
也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

上图中的三个虚线箭头就是undo log。
某个事务建立快照，只需根据transaction id。只认事务启动时小于数据版本的数据，除自己更新的数据。
快照实现
InnoDB在每个事务启动瞬间，构造了数组保存了当前启动但未提交的事务ID。
数组ID最小值为低水位，当前系统最大事务ID+1为高水位。
数组和高水位，组成了当前事务的一致性事务（read-view）。

黄色部分需分为以下两种情况，因为有可能大于低水位的某个事务已经提交：

• 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
• 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

select read-view创建在03 | 事务隔离中提过了，就不写了。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

不同隔离级别：

• 对于可重复读，查询只承认在事务启动前就已经提交完成的数据
• 对于读提交，查询只承认在语句启动前就已经提交完成的数据
• 而当前读，总是读取已经提交完成的最新版本。

09 | 普通索引和唯一索引，应该怎么选择？

查询过程操作成本相差无几。
更新过程
change buffer概念
change buffer是持久化数据，在内存中有拷贝，也会写到磁盘上。
当更新数据页时，如数据页在内存中直接更新。如果不在，在不影响数据一致性的前提下，innodb会将更新操作先缓存到change buffer中，当下次查询该数据页时，执行change buffer中与该页相关的操作。该操作称为merge，除了该情况，系统后台线程也会定期merge，数据库正常关闭也会merge。
change buffer可以减少读磁盘，而且数据读入内存会占用buffer pool。

什么条件下可以使用 change buffer 呢？
对于唯一索引，更新操作都需要判断操作是否违反唯一约束，所以需要将数据都读入到内存，所以会直接更新内存。
所以只有普通索引会使用change buffer。
change buffer使用buffer pool里的内存，参数innodb_change_buffer_max_size设置为50时，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

当更新记录的目标页不在内存中时，InnoDB 的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。
  所以这种情况，唯一索引会导致磁盘大量随机IO的访问（机械硬盘瓶颈）。
  但这种情况不是绝对的，写多读少的场景change buffer记录的变更多，收益越大。常见业务模型账单类、日志类的系统。对于写完马上读取的情况，会立即触发merge，反而增加了维护change buffer的成本。
  所以尽量选择普通索引。

change buffer 和 redo log
插入语句：

insert into t(id,k) values(id1,k1),(id2,k2);

假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存(InnoDB buffer pool) 中，k2 所在的数据页不在内存中。下图所示是带 change buffer 的更新状态图。

操作顺序：

1. Page 1 在内存中，直接更新内存
2. Page 2 没有在内存中，就在内存的change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中（图中 3 和 4）

图中的两个虚线箭头，是后台操作，不影响更新的响应时间。
执行查询操作：

select * from t where k in (k1, k2);

假设内存中的数据都还在，此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关。

1. 读 Page 1 的时候，直接从内存返回。不需要等内存中的数据更新后返回。
2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志（可能有多个），依次merge一个正确的版本。然后写redo log，redo log中包含数据变更和change buffer 变更。此时内存中数据页为脏页，刷脏是后台线程的流程。（ps：内存中的脏页和redo如何联系，脏页会不会换出，还未学到，知道的同学可以教我一下）

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

最后到底怎么选索引：

1. 业务正确性优先，业务可以保证不重复，普通索引提升效率。业务不能保证重复，就需要唯一索引保证。
2. 历史数据归档库没有唯一索引冲突，可以选择普通索引。

还有还有！~