《MySQL 实战 45 讲》笔记

极客时间教程 - MySQL 实战 45 讲 学习笔记

01 基础架构：一条 SQL 查询语句是如何执行的？

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、解析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

MySQL 整个查询执行过程，总的来说分为 6 个步骤：

1. 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
2. 查询缓存：命中缓存，则直接返回结果。弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。
3. 分析器

• 词法分析：解析 SQL 关键字
• 语法分析：生成一颗对应的语法解析树

4. 优化器

• 根据语法树生成多种执行计划
• 索引选择：根据策略选择最优方式

5. 执行器

• 校验读写权限
• 根据执行计划，调用存储引擎的 API 来执行查询

6. 存储引擎：存储数据，提供读写接口

02 日志系统：一条 SQL 更新语句是如何执行的？

更新流程和查询的流程大致相同，不同之处在于：更新流程还涉及两个重要的日志模块：

• redo log（重做日志）
• binlog（归档日志）

redo log

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 采用了 WAL 技术（全程是 Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为** crash-safe**。

binlog

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

redo log 和 binlog 的差异：

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

再来看一下：update 语句时的内部流程

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

两阶段提交

为什么日志需要“两阶段提交”

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。

• 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
• 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

03 事务隔离：为什么你改了我还看不见？

隔离级别

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在 MySQL 中，事务支持是在引擎层实现的。并不是所有的引擎都支持事务。比如 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

事务特性 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。隔离级别越高，效率越低。

• Oracle 的默认隔离级别是“读提交”
• MySQL 的默认隔离级别是“可重复读”

事务隔离的实现

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

04 深入浅出索引（上）

索引的出现是为了提高数据查询的效率。对于数据库的表而言，索引就像是书的目录。

索引的常见模型

哈希索引

哈希索引适用于只有等值查询的场景。

哈希索引的限制：

• 无法用于排序：因为哈希索引数据不是按照索引值顺序存储的。
• 不支持部分索引匹配查找：因为哈希索引时使用索引列的全部内容来进行哈希计算的。
• 不能用索引中的值来避免读取行：因为哈希索引只包含哈希值和行指针，不存储字段。
• 只支持等值比较查询（包括 =、IN()、<=>）；不支持任何范围查询
• 哈希索引非常快，除非有很多哈希冲突：
  • 出现哈希冲突时，必须遍历链表中所有行指针，逐行比较匹配
  • 如果哈希冲突多的话，维护索引的代价会很高

哈希索引的应用：Mysql 中，只有 Memory 存储引擎显示支持哈希索引。

有序数组索引

有序数组索引在等值查询和范围查询场景中的性能都非常优秀。

可以应用二分查找法检索数据，时间复杂度为 O(logN)。

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，更新数据的时候，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎。

N 叉搜索树

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。检索数据时，可以采用二分查找法，这个时间复杂度是 O(logN)。为了维持二叉搜索树的有序，就需要保证这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(logN)。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

树的高度意味着机械磁盘的最大扫描次数。假设一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块，也就意味着需要磁盘扫描 20 次。磁盘扫描是比较耗时的，所以应尽量减少磁盘扫描次数。因此，通过使用 N 叉树，来减少树的高度，是一个行之有效的策略。以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于非主键索引的查询需要多扫描一次主键索引树，这个过程称为回表。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

• 为了保证有序，插入新值时，可能需要按序挪动已有数据。
• 此外，如果所在的数据页满了，需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。
• 当相邻两个页由于删除了数据，利用率很低之后，会将数据页合并。合并的过程，可以认为是分裂过程的逆过程。

由于非主键索引的叶子节点内容是主键的值，因此**主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。**所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

适合用业务字段直接做主键的场景：

• 只有一个索引；
• 该索引必须是唯一索引。

05 深入浅出索引（下）

覆盖索引

能覆盖查询字段的索引，可以直接提供查询结果，无需回表，称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这里的最左，可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询 (>、<、BETWEEN、LIKE) 就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。

应该将选择性高的列或基数大的列优先排在多列索引最前列。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。“索引的选择性”是指不重复的索引值和记录总数的比值，最大值为 1，此时每个记录都有唯一的索引与其对应。索引的选择性越高，查询效率越高。如果存在多条命中前缀索引的情况，就需要依次扫描，直到最终找到正确记录。

