MySQL-InnoDB 存储引擎

MySQL InnoDB 存储引擎、事务相关笔记

Chapter 14 The InnoDB Storage Engine
https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

InnoDB 启动参数与系统变量

14.15 InnoDB Startup Options and System Variables
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

MySQL 全局系统变量在 5.1.7 节
5.1.7 Server System Variables
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

innodb_lock_wait_timeout(50秒)

show VARIABLES like '%innodb_lock_wait_timeout%';
该参数控制 Innodb 行锁等待的超时时间，单位为秒，该实例该参数的默认值为 50（秒）

默认50秒，我们生产系统设置成了 120 秒 set innodb_lock_wait_timeout=120;

事务超过此时间会报错
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

innodb_buffer_pool_size(128MB)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

启动参数：--innodb-buffer-pool-size=#
默认值：134217728 bytes (128M)
最小值：5242880
64 位系统中可配置的最大值为 2^64-1
32 位系统中可配置的最大值为 2^32-1
可动态调整：是

系统变量 innodb_buffer_pool_size 指定 InnoDB 引擎的 buffer pool 大小，单位字节。
InnoDB 使用 buffer pool 内存来缓存表和索引数据。

当 innodb_buffer_pool_size 大于等于 1GB 时，将 buffer pool 划分为多个实例(Buffer Pool Instance)可进一步提高性能，实例个数用系统变量 innodb_buffer_pool_instances 设置。
每个 Buffer Pool Instance 都有自己的锁，信号量，物理块(Buffer chunks)以及逻辑链表(List)。即各个 instance 之间没有竞争关系，可以并发读取与写入。
所有 instance 的物理块(Buffer chunks)在数据库启动的时候被分配，直到数据库关闭内存才予以释放。MySQL 使用 mmap 分配 Buffer Pool, 但是都是虚拟内存，在 top 命令中占用 VIRT 这一列，而不是 RES 这一列，只有相应的内存被真正使用到了，才会被统计到 RES 中，从而提高内存使用率。这就是为什么常常看到 MySQL 一启动就被分配了很多的 VIRT, 而 RES 却是慢慢涨上来的原因。

做数据库压测时启动了一个 innodb_buffer_pool_size=50G 的 MySQL，刚起动时 top 看内存占用如下：

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
98063 mysql     20   0   55.1g  15.5g  17096 S   0.3  2.0   0:26.35 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

用 sysbench oltp_read_only 压几次后，看到 MySQL 的 RES 已经有 40G 了。

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
98063 mysql     20   0   63.1g  40.9g  17696 S   0.3  5.4 125:32.95 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

InnoDB buffer pool 的扩缩容是以 块(chunks) 为单位进行的，块的大小由变量 innodb_buffer_pool_chunk_size 来定义，默认值为 128 MB.

innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。如果将 innodb_buffer_pool_size 改为一个非 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 整数倍的值，buffer pool 大小也会自动调整 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。

innodb_buffer_pool_size 的大小可动态配置而无需重启 MySQL 服务器。Innodb_buffer_pool_resize_status 状态变量可反映 buffer pool 在线扩缩容的状态。

5.7 中默认 134217728 字节即 134217728/1024/1024=128M
可在 my.cnf 中永久配置 innodb_buffer_pool_size=8G 后重启

在 Mysql 5.7 版本之前，调整 innodb_buffer_pool_size 大小必须在 my.cnf 配置里修改，然后重启 mysql 进程才可以生效。
如今到了 Mysql5.7 版本，就可以直接动态调整这个参数，方便了很多。

14.8.3.1 Configuring InnoDB Buffer Pool Size
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

[玩转MySQL之十]InnoDB Buffer Pool详解
https://zhuanlan.zhihu.com/p/65811829

修改 innodb_buffer_pool_size 大小

在 MySQL 5.7 之前 innodb_buffer_pool_size 的修改需要重启实例，在 5.7 后支持了动态修改 innodb_buffer_pool_size。

查看 innodb_buffer_pool_size 大小：
show variables like ‘innodb_buffer_pool_size’;
或
SELECT @@innodb_buffer_pool_size, @@innodb_buffer_pool_instances, @@innodb_buffer_pool_chunk_size;
134217728,1,134217728

改为 8gb

SET GLOBAL innodb_buffer_pool_size=8*1024*1024*1024;

MySQL InnoDB 数据预热

MySQL 的 InnoDB 引擎有 InnoDB Buffer Pool, 系统变量 innodb_buffer_pool_size 设置 buffer pool 大小，值越大，可以放到内存的数据越多，而大多数的项目都会有热点数据的存在，当热点数据经过 LRU 算法进入到 buffer pool 之后，读磁盘的次数减少，读的都是内存，速度是最快的。

