为无为，事无事，味无味。

这个问题目前在项目上很少碰到，这次也是出于好奇拿来学习探讨，下面总结下这个问题出现的场景：1.代码中存在较大事务，超过 binlog_cache_size，高并发下生成大量临时文件，占满 tmpdir。2.代码在事务执行过程中碰到 tmpdir 磁盘已满错误，未处理异常执行回滚，后续执行 Commit 导致。3.代码在事务执行过程中碰到 tmpdir 磁盘已满错误，未处理异常执行回滚，继续执行碰到嵌套事务，引发 Commit 导致。也许很多童鞋想到可以加大binlog_cache_size。

2024年4月30日刚刚发布长期稳定版本MySQL 8.4 版本(LTS)，就在短短3个月内，就被发现重大bug，虽然一般不会超过8000张表，也不会触发这个bug。但是这也从另一面证明了新出来的软件版本不一定稳定，还是得有人去趟坑，社区活跃性很重要，线上生产环境不要轻易“吃螃蟹”尝鲜。

不过很不幸的是MySQL5.7频频被爆出Table cache 导致 MySQL 崩溃。不过可惜啊，就在今年2024年07月MySQL8.0、8.4、MySQL9.0都还爆出数据库下表的数量超过1万张表时，触发重大bug Crash。MySQL 8.0.38,MySQL 8.4.1,MySQL 9.0.0三个版本中被确认，这个问题在 >= 8.0.38 版本中存在，包括 8.4.1 和 9.0.0。

MySQL经常会遇到Too many open files，MySQL上的open_files_limit和OS层面上设置的open file limit有什么关系？源码中也会看到不同的数据结构，TABLE, TABLE_SHARE，跟表是什么关系？MySQL flush tables又做了些什么，接下来梳理一下。

除了字典元素本生，字典表本生也有自己的属性，比如字段/表名等等，这些属性都放到了字典表定义这个类里面，注意这也是单例，下面是和它有关的继承关系，只截取一部分。比如这里Tables类，里面就要字段的定义如下除了上面提到的字典元素的类，字典表属性的类，建立视图还有一个类，这里简单看看。

数据库优化有四个维度，分别是：硬件升级、系统配置、表结构设计、SQL语句及索引。那优化的成本和效果分别如下：优化成本：硬件升级>系统配置>表结构设计>SQL语句及索引。优化效果：硬件升级由上图可以看出性价比排名也是硬件升级。影响/复杂度：硬件升级>系统配置>表结构设计>SQL语句及索引。其中，硬件升级>系统配置>表结构设计 又统称为“架构设计”。

Optimizing SELECT Statements先看官网MySQL5.7和MySQL8.4。MySQL8.4增加了三条select优化，如上图红色方框选择的3条。

在故障排查过程中，我们需要了解mysql的状态信息，如：mysqld启动时长，连接数、QPS、TPS、读写比、DQL/DML执行次数、慢查询次数、锁等待、ibf命中率等等状态统计信息，以便于根据当前mysql运行状态进行分析判断和调整优化。需要查询这些运行状态信息可以通过下面三种方式：1. 使用命令行工具：可以通过在命令行中执行`mysqladmin status`命令来查看MySQL的状态。该命令会显示MySQL的版本信息、运行时间、并发连接数等。2. 使用MySQL客户端：可以通过登录MySQ

前面有提到SHOW [FULL] PROCESSLIST命令，在排查一些MySQL的问题，会经常用到 show processlist这个命令。show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

为了排查和定位MySQL故障，除了要上面的监控之外，还需要了解一些常用的故障排查命令，他们可以帮助我们了解数据库的运行情况，定位问题。在这里主要整理：SHOW FULL PROCESSLIST，SHOW STATUS、SHOW VARIABLES等。

科普文：软件架构数据库系列之【MySQL的Prometheus监控：MySQL Exporter】-优快云博客科普文：软件架构数据库系列之【MySQL5.7的系统表梳理】-优快云博客前面提到过MySQL的监控，监控数据都是来MySQL自带的系统表。对于系统和状态变量，变量的作用域（变量作用域）为全局、会话或两者兼而有之。有关设置和使用选项和变量的详细信息，请参阅官方文档：https://dev.mysql.com/doc/refman/8.4/en/server-option-variable-refe