索引下推

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

06 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁（FTWRL）

• 作用：对整个数据库加锁，使数据库进入只读状态（阻塞所有数据更新、DDL 操作和事务提交）。
• 使用场景：
  • 全库逻辑备份（确保备份数据的一致性）。
  • 问题：
    • 主库备份会导致业务停摆（无法更新）。
    • 从库备份会阻塞主从同步（binlog 延迟）。
• 替代方案：
  • mysqldump --single-transaction（InnoDB 适用）：
    • 通过事务的可重复读隔离级别和 MVCC 实现一致性视图，备份期间允许数据更新。
  • readonly=true的缺陷：
    • 影响主备库判断逻辑；异常时不会自动释放锁，风险更高。
• 适用引擎：
  • InnoDB：优先用--single-transaction。
  • MyISAM：必须用 FTWRL（不支持事务）。

表级锁

表锁（LOCK TABLES ... READ/WRITE）

• 行为：显式加锁，限制其他线程的读写，同时限制本线程的操作范围（如LOCK TABLES t1 READ后，本线程只能读t1）。
• 应用场景：
  • MyISAM 等不支持行锁的引擎。
  • InnoDB 一般不用（行锁更细粒度）。

元数据锁（MDL）

• 自动加锁：

  • 读锁：增删改查时自动加（多个读锁不互斥）。
  • 写锁：修改表结构时加（与读锁/其他写锁互斥）。

• 常见问题：

  • 长事务阻塞 DDL：未提交的事务会持有 MDL 读锁，导致后续 DDL（如加字段）被阻塞，进而阻塞所有后续查询（线程爆满）。

• 解决方案：

  • 监控长事务（information_schema.innodb_trx），必要时 kill。

  • 使用 WAIT/NOWAIT 语法（MariaDB/AliSQL 支持）：

    ALTER TABLE tbl_name WAIT 10 ADD COLUMN ...;  -- 等待 10 秒超时
    ALTER TABLE tbl_name NOWAIT ADD COLUMN ...;  -- 立即放弃
    

关键实践建议

• 备份策略：
  • InnoDB 库：用mysqldump --single-transaction（非阻塞）。
  • 含 MyISAM 的库：用 FTWRL（需业务低峰期）。
• DDL 操作：
  • 避免在高峰期执行，优先检查长事务。
  • 使用支持超时的 DDL 语法（如 MariaDB 的WAIT N）。
• 锁升级：将 MyISAM 表迁移到 InnoDB，避免使用表锁。

小结

锁类型	命令/机制	适用场景	风险与解决方案
全局锁	FTWRL	MyISAM 备份	业务阻塞 → 改用 InnoDB+事务
表锁	LOCK TABLES	MyISAM 并发控制	影响粒度大 → 升级 InnoDB
MDL 锁	自动加锁（读/写）	防止表结构不一致	长事务阻塞 DDL → 监控/Kill

通过合理选择锁机制和引擎，可以平衡数据一致性与并发性能。

07 行锁功过：怎么减少行锁对性能的影响？

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

当出现死锁以后，有两种策略：

• 进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • 在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果此策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
  • 但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
  • 主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
  • 极端情况下，如果所有事务都要更新同一行：每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

08 事务到底是隔离的还是不隔离的

事务的启动时机：

• begin/start transaction 命令并不是事务的起点，事务的真正启动是在执行第一个操作 InnoDB 表的语句时。
• 使用 start transaction with consistent snapshot 可以立即启动事务并创建一致性视图。

一致性视图（Consistent Read View）：

• 在可重复读隔离级别下，事务启动时会创建一个一致性视图，事务执行期间看到的数据与该视图一致。
• 一致性视图是基于事务 ID（transaction id）和数据版本（row trx_id）来实现的。

“快照”在 MVCC 里是怎么工作的？

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。

图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。

当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况
   a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

更新逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

---

以下为【实践篇】

09 普通索引和唯一索引，应该怎么选择？

