MySQL的原理和实践？

MYSQL 的基础架构——sql 查询语句的执行流程

Mysql 的逻辑架构主要包括三部分：Mysql 客户端 Server 层和存储引擎层。

下面是一条连接命令

$ mysql -h$ip -u$user -p

Mysql 客户端：连接命令中的 mysql 是客户端工具，用来和服务端建立连接，完成经典的 TCP 握手之后，连接器就要开始认证身份。

如果认证通过，那么读出权限表里面查询该用户拥有的权限，这个连接里面的权限判断逻辑都依赖于此时读到权限。也就是说如果用管理员帐号对这个用户的权限进行修改，这种修改不会影响该连接，因为该连接需要的权限已经读取。如果客户端太长时间没有动静，那么连接器就会自动断开。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果要继续，就需要重连，然后再执行请求了。

Server 层：『连接器』『查询缓存』『分析器，优化器，执行器』

Server 层功能：内置函数，触发器，存储过程，视图。

『连接器』：管理连接，权限验证。
『查询缓存』：命中就直接返回结果
『分析器』：词法分析，语法分析
『优化器』：生成具体的执行流程，选择索引。
『执行器』：操作引擎，返回结果。

一句话总结就是：通过 mysql 连接器的权限验证之后，sql 语句被交给 mysql 分析器分析这条语句的语法和词法，然后通过 mysql 优化器选择索引并生成这条语句的具体的执行流程，最后具体的执行流程被交给 mysql 执行器，mysql 执行器选择要存储引擎，返回结果。

『连接器』

客户端连接分为长连接和短连接，长连接是指连接成功以后，如果客户端持续有需求，则一直使用同一个连接，短连接是指每次执行很少几次查询后就断开连接，下次查询就再重新建立一个。

所以尽量减少使用连接的动作，但是全部使用长连接以后，有的时候 Mysql 占用的内存涨的特别块。这个是因为 MYSQL 再执行的过程中使用的内存是管理在连接对象里面的，资源只有在断开连接的时候才会释放。长连接累积下来，就可能导致内存占用很大，被系统强行杀掉。那么就会导致 MYSQL 异常重启了。

MYSQL 长连接导致内存过大被系统杀死后，异常重启问题的解决：定期断开长连接，使用一段时间，或者在程序执行了一个占用很大内存的大查询之后，断开连接，之后的查询要重连。具体解决方案是：每次执行完大操作之后，执行 mysql_reset_connection 来重新初始化连接。这个过程不需要重连和做权限验证，只是恢复连接刚刚创建完成的状态。

『查询缓存』

连接建立完毕，就可以执行 select 语句，那么执行逻辑来到第二步：查询缓存。MYSQL 拿到一个查询请求之后，就会先到查询缓存看看，查询缓存顾名思义就是：查询语句的缓存。先看看查询缓存里面是不是有执行过该语句，如果执行过，那么之前执行的语句和结果可能被以 KEY——VALUE 的形式直接缓存到内存，KEY 是查询的语句，VALUE 是查询的结果。如果查询能在缓存中找到 KEY，那么 VALUE 就会被直接返回给客户端。

如果语句不在查询缓存中，那么继续执行后面的阶段，执行结束之后，执行结果被存入查询缓存。

如果查询直接命中缓存，那么 MYSQL 就不需要执行后面复杂的操作，直接返回给客户端。

但是不建议使用查询缓存，这是为什么？因为查询缓存的缺点大于优点。因为查询缓存的失效非常的频繁，一个更新操作更新一个表，那么对这个表的所有查询缓存都会被清空。对于更新压力大的表，查询缓存的命中非常的低。除非表是一张静态表。

