Skip to main content

MySQL

8/9/25About 49 min

InnoDB 存储引擎

基础

关系型数据库、SQL

关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

常见的关系型数据库：MySQL、PostgreSQL、Oracle、SQL Server、SQLite。

SQL 是一种结构化查询语言。目的是提供一种从数据库中读写数据的简单有效的方法。

MySQL

MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。

优点

  1. 成熟稳定，功能完善。
  2. 开源免费。
  3. 文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。
  4. 开箱即用，操作简单，维护成本低。
  5. 兼容性好，支持常见的操作系统，支持多种开发语言。
  6. 社区活跃，生态完善。
  7. 事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且，InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。
  8. 支持分库分表、读写分离、高可用。

字段类型

MySQL日期类型选择建议 | JavaGuide

MySQL 字段类型可以简单分为三大类：

  • 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
  • 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
  • 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

分类字段类型大小常见用途
整数型TINYINT1 字节年龄、状态码、布尔值等
SMALLINT2 字节人口、库存等小数值
MEDIUMINT3 字节特定范围的计数值
INT / INTEGER4 字节订单号、用户ID等
BIGINT8 字节高精度ID、日志记录等
小数型FLOAT4 字节温度、评分等近似数值
DOUBLE8 字节更高精度的科学计算
DECIMAL(p,s)可变金额、财务数据等高精度
字符串CHAR(n)固定 n 字节性别、固定长度编码等
VARCHAR(n)n+1 字节姓名、地址、邮箱等文本
TEXT最大 64KB评论、文章正文等大文本
时间型DATE3 字节出生日期、创建日期等
DATETIME8 字节精确时间记录
TIMESTAMP4 字节自动记录操作时间
TIME3 字节表示某个时间点（时分秒）
YEAR1 字节出生年份、注册年份等
其他ENUM(...)1~2 字节性别、状态、分类等固定选项
SET(...)1~8 字节标签、权限集合等

整数类型的 UNSIGNED 属性

MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

不推荐使用TEXT和BLOB

TEXT是长文本数据，例如博客内容。BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

在日常开发中，很少使用 TEXT 类型，但偶尔会用到，而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足，建议避免使用 TEXT。

数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如：

  • 不能有默认值。
  • 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）。
  • 检索效率较低。
  • 不能直接创建索引，需要指定前缀长度。
  • 可能会消耗大量的网络和 IO 带宽。
  • 可能导致表上的 DML 操作变慢。

避免使用 ENUM 类型

  • 修改 ENUM 值需要使用 ALTER 语句。
  • ENUM 类型的 ORDER BY 操作效率低，需要额外操作。
  • ENUM 数据类型存在一些限制，比如建议不要使用数值作为 ENUM 的枚举值。

有时不建议使用 NULL

原因主要包括以下几点：

  1. 影响索引效率 对含有 NULL 的列建索引时，NULL 不参与某些类型的索引优化。例如，在复合索引中，如果某一列是 NULL，可能导致索引失效，查询变慢。
  2. 查询逻辑复杂NULL 表示“未知”，不是普通的值，不能用 =、!= 比较，只能用 IS NULL、IS NOT NULL，这会导致 SQL 语句更加复杂、容易出错。
  3. 聚合函数和统计结果受影响 像 COUNT(col) 不会统计 NULL 值，而 COUNT(*) 会，开发中容易误解；类似的 AVG(col)、SUM(col) 等函数遇到 NULL 时也需要额外处理。
  4. 额外的存储和处理开销 每个包含 NULL 的列，MySQL 在内部需要额外的标志位记录其是否为 NULL，在数据量大时会影响存储效率和性能。
  5. 违反业务语义 有些字段本可以通过默认值表达“未设置”或“未知”状态，不一定非要用 NULL，使用明确的默认值（如 0、空字符串）更易于理解和处理。

手机号存储用VARCHAR

存储手机号，强烈推荐使用 VARCHAR 类型，而不是 INT 或 BIGINT。