查询过程的性能差异：对于查询操作，普通索引和唯一索引的性能差异微乎其微。唯一索引在找到第一个满足条件的记录后会停止检索，而普通索引需要继续查找下一个记录，但由于数据页的读取方式，这种差异可以忽略不计。

更新过程的性能差异：更新操作中，普通索引可以利用 change buffer 来优化性能，而唯一索引则不能使用 change buffer。

• change buffer 是一种将更新操作缓存在内存中的机制，减少了对磁盘的随机读取，从而提升了更新操作的性能。
• 唯一索引在更新时需要检查唯一性约束，必须将数据页读入内存，增加了磁盘 I/O 的开销。

change buffer 的应用

• change buffer 的数据是持久化的，即使机器掉电重启，change buffer 中的数据也不会丢失，因为它会被写入磁盘。
• change buffer 适用于写多读少的场景，如账单类、日志类系统，因为这些场景下数据页在写入后不会立即被访问，change buffer 可以显著减少磁盘 I/O。
• 对于写后立即查询的场景，change buffer 的效果不明显，甚至可能增加维护成本。

change buffer vs. redo log

• redo log 主要减少随机写磁盘的 I/O 消耗，将随机写转换为顺序写。
• change buffer 主要减少随机读磁盘的 I/O 消耗，通过缓存更新操作来减少磁盘读取。

总结：

• 唯一索引的主要作用是保证数据的唯一性，而普通索引则更灵活。在业务代码保证不会写入重复数据的情况下，普通索引和唯一索引在查询性能上几乎没有差别。
• 普通索引 在更新操作中性能更优，尤其是在写多读少的场景下，能够利用 change buffer 减少磁盘 I/O。
• 唯一索引 适用于需要保证数据唯一性的场景，但在更新操作中性能较差，因为它无法使用 change buffer。
• 在业务允许的情况下，优先选择普通索引，因为它可以利用 change buffer 来提升更新性能。如果业务要求必须保证数据的唯一性，则必须使用唯一索引。

10 MySQL 为什么有时候会选错索引？

MySQL 优化器负责选择索引，但有时会选错索引，导致查询性能下降。

优化器选择索引的依据是执行代价，主要考虑扫描行数、是否使用临时表、是否排序等因素。

• 扫描行数的估计依赖于索引的“区分度”和“基数”（cardinality），基数越大，区分度越好。
• MySQL 通过采样统计来估算基数，但由于采样统计的不准确性，可能导致优化器误判。

索引选择异常的处理方法：

• analyze table：如果只是统计信息不对，可以使用 analyze table 命令重新统计索引信息，修正优化器的误判。
• force index：强制使用指定索引，但这种方法不够优雅且维护成本高。
• 修改查询语句：通过改写 SQL 语句引导优化器选择正确的索引，例如调整 order by 条件。
• 新建或删除索引：通过调整索引来影响优化器的选择。

11 怎么给字符串字段加索引？

字符串字段索引的挑战：

• 字符串字段（如邮箱、身份证号）通常较长，直接创建完整索引会占用大量存储空间。
• 使用前缀索引可以节省空间，但可能会增加查询时的扫描行数，影响查询性能，因为前缀相同的字符串可能会导致多次回表查询。选择合适的前缀长度是关键。
• 可以通过 count(distinct left(column, length)) 来计算不同前缀长度的区分度，选择区分度足够高的前缀长度。

前缀索引对覆盖索引的影响：

• 覆盖索引是指查询可以直接从索引中获取所需数据，而不需要回表查询。
• 使用前缀索引时，无法利用覆盖索引的优势，因为前缀索引可能无法完全覆盖查询所需的字段。

其他优化方式：

有些情况下，前缀的区分度不够好，如我国身份证前 6 位表示地区，即同一地区的身份证号前 6 位一般是相同的。对此，有以下优化方式：

• 倒序存储：将字符串倒序存储后创建前缀索引，适用于某些特定场景（如身份证号），可以提高区分度。
• hash 字段：在表中增加存储字符串 hash 值的字段并作为索引。hash 字段索引占用空间小，查询性能稳定，但不支持范围查询。

倒序存储与 hash 字段的相同点是，都不支持范围查询。

倒序存储与 hash 字段的区别是：

