innodb存储引擎初步认识及配置

innodb的锁？

有哪些锁？

• Shared and Exclusive Locks：两种类型的行级锁，允许事务分别获取。
• Intention Locks：意图锁，表级锁，有两种类型（IS、IX）。作用就是表现意图，表明当前事务正在获取该表的行级锁。可以在 show engine innodb status查看
• Record Locks：记录锁。根据聚集索引或者第二索引锁住所有对应行记录不被其它事物更改
• Gap Locks：间隙锁。保证索引范围内不会被插入新的索引值的记录。在隔离级别为RR时才有，RC时没有gap锁。

  隔离界别为RC时，是不是mysql服务器不再使用gap锁？
  不是，RC隔离级别时，只是查找和索引扫描不再使用gap锁。但外键约束检查、重复键检查时依然使用gap锁。

• Next-Key Locks：下一键锁。是记录锁和间隙锁的结合。在RR隔离级别使用下键锁避免幻读现象。
• Insert Intention Locks：插入意图锁，间隙锁的一种类型，在插入操作之前获取。
• AUTO-INC Locks：自增锁，一种表级锁。效率较低，有innodb_autoinc_lock_mode配置下使你权衡自增算法和性能。但一般不建议使用此来实现自增。
• Predicate Locks for Spatial Indexes：空间索引预测锁，针对innodb的空间表的列的空间索引。

不同sql产生的不同类型的锁

语句	锁
select	无锁
SELECT … LOCK IN SHARE MODE	所有扫描行共享下一键锁；索引记录锁（唯一索引行时）
SELECT … FOR UPDATE	所有扫描行排它下一键锁；索引记录锁（唯一索引行时）
UPDATE … WHERE …	所有扫描行排它下一键锁 或 索引记录锁（唯一索引行时） 同时 隐式锁辅助索引 共享下一键锁（重复键检查时）
DELETE FROM … WHERE …	所有扫描行排它下一键锁；索引记录锁（唯一索引行时）
INSERT	先排它索引记录锁，修改前获取插入意图间隙锁。重复键时获取共享锁
INSERT … ON DUPLICATE KEY UPDATE	无记录时：索引记录锁（同insert） 有记录：排它下一键锁（同update）
REPLACE	无记录时：索引记录锁（同insert） 有记录：排它下一键锁（同update）
INSERT INTO T SELECT … FROM S WHERE …	T表：每条记录索引记录锁（同insert） S表：1. 无锁；2. 共享下一键锁
CREATE TABLE … SELECT …	同 INSERT INTO T SELECT … FROM S WHERE …
REPLACE INTO t SELECT … FROM s WHERE … or UPDATE t … WHERE col IN (SELECT … FROM s …)	S表：共享下一键锁
AUTO_INCREMENT 列自增	在innodb_autoinc_lock_mode=0,1时：table-level AUTO-INC锁（表级锁） 在innodb_autoinc_lock_mode=2时: 无锁
AUTO_INCREMENT 旧值获取	无锁
LOCK TABLES	表级锁

共享锁导致死锁的情况吗？
会。当3个session先后simple insert 同一个主键的记录时，第一个sessionrollback，后两个session同时获取到共享锁，因互相共享锁的存在均无法获得排它锁进行插入。

innodb表主键生成机制

自动增量机制

自增机制是指，insert语句在不指定主键的情况下，会为当前记录在历史记录基础上自动自增生成主键的过程。
自增主键初始值在mysql服务器或集群启动时确定：

• 当表空时，由 auto_increment_offset 决定初始值
• 当表非空时，获取表最大主键值 + auto_increment_increment步长 为初始值。

表最大主键值：SELECT MAX(ai_col) FROM table_name FOR UPDATE;

** AUTO_INCREMENT 初始值在不同场景下，是如何设置下一主键值的：**

• 指定主键值 < 0 或 > MAX：不设置，为空
• AUTO_INCREMENT = null 或 = 0: 按照指定模式自增

自动自增计数器如何存储：
只要服务器运行，InnoDB就会使用内存中的自动递增计数器。当服务器停止并重新启动时，InnoDB会重新按照上述规则初始化每个表的计数器。

innodb_autoinc_lock_mode：自增模式设置

• 0, 1, or 2, for traditional, consecutive, or interleaved, respectively
• The default setting is 1 (consecutive)

auto_increment_increment：自增步长

连续间隔，上面是模式，但这个配置则是连续间隔的步长。默认为1，常用于mysql主从复制服务器间避免主键冲突

三种自增模式及其语义