关于numa和其他cpu架构，可以看看上面的文章。在数据库系统中，OLTP（在线事务处理）数据库通常面临着高并发和高吞吐量的要求。在某些情况下，可以通过启用 NUMA（非统一内存访问）来改善 OLTP 数据库的性能。NUMA 系统的内存分配策略与传统的对称多处理 (SMP) 系统不同。在 SMP 系统中，所有 CPU 核心都可以访问同一个共享的内存。而在 NUMA 系统中，内存访问被限制在特定的节点（或节点集合），这取决于 CPU 核心的物理位置。

CheckPoint是MySQL的WAL和Redolog的一个优化技术。‌‌是一种优化技术，主要用于将缓存池中的脏页刷新到磁盘，确保数据的持久性和一致性。CHECKPOINT机制通过记录一个特定的时间点（称为检查点），在该时间点上，所有在此之前的修改都会被刷新到磁盘上，从而在系统崩溃时能够快速恢复数据。

前面我们把innodb的存储结构进行了拆分，分别进行了描述，在上一篇“科普文：软件架构数据库系列之【MySQL/innodb刷脏页】” 的文档中对刷脏页的后台线程做了概叙，这里就对后台线程做一个补充。InnoDB 通过独立的线程将Buffer Pool中的脏页刷入存储中。这些线程称作Page Cleaner. Page Cleaner的线程数量通过系统参数--innodb-page-cleaners控制。刷脏是以Buffer Pool实例为单位进行的。一个Buffer Pool实例同时只能有一个Pa

从脏页数据的产生、到刷脏页数据到磁盘这个过程基本涵盖了MySQL/innodb的内存、磁盘、后台线程三大构件。了解这个过程，可以让我们更好的理解innodb引擎下数据库的运行原理，为提升数据库性能提供理论依据。因为整个过程涉及到的技术点很多，我们前面已经拆分了进行详细描述，所以在这里不再赘述，会在后面SQL执行过程、磁盘数据加载过程、加锁过程、内存数据刷新过程中再统一描述。总之，InnoDB通过刷脏页的机制来保证数据的一致性/持久性和提高数据库性能。了解脏页的概念和刷脏策略对于数据库管理员和开发人员

FLUSH TABLES是 MySQL 的一个命令，用于关闭所有打开的表、清除表缓存并创建新的表缓存。这个命令在执行期间不允许对表进行修改，因为它会关闭所有打开的表，从而使得当前所有的数据库操作无法进行。如果你需要进行表的修改，应该在执行FLUSH TABLES命令之前完成你的修改工作。如果你在执行FLUSH TABLES时需要进行修改，你可以将修改操作在执行FLUSH TABLES命令前执行。

‌InnoDB的Buffer Pool具有以下四大特性‌：‌Change Buffer‌：​ Change Buffer是InnoDB存储引擎中的一个特殊数据结构，用于缓存对不在buffer pool中的二级索引页所做的修改。这些修改可能来自INSERT、UPDATE或DELETE操作（DML）。当这些页被其他读操作加载到buffer pool时，Change Buffer中的修改会被合并到这些页中，从而减少对磁盘的随机I/O操作‌。‌Double Write‌：Double Write机制用于确保I

innodb内存管理四剑客：LRU算法+Free_list、LRU_list、Flush List。在前面梳理innodb的内存结构和磁盘结构时，关于innodb的内存管理都是做了描述。今天单独把这一部分拿出来，也是为了更好的理解innodb的内存结构及其运行机制，为后面的SQL语句执行过程，磁盘数据如何加载到内存、内存的脏数据如何刷新到磁盘做准备。

MySQL使用采样统计的方法，会选出N个数据页，每个数据页大小16kb，接着统计选出来的数据页上的不同值就会得到一个平均值，用平均值在乘以索引的页面数得到的结果就是这个索引的基数。但别误解force index的使用方法，之前在代码中看到这样一个案例，给查询列使用了函数操作导致使用不上索引，然后这哥们就直接使用force index，肯定不行的哈！优化器选择了错误的索引，只用force index来快速矫正，再通过优化SQL语句来引导优化器选择正确的索引，最暴力的手法是直接删除误选的索引。