主要原因如下：

  1. 格式兼容性与完整性：
    • 手机号可能包含前导零（如某些地区的固话区号）、国家代码前缀（'+'），甚至可能带有分隔符（'-' 或空格）。INT 或 BIGINT 这种数字类型会自动丢失这些重要的格式信息（比如前导零会被去掉，'+' 和 '-' 无法存储）。
    • VARCHAR 可以原样存储各种格式的号码，无论是国内的 11 位手机号，还是带有国家代码的国际号码，都能完美兼容。
  2. **非算术性：**手机号虽然看起来是数字，但我们从不对它进行数学运算（比如求和、平均值）。它本质上是一个标识符，更像是一个字符串。用 VARCHAR 更符合其数据性质。
  3. 查询灵活性：
    • 业务中常常需要根据号段（前缀）进行查询，例如查找所有 "138" 开头的用户。使用 VARCHAR 类型配合 LIKE '138%' 这样的 SQL 查询既直观又高效。
    • 如果使用数字类型，进行类似的前缀匹配通常需要复杂的函数转换（如 CAST 或 SUBSTRING），或者使用范围查询（如 WHERE phone >= 13800000000 AND phone < 13900000000），这不仅写法繁琐，而且可能无法有效利用索引，导致性能下降。
  4. 加密存储的要求（非常关键）：
    • 出于数据安全和隐私合规的要求，手机号这类敏感个人信息通常必须加密存储在数据库中。
    • 加密后的数据（密文）是一长串字符串（通常由字母、数字、符号组成，或经过 Base64/Hex 编码），INT 或 BIGINT 类型根本无法存储这种密文。只有 VARCHAR、TEXT 或 BLOB 等类型可以。

关于 VARCHAR 长度的选择：

  • 如果不加密存储（强烈不推荐！）： 考虑到国际号码和可能的格式符，VARCHAR(20) 到 VARCHAR(32) 通常是一个比较安全的范围，足以覆盖全球绝大多数手机号格式。VARCHAR(15) 可能对某些带国家码和格式符的号码来说不够用。
  • 如果进行加密存储（推荐的标准做法）： 长度必须根据所选加密算法产生的密文最大长度，以及可能的编码方式（如 Base64 会使长度增加约 1/3）来精确计算和设定。通常会需要更长的 VARCHAR 长度，例如 VARCHAR(128), VARCHAR(256) 甚至更长。

存储 IP 地址

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

  • INET_ATON()：把 ip 转为无符号整型 (4-8 位)
  • INET_NTOA() :把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

CHAR vs VARCHAR

CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

VARCHAR(100) vs VARCHAR(10)

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。

但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的；但是VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

DECIMAL vs FLOAT

DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

DATETIME vs TIMESTAMP

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

MySQL 不建议使用 NULL 作为列默认值

NULL vs ''空字符串

NULL 代表缺失或未知的数据，而 '' 表示一个已知存在的空字符串。

  • NULL 的存储空间占用取决于数据库的实现，通常需要一些空间来标记该值为空。

  • '' 的存储空间占用通常较小，因为它只存储一个空字符串的标志，不需要存储实际的字符。

  • 任何值与 NULL 进行比较（例如 =, !=, >, < 等）的结果都是 NULL，表示结果不确定。要判断一个值是否为 NULL，必须使用 IS NULL 或 IS NOT NULL。''可以像其他字符串一样进行比较运算。例如，'' = ''的结果是 true

大多数聚合函数（例如 SUM, AVG, MIN, MAX）会忽略 NULL 值。COUNT(*) 会统计所有行数，包括包含 NULL 值的行。COUNT(列名) 会统计指定列中非 NULL 值的行数。

空字符串 '' 会被聚合函数计算在内。例如，SUM 会将其视为 0，MIN 和 MAX 会将其视为一个空字符串。

架构

MySQL 主要由下面几部分构成：

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器： 按照 MySQL 认为最优的方案去执行。
  • 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
  • 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。InnoDB 是 MySQL 的默认存储引擎，绝大部分场景使用 InnoDB 就是最好的选择。

一条 SQL 语句在 MySQL 内部是如何执行的、

存储引擎

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

