《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 各存储引擎之间的比较

特性	MyISAM	BDB	内存	InnoDB	归档	NDB
存储限制	否	否	是	64TB	否	是
事务（提交、回滚等）	否	是	否	是	否	是
锁定粒度	表	页	表	行	行	行
MVCC/快照读取	否	是	是	是	否	是
地理空间支持	否	否	否	是	否	否
B树索引	是	是	是	是	否	是
哈希索引	否	是	是	是	否	是
全文搜索索引	是	否	否	是	否	否
聚簇索引	否	是	否	是	否	是
数据缓存	是	是	是	是	否	是
索引缓存	是	是	是	是	否	是
压缩数据	否	是	否	是	是	是
加密数据（通过函数）	否	是	否	是	否	是
存储成本（空间使用）	低	低	不适用	高	非常低	低
内存成本	低	低	中等	高	低	高
批量插入速度	高	高	高	低	非常低	高
集群数据库支持	否	是	否	是	否	是
复制支持	是	是	否	是	是	是
外键支持	否	是	否	是	否	是
备份/点时间恢复	是	是	是	是	是	是
查询缓存支持	是	是	是	是	否	是
数据字典更新统计	是	是	是	是	否	是

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. 版本演进

版本	关键功能
老版本 InnoDB	支持 ACID、行锁、MVCC
1.0.x（InnoDB Plugin）	新增压缩页（compress/dynamic 页格式）
1.1.x	支持 Linux Native AIO、多回滚段，提升并发事务处理能力
1.2.x	支持全文索引、在线索引添加、Page Cleaner Thread 优化脏页刷新

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. 日志文件

日志类型	功能	关键参数
错误日志	记录启动、运行、关闭错误及警告	log_error（路径）
慢查询日志	记录执行时间超过阈值（long_query_time，默认 10 秒）或未使用索引的 SQL	log_slow_queries（启用）、log_queries_not_using_indexes
二进制日志（binlog）	记录数据更改操作（不包括 SELECT），用于恢复和复制	log_bin（启用）、binlog_format（STATEMENT/ROW/MIXED）
查询日志	记录所有请求（包括失败操作）	log_output（FILE/TABLE）

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）

特性	主键（Primary Key）	唯一键（Unique Key）	外键（Foreign Key）
唯一性	必须唯一，且不能为NULL	必须唯一，但可以为NULL	可以重复，值必须是被引用表中主键或唯一键的有效值，或者NULL
数量限制	一个表只能有一个主键	一个表可以有多个唯一键	一个表可以有多个外键
主要作用	唯一标识表中的记录，方便对数据进行操作	保证数据的唯一性，作为备选键	建立表之间的关联关系，维护数据的完整性
示例	学生编号（StudentID）	身份证号（IDNumber）	班级编号（ClassID）

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 版本一致，开发版需关注未稳定特性。
• 调试安全：在测试环境进行源码修改，备份原始代码，通过单元测试验证功能正确性。