0: tranditional，传统模式

效率最低，不同模式的插入都要获取表级AUTO-INC锁。
表的主键单调连续递增。

1: consective，连续模式

效率高于传统模式，在简单插入时不需要获取表级AUTO-INC锁
一般情况下，表的主键也是单调连续递增的。

思考：什么情况下连续模式的表主键会间隔和丢失？
第一中情况：事务回滚时自增主键会丢失，
第二种情况：混合插入模式时自增主键会发生高估，多余的主键会丢失

2: interleaved，间隔模式

• 表现：
  无并发写情况下，简单插入、批量插入都是单调连续递增的。
  并发写情况下，批量插入语句是可能发生间隔的。

• 间隔模式：无论何种插入方式，都不使用表级AUTO-INC锁。其追求最好的性能和并发能力。

MVCC

各个标志位含义：

标志位	DB_TRX_ID	deletion-mark	DB_ROLL_PTR	DB_ROW_ID
长度	6-byte	1-byte	7-byte	6-byte

innodb表的每条记录都含有如上4个标志位，分别用于表示当前最大的事务ID、删除表级、回滚段撤销日志记录、行ID。

辅助索引如何MVCC

deleted-mark、trx_id、db_roll_ptr都标记在聚集索引里
即使ICP开启，如果辅助索引对应记录没找到则不回表聚集索引，如果存在就回到聚集索引里标记

innodb架构

mysql整体架构

server接收DML语句（优化、缓存sql等） -> 发给存储引擎执行 -> server进行过滤并返回结果

Innodb 存储引擎架构

in-memory 部分架构

公共四部分：

• change buffer：作用缓存第二索引的变化，防止第二索引引发的频繁随机IO降低buffer pool的查询。change buffer数据进入buffer pool会进行整理合并
• log buffer：默认16M，适当的增大可以减少磁盘IO频率

on-disk 部分架构

• redo log：作用事务的不完整变化的数据记录，在mysql宕机恢复后可用于事务恢复。默认两个文件：ib_logfile0和ib_logfile1。

OS Cache部分

是in-memory和on-disk的中间部分，就是说innodb可以将一些日志、数据的写入
先写到OS操作系统的buffer里面缓存，积累到一定量再一次写入到磁盘文件中持久化。

对innodb的性能监控，主要分为以下三部分：

1. innodb内存：buffer pool大小是否合适？change buffer大小设置、模式选择、监控？自适应哈希索引是否开启或分区或总发生锁碰撞？log buffer是否够用？
2. innodb磁盘：使用的是独立表空间还是系统表空间？磁盘持久化的频率？开启二次写入double write？存储是否分区？
3. OS Cache：缓冲池数据刷盘频率设置？

innodb索引

innodb支持哪些索引算法：

• b-tree：mysql中大多数情况使用b-tree索引
• hash：临时表和自适应哈希索引
• R-Tree：空间数据类型
• 倒排序：全文索引

索引分类：

• PRIMARY KEY：主键索引
• UNIQUE：唯一索引
• INDEX：辅助索引、联合索引
• FULLTEXT：全文索引

hash索引 vs B-Tree索引

比较	B-Tree索引	hash索引
应用场景	innodb默认存储引擎	Memory默认存储引擎
适用情形	1. 适用between、<、<= 等 2. 支持like子句遵循最左前缀原则模糊查询 3. 可以进行排序	1. 只适用等值、非等值情形 2. 不支持like最左前缀查询 3. 不支持排序order by 4. 无法预知两个索引值之间的记录行数

当支持多索引算法时，如何选择哪种索引算法查询数据？

使用范围优化器估算预判哪种索引方式需要扫描的记录行数更少，则最终适用那种索引来具体查询数据。

自适应哈希索引

自适应：innodb的监视索引搜索的机制
哈希索引：在innodb的内存中，将索引值转化为一种指针

作用：
好处：带来比一般索引查找过程的工作负载的大大减小
坏处：监视索引搜索机制 & 维护哈希索引结构，但二者的消耗远小于对哈希索引查找带来的效率提升

何时开启：MySQL 5.6 及早期版本适合禁用，5.7 添加了哈希索引的分区会减少哈希索引的并发锁故建议开启

innodb特性

预读

将on-disk数据随机或顺序预读到buffer pool中，以避免内存-磁盘的读写速率不匹配问题。
根据计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

自适应哈希索引

对第二索引的热数据进行哈希缓存，减少频繁的非聚集索引文件的查找

二次写double write

