《InnoDB 存储引擎》学习笔记

第1章 MySQL 体系结构和存储引擎

1.1 定义数据库和实例

• MySQL的跨平台特性：MySQL可以在多种操作系统上运行，如Linux、Solaris、FreeBSD、Mac和Windows。

• 数据库（database）：是物理操作系统文件或其他形式文件类型的集合。在MySQL中，数据库文件可以是frm、MYD、MYI、ibd结尾的文件。

• 实例（instance）：MySQL数据库由后台线程以及一个共享内存区组成。实例与数据库的关系通常是一对一的，但在集群情况下可能存在一个数据库被多个数据实例使用的情况。

• MySQL实例启动命令：

  ./mysqld_safe
  

• 查看MySQL进程：

  ps -ef | grep mysqld
  

1.2 MySQL 体系结构

• MySQL体系结构组成：
  • 连接池组件
  • 管理服务和工具组件
  • SQL接口组件
  • 查询分析器组件
  • 优化器组件
  • 缓冲（Cache）组件
  • 插件式存储引擎
  • 物理文件

1.3 MySQL 存储引擎

1.3.1 InnoDB 存储引擎

• 特点：
  • 支持事务、行锁、MVCC。
  • 从MySQL 5.5.8版本开始，默认存储引擎。
  • 支持数据和索引的独立存储。
  • 支持插入缓冲、二次写、自适应哈希索引等高级功能。
• 适用场景：适用于需要事务支持的OLTP应用。

1.3.2 MyISAM 存储引擎

• 特点：
  • 不支持事务、表锁设计。
  • 适合OLAP数据库应用。
  • 由MYD和MYI组成，MYD存放数据文件，MYI存放索引文件。
  • 支持全文索引。
• 适用场景：适用于读多写少的OLAP应用。

1.3.3 NDB 存储引擎

• 特点：
  • 集群存储引擎，类似于Oracle的RAC。
  • 数据全部放在内存中，提供高可用性和高性能。
  • 支持share nothing的集群架构。
• 适用场景：适用于需要高可用性和高性能的集群系统。

1.3.4 Memory 存储引擎

• 特点：
  • 将数据存储在内存中，速度快但易丢失。
  • 适合临时表和数据缓存。
  • 不支持TEXT和BLOB列类型。
• 适用场景：适用于需要快速访问的临时数据。

1.3.5 Archive 存储引擎

• 特点：
  • 仅支持INSERT和SELECT操作。
  • 使用zlib算法压缩数据。
  • 适合归档数据。
• 适用场景：适用于需要高压缩比的归档数据。

1.3.6 Federated 存储引擎

• 特点：
  • 不存储数据，指向远程MySQL服务器上的表。
  • 类似于SQL Server的链接服务器和Oracle的透明网关。
• 适用场景：适用于需要访问远程MySQL服务器上的数据。

1.3.7 Maria 存储引擎

• 特点：
  • 由MySQL创始人Michael Widenius开发。
  • 支持事务、MVCC、BLOB字符类型。
  • 适用于需要高性能和高可用性的OLTP应用。
• 适用场景：适用于需要高性能和高可用性的OLTP应用。

1.4 各存储引擎之间的比较

1.5 连接 MySQL

1.5.1 TCP/IP

• 连接命令：

  mysql -h <host> -u <user> -p
  

1.5.2 命名管道和共享内存

• Windows系统：

  • 使用命名管道连接：

    --enable-named-pipe
    

  • 使用共享内存连接：

    --shared-memory
    

1.5.3 UNIX 域套接字

• Linux和UNIX系统：

  • 使用UNIX域套接字连接：

    mysql -u <user> -S /tmp/mysql.sock
    

第2章 InnoDB 存储引擎

2.1 InnoDB 概述

1. 基本特性
   • ACID 事务支持：首个完整支持 ACID 的 MySQL 存储引擎（BDB 曾支持但已停止开发），支持行级锁、MVCC（多版本并发控制）、外键约束。
   • 设计目标：高效利用内存和 CPU，适用于 OLTP 场景，自 MySQL 5.5 起成为默认存储引擎。
   • 应用场景：大型网站（如 Google、Facebook）、网络游戏（如《魔兽世界》）广泛使用。
2. 版本演进

2.2 体系架构

1. 后台线程
   • Master Thread：核心线程，负责脏页异步刷新、插入缓冲合并、Undo 页回收等，版本演进中逐步优化刷新策略（如 1.0.x 引入innodb_io_capacity控制刷页数量）。
   • IO Thread：处理异步 IO 回调，1.0.x 后读写线程各增至 4 个，通过innodb_read/write_io_threads配置。
   • Purge Thread：1.1.x 后独立线程回收 Undo 页，1.2.x 支持多 Purge Thread 提升回收效率。
   • Page Cleaner Thread（1.2.x 引入）：独立处理脏页刷新，减轻 Master Thread 压力。
