Innodb

原文：https://nullwy.me/2023/07/innodb-locking/如果感觉我的文章对你有用，请随便赞叹目前支流数据库事务的并发管制实现，如 MySQL InnoDB、PostgreSQL、Oracle，都应用两阶段封闭 2PL 与 MVCC 技术，但具体实现细节上存在差别。InnoDB 是在以封闭技术为主体的状况下，用 MVCC 技术辅助实现读-写、写-读操作的并发。PostgreSQL 的并发控制技术是以 MVCC 技术为主，封闭技术为辅。本文次要关注 InnoDB 事务的并发管制实现。 背景常识并发管制，是数据库系统的 ACID 个性中的隔离性（Isolation)）的保障。所谓隔离性，就是事务的执行不应受到其余并发执行事务的烦扰，事务的执行看上去应与其余事务是隔离的。被隔离的执行，等价于事务的某种串行执行，或者说，它等价于一个没有并发的执行。保障串行性可能只容许极小的并发度，采纳较弱隔离性，能带来更高的并发度，是并发事务的正确性和性能之间的斗争。 晚期各大数据库厂商实现并发管制时多采纳基于封闭的并发控制技术，所以在基于封闭的技术背景下，才在 ANSI SQL-92 规范中提出了四种隔离级别：未提交读（Read Uncommitted）、己提交读（Read Committed）、可反复读（Repeatable Read）、可串行化（Serializable）（附注：为了书写简便本文将各个隔离级别顺次缩写为 RU、RC、RR、SER）。ANSI SQL-92 规范的四种隔离级别，是依据三种读异常现象（phenomena）定义的，隔离级别和异常现象的关系如下： 隔离级别P1 脏读P2 不可反复读P4 幻读Read Uncommitted可能可能可能Read Committed防止可能可能Repeatable Read防止防止可能Serializable防止防止防止ANSI SQL-92 规范文档对三种读异常现象的定义原文如下 [ref]： The isolation level specifies the kind of phenomena that can occur during the execution of concurrent SQL-transactions. The following phenomena are possible:1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.除了脏读、不可反复读和幻读这 3 种读数据异样外，还有写数据异样，即脏写和失落更新。各个异样的含意如下： ...