数据库重启后 buffer pool 中是空的，热点数据被清空，此时 MySQL 就只能从磁盘中读取数据到内存中，所以刚启动后一段时间内性能较差。Buffer Pool 中数据从无到业务频繁使用热数据的过程称之为预热。让应用系统自身慢慢通过 SQL 给 InnoDB Buffer Pool 预热成本很高，如果遇到高峰期极有可能带来一场性能灾难，业务卡顿不能顺利运营。所以，需要人工触发 buffer pool 的预热。

MySQL 5.6 引入了数据预热机制。
innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 两个参数控制了预热，不过默认都是关闭的，需要开启。MySQL 5.7 则是默认开启。

MySQL 5.7 数据预热
在 MySQL 5.7 版本中引入将 Innodb Buffer 中数据备份和恢复的新特性，具体原理是将 Buffer pool 中 LRU 链表上存放的 spaceid 和 page id 存储到文件中，在数据库恢复时，按照该文件中存放的 spaceid 和 page id 数据对应的页面加载到 buffer pool 中，快速预热内存，提升查询性能。

相关参数：
innodb_buffer_pool_dump_now : 立刻做一次buffer pool LRU dump
innodb_buffer_pool_dump_at_shutdown ：在正常关闭实例时做一次转储
innodb_buffer_pool_load_at_startup : 在启动实例时读入转储文件中记录的Page
innodb_buffer_pool_load_now : 立即做一次转储文件读入
innodb_buffer_pool_load_abort：立刻中断LOAD操作
innodb_buffer_pool_dump_pct : 表示转储每个 Buffer pool instance 的 LRU 上最热的 page 的百分比。通过设置该参数可以减少转储的 page 数。

参数 innodb_buffer_pool_dump_pct 在 MySQL 5.7.2 版本中引入，在 MySQL 5.7.6 及之前版本中默认值为 100，而在 MySQL 5.7.7 及之后版本中默认值为 25

在 MySQL 5.6.21 和 MySQL 5.7.5 之前的版本中，由于使用同步方式读取 page 到 Buffer pool中，效率较低，后期版本采用异步方式加载，能明显提升加载速度。

innodb_buffer_pool_chunk_size(128MB)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size

启动参数：--innodb-buffer-pool-chunk-size=#
默认值：134217728 (128MB)
最小值：1048576
最大值：innodb_buffer_pool_size / innodb_buffer_pool_instances

InnoDB buffer pool 的扩缩容是以 块(chunks) 为单位进行的。
变量 innodb_buffer_pool_chunk_size 定义了 InnoDB buffer pool 扩缩容操作时 块(chunks) 的大小。
innodb_buffer_pool_chunk_size 可以以 1MB(1048576 bytes) 为单位增减配置。

如果 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 大于 innodb_buffer_pool_size, InnoDB buffer pool 初始化时会将 innodb_buffer_pool_chunk_size 设为 innodb_buffer_pool_size / innodb_buffer_pool_instances

innodb_buffer_pool_size 必须等于 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 或是其整数倍。如果修改 innodb_buffer_pool_chunk_size, innodb_buffer_pool_size 会自动调整为 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。

所以调整 innodb_buffer_pool_chunk_size 时务必小心，因为这可能引起 innodb_buffer_pool_size 的变化。

innodb_buffer_pool_instances(8)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

启动参数：--innodb-buffer-pool-instances=#
默认值：8（当 innodb_buffer_pool_size < 1GB 时默认值为 1）
最小值：1
最大值：64

InnoDB buffer pool 实例个数。
对于大于 1GB 的 buffer pool 来说，将其划分为不同实例可提高并发。
缓存页(page) 进行存取时，会根据哈希算法先映射到具体的 buffer 实例, 来实现不同 buffer 实例之间的隔离。

只有当 innodb_buffer_pool_size 大于等于 1GB 时，这个选项才起作用。
尽量合理的设置 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的大小，使得每个 buffer 实例的大小都大于 1GB.

除 Windows 平台外，当 innodb_buffer_pool_size 大于等于 1GB 时，默认值为 8, 否则默认值为 1.

innodb_page_size(16KB) 页大小

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size

启动参数：--innodb-page-size=#
默认值：16384
可取值： 4096 8192 16384 32768 65536

变量 innodb_page_size 指定 InnoDB tablespaces 的页大小，默认单位是 bytes.
可以用 bytes 或 KB 来指定，例如 16KB 可表示为 16384, 16KB, 16k.
innodb_page_size 必须在初始化 MySQL 实例之前配置，并且 MySQL 启动后无法再变。

