mysql小知识整理

1 mysql执行流程

执行mysql，首先你要获取到mysql一个连接，建立连接之后，开始正式sql语句流程：首先请求打到server层，如果开启了查询缓存选项（这个缓存在表有新数据写入或者更新时就会清空，所以很多公司都不会开启这个选项），那么首先查看有没有命中缓存，如果没有查询到，那么就开始执行正常的流程，mysql的解释器对sql语句进行词法分析，语法分析，优化器判断到底走不走索引，走什么索引，最终通过引擎层（常见的Innodb,myisam）发生IO，拿到结果。这里盗取极客时间，Mysql45讲的一张图。

image.png

2 mysql中的三种重要日志

首先要明确一点，mysql的所有操作最终都要落盘的，不过mysql是先有日志，再根据日志去落盘。三种主要的日志为：redolog, binlog, undolog。

• redolog: 当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。注意：redolog是Innodb引擎特有的，同时，redolog是循环写的，就像行车记录仪一样，写完了会覆盖之前的内容。
  有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

要理解crash-safe这个概念，可以想象赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

• binlog: 是一个逻辑日志，基本功能是记录每一条sql语句，是属于mysql的server层的，也就是无论什么引擎都会有binlog。一般事务提交的时候会同时用到redolog和binog。mysql的事务提交是两阶段提交，状态更新到内存后，首先写入redolog并使其处于ready状态，将binlog落盘，最后将redolog更新为commit状态。

  
  image.png
• undolog: 在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。例如： A->B->C，每一次更新都会生成回滚日志。

mysql崩溃后恢复流程：
假如一个事务插入了一行数据，事务运行中mysql崩溃了：

• redolog和binlog都没有任何记录，则不会有任何影响。
• redolog有prepare记录，则通过全局事务ID去检查binlog有没有相关内容，有则提交，无则丢弃。
• redolog有commit记录，则一定是已经提交的事务了。

3 innodb四种事务隔离级别

• 未提交读：一个事务还没提交时，它做的变更就能被别的事务看到。
• 已提交读：一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化（不可重复读）：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

4 事务多版本控制

事务的多版本控制（MVCC）详细机制复杂，主要是通过视图和undolog进行操作。只要记住以下两条原则就行了，在InnoDb默认的RR级别下，已提交的事务作用不会消失，update会基于最新的。RR的可重复读性质一定会保证。下面具体讲一下：（trx = transaction）
在事务开始时，事务会创建一个视图，并且会有一个数组记录当前正在活跃的事务号，这里用m_ids表示。这个数组是一个单调递增的数组，min_trx_id表示系统中活跃的最小事务id，也就是 m_ids中的最小值。max_trx_id表示在生成视图时，系统应该分配给下一个事务的id。creator_trx_id表示当前的事务id。

• 如果被访问的版本的trx_id和视图的creator_trx_id相同，就意味着当前版本就是由你“造成”的，可以读出来。
• 如果被访问的版本的trx_id小于min_trx_id，表示生成该版本的事务在创建视图的时候，已经提交了，所以该版本可以读出来。
• 如果被访问版本的trx_id大于或等于max_trx_id值，说明生成该版本的事务在当前事务开启后才开启，所以该版本不可以被读出来。
• 如果生成被访问版本的trx_id在min_trx_id和max_trx_id之间，那就需要判断下trx_id在不在m_ids中：如果在，说明创建视图的时候，生成该版本的事务还是活跃的（没有被提交），该版本不可以被读出来；如果不在，说明创建视图的时候，生成该版本的事务已经被提交了，该版本可以被读出来。
• 如果某个数据的最新版本不可以被读出来，就顺着roll_pointer找到该数据的上一个版本，继续做如上的判断，以此类推，如果第一个版本也不可见的话，代表该数据对当前事务完全不可见，查询结果就不包含这条记录了。

上述关于mvcc的描述摘取自知乎上的回答
作者：Java汤姆
链接：https://zhuanlan.zhihu.com/p/131395174

min_trx_id和mx_trx_id通常被称为低水位和高水位，低于低水位的一定可见，高于高水位的一定不可见，位于两者之间的要看是否存在于活跃事务中，存在于活跃事务中的不可见，不存在的可见（说明事务开启前已经被其他事务提交）。

但是对于更新来说，更新操作一定是基于已经提交的最新事务来做的，要不然已经提交的事务就会丢失。所以，对于某些更新操作来说，虽然“读不出来”最新的值，但是会按照最近的值去更新。

5 常见的锁

事先声明，这些锁是站在不同维度来说的，并不是相互对立的概念。https://www.jianshu.com/p/bf862c37c4c9
这篇讲的很好。

• X锁：排它锁，禁止其他事务读或写。
• S锁：共享锁。
• 元数据锁：出现的场景为，假如一个事务处于活跃状态，不能对数据库进行alter table变更。

• 间隙锁：一种排它锁，要明确行锁一定是加在索引上的，要不然就是锁全表了。间隙锁是加在普通索引之间的，在索引相同时再加上主键的判断的一种锁。目的是为了防止幻读。

  
  image.png

session 1:
start  transaction ;
select  * from news where number=4 for update ;

session 2:
start  transaction ;
insert into news value(2,4);#（阻塞）
insert into news value(2,2);#（阻塞）
insert into news value(4,4);#（阻塞）
insert into news value(4,5);#（阻塞）
insert into news value(7,5);#（执行成功）
insert into news value(9,5);#（执行成功）
insert into news value(11,5);#（执行成功）

可以看到，虽然4这个索引只出现了一次，但是这条记录两边的间隙都被锁住了，不允许插入新的数据。

• 记录锁：select * for update会加记录锁，如果*为主键或者唯一键，那就只有记录锁，否则会变为next-key锁。
• next-key锁：临键锁，间隙锁+记录锁。

tips：要牢记间隙锁是加在索引上的，在看间隙锁锁住哪些间隙的时候，要以普通索引为主，主键索引为辅，例如：三条记录： (1, 1),(2, 2),(3,3),(4,1)这四条记录，假如第一列为聚簇索引，第二列为普通索引，判断间隙锁锁什么的时候应该这样看：
(1,1),(4,1),(2,2),(3,3)，如果对普通索引为1进行加锁，那么会锁住与(2,2)之间的间隙，(3,3)前后不受影响。

6 myisam引擎和innodb引擎的区别

myisam是5.5版本之前的默认引擎，后续的默认引擎都换为了innodb，具体区别如下：
作者：oscarwin
链接：https://www.zhihu.com/question/20596402/answer/211492971
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

• 1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
• 2 InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，如果没有指明主键，就会默认生成一个主键。 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• 3. InnoDB 不保存表的具体行数，执行 select count(1) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；explain可以大致看到有多少行，但不精确。
• 4. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

小知识：

• begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot 这个命令。