防止磁盘的以页为单位的持久化方式导致页缺失而造成数据不一致。默认是开启的。本身不会造成双倍的磁盘IO，它是一个大的顺序存储块，不同于一般的磁盘操作IO。
是位于buffer pool到磁盘文件中间的一块存储区域，对磁盘页数据的拷贝。

磁盘刷盘

innodb存储刷盘主要涉及两部分：bin_log二进制日志 & ibd_logfile日志文件

• bin_log二进制日志是无论底层使用任何存储引擎都会有的，是具体执行DML语句的存储。有三种形式：statement、row、mixed。作用可用于精确的数据恢复。
• ibd_logfile日志文件是innodb存储引擎特有的日志文件，用于存储redo log日志，是一种磁盘顺序文件存储结构。作用可用于数据的宕机恢复，保证数据的持久化。

刷盘频率

磁盘刷盘指的就是bin_log二进制日志 & ibd_logfile日志文件 的刷盘方式以及执行刷盘的频次。
频次越高、刷盘越保证此次都刷入磁盘 则数据的可靠性、持久性越强。但带来的副作用就是mysql服务器的性能则相反越逊色。

双1，指bin_log二进制日志 & ibd_logfile日志文件都设置为1。则 每个sql语句执行都刷盘记录，每个事务都及时刷盘持久化，这样的可靠性最高，但反之性能也是最差的。

innodb配置

• innodb_log_buffer_size

• innodb_log_file_size redo日志文件大小，默认5M

• innodb_log_files_in_group redo文件个数

• innodb_flush_log_at_trx_commit ：how flushing to disk

• innodb_flush_log_at_timeout ：log flushing frequency

• innodb_autoinc_lock_mode 主键生成方式，默认1（连贯的）。

• innodb_lock_wait_timeout sql获取锁等待时间，默认50s。

• innodb-rollback-on-timeout 设置sql超时，事务回滚。默认OFF。

• key_buffer_size：caching size for MyISAM

• query_cache_size: caching size for innodb

• innodb_deadlock_detect : 5.7引入死锁检测

• innodb_doublewrite 二次写入开启

• innodb_rollback_segments innodb使用的撤销回滚段总个数，默认128

• innodb_flush_log_at_trx_commit 刷盘方式，默认1

• innodb_undo_directory undo日志存储路径，默认/usr/local/mysql/data

• innodb_undo_tablespaces undo日志空间，默认0

• innodb_fast_shutdown 是否快速停止服务，默认1。

• innodb_force_recovery 是否强制重启。默认0，没有强制重启。值域0-6。

• innodb_read_ahead_threshold 预读灵敏度。默认56。

• innodb_thread_concurrency innodb并发数，默认0，表示无限并发数。

• innodb_read_io_threads 默认4

• innodb_write_io_threads 默认4

• key_buffer_size MyISAM引擎的索引缓存大小，默认8M

• table_open_cache 设置FD文件描述符数量，所有线程可打开文件数量，默认2000

• sort_buffer_size 用于排序的缓冲区大小，默认2M。

• binlog_format 默认ROW

标准监控和锁输出

标准输出模块解析

• Status
  显示时间戳、监视器名称和每秒平均值所基于的秒数。秒数是当前时间与上次打印InnoDB Monitor输出之间经过的时间。
• BACKGROUND THREAD
  显示主后台线程所做的工作。
• SEMAPHORES
  报告等待信号量的线程，以及线程在互斥锁或rw锁信号量上需要自旋或等待的次数的统计信息。大量等待信号量的线程可能是由于磁盘I/O或InnoDB内部的争用问题造成的。争用可能是由于查询的高度并行性或操作系统线程调度中的问题。
• LATEST FOREIGN KEY ERROR
  有关最新外键约束错误的信息。如果没有发生此类错误，则不存在。
• LATEST DETECTED DEADLOCK
  提供有关最近死锁的信息。如果没有发生死锁，则不存在。内容显示了涉及哪些事务、每个事务试图执行的语句、它们拥有和需要的锁以及InnoDB决定回滚哪个事务来打破死锁。
• TRANSACTIONS
  事务的锁争用信息。
• FILE I/O
  提供有关InnoDB用于执行各种类型I/O的线程的信息。这些线程的数量由innodb_read_io_threads和innodb_write_io_threads参数控制。
• INSERT BUFFER AND ADAPTIVE HASH INDEX
  显示InnoDB insert buffer（也称为change buffer）和自适应哈希索引的状态。
• LOG
  包括当前日志序列号、日志被刷新到磁盘的距离以及InnoDB最后一次执行检查点的位置。