MySQL 5.5 及之前，InnoDB 页大小是固定的 16KB.
从 MySQL 5.6 开始，可通过 innodb_page_size 变量将页大小配置为 4KB, 8KB 或 16KB
从 MySQL 5.7.6 开始，支持配置为 32KB 或 64 KB.

innodb_max_dirty_pages_pct(75) 最大脏页百分比

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct

命令行参数：--innodb-max-dirty-pages-pct=#
默认值：75

InnoDB buffer pool 的最大脏页百分比，默认值 75%.
InnoDB 会尝试 flush 数据以保证 buffer pool 中的脏页比例不超过 innodb_max_dirty_pages_pct

innodb_max_dirty_pages_pct_lwm(0) 脏页低水位

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct_lwm

命令行参数：--innodb-max-dirty-pages-pct-lwm=#
默认值：0

InnoDB buffer pool 中脏页的 低水位(low water mark) 百分比阈值，超过此阈值时会开启 预flush 以便控制脏页比例。
默认值 0 禁用预flush 功能。
innodb_max_dirty_pages_pct_lwm 必须低于 innodb_max_dirty_pages_pct

innodb_flush_neighbors(1) 脏页临近刷新

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

命令行参数：--innodb-flush-neighbors=#
默认值：1
可配置的值：0, 1, 2

将 buffer pool 中的脏页 flush 到磁盘时，时是否将同一 tablespace 中的其他脏页也刷入磁盘。
flush 指的是将 buffer pool 中的 脏页(dirty page) 写入磁盘上的数据文件。

设为 0 表示禁用此功能，相邻的脏页不会被同时刷入磁盘。
默认值 1 会将同一 tablespace 中连续的相邻脏页刷入磁盘。
设为 2 时会将同一 tablespace 中的所有脏页同时刷入磁盘。

如果数据是存储在传统的 HDD 机械硬盘上，相比于多次 flush 单独的脏页，同时 flush 相邻的脏页可大大减少 I/O 开销，因为会减少磁盘寻道时间。
如果数据是存储在 SSD 固态硬盘上，磁盘寻道时间不是主要 I/O 开销，可关闭此配置项。

innodb_file_per_table(ON) 独立表空间

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_file_per_table

show variables like '%innodb_file_per_table%';

该参数在 MySQL 5.6.6 及其后续版本默认开启。

开启该参数的时候，Innodb 将每个新创建的表的数据及索引存储在一个独立的 .ibd 文件里，而不是系统的表空间。当这些 innodb 表被删除或清空的时候，存储空间会被回收。

innodb_file_per_table 可通过 SET GLOBAL 动态的修改为ON或OFF set global innodb_file_per_table =ON;，也可以在 my.cnf 中做永久性修改，在 my.cnf 中修改后生效的话需要重启 mysqld 服务。

MySQL默认事务隔离级别

14.7.2.1 Transaction Isolation Levels
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

MySQL使用可重复读作为默认隔离级别的原因
https://www.cnblogs.com/vinchen/archive/2012/11/19/2777919.html

MySQL一致性读分析

[MySQL] 一致性读分析
https://www.cnblogs.com/renolei/p/5910060.html

InnoDB 表空间

InnoDB 表空间（Tablespace）可以看做一个逻辑概念，InnoDB 把数据保存在表空间，本质上是一个或多个磁盘文件组成的虚拟文件系统。InnoDB 表空间不仅仅存储了表和索引，它还保存了回滚日志（redo log）、插入缓冲（insert buffer）、双写缓冲（doublewrite buffer）以及其他内部数据结构。

共享表空间与独立表空间

15.6.3.2 File-Per-Table Tablespaces
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

Innodb 存储引擎可将所有数据存放于名为 ibdata* 的共享表空间，也可将每张表的数据存放于独立的名为表名.ibd 文件的独立表空间。

MySQL 5.6.6 及其后续版本中，默认开启独立表空间，即 innodb_file_per_table=on

共享表空间：某一个数据库所有表的数据、索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 datadir 目录下，默认文件名为 ibdata1，初始化为10M。
独立表空间：每一个表的数据都会以独立的文件来存储，每一个表都有一个 .frm 表描述文件，和一个 .ibd 文件。 ibd 文件包括了单独一个表的数据内容以及索引内容，默认情况下存储在 datadir/db名目录下，初始大小为 96KB。

