运维日记| MySQL/Oracle深度解析之一：逻辑读

布满荆棘的人生 2023-06-14 10:38 30阅读 0赞

原创： 吕海波 新运维新数据 今天

各位新朋友～记得先点蓝字关注我哦～

前言  
THE FIRST

比较数据库优劣、异同的文章有很多了，使用压测工具，进行不同压力下的测试，就能大致上比较出来哪种数据库是“最快”的数据库。但从有经验的数据架构、DBA等专业人士角度，仅仅“最快”是不够的，数据库是十分复杂的体系，要考虑方方面面的问题。

本文从“竞争”的角度，使用调试技术，在MySQL和Oracle的代码世界中畅游，像使用显微镜一样，以“最近”的距离，分析两种数据库由于代码设计的不同，而造成的竞争与锁机制的异同。

数据库的竞争与锁机制，是数据库最为复杂的部分，不可能在一篇短短的文章中描述完，本文以“逻辑读”为样本，从细枝末节中对比MySQL、Oracle的优劣、异同。逻辑读是数据库最基础的操作，也是最繁忙的操作。

我们先从MySQL入手，这里的研究对象当然是MySQL的InnoDB引擎。其他常用引擎比如Myisam根本没有缓存，没有数据库层面的逻辑读。

再说一下调试技术，它不同于直接一上来就开始的源码阅读，调试技术重点在于要读“活”着的源码。我们不去看磁盘文件中静止的.c、.cpp、.h等文件，而是要把程序运行起来，再结合源码，观察它运行起来的行为，理解源码其中的细枝末节。

有了对细节的探索，可以像他一样：

否则，只能像他一样：

第一节  
基础知识介绍：MySQL中的HASH表与CBC Latch  
MySQL的逻辑读，网上有相关函数，从buf\_page\_get\_gen函数开始，一直到MySQL8都是这样。不过相比5.7，这个函数在MySQL8中有了很大的改动。我们后面的所有分析，都是针对MySQL 8。

数据页或块的缓存，在MySQL中称为Buffer Pool，在Oracle中称为Buffer Cache，下面统一简称数据缓存。无论MySQL还是Oracle，数据缓存的管理方式类似，如下图：

其实不只MySQL、Oracle，此图适用于所有主流关系型数据库的数据缓存管理。

在MySQL中，HASH表由hash\_cell\_t型的结构组成，每一个Bucket，都是一个hash\_cell\_t型的结构变量。hash\_cell\_t的定义如下：

plugin/innodb\_memcached/innodb\_memcache/include/innodb\_utility.h的126行中你可以找到它的定义。

我建议你打开它看一看，这样，你也是读过MySQL源码的人了，酷不酷。

每一个HASH Bucket都是一个void型的指针，也就是内存地址。在64位系统中，占8字节：

每一个HASH Bucket后，都是一个链表，就是如下的部分：

Oracle中称其为Cache Buffer Chains，简称CBC链。MySQL中没有专门的称呼，也称为CBC链吧，反正和Oracle都是一样的东西。

MySQL中，链表中的每个Node，都是buf\_page\_t型的类，它的定义在storage/innobase/include/buf0buf.h的1156行，这个就比较复杂了，如果不准备入调试技术的坑，只是看个热闹，我就不建议打开看了。它主要包含文件号、页号、下一个Node的地址、Buffer地址等成员变量。

数据库在HASH Table中搜索Buffer Pool中某个Buffer的过程，我们就不详细说了，有过很多资料讲述这一过程。MySQL中的相关代码在torage/innobase/include/buf0buf.ic的948行buf\_page\_hash\_get\_low函数中，代码量并不算复杂，读一下可以使你更拉轰。Oracle中参考《Oracle内核技术揭密》吧，第三章专门讲这个。

无论MySQL、Oracle，在HASH Table中搜索Buffer的过程都需要锁的保护。Oracle中称这个锁为Cache Buffer Chains Latch，简称CBC Latch，MySQL中也没有专门的称呼，我们也称它为MySQL的CBC Latch吧。

好了，基础知识介绍完毕。下面进入本次分享的最主要内容了，近距离分析MySQL、Oracle的异同。下面先从CBC Latch的数量开始。

第二节  
MySQL CBC Latch数量造成的竞争

Oracle中CBC Latch数量是很多的（由隐藏参数“\_db\_block\_hash\_buckets”计算得到），缺省情况下根据你所设定的Buffer cache大小设定，至少几千个。Buffer Cache越大，CBC Latch数量也越多。

