《高性能的MySQL》阅读笔记——MySQL架构与历史

目录：

• 1 MySQL 架构与历史
  • MySQL 逻辑架构
  • 并发控制
  • 事务
  • 死锁
  • 多版本并发控制
  • MySQL 的存储引擎

1 MySQL 架构与历史

MySQL 逻辑架构

MySQL 逻辑架构图：

• 最上层的服务负责连接管理，授权认证，安全等。

• 第二层包含了 MySQL 的核心功能。包括：解析、分析、优化、缓存及所有内置函数（日期，时间等），跨存存储引擎功能：存储过程、触发器、视图等。

• 第三层包含了存储引擎。存储引擎类似 UNIX 的文件系统，服务器通过 API 与存储引擎通信。存储引擎 API 包含了几十个底层函数，用于执行如：“开始一个事务”，“根据主键提取一行记录”等操作。存储引擎不解析 SQL 。

连接管理与安全
每个客户端连接都会在服务器进程拥有一个线程，这个连接的查询只会在这个单独的线程中执行。服务器会缓存线程或使用线程池，无需手动销毁每个连接的线程。

优化与执行
MySQL 会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询，表读取顺序，选择合适的索引等。

并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制问题。MySQL 在两个层面对并发控制进行了处理：服务器层和存储引擎层，接下来主要讨论 MySQL 如何控制并发读写。

读写锁
在处理并发读写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。即共享锁和排他锁，又称读锁和写锁。

读锁是共享的，写锁是排他的，会阻塞其他写锁和读锁。

锁粒度
一种提高共享资源并发性的方式就是让锁定的对象更有选择性。尽量只锁定需要修改的部分，而不是锁住所有资源。锁定的数据量越少，系统的并发程度越高。

但加锁也需要消耗资源。锁的各种操作如：获得锁、检查锁是否已经解除、释放锁等，都会增加开销。

所谓锁策略，就是在锁的开销和数据的安全性之间寻求平衡。大多数商业数据库一般都会在表上施加行级锁。MySQL 的多种存储引擎都可以实现自己的锁策略和锁粒度。

表锁
MySQL 中最基本的，开销最小的锁策略。用户在进行写操作是需获得写锁，阻塞其他用户对该表的读写操作。没有写锁才能获得读锁，读锁之间不相互阻塞。

MySQL 服务器在执行 ALTER TABLE 之类的语句时会使用表锁。

行级锁
行级锁可以最大程度地支持并发处理，同时开销最大。行级锁只在存储引擎层实现，MySQL 服务器没有实现。

事务

事务就是一组原子性的 SQL 查询。事务的 4 个特性包括：

1. 原子性（atomicity）
2. 一致性（consistency）
3. 隔离性（isolation）
4. 持久性（durability）

隔离级别
在 SQL 标准定义了四种隔离级别，每一种隔离级别都规定了一个事务中所做的修改，哪些事务是可见的，哪些是不可见的。较低的隔离级别通常可以执行更高的并发，系统的开销更低。标准的 4 种隔离级别如下：

1. READ UNCOMMITTED(未提交读)：在该隔离级别下，事务中的修改，即使没有提交，对其他事务也是可见的。会产生脏读问题。
2. READ COMMITTED(提交读)：一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。会产生不可重复读问题。
3. REPEATABLE READ(可重复读)：该级别保证了同一个事务多次读取同样记录的结果是一样的。会产生幻读问题。
4. SERIALIZABLE(可串行化)：在读取数据的每一行都加锁。

• 脏读：事务可以读取未提交的数据，如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
• 不可重复读：执行两次相同的查询，可能会得到不同的结果。
• 幻读：事务1读取了某个范围的数据，事务2在该范围又插入了数据，事务1再次读取该范围数据时会产生幻行。InnDB 等存储引擎通过 MVCC 解决了该问题。

死锁

死锁指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致的恶性循环现象。多个事务以不同的顺序锁定资源时，就可能产生死锁，多个事务同时锁定一个资源时也会产生死锁。

死锁的例子：

事务1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4;
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3;

