MySQL实战45讲之基础篇 – flowers「终于解决」

前言

本文主要记录学习MySQL实战45讲之基础篇过程中一些新的收获，以及总结主要内容。其中包括SQL如何运行、日志系统、事务隔离、索引和锁等。

基础架构

连接器，建立连接的过程中，连接器会查询用户的权限，本次之后的操作都依赖此时查询出来的权限，意味着，即使你用管理员账号修改了用户的权限，也不会影响已经存在的连接。

查询缓存，大部分场景下查询缓存都是弊大于利，因为一旦表发生修改，缓存就会失效，除非是静态表，即永远不发生修改的。并且，MySQL8.0已经将该功能移除。

分析器，对SQL进行词法分析、语法分析。

优化器，通过分析器知道了要干什么，优化器则是选择干的方案。比如说选择哪个索引来执行？全表扫描还是索引扫描再回表？等等。

执行器，开始执行SQL。在执行之前，会先验证用户是否有查询该表的权限。
为什么不在之前验证表查询权限呢？因为此处不仅要验证表权限，可能还有触发器权限，这个必须在运行时才能确认。

日志系统

redo log

1、用于崩溃恢复，由InnoDB引擎提供。
2、物理日志，记录“在某个数据页上做了什么修改”，通过redo log实现执行SQL之后，不需要马上刷到磁盘，而是先写日志，即WAL（Write Ahead Logging）技术。
3、日志固定大小，通过两个指针循环写。比如 0 ~ write_pos ~ check_point ~ end，write_pos ~ check_point之间的为可写空间，check_point之后循环到write_pos为待刷新到磁盘的数据。当write_pos追到check_point时会停止写，先将check_point后的数据刷到磁盘。

bin log

1、用于操作归档及主从同步，由Server层提供，任何引擎都可以使用。
2、逻辑日志，SQL模式，记录执行的SQL语句；row模式，记录更新前和更新后的行记录。
3、单个日志文件有最大值，满了后创建新的追加写。

总结

redo log和bin log使用两阶段提交来保证两个日志的一致性。此外，通过全量备份和bin log可以恢复到之前任意时刻的数据库状态。

那每周一备和每日一备如何选择？
每周一备，数据恢复时间大概率更长，但需要的存储空间更少；而每日一备，数据的恢复时间更短，但需要的存储空间更多。如何选择，则看业务的重要性考虑。

隔离原理

在可重复读隔离级别下，对于同一个值，不同时刻启动的事务可能读取到不同的值，也叫快照读。这个同一条记录多个版本就是多版本并发控制MVCC，不同版本的记录即回滚日志，是在undo log中存储的。

当然，这个回滚日志不能一直存在。如果没有比这条回滚日志更早的视图时，日志就会被删除。insert操作在事务提交后会直接删除，update和delete操作会写到undo log list中，当判定不会被使用后，要么重用，要么放到删除列表等待purge线程清除。

由上可知，长事务存在一个弊端就是会有很多老视图存在数据库中占据存储空间。所以，建议开启事务的自动提交set autocommit=1。如果想避免每次开启事务的交互，可以考虑用commit work and chain优化，提交事务并启动下一个事务。

索引结构

哈希表，只适用于等值查询的场景，比如Nosql数据库等。对于范围查询必须全表扫描。

有序数组，只从查询效率上考虑的话是很好的结构，但是插入删除时需要移动元素。所以，只适用于静态存储的场景，即从不发生改变的表。

树，增删改查效率都比较好。由于对于同样大小的数据，二叉树高度远高于N叉树，所以为了减少查询时的IO读取，选择的是N叉树，且N差不多为1200，这样的树第4层的节点就有上亿个，即大部分存储场景只需要3~4层就可以满足。并且，一般会提前将1 or 2层先加载到内存。

主键索引选择

主键索引一般默认选择自增主键，原因如下：
1、性能上，自增主键插入索引树，默认是追加，可以有效地避免页分裂，页分裂需要创建新的页，并拷贝数据，必然影响性能。
2、存储空间上，自增主键一般只占用4 or 8字节，采用其他像字符串一样的作为索引需要更多的存储空间。并且，主键值类型占用空间越多，普通索引的占用空间也越大。

重建索引

为什么需要重建索引？
因为删除数据或页分裂，可能导致索引出现空洞，虽然后续操作会尽可能地填补空洞，但始终会存在存储空间的浪费。而重建索引后，可以让数据紧凑，消除数据空洞，提高空间利用率。

不过注意，如果要重建主键索引，默认会删除所有的索引树再重新创建。此时，可以考虑用Alter Table t Engine = InnoDB。

全局锁

全局锁，就是对数据库对象上锁。MySQL支持全局读锁，Flush Table With Read Lock（FTWRL），上锁后会阻塞增删改操作，DDL操作（创建表、修改表结构等）和更新类事务操作。

