MySQL 思维导图模板_ProcessOn思维导图、流程图

基本架构

连接器

缓存（新版本移除）

sql分析器（语法，词法）

sql优化器（执行方案）

执行器和存储引擎

存储引擎区别

InnoDB和MyIsam区别

5.5版本之前 MyIsam

优点

性能极佳，很适合读密集的场景。

缺点

不支持事务和行级锁，数据库崩溃后无法安全恢复。不支持MVCC。

5.5 版本之后 InnoDB

优点

支持并发事务，支持且默认开启行级锁

有独特日志系统，崩溃后可以安全恢复，因为有独有的redolog。(binlog是什么引擎都有的)

如何选择

InnoDB事务

事务的四大特性

原子性 undolog来保证

一致性

隔离性隔离级别和gap锁来保证

持久性 redolog来保证

并发事务带来的问题

脏写 Dirty Write

一个事务修改了另一个事务已修改但未提交的记录，那就意味着发生了脏写。

脏读（单个记录的多次读取）Dirty Read

一个事务读取了另一个事务已修改但未提交的记录，那就意味着发生了脏读。

不可重复读（单个记录多次读取）Non-Repeatable Read

如果一个事务只能读取另一个已经提交的事务修改后的数据，并且另一个事务每对该数据进行一次修改并提交后，该事务都能查询到最新的值，那就意味着发生了不可重复读。

幻读

如果事务A先根据某些条件查询出一些记录，之后事务B又向表中插入了符合这些条件的记录，事务A再次按照该条件查询时，能把事务B插入的记录也读出来，那就意味着发生了幻读。

注意：如果事务B删除了符合条件的记录，事务A再次按照该条件查询，发现记录少了。这种情况并不算是幻读！幻读强调的是一个事务按照相同条件多次读取记录时，后续读到了之前没有读到的记录。对于之前已经读到的记录，之后又读不到这种情况，其实是算作不可重复读的现象。幻读只是强调了读取到了之前没有获取到的记录。

解决方法

GAP间隙锁

只在RR隔离级别生效。并且GAP不所记录本身，只锁未出现的行

丢失更新

一个事务覆盖了另一个事务对同一条数据的修改

解决方法

乐观锁

for update悲观锁

SQL标准中对不同事务隔离级别的中可能发生问题的规定

在READ UNCOMMITTED隔离级别下，脏读、不可重复读、幻读都可能发生。

在READ COMMITTED隔离级别下，不可重复读、幻读可能发生，脏读不可以发生。

在REPEATABLE READ隔离级别下，幻读不能发生，脏读和不可重复读不可以发生。

在SERIALIZABLE隔离级别下，上述问题都不可以发生。

MySQL对SQL标准中事务隔离级别的实现

READ UNCOMMITTED

读未提交。啥都解决不了。

READ COMMITTED

读已提交。可以解决脏读，但是不能解决不可重复读和幻读。

REPATEABLE READ（默认）

重复读。可以解决脏读，不可重复读，MySQL对RR级别的实现中可以禁止幻读发生。

SERIALIZABLE

串行，没有任何问题。并发性极低。

事务实现原理

解决并发事务问题的两种方式

快照读：读操作用MVCC（无锁）来控制，读写分离

就是在查询时生成了一个ReadView，通过ReadView来获取符合比较条件的版本。ReadView的存在，本身就决定了事务无法读取到其他事务修改未提交的记录，RC级别每次Select都会生成新的ReadView，也就在RC级别下阻止了脏读的发生。查询语句只能读到在生成ReadView之前事务已经提交的版本，在事务正在修改和事务之后提交的记录都对它不可见。而写操作都是针对最新的记录，所以读历史记录和写最新记录并不干扰。并且可以通过生成ReadView时机的控制来解决不可重复读的问题，即区分了出了RC和RR级别。换句话说，采用MVCC，实现了读写分离，读写操作不冲突。

一致性读，或者称为快照读（无锁）（Consistent Reads）

事务通过MVCC进行的读取称为一致性读，也叫作一致性无锁读或者快照读。所有普通select语句在RC或RR级别下都属于一致性读。一致性读不会对表中的任何记录加锁，其他事务可以放心修改。

写操作用锁来控制，读写都要加锁

脏读的发生是事务A读取到事务B已修改但未提交的记录，当事务A或者B读取或者修改时加锁了，那另一方都是无法读到的，也就避免了脏读。不可重复读是事务A多次读取到了事务B对记录已经提交的修改，如果事务A读取时对该记录加锁了，那事务B是无法修改的，也就避免了不可重复读。幻读则是通过锁定未出现的行，另一个事务则无法插入新记录，来解决幻读问题。