MySQL中呢，CBC Latch的数量就少的可怜了，16个（准确来说，是一个Buffer Pool Instance 16个。一个Buffer Pool Instance就是一个Buffer Pool的子池）。而且，这个数字并不会随着Buffer Pool加大而增大。再大的Buffer Pool，它也是16个。

我猜测，可能是MySQL逻辑读时都是以共享方式持有CBC Latch。因此，MySQL的开发者认为CBC Latch的竞争不会太激烈，所以CBC Latch数量不必太多。  
虽然逻辑读都是以共享的模式持有CBC Latch，但物理读可就要以独占方式持有CBC Latch了。如果只有16个CBC Latch的话，可以想像，在物理I/O比较多时，竞争一定是激烈的。

做个测试，验证一下逻辑读、物理读时CBC Latch的阻塞情况。测试非常简单，因为CBC Latch锁只有16个，只要找17个页，这17个页中，必然有两个页共用一个CBC Latch。

我使用下面的存储过程，向一个表中插入了50行。每一行都占6000字以上，50行数据，使用的页数量绝对在17个以上，一定有两个页用同一个CBC Latch保护。

可以得到T1表所有页的HASH值，HASH值相同的，就是使用同一个CBC Latch的页。在我的测试环境，显示的结果id1为1行所在页，和id1为34的行所在页，共用一个CBC Latch，测试过程如下图：

简单说一下这个测试，先开始一个逻辑读（查询多次id1=1的页），使用gdb，当线程获得id1=1页上的CBC Latch后停住（也就是在执行完pfs\_rw\_lock\_s\_lock\_func()函数后停住）。

接下来再开启一个连接，查询id1=34的行。Id1=34的行所在页之前没被读过，它是一次物理读，它会被前面的逻辑读阻塞。然后我们可以慢慢观察MySQL出现CBC Latch阻塞后的情况。首先查看show processlist中的状态：

红框中的Session，正在执行id1=34的查询，它已经被阻塞了。但你无法得知它被阻塞，它的状态还是Query和Statistics。Oracle中可不是这样，你可以在v$session中看到Session的准确状态，是WAITING、WAITED SHORT TIME和ACTIVE、INACTIVE。

MySQL中除了show processlist，还可以在show engine innodb status中看到更多信息：

在“show engine innodb status”中可以看到CBC Latch出现阻塞。上图红框中的信息说明有一个线程已经被阻塞了，它在等待X-LOCK锁。

但是show engine innodb status的结果不是标准的二维表，而且这里只显示有阻塞，阻塞的次数、时间都不知道。而且CBC Latch的持有时间都是很快的，多了一小会儿竞争时间，你也感觉不到。可能为让一次毫秒级耗时的逻辑读，增加几十、或几百微秒。

Oracle中可以SQL统计计算CBC Latch被调用的总次数、阻塞次数、阻塞时间、……等等信息。通过这些信息，美创运维中心中有经验的DBA一眼就可以看出来，CBC Latch是否竞争过于激烈。

我们还回到CBC Latch的数量问题上来。我们可以得到一点结论了，在MySQL中，因为只有16个CBC Latch锁，平均17个页会有两个页共用一个CBC Latch锁，也就是说平均17次物理读，会有一次CBC Latch竞争。

我们展开说一下，MySQL其实不是SSD友好型数据库。SSD大大提高了物理读的速度，但由于CBC Latch数量过少，在出现物理读时，较容易引发阻塞。

而且你很难察觉这些阻塞。它只是多占了点CPU、让你本来可以更快的SQL，慢了一点。至于慢了多少就很难说了，我粗略的说一下，物理读稍多的情况下有大概5%到10%的性能损耗。有没有可能解决这种问题呢？

当然有，就是增加CBC Latch的数量。当你使用了SSD、特别是高性能的pci-e槽的SSD时，一定要记得增加CBC Latch的数量。

MySQL使用参数“innodb\_page\_hash\_locks”控制CBC Latch的数量，该参数的缺省值是16。修改它也是很简单的事。但是，等等，为什么从来没听人说过要修改这个参数呢？网上搜索了一下，也鲜有针对这个参数的介绍：

你可以换种搜索引擎，反正我用某度搜索“innodb\_page\_hash\_locks”，啥都没搜到。并不是它不重要，刚才我们说了，是它造成的竞争很难察觉，大家不知道而已。

