InnoDB基础

呵离

墨衍会员 · AI 创作全网分发码龄12年

关注

原创最新推荐文章于 2026-08-08 10:09:35 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

Mplus—交叉滞后面板模型（Cross-Lagged Panel Model, CLPM）本文介绍交叉滞后模型相关知识，以及显变量模型与潜变量模型在Mplus中的语法。阅读详情

常用命令

配置文件位置
mysql --help | grep my.cnf
数据库所在路径
my.cnf中的datadir参数, 或在mysql中, 通过show variables like 'datadir';
show engines 显示mysql支持的引擎;
通过unix域套接字来连接mysql

// .sock文件路径
show variables like 'socket';
// -S /xxx  练级
mysql -uxxx -S /tmp/mysql.sock

异步IO(AIO)
SHOW ENGINE INNODB STATUS,
线程列表 show processlist
删除表
DELETE FROM tbl [where]; 慢, 返回个数;
TRUNCATE TABLE tbl; 快, 返回0;
查看表的行, 内存, 索引大小

SELECT table_name, table_rows, data_length FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

INSERT INTO tbl(x,x,x) SELECT x,x,x FROM ...
显示索引

show index from tbl;
show create table tbl;

innodb相关介绍

主要面向OLTP应用; (MyISAM, 不支持事务, 表锁设计, 全文index, 适合OLAP应用), (Memory, HEAP存储引擎, 数据放于内存中, 适合于临时数据表, 速度快)
MVCC实现高并发;
4中隔离级别, 默认REPEATALBE READ, 即事务中, 重复执行一个SQL其结果是一样的, 不会受其它SQL的影响;
采用聚集(clustered)方式, 主键按顺序存放, 没有制定主键, 则自动生成6字节的ROWID列作为主键;

体系架构

线程

master 线程
4 Read 线程, 4 Write线程,
1 insert buffer线程, 1 log线程
purge线程gmail
回收无用的undo log页, innodb_purge_threads设置purge线程的数量, 独立的线程减轻mster线程的压力;

内存

缓冲池

innodb_buffer_pool_size为缓冲池大小
包括数据页, 索引页, 插入缓冲, 自适应哈希索引, lock info等; 还有其它缓冲如redo log 缓冲;
innodb_buffer_pool_instances可以设置缓冲池数量;
information_schema表查看缓冲池情况

SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES FROM INNFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

缓冲池管理算法LRU, Free List, Flush List

页默认大小16KB;
LRU Latest Recent Used, 最近最少使用, 最频繁使用的在LRU前端, 最少使用的在链表尾部, 当缓冲池Free List没有空间时, 释放LRU的尾部页, 数据放入链表头;
Midpoint机制
1. Innodb对LRU算法做了优化, 通过innodb_old_blocks_pct设置midpoint点, 新的页不是放到尾部, 而是midpoint位置, midpoint前面为new列表, 后面为old列表, 理解为new为活跃数据, old为非活跃数据;
2. 该算法是为了防止某些情况下, 新页数据被频繁刷出, 影响性能, 如扫描操作, 一般都是临时读取数据, 如果页放在链表头, 可能会将所有的热点数据刷出LRU链表;
减少innodb_old_blocks_pct可以减少热点数据被刷出的频率;
pages made young
页从old加入到new时, 即转变为热门数据;
通过表查询:

SELECT POOL_ID, HIT_RATE, PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNG FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

Flush List

LRU中修改过的页即脏页dirty page, 内存数据功能磁盘数据不一致, 这是需要通过checkpoint机制将脏页刷新回磁盘;
Flush List即脏页列表, 脏页即存在与LRU与存在与Flush List
查看LRU中的脏页:

SELECT TABLE_NAME, SPACE, PAGE_NUMBER, PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION>0;

重做日志缓冲

redo log先放入这个缓冲, 再fsync到磁盘;
innodb_log_buffer_size, 默认8MB;
fsync时机:
1. master线程每秒执行一次;
2. COMMIT操作;
3. innodb_log_buffer_size空间小于1/2;

checkpoint机制

将脏页刷新回磁盘.

sharp checkpoint即服务器关机时将所有的脏页刷新回磁盘
fuzzy checkpoint 运行时只刷新一部分脏页;
fuzzy checkpoint时机:
1. Master Tread Checkpoint
  master线程每1或10秒刷新回一部分脏页;
2. FLUSH_LRU_LIST Checkpoint
  innodb_lru_scan_depth表示LRU列表中可用页的数量, 默认1024, 如果少于这个值, 则会启动checkpoint;
3. Async/Sync Flush Checkpoint
  若redo_lsn-checkpoint_lsn达到总redo日志大小一定比例时, 就会启动;
4. DIrty Page too much Checkpoint
  缓冲池中脏页数量占据75%时启动;

关键特性

插入缓冲(insert buffer / change buffer)