2. 内存结构
   • 缓冲池（innodb_buffer_pool_size）：缓存数据页、索引页、Undo 页、插入缓冲等，支持多实例（innodb_buffer_pool_instances）减少资源竞争。
   • LRU 列表优化：引入 midpoint 插入策略（innodb_old_blocks_pct控制比例），避免全表扫描污染热点数据；通过innodb_old_blocks_time控制页进入热端的延迟。
   • 重做日志缓冲（innodb_log_buffer_size）：默认 8MB，每秒刷新或事务提交时写入磁盘，确保持久性。
   • 额外内存池：分配数据结构内存（如缓冲控制块），需根据缓冲池大小调整。

2.3 关键特性

1. 插入缓冲（Insert Buffer/Change Buffer）
   • 原理：非唯一辅助索引插入时，若目标页不在缓冲池，先缓存到 Insert Buffer，后续合并以减少随机 IO。
   • 升级：1.0.x 引入 Change Buffer，支持 INSERT、DELETE、UPDATE 缓冲（需非唯一辅助索引），通过innodb_change_buffer_max_size控制最大内存占用（默认 25%）。
2. 两次写（Double Write）
   • 作用：防止部分写失效，脏页先写入共享表空间的 doublewrite buffer（2MB），再写入数据文件，提升数据可靠性。
3. 自适应哈希索引（AHI）
   • 自动优化：监控索引页访问频率，对热点页构建哈希索引，加速等值查询（O (1) 复杂度）。
4. 异步 IO（Async IO）
   • 优势：支持 Native AIO（Linux/Windows），合并多个 IO 请求，提升磁盘吞吐量，1.1.x 版本后默认启用。
5. 刷新邻接页（Flush Neighbor Page）
   • 机制：刷新脏页时同步刷新同区的脏页，机械硬盘场景提升效率，固态硬盘可通过innodb_flush_neighbors关闭。

2.4 启动与恢复

• 参数影响
  • innodb_fast_shutdown：0（完整清理）、1（默认，快速关闭）、2（仅写日志，启动时恢复）。
  • innodb_force_recovery：故障时强制恢复（1-6 级，跳过部分检查，需谨慎使用）。

第3章 文件

3.1 MySQL 核心文件类型

1. 参数文件（my.cnf）
   • 作用：配置实例启动参数（如内存大小、日志路径），分动态（运行中修改）和静态参数（需重启生效）。
   • 查看方式：SHOW VARIABLES或INFORMATION_SCHEMA.GLOBAL_VARIABLES。
2. 日志文件

3.2 InnoDB 存储引擎文件

1. 表空间文件
   • 共享表空间：默认ibdata1，存储系统数据、回滚段等，可通过innodb_data_file_path配置多文件。
   • 独立表空间：innodb_file_per_table=ON时，每个表生成.ibd文件（存储数据、索引），减少共享表空间膨胀。
2. 重做日志文件
   • 作用：记录 InnoDB 事务日志，用于崩溃恢复，默认ib_logfile0和ib_logfile1，循环写入。
   • 关键参数：innodb_log_file_size（单个文件大小，1.2.x 后最大 512GB）、innodb_log_files_in_group（每组文件数，默认 2）。
   • 写入策略：innodb_flush_log_at_trx_commit=1（提交时同步写磁盘，保证持久性）。

3.3 其他重要文件

• 表结构文件（.frm）：存储表定义（包括视图），文本格式，可直接查看。
• 套接字文件（socket）：UNIX 域套接字连接使用，默认/tmp/mysql.sock。
• PID 文件：记录实例进程 ID，默认位于数据目录。

3.4 总结

• InnoDB 优势：通过多线程架构、内存优化（缓冲池 / LRU）、事务可靠性特性（两次写 / 重做日志），成为 OLTP 首选引擎。
• 文件管理：二进制日志和重做日志是恢复与复制的核心，需合理配置大小和策略；独立表空间便于单表管理，但共享表空间仍存储系统关键数据。
• 版本差异：不同 InnoDB 版本在功能（如全文索引）和性能（如 AIO 支持）上有显著差异，需根据业务场景选择。

第4章 表

4.1 索引组织表

• InnoDB存储引擎中的表是基于主键顺序组织存放的，称为索引组织表。每张表都有一个主键（Primary Key）。
• 如果创建表时没有显式定义主键，InnoDB存储引擎会自动创建一个6字节大小的指针作为主键。
• 主键的选择基于定义的顺序，而不是创建表列的顺序。

4.2 InnoDB 逻辑存储结构

1. 表空间（Tablespace）：所有数据都逻辑地存放在一个空间中。

2. 段（Segment）：表空间由段组成，包括叶节点段、非叶节点段和回滚段。

3. 区（Extent）：段由区组成，每个区包含64个连续的页。