事务2
START TRANSACTION;
UPDATE StockPrice SET close = 20.12 WHERE stock_id = 3;
UPDATE StockPrice SET close = 47.20 WHERE stock_id = 4;

此时，陷入了死循环，只有外部因素接触才可能解除死锁。

为了解决这个问题，数据库系统实现了死锁检测和死锁超时机制。InnoDB 检测到死锁后能立即返回一个错误，并将持有最少行排他锁的事务进行回滚。

事务型的系统，死锁时不可避免的，程序的设计时应考虑如何处理死锁。

事务日志

事务日志可以帮忙提高事务的效率。特点：

• 修改行为记录到日志。
• 写日志采用顺序 I/O，而非随机 I/O。
• 事务日志持久化后，后台慢慢刷回磁盘
• 称为预写日志（Write-Ahead Logging），修改数据写两次磁盘。

MySQL 中的事务

自动提交（AUTOINCREMENT）：MySQL 默认采用，每个查询都被当做一个事务执行提交。

查看并设置自动提交模式：

SHOW VARIABLES LIKE 'AUTOCONMMIT'
SET AUTOCOMMIT = 1;

设置事务的隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITABLE;

隐式和显示锁定

InooDB 采用两阶段锁定协议。事务在执行过程中随时可能会锁定，只有在 COMMIT 或 ROLLBACK 的时候才会释放，所有锁都会释放。
InnoDB 的显式锁定：

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

多版本并发控制

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁，它们一般都实现了多版本并发控制（MVCC）。可以认为 MVCC 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。

实现原理

通过保存数据在某个时间点的快照（InnoDB 的 undo 段）来实现。不管事务执行多长时间，每个事务看到的数据都是一致的。根据事务的开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB 的 MVCC

InnoDB MVCC 是通过在每行记录后面保存两个隐藏的列实现的。一个保存的是开始时间，一个保存的是过期时间，时间指的是版本号。每新开始一个事务，系统版本号会递增，做为该事务的事务版本号，用来和查询到的每行记录做比较。

MVCC 的具体操作：

• SELECT
  • InnoDB 会根据两个条件检查每行记录：
    • InnoDB 只查找版本早于当前事务版本的数据行，确保事务读取的行，要么是事务开始前已经纯在的，要么是事务本身操作的。
    • 行的删除版本要么未定义，要么大于当前事务的版本号。确保事务读取到的行，在事务开始之前未被删除。
• INSERT,DELETE
  • InnoDB 为新插入的每一行，保存当前系统版本号作为行版本号。
  • InnoDB 为删除的每一行保存当前系统的版本号作为行版本号。
• UPDATE
  • InnoDB 为插入的新记录，使用当前系统版本号作为新行行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

使用 MVCC 使得大多数读操作不需要加锁，提高读取数据性能，但是同时需要额外的存储空间，用来检查维护工作。有时需要在并发（行锁比表锁并发程度更高）和系统开销（获取锁，释放锁，锁检测耗费CPU）上做权衡。

MVCC 只在 REPEATABLE READ 和 READ COMMITED 两个隔离级别下工作。

MySQL 的存储引擎

在文件系统中，MySQL 将每个数据库（也可称为 schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。

可以使用 SHOW TABLE STATUS 显示表的相关信息。

InnDB 存储引擎
InoDB 是 MySQL 的默认 事务型存储引擎 。它被设计用来处理大量的短期事务。

InnoDB 的数据存储在 表空间 中，表空间是由 InnoDB 管理的一个黑盒子，由一系列的数据文件组成。在高版本的 MySQL 中，InnoDB 可以将每个表的数据和索引存放在单独的文件中。

InnoDB 采用 MVCC 来支持高并发，并且通过 间隙锁 策略防止幻读的出现。

InnoDB 做了很多内部优化，包括从磁盘读取数据时采用的 可预测性预读 ，能够自动在内存中创建 自适应哈希索引 ，以及能够加速插入操作的 插入缓冲区 等。

支持热备份，有相应的工具。

MyISAM 存储引擎
MyISAM 提供了大量特性，包括全文索引，压缩，空间函数等（GIS）。但 MyISAM 不支持事务和行级锁，而且崩溃后无法恢复。