常见存储引擎包括：

  • InnoDB：默认引擎，支持事务、行级锁、外键，适合高并发 OLTP 场景；
  • MyISAM：早期默认引擎，不支持事务和行级锁，查询快但并发差；
  • Memory：数据存储在内存中，访问快但重启丢失，适合临时数据；
  • Archive：适合归档、只写不查的场景；
  • CSV：将表以 CSV 文件格式存储，兼容性高但功能有限；
  • NDB：分布式引擎，适用于 MySQL Cluster。

存储引擎架构

MySQL 存储引擎架构采用了插件式设计，SQL 层和存储引擎层解耦，这使得用户可以根据业务需求选择不同的存储引擎，比如事务型、高并发型或读多写少型等。

架构大致分为两层：

  1. SQL 层（上层） 负责解析 SQL 语句、优化查询、缓存查询结果、权限控制等逻辑，与具体的存储引擎无关。 包括查询解析器、优化器、执行器、缓存系统等模块。
  2. 存储引擎层（下层） 负责数据的实际存储与读取，包括表的创建、索引维护、数据更新、事务处理等。MySQL 允许为每张表指定不同的存储引擎。

MyISAM vs InnoDB

InnoDB 支持行级锁，并发性能高；而 MyISAM 只支持表级锁，在高并发写入场景下容易产生锁竞争，性能较差。 InnoDB 提供事务支持，符合 SQL 标准定义的四种隔离级别，可保证 ACID 属性；MyISAM 不支持事务，数据一致性需要应用层保证。 InnoDB 支持外键约束，可以维护数据之间的完整性关系；MyISAM 不支持外键。 InnoDB 实现了 MVCC（多版本并发控制），提升并发读性能；MyISAM 不支持 MVCC。 虽然两者都使用 B+Tree 作为索引结构，但 InnoDB 是聚簇索引（数据与主键索引存储在一起），而 MyISAM 是非聚簇索引，数据和索引分开存储。 在数据安全方面，InnoDB 支持崩溃恢复机制，通过 redo log 和 undo log 保证数据可靠性；MyISAM 不具备 crash-safe 能力，系统异常时可能导致数据损坏。

总体来说，InnoDB 功能更完善，性能更强大，适合事务型、高并发的业务系统；而 MyISAM 结构简单、读取性能好，适合读多写少、对事务要求不高的场景。

索引

基础

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构

优点

索引的最大优点是提升查询性能。它就像数据库中的目录或书的目录，可以让数据库在查找数据时快速定位，而不是全表扫描，大大减少了磁盘 I/O 次数，提高了查询效率。

除了查询，排序、分组、关联等操作在使用索引后，也能显著提高执行速度。对于主键约束、唯一约束来说，索引还能帮助数据库快速判断值是否存在，保障数据的唯一性。

另外，在某些场景下，索引还能实现覆盖查询，即只通过索引就能返回查询所需的数据，避免访问原始数据页，进一步提升性能。

缺点

它也存在一些明显的缺点，主要体现在性能开销和维护成本上。

首先，索引会占用额外的存储空间，尤其是对大表或多个字段建立索引时，空间消耗可能非常可观。

其次，索引会降低数据写入性能。每当执行 INSERT、UPDATE 或 DELETE 操作时，数据库不仅要修改原始数据，还要同步更新相关索引结构，这会增加写操作的成本，导致写入变慢。

另外，如果建立了不合适或过多的索引，还可能导致 查询优化器误判执行计划，反而使查询效率变低。

用了索引就一定能提高查询性能吗？

不一定。 大多数情况下，合理使用索引确实比全表扫描快得多。但也有例外：

  • 数据量太小：如果表里的数据非常少（比如就几百条），全表扫描可能比通过索引查找更快，因为走索引本身也有开销。

  • 查询结果集占比过大：如果要查询的数据占了整张表的大部分（比如超过20%-30%），优化器可能会认为全表扫描更划算，因为通过索引多次回表（随机I/O）的成本可能高于一次顺序的全表扫描。

  • 索引维护不当或统计信息过时：导致优化器做出错误判断。

数据结构

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

B树 & B+树

B 树也称 B- 树，全称为 多路平衡查找树，B+ 树是 B 树的一种变体。

