【详解】MySQL架构

1. MySQL逻辑架构

线程处理

• 每个服务的的连接都会有一个线程，对数据库的查询只会在这个线程中进行
• MySQL5.5之后支持线程池。可以利用少量的线程来服务大量的服务连接

优化和执行

• MySQL会解析查询，并在内部创建（解析树），对其进行优化。包括重写查询、决定表的读取顺序、以及选择合适的索引等
• 可以通过explain查看优化器的各个过程

2. 并发控制

通过共享锁和排他锁

详解请参考：https://blog.csdn.net/qq_43040688/article/details/105440448

3. 事务

MySQL有四种事务级别用来解决并发产生的问题。

详解请参考：https://blog.csdn.net/qq_43040688/article/details/105441274

4. 多版本并发控制（MVCC）

• 是一种行锁的变种，在很多情况下避免了加锁的开销
• 通过保存数据在某个时间点的快照实现的
• 实现主要有乐观并发控制和悲观并发控制

InnoDB的实现原理

• 在每个行记录都有两个隐藏的列
• 这两个列一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），存储的不是时间，而是版本号
• 事务开启时刻的版本号，会作为该事务的版本号，用来和查询到的每行记录的版本号进行比较

以下实现过程针对可重复读隔离级别。
当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增，因此新创建一个事务时，这个事务的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大。

1. SELECT

• 多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。
• 把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于 T 的版本号，因为如果大于或者等于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。除此之外，T 所要读取的数据行快照的删除版本号必须大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删除的，不应该去读取它。
• 读取的数据应该是这个事务开启前的数据，即使在这个期间，别的事务进行了修改，也不可见。同时，还应该保证当前事务在该行数据删除或更新前读取，即使删除或更新，也可以读取到。

2. INSERT
   将当前系统版本号作为数据行快照的创建版本号。
3. DELETE
   将当前系统版本号作为数据行快照的删除版本号。
4. UPDATE
   将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

版本号

• 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号：事务开始时的系统版本号。

注意：

• MVCC仅在为提交读和可重复读这两个事务级别下有效，其他事务会造成冲突，可以不加锁。
• 未提交读读取的数据总是最新的；可串行化会强制加锁

5. 存储引擎

5.1 InnoDB

• 是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
• 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。
• 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
• 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
• 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

5.2 MyISAM

• 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。提供了大量的特性，包括压缩表、空间数据索引等。
• 不支持事务。
• 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。
• 可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。
• 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

5.3 比较

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM 支持压缩表和空间数据索引。

5.4 引擎的选择

• 如果需要事务，选择InnoDB
• 如果数据只读或者大部分只读，选择MyISAM
• 如果并发量大，对数据安全有要求，选择InnoDB
• 订单处理，选择InnoDB
• 大数据量，选择Infobright或者TokuDB