锁定读（Locking Reads）

共享锁

事务要读取一条记录时，需要获取S锁。如果事务A想读取记录，可以获得S锁，此时事务B也想读取，也可以获取该S锁进行读取。即S锁和S锁是兼容的。

独占锁

事务要修改一条记录时，需要索取X锁。如果事务A获取S锁进行读取，事务B想获取其X锁进行修改，那么会被S锁阻塞。直到事务A操作结束将S锁释放，事务B才能获取X锁进行修改。即S锁和X锁，X锁和S锁，X锁和X锁之间都是阻塞的。

使用S锁和X锁来锁定读的语句

select * from table lock in share mode

select * from table for update

写操作

增

一般情况下，插入记录时并不会加锁。MySQL中有一种隐式锁来保证插入数据事务结束后，其他事务才能访问。当然，在一些特殊情况下insert操作也是会去获取锁的。

删、改

这个过程可以认定是执行获取X锁的锁定读，锁定待删除记录的位置，再执行操作。

比较

很明显，采用MVCC来实现的读写分离来解决三大问题，性能要高于直接读写加锁。但是在某些场景下，是需要通过加锁的方式来解决问题的，比如银行转账问题，对账户一条记录访问时，必须锁定后修改。

InnoDB索引

索引为何能提高查询速度

索引的优缺点

InnoDB支持的索引类型

B树

自适应Hash，无法干预

聚簇索引和非聚簇索引

聚簇索引（一种数据结构）

以主键创建的索引

叶子节点存储的是主键和对应的数据行

是通往真实数据行的唯一途径。

创建带有主键的表默认使用的就是聚簇索引结构。如果没有主键，引擎会选取带有Unique键(唯一索引)作为主键。如果还没有唯一索引，引擎会自己偷偷给表添加一个row_id隐藏列作为主键来创建聚簇索引。

非聚簇索引（二级索引）

以普通字段创建的索引

叶子节点存储的是索引列和主键

查询时比聚簇索引慢。

创建非聚簇索引的注意事项

联合索引而非多个单列索引，也称为多列索引

最左匹配原则：多列索引时，InnoDB引擎会从左往右匹配，直到遇到>, <, between, like操作符停止匹配

尽量选择索引选择性高的列作为索引列。0-1,1最好

索引列不能是表达式的一部分

索引失效的情况举例

索引一般加在哪些字段

多列索引顺序的选择

索引未生效，该如何排查

Explain

InnoDB中的锁

表锁

行锁的类型

Record Lock （LOCK_REC_NOT_GAP）

平时对一条记录上的就是这个普通记录锁。记录锁也是分S锁和X锁的，满足S和X锁互斥，S S锁兼容的原则。

Gap Lock （LOCK_GAP）

RR级别多次索引范围查询时，可是实现重复读，也同时避免了幻读。虽然防止了幻读，但是不能防止插入幻影记录。Gap Lock的出现仅仅就是为了解决插入幻影行的问题。

Next-Key Lock （LOCK_ORDINARY）

是Record Lock和Gap Lock的合体。即能保护当前记录，又能保护住插入间隙。

Insert Intention Lock  插入意向锁

其他事务在向GAP间隙锁中插入记录时，会被阻塞并且获取该间隙中的插入意向锁，当GAP锁的事务结束时，才会去插入记录。插入意向锁并不会组织其他事务继续获取该记录上的其他任何类型的锁（就是个鸡肋）。

隐式锁（不显式的在内存中创建锁结构）

隐式锁是对于Insert语句来说的。事务在执行Insert时一般是不加锁的，一种特殊情况是插入到间隙时遇到了Gap锁，此时的insert会加插入意向锁。而由此引发了一个问题，如果insert不加锁，那插入后事务还未提交时，其他事务如果获取到了这条记录的S锁或者X锁，岂不是会发生脏读和脏写问题吗？这就需要知道Insert过程中的隐式锁了。我们都知道一条记录中包换隐藏列trx_id，记录了最近修改该条记录的事务id。因此当其他事务在尝试获取该记录的S锁或者X锁时，会先检查记录的trx_id是否为当前活跃的事务id，如果是，就帮助当前事务创建一个X锁以阻止其他事务读写，并且为自己也创建一个锁来进入等待状态。

InnoDB log

binlog（逻辑日志）

内容：记录了对数据库数据和表的变更。除了select都记录。

功能：复制和恢复，主从复制就是用这个来做

模式

row

记录每一行的修改情况。日志量很大。

statement

记录执行的修改语句。日志量小。

mixed

前两者的混合。

redolog（物理日志）