B 树& B+ 树两者有何异同呢

  1. 数据存储位置不同：
    • B 树的每个节点都存储键值和数据；
    • B+ 树只有叶子节点存储完整数据，非叶子节点只存储键值用于导航。
  2. 叶子节点结构不同：
    • B 树的叶子节点是分散的；
    • B+ 树的叶子节点通过链表相连，天然支持区间查询和范围扫描。
  3. 查询性能一致性：
    • B 树查找数据可能在任意层级结束，路径不确定；
    • B+ 树查找所有数据都在叶子节点，查找路径统一，性能更稳定。

使用场景中：B 树查询路径短，但不利于范围查询；B+ 树访问路径长一些，但更适合数据库等场景的范围查询、排序和磁盘预读，因此 MySQL InnoDB 使用的是 B+ 树结构作为索引

哈希表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

**为什么Mysql不选择哈希表？**主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。比如SELECT * FROM tb1 WHERE id < 500;，对于HASH搜索引吗，MySQL 优化器可能忽略HASH 索引，回退到全盘扫描。

二叉查找树(BST)

二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构。

当二叉查找树是平衡时，查询的时间复杂度为 O(log2(N))，具有比较高的效率。不平衡时，例如在最坏情况下（有序插入节点），树会退化成线性链表（也被称为斜树），导致查询效率急剧下降，时间复杂退化为 O(N)。

二叉查找树的性能非常依赖于它的平衡程度，这就导致其不适合作为 MySQL 底层索引的数据结构。

AVL 树

实际应用中，AVL 树使用的并不多。

AVL 树是计算机科学中最早被发明的自平衡二叉查找树。AVL 树的特点是保证任何节点的左右子树高度之差不超过 1，因此也被称为高度平衡二叉树，它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了数据库写操作的性能。并且， 在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

红黑树

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态。

和 AVL 树不同的是，红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。

索引类型

按照应用维度划分：

  • 主键索引：数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。在 InnoDB 中，若未显式指定主键，MySQL 会优先使用非空唯一索引作为主键，否则自动生成一个 6 字节的隐藏自增主键。
  • 普通索引：普通索引的唯一作用就是为了快速查询数据。一张表允许创建多个普通索引，并允许数据重复和 NULL。
  • 唯一索引：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
  • 覆盖索引：覆盖索引是指查询的所有字段都能从索引中直接获取，无需回表查询数据行，因此性能更高。常用于只查询索引列的 SELECT 语句。
  • 联合索引：联合索引是将多个列组合成一个索引，按照最左前缀原则进行匹配，可同时加速多列查询。常用于多条件查询中。
  • 全文索引：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MyISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
  • 前缀索引：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

按照底层存储方式角度划分：

  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引（辅助索引）就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照数据结构维度划分：

  • BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
  • 哈希索引：类似键值对的形式，一次即可定位。
  • RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

聚簇索引的优缺点

优点：

  • 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+ 树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
  • 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

  • 依赖于有序的数据：因为 B+ 树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  • 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引的优缺点

优点：

更新代价比聚簇索引要小。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点：

  • 依赖于有序的数据：跟聚簇索引一样，非聚簇索引也依赖于有序的数据。
  • 可能会二次查询（回表）：这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

非聚簇索引是否一定会回表查询？

不一定。非聚簇索引是否回表，关键取决于查询是否命中了覆盖索引。

在 InnoDB 中，非聚簇索引的叶子节点只存储对应记录的主键值，而不包含整行数据。因此，当查询所需字段不全在索引中时，必须先通过非聚簇索引定位主键，再回到主键索引（即聚簇索引）中取出完整的行数据，这就是回表查询。

但如果查询中所涉及的字段全部都在非聚簇索引中可以直接获取到，就不需要回表，这种情况称为覆盖索引。使用覆盖索引可以减少一次磁盘访问，提升查询性能。

最左前缀匹配原则

最左前缀匹配原则是 MySQL 联合索引使用的一个核心规则，它指的是：在使用联合索引（多列索引）时，查询条件必须从最左的列开始，连续匹配索引中的前缀列，索引才能生效。

查询缓存

MySQL查询缓存详解 | JavaGuide

执行查询语句的时候，会先查询缓存，如果缓存中有对应的查询结果，就会直接返回。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。

MySQL 5.6 开始，查询缓存已默认禁用。MySQL 8.0 开始，已经不再支持查询缓存了。