启用 innodb_file_per_table 独立表空间选项后，每张表的表空间内存放的只是数据、索引和插入缓冲，其它的数据，如撤销（Undo）信息、系统事务信息、二次写缓冲（double write buffer）等还是存放在原来的共享表空间内。所以即使启用了参数 innodb_file_per_table ，共享表空间还是会不断地增加其大小。

独立表空间的优点：
1、共享表空间时，由于数据都统一保存到innodb系统表空间文件中，在 drop table ，truncate table 后表空间文件并不会进行收缩，也就是说表空间文件所占的磁盘空间并不会因为drop table , truncate table 而释放。
开启独立表空间后，drop table ，truncate table 后磁盘空间可被回收

ibd文件结构(段区页)

ibd 文件，或者说 InnoDB 表空间，由段（segment）、区（extent）、页（page）组成：

段(Segment) 分为索引段，数据段，回滚段等。其中索引段就是非叶子结点部分，而数据段就是叶子结点部分，回滚段用于数据的回滚和多版本控制。一个段包含256个区(256M大小)。
区(Extent) 是页的集合，一个区包含64个连续的页，默认大小为 1MB (64*16K)。
页(Page) 是 InnoDB 管理的最小单位，常见的有 FSP_HDR, INODE, INDEX 等类型。所有页的结构都是一样的，分为文件头(前38字节)，页数据和文件尾(后8字节)。页数据根据页的类型不同而不一样。

InnodDB 采用 Btree 作为存储结构，当用户创建一个Table的时候，就会根据显示或隐式定义的主键构建了一棵Btree，而构成Btree的叶子节点被称为Page，默认大小为16KB，每个Page都有一个独立的Page_no。在我们对数据库中的Table进行修改时，最终产生的影响都是去修改对应TableSpace所对应的Btree上的一个或多个Page。

为了更加方便管理和维护Extent和Page，设置了一些特殊的Page来索引它们，即 Page0，Page1，Page2，Page3，从代码的注释来看，各个Page的作用如下：
1、Page0 是 .ibd 文件的第一个Page，这个Page是在创建一个新的Tablespace的时候初始化，类型为 FIL_PAGE_TYPE_FSP_HDR，是表空间的root page，存储表空间关键元数据信息。这个Page用来跟踪后续256个Extent（约256M）的空间管理，所以每隔256M空间大小就需要创建相仿于Page0的Page，类型为 FIL_PAGE_TYPE_XDES，XDES Page除了文件头部外，其他都和FSP_HDR页具有相同的数据结构，可以称之为 Extent描述页，每个Extent占用40个字节，一个XDES Page最多描述256个Extent。

2、page1 主要用于跟踪随后的每个page的change buffer信息；

3、page2 类型为 FIL_PAGE_INODE，存储inode（index node），管理表空间的segment。每个inode对应一个segment。每个inode page默认存储FSP_SEG_INODES_PER_PAGE（85）个inode。预留12个字节来记录前后节点位置

4、page3 也就是第一个索引页，是真正存放用户数据页。在InnoDB中，数据即索引，索引即数据

table metadata lock

metadata lock 是为了保护 database objects （包括表结构、存储过程、触发器等）而设计的。

下面的 MDL 操作需要获取 metadata lock
创建、删除索引。
修改表结构。
表维护操作(optimize table、repair table等)。
删除表。
获取表上表级写锁 (lock table tab_name write)。

那 metadata lock 和事务有什么关系呢？
在事务中，当它需要访问一个 database object 时都需要先获得其 metadata lock，在事务结束后才会释放 metadata lock。这样做有几个目的：

第一是为了进一步保证事务的一致性，比如我在事务 A 中对某一行记录进行了更新，我的事务现在还没有提交，但是这个时候另外一个会话2要修改表名，如果事务 A 持有了 metadata lock，那么这时候另一个会话2将无法修改，show processlist 会发现它在 Waiting for table metadata lock 。直到事务 A 提交或回滚后，才能获得 metadata lock 修改成功。如果我们没有 metadata lock 的机制，那么会话2 就可以直接修改表名，这样当事务 A 由于其它原因需要回滚的时候，就回滚不了，因为表名被修改了，这样导致数据不一致。

第二是为了解决 binlog 同步的一个 bug，这个和上面的原因一样。binlog 的操作是基于事务的提交顺序的。事务 A 还未提交，另一个会话删除了相关表，这样 binlog 先记录的是删除表的操作，从库执行的顺序就不对了。

Waiting for table metadata lock

MySQL在进行alter table等DDL操作时，有时会出现 Waiting for table metadata lock 的等待场景。
而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）都无法进行，因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。
如果是产品环境的核心表出现了这样的锁等待队列，就会造成灾难性的后果。