1 InnoDB存储引擎InnoDB存储引擎最早由Innobase Oy公司开发（属第三方存储引擎）。从MySQL 5.5版本开始作为表的默认存储引擎。该存储引擎是第一个残缺反对ACID事务的MySQL存储引擎，特点是行锁设计、反对MVCC、反对外键、提供一致性非锁定读，非常适合OLTP场景的利用应用。目前也是利用最宽泛的存储引擎。 InnoDB存储引擎架构蕴含内存构造和磁盘构造两大部分，总体架构图如下： 8.0版本： []() 5.5版本： []() 2 InnoDB 存储构造2.1 磁盘构造2.1.1 表空间 TablespacesInnoDB存储引擎的逻辑存储构造是将所有的数据都被逻辑地放在了一个空间中，这个空间中的文件就是理论存在的物理文件（.ibd文件），即表空间。默认状况下，一个数据库表占用一个表空间，表空间能够看做是InnoDB存储引擎逻辑构造的最高层，所以的数据都寄存在表空间中，例如：表对应的数据、索引、insert buffer bitmap undo信息、insert buffer 索引页、double write buffer files 等都是放在共享表空间中的。 表空间分为零碎表空间(ibdata1文件)(共享表空间)、长期表空间、惯例表空间、Undo表空间和file-per-table表空间(独立表空间)。零碎表空间又包含双写缓冲区(Doublewrite buffer)、Change Buffer等 1.零碎表空间 System Tablespace 零碎表空间能够对应文件系统上一个或多个理论的文件，默认状况下， InnoDB会在数据目录下创立一个名为.ibdata1，大小为 12M的文件，这个文件就是对应的零碎表空间在文件系统上的示意。这个文件是能够自扩大的，当不够用的时候它会本人减少文件大小。须要留神的一点是，在一个MySQL服务器中，零碎表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，咱们表中的数据都会被默认存储到这个零碎表空间。 show variables like '%innodb_data_file_path%'[]() 2.独立表空间 在MySQL5.6.6以及之后的版本中， InnoDB并不会默认的把各个表的数据存储到零碎表空间中，而是为每一个表建设一个独立表空间，也就是说咱们创立了多少个表，就有多少个独立表空间。应用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创立一个示意该独立表空间的文件，文件名和表名雷同，只不过增加了一个.ibd的扩展名而已。 show variables like '%innodb_file_per_table%'[]() 独立表空间只是存放数据、索引和插入缓冲Bitmap页，其余类的数据如回滚（undo）信息、插入缓冲索引页、零碎事务信息、二次写缓冲等还是寄存在原来的零碎表空间。 3.其余类型的表空间 随着MySQL的倒退，除了上述两种表空间之外，当初还新提出了一些不同类型的表空间，比方通用表空间 (general tablespace)、undo表空间(undo tablespace)、长期表空间(temporary tablespace)等 4.表空间结构 表空间又由段(segment)、区( extent)、页(page)组成，页是InnoDB磁盘治理的最小单位。在咱们执行sql时，不论是查问还是批改，mysql 总会把数据从磁盘读取内内存中，而且在读取数据时，不会独自加在一条数据，而是间接加载数据所在的数据页到内存中。表空间实质上就是一个寄存各种页的页面池。 []() 「页」是InnoDB治理存储空间的根本单位，也是内存和磁盘交互的根本单位。也就是说，哪怕你须要1字节的数据，InnoDB也会读取整个页的数据，InnoDB有很多类型的页，它们的用途也各不相同。比方：有寄存undo日志的页、有寄存INODE信息的页、有寄存Change Buffer信息的页、寄存用户记录数据的页（索引页）等等。 InnoDB默认的页大小是16KB，在初始化表空间之前能够在配置文件中进行配置，一旦数据库初始化实现就不可再变更了。 SHOW VARIABLES LIKE 'innodb_page_size'[]() 2.1.2 重写日志 redo log文件redo log记录数据库的变更，数据库解体后，会从redo log获取事务信息，进行零碎复原。redo log在磁盘上体现为ib_logfile0和ib_logfile1两个文件。MySQL会在事务的提交前将redo日志刷新回磁盘。 ...

作者：刘开洋 爱可生交付服务团队北京 DBA，对数据库及周边技术有浓重的学习趣味，喜爱看书，谋求技术。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 问题共事在客户那里解决问题时发现一个报错，出于趣味就进行了钻研： Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions,and never in the same statement as updates to transactional tables. 报错的意思是指业务端下发的事务违反了 GTID 的一致性。 起因是，非事务表和事务表在一条事务里进行了更新，MySQL 是不容许咱们这么做的，但业务非要胳膊掰大腿，怎么办？ 官网阐明： Only statements that can be logged using GTID safe statements can be logged when enforce_gtid_consistency is set to ON,so the operations listed here cannot be used with this option: ...

问题：MySQL 官网文档说 innodb_log_buffer_size 是 redo log 的写缓存，设置大一点能缩小写操作。 咱们用试验来学习一下这个参数的作用。 试验：宽油起个数据库： 先将 innodb_log_buffer_size 设置为最小值。 接下来，咱们先建一个有数据的表，建表的办法参考试验 11： 反复执行最初一句 insert，直到表中有 65536 行数据： 接下来咱们复制一张表，因为咱们要做两次试验： 接下来咱们开启 innodb metrics 和 performance_schema： 执行一次 insert，计算一下 LSN 的差值，也就是这个事务产生了多少 redo log： 能够看到这个事务大略产生了 3M 左右的 redo log。 再看一下这个过程产生了多少 IO： 能够看到产生了 7 次 IO，6 次 write 和 1 次 sync，每次 write 大略须要 0.8ms。 当初咱们变更一下 my.cnf，将 innodb_log_buffer_size 变更为默认值 16777216，重启数据库。此处省略步骤, 只看后果： 因为重启了一次数据库，记得再开启一次 innodb metrics 和 performance_schema： ...