执行计划

MySQL执行计划分析 | JavaGuide

日志

  • MySQL 中常见的日志有哪些？
  • 慢查询日志有什么用？
  • binlog 主要记录了什么？
  • redo log 如何保证事务的持久性？
  • 页修改之后为什么不直接刷盘呢？
  • binlog 和 redolog 有什么区别？
  • undo log 如何保证事务的原子性？

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

redo log（重做日志）

在 MySQL 的 InnoDB 存储引擎中，redo log 是用来保障事务持久性（ACID 中的 D，Durability）的一种机制。它的作用是：即使数据库发生崩溃，也能通过日志把数据恢复到一致的状态，确保已提交的事务不会丢失。

redo log 属于 InnoDB 引擎层，记录的是对数据页的物理修改操作，而不是 SQL 语句。InnoDB 采用了 WAL（Write-Ahead Logging，先写日志再写磁盘）的策略，数据在内存中修改后，会先写入 redo log，再异步刷新到磁盘，这样可以提升写入性能，并避免数据丢失。

redo log 分为两个阶段：prepare 和 commit。当事务执行时先记录 redo log 的 prepare 部分；事务提交时，再追加 commit 标记。这样即使宕机重启，系统也能通过 redo log 判断哪些事务已经提交，并将其变更“重做”到数据页中。

此外，redo log 是顺序写入磁盘的，相比频繁随机写数据页，性能更高。它与 binlog 不同，binlog 是逻辑日志、Server 层日志，而 redo log 是物理日志、InnoDB 层日志。两者配合，实现了崩溃恢复和主从复制的一致性。

binlog（归档日志）

binlog（Binary Log）是 MySQL Server 层的日志，记录的是数据库执行的所有修改类操作的逻辑事件，例如 INSERT、UPDATE、DELETE 等，用于实现主从复制、数据恢复和增量备份。

binlog 是逻辑日志，也就是说它记录的是“做了什么”，而不是像 redo log 那样记录“修改了哪些数据页”。

binlog 的主要特点包括：

  1. 属于 MySQL Server 层，而非存储引擎层，因此所有存储引擎都可以使用 binlog；
  2. 只记录提交事务的内容，且是追加写入、不可修改的文件格式；
  3. 支持三种格式：
    • STATEMENT：记录 SQL 语句；
    • ROW：记录每一行数据的变更；
    • MIXED：两者结合，根据场景自动选择；
  4. binlog 被广泛用于：
    • 主从复制（Replication）：从库通过 binlog 实现数据同步；
    • 数据恢复：结合全量备份，可实现时间点恢复；
    • 审计和同步：分析数据库变更操作。

undo log（回滚日志）

undo log 是 InnoDB 存储引擎中用于实现事务原子性和一致性的重要日志，主要用于回滚操作和 MVCC（多版本并发控制）。

当事务对数据进行修改时，InnoDB 会在执行之前生成一份数据修改前的副本，写入 undo log。如果事务中途失败或被回滚，InnoDB 就可以利用 undo log 将数据恢复到原来的状态，实现事务的原子性（即“要么全部执行，要么全部不执行”）。

同时，在并发事务中，为了实现一致性读（即快照读），其他事务需要读取某行数据的旧版本时，也会通过 undo log 构造出一个历史版本，从而实现 MVCC，避免加锁，提高并发性能。

undo log 的特点包括：

  1. 逻辑日志，记录的是“某行数据在被修改前是什么样子”；
  2. 与 redo log 相对，redo log 是“重做未来”，undo log 是“回滚过去”；
  3. 存储在系统表空间或独立 undo 表空间中，支持自动清理（由后台线程或事务提交触发）；
  4. 支持两种类型：
    • Insert Undo Log：用于回滚插入；
    • Update Undo Log：用于回滚更新和删除，同时供快照读使用。

事务

**事务（Transaction）**是一组不可分割的操作单位，用于确保数据从一个一致状态变更为另一个一致状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

数据库事务是指在关系型数据库中对一组 SQL 操作的统一管理，可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。

ACID特性

关系型数据库(MySQL、SQL Server、Oracle 等)的事务都有 ACID 特性。

  • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

  • 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

  • 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

  • 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的。