innodb主键是行唯一标识符, 聚集索引的插入一般是顺序的, 不需要随机读取, 速度非常快;
如果表中有非唯一的辅助索引, 则此刻插入可能需要随机读取非聚集索引页, 所以这里用了insert buffer来解决这个问题;
对于非聚集索引或非唯一索引, 其插入或更新操作, 不是每次都直接插入到索引页中, 而是判断插入的非聚集索引页是否在缓冲池中, 在的话则直接插入, 没有则先放到一个insert buffer中. 然后再以一定的频率将insert buffer与辅助索引页的子节点的meige操作.
change buffer是insert buffer的升级, 包括insert buffer, delete buffer, pruge buffer;

double write

partial page write
数据库在写入某页到表时发生宕机, 且只写了一半, 此时可能无法通过redo log来恢复, 这里就需要用到doble write来解决;
doublewirte buffer与物理磁盘共享表空间128页空间(2个区extent)
对缓冲池的脏页进行刷新, 不是直接写磁盘, 而是通过memcpy复制到内存的doublewrite buffer, 在分两次, 每次1MB(64个页)顺序的写入到共享表空间的物理磁盘上, 然后马上调用fsync进行磁盘同步;
SHOW GLOBAL STATUS LIKE 'innodb_dblwr%';
其中, innodb_dblwr_written实际写入的页数, innodb_dblwr_writes为写入的次数, 正常两者为64:1, 如果远小于64:1, 则说明写入压力不大;

启动, 关闭, 恢复

关闭

参数innodb_fast_shutdown:可以设为0,1,2;

innodb_fast_shutdown=0
关闭时, 完成所有的full purge 和 merge insert buffer, 并将所有的脏页刷新回磁盘; 数据量大时, 可能非常耗时, 可以先将参数设置为2, 在服务器下次启动时会执行恢复;
innodb_fast_shutdown=1
默认配置, 不完成full purge 和 merge insert buffer, 但要将所有的脏页刷新回磁盘;
innodb_fast_shutdown=1
不完成full purge 和 merge insert buffer, 也不刷新脏页回磁盘; 只将日志文件写回日志文件, 保证事务不会丢失, 下次启动时完成恢复;

恢复

参数innodb_force_recovery

innodb_force_recovery=0, 默认为0, 启动时执行所有恢复;

有时不需要服务器自己执行恢复, 因为很慢, 我们可以自定导数据到新表;
innodb_force_recovery=1, SRV_FORCE_IGNORE_CORRUPT, 忽略检查的corrupt页;
innodb_force_recovery=2, SRV_FORCE_NO_BACKGROUND,阻止master thread运行(会执行full purge, 导致crash);
innodb_force_recovery=3, SRV_FORCE_NO_TRX_UNDO, 不进行事务回滚;
innodb_force_recovery=4, SRV_FORCE_NO_IBUF_MERGE, 不进行插入缓冲的合并操作;
innodb_force_recovery=5, SRV_FORCE_NO_UNDO_LOG_SCAN, 不查看撤销日志, 会将为提交的事务作为已提交;
innodb_force_recovery=6, SRV_FORCE_NO_LOG_REDO, 不进行前滚操作;

慢查询

`mysqldumpslow`

执行时间最长的10条SQL语句

mysqldumpslow -s al -n 10 xxx.log

慢查询记录到表中

参数log_output默认为FILE, 可以配置为TABLE;
查询slow_log表:

SELECT * FROM mysql.slow_log;

分区

procedure

DELIMITER $$
CREATE PROCEDURE()
BEGIN
	DECLARE dt DATE;
	DECLARE ds DATE;
	DECLARE de DATE;
	DECLARE dInt INT;
	SET ds = DATE(20170310);
	SET de = DATE(20170320);
	SET dt = ds;
	WHERE dt <=de DO
		SET dInt = YEAR(dt);
		Call trans_data('alph','dInt');  # 调用过程
		..
		SET dt = DATE_ADD(dt, INTERVAL | DAY);
	END WHILE;
END; $$
DELIMITER ;

4种隔离级别

READ_UNCOMMITTED
可以读取到其它事务未提交的修改;
READ_COMMITTED
在同一事务中, 能读取事务中已经修改过的数据(隔离性Isolation没有满足), 但未提交前, 修改对其它事务不可见;
REPEATABLE_READ
只会读取到已提交的结果, 同一事务同一SQL语句查询的结果永远一致(满足Isolation特性);
SERIALIZABLE
与REPEATABLE类似, 如果autocommit=0, 则SELECT会自动转化为SELECT .... LOCK IN SHARE MODE;, 会对SELECT加上S锁, 造成阻塞;

锁

MyISAM

表级锁;

InnoDB

行级锁, 表级锁

latch
底层锁, 如mutex, rwlock, 轻量级, 时间短;
lock
事务级锁, 一般在COMMIT, ROLLBACK后释放, 有死锁机制;