作者：郭斌斌爱可生 DBA 团队成员，负责我的项目日常问题解决及公司平台问题排查。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 对于主从复制，目前用的比拟广泛的是 RBR（Row-base replication） 这种形式，有这么一个问题大家能够一起思考一下。 问题：RBR 主从复制，主库表 A 有两个字段 (c1 int,c2 int)，从库表 A 有一个字段 （c1 int），主库对表 A 的 c2 字段执行插入或者更新操作，主从复制是否会受影响？ 论断：论断是复制不受影响。 验证（版本 MySQL 5.7.25）：主库 t1 表构造和数据 从库 t1 表构造和数据 从库复制状态： 主库执行 insert 操作： 从库查看复制状态和同步的后果： 主库执行 update 操作 从库查看复制状态，并查看 t1 数据 起因：对于主从复制，主从同步的表构造其实是能够不统一的，主库表的列数能够比从库表的多，也能够比从库表的少，然而这种场景对于复制无影响也是有条件的。 条件：1、雷同列的定义程序必须完全一致。 2、雷同列的定义肯定在在差别列之前。 3、差别的列必须要有默认值。 参考链接：https://dev.mysql.com/doc/ref...

本篇次要介绍 MySQL 的函数索引（也叫表达式索引）。 通常来讲，索引都是基于字段自身或者字段前缀（第 20 篇），而函数索引是基于字段自身加上函数、操作符、表达式等计算而来。如果将表达式或者操作符也看做函数的话，简略来说，这样的索引就能够统称函数索引。 MySQL 的函数索引外部是基于虚构列（generated columns）实现，不同于间接定义虚构列，函数索引主动创立的虚构列自身实时计算结果，并不存储数据，只把函数索引自身存在磁盘上。 MySQL 8.0.13 之前不反对函数索引，所以老版本包含当初支流的 MySQL 5.7 也不反对函数索引，须要手工模仿创立或者改 SQL。 本章基于以下几点来讲函数索引： 1.函数索引的应用场景函数索引最最经典的应用场景莫过于就是对日期的解决，特地是表中只定义了一个字段，前期对这个字段的查问都是基于局部后果。比方 “2100-02-02 08:09:09.123972” 蕴含了日期 “2100-02-02”，工夫 “08:09:09”，小数位工夫 “123972”，有可能会对这个值拆解后局部查问。 举个简略例子，表 t1 有两个字段，一个主键，另外一个工夫字段，总记录数不到 40W。 <localhost|mysql>show create table t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `log_time` datetime(6) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_log_time` (`log_time`)) ENGINE=InnoDB AUTO_INCREMENT=524268 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)<localhost|mysql>select count(*) from t1;+----------+| count(*) |+----------+| 393216 |+----------+1 row in set (0.07 sec)执行上面这条 SQL 1，把日期独自拿进去，执行了 0.09 秒。 ...

innodb存储引擎应用B+树实现索引的B+树不同于B树的规定：1、数据或者数据的指针只寄存在叶子节点，非叶子节点只存储关键字2、叶子节点中关键字由小到大排列，左叶子开端数据会保留右叶子开始数据的指针；所以，B+树的长处：1、因为非叶子节点只存储关键字，能够寄存的关键字大大增加，因而树的层级更少2、查问任何一个数据都须要从叶子节点获取数据的地址，因而每次数据的查问次数完全一致，因而查问的速度更稳固3、叶子节点人造就是有序链表4、全表遍历时，只须要遍历叶子节点链表即可，不须要像B树一样须要遍历每一层，性能更好当然B树也有B+树所不具备的有点：如果要查问的某条数据离树的根节点更近，这时查问速度比拟B+树更快

这里次要介绍 MySQL 的前缀索引。从名字上来看，前缀索引就是指索引的前缀，当然这个索引的存储构造不能是 HASH，HASH 不反对前缀索引。 先看上面这两行示例数据： 你是中国人吗？是的是的是的是的是的是的是的是的是的是的确定是中国人？是的是的是的是的是的是的是的是的是的是的这两行数据有一个独特的特点就是后面几个字符不同，前面的所有字符内容都一样。那面对这样的数据，咱们该如何建设索引呢？ 大抵有以下 3 种办法： 拿整个串的数据来做索引这种办法来的最简略直观，然而会造成索引空间极大的节约。反复值太多，进而索引中无用数据太多，无论写入或者读取都产生极大资源耗费。 将字符拆开，将一部分做索引把数据后面几个字符和残余的局部字符分拆为两个字段 r1_prefix，r1_other，针对字段 r1_prefix 建设索引。如果排除掉表构造更改这块影响，那这种办法无疑是最好的。 把后面 6 个字符截取进去的子串做一个索引是否不拆分字段，又能防止太多反复值的冗余？咱们明天讨论一下前缀索引。 前缀索引前缀索引就是基于原始索引字段，截取后面指定的字符个数或者字节数来做的索引。 MySQL 基本上大部分存储引擎都反对前缀索引，目前只有字符类型或者二进制类型的字段能够建设前缀索引。比方：CHAR/VARCHAR、TEXT/BLOB、BINARY/VARBINARY。 字符类型基于前缀字符长度，f1(10) 指的前 10 个字符；二进制类型基于字节大小，f1(10) 指的是前 10 个字节长度；TEXT/BLOB 类型只反对前缀索引，不反对整个字段建索引。举个简略例子，表 t1 有两个字段，针对字段 r1 有两个索引，一个是基于字段 r1 的一般二级索引，另外一个是基于字段r1的前缀索引。 10px;">`<localhost|mysql>show create table t1\G*************************** 1\. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `r1` varchar(300) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_r1` (`r1`), KEY `idx_r1_p` (`r1`(6))) ENGINE=InnoDB AUTO_INCREMENT=32755 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)<localhost|mysql>select count(*) from t1;+----------+| count(*) |+----------+| 24576 |+----------+1 row in set (0.04 sec)上面别离是表 t1 只有 idx_r1 和 idx_r1_p 的表空间文件大小，很显著，前缀索引十分有劣势。 ...

这里讲述 MySQL 哈希索引的实现形式以及应用场景。哈希表在 MySQL 里有如下利用： 各种存储引擎的哈希索引存储。MySQL 的 Memory，NDB 都有哈希索引的具体实现；MySQL 外部生成的哈希表。比方：数据在 innodb buffer pool 里的疾速查找；子查问的物化主动加哈希索引；JOIN KEY 无 INDEX 下的 HASH JOIN 等。一、哈希数据分布哈希索引显式利用次要存在于内存表，也就是 Memory 引擎，或者是 MySQL 8.0 的 Temptable 引擎。本篇的内容上都是基于内存表，MySQL 内存表的大小由参数 max_heap_table_size 来管制，其中蕴含了表数据，索引数据等。 举个例子，表 t1 有六行记录，主键哈希索引。 # MySQL 内存表主键默认哈希索引mysql> create table t1(id int , name varchar(64), gender char(1), status char(2),primary key (id)) engine memory;Query OK, 0 rows affected (0.02 sec)mysql> insert into t1 values(101,'张三','男','未婚');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(102,'小明','男','已婚');Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(103,'李四','男','未婚');Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(104,'李庆','女','已婚');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(105,'杨阳','女','未婚');Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(106,'余欢水','男','二婚');Query OK, 1 row affected (0.01 sec)我简略画了张主键哈希索引的分布图，见图 1： ...

MySQL 的默认索引构造是 B+ 树，也能够指定索引构造为 HASH 或者 R 树等其余构造来适应不同的检索需要。这里咱们来介绍 MySQL 哈希索引。 MySQL 哈希索引又基于哈希表（散列表）来实现，所以理解什么是哈希表对 MySQL 哈希索引的了解至关重要。接下来，咱们来一步一部介绍哈希表。 1. 数组数组是最罕用的数据结构，是一种线性表的顺序存储形式，由下标（也叫索引）和对应的值形成。数组在各个开发语言以及数据库中都有相似的构造，相似下图1： 图 1 展现了一个一维整数数组，数组的长度为 10，下标从 0-9， 每个下标对应不同的值。每种编程语言基本上都有数组，大部分数据库也提供了数组或者是相似数组的构造，MySQL 也有数组，以下为 MySQL 的一维数组： mysql> select @a as "array",json_length(@a) as "array_size";+-------------------------------------------+------------+| array | array_size |+-------------------------------------------+------------+| [10, 20, 30, 40, 50, 60, 70, 80, 90, 100] | 10 |+-------------------------------------------+------------+1 row in set (0.00 sec)数组元素也能够是数组，这样的示意称为多维数组，如图 2，一个二维字符串数组： 以下为 MySQL 里的多维数组： mysql> select json_pretty(@a)\G*************************** 1. row ***************************json_pretty(@a): [ [ "mysql", "db2" ], [ "oracle", "mongodb", "sql server", "redis" ], [ "memcached", "dble", "postgresql", "mariadb" ]]1 row in set (0.01 sec)数组优缺点如下，长处： ...

从数据结构角度1：B+树索引（o(logn)2：hash索引 (key=>value)3：全文索引4：R-tree索引物理角度1：汇集索引2：飞汇集索引逻辑角度1：主键索引2：一般索引3：惟一索引为什么会抉择B+树数据结构来存储数据在B+树中，非叶子节点上是不存数据的，仅存储键值。因为在数据中的页大小是固定的，Innodb中页的默认大小是16kb，如果不存储数据，那么节点就能够存储更多的键值，相应的树的节数更大，对于同样的数据量来说，须要的树高就会变低。如此一来查问数据的时候进行磁盘的IO次数就会缩小，晋升查问效率。因为B+树的节数等于键值数量，假如B+树的一个结点能够存储1000个键值，那么3层的B+树能够存储100010001000=10亿个数据。并且个别节点是常驻内存的，所以查找10亿个数据，只有两次IOB+树的叶子节点中的索引数据是依照程序排序的，并且叶子节点间是通过双向链表进行链接 主键索引和一般索引的差异主键索引 主键索引的叶子节点存的整行数据，主键索引也称为聚簇索引非主键索引 非主键索引的叶子节点内容存在的主键的值，非主键索引也成为二级索引或是一般索引基于主键索引和一般索引的查问有什么区别？主键索引，索引查问进去间接就是查问的值非主键索引，先搜寻查到主键索引，而后主键索引在搜寻一次。这个过程成为回表

1：InnoDb的四个个性：一致性、原子性、持久性、隔离性2：事物的隔离级别：可反复读、读已提交、读未提交、串行化3：查看以后的事物隔离级别 show varibles like "transaction-isolation'4：幻读：幻读是一个事物在事物开始中，两次select查问的后果不始终， 这产生的幻读。幻读存在的隔离级别有 R-U R-C R-R 。5：幻读的解决办法：在RR的隔离级别下，咱们对记录手动加上X锁的办法打消幻读（在Innodb的行锁锁定的是这个索引，估在记录实体存在与否没有关系，存在就在X锁，不存在就加 next-key lock/间隙锁），其余事物则无奈插入此索引记录，防止幻读）5：共享锁（shared）：又称为S锁，读锁。一个事物对数据对象O记上S锁，能够对O进行读取操作，然而不能进行更新操作。加锁期间其余事物能对o加s锁，然而能加X锁6：排他锁（Exclusive）：又称为X锁，写锁。一个事物对数据0加上X锁，就能够对O进行读取和更新。加上期间其余事物不能对0加任何锁。 参考文档：https://juejin.cn/post/684490...https://segmentfault.com/a/11...

上一章讲了数据库基本上都用 B+ 树来存储索引的起因：适宜磁盘存储，可能充分利用多叉均衡树的个性，磁盘预读，并且很好的反对等值，范畴，程序扫描等。这篇次要介绍 MySQL 两种罕用引擎，MyISAM 和 InnoDB 的索引组织形式，理解这些存储形式，对数据库优化很有帮忙。 MySQL 的索引依照存储形式分为两类：汇集索引：也称 Clustered Index。是指关系表记录的物理程序与索引的逻辑程序雷同。因为一张表只能依照一种物理程序寄存，一张表最多也只能存在一个汇集索引。与非汇集索引相比，汇集索引有着更快的检索速度。 MySQL 里只有 INNODB 表反对汇集索引，INNODB 表数据自身就是汇集索引，也就是常说 IOT，索引组织表。非叶子节点依照主键程序寄存，叶子节点寄存主键以及对应的行记录。所以对 INNODB 表进行全表程序扫描会十分快。 非汇集索引：也叫 Secondary Index。指的是非叶子节点依照索引的键值程序寄存，叶子节点寄存索引键值以及对应的主键键值。MySQL 里除了 INNODB 表主键外，其余的都是二级索引。MYISAM，memory 等引擎的表索引都是非汇集索引。简略点说，就是索引与行数据离开存储。一张表能够有多个二级索引。  MYISAM 表：MYISAM 表是典型的数据与索引拆散存储，主键和二级索引没有本质区别。比方在 MYISAM 表里主键、惟一索引是一样的，没有本质区别。 假如表 t1 为 MYISAM 引擎，列为 ID，姓名，性别，年龄，手机号码。其中 ID 为主键，年龄为二级索引。记录如下： 那对应的两个 B+ 树索引如下图所示， 主键字段索引树： 上图是一个 3 阶的 B+ 树，非叶子节点依照主键的值排序存储，叶子节点同样依照主键的值排序存储，并且蕴含指向磁盘上的物理数据行指针。  年龄字段索引树： 上图年龄字段索引树同样是一个 3 阶的 B+ 树，非叶子节点依照年龄字段的值顺序存储，叶子节点保留年龄字段的值以及指向磁盘上的物理数据行指针。 从下面两张图能够看出，MYISAM 表的索引存储形式最大的毛病没有依照物理数据行顺序存储，这样无论对主键的检索还是对二级索引的检索都须要进行二次排序。  举个简略例子演示下， 以下 SQL 1 默认没有排序，乱序输入；须要依照 ID 程序输入，就得用 SQL 2，显式加 ORDER BY 。 ...

ref重做日志double write

作者：宗杨爱可生产品交付团队成员，次要负责公司运维平台和数据库运维故障诊断。青睐数据库、容器等技术，喜好历史、追剧。 本文起源：原创投稿*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。一、事件背景咱们的单干客户，驻场人员报告说一个 RDS 实例呈现磁盘有余的告警，须要排查。 告警信息： 告警内容：数据库 data 磁盘有余，磁盘占用 80% 以上数据库 binlog 磁盘有余，磁盘占用 80% 以上 二、排查过程登陆告警的服务器，查看磁盘空间，并寻找大容量文件后，发现端口号为 4675 的实例长期表空间 ibtmp1 的大小有 955G，导致磁盘被应用了 86%； 猜想和库里执行长 SQL 有关系，产生了很多长期数据，并写入到长期表空间。      看到有这样一条 SQL，持续剖析它的执行打算； 很显著看到图中标记的这一点为应用了长期计算，阐明长期表空间的快速增长和它有关系。这条 SQL 进行了三表关联，每个表都有几十万行数据，三表关联并没有在 where 条件中设置关联字段，造成了笛卡尔积，所以会产生大量长期数据；而且都是全表扫描，加载的长期数据过多;还波及到排序产生了长期数据；这几方面导致 ibtmp1 空间疾速爆满。 三、解决办法和项目组沟通后，杀掉这个会话解决问题； 然而这个 SQL 停下来了，长期表空间中的长期数据没有开释； 最初通过重启 mysql 数据库，开释了长期表空间中的长期数据，这个只能通过重启开释。 四、剖析原理通过查看官网文档，官网是这么解释的： 翻译： 依据官网文档的解释，在失常敞开或初始化停止时，将删除长期表空间，并在每次启动服务器时从新创立。重启可能开释空间的起因在于失常敞开数据库，长期表空间就被删除了，重新启动后从新创立，也就是重启引发了长期表空间的重建，从新初始化，所以，重建后的大小为 12M。 从谬误日志里能够验证下面的观点： 五、官网对于 ibtmp1 大小的阐明 六、如何防止1. 对长期表空间的大小进行限度，容许主动增长，但最大容量有下限,本例中因为 innodb_temp_data_file_path 设置的主动增长，但未设下限，所以导致 ibtmp1 有 955G。正确办法配置参数 innodb_temp_data_file_path：[mysqld]innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M参考官网文档： ...

MySQL InnoDB 表数据页或者二级索引页（简称数据页或者索引页）的合并与决裂对 InnoDB 表整体性能影响很大；数据页的这类操作越多，对 InnoDB 表数据写入的影响越大。MySQL 提供了一个数据页合并临界值（MERGE_THRESHOLD），在某些场景下，能够人为染指，缩小数据页的合并与决裂。在 InnoDB 表里，每个数据页默认16K 大小，默认 MERGE_THRESHOLD 值为 50，取值范畴从 1 到 50，默认值即是最大值。也就是当页面记录数占比小于 50% 时，MySQL 会把这页和相邻的页面进行合并，保证数据页的紧凑，防止太多节约。 触发临界值场景场景一：页 A 里原本数据占用 100%，有一部分记录被删掉后，数据占用小于 50%，刚好触发了临界值。 场景二：页 B 里寄存的记录被更新为更短的模式，比方记录值由 rpad（'我爱你们所有人' , 10000, '增加冗余字符'）变为 '我只爱你' ，这时候记录对数据页占用也小于 50%，刚好触发了临界值。 简述数据页的合并页 A 在删除一些记录后，此页里残余记录对页 A 的占用小于 MERGE_THRESHOLD 设定的值，此时刚好页 A 相邻的一个页 C，数据占用也不到 50%，这时候 MySQL 会把页 C 的记录并入页 A，之后页 C 的空间就被开释，不蕴含任何数据，页 C 就可用于当前新记录的写入，防止空间的节约。 简述数据页的决裂页 D 和页 E，两个页面记录占用都在 49%。那么页合并后，页 D 记录占用 98%，只剩下 2%。页 F 和页 H，两个页面记录占用也都是 49%，那么合并后，页 F 记录占用 98%，也只剩下 2%。此时有新的插入申请过去，这条记录的主键刚好在页 D 和页 F 之间，可是页 D 和页 F 都只剩下 2% 的空间，不够插入这条记录。那怎么办？此时只能拆分页 D。建设一个新的页 I，完了把页 D 原来的记录和新插入的记录做一个排序，再依照新的程序把页 D 填满，剩下的数据放到页 I。所以页决裂会波及到老页数据的迁徙到新建页的建设，如果页的决裂频繁，那开销很大。 下来看看 MERGE_THRESHOLD 的理论用法与监测。留神！MERGE_THRESHOLD 不能小写，必须大写！小写就会被 MySQL 当作简略的正文。 ...

作者：岳明强爱可生北京分公司 DBA 团队成员，负责数据库治理平台的运维和 MySQL 问题解决。善于对 MySQL 的故障定位。本文起源：原创投稿*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。问题有的时候 SQL 语句被锁住了，可是通过 show processlist 找不到加锁的的 SQL 语句，这个时候应该怎么排查呢 前提performance_schema = on;试验1、建一个表，插入三条数据 mysql> use test1;Database changedmysql> create table action1(id int);Query OK, 0 rows affected (0.11 sec) mysql> insert into action1 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from action1;+------+| id |+------+| 1 || 2 || 3 |+------+3 rows in set (0.00 sec)2、开启一个事务，删除掉一行记录，但不提交 mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> delete from action1 where id = 3;Query OK, 1 row affected (0.00 sec)3、另开启一个事务，更新这条语句，会被锁住 ...