4. 页（Page）：区由页组成，页是InnoDB磁盘管理的最小单位，默认大小为16KB。

   • 数据页（B-tree Node）：存储实际数据的页。
   • Undo页（Undo Log Page）：用于存储事务回滚信息。
   • 系统页（System Page）：存储系统信息。
   • 事务数据页（Transaction system Page）：存储事务信息。
   • 插入缓冲位图页（Insert Buffer Bitmap）：用于管理插入缓冲区。
   • 插入缓冲空闲列表页（Insert Buffer Free List）：管理插入缓冲区的空闲页。
   • 未压缩的二进制大对象页（Uncompressed BLOB Page）：存储未压缩的BLOB数据。
   • 压缩的二进制大对象页（Compressed BLOB Page）：存储压缩后的BLOB数据。

5. 行（Row）：页中存储行记录，每行数据包括记录头信息和实际列数据。

   • InnoDB存储引擎是面向行的，每页最多存放16KB，即1024行记录。

4.3 InnoDB 行记录格式

1. Compact行记录格式：高效存储数据，一个页中存放的行数据越多，性能越高。

   • 变长字段长度列表：按列逆序存放。
   • NULL标志位：用1字节表示。
   • 记录头信息：固定占用5字节。
   • 列数据：实际存储每个列的数据。

2. Redundant行记录格式：兼容之前版本的页格式。

   • 字段长度偏移列表：按列逆序存放。

   • 记录头信息：固定占用6字节。

3. 行溢出数据

   • 当行数据超出页大小时，会存储在BLOB页中。

   • BLOB页可以存储大对象数据，如TEXT、VARCHAR等。

4.4 InnoDB 数据页结构

1. File Header（文件头）：记录页的一些头信息，共38字节。
2. Page Header（页头）：记录数据页的状态信息，共56字节。
3. Infimum和Supremum Records：虚拟行记录，用于限定记录的边界。
4. User Records（用户记录，即行记录）：实际存储行记录的内容。
5. Free Space（空闲空间）：页中未使用的空间。
6. Page Directory（页目录）：记录页中记录的相对位置。
7. File Trailer（文件结尾信息）：用于检测页的完整性。

4.5 Named File Formats 机制

• InnoDB 1.0.x版本引入了新的文件格式（file format），称为Barracuda文件格式。

• 新的文件格式支持Compacted和Dynamic行记录格式。

• 参数innodb_file_format指定文件格式，可以通过以下命令查看当前使用的InnoDB存储引擎的文件格式：

  mysql> SELECT @@version;
  mysql> SHOW VARIABLES LIKE 'innodb_version';
  mysql> SHOW VARIABLES LIKE 'innodb_file_format';
  

4.6 约束

4.6.1 约束类型

1. 数据完整性：关系数据库通过约束机制保证数据的完整性。

2. 约束类型：主键（Primary Key）、唯一键（Unique Key）、外键（Foreign Key）、默认值（Default）

4.6.2 约束的创建和查找

• 可以在创建表时定义约束；可以使用ALTER TABLE添加约束；可以通过CREATE INDEX创建唯一键约束。

• 使用 information_schema 架构下的 TABLE_CONSTRAINTS 和 REFERENTIAL_CONSTRAINTS 表查看约束信息。

  SELECT constraint_name, constraint_type
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE table_schema='mytest' AND table_name='u';
  

4.6.3 约束和索引的区别

1. 约束是逻辑概念，保证数据完整性。
2. 索引是数据结构，提高查询性能。

4.6.4 对错误数据的约束

• 通过设置 sql_mode 参数来控制错误数据的插入。

  STRICT_TRANS_TABLES：严格检查约束。

  SET sql_mode = 'STRICT_TRANS_TABLES';
  

4.6.5 ENUM和SET约束

• ENUM和SET用于限制列的取值范围。

  CREATE TABLE a (
     id INT,
     sex ENUM('male','female')
   );
  

4.6.6 触发器与约束

• 触发器可以在INSERT、UPDATE、DELETE操作之前或之后执行。

  CREATE TRIGGER tgr_Orders_insert
  AFTER INSERT ON Orders
  FOR EACH ROW
  BEGIN
     SET @old_price_sum = 0;
     SET @old_amount_sum = 0;
     SET @old_price_avg = 0;
     SET @old_orders_cnt = 0;
     SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0)
     FROM Orders_MV
     WHERE product_name = NEW.product_name
     INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
     SET @new_price_sum = @old_price_sum + NEW.price;
     SET @new_amount_sum = @old_amount_sum + NEW.amount;
     SET @new_orders_cnt = @old_orders_cnt + 1;
     SET @new_price_avg = @new_price_sum / @new_orders_cnt;
     REPLACE INTO Orders_MV
     VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);
  END;
  

4.8 分区表

4.8.1 分区概述

• 分区表将表或索引分解为多个更小、更易管理的部分。MySQL 5.1 版本开始支持分区。
• 分区表可以提高查询性能，特别是对于大数据量的表。

4.8.2 分区类型

1. RANGE 分区

   • 根据列的值范围进行分区。

     CREATE TABLE t (
        id INT) ENGINE=INNDB
     PARTITION BY RANGE (id) (
            PARTITION p0 VALUES LESS THAN (10),
            PARTITION p1 VALUES LESS THAN (20));
     

