漫谈mysql架构（第一期）

1、mysql逻辑架构

第一层：比如连接处理、授权认证、安全等等 职责，针对用户端的网关

第二层：核心服务功能，包括 查询解析、 分析、 优化、 缓存以及所有的内置函数（例如， 日期、 时间、 数学和加密函数） ， 所有跨存储引擎的功能都在这一层实现：存储过程、 触发器、 视图等 
第三层：存储引擎 ， 数据的存储和提取

2、优化与执行

在第二层服务器层面，mysql会用用户提交的sql进行解析，创建 解析树，优化，选择合适的索引，过程如下：

a.SQL ：
	编写过程：
		select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..
	解析过程：			
		from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
b.SQL优化， 主要就是 在优化索引
	索引：相当于书的目录
	索引：index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树(默认)、Hash树...）

用户可以通过特殊的关键字提示（hint） 优化器， 影响它的决策过程。也可以请求优化器解释（explain） 优化过程的各个因素， 使用户可以知道服务器是如何进行优化决策的， 并提供一个参考基准， 便于用户重构查询和schema、 修改相关配置， 使应用尽可能高效运行 。

对于SELECT 语句， 在解析查询之前， 服务器会先检查查询缓存（Query Cache） ， 如果能够在其中找到对应的查询， 服务器就不必再执行查询解析、 优化和执行的整个过程， 而是直接返回查询缓存中的结果集。

 

3、并发控制

隔离级别

①读未提交（read uncommited）

事务中的修改， 即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据， 这也被称为脏读（Dirty Read）

②读已提交(read commited)

一个事务开始时， 只能“看见”已经提交的事务所做的修改。换句话说， 一个事务从开始直到提交之前， 所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读
（nonrepeatable read）

③可重复读(repeatable read)

REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上， 可重复读隔离级别还是无法解决另外一个幻读（Phantom Read） 的问题。所谓幻读， 指的是当某个事务在读取某个范围内的记录时， 另外一个事务又在该范围内插入了新的记录， 当之前的事务再次读取该范围的记录时， 会产生幻行（Phantom Row） 。InnoDB存储引擎通过多版本并发控制（MVCC， Multiversion ConcurrencyControl） 解决了幻读的问题。

具体实现是通过间隙锁来实现

④可串行化(serializable)

SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行， 避免了前面说的幻读的问题。简单来说， SERIALIZABLE 会在读取的每一行数据上都加锁， 所以可能导致大量的超时和锁争用的
问题。实际应用中也很少用到这个隔离级别， 只有在非常需要确保数据的一致性而且可以接受没有并发的情况下， 才考虑采用该级别。

 

间隙锁论述：

在mysql的innoDB存储引擎中，如果更新操作是针对一个区间的，那么它会锁住这个区间内所有的记录，比如update xxx where id between a and b那么它会锁住a到b之间所有记录，注意是所有记录，甚至这个记录并不存在也会被锁住，这个时候，如果另外一个连接需要插入一条记录到a到b之间，那么它就必须等到上一个事务结束。

典型的例子就是使用auto_increment id，由于这个id是一直往上分配的，因此两个事务都insert时，会得到两个不同的，但是这两条记录还没有被提交，因此也就不存在，如果这个时候有一个事务进行范围操作，而且恰好要锁住不存在的，就是触发间隙锁问题。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

 

结论：mysql中尽量不要使用区间更新。

在如表20-13所示的例子中，假如emp表中只有101条记录，其empid的值分别是1,2,......,100,101。

表20-13    InnoDB存储引擎的间隙锁阻塞例子

 

session_1	session_2
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)   mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
当前session对不存在的记录加for update的锁： mysql> select * from emp where empid = 102 for update; Empty set (0.00 sec)
	这时，如果其他session插入empid为201的记录（注意：这条记录并不存在），也会出现锁等待： mysql>insert into emp(empid,...) values(102,...); 阻塞等待
Session_1 执行rollback： mysql> rollback; Query OK, 0 rows affected (13.04 sec)
	由于其他session_1回退后释放了Next-Key锁，当前session可以获得锁并成功插入记录： mysql>insert into emp(empid,...) values(102,...); Query OK, 1 row affected (13.35 sec)

 

4、死锁