• BUFFER POOL AND MEMORY
  有关读写页面的统计信息。您可以从这些数字中计算查询当前正在执行的数据文件I/O操作的数量。
• INDIVIDUAL BUFFER POOL INFO
  单独的缓冲区的读写页面的统计信息。
• ROW OPERATIONS
  主线程正在做什么，包括每种类型的行操作的数量和性能。

undo log

回滚日志的会回滚段有哪三部分组成：

• 系统表空间的undo logs存储块
• 撤销表空间的undo_xxx文件
• 临时表空间中：这部分回滚没有重做日志

支持最大回滚段128，其中默认96个给常规表，其余是临时表。
回滚段可支持的回滚槽多少具体由page size大小决定：slots = (InnoDB Page Size / 16)

undo log可支持事务总数？
由“每个事务的撤销日志数”和“撤销槽总数”共同决定

redo log

以磁盘顺序读写的方式记录了已提交和为提交事务的数据修改的物理存储，是innodb磁盘空间的存储块，作用于mysql服务器的崩溃恢复防止数据丢失。

• 只针对通常的表，临时表没有redo log日志
• 刷盘方式：innodb_flush_log_at_trx_commit 配置决定
• 文件：默认路径为/usr/local/mysql/data，文件为ibd_logfile0和ib_logfile1 两个
• 崩溃恢复：将redo log 中已完成的事务进行数据恢复，回滚未提交事务
• group commit：允许批量提交，但是XA协议下不允许批量提交

崩溃恢复具体过程：

1. 表空间发现：判断需要进行redo log程序的表空间，由此判断是否需要进行redo log 程序
   
   与其说是表空间发现，不如说是基于redo log的发现，此阶段会扫描redo logs日志自最后一次检查点开始的重做日志，检索发现索引收到redo日志影响的表空间。
   具体扫描redo logs日志的MLOG_FILE_NAME 记录，每条MLOG_FILE_NAME 记录就记录着表空间ID和文件名。根据所有的MLOG_FILE_NAME 记录打开响应的表空间及文件去进行下一阶段redo log 程序。
   
   
2. redo log 程序：崩溃恢复时进行数据恢复程序。可选快速启动或恢复不进行redo log程序，但不建议会丢失数据造成不一致。
   
   redo log 程序的作用就是把因为服务崩溃导致未成功刷盘的事务进行恢复。相当于把已提交事务前滚提交或重做。
   如果shutdown时，buffer pool数据都持久化到了表空间时，或redo日志缺失时，都不会进行redo log 程序。
   
   
3. 回滚未完成事务
   
   回滚未完成事务往往要花较长时间，可以通过启动参数控制是否进行回滚 innodb_force_recovery 。设置大于3时，会跳过回滚未完成事务阶段。
   
   
4. change buffer 合并
   对第二索引进行合并，写入磁盘，读入buffer pool
   
   
5. 清除
   清除删除标记的数据记录

Q & A

很多TCC的补偿原理中，为什么要在失败补偿时需要间隔一定时间呢？例如：一分钟

Q1. 什么情况下，mysql会拒绝重启？

磁盘严重损坏
系统错误

Q2. 当mysql磁盘损坏，如何恢复数据？

先冷备基础数据，再mysqlbinlog恢复热备份或时间点数据

Q3. 为什么需要redo log，而不直接选择存储到磁盘？

redo log

Q4. 如果发生表缺失等原因导致服务器无法启动无法重启冷备份数据如何处理？

innodb_force_recovery 设置为3以上，会关闭检验进程同时关闭redo log日志程序。最终会正常启动。

Q5. mysql如何RR保证幻读的？

间隙锁。RR的基础上支持间隙锁，同时保证了避免幻读时的并发性能。

Q6. 自增主键建议实现方式？

雪花算法、redis批量生成、zk分布式锁自定义生成

Q7. innodb、MyISAM存储引擎删除两个记录重启后自增主键的变化？

innodb删除后不重启，主键间隔的，两条删除的记录自增主键丢失。重启后则连续，不会丢失。
MyISAM无论是否重启，都是连续的。因为MyISAM表有最大记录的主键值可以直接使用。

Q8. 自增主键对应事务发生回滚，自增主键是否可以再被使用？

不可，发生回滚的自增主键丢失，不会被再次使用。

Q9. 检查死锁的工具有哪些？

• innodbtop
• SHOW ENGINE INNODB STATUS 输出日志的LATEST DETECTED DEADLOCK模块