• 倒序存储：不占用额外存储空间，但每次查询需要调用 reverse 函数，且仍然使用前缀索引，可能会增加扫描行数。
• hash 字段：需要额外存储空间，查询性能稳定，但需要调用 crc32 函数，且不支持范围查询。

12 为什么我的 MySQL 会“抖”一下？

有时 SQL 语句执行速度突然变慢，持续时间短且难以复现，这种现象称为 MySQL“抖动”。这种现象通常与 InnoDB 的刷脏页（flush）操作有关。

InnoDB 使用 WAL（Write-Ahead Logging）机制，更新操作先写 redo log，再写内存，最后刷到磁盘。当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

刷脏页的触发场景：

• redo log 写满：当 redo log 写满时，系统会停止所有更新操作，推进 checkpoint，刷脏页以释放 redo log 空间。
• 内存不足：当内存不足时，InnoDB 会淘汰一些数据页。如果淘汰的是脏页，则需要先将脏页刷到磁盘。
• 系统空闲时：MySQL 在系统空闲时，会主动刷脏页。
• 数据库关闭时：MySQL 正常关闭时，会将所有脏页刷到磁盘，以便下次启动时快速恢复。

刷脏页对性能的影响：

• redo log 写满：这种情况会导致系统无法接受更新操作，更新数跌为 0，影响写性能。
• 内存不足：查询需要淘汰脏页时，会导致查询响应时间变长。

InnoDB 刷脏页的控制策略：

• innodb_io_capacity：该参数用于告诉 InnoDB 磁盘的 IO 能力，建议设置为磁盘的 IOPS。
• 脏页比例控制：InnoDB 通过脏页比例和 redo log 写入速度来控制刷脏页的速度。脏页比例上限由 innodb_max_dirty_pages_pct 参数控制，默认值为 75%。
• 刷脏页速度计算：InnoDB 根据脏页比例和 redo log 写入速度计算出刷脏页的速度，取两者中的较大值。

刷脏页的“连坐”机制：

• InnoDB 在刷脏页时，可能会连带刷掉相邻的脏页，以减少随机 IO。该行为由 innodb_flush_neighbors 参数控制。
• 对于 SSD 等高性能存储设备，建议将 innodb_flush_neighbors 设置为 0，以避免不必要的 IO 操作。

监控脏页比例：

• 可以通过查询 Innodb_buffer_pool_pages_dirty 和 Innodb_buffer_pool_pages_total 来监控脏页比例，确保其不要经常接近 75%。

13 为什么表数据删掉一半，表文件大小不变？

表数据删除后空间不回收的原因：

• 当使用 DELETE 命令删除表中的数据时，InnoDB 引擎只是将数据标记为“可复用”，并不会立即释放磁盘空间。这些被标记为可复用的空间称为“空洞”。
• 空洞不仅由删除操作引起，插入和更新操作也可能导致空洞。例如，随机插入数据可能导致页分裂，从而产生空洞。

innodb_file_per_table 参数：

• 该参数控制表数据的存储方式。设置为 ON 时，每个表的数据存储在一个单独的 .ibd 文件中；设置为 OFF 时，表数据存储在共享表空间中。
• 建议将该参数设置为 ON，因为单独存储表数据文件更容易管理，且在删除表时可以直接回收空间。

数据删除流程：

• 删除操作只是标记数据为可复用，不会立即释放磁盘空间。数据页的复用与记录的复用不同，数据页可以被复用到任何位置，而记录的复用仅限于符合特定条件的数据。

重建表以回收空间：

• 为了回收表空间，可以通过重建表来去除空洞。重建表的操作可以通过 ALTER TABLE t ENGINE=InnoDB 命令实现。
• 在 MySQL 5.5 及之前版本，重建表操作会阻塞表的增删改操作（非 Online DDL）。
• 从 MySQL 5.6 开始，引入了 Online DDL，允许在重建表的过程中继续对表进行增删改操作。

Online DDL 和 inplace 操作：

• Online DDL 允许在重建表的过程中继续对表进行增删改操作，减少了锁表时间。
• inplace 操作指的是在 InnoDB 内部完成数据重建，不需要将数据移动到临时表。Online DDL 一定是 inplace 操作，但 inplace 操作不一定是 Online 的。