2. LIST 分区

   • 根据列的离散值进行分区。

     CREATE TABLE t (
         a INT,
         b INT) ENGINE=INNODB
     PARTITION BY LIST(b) (
         PARTITION p0 VALUES IN (1,3,5,7,9),
         PARTITION p1 VALUES IN (0,2,4,6,8));
     

3. HASH 分区

   • 根据列的哈希值进行分区。

     CREATE TABLE t_hash (
         a INT,
         b INT) ENGINE=InnoDB
     PARTITION BY HASH (YEAR(b))
     PARTITIONS 4;
     

4. KEY 分区

   • 使用MySQL提供的函数进行分区。

     CREATE TABLE t_key (
         a INT,
         b DATETIME) ENGINE=InnoDB
     PARTITION BY KEY (b)
     PARTITIONS 4;
     

5. COLUMNS 分区

   • 可以对多个列的值进行分区。

     CREATE TABLE t_columns_range(
         a INT,
         b DATETIME
       ) ENGINE=INODB
      PARTITION BY RANGE COLUMNS (b) (
         PARTITION p0 VALUES LESS THAN ('2009-01-01'),
         PARTITION p1 VALUES LESS THAN ('2010-01-01'));
     

4.8.3 子分区

• 子分区是在分区分的基础上再进行分区分。

  CREATE TABLE ts (a INT, b DATE) ENGINE=INODB
   PARTITION BY RANGE (YEAR(b))
    SUBPARTITION BY HASH(TO_DAYS(b))
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );
  

4.8.4 分区中的NULL值

• MySQL分区分区总是视NULL值小于任何非NULL值。

  CREATE TABLE t_range (
      a INT,
      b INT) ENGINE=innoDB
   PARTITION BY RANGE (b) (
       PARTITION p0 VALUES LESS THAN (10),
       PARTITION p1 VALUES LESS THAN (20),
       PARTITION p2 VALUES LESS THAN MAXVALUE
   );
  

4.8.5 分区和性能

• 分区可以提高查询性能，但需要根据具体情况进行设计。

  CREATE TABLE Profile (
      id int(11) NOT NULL AUTO_INCREMENT,
      nickname varchar(20) NOT NULL DEFAULT '',
      password varchar(32) NOT NULL DEFAULT '',
      sex char(1) NOT NULL DEFAULT '',
      rdate date NOT NULL DEFAULT '0000-00-00',
      PRIMARY KEY ('id'),
      KEY 'nickname' ('nickname')
  ) ENGINE=InnoDB
   PARTITION BY HASH (id)
   PARTITIONS 10;
  

4.8.6 在表和分区间交换数据

• 使用 ALTER TABLE ... EXCHANGE PARTITION 语句在表和分区间交换数据。

  CREATE TABLE e (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30)
  ) PARTITION BY RANGE (id) (
      PARTITION p0 VALUES LESS THAN (50),
      PARTITION p1 VALUES LESS THAN (100),
      PARTITION p2 VALUES LESS THAN (150),
      PARTITION p3 VALUES LESS THAN (MAXVALUE)
  );
  INSERT INTO e VALUES
  (1669, "Jim", "Smith"),
  (337, "Mary", "Jones"),
  (16, "Frank", "White"),
  (2005, "Linda", "Black");
  CREATE TABLE e2 LIKE e;
  ALTER TABLE e2 REMOVE PARTITIONING;
  ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
  

第5章 索引与算法

5.1 InnoDB 存储引擎索引概述

• InnoDB 存储引擎支持的索引类型：B+树索引、全文索引、哈希索引

5.2 数据结构与算法

5.2.1 二分查找法

• 二分查找法（binary search）用于在有序数组中查找特定元素。
• 通过不断将查找区间缩小一半，最终找到目标元素或确定其不存在。

5.2.2 二叉查找树和平衡二叉树

1. 二叉查找树（BST）：每个节点的左子树键值小于根节点，右子树键值大于根节点。
2. 平衡二叉树（AVL树）：任何节点的两个子树高度最大差为1。插入和删除操作可能导致树的不平衡，需要通过旋转操作来维护平衡。

5.3 B+树

5.3.1 B+树的插入操作

• B+树的插入操作需要保持节点的有序性和树的平衡。
• 插入操作可能涉及页的拆分和旋转操作。

5.3.2 B+树的删除操作

• B+树的删除操作需要维护树的平衡和节点的有序性。
• 删除操作可能涉及页的合并和索引页的更新。

5.3.3 B+树索引的分裂

• B+树的分裂操作发生在页满时，需要将记录分配到两个新页中。
• 分裂点的选择基于记录的插入位置和页的顺序信息。

5.3.4 B+树索引的管理

• 索引的创建和删除可以通过 ALTER TABLE 和 CREATE/DROP INDEX 实现。

5.4 B+树索引

5.4.1 聚集索引