并发事务

并发事务带来的问题

脏读

脏读（Dirty Read）指的是一个事务读到了另一个事务尚未提交的数据，而这些数据后续可能会被回滚，从而导致读到的是无效甚至错误的信息。

脏读是事务隔离性不足导致的一种数据不一致现象，只有在 读未提交（Read Uncommitted） 隔离级别下才可能发生。

丢失修改

丢失修改（Lost Update）是指两个事务同时读取了同一条数据并进行了更新，其中一个事务的修改被另一个事务覆盖，从而导致前一个修改“丢失”了。

丢失修改通常发生在隔离级别较低的环境（如 Read Uncommitted 或 Read Committed）中。通过使用行级锁或设置更高的隔离级别（如 Repeatable Read 或串行化）可以避免这种问题。

不可重复读

在一个事务中，对同一条记录的多次读取结果不一致，因为在这个事务执行期间，另一个事务对该记录做了修改并提交了。

比如：

  • 事务 A 第一次查询某用户余额为 100；
  • 此时事务 B 将该用户余额改为 200 并提交；
  • 事务 A 再次查询该用户余额，结果变成了 200。

事务 A 两次读取了同一条记录，却得到了不同的结果，这就是不可重复读。

这种问题通常发生在Read Committed隔离级别下，在更高的隔离级别（如 Repeatable Read 或 Serializable）中可以避免。

幻读

**幻读（Phantom Read）*是指在一个事务中，**两次相同条件的查询，结果集数量不一致**，因为*其他事务在期间插入或删除了满足条件的新数据。

举个例子：

  • 事务 A 查询“工资 > 5000”的员工，结果返回 5 条；
  • 此时事务 B 插入了一条工资为 6000 的新员工并提交；
  • 事务 A 再次执行相同查询，结果返回了 6 条，出现了“幻影”数据。

幻读通常在 Read Committed 或 Repeatable Read 隔离级别下可能发生，而在 Serializable（串行化）隔离级别下可以彻底避免。

不可重复读 vs 幻读

不可重复读关注的是已有记录被修改导致读出值不一致，幻读则是查询条件下新增或删除了记录导致结果集条数变化。

并发事务的控制方式

MySQL 中并发事务的控制方式主要两种：锁 和 MVCC。

锁机制

这是最直接的控制方式，通过加锁来保证事务间的隔离。

MySQL 中主要是通过 读写锁 来实现并发控制。

共享锁（S 锁）：又称读锁，多个事务可以同时读取同一条数据，不会互相阻塞。

排他锁（X 锁）：又称写锁/独占锁，一个事务写数据时会阻止其他事务对该数据进行读或写，确保修改过程的独占性；

如果一个事务要先读再写，会先加 S 锁，再升级为 X 锁。

根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) ，还有更细的，如间隙锁、临键锁（用于防止幻读）

行级锁：锁住一行数据，粒度小，并发高；

表级锁：锁住整个表，粒度大，并发低；

多版本并发控制（MVCC）

InnoDB存储引擎对MVCC的实现 | JavaGuide

对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

  • undo log : undo log 用于记录某行数据的多个版本的数据。
  • read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

具体来说，InnoDB 是通过隐藏字段和 undo log 相结合来实现 MVCC 的。当事务对一行数据进行更新时，并不会直接覆盖原始数据，而是会将更新前的版本记录到 undo log 中，并将该记录与当前数据行通过回滚指针关联起来。这样，每当有事务执行读取操作时，系统会根据当前事务的视图来判断它应当看到哪一个版本的数据。

InnoDB 会在每个事务开始时生成一个Read View（读视图），其中记录了当前活跃的事务 ID 列表。随后在读取每一行记录时，会比较记录上的事务 ID 与读视图中的信息，从而决定该数据版本是否对当前事务可见。

这种机制的核心优势在于：写操作不会阻塞读操作，读操作也不会阻塞写操作，从而在不牺牲一致性的前提下，极大地提高了系统的并发能力。

事务隔离级别