造成alter table产生Waiting for table metadata lock的原因其实很简单，一般是以下几个简单的场景：

场景一：长事物运行，阻塞DDL，继而阻塞所有同表的后续操作
通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。
处理方法： kill 掉 DDL所在的session.

场景二：未提交事物，阻塞DDL，继而阻塞所有同表的后续操作
通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。
处理方法：通过 select * from information_schema.innodb_trx, 找到未提交事物的sid, 然后 kill 掉，让其回滚。

Waiting for table metadata lock 案例分析

添加索引的过程中，有查询请求，查询请求等待 table metadata lock 超时。

MySQLQueryInterruptedException

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted
; uncategorized SQLException; SQL state [70100]; error code [1317]; Query execution was interrupted; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted, StackTrace: [org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89), org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81), org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81), org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75), org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447), com.sun.proxy.$Proxy149.selectList(Unknown Source), org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231), org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:128), org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68), org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53), com.sun.proxy.$Proxy172.queryUserIdByStatusAndRole(Unknown Source), com.nio.uds.watson.service.sales.LeadsDistributeService.queryUserIdByStatusAndRole(LeadsDistributeService.java:573),

show engine innodb status

show engine innodb status;

查看innodb死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-11-05 19:07:44 2b6bcb03f700
*** (1) TRANSACTION:
TRANSACTION 67059742790, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 462 lock struct(s), heap size 63016, 4 row lock(s)
MySQL thread id 39706604, OS thread handle 0x2b6ba0f91700, query id 168182824647 10.128.18.185 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 1800241143
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742790 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 67059742791, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
641 lock struct(s), heap size 95784, 3 row lock(s)
MySQL thread id 39707153, OS thread handle 0x2b6bcb03f700, query id 168182824649 10.128.19.96 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 29223 n bits 792 index `account1_id` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (1)

查看innodb缓存命中率

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8795455488
Dictionary memory allocated 2653798
Buffer pool size   524288
Free buffers       858
Database pages     504022
Old database pages 186034
Modified db pages  60379
Pending reads      1
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 1642297407, not young 99650347544
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1759973825, created 88493948, written 1212213539
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 58 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 504022, unzip_LRU len: 0
I/O sum[19343]:cur[72], unzip sum[0]:cur[0]

（1）Total memory allocated，这就是说buffer pool最终的总大小是多少
（2）Buffer pool size，这就是说buffer pool一共能容纳多少个缓存页
（3）Free buffers，这就是说free链表中一共有多少个空闲的缓存页是可用的
（4）Database pages和Old database pages，就是说lru链表中一共有多少个缓存页，以及冷数据区域里的缓存页数量
（5）Modified db pages，这就是flush链表中的缓存页数量
（6）Pending reads和Pending writes，等待从磁盘上加载进缓存页的数量，还有就是即将从lru链表中刷入磁盘的数量、即将从flush链表中刷入磁盘的数量
（7）Pages made young和not young，这就是说已经lru冷数据区域里访问之后转移到热数据区域的缓存页的数量，以及在lru冷数据区域里1s内被访问了没进入热数据区域的缓存页的数量
（8）youngs/s和not youngs/s，这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页的数量
（9）Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，这里就是说已经读取、创建和写入了多少个缓存页，以及每秒钟读取、创建和写入的缓存页数量
（10）Buffer pool hit rate xxx / 1000，这就是说每1000次访问，有多少次是直接命中了buffer pool里的缓存的
（11）young-making rate xxx / 1000 not xx / 1000，每1000次访问，有多少次访问让缓存页从冷数据区域移动到了热数据区域，以及没移动的缓存页数量
（12）LRU len：这就是lru链表里的缓存页的数量
（13）I/O sum：最近50s读取磁盘页的总数
（14）I/O cur：现在正在读取磁盘页的数量

如果Free buffers接近0，可能需要增加缓冲池大小

Innodb_buffer_pool_wait_free

select * 
from performance_schema.global_status 
where variable_name = 'Innodb_buffer_pool_wait_free';

当该值大于0的时候，就要立即发出告警，表示InnoDB Buffer Pool严重不够用，如果物理内存足够，则适当加大，或者迁移到更高内存的服务器上。

Innodb_buffer_pool_reads 缓存命中率

select *
from performance_schema.global_status
where variable_name in (‘innodb_buffer_pool_reads’, ‘innodb_buffer_pool_read_requests’);

Innodb_buffer_pool_read_requests 表示read请求的次数，
Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数
所以innodb buffer的read命中率就可以这样得到：（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads） / Innodb_buffer_pool_read_requests * 100%。一般来讲这个命中率不会低于99%，如果低于这个值的话就要考虑加大innodb buffer pool。