• 聚集索引（clustered index）按照每张表的主键构建 B+树，叶子节点存放实际数据。
• 聚集索引决定了数据在表中的物理存储顺序。

5.4.2 辅助索引

• 辅助索引（secondary index）不包含实际数据，叶子节点包含键值和指向实际数据的指针。
• 辅助索引通过书签到聚集索引，找到对应的行数据。

5.4.3 辅助索引与聚集索引的关系

• 辅助索引的存在不影响数据在聚集索引中的组织。
• 每张表可以有多个辅助索引，通过叶级别的指针访问聚集索引。

5.4.4 B+树索引的管理

• 索引的管理包括创建、删除和修改。

5.5 Cardinality 值

5.5.1 什么是 Cardinality

• Cardinality 值表示索引中不重复记录的预估值。
• 高选择性（接近1）的索引通常更有效。
• 示例：通过 SHOW INDEX 查看表 t 的索引 Cardinality 值。

5.5.2 InnoDB 存储引擎的 Cardinality 统计

• InnoDB 通过采样方法统计 Cardinality 值。
• 示例：通过 SHOW INDEX 和 ORDER BY 观察 Cardinality 值的变化。

5.5.3 Cardinality 值的应用

• Cardinality 值用于优化查询执行计划。

5.6 B+树索引的使用

5.6.1 不同应用中B+树索引的使用

• B+树索引的使用需要根据具体应用场景进行判断。
  1. OLTP（在线事务处理）应用中，查询操作通常只涉及少量数据，适合使用索引。
  2. OLAP（在线分析处理）应用中，查询通常涉及大量数据，索引的选择和使用需要谨慎。

5.6.2 联合索引

• 联合索引是对多个列进行索引，适用于多列联合查询。
• InnoDB引擎中，联合索引使用哈希算法来提高查找效率。

5.6.3 覆盖索引

• 覆盖索引（或称索引覆盖）是指索引包含了查询所需的所有列，减少了对数据文件的访问。
• InnoDB引擎从1.2版本开始支持全文检索，使用倒排索引实现。

5.6.4 优化器选择不使用索引的情况

• 优化器可能会选择全表扫描而不是使用索引，特别是在范围查询和JOIN操作中。

5.6.5 索引提示

• 索引提示（INDEX HINT）用于显式指定优化器使用特定索引。
• 可以使用USE INDEX、FORCE INDEX、IGNORE INDEX等关键字来强制优化器使用或忽略索引。

5.6.6 Multi-Range Read优化

• Multi-Range Read（MRR）优化用于减少磁盘随机访问，提高顺序访问性能。
• InnoDB和MyISAM存储引擎支持MRR优化。

5.6.7 Index Condition Pushdown（ICP）优化

• ICP优化将WHERE条件的部分过滤操作放在存储引擎中进行，减少上层SQL层的负担。

5.7 哈希算法

• 哈希算法是一种常见的散列算法，时间复杂度为O(1)。
• InnoDB存储引擎使用哈希算法来处理字典查找。

5.8 全文检索

5.8.1 概述

• 全文检索是通过索引字段的前缀进行查找，适用于博客内容等文本数据。
• InnoDB存储引擎从1.2版本开始支持全文检索。

5.8.2 倒排索引

• 倒排索引存储单词与文档的映射关系，适用于全文检索。
• InnoDB全文索引使用full inverted index，包含单词和文档ID的对。

5.8.3 InnoDB全文检索

• InnoDB使用倒排索引和FTS Index Cache来提高全文检索性能。
• 文档的插入和删除操作分别记录在FTS Document ID和DELETED表中。

5.8.4 全文检索的限制

• 每张表只能有一个全文检索索引。
• 不支持没有单词界定符的语言。

第6章 锁

6.1 什么是锁

锁是数据库系统中用于管理对共享资源并发访问的关键机制。锁机制确保了数据的一致性和完整性，防止多个事务同时修改同一数据导致的数据不一致问题。

锁可以分为不同类型，包括：

1. 行锁：锁定数据库中的一行数据。
2. 表锁：锁定整个表。
3. 意向锁：表示事务希望在更细粒度上加锁。

锁的实现方式类似于Oracle数据库，但InnoDB提供了更细粒度的锁控制，如行级锁和表级锁。

6.2 lock与latch

1. lock：用于保护数据库内容，持续整个事务过程。锁的类型包括行锁、表锁和意向锁等。
2. latch：用于保护内存数据结构，持续时间短。latch的类型包括共享锁、互斥锁等。

InnoDB存储引擎中，可以通过命令查看latch和lock的信息：

• 查看latch：SHOW ENGINE INNODB MUTEX
• 查看lock：SHOW ENGINE INNODB STATUS

6.3 InnoDB存储引擎中的锁

InnoDB存储引擎实现了多种锁机制，包括：

• 共享锁（S Lock）：允许多个事务读取一行数据。
• 排他锁（X Lock）：允许事务删除或更新一行数据。