全局锁的一个典型场景是全量备份。备份期间，业务停止工作且主从库之间数据同步停止，这是比较低效的。不过，这是不支持事务的存储引擎使用的备份方式。对于支持事务的引擎，可以通过在可重复读隔离级别下，开启单个事务来备份，即不用上锁就可以实现一致性读。

表级锁

表级锁，分为两种表锁和元数据锁（meta data lock，MDL）。表锁就是对表数据显式上锁和释放，避免同时对表数据修改；而元数据锁，则是隐式对表结构上锁和释放，分为读锁和写锁，读取数据时上读锁，修改表结构上写锁。读写和写写互斥，避免读取数据时其他事务修改表结构。

注意，如果在一个事务中，先出现DML读取数据，再进行DDL修改表结构，则会阻塞后续的所有读写操作。

那如何安全地给小表加字段？
1、如果有长事务存在，考虑先暂停DDL或者kill掉这个长事务。
2、如果修改的表是热点表，并且不得不加字段，此时kill基本没用，考虑给DDL设定等待时间，失败了等一段时间再重试吧。

行锁

在一个事务中对某一行加锁并处理完后，并不会马上释放锁，而是遵循两阶{段锁协议。所以，为了尽量减少锁冲突，尽量让可能发生锁冲突的操作在事务最末尾执行。

1. 一阶段锁协议
   直接尝试一次性获取所有锁资源，如果其中一个获取失败，就不执行事务，并在事务尾端释放所有资源。
   一阶段锁协议解决了死锁问题，但事务并发度不高。

2. 两阶段锁协议
   整个事务分为两个阶段。第一阶段进行上锁，可以处理数据，但不能释放锁；第二阶段开始释放锁，也可以处理数据，但不能再加锁。
   两阶段锁协议的并发度较高，因为释放锁不必在事务末尾，但它没有解决死锁问题，因为加锁阶段没有顺序要求。

死锁

因为MySQL采用两段锁协议进行加锁，如果加锁顺序不合理时，会产生死锁。解决办法有两种，锁超时和死锁检测。

锁超时

如果因为发生死锁一直锁等待，到达超时时间后会自动回滚超时事务。MySQL默认设置了锁等待超时时间，innodb_lock_wait_timeout=50s，并且通过下面语句操作：

查看：SHOW GLOBAL VARIABLES LIKE "innodb_lock_wait_timeout";
设置：SET GLOBAL innodb_lock_wait_timeout=1500;

死锁检测

MySQL Server层提供的自动检测机制，当发现两个或多个事务形成死锁时，会回滚其中一个或多个较小代价的事务。并且，MySQL默认开启了死锁检测（innodb_deadlock_detect=on）。

当事务并发量大时，死锁检测十分损耗CPU。

那如何处理热点行更新导致的性能问题？

首先，为什么会有性能问题呢？因为热点行可能存在同一时刻大量事务更新同一个行，此时会出现大量锁等待，并触发死锁检测，每个死锁检测都是O（n）的时间复杂度，导致损耗大量的CPU资源。

第一个头痛医头的方法是，如果可以确认不会发生死锁，可以考虑关闭死锁检测，但这一般不采用。

第二个方法是减少并发度，避免同一时刻触发太多的死锁检测。比如控制同一时刻每行最多只有10个线程在更新，或者将原先的一行拆分成多行，这样就可以将原先的并发量缩减为原来的1/n，但视业务场景，可能要考虑拆分成多行造成的副作用，做一个详细方案的设计。

快照读和当前读

快照读

快照读是可重复读隔离级别下默认的查询方式。在每个事务启动时，都会先创建一份读视图，根据视图读取数据。

这个视图实际上就是一个事务id数组，表示当前事务启动时，全库范围内“活跃”的未提交事务id。每个事务都有一个唯一的事务id，由InnoDB事务系统在事务创建前分配，并且按申请顺序严格递增。

此外，真实的快照数据存储在undo log中，在事务更新行记录前，都会在undo log中存储历史版本数据，并记录上当前事务的id，表示row trx_id。从而，通过当前执行的事务id和行的历史版本中的row trx_id比较，就可以判断哪些数据可见。

undo log是逻辑日志，存储的是与更新时相反的逻辑，就是下图中的U1、U2、U3，而V1、V2、V3、V4是不存在的，需要临时计算出来。

可见性结论：
1、版本未提交，不可见。
2、版本已提交，但是在视图创建后提交的，不可见。
3、版本已提交，在视图创建前提交，可见。

当前读

当前读用于更新语句，或者加锁的查询操作（SELECT * FROM t lock in share mode或者SELECT * FROM for update，分别加了共享锁和排他锁），读取数据时会获取最新版本。

总结而言，可重复读依赖快照读实现，当要更新数据时，则采用当前读。

另外，提交读隔离级别下也用到了视图，不过与可重复读创建视图的时机不同。可重复读下，只在事务启动时创建视图，提交读则是每次执行语句前都创建一次视图。

参考

• [1] MySQL 45讲
• [2] MySQL undo log日志