如何找出哪些InnoDB表缓存在innodb buffer pool中?

使用sys模式可以发现缓冲池(sys模式)中缓存了多少页的表(从MySQL Server 5.7开始，它是默认安装的)。要使用的查询是:
SELECT * FROM sys.innodb_buffer_stats_by_table order by allocated desc limit 10;

事务/锁相关案例排查

mysql行锁与synchronized死锁案例排查

表结构

create table user (
    id bigint auto_increment primary key,
    mobile varchar(20) not null comment '手机号',
  constraint mobile unique (mobile)
);

事务的隔离级别为 Spring 默认的读已提交 READ_COMMITTED，传播属性为默认的 PROPAGATION_REQUIRED 即有则加入无则新建。

代码结构如下，这是个创建 user 的功能，入口有2个，一个来自 http 接口调用，一个收听 kafka 消息创建。最终都通过 insertUser 方法插入user表。

// http 创建 user 接口
@Transactional
public void httpCreateUser(request) {
  if(select no user) {
    userService.insertUser(request);  // @1
  }
  ... ...
  userService.insertUser(request);  // @2
  ... ...
}

// kafka 消息处理
@Tranactional
public void consumerCreateUser(request) {
  ... ...
  if(select no user) {
    userService.insertUser(request);  // @3
  }
  ... ...
}

private void insertUser(request) {
  // 从全局 static lock map 中根据 mobile 获取唯一的锁对象
  Object lockObject = getLockByString(request.getMbile());
  synchronized(lockObject) {
    userService.insertUser(request);
  }
}

问题：
生产环境出现事务死锁，造成后续大量请求超时
org.springframework.dao.CannotAcquireLockException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction|

原因：
死锁，mysq 行锁和 java synchronized 对象锁互相死锁。
经分析发现造成死锁的过程如下，在同一台server上，依次按如下时序:
1、线程1 从 http request 请求创建用户，进入 httpCreateUser, 开启事务, 在 @1 处调用 insertUser, 插入新user，同时也锁住了 mobile 所在的行。
2、线程2 消费创建新 user 的 kafka 消息，进入 consumerCreateUser, 开启事务, 此时由于线程1 的事务还没提交，线程2 中查不到，认为此手机号的用户还没创建，在 @3 处调用 insertUser, synchronized 加锁成功，但 insert sql执行时需要等待 mysql 行锁
3、线程1 不管什么原因在 @2 处又调用了一次 insertUser, 且调用之前没先select查一下，在 insertUser 方法中 synchronized 加锁不成功，因为同一个手机号的 object 锁已经被线程2 占有了。
线程1占着 mysq 行锁等待 synchronized 对象锁，线程2占着 synchronized 对象锁等待 mysq 行锁，造成死锁。

其实，线程2 在 mysq 行锁等待超时后（mysql默认50秒，我们生产系统设置成了120秒，innodb_lock_wait_timeout 变量设置），可以抛异常并自动释放 synchronized 对象锁，结束执行。然后线程1 拿到 synchronized 对象锁再次insert，mysql报重复key异常，结束执行。是可以自动解开死锁的，之所以没有自动解开，是因为后续又进来了n多个重试请求，要求insert这个手机号的user，并且 synchronized 是非公平锁，后续来的活跃请求会插队到线程1 前面，也就是线程2 即使抛异常结束后线程1 也拿不到 synchronized 锁，然后就一直等着也不commit事务，一直锁着这个手机号对应的数据行。然后后续所有的插入请求都会等待 innodb_lock_wait_timeout 时间后再返回，产生雪崩效应。
可以本地开两个session试下
session 1 中，手动开启事务，插入一个原来没有的手机号到user, 先不 commit

mysql > begin;
mysql > insert into user(mobile) values('98762427151');

此时，session 2 中还查不到这个手机号（默认隔离级别RR或RC都看不到），插入同一个手机号的user（手动或自动提交事务都行），会等待锁超时时间（默认50秒）后，给出 Lock wait timeout exceeded 错误，因为mysql锁住了这个手机号所在的行。

mysql > select * from user where mobile='98762427151';
Empty set (0.000 sec)
mysql > insert into user(mobile) values('98762427151');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

事务内加分布式锁导致重复数据案例排查

以如下关系表为例

create table student_school_table
(
    student_id bigint null,
    school_id bigint null
);

业务要求是一个学生只能有唯一的一所学校，即 student_school_table 表中每个 student_id 最多对应一个 school_id，但表上忘了给 student_id 加唯一索引。