InnoDB支持多粒度锁（granular）锁，允许在行级和表级上加锁。此外，InnoDB还支持意向锁（Intention Lock），用于在更细粒度上进行加锁。

6.4 锁的算法

InnoDB存储引擎有3种行锁算法：

1. Record Lock：单个行记录上的锁。
2. Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
3. Next-Key Lock：结合了Gap Lock和Record Lock，锁定一个范围，并锁定记录本身。

Next-Key Lock是InnoDB默认的行锁算法，旨在解决Phantom Problem（幻读问题）。

Next-Key Lock通过锁定记录和记录之间的间隙，防止其他事务插入新的记录，从而避免了幻读问题。

6.5 锁问题

锁机制虽然提高了并发性，但也带来了一些问题：

1. 脏读（Dirty Read）：一个事务读取了另一个未提交事务的数据。
2. 不可重复读（Non-Repeatable Read）：一个事务多次读取同一数据集合，结果不一致。
3. 丢失更新（Lost Update）：一个事务的更新被另一个事务覆盖。

InnoDB通过Next-Key Lock算法避免了不可重复读的问题。脏读和丢失更新问题可以通过事务隔离级别和锁机制来解决。

6.6 阻塞

阻塞是指一个事务中的锁需要等待另一个事务中的锁释放，阻塞是确保事务可以并发且正常运行的必要机制。

InnoDB通过参数innodb_lock_wait_timeout控制等待时间，默认为50秒。

6.7 死锁

死锁是指两个或多个事务互相等待对方释放锁，导致事务无法继续执行。InnoDB通过 超时机制 和 wait-for graph算法 检测死锁。

wait-for graph是一种主动的死锁检测方式，通过构建事务等待链表和锁信息链表，检测是否存在回路，从而判断是否存在死锁。

6.8 锁升级

锁升级是指将当前锁的粒度降低。例如，数据库可以将行锁升级为页锁或表锁，以提高并发性能。锁升级可以减少锁的开销，提高系统的整体性能。

第7章 事务

7.1 认识事务

• 事务（Transaction）：数据库区别于文件系统的重要特性之一。事务是一组操作的集合，这些操作要么全部成功，要么全部失败。
• ACID特性：
  1. 原子性（Atomicity）：事务是不可分割的工作单位，要么全部成功，要么全部失败。
  2. 一致性（Consistency）：事务将数据库从一种一致状态转换为另一种一致状态。
  3. 隔离性（Isolation）：事务的隔离性要求每个事务的对象对其他事务不可见。
  4. 持久性（Durability）：事务一旦提交，其结果就是永久性的。

7.2 事务的实现

1. redo log：用于保证事务的原子性和持久性，记录的是页的物理操作。
2. undo log：用于保证事务的一致性，记录的是逻辑日志。

7.2.1 redo

• 基本概念：
  • redo log buffer：内存中的重做日志缓冲区。
  • redo log file：持久化的重做日志文件。
  • log block：重做日志块，由log block header和log block body组成。
• redo log的写入：
  • 事务提交时，必须将该事务的所有日志写入到重做日志文件进行持久化。
  • 为了保证每次日志都写入重做日志文件，InnoDB存储引擎需要调用一次fsync操作。

7.2.2 undo

• 基本概念：
  • undo log：记录了事务的行为，可以进行“重做”操作。
  • undo segment：undo log存放在数据库内部的一个特殊段中。
• undo存储管理：
  • InnoDB存储引擎对undo的管理采用段的方式。
  • 每个回滚段记录了1024个undo log segment。

7.2.3 purge

• 基本概念：
  • purge：用于最终完成delete和update操作，清理之前的delete和update操作。
• purge操作：
  • 不能在事务提交时立即进行处理，而是通过purge来进行判断和清理。

7.3 事务的分类

1. 扁平事务（Flat Transactions）
2. 带保存点的扁平事务（Flat Transactions with Savepoints）
3. 链事务（Chained Transactions）
4. 嵌套事务（Nested Transactions）
5. 分布式事务（Distributed Transactions）

7.4 事务的实现

• InnoDB存储引擎：
  • 支持扁平事务、带保存点的扁平事务、链事务、嵌套事务和分布式事务。
  • 通过redo log和undo log来实现事务的ACID特性。

具体实现细节

1. redo log的写入：事务提交时，日志写入重做日志文件，并调用fsync操作。

2. undo log的写入：事务进行中，undo log记录在undo segment中。

3. purge操作：清理不再需要的undo log，释放存储空间。

4. 创建表和存储过程：

CREATE TABLE test_load (
  a INT,
  b CHAR(80)
) ENGINE=INNODB;

DELIMITER //
CREATE PROCEDURE p_load(count INT UNSIGNED)
BEGIN
  DECLARE s INT UNSIGNED DEFAULT 1;
  DECLARE c CHAR(80) DEFAULT REPEAT('a', 80);
  WHILE s <= count DO
      INSERT INTO test_load SELECT NULL, c;
      COMMIT;
      SET s = s+1;
  END WHILE;