死锁是指两个或者多个事务在同一资源上相互占用， 并请求锁定对方占用的资源， 从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时， 就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。例如， 设想下面两个事务同时处理StockPrice 表：

事务1
START TRANSACTION;
更多电子书资料请搜索「书行天下」：http://www.sxpdf.com
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002
COMMIT;
事务2
START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-
COMMIT;
如果凑巧， 两个事务都执行了第一条UPDATE 语句， 更新了一行数据， 同时也锁定了该行数据， 接着每个事务都尝试去执行第二条UPDATE语句， 却发现该行已经被对方锁定， 然后两个事务都等待对方释放锁，同时又持有对方需要的锁， 则陷入死循环。除非有外部因素介入才可能解除死锁 。

 

解决办法：

数据库层面：

数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方法是， 将持有最少行级排他锁的事务进行回滚。

应用程序层面：

大多数情况下只需要重新执行因死锁回滚的事务即可。

 

5、事物日志

事务日志可以帮助提高事务的效率。使用事务日志， 存储引擎在修改表的数据时只需要修改其内存拷贝， 再把该修改行为记录到持久在硬盘上的事务日志中， 而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式， 因此写日志的操作是磁盘上一小块区域内的顺序I/O， 而不像随机I/O需要在磁盘的多个地方移动磁头， 所以采用事务日志的方式相对来说要快得多。事务日志持久以后， 内存中被修改的数据在后台可以慢慢地刷回到磁盘。通常称之为预写式日志（Write-Ahead Logging） ， 修改数据需要写两次磁盘。如果数据的修改已经记录到事务日志并持久化， 但数据本身还没有写回磁盘， 此时系统崩溃， 存储引擎在重启时能够自动恢复这部分修改的数据。

 

6、两阶段锁定协议（two-phase locking protocol）

InnoDB采用的是两阶段锁定协议（two-phase locking protocol） 。在事务执行过程中， 随时都可以执行锁定， 锁只有在执行COMMIT 或者ROLLBACK 的时候才会释放， 并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定， InnoDB会根据隔离级别在需要的时候自动加锁。
另外， InnoDB也支持通过特定的语句进行显式锁定， 这些语句不属于SQL规范  ：
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
MySQL也支持LOCK TABLES 和UNLOCK TABLES 语句， 这是在服务器层实现的， 和存储引擎无关。

 

7、 多版本并发控制

MVCC的实现， 是通过保存数据在某个时间点的快照来实现的。也就是说， 不管需要执行多长时间， 每个事务看到的数据都是一致的。根据事务开始的时间不同， 每个事务对同一张表， 同一时刻看到的数据可能是不一样的。InnoDB的MVCC， 是通过在每行记录后面保存两个隐藏的列来实现的。这两个列， 一个保存了行的创建时间， 一个保存行的过期时间（或删除时间） 。当然存储的并不是实际的时间值， 而是系统版本号（system version number） 。每开始一个新的事务， 系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号， 用来和查询到的每行记录的版本号进行比较。

SELECT
     InnoDB会根据以下两个条件检查每行记录：
     a. InnoDB只查找版本早于当前事务版本的数据行（也就是， 行的系统版本号小于或等于事务的系统版本号） ， 这样可以确保事务读取的行， 要么是在事务开始前已经存在的， 要么是事务自身插入或者修改过的。
     b. 行的删除版本要么未定义， 要么大于当前事务版本号。这可以确保事务读取到的行， 在事务开始之前未被删除。只有符合上述两个条件的记录， 才能返回作为查询结果。
INSERT
      InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE
      InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
      InnoDB为插入一行新记录， 保存当前系统版本号作为行版本号， 同时保存当前系统版本号到原来的行作为行删除标识 。

 

保存这两个额外系统版本号， 使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单， 性能很好， 并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间， 需要做更多的行检查工作， 以及一些额外的维护工作。

 

总结

MySQL拥有分层的架构。上层是服务器层的服务和查询执行引擎， 下层则是存储引擎。虽然有很多不同作用的插件API， 但存储引擎API还是最重要的。如果能理解MySQL在存储引擎和服务层之间处理查询时如何通过API来回交互， 就能抓住MySQL的核心基础架构的精髓。 

 

如果您已阅读到此，感谢您对公众号里文章的认可，请动动你可爱的小指头关注此公众号，每天都有硬核文章推送，就算离开也能找到回家的路