SQL 标准定义了四种事务隔离级别，用来平衡事务的隔离性（Isolation）和并发性能。级别越高，数据一致性越好，但并发性能可能越低。这四个级别是：

  • READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。这种级别在实际应用中很少使用，因为它对数据一致性的保证太弱。
  • READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。这是大多数数据库（如 Oracle, SQL Server）的默认隔离级别。
  • REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。MySQL InnoDB 存储引擎的默认隔离级别正是 REPEATABLE READ。并且，InnoDB 在此级别下通过 MVCC（多版本并发控制） 和 Next-Key Locks（间隙锁+行锁） 机制，在很大程度上解决了幻读问题。
  • SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认隔离级别是 REPEATABLE READ。

如何实现隔离级别：MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL锁

表级锁、行级锁

MyISAM 仅仅支持表级锁；InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

注：InnoDB 的行级锁是依赖索引实现的，如果 SQL 没有命中索引或索引失效，就无法使用行锁，会退化成表锁或锁全表的数据行，严重影响性能和并发。这在开发中非常常见。

InnoDB 有哪几类行锁

MySQL InnoDB 支持三种行锁定方式：

  • 记录锁（Record Lock）：属于单个行记录上的锁。
  • 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
  • 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁）：又称读锁，多个事务可以同时读取同一条数据，不会互相阻塞。

排他锁（X 锁）：又称写锁/独占锁，一个事务写数据时会阻止其他事务对该数据进行读或写，确保修改过程的独占性；

如果一个事务要先读再写，会先加 S 锁，再升级为 X 锁。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

意向锁

意向锁是表级锁，共有两种：

  • 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁）。

自增锁

自增锁是 MySQL InnoDB 存储引擎中专门用于处理含有自增列（AUTO_INCREMENT）字段的表时的并发控制机制。

InnoDB 为了保证多个事务同时插入自增字段时不会出现主键重复或冲突，引入了一种特殊的锁，称为 自增锁（auto-inc lock）。这个锁在并发插入时控制对自增计数器的访问。

核心特点有以下几点：

  1. 自增锁是一种表级锁，在插入带有 AUTO_INCREMENT 字段的记录时，会对整张表加锁，直到插入语句执行完成，防止其他事务同时获取自增值。
  2. 默认行为是：语句执行时加锁，执行完释放（语句级锁），而不是整个事务持有（事务级锁），这样可以在一定程度上支持并发插入。
  3. 并发插入可能形成阻塞：如果多个事务同时插入带自增主键的记录，即使其他字段没有冲突，也要串行获取自增值，可能造成性能瓶颈。

快照读和当前读

在 InnoDB 中，读操作分为两类：快照读（Snapshot Read）和当前读（Current Read），它们的本质区别在于是否使用 MVCC（多版本并发控制）机制。

快照读是通过 MVCC 实现的非锁定读，读取的是数据某一时刻的历史版本，并不是当前最新值。

  • 典型操作：SELECT 语句，不加锁；
  • 适用隔离级别：Read Committed 和 Repeatable Read；
  • 优势：不会加锁，适合高并发环境下的查询。

例如：你执行 SELECT * FROM user WHERE id = 1，此时读到的是符合隔离级别要求的某个“可见版本”的数据，而不是此刻最新的一行数据。

当前读是加锁的读取操作，读取的就是当前最新版本的数据，并且会加锁以保证一致性。

  • 典型操作：SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE；
  • 它会读取最新数据，并在必要时阻塞其他事务，直到锁释放。

MySQL调优

有哪些常见SQL优化手段

性能分析

读写分离

分库分表

深度分页

数据冷热分离

设计与开发规范

数据库命名

所有数据库对象名称必须使用小写字母并用下划线分割。

所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）。

数据库对象的命名要能做到见名识义，并且最好不要超过 32 个字符。

临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀。

所有存储相同数据的列名和列类型必须一致

基本设计

没有特殊要求（即 InnoDB 无法满足的功能如：列存储、存储空间数据等）的情况下，所有表必须使用 InnoDB 存储引擎

数据库和表的字符集统一使用 UTF8，兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。

所有表和字段都需要添加注释，使用 comment 从句添加表和列的备注，从一开始就进行数据字典的维护。

尽量控制单表数据量的大小，建议控制在 500 万以内，过大会造成修改表结构，备份，恢复都会有很大的问题。