END //
DELIMITER ;

2. 查看LSN（Log Sequence Number）：

SHOW ENGINE INNODB STATUS\G;

3. 查看undo信息：

SELECT * FROM information_schema.INNODB_TRX_UNDO\G;

4. 查看rollback segment信息：

SELECT segment_id, space, page_no FROM INNODB_TRX_ROLLBACK_SEGMENT\G;

7.5 事务控制语句

1. 自动提交：在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后会马上执行COMMIT操作。
2. 显式开启事务：使用命令BEGIN、START TRANSACTION或设置SET AUTOCOMMIT=0来显式开启一个事务。
3. 提交事务：使用COMMIT命令来提交事务，使得已对数据库做的所有修改成为永久性的。
4. 回滚事务：使用ROLLBACK命令来回滚事务，撤销正在进行的所有未提交的修改。
5. 保存点：使用SAVEPOINT identifier命令在事务中创建一个保存点，可以通过ROLLBACK TO SAVEPOINT来回滚到某个保存点。
6. 删除保存点：使用RELEASE SAVEPOINT identifier命令删除一个事务的保存点。
7. 设置事务隔离级别：使用SET TRANSACTION命令来设置事务的隔离级别。

7.6 隐式提交的SQL语句

1. DDL语句：如ALTER DATABASE、CREATE TABLE等，执行这些语句后，会有一个隐式的COMMIT操作。
2. 修改MySQL架构的操作：如CREATE USER、DROP USER等，也会隐式提交。
3. 管理语句：如ANALYZE TABLE、CHECK TABLE等，同样会隐式提交。

7.7 对于事务操作的统计

• 事务统计：InnoDB存储引擎支持事务，因此需要关注每秒事务处理的能力（TPS）。
• 统计方法：计算TPS的方法是（com_commit+com_rollback）/time。
• 参数：handler_commit和handler_rollback用于统计事务操作。

7.8 事务的隔离级别

1. SQL标准隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。
2. InnoDB默认隔离级别：REPEATABLE READ，相当于SQL标准的SERIALIZABLE。
3. 设置隔离级别：可以使用命令SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL来设置当前会话或全局的事务隔离级别。

7.9 分布式事务

• XA事务：InnoDB存储引擎提供了对XA事务的支持，通过XA事务来支持分布式事务的实现。
• 分布式事务模型：由一个或多个资源管理器、一个事务管理器以及一个应用程序组成。
• XA事务的SQL语法：XA {START|BEGIN} xid [JOIN|RESUME]等。

第8章 备份与恢复

8.1 备份分类与核心概念

1. 备份类型
   • 按操作状态：热备（在线备份，如 XtraBackup）、冷备（离线备份，复制物理文件）、温备（半在线，加全局读锁）。
   • 按内容形式：逻辑备份（SQL 语句 / 数据，如 mysqldump）、物理备份（裸文件复制，如 ibbackup）。
   • 按范围：完全备份、增量备份（XtraBackup 支持真正增量）、日志备份（二进制日志）。
2. 一致性备份
   • InnoDB 支持 MVCC，通过--single-transaction选项在事务中导出数据，确保一致性。
   • 避免分事务操作（如扣费与道具插入分离），防止备份不一致。

8.2 常用备份方法

1. 冷备（物理备份）
   • 步骤：停止数据库，复制.frm、共享表空间（ibdata1）、独立表空间（.ibd）、重做日志（ib_logfile*）。
   • 优缺点：简单快速，恢复无需重建索引；文件体积大，跨平台需注意格式差异。
2. 逻辑备份
   • mysqldump：支持--single-transaction（InnoDB 专用）、--master-data（复制场景）、--where（条件导出），导出内容含建表和插入语句。
   • SELECT INTO OUTFILE：导出数据为文本，需注意字段分隔符和权限（FILE权限），恢复用LOAD DATA INFILE。
3. 热备与增量备份
   • XtraBackup：开源热备工具，支持增量备份（基于 LSN 比较页修改），步骤为全备→增量备→应用日志→恢复文件。
   • ibbackup：官方热备工具（收费），原理类似 XtraBackup，复制表空间并记录 LSN 区间。
4. 快照备份
   • 利用 LVM、ZFS 等文件系统快照功能，对数据库所在分区创建写时复制（CoW）快照，快速备份且不影响业务。

8.3 日志备份与复制

1. 二进制日志（binlog）
   • 作用：记录数据更改，用于 Point-in-Time 恢复和复制，需启用log-bin、sync_binlog=1、innodb_support_xa保证一致性。
   • 恢复工具：mysqlbinlog解析日志，结合--start-position/--stop-datetime指定恢复区间。
2. 复制（Replication）
   • 原理：主库写 binlog→从库 IO 线程复制到中继日志→SQL 线程应用日志，异步实时同步，存在主从延迟。
   • 监控：通过SHOW SLAVE STATUS查看Seconds_Behind_Master，启用从库read-only防止误操作。

8.4 最佳实践