页是InnoDB存取数据的基本单位，默认页大小是16KB，InnoDB为了不同的目的设计了很多不同类型的页，本文重点分析了存放用户记录的索引页。页的头尾部分File Header和File Trailer是所有页都有的一个通用结构，它们记录了页的一些通用状态信息，和Checksum用来验证页的完整性。Page Header是索引页特有的结构，它记录了页内用户记录相关的状态信息。User Records部分用来存放用户记录。

了解 MySQL 的 ROW_FORMAT 默认值及其影响对于优化数据库性能和存储至关重要。选择合适的 ROW_FORMAT 可以提高查询效率，减少存储需求，并根据具体需求灵活调整。通过本文的代码示例和图表，希望读者能够更深入地理解 ROW_FORMAT 的作用和选择依据。在实际应用中，建议根据数据的特点和查询需求，合理选择 ROW_FORMAT。同时，定期监控和评估数据库性能，以确保数据存储和查询的最优化。

c. 假设某个字符集中最多需要W个字节表示一个字符，该列允许存储最多M个字符，实际占用字节数为L，如果该变长字段允许存储的最大字节数（M * W）超过255个字节，并且真实占用的字节数L 超过127个字节，则使用2 字节来表示真实数据占用的字节数，否则用一个字节。的方式记录长度的（涉及大端、小端存储问题），如03 02 01代表的是第一个变长字段长度为1，第二个变长字段长度为2，第三个变长字段长度为3.至于变长字段怎么确定的序列，是根据创建的先后来规定的，即第一个创建的变长字段为第一个变长字段。

我们先看一下官网，从目录可以看到”MySQL 8.4 Reference Manual / Optimization / Optimization and Indexes“索引放在优化目录下面，也就是索引是用来提升性能的。MySQL 选择B+树作为索引结构的主要原因是B+树的以下特性；检查索引列是否合理

Index Merge是通过同时扫描多个索引，再将数据合并到一起的访问方式。只适用于单表有多个索引可选的情况，不支持用多表场景。合并数据的种类有：unions，intersections。Inerge-Merge的使用限制： ● 如果where条件中有复杂的AND/OR嵌套，可能选不中较优的计划。

PolarDB 在线上支持了非常多的大表实例, 10+TB 的大表其实非常多, 我也看到之前很多大厂 DBA 朋友的实际分享, 比如微博6B(billion) 哥, 讲述微博的某一张单表 60 亿行数据等等, NineData 创始人斗佛公众号大圣聊数据库讲述海外类似微信业务单表几十亿都是运行的挺好的. 所以其实如果业务表结构设计合理, 其实大表是完全没问题的, 不用被现在的数据库厂商强行引导.其实 Btree 是一个非常扁平的 Tree, 绝大部分 Btree 不超过 4 层的, 我们看一下实际情况。

InnoDB的read-ahead，在触发的时候，针对下一个extent，对每一个page提交了异步IO请求，也就是增加了IO request次数，虽然Native AIO和disk会有针对性合并IO，但仍然非常有限，而Oracle每次提交合并多个连续数据块的IO请求，能够更好利用disk的吞吐能力。在高并发的场景下，sql响应时间主要取决于同步IO请求的时间，而InnoDB的预读通常不会触发，就算触发，更多的是预热(warmup)的效果，并不会对系统带来非常大的收益，对rt的影响也非常小。

在前面几期月报我们介绍了undo log、redo log以及InnoDB如何崩溃恢复来实现数据ACID的相关知识。本期我们介绍另外一种重要的数据变更日志，也就是InnoDB change buffer。Change buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。

MySQL · 引擎特性 · InnoDB Adaptive hash index介绍 · 数据库内核月报 · 看云我们知道InnoDB的索引组织结构为Btree。通常情况下，我们需要根据查询条件，从根节点开始寻路到叶子节点，找到满足条件的记录。为了减少寻路开销，InnoDB本身做了几点优化。首先，对于连续记录扫描，InnoDB在满足比较严格的条件时采用row cache的方式连续读取8条记录（并将记录格式转换成MySQL Format），存储在线程私有的中；