后端代码中，学生学校信息的更新是放在一个大事务中的，伪代码如下

@Transactional(isolation = Isolation.READ_COMMITTED)
public void updateStudentInfo(Request request) {
  // 更新学生基本信息
  updateBaseInfo(request);

  // 更新地址信息
  updateAddress(request);

  // 创建或更新学校信息
  upsertSchool(request);

  // 更新家庭信息
  updateFamilyInfo(request);
}

事务的隔离级别为Spring默认的读已提交READ_COMMITTED，传播属性为默认的PROPAGATION_REQUIRED即有则加入无则新建。

更新学校信息放在 upsertSchool 方法中，为了达到“一个学生最多只有一所学校”的目的，upsert 学校时加了分布式锁，保证对同一个学生的学校信息更新只能串行进行。伪代码如下：

public void upsertSchool(Request request) {
  try {
    // 加分布式锁，锁 key 是 student_id
    lock(student_id);
    // select 查询 student_id 是否已有学校信息，没有的话插入，已有的话更新
    ... ...
  } catch (Exception exception) {
    // log.error
  } finally {
    unlock();
  }
}

结果还是出现了 student_school_table 表中一个 student_id 有两个学校信息的情况。
查日志分析出问题的过程如下：
0、前提： id为1 学生目前还没有学校信息。
1、两台服务器 server1 和 server2 上同时进来更新学生1 的信息的请求，server1请求将学生1 的学校更新为100，server2请求将学生1 的学校更新为200
2、两台服务器上几乎同时进入 updateSchool 方法，server1先加分布式锁成功，select发现学生1 无学校信息，插入一条记录：(学生1, 学校100)，但事务还未提交，因为要等到整个updateStudentInfo方法结束才提交。updateSchool 方法结束后释放分布式锁。
3、刚才 server1 执行 updateSchool 方法时 server2 一直在等待分布式锁，等 server1 释放后，server2 获得锁，select 发现学生1 无学校信息，插入一条记录：(学生1, 学校200)，这里有非常关键的一点：由于事务隔离级别为读已提交，在server1提交事务前，server2查库时是看不到server1写进去的(学生1, 学校100)这条记录的，所以会认为学生1还没有学校信息
4、由于数据库也没给 student_school_table 的 student_id 字段加唯一索引，就出现了学生1 有两条学校记录的情况。

解决方法：
1、给 student_school_table 的 student_id 字段加唯一索引。
2、事务内加分布式锁导致多线程间数据不可见，所以可以改为把分布式锁加到 updateStudentInfo 方法外，在锁内提交事务。

数据库磁盘空间不足导致事务超时排查案例

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

异常表现：
azkaban web界面无法上传新任务也无法删除和修改任何任务属性，看后台日志都是等待一段时间后抛出锁等待超时异常，比如：

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction Query: INSERT INTO project_versions (project_id, version, upload_time, uploader, file_type, file_name, md5, num_chunks, resource_id) values (?,?,?,?,?,?,?,?,?) Parameters: [56, 7, 1544775799795, azkaban, zip, sync-user-identity-1.0.0-SNAPSHOT-sync-user-identity.zip, [-27, 52, 115, 62, -67, 107, 55, 49, 95, -107, -41, 27, -81, 90, 22, 115], 0, null]

原因与解决：
查看 information_schema.INNODB_TRX 表，发现有好多卡住的事务，不知道为什么。
在mysql命令行 kill trx_mysql_thread_id 也不起作用。
找DBA帮看下，发现事务卡住是因为azkaban数据库所在的服务器磁盘满了，写不进去。
DBA紧急给做了磁盘扩容，马上就好了。

所以有时候真的无法想象会出现什么异常，谁能想到是数据库服务器磁盘满了导致写不进去，事务卡住，又引起了后来事务的锁等待超时，最终表现为web界面无法进行任何写入操作。

关于MySQL的lock wait timeout exceeded解决方案
https://segmentfault.com/a/1190000015314171

交叉索引导致死锁案例排查

表结构如下：

create table relation_table
(
    id bigint auto_increment primary key,
    account1_id bigint null,
    account2_id bigint null,
    relation smallint(6) not null,
    create_time datetime default CURRENT_TIMESTAMP not null,
    update_time timestamp default CURRENT_TIMESTAMP not null,
    constraint relation_fk_1 foreign key (account1_id) references account (account_id),
    constraint relation_fk_2 foreign key (account2_id) references account (account_id)
);

业务上来说，是两个用户同时对一个用户更新关系类型，比如 Account A 和 Account B 同时更新和 Account C 的关系。
反应在表上就是，表中已有：
(A, C)
(B, C)
然后又同时更新(A, C) 和 (B, C)