谨慎使用 MySQL 分区表。分区表在物理上表现为多个文件，在逻辑上表现为一个表。谨慎选择分区键，跨分区查询效率可能更低。建议采用物理分表的方式管理大数据。

经常一起使用的列放到一个表中，避免更多的关联操作。

禁止在表中建立预留字段。预留字段的命名很难做到见名识义。预留字段无法确认存储的数据类型，所以无法选择合适的类型。对预留字段类型的修改，会对表进行锁定。

禁止在数据库中存储文件（比如图片）这类大的二进制数据，在数据库中存储文件会严重影响数据库性能，消耗过多存储空间。

不要被数据库范式所束缚在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度。

字段设计

优先选择符合存储需要的最小的数据类型

避免使用 TEXT、BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据；建议把 BLOB 或是 TEXT 列分离到单独的扩展表中。TEXT 或 BLOB 类型只能使用前缀索引

避免使用 ENUM 类型。

除非有特别的原因使用 NULL 值，否则应该总是让字段保持 NOT NULL。

同财务相关的金额类数据必须使用 decimal 类型

索引设计

限制每张表上的索引数量，建议单张表索引不超过 5 个。因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划。如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

禁止使用全文索引。全文索引不适用于 OLTP 场景。

禁止给表中的每一列都建立单独的索引

常见索引列建议：

  • 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列。

  • 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段。

  • 不要将符合 1 和 2 中的字段的列都建立一个索引，通常将 1、2 中的字段建立联合索引效果更好。

  • 多表 join 的关联列。

如何选择索引列的顺序

开发规范

尽量不在数据库做运算，复杂运算需移到业务应用里完成。这样可以避免数据库的负担过重，影响数据库的性能和稳定性。数据库的主要作用是存储和管理数据，而不是处理数据。

禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

  • SELECT * 会消耗更多的 CPU。
  • SELECT * 无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段（如 varchar、blob、text）。
  • SELECT * 无法使用 MySQL 优化器覆盖索引的优化（基于 MySQL 优化器的“覆盖索引”策略又是速度极快、效率极高、业界极为推荐的查询优化方式）。
  • SELECT <字段列表> 可减少表结构变更带来的影响。

禁止使用不含字段列表的 INSERT 语句

建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少 SQL 编译所需要的时间，还可以解决动态 SQL 所带来的 SQL 注入的问题。

只传参数，比传递 SQL 语句更高效。

相同语句可以一次解析，多次使用，提高处理效率。

避免数据类型的隐式转换，隐式转换会导致索引失效。

避免使用子查询，可以把子查询优化为 join 操作。通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

避免使用 JOIN 关联太多的表

对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置。

在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。

如果程序中大量地使用了多表关联的操作，同时 join_buffer_size 设置得也不合理，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL 最多允许关联 61 个表，建议不超过 5 个。

对应同一列进行 or 判断时，使用 in 代替 or

禁止使用 order by rand() 进行随机排序：order by rand() 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值。如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

WHERE 从句中禁止对列进行函数转换和计算：对列进行函数转换或计算时会导致无法使用索引。

在明显不会有重复值时使用 UNION ALL 而不是 UNION：UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；UNION ALL 不会再对结果集进行去重操作。

拆分复杂的大 SQL 为多个小 SQL：大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL。MySQL 中，一个 SQL 只能使用一个 CPU 进行计算。SQL 拆分后可以通过并行执行来提高处理效率。

程序连接不同的数据库使用不同的账号，禁止跨库查询：为数据库迁移和分库分表留出余地。降低业务耦合度。避免权限过大而产生的安全风险。

操作行为规范

超 100 万行的批量写 (UPDATE、DELETE、INSERT) 操作，要分批多次进行操作

大批量操作可能会造成严重的主从延迟

主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况。

binlog 日志为 row 格式时会产生大量的日志

大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因。

避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。

特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批。

对于大表使用 pt-online-schema-change 修改表结构

  • 避免大表修改产生的主从延迟。
  • 避免在对表字段进行修改时进行锁表。

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

pt-online-schema-change 它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。把原来一个 DDL 操作，分解成多个小的批次进行。

SQL语句在MySQL中的执行过程