预读（Prefetching）应用场景：数据库：数据库管理系统（如PostgreSQL, MySQL）经常使用预读来加速查询过程。高性能文件服务器：服务器应用程序预读大文件，以便客户端请求可以快速响应。预读(Read-Ahead)是InnoDB预估执行当前的请求可能之后会读取某些数据页，就预先把它们加载到 Buffer Pool中。预读和数据页访问机制、缓冲池刷新策略息息相关。Innodb预读前提：InnoDB Buffer Pool足够大（重点参考‌InnoDB Buffer Pool的命中率>99

InnoDB的Buffer Pool是其存储引擎中非常重要的一个组件,它的主要功能是缓存数据和索引页,以减少磁盘I/O操作,从而提高查询性能。‌InnoDB的Buffer Pool具有以下四大特性‌：Change Buffer、Double Write、Adaptive Hash Index、Read Ahead

【摘要】本文介绍了农商行在新核心系统采用分布式数据库的探索，包括需求分析、选型、架构设计，重点介绍了在此过程中遇到的难点和调优的经验，是值得参考的一手实践分享。

innodb通过日志文件（redo log 和 undo log）、锁技术以及MVCC（多版本并发控制）来实现事务的持久性、原子性、一致性和隔离性。

事务、隔离级别、并发之间的关系。RR和RC两种隔离级别下，innodb如何解决幻读、不可重复读、死锁等并发问题；为何MySQL默认隔离级别是RR，而很多大厂都选择RC？

MySQL数据库三高高并发：同时处理的事务数高高性能：事务/SQL的执行速度高高可用：系统可用的时间高MySQL数据库如何实现三高高并发：通过复制和扩展，将数据分散至多个节点高性能：复制提升速度，扩展提升容量高可用：节点间身份切换保证随时可用要实现数据的三高，其实是和整个软件架构的三高设计是密不可分的。这里只是拆出来单独梳理一下。MySQL数据库实现三高的手段

MySQL 锁原理通过 6 个死锁案例，让你彻底理解 MySQL 锁机制，死锁的原因！两个事务发生死锁时，MySQL Server会选择一个事务释放锁并进行rollback.

MySQL中的间隙锁主要用于解决幻读问题，它可以锁定一个范围，但不包括范围内的实际数据，从而避免了幻读。解决幻读问题的关键是要在事务中使用正确的隔离级别，并且在查询时使用合适的锁定策略。

关于死锁，确切的说是innodb引擎表的死锁，在我们梳理的场景下，基本都是“行锁”导致的，而且隔离级别大多都是RR。案例剖析，MySQL唯一索引并发插入导致死锁其实就是Gap Lock间隙锁导致的死锁。

数据事务设计遵循ACID的原则。MySQL数据库提供了四种默认的隔离级别，读未提交()、读已提交(或不可重复读)()、可重复读()、串行化(MySQL的默认隔离级别是RR。除了以上给出的几种死锁模式，还有很多其他死锁的场景。无论是哪种场景，万变不离其宗，都是由于某个区间上或者某一个记录上可以同时持有锁，例如不同事务在同一个间隙gap上的锁不冲突；不同事务中，S锁可以阻塞X锁的获取，但是不会阻塞另一个事务获取该S锁。这样才会出现两个事务同时持有锁，并互相等待，最终导致死锁。

关于死锁，确切的说是innodb引擎表的死锁，在我们梳理的场景下，基本都是“行锁”导致的，而且隔离级别大多都是RR。Tx1和Tx2两个事务并行insert相同唯一索引的数据，导致先执行insert的Tx1获得了排他锁，Tx2等待获得共享锁；在Tx1释放排他锁的时候，Tx2拿到了共享间隙锁，但此时另一个事务Tx3请求该行的排他锁，被阻塞；之后Tx2也去请求该行的排他锁，至此，形成了Tx2和Tx3组成的环形等待，形成死锁

Gap Lock间隙锁导致的死锁。并发事务，间隙锁可能互斥(1)A删除不存在的记录，获取共享间隙锁；(2)B插入，必须获得排他间隙锁，故互斥；并发插入相同记录，可能死锁(某一个回滚)并发插入，可能出现间隙锁死锁(难排查)可以查看InnoDB的锁情况，也可以调试死锁。