• 备份策略：定期全备 + 增量备 + 日志备份，远程异地容灾（如跨机房备份）。
• 工具选择：OLTP 场景首选 XtraBackup（支持增量），逻辑迁移用 mysqldump，复制结合快照防止误操作。

第9章 性能调优

9.1 硬件与架构优化

1. CPU 选择
   • OLTP 特性：事务短、索引查询为主，CPU 核心数影响并发处理，优先 64 位 CPU 支持大内存。
   • InnoDB 适配：1.2 + 版本支持多核，调整innodb_read/write_io_threads充分利用 CPU 多核性能。
2. 内存配置
   • 缓冲池（InnoDB Buffer Pool）：直接影响性能，建议大小为活跃数据的 1.5-2 倍，通过SHOW GLOBAL STATUS计算命中率（应≥99%）。
   • 参数参考：innodb_buffer_pool_size设为物理内存的 60%-80%，多实例innodb_buffer_pool_instances减少竞争。
3. 存储介质
   • 机械硬盘：RAID10 兼顾速度与冗余，启用 RAID 卡 Write Back 功能（需 BBU 电池备份）。
   • 固态硬盘（SSD）：随机访问快，禁用innodb_flush_neighbors减少邻接页刷新，调大innodb_io_capacity（如 8000-10000）。

9.2 RAID 与文件系统

1. RAID 类型对比

   RAID 类型	优势	适用场景
   RAID10	读写性能强，高可用性	OLTP 核心库
   RAID5	空间利用率高，中等性能	非核心库或读多写少场景
   RAID0	纯性能，无冗余	测试环境

2. 文件系统选择

   • Linux 推荐 EXT4/XFS（稳定性优先），Solaris 推荐 ZFS（自带快照），Windows 用 NTFS。
   • 避免过度纠结文件系统性能差异，关注mount参数（如noatime减少元数据更新）。

9.3 基准测试工具

1. sysbench
   • 功能：测试 CPU、磁盘 IO、OLTP 性能，支持多线程模拟负载。
   • OLTP 测试：生成大表（如--oltp-table-size=80000000），关注 TPS（事务 / 秒）和响应时间百分位（如 95% latency）。
2. mysql-tpcc
   • 标准：遵循 TPC-C 规范，模拟复杂 OLTP 场景（订单处理、库存管理等）。
   • 指标：tpmC 值（事务处理能力），重点测试 New Order 事务占比（≥43%）。

9.4 关键参数与最佳实践

• 磁盘 IO：innodb_io_capacity=200（机械盘）→8000（SSD），innodb_flush_neighbors=0（SSD）/1（机械盘）。
• 日志策略：innodb_flush_log_at_trx_commit=1（强一致性），sync_binlog=1（配合 XA 保证日志与数据同步）。
• 监控重点：缓冲池命中率、磁盘 IO 利用率（iostat 查看%util）、主从延迟（复制场景）。

第10章 InnoDB 源代码编译与调试

10.1 源代码获取与结构

1. 获取方式
   • 从 MySQL 官网下载对应版本源代码（GA 版或开发版），解压后storage/innobase为 InnoDB 源码目录。
   • MySQL 5.1 需注意innobase（旧版）和innodb_plugin（新版）文件夹，编译时需重命名插件版为innobase。
2. 目录结构
   • 核心模块：btr（B + 树）、buf（缓冲池）、dict（数据字典）、trx（事务）、log（重做日志）。
   • 工具类：ut（通用工具）、os（操作系统封装）、sync（互斥量实现）。

10.2 编译与调试环境

1. Windows 平台（Visual Studio）
   • 工具：CMake 生成工程文件，配置WITH_INNOBASE_STORAGE_ENGINE启用 InnoDB。
   • 步骤：运行win/configure.js→build-vsx.bat生成解决方案，设置mysqld为启动项目，断点调试 master thread 等核心逻辑。
2. Linux 平台（Eclipse）
   • 工具链：使用BUILD/compile-amd64-debug-max-no-ndb生成 Make 文件，导入 Eclipse 创建 C++ 项目。
   • 调试：配置调试参数（如--datadir），通过 GDB 或 Eclipse 内置调试器设置断点，追踪 InnoDB 内部函数（如buf_flush_get_desired_flush_rate）。
3. 跨平台（cmake）
   • 命令：cmake .. -GXcode（Mac OSX 生成 Xcode 工程），简化编译流程，支持多平台快速构建。

10.3 调试重点与扩展

• 核心流程：跟踪master_thread刷新脏页、purge_thread回收 Undo 页、page_cleaner_thread独立刷页逻辑。
• 扩展开发：基于example_storage_engine模板，修改 InnoDB 源码（如优化插入缓冲算法），需注意版本兼容性和测试。

10.4 注意事项

• 版本匹配：确保编译环境与目标 MySQL 版本一致，开发版需关注未稳定特性。
• 调试安全：在测试环境进行源码修改，备份原始代码，通过单元测试验证功能正确性。