innodb lock类型

S Lock, 共享锁, 用于读;
X Lock, 排它锁, 用于写;

表级锁(意向锁)

IS Lock, 事务要获得一张表中某几行的共享锁;
IX Lock, 事务要获得一张表中某几行的排它锁;

一致性锁定读

innodb默认隔离级别为REPEATABLE READ, 为一致性非锁定读, 所以某些情况下需要用户通过主动枷锁的方式来保证逻辑一致性, 实现一致性锁定读;

加X Lock

SELECT ... FOR UPDATE;

加S Lock

SELECT ... LOCK IN SHARE MODE

幻读Phantom Problem

在同一事务下, 连续执行两次同样的SQL语句, 可能导致不同的结果. 第二个SQL可能返回与第一个SQL不存在的行;

这个特效使得其不满足I特性; mysql通过next-key lock来解决; 如数据中有id=1,2,5, 执行

SELECT * FROM t WHERE id>2 FOR UPDATE;

这里id>2只有5, 而由于next-key lock存在, 这里锁住的不仅仅时id=5, 而是id>2, 所以如果此时执行INSERT INTO t id=4是会被阻塞的;

锁的问题

脏数据
事务对缓冲池进行修改却还没有提交, 如果另一个事务读到了该未提交的数据, 即违背了隔离性I, 就是读到了脏数据;
幻读(不可重复读)
一个事务中两次相同的读取结果不一样, 其与脏读的区别是, 幻读读到是另一个已经提交了的事务的修改;
更新丢失
两个事务同时更新, 一个被另一个覆盖; 可以通过X Lock解决;

锁是的事务阻塞, 其阻塞时间为参数innodb_lock_wait_timeout

事务

innodb的事务满足ACID特性: Automicity, Consistency, Isolation, Durability
事务是数据库从一种状态转变到另一种状态, 提交事务时, 要么所有的修改都已提交, 要么所有的都没保存;
Isolation: 针对并发, 即一个事务在提交前其修改对其它事务是不可见的;
Durability: 锁一旦提交, 其修改是永久的, 即使发生宕机, 也能恢复;

事务启动

BEGIN
START TRANSACTION
SET AUTOCOMMIT=0

隐式提交SQL

DML: ALTER TABLE, CREATE TABLE等;
管理语句: ANALYZE TABLE, OPTIMIZE TABLE等;

事务实现

隔离性Isolation

通过锁来实现

持久性Durability

重做日志分为: redo log buffer, redo log file, 重做日志需要先写到缓冲中, 在fsync到磁盘;
Force log at commit
当事务提交时, 必须将事务的所有日志写到重做日志, 进行持久化;
undo log
帮助事务回滚, 以及MVCC功能;

innnodb_flush_log_at_trx_commit

有三个值可选, 0, 1, 2. 默认为1;

0: commit提交时. 不执行fsync, 有master线程每个1s执行;
1: 默认, 事务提交时, 执行fsync;
2: commit时, 写入文件系统缓冲, 不执行fsync;

LSN(Log Sequence Number) 日志序列号

show engine innodb status里有如下值:

Log
Log Sequence Number 当前redo log buffer的LSN
Log Flushed up to 刷新到重做日志文件的LSN
Last Checkpoint at 磁盘的LSN

由于默认autocommit=1, 所以对于大量的insert操作, 应该放入一个事务中, 同意commit, 减少fsync次数;

事务类型

扁平事务Flat Transaction

BEGIN WORK开始, COMMIT WORK 或 RollBack WORK结束;
保存点: SAVEPOINT, 可以回滚到的指定的保存点;
ROLLBACK WORK: 2, 回滚到保存点2, 此时事务仍然活跃, 由Rollback work来结束;

链事务Chained Transaction

SAVEPOINT是易失的, 没有完成COMMIT, 当系统崩溃时, 数据会消失. 事务链, 就是阶段性的执行COMMIT操作, 其事务提交与开始一个新的事务是原子操作, 同时能够保留上一个事务的环境.

嵌套事务Nested Transaction

嵌套事务中, 事务的回滚操作会将子事务一同回滚. 子事务可以提交也可以回滚, 但提交操作只会在父事务提交时才会真正执行;

undo log

作用:

ROLLBACK回滚
undo log并不是将数据库物理的恢复, 而是针对某个事务执行其逻辑恢复. 因为并发, 执行回滚后, 数据库的物理数据与事务开始时会不一样;
MVCC
如果某一事务读取某一行时发现, 已被其它事务占用, 可以通过undo读取到其占用前的数据, 以实现MVCC;
同理事务提交后, 并不能直接删除undo log, 因为可能还有其它的事务需要, 所以会将undo log放到一个链表中, 有purge线程执行最终的删除;
show engine innodb status命令下的History List Length显示的是undo log 的数量;