那么这个参数具体应该设为什么值呢？这要结合你的系统、主机CPU/内存等来确定。也可以参照Oracle中CBC Latch的数量，这可是经过了无数大厂验证过的值。或者，我建议你找美创科技的专业运维团队，为你的系统进行健康检查后，再决定是否需要修改、如何修改此参数的值。

如果你的Oracle数据库，遇到了CBC Latch或其他情况的竞争，当然也可以找我们，记住一句口号：

“有情况，找美创”

接下来我们再来看看访问Buffer页时的情况。

第三节  
MySQL与Oracle的Buffer Pin Lock比较

搜索CBC链、找到目标Buffer之后，就要对页进行访问了。访问页也需要在锁的保护下进行，这个锁在Oracle中，称为Buffer Pin Lock，有兴趣了解Oracle的Buffer Pin Lock的话，可以看我的那本《Oracle内核技术揭密》第三章3.1.2小节。MySQL中的“Buffer Pin Lock”，限于篇幅，原理咱们就不展开介绍了。我们只说Buffer Pin Lock的相容、阻塞规则。

MySQL的Buffer Pin Lock规则很简单：  
①　读与读不阻塞。  
②　读与写互相阻塞。  
③　写与写互相阻塞。

读就是共享锁，写就是独占锁。Select就是读，DML就是写。上面的规则，其实就是，当两个线程同时操作同一页时：Select与DML互相阻塞、DML互相阻塞、Select互不阻塞。这种规则很容易理解。所有的数据库也都是这样，但是，Oracle不一样。

Oracle的规则是：  
①　读与读不阻塞。  
②　读不阻塞写。  
③　写阻塞读。  
④　写与写互相阻塞。  
看出来没，Oracle与MySQL的区别：“读不阻塞写”。

Oracle比MySQL向前走了一步，实现了读不阻塞写的Buffer Pin Lock。在读多写少的环境，读不阻塞写，可以有效的减少竞争。不像CBC Latch数量的问题，有一个“innodb\_page\_hash\_locks”参数可以简单的弥补MySQL在此点上的不足。Buffer Pin Lock的相容、阻塞规则，是无法被改变的。只能期待后续MySQL代码的进步。

第四节  
MySQL的棋高一着：共享的CBC Latch

当然，也不是所有地方Oracle都比MySQL棋高一着。有一个地方，MySQL还是有优势的。MySQL的CBC Latch只保护搜索HASH表：

如上图红色虚线框中的画，假设Bucker 2后的Node2是目标，在找到Node2后，MySQL将马上释放CBC Latch。Oracle不一样，如下图：

Oracle中在找到目标的Node 2后，并不马上释放CBC Latch，还要再修改Buffer Pin Lock的锁变量、设置Buffer Pin Lock锁，然后才能释放CBC Latch。（注：Oracle的Buffer Pin Lock锁就在图中的Node 2的内存中）

有看官说，“不就是多了个加个锁的操作吗，速度是很快的，Oracle的CBC Latch持有时间也不会比MySQL的长多少啊！”

话不能这么说，加锁虽然是耗时很短的操作，特别是Buffer Pin Lock这种“低级内存级锁”，也就是修改个内存标志位的事。但是，正是因为“修改”二字，导致Oracle的CBC Latch变成独占的了。所以，在Oracle 10G之前，CBC Latch没有模式，只有持有、不持有。因此只要持有CBC Latch，就是独占的，没有共享CBC Latch这一说。

但Oracle当然也非浪得虚名，在11G后，对CBC Latch、Buffer Pin Lock机制做了很大的调整。最大的改变发生在11.2.0.4后，在这个版本中，共享模式的CBC Latch得到了更为普及的使用。但总体上来说，独占模式的CBC Latch，还是比MySQL中要多。在这一点上，MySQL算是扳回一局。并不是所有地方，Oracle都比MySQL先进。Oracle的最低层内核，其实还有提升空间。

好了，为了照顾看热闹的群众，我并没有对MySQL、Oracle的部分原理做太过细致的剖析，省略了调试过程的细节。对调试技术、数据库源码有兴趣的朋友，请持续观注美创新运维新数据公众号，精彩持续中。

美创运维中心数据库服务团队拥有Oracle ACE 1人、Oracle OCM 10余人、Oracle OCP 40余人。著有《Oracle数据库性能优化方法论和最佳实践》、《Oracle DBA实战攻略》等书籍。目前运维数据库2000余套，精通Oracle、DB2、MYSQL、MSSQL、达梦等各项数据库技术，拥有丰富的处理实战经验。