代码层面，对 account1_id-account2_id 这个key加了redis分布式锁，所以同时写 account1 和 account2 都相同的，分布式锁层面就会拦截住，但这两个sql account2不同，分布式锁锁不住。

explain 下这条sql语句：

MariaDB [uds]> explain UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475;
+------+-------------+-----------------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
| id   | select_type | table                 | type        | possible_keys           | key                     | key_len | ref  | rows | Extra                                                 |
+------+-------------+-----------------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
|    1 | SIMPLE      | relation_table        | index_merge | account1_id,account2_id | account1_id,account2_id | 9,9     | NULL |    1 | Using intersect(account1_id,account2_id); Using where |
+------+-------------+-----------------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
1 row in set (0.009 sec)

同事帮看了下，看到explain的type是index_merge，感觉像是走交叉索引导致的死锁。
这个表在 account1 和 account2 上都有外键，外键默认会创建索引，所以这条sql会同时使用2个索引来加锁。
上网搜了下，有好多类似情况，大体上能确定原因就是这个。

InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
1、在不通过索引条件查询的时候,InnoDB 确实使用的是表锁,而不是行锁。
2、由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。
3、当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
4、即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的,如果 MySQL 认为全表扫效率更高,比如对一些很小的表,它就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

MySQL中的行级锁,表级锁,页级锁
http://www.hollischuang.com/archives/914

index_merge引发的死锁排查
https://www.cnblogs.com/cchust/p/6464188.html

index merge 引起的死锁分析
http://seanlook.com/2017/03/11/mysql-index_merge-deadlock/

解决：
解决方法：加联合索引
添加联合索引 account_key(account1_id,account2_id)，这样以来查询会选择这一个索引，至于 idx_sid 这个单列索引还需不需要，看业务场景。

创建联合索引：

create index idx_account1_2 on relation_table (account1_id, account2_id);

创建之后再explain下这个SQL：

MariaDB [uds]> explain UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475;
+------+-------------+-----------------------+---------+-----------------------------+-------------------------+---------+------+------+-------------+
| id   | select_type | table                 | type    | possible_keys               | key                     | key_len | ref  | rows | Extra       |
+------+-------------+-----------------------+---------+-----------------------------+-------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | relation_table        | range   | account2_id,idx_account1_2  | idx_account1_2          | 18      | NULL |    1 | Using where |
+------+-------------+-----------------------+---------+-----------------------------+-------------------------+---------+------+------+-------------+
1 row in set (0.009 sec)

index_merge 变为 range

但我在test环境上，创建联合索引后，explain 还是 index_merge，DBA在生产上explain是range，说索引跟数据量关系很大，就算结构一样，数据量不一样的表，走的索引也不太一样的。

MySQL死锁日志：
show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-11-05 19:07:44 2b6bcb03f700
*** (1) TRANSACTION:
TRANSACTION 67059742790, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 462 lock struct(s), heap size 63016, 4 row lock(s)
MySQL thread id 39706604, OS thread handle 0x2b6ba0f91700, query id 168182824647 10.128.18.185 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 1800241143
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742790 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 67059742791, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
641 lock struct(s), heap size 95784, 3 row lock(s)
MySQL thread id 39707153, OS thread handle 0x2b6bcb03f700, query id 168182824649 10.128.19.96 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `uds`.`relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 29223 n bits 792 index `account1_id` of table `uds`.`relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (1)

服务端异常信息：

[Handled] unknown exception
org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.masikkk.mapper.MyRelationMapper.updateAppUserRelation-Inline
### The error occurred while setting parameters
### SQL: UPDATE relation_table SET relation = ? WHERE account1_id =? AND account2_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263) ~[spring-jdbc-4.3.16.RELEASE.jar!/:4.3.16.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.16.RELEASE.jar!/:4.3.16.RELEASE]
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75) ~[mybatis-spring-1.3.0.jar!/:1.3.0]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447) ~[mybatis-spring-1.3.0.jar!/:1.3.0]
    at com.sun.proxy.$Proxy120.update(Unknown Source) ~[?:?]
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295) ~[mybatis-spring-1.3.0.jar!/:1.3.0]
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59) ~[mybatis-3.4.0.jar!/:3.4.0]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) ~[mybatis-3.4.0.jar!/:3.4.0]
    at com.sun.proxy.$Proxy137.updateAppUserRelation(Unknown Source) ~[?:?]

当前位置 : 首页 » 文章分类 : 开发 » MySQL-InnoDB 存储引擎