重建表的其他方式：

• ANALYZE TABLE：重新统计表的索引信息，不修改数据。
• OPTIMIZE TABLE：相当于 RECREATE + ANALYZE，会重建表并重新统计索引信息。

思考题：

• 文章最后提出了一个思考题：为什么在某些情况下，执行 ALTER TABLE t ENGINE=InnoDB 后，表空间不仅没有缩小，反而变大了？可能的原因包括数据页的重新排列、索引的重建等。

14 count(*) 这么慢，我该怎么办？

COUNT(*) 的实现方式：

• MyISAM 引擎：将表的总行数存储在磁盘上，执行 COUNT(*) 时直接返回该值，效率很高。
• InnoDB 引擎：由于支持事务和 MVCC，COUNT(*) 需要逐行扫描数据并判断可见性，导致性能较差。

为什么 InnoDB 不存储行数：

• 由于 MVCC 的存在，不同事务在同一时刻看到的行数可能不同，因此 InnoDB 无法像 MyISAM 那样直接存储行数。
• InnoDB 在执行 COUNT(*) 时会选择最小的索引树进行遍历，以减少扫描的数据量。

SHOW TABLE STATUS 的局限性：

• SHOW TABLE STATUS 命令中的 TABLE_ROWS 是通过采样估算的，误差可能达到 40% 到 50%，因此不能替代 COUNT(*)。

不同 COUNT 用法的性能差异：

• COUNT（主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
• COUNT(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
• COUNT（字段）：
  • 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
  • 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
• COUNT(*)：InnoDB 做了专门优化，不取值，直接按行累加，性能最好。

结论：按照效率排序的话，COUNT（字段） < COUNT（主键 id) < COUNT(1) ≈ COUNT(*)。推荐采用 COUNT(*) 。

优化查询计数：

• 可以使用 Redis 保存计数，但存在数据丢失和逻辑不一致的问题。

• 可以使用数据库其他表保存计数，利用事务的原子性和隔离性，可以避免数据丢失和逻辑不一致的问题。

15 答疑文章（一）：日志和索引相关问题

日志相关问题

• 两阶段提交与崩溃恢复：MySQL 使用两阶段提交（2PC）来保证 binlog 和 redo log 的一致性。在两阶段提交的不同时刻，如果发生崩溃，MySQL 会根据 redo log 和 binlog 的状态来决定是提交事务还是回滚事务。
• 崩溃恢复的判断规则：
  • 如果 redo log 中有 commit 标识，直接提交事务。
  • 如果 redo log 处于 prepare 状态，检查 binlog 是否完整，完整则提交事务，否则回滚。
• binlog 的完整性：binlog 有固定的格式（statement 格式有 COMMIT，row 格式有 XID event），并且可以通过 binlog-checksum 参数验证其完整性。
• redo log 和 binlog 的关联：通过 XID 字段关联 redo log 和 binlog。崩溃恢复时，MySQL 会扫描 redo log，并根据 XID 查找对应的 binlog。
• 为什么需要两阶段提交：两阶段提交是为了保证事务的持久性和数据一致性。如果 redo log 直接提交，而 binlog 写入失败，会导致数据不一致。
• redo log 的大小设置：redo log 太小会导致频繁刷盘，建议设置为 4 个文件，每个文件 1GB。
• 数据最终落盘：数据最终落盘是从 buffer pool 中的脏页写入磁盘，而不是从 redo log 更新过来。redo log 只用于崩溃恢复时恢复数据页。

互相关注的业务场景

• 在并发场景下，A 和 B 同时关注对方可能导致无法成为好友的问题。解决方案是通过在 like 表中增加 relation_ship 字段，并使用 insert ... on duplicate key update 语句来确保行锁的生效。
• 通过按位或操作和 insert ignore 语句，确保在并发场景下也能正确处理互相关注的逻辑。

更新操作的内部处理：

• 当执行 update t set a=2 where id=1 时，MySQL 会先读取数据，发现 a 的值已经是 2，因此不会进行实际的更新操作，直接返回。
• 这种行为是为了减少不必要的写操作，提升性能。

参考资料

• 极客时间教程 - MySQL 实战 45 讲