数据库性能调优
一、mysql调优之sql语句
我们就重点看看慢 SQL 语句的几种常见诱因，从这点出发，找到最佳方法，开启
高性能 SQL 语句的大门。
慢 SQL 语句的几种常见诱因
1. 无索引、索引失效导致慢查询
如果在一张几千万数据的表中以一个没有索引的列作为查询条件，大部分情况下查询会非常
耗时，这种查询毫无疑问是一个慢 SQL 查询。所以对于大数据量的查询，我们需要建立适
合的索引来优化查询。
虽然我们很多时候建立了索引，但在一些特定的场景下，索引还有可能会失效，所以索引失
效也是导致慢查询的主要原因之一。针对这点的调优，我会在第 34 讲中详解。
2. 锁等待
们常用的存储引擎有 InnoDB 和 MyISAM，前者支持行锁和表锁，后者只支持表锁。
如果数据库操作是基于表锁实现的，试想下，如果一张订单表在更新时，需要锁住整张表，
那么其它大量数据库操作（包括查询）都将处于等待状态，这将严重影响到系统的并发性
能。
这时，InnoDB 存储引擎支持的行锁更适合高并发场景。但在使用 InnoDB 存储引擎时，我
们要特别注意行锁升级为表锁的可能。在批量更新操作时，行锁就很可能会升级为表锁
MySQL 认为如果对一张表使用大量行锁，会导致事务执行效率下降，从而可能造成其它事
务长时间锁等待和更多的锁冲突问题发生，致使性能严重下降，所以 MySQL 会将行锁升
级为表锁。还有，行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行
锁也会升级为表锁。
因此，基于表锁的数据库操作，会导致 SQL 阻塞等待，从而影响执行速度。在一些更新操
作（insert\update\delete）大于或等于读操作的情况下，MySQL 不建议使用 MyISAM
存储引擎。
除了锁升级之外，行锁相对表锁来说，虽然粒度更细，并发能力提升了，但也带来了新的问
题，那就是死锁。因此，在使用行锁时，我们要注意避免死锁。
3. 不恰当的 SQL 语句
使用不恰当的 SQL 语句也是慢 SQL 最常见的诱因之一。例如，习惯使用 <SELECT *>，
<SELECT COUNT(*)> SQL 语句，在大数据表中使用 <LIMIT M,N> 分页查询，以及对非
索引字段进行排序等等。
优化 SQL 语句的步骤
通常，我们在执行一条 SQL 语句时，要想知道这个 SQL 先后查询了哪些表，是否使用了索
引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等，我们可以通过 EXPLAIN
命令来查看这些执行信息。这些执行信息被统称为执行计划。
3.1通过 EXPLAIN 分析 SQL 执行计划
假设现在我们使用 EXPLAIN 命令查看当前 SQL 是否使用了索引，先通过 SQL EXPLAIN
导出相应的执行计划如下：
下面对图示中的每一个字段进行一个说明，从中你也能收获到很多零散的知识点。
id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查
询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、
SUBQUERY（子查询）等。
table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
partitions：访问的分区表信息。
type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，
结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。
如果是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底
层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。
eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描
range：索引范围扫描，比如，<，>，between 等操作。
index：索引全表扫描，此时遍历整个索引树。
ALL：表示全表扫描，需要遍历全表来找到对应的行。
possible_keys：可能使用到的索引。
key：实际使用到的索引。
key_len：当前使用的索引的长度。
ref：关联 id 等信息。
rows：查找到记录所扫描的行数。
filtered：查找到所需记录占总扫描记录数的比例。
Extra：额外的信息
3.2 通过 Show Profile 分析 SQL 执行性能
上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们
想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以
选择 Profile。
Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、
BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时
间。以下是相关命令的注释：
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type 参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示 CPU 的相关开销信息
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置 (行数)
| SWAPS：显示 swap 交换次数的相关开销信息值得注意的是，MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，如果你不太确
定的话，可以通过 select @@have_profiling 查询是否支持该功能，如下图所示：
最新的 MySQL 版本是默认开启 Show Profile 功能的，但在之前的旧版本中是默认关闭该
功能的，你可以通过 set 语句在 Session 级别开启该功能：
Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15
条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100。
获取到 Query_ID 之后，我们再通过 Show Profile for Query ID 语句，就能够查看到对应
Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了
通过以上分析可知：SELECT COUNT(*) FROM `order`; SQL 语句在 Sending data 状态所
消耗的时间最长，这是因为在该状态下，MySQL 线程开始读取数据并返回到客户端，此时
有大量磁盘 I/O 操作。
4 常用的 SQL 优化
在使用一些常规的 SQL 时，如果我们通过一些方法和技巧来优化这些 SQL 的实现，在性能
上就会比使用常规通用的实现方式更加优越，甚至可以将 SQL 语句的性能提升到另一个数
量级。
4.1 优化分页查询
通常我们是使用 <LIMIT M,N> + 合适的 order by 来实现分页查询，这种实现方式在没有
任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能将会非常得糟
糕。如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性
能就越差。
这是因为我们在使用 LIMIT 的时候，偏移量 M 在分页越靠后的时候，值就越大，数据库检
索的数据也就越多。例如 LIMIT 10000,10 这样的查询，数据库需要查询 10010 条记录，
最后返回 10 条记录。也就是说将会有 10000 条记录被查询出来没有被使用到
我们模拟一张 10 万数量级的 order 表，进行以下分页查询：
select * from `demo`.`order` order by order_no limit 10000, 20;通过 EXPLAIN 分析可知：该查询使用到了索引，扫描行数为 10020 行，但所用查询时间
为 0.018s，相对来说时间偏长了。
利用子查询优化分页查询
以上分页查询的问题在于，我们查询获取的 10020 行数据结果都返回给我们了，我们能否
先查询出所需要的 20 行数据中的最小 ID 值，然后通过偏移量返回所需要的 20 行数据给
我们呢？我们可以通过索引覆盖扫描，使用子查询的方式来实现分页查询：
通过 EXPLAIN 分析可知：子查询遍历索引的范围跟上一个查询差不多，而主查询扫描了更
多的行数，但执行时间却减少了，只有 0.004s。这就是因为返回行数只有 20 行了，执行
效率得到了明显的提升。
4.2 优化 SELECT COUNT(*)
COUNT() 是一个聚合函数，主要用来统计行数，有时候也用来统计某一列的行数量（不统
计 NULL 值的行）。我们平时最常用的就是 COUNT(*) 和 COUNT(1) 这两种方式了，其实
两者没有明显的区别，在拥有主键的情况下，它们都是利用主键列实现了行数的统计。
但 COUNT() 函数在 MyISAM 和 InnoDB 存储引擎所执行的原理是不一样的，通常在没有
任何查询条件下的 COUNT(*)，MyISAM 的查询速度要明显快于 InnoDB。
这是因为 MyISAM 存储引擎记录的是整个表的行数，在 COUNT(*) 查询操作时无需遍历表
计算，直接获取该值即可。而在 InnoDB 存储引擎中就需要扫描表来统计具体的行数。而
当带上 where 条件语句之后，MyISAM 跟 InnoDB 就没有区别了，它们都需要扫描表来
进行行数的统计。
如果对一张大表经常做 SELECT COUNT(*) 操作，这肯定是不明智的。那么我们该如何对
大表的 COUNT() 进行优化呢？
使用近似值
有时候某些业务场景并不需要返回一个精确的 COUNT 值，此时我们可以使用近似值来代
替。我们可以使用 EXPLAIN 对表进行估算，要知道，执行 EXPLAIN 并不会真正去执行查
询，而是返回一个估算的近似值。
增加汇总统计
如果需要一个精确的 COUNT 值，我们可以额外新增一个汇总统计表或者缓存字段来统计
需要的 COUNT 值，这种方式在新增和删除时有一定的成本，但却可以大大提升 COUNT()
的性能。
4.3 优化 SELECT *
我曾经看过很多同事习惯在只查询一两个字段时，都使用 select * from table where xxx
这样的 SQL 语句，这种写法在特定的环境下会存在一定的性能损耗。
MySQL 常用的存储引擎有 MyISAM 和 InnoDB，其中 InnoDB 在默认创建主键时会创建
主键索引，而主键索引属于聚族索引，即在存储数据时，索引是基于 B + 树构成的，具体
的行数据则存储在叶子节点。
而 MyISAM 默认创建的主键索引、二级索引以及 InnoDB 的二级索引都属于非聚族索引，
即在存储数据时，索引是基于 B + 树构成的，而叶子节点存储的是主键值。
假设我们的订单表是基于 InnoDB 存储引擎创建的，且存在 order_no、status 两列组成的
组合索引。此时，我们需要根据订单号查询一张订单表的 status，如果我们使用 select *
from order where order_no='xxx’来查询，则先会查询组合索引，通过组合索引获取到
主键 ID，再通过主键 ID 去主键索引中获取对应行所有列的值。
如果我们使用 select order_no, status from order where order_no='xxx’来查询，则只
会查询组合索引，通过组合索引获取到对应的 order_no 和 status 的值。
总结
在开发中，我们要尽量写出高性能的 SQL 语句，但也无法避免一些慢 SQL 语句的出现，或
因为疏漏，或因为实际生产环境与开发环境有所区别，这些都是诱因。面对这种情况，我们
可以打开慢 SQL 配置项，记录下都有哪些 SQL 超过了预期的最大执行时间。首先，我们可
以通过以下命令行查询是否开启了记录慢 SQL 的功能，以及最大的执行时间是多少：
Show variables like 'slow_query%';
Show variables like 'long_query_time';
如果没有开启，我们可以通过以下设置来开启：
set global slow_query_log='ON'; // 开启慢 SQL 日志
set global slow_query_log_file='/var/lib/mysql/test-slow.log';// 记录日志地址
set global long_query_time=1;// 最大执行时间
除此之外，很多数据库连接池中间件也有分析慢 SQL 的功能。总之，我们要在编程中避免
低性能的 SQL 操作出现，除了要具备一些常用的 SQL 优化技巧之外，还要充分利用一些
SQL 工具，实现 SQL 性能分析与监控。
二、mysql调优之事务
高并发场景下的数据库事务调优
数据库事务是数据库系统执行过程中的一个逻辑处理单元，保证一个数据库操作要么成功，
要么失败。谈到他，就不得不提 ACID 属性了。数据库事务具有以下四个基本属性：原子
性（Atomicity）、一致性（Consistent）、隔离性（Isolation）以及持久性
（Durable）。正是这些特性，才保证了数据库事务的安全性。而在 MySQL 中，鉴于
MyISAM 存储引擎不支持事务，所以接下来的内容都是在 InnoDB 存储引擎的基础上进行
讲解的。
我们知道，在 Java 并发编程中，可以多线程并发执行程序，然而并发虽然提高了程序的执
行效率，却给程序带来了线程安全问题。事务跟多线程一样，为了提高数据库处理事务的吞
吐量，数据库同样支持并发事务，而在并发运行中，同样也存在着安全性问题，例如，修改
数据丢失，读取数据不一致等。
在数据库事务中，事务的隔离是解决并发事务问题的关键， 今天我们就重点了解下事务隔
离的实现原理，以及如何优化事务隔离带来的性能问题
2.1并发事务带来的问题
我们可以通过以下几个例子来了解下并发事务带来的几个问题：
2.2. 数据丢失
2.3. 脏读
2.4 幻读
事务隔离解决并发问题
以上 4 个并发事务带来的问题，其中，数据丢失可以基于数据库中的悲观锁来避免发生，
即在查询时通过在事务中使用 select xx for update 语句来实现一个排他锁，保证在该事务
结束之前其他事务无法更新该数据
当然，我们也可以基于乐观锁来避免，即将某一字段作为版本号，如果更新时的版本号跟之
前的版本一致，则更新，否则更新失败。剩下 3 个问题，其实是数据库读一致性造成的，
需要数据库提供一定的事务隔离机制来解决
我们通过加锁的方式，可以实现不同的事务隔离机制。在了解事务隔离机制之前，我们不妨
先来了解下 MySQL 都有哪些锁机制。
InnoDB 实现了两种类型的锁机制：共享锁（S）和排他锁（X）。共享锁允许一个事务读
数据，不允许修改数据，如果其他事务要再对该行加锁，只能加共享锁；排他锁是修改数据
时加的锁，可以读取和修改数据，一旦一个事务对该行数据加锁，其他事务将不能再对该数
据加任务锁。
熟悉了以上 InnoDB 行锁的实现原理，我们就可以更清楚地理解下面的内容
在操作数据的事务中，不同的锁机制会产生以下几种不同的事务隔离级别，不同的隔离级别
分别可以解决并发事务产生的几个问题，对应如下：
未提交读（Read Uncommitted）：在事务 A 读取数据时，事务 B 读取和修改数据加了
共享锁。这种隔离级别，会导致脏读、不可重复读以及幻读
已提交读（Read Committed）：在事务 A 读取数据时增加了共享锁，一旦读取，立即释
放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事
务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B
才能修改。这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。
可重复读（Repeatable Read）：在事务 A 读取数据时增加了共享锁，事务结束，才释放
锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务
A 在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能
修改。这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。
可序列化（Serializable）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，
事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏
读、不可重复读、幻读等问题，但隔离级别越来越高的同时，并发性会越来越低
InnoDB 中的 RC 和 RR 隔离事务是基于多版本并发控制（MVVC）实现高性能事务。一旦
数据被加上排他锁，其他事务将无法加入共享锁，且处于阻塞等待状态，如果一张表有大量
的请求，这样的性能将是无法支持的
MVVC 对普通的 Select 不加锁，如果读取的数据正在执行 Delete 或 Update 操作，这时
读取操作不会等待排它锁的释放，而是直接利用 MVVC 读取该行的数据快照（数据快照是
指在该行的之前版本的数据，而数据快照的版本是基于 undo 实现的，undo 是用来做事务
回滚的，记录了回滚的不同版本的行记录）。MVVC 避免了对数据重复加锁的过程，大大
提高了读操作的性能。
其实 MySQL 的并发事务调优和 Java 的多线程编程调优非常类似，都是可以通过减小锁粒
度和减少锁的持有时间进行调优。在 MySQL 的并发事务调优中，我们尽量在可以使用低
事务隔离级别的业务场景中，避免使用高事务隔离级别。
在功能业务开发时，开发人员往往会为了追求开发速度，习惯使用默认的参数设置来实现业
务功能。例如，在 service 方法中，你可能习惯默认使用 transaction，很少再手动变更事
务隔离级别。但要知道，transaction 默认是 RR 事务隔离级别，在某些业务场景下，可能
并不合适。因此，我们还是要结合具体的业务场景，进行考虑。
三、mysql调优之索引
不知道你是否跟我有过同样的经历，那就是作为一个开发工程师，经常被 DBA 叫过去“批
评”，而最常见的就是申请创建新的索引或发现慢 SQL 日志了。
记得之前有一次迭代一个业务模块的开发，涉及到了一个新的查询业务，需要根据商品类
型、订单状态筛选出需要的订单，并以订单时间进行排序。由于 sku 的索引已经存在了，
我在完成业务开发之后，提交了一个创建 status 的索引的需求，理由是 SQL 查询需要使用
到这两个索引：
select * from order where status =1 and sku=10001 order by
create_time asc
然而，DBA 很快就将这个需求驳回了，并给出了重建一个 sku、status 以及 create_time
组合索引的建议，查询顺序也改成了 sku=10001 and status=1。当时我是知道为什么要
重建组合索引，但却无法理解为什么要添加 create_time 这列进行组合。
MySQL 索引存储结构
索引是优化数据库查询最重要的方式之一，它是在 MySQL 的存储引擎层中实现的，所以
每一种存储引擎对应的索引不一定相同。我们可以通过下面这张表格，看看不同的存储引擎
分别支持哪种索引类型：
B+Tree 索引和 Hash 索引是我们比较常用的两个索引数据存储结构，B+Tree 索引是通过
B+ 树实现的，是有序排列存储，所以在排序和范围查找方面都比较有优势。如果你对
B+Tree 索引不够了解，可以通过该链接了解下它的数据结构原理。
Hash 索引相对简单些，只有 Memory 存储引擎支持 Hash 索引。Hash 索引适合 keyvalue 键值对查询，无论表数据多大，查询数据的复杂度都是 O(1)，且直接通过 Hash 索
引查询的性能比其它索引都要优越。
在创建表时，无论使用 InnoDB 还是 MyISAM 存储引擎，默认都会创建一个主键索引，而
创建的主键索引默认使用的是 B+Tree 索引。不过虽然这两个存储引擎都支持 B+Tree 索
引，但它们在具体的数据存储结构方面却有所不同。
InnoDB 默认创建的主键索引是聚族索引（Clustered Index），其它索引都属于辅助索引
（Secondary Index），也被称为二级索引或非聚族索引。接下来我们通过一个简单的例
子，说明下这两种索引在存储数据中的具体实现
首先创建一张商品表，如下：
CREATE TABLE `merchandise` (
`id` int(11) NOT NULL,
`serial_no` varchar(20) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`unit_price` decimal(10, 2) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;然后新增了以下几行数据，如下：
如果我们使用的是 MyISAM 存储引擎，由于 MyISAM 使用的是辅助索引，索引中每一个
叶子节点仅仅记录的是每行数据的物理地址，即行指针，如下图所示：
当我们在使用复合索引时，需要使用索引中的最左边的列进行查询，才能使用到复合索引。
例如我们在 order 表中建立一个复合索引 idx_user_order_status(order_no, status, us
er_id)，如果我们使用 order_no、order_no+status、order_no+status+user_id 以及
order_no+user_id 组合查询，则能利用到索引；而如果我们用 status、status+user_id
查询，将无法使用到索引，这也是我们经常听过的最左匹配原则。
如果查询条件中使用 or，且 or 的前后条件中有一个列没有索引，那么涉及的索引都不会被
使用到。
所以，你懂了吗？作为一名开发人员，如果没有熟悉 MySQL，特别是 MySQL 索引的基础
知识，很多时候都将被 DBA 批评到怀疑人生。
总结
在大多数情况下，我们习惯使用默认的 InnoDB 作为表存储引擎。在使用 InnoDB 作为存
储引擎时，创建的索引默认为 B+ 树数据结构，如果是主键索引，则属于聚族索引，非主键
索引则属于辅助索引。基于主键查询可以直接获取到行信息，而基于辅助索引作为查询条
件，则需要进行回表，然后再通过主键索引获取到数据。
如果只是查询一列或少部分列的信息，我们可以基于覆盖索引来避免回表。覆盖索引只需要
读取索引，且由于索引是顺序存储，对于范围或排序查询来说，可以极大地极少磁盘 I/O
操作。
除了了解索引的具体实现和一些特性，我们还需要注意索引失效的情况发生。如果觉得这些
规则太多，难以记住，我们就要养成经常检查 SQL 执行计划的习惯
四、记一次线上sql死锁事故
之前我参与过一个项目，在项目初期，我们是没有将读写表分离的，而是基于一个主库完成
读写操作。在业务量逐渐增大的时候，我们偶尔会收到系统的异常报警信息，DBA 通知我
们数据库出现了死锁异常。
按理说业务开始是比较简单的，就是新增订单、修改订单、查询订单等操作，那为什么会出
现死锁呢？经过日志分析，我们发现是作为幂等性校验的一张表经常出现死锁异常。我们和
DBA 讨论之后，初步怀疑是索引导致的死锁问题。后来我们在开发环境中模拟了相关操
作，果然重现了该死锁异常
接下来我们就通过实战来重现下该业务死锁异常。首先，创建一张订单记录表，该表主要用
于校验订单重复创建
CREATE TABLE `order_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` int(11) DEFAULT NULL,
`status` int(4) DEFAULT NULL,
`create_date` datetime(0) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_order_status`(`order_no`,`status`) USING BTREE
) ENGINE = InnoDB为了能重现该问题，我们先将事务设置为手动提交。这里要注意一下，MySQL 数据库和
Oracle 提交事务不太一样，MySQL 数据库默认情况下是自动提交事务，我们可以通过以
下命令行查看自动提交事务是否开启：
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)下面就操作吧，先将 MySQL 数据库的事务提交设置为手动提交，通过以下命令行可以关
闭自动提交事务：
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
订单在做幂等性校验时，先是通过订单号检查订单是否存在，如果不存在则新增订单记录。
知道具体的逻辑之后，我们再来模拟创建产生死锁的运行 SQL 语句。首先，我们模拟新建
两个订单，并按照以下顺序执行幂等性校验 SQL 语句（垂直方向代表执行的时间顺序）：
此时，我们会发现两个事务已经进入死锁状态。我们可以在 information_schema 数据库
中查询到具体的死锁情况，如下图所示：
看到这，你可能会想，为什么 SELECT 要加 for update 排他锁，而不是使用共享锁呢？试
想下，如果是两个订单号一样的请求同时进来，就有可能出现幻读。也就是说，一开始事务
A 中的查询没有该订单号，后来事务 B 新增了一个该订单号的记录，此时事务 A 再新增一
条该订单号记录，就会创建重复的订单记录。面对这种情况，我们可以使用锁间隙算法来防
止幻读。
死锁是如何产生的？
上面我们说到了锁间隙，在第 33 讲中，我已经讲过了并发事务中的锁机制以及行锁的具体
实现算法，不妨回顾一下。
行锁的具体实现算法有三种：record lock、gap lock 以及 next-key lock。record lock
是专门对索引项加锁；gap lock 是对索引项之间的间隙加锁；next-key lock 则是前面两
种的组合，对索引项以其之间的间隙加锁。
只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock，在
Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取
gap lock 或 next-key lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键
索引是不会使用 gap lock 或 next-key lock。
在 MySQL 中，gap lock 默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是
disable 的，且 MySQL 中默认的是 RR 事务隔离级别。
当我们执行以下查询 SQL 时，由于 order_no 列为非唯一索引，此时又是 RR 事务隔离级
别，所以 SELECT 的加锁类型为 gap lock，这里的 gap 范围是 (4,+∞）。
SELECT id FROM demo.order_record where order_no = 4 for
update;
执行查询 SQL 语句获取的 gap lock 并不会导致阻塞，而当我们执行以下插入 SQL 时，会
在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 gap lock 是
冲突的，所以当其它事务持有该间隙的 gap lock 时，需要等待其它事务释放 gap lock 之
后，才能获取到插入意向锁。
以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插
入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。
INSERT INTO demo.order_record(order_no, status, create_date)
VALUES (5, 1, ‘2019-07-13 10:57:03’);我们可以通过以下锁的兼容矩阵图，来查看锁的兼容性：
避免死锁的措施
知道了死锁问题源自哪儿，就可以找到合适的方法来避免它了。
避免死锁最直观的方法就是在两个事务相互等待时，当一个事务的等待时间超过设置的某一
阈值，就对这个事务进行回滚，另一个事务就可以继续执行了。这种方法简单有效，在
InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的。
另外，我们还可以将 order_no 列设置为唯一索引列。虽然不能防止幻读，但我们可以利用
它的唯一性来保证订单记录不重复创建，这种方式唯一的缺点就是当遇到重复创建订单时会
抛出异常。
我们还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及
ZooKeeper 来实现，运行效率比数据库更佳。
其它常见的 SQL 死锁问题
这里再补充一些常见的 SQL 死锁问题，以便你遇到时也能知道其原因，从而顺利解决。
我们知道死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生
死锁，这些条件必然成立。所以在一些经常需要使用互斥共用一些资源，且有可能循环等待
的业务场景中，要特别注意死锁问题
接下来，我们再来了解一个出现死锁的场景。
我们讲过，InnoDB 存储引擎的主键索引为聚簇索引，其它索引为辅助索引。如果使用辅助
索引来更新数据库，就需要使用聚簇索引来更新数据库字段。如果两个更新事务使用了不同
的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环
等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了
我们还是以上面的这个订单记录表来重现下聚簇索引和辅助索引更新时，循环等待锁资源导
致的死锁问题
出现死锁的步骤：
综上可知，在更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不
必要的死锁发生
总结
数据库发生死锁的概率并不是很大，一旦遇到了，就一定要彻查具体原因，尽快找出解决方
案，老实说，过程不简单。我们只有先对 MySQL 的 InnoDB 存储引擎有足够的了解，才
能剖析出造成死锁的具体原因。
例如，以上我例举的两种发生死锁的场景，一个考验的是我们对锁算法的了解，另外一个考
验则是我们对聚簇索引和辅助索引的熟悉程度
解决死锁的最佳方式当然就是预防死锁的发生了，我们平时编程中，可以通过以下一些常规
手段来预防死锁的发生：
1. 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条
相同的记录，但更新顺序不一样，有可能导致死锁；
2. 在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导
致的死锁问题；
3. 更新表时，尽量使用主键更新；
4. 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
5. 设置锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时
阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等
待，占用系统资源，造成严重的性能开销
在当今互联网时代，海量数据基本上是每一个成熟产品的共性，特别是在移动互联网产品
中，几乎每天都在产生数据，例如，商城的订单表、支付系统的交易明细以及游戏中的战报
等等。
对于一个日活用户在百万数量级的商城来说，每天产生的订单数量可能在百万级，特别在一
些活动促销期间，甚至上千万。
假设我们基于单表来实现，每天产生上百万的数据量，不到一个月的时间就要承受上亿的数
据，这时单表的性能将会严重下降。因为 MySQL 在 InnoDB 存储引擎下创建的索引都是
基于 B+ 树实现的，所以查询时的 I/O 次数很大程度取决于树的高度，随着 B+ 树的树高
增高，I/O 次数增加，查询性能也就越差。
当我们面对一张海量数据的表时，通常有分区、NoSQL 存储、分表分库等优化方案。
分区的底层虽然也是基于分表的原理实现的，即有多个底层表实现，但分区依然是在单库下
进行的，在一些需要提高并发的场景中的优化空间非常有限，且一个表最多只能支持 1024
个分区。面对日益增长的海量数据，优化存储能力有限。不过在一些非海量数据的大表中，
我们可以考虑使用分区来优化表性能。
分区表是由多个相关的底层表实现的，这些底层表也是由句柄对象表示，所
以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普
通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是
在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表
和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表，还是一
个分区表的一部分
而 NoSQL 存储是基于键值对存储，虽然查询性能非常高，但在一些方面仍然存在短板。例
如，不是关系型数据库，不支持事务以及稳定性方面相对 RDBMS 差一些。虽然有些
NoSQL 数据库也实现了事务，宣传具有可靠的稳定性，但目前 NoSQL 还是主要用作辅助
存储
五、什么时候需要分库分表
分析完了分区、NoSQL 存储优化的应用，接下来我们就看看这讲的重头戏——分表分库。
在我看来，能不分表分库就不要分表分库。在单表的情况下，当业务正常时，我们使用单表
即可，而当业务出现了性能瓶颈时，我们首先考虑用分区的方式来优化，如果分区优化之后
仍然存在后遗症，此时我们再来考虑分表分库。
我们知道，如果在单表单库的情况下，当数据库表的数据量逐渐累积到一定的数量时
（5000W 行或 100G 以上），操作数据库的性能会出现明显下降，即使我们使用索引优化
或读写库分离，性能依然存在瓶颈。此时，如果每日数据增长量非常大，我们就应该考虑分
表，避免单表数据量过大，造成数据库操作性能下降
面对海量数据，除了单表的性能比较差以外，我们在单表单库的情况下，数据库连接数、磁
盘 I/O 以及网络吞吐等资源都是有限的，并发能力也是有限的。所以，在一些大数据量且
高并发的业务场景中，我们就需要考虑分表分库来提升数据库的并发处理能力，从而提升应
用的整体性能
如何分表分库？
通常，分表分库分为垂直切分和水平切分两种。
垂直分库是指根据业务来分库，不同的业务使用不同的数据库。例如，订单和消费券在抢购
业务中都存在着高并发，如果同时使用一个库，会占用一定的连接数，所以我们可以将数据
库分为订单库和促销活动库
而垂直分表则是指根据一张表中的字段，将一张表划分为两张表，其规则就是将一些不经常
使用的字段拆分到另一张表中。例如，一张订单详情表有一百多个字段，显然这张表的字段
太多了，一方面不方便我们开发维护，另一方面还可能引起跨页问题。这时我们就可以拆分
该表字段，解决上述两个问题。
水平分表则是将表中的某一列作为切分的条件，按照某种规则（Range 或 Hash 取模）来
切分为更小的表。
水平分表只是在一个库中，如果存在连接数、I/O 读写以及网络吞吐等瓶颈，我们就需要考
虑将水平切换的表分布到不同机器的库中，这就是水平分库分表了。
结合以上垂直切分和水平切分，我们一般可以将数据库分为：单库单表 - 单库多表 - 多库
多表。在平时的业务开发中，我们应该优先考虑单库单表；如果数据量比较大，且热点数据
比较集中、历史数据很少访问，我们可以考虑表分区；如果访问热点数据分散，基本上所有
的数据都会访问到，我们可以考虑单库多表；如果并发量比较高、海量数据以及每日新增数
据量巨大，我们可以考虑多库多表
这里还需要注意一点，我刚刚强调过，能不分表分库，就不要分表分库。这是因为一旦分
表，我们可能会涉及到多表的分页查询、多表的 JOIN 查询，从而增加业务的复杂度。而一
旦分库了，除了跨库分页查询、跨库 JOIN 查询，还会存在跨库事务的问题。这些问题无疑
会增加我们系统开发的复杂度。
分表分库之后面临的问题
然而，分表分库虽然存在着各种各样的问题，但在一些海量数据、高并发的业务中，分表分
库仍是最常用的优化手段。所以，我们应该充分考虑分表分库操作后所面临的一些问题，接
下我们就一起看看都有哪些应对之策。
为了更容易理解这些问题，我们将对一个订单表进行分库分表，通过详细的业务来分析这些
问题。
假设我们有一张订单表以及一张订单详情表，每天的数据增长量在 60W 单，平时还会有一
些促销类活动，订单增长量在千万单。为了提高系统的并发能力，我们考虑将订单表和订单
详情表做分库分表。除了分表，因为用户一般查询的是最近的订单信息，所以热点数据比较
集中，我们还可以考虑用表分区来优化单表查询。
通常订单的分库分表要么基于订单号 Hash 取模实现，要么根据用户 ID Hash 取模实现。
订单号 Hash 取模的好处是数据能均匀分布到各个表中，而缺陷则是一个用户查询所有订单
时，需要去多个表中查询。
由于订单表用户查询比较多，此时我们应该考虑使用用户 ID 字段做 Hash 取模，对订单表
进行水平分表。如果需要考虑高并发时的订单处理能力，我们可以考虑基于用户 ID 字段
Hash 取模实现分库分表。这也是大部分公司对订单表分库分表的处理方式。
5.1 分布式事务问题
在提交订单时，除了创建订单之外，我们还需要扣除相应的库存。而订单表和库存表由于垂
直分库，位于不同的库中，这时我们需要通过分布式事务来保证提交订单时的事务完整性。
通常，我们解决分布式事务有两种通用的方式：两阶事务提交（2PC）以及补偿事务提交
（TCC）。有关分布式事务的内容，我将在第 41 讲中详细介绍。
通常有一些中间件已经帮我们封装好了这两种方式的实现，例如 Spring 实现的 JTA，目前
阿里开源的分布式事务中间件 Fescar，就很好地实现了与 Dubbo 的兼容。
5.2 跨节点 JOIN 查询问题
用户在查询订单时，我们往往需要通过表连接获取到商品信息，而商品信息表可能在另外一
个库中，这就涉及到了跨库 JOIN 查询。
通常，我们会冗余表或冗余字段来优化跨库 JOIN 查询。对于一些基础表，例如商品信息
表，我们可以在每一个订单分库中复制一张基础表，避免跨库 JOIN 查询。而对于一两个字
段的查询，我们也可以将少量字段冗余在表中，从而避免 JOIN 查询，也就避免了跨库
JOIN 查询.
5.3 跨节点分页查询问题
我们知道，当用户在订单列表中查询所有订单时，可以通过用户 ID 的 Hash 值来快速查询
到订单信息，而运营人员在后台对订单表进行查询时，则是通过订单付款时间来进行查询
的，这些数据都分布在不同的库以及表中，此时就存在一个跨节点分页查询的问题了。
通常一些中间件是通过在每个表中先查询出一定的数据，然后在缓存中排序后，获取到对应
的分页数据。这种方式在越往后面的查询，就越消耗性能。
通常我们建议使用两套数据来解决跨节点分页查询问题，一套是基于分库分表的用户单条或
多条查询数据，一套则是基于 Elasticsearch、Solr 存储的订单数据，主要用于运营人员根
据其它字段进行分页查询。为了不影响提交订单的业务性能，我们一般使用异步消息来实现
Elasticsearch、Solr 订单数据的新增和修改。
5.4全局主键 ID 问题
在分库分表后，主键将无法使用自增长来实现了，在不同的表中我们需要统一全局主键
ID。因此，我们需要单独设计全局主键，避免不同表和库中的主键重复问题。
使用 UUID 实现全局 ID 是最方便快捷的方式，即随机生成一个 32 位 16 进制数字，这种
方式可以保证一个 UUID 的唯一性，水平扩展能力以及性能都比较高。但使用 UUID 最大
的缺陷就是，它是一个比较长的字符串，连续性差，如果作为主键使用，性能相对来说会比
较差。
我们也可以基于 Redis 分布式锁实现一个递增的主键 ID，这种方式可以保证主键是一个整
数且有一定的连续性，但分布式锁存在一定的性能消耗。
我们还可以基于 Twitter 开源的分布式 ID 生产算法——snowflake 解决全局主键 ID 问
题，snowflake 是通过分别截取时间、机器标识、顺序计数的位数组成一个 long 类型的主
键 ID。这种算法可以满足每秒上万个全局 ID 生成，不仅性能好，而且低延时。
5.5 扩容问题
随着用户的订单量增加，根据用户 ID Hash 取模的分表中，数据量也在逐渐累积。此时，
我们需要考虑动态增加表，一旦动态增加表了，就会涉及到数据迁移问题。
我们在最开始设计表数据量时，尽量使用 2 的倍数来设置表数量。当我们需要扩容时，也
同样按照 2 的倍数来扩容，这种方式可以减少数据的迁移量。
总结
在业务开发之前，我们首先要根据自己的业务需求来设计表。考虑到一开始的业务发展比较
平缓，且开发周期比较短，因此在开发时间比较紧的情况下，我们尽量不要考虑分表分库。
但是我们可以将分表分库的业务接口预留，提前考虑后期分表分库的切分规则，把该冗余的
字段提前冗余出来，避免后期分表分库的 JOIN 查询等。
当业务发展比较迅速的时候，我们就要评估分表分库的必要性了。一旦需要分表分库，就要
结合业务提前规划切分规则，尽量避免消耗性能的跨表跨库 JOIN 查询、分页查询以及跨库
事务等操作。
六、电商系统表设计优化案例分析
如果在业务架构设计初期，表结构没有设计好，那么后期随着业务以及数据量的增多，系统
就很容易出现瓶颈。如果表结构扩展性差，业务耦合度将会越来越高，系统的复杂度也将随
之增加。这一讲我将以电商系统中的表结构设计为例，为你详讲解在设计表时，我们都需要
考虑哪些因素，又是如何通过表设计来优化系统性能
核心业务
要懂得一个电商系统的表结构设计，我们必须先得熟悉一个电商系统中都有哪些基本核心业
务。这部分的内容，只要你有过网购经历，就很好理解。
一般电商系统分为平台型和自营型电商系统。平台型电商系统是指有第三方商家入驻的电商
平台，第三方商家自己开设店铺来维护商品信息、库存信息、促销活动、客服售后等，典型
的代表有淘宝、天猫等。而自营型电商系统则是指没有第三方商家入驻，而是公司自己运营
的电商平台，常见的有京东自营、苹果商城等。
两种类型的电商系统比较明显的区别是卖家是 C 端还是 B 端，很显然，平台型电商系统的
复杂度要远远高于自营型电商系统。为了更容易理解商城的业务，我们将基于自营型电商系
统来讨论表结构设计优化，这里以苹果商城为例。
一个电商系统的核心业务肯定就是销售商品了，围绕销售商品，我们可以将核心业务分为以
下几个主要模块：
6.1 商品模块
商品模块主要包括商品分类以及商品信息管理，商品分类则是我们常见的大分类了，有人喜
欢将分类细化为多个层级，例如，第一个大类是手机、电视、配件等，配件的第二个大类又
分为耳机、充电宝等。为了降低用户学习系统操作的成本，我们应该尽量将层级减少。
当我们通过了分类查询之后，就到了商品页面，一个商品 Item 包含了若干商品 SKU。商
品 Item 是指一种商品，例如 IPhone9，就是一个 Item，商品 SKU 则是指具体属性的商
品，例如金色 128G 内存的 IPhone9。
6.2 购物车模块
购物车主要是用于用户临时存放欲购买的商品，并可以在购物车中统一下单结算。购物车一
般分为离线购物车和在线购物车。离线购物车则是用户选择放入到购物车的商品只保存在本
地缓存中，在线购物车则是会同步这些商品信息到服务端。
目前大部分商城都是支持两种状态的购物车，当用户没有登录商城时，主要是离线购物车在
记录用户的商品信息，当用户登录商城之后，用户放入到购物车中的商品都会同步到服务
端，以后在手机和电脑等不同平台以及不同时间都能查看到自己放入购物车的商品。
6.3 订单模块
订单是盘活整个商城的核心功能模块，如果没有订单的产出，平台将难以维持下去。订单模
块管理着用户在平台的交易记录，是用户和商家交流购买商品状态的渠道，用户可以随时更
改一个订单的状态，商家则必须按照业务流程及时订单的更新状态，告知用户已购买商品的
具体状态。
通常一个订单分为以下几个状态：待付款、待发货、待收货、待评价、交易完成、用户取
消、仅退款、退货退款状态。一个订单的流程见下图：
6.4 库存模块
这里主要记录的是商品 SKU 的具体库存信息，主要功能包括库存交易、库存管理。库存交
易是指用户购买商品时实时消费库存，库存管理主要包括运营人员对商品的生产或采购入
库、调拨。
一般库存信息分为商品 SKU、仓区、实时库存、锁定库存、待退货库存、活动库存。
现在大部分电商都实现了华南华北的库存分区，所以可能存在同一个商品 SKU 在华北没有
库存，而在华南存在库存的情况，所以我们需要有仓区这个字段，用来区分不同地区仓库的
同一个商品 SKU。
实时库存则是指商品的实时库存，锁定库存则表示用户已经提交订单到实际扣除库存或订单
失效的这段时间里锁定的库存，待退货库存、活动库存则分别表表示订单退款时的库存数量
以及每次活动时的库存数量。
除了这些库存信息，我们还可以为商品设置库存状态，例如虚拟库存状态、实物库存状态。
如果一个商品不需要设定库存，可以任由用户购买，我们则不需要在每次用户购买商品时都
去查询库存、扣除库存，只需要设定商品的库存状态为虚拟库存即可。
6.5 促销活动模块
促销活动模块是指消费券、红包以及满减等促销功能，这里主要包括了活动管理和交易管
理。前者主要负责管理每次发放的消费券及红包有效期、金额、满足条件、数量等信息，后
者则主要负责管理用户领取红包、消费券等信息。
业务难点
了解了以上那些主要模块的具体业务之后，我们就可以更深入地去评估从业务落实到系统实
现，可能存在的难点以及性能瓶颈了。
总结
这一讲，我们结合电商系统实战练习了如何进行表设计，可以总结为以下几个要点：
在字段比较复杂、易变动、不方便统一的情况下，建议使用键值对来代替关系数据库表存
储；
在高并发情况下的查询操作，可以使用缓存代替数据库操作，提高并发性能；
数据量叠加比较快的表，需要考虑水平分表或分库，避免单表操作的性能瓶颈；
除此之外，我们应该通过一些优化，尽量避免比较复杂的 JOIN 查询操作，例如冗余一些
字段，减少 JOIN 查询；创建一些中间表，减少 JOIN 查询。
七、数据库参数设置优化，失之毫厘差之千里
MySQL 是一个灵活性比较强的数据库系统，提供了很多可配置参数，便于我们根据应用和
服务器硬件来做定制化数据库服务。如果现在让你回想，你可能觉得在开发的过程中很少去
调整 MySQL 的配置参数，但我今天想说的是我们很有必要去深入了解它们。
我们知道，数据库主要是用来存取数据的，而存取数据涉及到了磁盘 I/O 的读写操作，所
以数据库系统主要的性能瓶颈就是 I/O 读写的瓶颈了。MySQL 数据库为了减少磁盘 I/O 的
读写操作，应用了大量内存管理来优化数据库操作，包括内存优化查询、排序以及写入操
作。
也许你会想，我们把内存设置得越大越好，数据刷新到磁盘越快越好，不就对了吗？其实不
然，内存设置过大，同样会带来新的问题。例如，InnoDB 中的数据和索引缓存，如果设置
过大，就会引发 SWAP 页交换。还有数据写入到磁盘也不是越快越好，我们期望的是在高
并发时，数据能均匀地写入到磁盘中，从而避免 I/O 性能瓶颈
SWAP 页交换：SWAP 分区在系统的物理内存不够用的时候，就会把物理内
存中的一部分空间释放出来，以供当前运行的程序使用。被释放的空间可能
来自一些很长时间没有什么操作的程序，这些被释放的空间的数据被临时保
存到 SWAP 分区中，等到那些程序要运行时，再从 SWAP 分区中恢复保存
的数据到内存中。
所以，这些参数的设置跟我们的应用服务特性以及服务器硬件有很大的关系。MySQL 是一
个高定制化的数据库，我们可以根据需求来调整参数，定制性能最优的数据库。
不过想要了解这些参数的具体作用，我们先得了解数据库的结构以及不同存储引擎的工作原
理。
MySQL 体系结构
我们一般可以将 MySQL 的结构分为四层，最上层为客户端连接器，主要包括了数据库连
接、授权认证、安全管理等，该层引用了线程池，为接入的连接请求提高线程处理效率。
第二层是 Server 层，主要实现 SQL 的一些基础功能，包括 SQL 解析、优化、执行以及缓
存等，其中与我们这一讲主要相关的就是缓存。
第三层包括了各种存储引擎，主要负责数据的存取，这一层涉及到的 Buffer 缓存，也和这
一讲密切相关。
最下面一层是数据存储层，主要负责将数据存储在文件系统中，并完成与存储引擎的交互。
接下来我们再来了解下，当数据接收到一个 SQL 语句时，是如何处理的
7.1 查询语句
一个应用服务需要通过第一层的连接和授权认证，再将 SQL 请求发送至 SQL 接口。SQL
接口接收到请求之后，会先检查查询 SQL 是否命中 Cache 缓存中的数据，如果命中，则直
接返回缓存中的结果；否则，需要进入解析器。
解析器主要对 SQL 进行语法以及词法分析，之后，便会进入到优化器中，优化器会生成多
种执行计划方案，并选择最优方案执行。
确定了最优执行计划方案之后，执行器会检查连接用户是否有该表的执行权限，有则查看
Buffer 中是否存在该缓存，存在则获取锁，查询表数据；否则重新打开表文件，通过接口
调用相应的存储引擎处理，这时存储引擎就会进入到存储文件系统中获取相应的数据，并返
回结果集。
7.2 更新语句
数据库更新 SQL 的执行流程其实跟查询 SQL 差不多，只不过执行更新操作的时候多了记录
日志的步骤。在执行更新操作时 MySQL 会将操作的日志记录到 binlog（归档日志）中，
这个步骤所有的存储引擎都有。而 InnoDB 除了要记录 binlog 之外，还需要多记录一个
redo log（重做日志）。
redo log 主要是为了解决 crash-safe 问题而引入的。我们知道，当数据库在存储数据时发
生异常重启，我们需要保证存储的数据要么存储成功，要么存储失败，也就是不会出现数据
丢失的情况，这就是 crash-safe 了。
我们在执行更新操作时，首先会查询相关的数据，之后通过执行器执行更新操作，并将执行
结果写入到内存中，同时记录更新操作到 redo log 的缓存中，此时 redo log 中的记录状
态为 prepare，并通知执行器更新完成，随时可以提交事务。执行器收到通知后会执行
binlog 的写入操作，此时的 binlog 是记录在缓存中的，写入成功后会调用引擎的提交事
务接口，更新记录状态为 commit。之后，内存中的 redo log 以及 binlog 都会刷新到磁
盘文件中
7.3 内存调优
基于以上两个 SQL 执行过程，我们可以发现，在执行查询 SQL 语句时，会涉及到两个缓
存。第一个缓存是刚进来时的 Query Cache，它缓存的是 SQL 语句和对应的结果集。这里
的缓存是以查询 SQL 的 Hash 值为 key，返回结果集为 value 的键值对，判断一条 SQL
是否命中缓存，是通过匹配查询 SQL 的 Hash 值来实现的。
很明显，Query Cache 可以优化查询 SQL 语句，减少大量工作，特别是减少了 I/O 读取
操作。我们可以通过以下几个主要的设置参数来优化查询操作：
我们可以通过设置合适的 query_cache_min_res_unit 来减少碎片，这个参数最合适的大小
和应用程序查询结果的平均大小直接相关，可以通过以下公式计算所得：
（query_cache_size - Qcache_free_memory）/ Qcache_queries_in_cache
Qcache_free_memory 和 Qcache_queries_in_cache 的值可以通过以下命令查询：
show status like 'Qcache%'
Query Cache 虽然可以优化查询操作，但也仅限于不常修改的数据，如果一张表数据经常
进行新增、更新和删除操作，则会造成 Query Cache 的失效率非常高，从而导致频繁地清
除 Cache 中的数据，给系统增加额外的性能开销。
这也会导致缓存命中率非常低，我们可以通过以上查询状态的命令查看 Qcache_hits，该
值表示缓存命中率。如果缓存命中率特别低的话，我们还可以通过 query_cache_size = 0
或者 query_cache_type 来关闭查询缓存。
经过了 Query Cache 缓存之后，还会使用到存储引擎中的 Buffer 缓存。不同的存储引
擎，使用的 Buffer 也是不一样的。这里我们主要讲解两种常用的存储引擎。
MySQL 数据库的参数设置非常多，今天我们仅仅是了解了与内存优化相关的参数设置。除
了这些参数设置，我们还有一些常用的提高 MySQL 并发的相关参数设置，总结如下：
八、