具体的做法是：设置为 DEMAND `那么默认就不使用缓存，如果是对于要使用缓存的语句，那么就用

mysql> select SQL_CACHE *from T where ID=10;

『分析器』

mysql> select * from tbl_user where id =10;

如果没有命中缓存，那么开始词法分析，识别 select 这是一个查询语句,tbl_user 这是一个表名，ID 识别为列名，继续进行语法分析，是 select 而不是 elect,注意语法错误会提示第一个出现错误的位置。

『优化器』

mysql> select * from tbl_user join tbl_student using(ID) where tbl_user.id =10 tbl_student.id=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。在这一阶段还涉及到优化器如何选择索引的问题。

『执行器』:

mysql> select * from tbl_user where id =10;

在执行的时候，先判断该用户对表又没有执行的权限，如果没有，那么返回错误。如果有权限，那么就打开表，按照该表的引擎继续执行. 那么这个时候，执行器的流程是：调用引擎接口取表的第一行，判断 id =10 是否成立？如果成立就把结果存储在结果集当中，不成立就跳过。调取引擎接口取下一行，重复，直到最后一行。最后把结果集返回给客户端。

存储引擎层：负责数据的存储和提取。

ip:连接指定 ip 地址的数据库
u:指定用户
p:执行密码

MYSQL 日志系统——sql 更新语句的执行过程

mysql> update tbl_user set name="AnNa" where id =10;

『分析器』做了什么？

词法分析和语法分析解析知道这个是一个更新语句

『优化器』做了什么？

优化器决定使用 ID 这个索引

『执行器』做了什么？

执行器负责具体执行

但是和查询语句不同的是更新语句涉及到重要的日志模块，那就是 redo log (重做日志)和 binlog（归档日志）

redo log (重做日志)和 binlog（归档日志）有很多有意思的设计思路可以借鉴到自己的代码中。

redolog

对于一个小酒馆，可以赊账和还账。那么作为 boss 有两种操作

方法 1：对于每一个来赊账和还账和人，直接账本上记录谁赊账或者还账。方法 2：对于每一个来赊账和还账和人，先把他们的赊账或者还账信息记录在黑板上，等到没有人的时候再拿出账本进行操作。

这两种方法的不同是：多次拿出账本和一次性拿出账本。

因为更新操作需要写磁盘，并且是找到磁盘中对应的记录并且写磁盘，整个过程的 I/O 成本，查找成本很高，为了解决这个问题，那么就采用了一种技术叫做——WAL 技术（Write-Ahead-Logging）关键就是先写日志，再写磁盘。

具体操作是：当有一条记录需要更新的时候，InnoDB 先把记录写到 redo log 并更新内存，InnoDB 会在适当的时候把这个操作记录到磁盘。但是 redo log 的大小是固定的，可以配置一组四个文件，每个文件的大小是 1GB，那么这个 redo log 就总共旧可以记录 4GB 的操作。

如下所示：

ib_logfile_0 ib_logfile_1 ib_logfile_2 ib_logfile_3

假设 write pos 当前指向 ib_logfile_3，checkpoint 当前指向 ib_logfile_1

一个 write pos 用来记录当前记录的位置，一边写一边往后移，写到 ib_logfile_3 这个文件之后就回到 ib_logfile_0 文件开头的位置。循环写，checkpoint 是当前要擦除的位置，也是往后推移并且循环的。擦出之前要把记录更新到数据文件。只有 checkpoint 和 write pos 之间的位置可以用来写新操作。如果 write pos 追上 checkpoint，那么意味着粉版已经满了，这个时候不能再继续执行新的操作，需要先停下来擦出一些记录。

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

redo log 是 InnoDB 引擎特有的日志，而 binlog 是 server 层自己的日志。InnoDB 是另外一家公司以插件的形式引入 MYSQL 的。只依靠 binlog 是没有办法保证 crash safe 的。

binlog

bin log 和 redo log 的不同是：

redo log 是 InnoDB 引擎特有的日志。binlog 是 MYSQL Server 层的日志，不论什么存储引擎都可以使用。

redo log 是物理日志：记录在哪个数据页上做了什么修改，bin log 是逻辑日志，记录的是语句的原始逻辑。（比如把 ID=2 行的 Name 列的改为=“LiHua”）

redo log 是循环写，空间肯定会用完。binlog 是追加写，不会覆盖之前的内容。

执行器和 InnoDB 引擎在执行 Update 操作时的内部流程。

mysql>  update tbl_user set name="LiHua" where id =10

1.执行器先拿引擎取出 id=10 的这一行，id 是主键，引擎直接用树搜索找到这一行。如果 id=10 这个数据在内存就直接返回。否则要从磁盘读入内存，然后再返回。

2.执行器把这行数据更改为”LiHua”，然后再次调用引擎接口写入这行新数据。

3.引擎将这行新数据更新到内存，同时记录更新操作到 redolog .此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4.执行器生成这个操作的 binlog,并把 binlog 写入磁盘。

5.执行器调用引擎的事务提交接口，引擎把刚写入的 redolog 改为提交状态。更新完成。

redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。

重点是：存储引擎把更新的行数据写入内存，并且把更新操作写入 redo log,变更 redo log 的状态为 prepare。然后执行器生成这个操作的 binlog，并且把 bin log 写入磁盘。然后执行器调用引擎的事务提交接口，引擎把刚才写入的 redo log 变更为 commit 提交状态。

也就是引擎把行数据写入到内存以后，生成的 redolog 是 prepare 状态，等待执行器生成 bin log，并且把 bin log 写入到磁盘，才会变更 redo log 状态为 commit 状态。

二阶段提交？

数据恢复：

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那可以这么做：

1.首先找到最近的一次全量备份。2.取出 binlog 重放.

为什么需要二阶段提交？

二阶段提交是跨系统维持数据的逻辑一致性的一个方案。 binlog 的作用是用来备份恢复数据。redlog 记录对数据物理修改，在哪个数据页上做了什么修改。

事务隔离

事务就是保证一组数据库操作要么全部成功，要么全部失败。在 MYSQL 里面，事务的支持是在引擎层实现的。InnoD 除了支持 redolog,还支持事务。

隔离性和隔离级别

隔离性由锁机制实现，原子性、一致性、持久性都是通过数据库的 redo 日志和 undo 日志来完成

事务：ACID（A 原子性）（C 一致性）（I 隔离性）（D 持久性）

为什么要有隔离级别？答：多个事务同时执行就会出现“幻读”等问题，所以为了防止出现这种问题，就出现了“隔离级别”的概念。

隔离的越严，效率肯定越低。为了平衡效率，于是有下面几种隔离级别。

读未提交：一个事务未提交的数据也可以被其他事务看到。

读提交：一个事务提交之后，它做的变更才能被其他事务看到

可重复读：一个事务在执行过程中看到的数据总是和这个事务在启动的时候看到的一样。

串行化：一个事务等待另外一个事务执行结束。（原因是对同一行数据写的时候会加锁，出现锁冲突的时候，后一个等待前一个）

设置隔离级别：

set [global|session] transaction isolation level {read uncommitted|...

事务隔离的实现

场景：1->2->3->4 （更新操作）

假设： read-viewA 读视图看到的是 1->2 read-viewB 读视图看到的是 2->3 read-viewC 读视图看到的是 3->4

（因为不同时刻启动的事务看到的视图不同）如果是事务 A 想回滚得到 1，必须依次执行图中所有的回滚操作得到。即便有另外一个事务正在把 4->5，那么这个事务跟 read-viewA、read-viewB、read-viewC 是不会冲突的。

InnoDB 的实现

隔离性由锁机制实现，原子性、一致性、持久性都是通过数据库的 redo 日志和 undo 日志来完成。 redo 日志：重做日志，它记录了事务的行为； undo 日志：对数据库进行修改时会产生 undo 日志，也会产生 redo 日志，使用 rollback 请求回滚时通过 undo 日志将数据回滚到修改前的样子。 InnoDB 存储引擎回滚时，它实际上做的是与之前相反的工作，insert 对应 delete，delete 对应 insert，update 对应相反的 update。

事务的启动方式

显式启动事务语句。

begin
commit
rollback
begin=start transaction

set autocommit=0 这个会把这个线程的自动提交关闭。意味着一个事务开启之后就不会关闭，直到主动执行 commit 或者 rollback 语句，再或者断开连接。

有些客户端框架会默认连接成功以后先执行一个 set autocommit=0 的命令，导致接下来的查询都是事务中，如果是长连接，就导致了意外的长事务。

所以对于一个频繁使用事务的业务而言：方法 1：使用 set autocommit=1,通过显示的语句来启动事务。方法 2：commit work and chain 是提交事务，并且启动下一个事务。

有些语句会造成隐式提交，主要有 3 类：

DDL 语句：create event、create index、alter table、create database、truncate 等等，所有 DDL 语句都是不能回滚的。

权限操作语句：create user、drop user、grant、rename user 等等。

管理语句：analyze table、check table 等等。

索引

索引就是书的目录

索引的三个模型：

哈希表模型：哈希表是 key-value 的数据结构，实现思路是把值存在数组，用一个哈希函数把 key 换算成具体的位置，然后把 value 放在这个数组的这个具体位置。多个 KEY 可能换算出相同的位置，这个时候就是拉出一个链表。

哈希表这种数据结构只适合：等值查询的场景。区间查询是很慢速的。

有序数组模型：有序数组适合等值查询和范围查询。有序数组只适合静态存储引擎。有序数组的时间复杂度是：O（log(N)）

二叉树：二叉树的搜索效率是 O（log(N)）但是大多数数据存储并不使用二叉树，原因是：索引不仅仅在内存，还在磁盘。

可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

不管是哈希还是有序数组，或者 N 叉树，它们都是不断迭代、不断优化的产物或者解决方案。数据库技术发展到今天，跳表、LSM 树等数据结构也被用于引擎设计中

数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

InnoDB 的索引模型

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

分布式（补充）

分布式场景下：要么同时成功，要么同时失败。

场景：每一组业务处理流程中会包含多组动作（A、B…），每个动作可能关联到分布式环境下的不同系统。但是这些动作之间需要保证一致性：A 和 B 动作必须同时成功或失败，不能出现 A 动作成功 B 动作失败这类问题。

这类问题在单机下，用单机数据库事务就可以解决，为什么分步式系统中会变得如此复杂？

分布式系统是异步系统，异步系统最大的问题是：超时。超时一定有可能发生，但是超时后无法判断一个操作究竟是成功还是失败，导致业务状态异常。

一致性的两种翻译(consistency/consensus)是不同的概念，不同于 consistency，consensus 问题是为了解决若干进程对同一个变量达成一致的问题，例如集群的 leader 选举问题。consensus 问题通常使用 Paxos 或 Raft 协议来实现。

分布式的理论基础——CAP 理论

C:consistency:写操作之后的读操作必须返回最新写的数据。 A:Availability:服务在正常的响应时间内一直是可用的，返回的状态是成功。 P:Partition-tolerance：分区容错性：遇到某个节点，或者网络故障以后，系统仍然可以提供正常的服务。

CAP 中最最最重要的是：PARTITION TOLERANCE（分区容错性）所以 CAP 可以理解为：当 P（网络发生分区）时，要么选择 C（一致性）要么选择 A（可用性）

对于一个支付例子，当网络发生分区的时候，要么用户可以支付（可用性），但是余额不会立即减少，要么用户不能支付（不可用），这样的话，用户的余额保持一致。

CAP 理论是由 Eric Brewer 在 2000 年提出的，用于描述分布式系统中三个基本的保证属性：

C - Consistency（一致性）: 所有节点在同一时刻应该看到相同的数据。换句话说，如果一个操作在分布式系统中的一个节点上完成，那么其他所有节点都应该同步地达到相同的状态。

A - Availability（可用性）: 在对分布式系统进行读或写操作时，系统应该保证响应，即使其中一部分节点不可用。简单地说，每个请求都应该得到一个（可能是不一致的）响应，而不是错误或超时。

P - Partition Tolerance（分区容忍性）: 系统应该在网络分区（即，节点之间的通信故障）的情况下继续运行。分区容忍性意味着系统应该能够容忍网络失败，即使这导致了系统组件之间的通信中断。

CAP 理论的核心观点是，在任何给定的时间，分布式系统只能同时满足这三个属性中的两个。这通常被表述为 “只能选两个”，并且这种权衡在设计和运行分布式系统时是非常重要的。

为什么只能选两个？

CAP 理论的核心观点是，在一个分布式系统中，一致性（Consistency）、可用性（Availability）和分区容忍性（Partition Tolerance）这三个属性是不可能同时完全达到的。这主要是因为以下几点原因：

网络分区是不可避免的：在现实世界的分布式系统中，网络分区（即，节点间通信的中断或延迟）是不可避免的。因此，分区容忍性（P）通常是必须要考虑的。

一致性与可用性的权衡：

如果想保证一致性（C），那么在网络分区或其他故障发生时，可能需要牺牲可用性（A）来确保所有节点都达到一致的状态。这可能意味着拒绝或延迟服务请求直到系统恢复一致。如果想保证可用性（A），则在网络分区或其他故障发生时，可能需要牺牲一致性（C）来继续处理请求。这可能意味着返回可能不一致或过时的数据。实际操作中的权衡：在实际的系统设计中，通常会根据业务需求和特定场景来进行权衡，选择更加偏向于一致性或可用性的设计。例如，一些系统可能可以容忍短暂的不一致，以换取更高的可用性；而另一些系统可能需要严格的一致性保证。

因此，当说“只能选两个”时，通常的含义是，在面对网络分区这一不可避免的现实时（即，P 是必选的），必须在一致性（C）和可用性（A）之间做出选择。

分布式的理论基础——BASE 理论

CAP 理论中需要考虑 A 和 C 的取舍，当 A（可用性！！！）很很很重要的时候，这个时候它的替代方案就是 BASE（Basically Available 基本可用/Soft State 软状态/Eventual consistent 最终一致性） BASE 并不是一个明确的 AP 或者 CP 方案，而是在 A 和 C 之间倾向于 A，思路是用最终一致性替代强一致性。

三个关键点：

数据分片，拆分服务：通过数据分片的方式将服务拆分到不同的系统中，这样即使出现故障也只会影响一个分片而不是所有用户。 数据存在中间状态：允许数据存在中间状态，也就是事务的隔离性可能不能保证 经过同步后最终是一致的：所有的数据/系统状态，在经过一段时间的同步或者补偿之后，最终都能够达到一个一致的状态

一致性问题的解决方案

基于 2PC 的分布式事务

两个角色：一个节点是协调者，其他节点是参与者。

执行过程是：参与者将执行结果的成败通知协调者，由协调者决定参与者本地要不要提交自己的修改。（或者是终止操作）

基于 TCC 的补偿型事务

TCC 和 2PC 的本质区别是：把事务框架从资源层上升到了服务层，TCC 要求开发者按照这个框架进行编程，业务逻辑的每个分支都有 Try Confirm Cancel 三个操作集合。

主业务服务
tryX
confirmX
cancelX
DB

具体实现：

一个完整的业务活动由一个主业务服务和若干从业务活动组成。主业务活动负责发起并完成整个业务活动。从业务服务提供 Try Confirm Cacel 三个业务操作。

Try:负责资源的检查，同时必须要预留资源，而且预留的资源要支持提交和回滚。 Confirm:负责使用 Try 预留的资源真正的完成业务动作。 Cancel:负责释放 Try 预留的资源。

举个栗子：

XX 给我转账 100，这里是有两个操作系统，一个是支付，一个是财务。各自有自己的数据库和服务。要求支付了一定要存在收据。在这个过程里面支付系统是支付的发起方，财务系统是参与方。TCC 的第一阶段，会执行所有参与方的 Try 操作，如果所有的 Try 操作都成功，那么就会记录日志并继续执行发起方的业务操作。TCC 的二阶段根据第一阶段 Try 的结果来决定整体是 Confirm 还是 Cancel

TCC 是一种服务层的事务模式，因此在业务中就需要充分考虑到分布式事务的中间状态。在这个例子中，Try 必须真在起到预留资源的作用。冻结余额就是资源在预留层的体现。而未达金额就是还未到账的金额。TCC 保证的是最终一致性，因此在财务的模型里面：可用余额=账户余额+冻结余额+未达余额。TCC 的一阶段就是在发起方的本地事务里面执行的。

如果一阶段，即发起方的本地事务中失败，则本地事务直接回滚，然后继续触发二阶段的 Cancel 如果二阶段未能成功，那么就需要一个新的恢复系统，从发起方的 DB 扫描异常事务，进行重试。

基于可靠消息的一致性模型

为什么基于可靠消息也可以保证系统间的一致性？

答：消息中间件就是一个可靠消息的平台，因为消息中间件：异步+持久化+重试的机制保证了消息一定会被消费者消费，可以用于一定能成功的业务场景。

只能保证消息一定被消费，但是无法提供全局的回滚。

一定会成功的场景：发通知等等 不一定会成功的场景：减少用户钱包的余额

基于事务的消息

参考阿里云 RocketMQ 的官方文档为例：整个流程需要有两个参与者，一个是消息发送方，一个是消息订阅方。消息发送方的本地事务和消息订阅方的消费需要保证一致性。类似于两阶段提交，事务消息也是把一次消息发送拆成了两个阶段：首先消息发送方会发送一个“半事务消息”，然后再执行本地事务，根据本地事务执行的结果，来给消息服务端再发送一个 commit 或 rollback 的确认消息。只有服务端收到 commit 消息后，才会真正的发送消息给订阅方。

基于本地的消息

本地事务+本地消息表也是实现可靠消息的一种方式。只需要在本地的数据库维持一个本地消息表。把发送消息动作转变为本地消息表的 insert 操作。然后用定时任务从本地消息表里面取出数据来发送消息。实现本地事务和消息消费方的一致性。

虽然实现上比较简单，但是本地消息表需要和本地业务操作在同一个 OLTP 数据库中，对于分库分表等分布式存储方案可能不太适合.

异常处理：本地消息的异常情况更加简单，如果是本地事务执行过程中的异常，事务会自动回滚，也不用担心会有消息被发送出去。而只要事务成功提交了，定时任务就会捞起未处理的消息并发送，即使发送失败也会在下个定时周期重试补偿。此时唯一需要关注的点在于消息接收方需要针对消息体进行幂等处理，保证同一个消息体只会被真正消费一次。

Soga 模式

Saga 模式的理论最初来自于普林斯顿大学的 Hector Garcia-Molina 和 Kenneth Salem 发表的一篇论文。最初是为了解决长事务带来的性能损耗，可以拆分成若干个子事务，然后通过依次执行或补偿来实现一致性。

关键点：拆分若干事务，依次执行/补偿若干事务。

使用场景：分布式场景下跨微服务的数据一致性。

执行过程：每个事务更新各自的服务并发布消息触发下一个服务，如果某个事务执行失败，那么就依次向前执行补偿动作来抵消以前的子事务。

总结：

针对系统之间的一致性问题，老生常谈的方案就是分布式事务。如果业务场景对一致性要求很高，如支付、库存，这种场景的确需要分布式事务——通常会采用 2PC 的变种或者 TCC 来实现。分布式事务的确对于提高一致性的强度有很大帮助，但是开发难度和复杂度会比较高，对于一些普通的业务场景来说性价比不高。参考 BASE 定理，更多的场景适合用可靠消息或者重试模式来实现一致性。

Mysql 索引

InnoDB 存储引擎的逻辑存储结构：

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

回到主键索引树搜索的过程，我们称之为回表。

如何索引优化避免回表？

覆盖索引

select ID from T where k between 3 and 5

只需要查 ID ，ID 的值在 k 索引树上
可以直接提供查询的结果，不需要回表
索引 K 覆盖了查询需求（所以叫覆盖索引）

使用覆盖索引减少了回表的次数，显著提升查询性能，所以使用覆盖索引是常用的性能优化手段

在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA，或者称为业务数据架构师的工作。

最左前缀原则

如果为每一种查询都设计一个索引，索引是不是太多了。如果我现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。假设是一个（姓名，年龄）的索引

（李四）（张三，10）（张三，11）（张三，12）（王五）

当的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果要查的是所有名字第一个字是“张”的人，的 SQL 语句的条件是”where name like ‘张 %’”。这时，也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

只要满足最左前缀，就可以利用索引来加速检索

在建立联合索引的时候，如何安排索引内的字段顺序。当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。我们要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持“根据身份证号查询地址”的需求。

索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，可能要问，那些不符合最左前缀的部分，会怎么样呢？

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select \* from tuser where name like '张 %' and age=10;

假设有索引 name age 索引下推就是对于索引name age 里面的一条记录，先判断 name 和 age 符不符合条件，然后找到后再到主键索引中间拿出所有的字段。

但是如果只有name这个索引，那么先判断name=’张 %’ 是否符合条件，如果符合条件就回到主键索引，再判断 age 等不等于 10，如果不是，那么这个时候就比name age 多回了一次表

全局锁和表锁

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

全局锁

MySQL 提供了一个加全局读锁的方法

Flush tables with read lock

FTWRL 的典型使用场景是：在做全库的逻辑备份的时候，就是把每个表都存成文本形式。但是这种情况导致业务停摆。

如果不加锁出现的问题是：备份过程中在不同的时间节点看到的视图可能不一致。

那么如何拥有一致性视图？

四个隔离级别：读提交，读未提交，可重复读，串行化。可重复读：事务开始后，看到的数据都是事务最开始看到的数据。那么解决办法就是在：可重复读隔离级别下开启一个事务。

MYSQL 官方自带的逻辑备份工具是：mysqldump，当 mysqldump 使用参数-single-transcation 的时候，导数据之前就会启动一个事务确保拿到一致性视图。由于 MVCC 支持的导出数据的过程中间，可以正常的更新 MYSQL 里面的数据。

mysqldump

既然有 mysqldump 那么为什么还需要 FTWRL，MYSQL 的 MyISAM 引擎不支持事务。 FTWRL 和 set global readonly=true 有什么区别？

在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高

表级锁

对表做增删改查操作的时候会加 MDL 读锁。如果是对表结构做更改，那么加 MDL 写锁。

为什么多个线程可以对同一张表进行增删改查的操作？

MYSQL 引入了 MDL（metadata lock）默认是对表加读锁，但是读锁之间不互斥，所以多个线程可以同时对一张表增删改查

对表结构进行修改的时候，是否可以继续读出数据，并进行（meta data 的增删改查操作）？

不可以，修改表结构的时候对表加写锁。读锁和写锁是互斥的。也就是说：增删改查操作后如果没有释放 MDL 读锁，那么 MDL 写锁就会被阻塞。在一个事务中间，一个修改表字段的 MDL 写锁被阻塞是没有关系的，但是存在的问题是这个 MDL 写锁会一致阻塞后来的 MDL 读锁。这旧造成了很大的影响。

事务中的 MDL 写锁是语句开始的时候申请的，但是语句结束后并不会马上释放，而是等到事务提交以后被释放。

如何安全的对表加字段？

事务不提交不会释放 MDL 写锁，所以在 MYSQL 的 information_schema 库里面的 innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

存在的问题是：如果我需要变更一个热点表，虽然数据不大，但是上面的请求很频繁，但是不得不加个字段，那么应该怎么做？比较好的解决方案是：在 Alter table 语句里面设定等待时间，如果在这个时间里能够拿到 MDL 写锁最好，如果拿不到也没关系，就先放弃，不要阻塞后面的业务。

ALTER TABLE tbl_name NOWAIT add column

ALTER TABLE tbl_name WAIT N add column

行锁

怎么减少行锁对性能的影响？

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

场景：『1』从顾客 A 账户余额中扣除电影票价；『2』给影院 B 的账户余额增加这张电影票价；『3』记录一条交易日志。『4』试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，会怎样安排这三个语句在事务中的顺序呢？

根据两阶段锁协议，不论怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

死锁和死锁检测

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

循环等待
不可剥夺
资源互斥
请求对资源保持不释放

出现死锁问题的解决：

设置 innodb_lock_wait_timeout (死锁超时)
设置 innodb_lock_detect (死锁检测)

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

那如果是我们上面说到的所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

问题在于每个来到线程都要判断是否会因自己的加入而导致死锁。

如何解决因热点行的更新而导致性能问题？

解决方案 1——关闭死锁检测

确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。**但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

解决方案 2——控制并发数量，如果并发数数量很少，死锁检测的成本不高。但是哪怕一个客户端 2 ～ 3 个并发数量，几百个客户端导致的并发数量也很多。所以并发控制做在数据库服务端，如果有中间件也考虑放在中间件实现。

总结

全局表是用于在备份的逻辑中，对于使用 InnoDB 引擎的库就使用 single-transaction,或者可重复读隔离级别下+事务。

表级锁是在数据库引擎不支持行锁的时候才会被用到的。如果代码有 lock tables 这样的语句，那么需要追查一下。比较可能的情况是：系统使用 MyISAM 这类不支持事务的引擎，那么需要安排升级换引擎。MDL 是事务提交以后才会释放，在做表结构更新的时候，加 MDL 写锁会导致锁住线上查询和更新。

两阶段协议为起点，如何安排正确的事务语句。这里的原则是：如果的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

调整语句顺序并不能完全避免死锁。所以我们引入了死锁和死锁检测的概念，以及提供了三个方案，来减少死锁对数据库的影响。减少死锁的主要方向，就是控制访问相同资源的并发事务量。

第一种，直接执行 delete from T limit 10000; 第二种，在一个连接中循环执行 20 次 delete from T limit 500; 第三种，在 20 个连接中同时执行 delete from T limit 500

三个方案该如何选择？答：第二种方式是相对较好的。第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

索引选择

普通索引和唯一索引——查询过程

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

但是二者的差别很小，原因在于：InnoDB 的数据是按照数据页为单位来读写的。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

普通索引和唯一索引——更新过程

更新操作要判断，如果对应的数据页在内存，就直接更新，要么存储在 change buffer

change buffer 用来存储更新操作，等到更新操作对应的数据页被加载到内存的时候持久化数据。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

change buffer 的使用场景是在写多读少的情况下。在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大

索引选择优化

总的来说，普通索引和更新索引在查询上面没有区别，在更新多的情况的选择普通索引。如果在更新的操作后面立马伴随的是对记录的查询，那么就应该关闭 change buffer

普通索引+change buffer 的操作对更新的优化明显。

change buffer 和 redo log

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

这条更新语句做了如下的操作（按照图中的数字顺序）：

Page 1 在内存中，直接更新内存； Page 2 没有在内存，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息在 redo log 中间记录两个操作：add (id1,k1)和 new change buffer item “add id2,k2” to Page 2

读 Page 1 的时候，直接从内存返回。读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。

所以说：redo log 主要节省的是随机写磁盘的消耗。chang buffer 主要节省的是随机读磁盘的消耗。

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

set slow_query_time =0;
select * from t where a between 10000 and 20000;
select * from t force index(a) where a between 10000 and 20000;

第一句，是将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；

选择索引是优化器的工作,而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

优化器需要做的事——判断扫描的行数

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

可以通过show index查看到一个索引的基数。

一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）

基数的统计是采样统计得到的。

优化器需要做的事——先其他索引再回表走主键 OR 直接走主键索引

如果使用索引，每次从索引上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。

而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。

优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

那么如何修复这个问题？

analyze table t

重新统计索引信息。

索引选择异常和处理

方法 1：

select * from t force index() where

开发的时候通常不会先写上 force index。而是等到线上出现问题的时候，才会再去修改 SQL 语句、加上 force index。但是修改之后还要测试和发布，对于生产系统来说，这个过程不够敏捷

方法 2：

考虑修改语句，引导 MySQL 使用我们期望的索引。

select * from t force  where a between 1 and 1000  order by a limit 1;

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

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;

如果要使用邮箱登陆，如果不加索引就会走全表扫描。MYSQL 支持前缀索引，如果创建索引不指定前缀长度，那么索引就会包含整个字符串。email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email=’zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

但是对于前缀区分度不高的例子怎么办？

第一种方式是使用倒序存储。

mysql> select field_list from t where id_card = reverse('input_id_card_string');

第二种方式是使用 hash 字段。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以的查询语句 where 部分要判断 id_card 的值是否精确相同。

它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询。

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。

在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

总结：字符串创建索引的方式有：直接创建完整索引：可能比较占用空间创建前缀索引，节省空间，但是查询的时候会增加扫描的次数。倒序存储，再创建前缀索引。创建 hash 字段索引，查询性能优秀，但是有额外的存储和计算消耗，不知处范围扫描。

如果在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号 @gmail.com”, 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。

系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，会怎么设计这个登录名的索引呢？

用数字类型来存这 9 位数字。比如 201100001，这样只需要占 4 个字节。其实这个就是一种 hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性。

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

MYSQL 如何回收空间的方法？

针对 MySQL 中应用最广泛的 InnoDB 引擎，表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

一个表单独存储为一个文件更容易管理，而且在不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

将 innodb_file_per_table 设置为 ON，是推荐做法，我们接下来的讨论都是基于这个设置展开的。

我们在删除整个表的时候，可以使用 drop table 命令回收表空间。但是，我们遇到的更多的删除数据的场景是删除某些行，这时回到开始的问题：表中的数据被删除了，但是表空间却没有被回收。

假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

数据页的复用跟记录的复用是不同的。记录的复用，只限于符合范围条件的数据。比如上面的这个例子，R4 这条记录被删除后，如果插入一个 ID 是 400 的行，可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个位置了。

如果将数据页 page A 上的所有记录删除以后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录需要使用新页的时候，page A 是可以被复用的。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

进一步地，如果我们用 delete 命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

删除整张表格的时候，所有的数据页仅仅也只是被标记为可复用。磁盘文件并不会缩小。通过 delete 不能回收表空间。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

重建表就可以解决这个问题，新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。

Online DDL 来重建表

引入了 Online DDL 之后，重建表的流程：

建立一个临时文件，扫描表 A 主键的所有数据页；用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中。临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件。用临时文件替换表 A 的数据文件。

这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源.

但是 DDL 操作是要拿到 MDL 写锁的。alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。因为 MDL 读锁不会阻塞增删改操作。

可以用使用 GitHub 开源的 gh-ost 来做。

optimize table、analyze table 和 alter table

重建表的这个语句 alter table t engine=InnoDB，其实隐含的意思是：

alter table t engine=innodb,algorithm=inplace;

alter table t engine=innodb,algorithm=copy;

analyze table不是重建表，而是对表的索引信息重新做统计，没有修改数据 optmize table等于 recreate+analyze

如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，还要通过 alter table 命令重建表，才能达到表文件变小的目的。重建表的两种实现方式，Online DDL 的方式是可以考虑在业务低峰期使用的

count()

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

 select count(*) from t

count()的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；

而 InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的

假设表 t 中现在有 10000 条记录，我们设计了三个用户并行的会话。

会话 A 先启动事务并查询一次表的总行数；会话 B 启动事务，插入一行后记录后，查询表的总行数；会话 C 先启动一个单独的语句，插入一行记录后，查询表的总行数。

这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count() 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

InnoDB 是索引组织表，逐渐索引树的叶子节点是数据，普通索引的叶子节点是主键值。普通索引树比主键索引树小很多。

对于 count() 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

用过 show table status 命令的话，就会发现这个命令的输出结果里面也有一个 TABLE_ROWS 用于显示这个表当前有多少行，这个命令执行挺快的，那这个 TABLE_ROWS 能代替 count() 吗？

索引统计的值是通过采样来估算的。实际上，TABLE_ROWS 就是从这个采样估算得来的，因此它也很不准。有多不准呢，官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用。

MyISAM 表虽然 count() 很快，但是不支持事务； show table status 命令虽然返回很快，但是不准确； InnoDB 表直接 count() 会遍历全表，虽然结果准确，但会导致性能问题。

如何优化？

用 Redis 来支持，每插入一行计数+1 删除-1

更新很频繁的库来说，可能会第一时间想到，用缓存系统来支持。

可以用一个 Redis 服务来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1，每被删除一行 Redis 计数就减 1。这种方式下，读和更新操作都很快，但再想一下这种方式存在什么问题吗？

没错，缓存系统可能会丢失更新

但实际上，将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使 Redis 正常工作，这个值还是逻辑上不精确的。

可以设想一下有这么一个页面，要显示操作记录的总数，同时还要显示最近操作的 100 条记录。那么，这个页面的逻辑就需要先到 Redis 里面取出计数，再到数据表里面取数据记录。

我们是这么定义不精确的：

一种是，查到的 100 行结果里面有最新插入记录，而 Redis 的计数里还没加 1；另一种是，查到的 100 行结果里没有最新插入的记录，而 Redis 的计数里已经加了 1。

这两种情况，都是逻辑不一致的。

在数据库保存计数

如果我们把这个计数直接放到数据库里单独的一张计数表 C 中，又会怎么样呢？

按照事务的可重复读隔离级别：但是因为这时候更新事务还没有提交，所以计数值加 1 这个操作对会话 B 还不可见。

count 的用法

count()、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能，有哪些差别?

count 是个聚合函数，对于返回的结果集，一行一行的判断，如果 count 的参数不是 null ,累计值就+1，否则不加，最后返回累计值。

分析性能差别的时候，可以记住这么几个原则： server 层要什么就给什么 InnoDB 只给必要的数值优化器只优化了 count() 的语义为“取行数”其他“显而易见”的优化并没有做。

count(主键 id) 来说：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加

按照效率排序： count()>count(1)>count(主键id)>count(字段)

日志相关问题

取 ID=2 这一行
数据页是否再内存中？
是
返回行数据
将这行的数据的 C+1
写 redo log （处于 prepare 阶段）
写 bin log
提交事务（处于 commit 阶段）

“commit 语句”执行的时候，会包含“commit 步骤”。

binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整： a. 如果是，则提交事务； b. 否则，回滚事务。

MySQL 怎么知道 binlog 是完整的?

答： statement 格式的 binlog，最后会有 COMMIT； row 格式的 binlog，最后会有一个 XID event。

redo log 和 binlog 是怎么关联起来的? 答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

如果这样的话，为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？答：对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。

那能不能只用 redo log，不要 binlog？答：binlog 用于归档

redo log 一般设置多大？答：redo log 设置为 4 个文件、每个文件 1GB

Tags: Mysql