MySQL 实战 45 讲

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

MySQL的更新流程。

首先判断要修改的数据页是否在内存中，如果不在的话则去磁盘里面取出来然后放到内存里。在的话就直接写。

然后把把数据更新到内存。

写入redolog 处于prepare阶段

写入binlog

提交事务，把redolog改成commit阶段

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

Untitled

4 深入浅出索引 上

索引的结构

Untitled

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

问题

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

最后，我给你留下一个问题吧。对于上面例子中的 InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写

alter table T drop index k;
alter table T add index(k);

如果你要重建主键索引，也可以这么写：

alter table T drop primary key;
alter table T add primary key(id);

我的问题是，对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB(这个语句在innobDB里会触发mysql重建该表，并进行碎片处理)。

4 深入浅出索引 下

索引覆盖

索引包含的信息就足以满足查询要求，不需要回表（回表指的是通过索引查到主键之后通过主键去表里查询记录的过程）。

如果我们有一个高频请求，是在市民表通过身份证查询名字。这个时候建立索引（身份证号、姓名），就可以简单通过索引来查到名字，而不需要回表。

最左前缀原则

对于索引(a, b)，如果查询字段是a，那还是可以用这个索引的。

所以索引(a, b)可以用来查询字段a,b，也可以单独查询字段a。如果还要单独查询b的话，就需要建立一个索引b。

如果能通过调整顺序，减少维护一个索引是最好的。

如果a和b都有分别查询的需求，我们需要创建(a, b),(b)还是(b, a), (a)呢？可以从空间的角度来衡量，单独为a字段创建索引还是为b字段创建索引更节省空间。

索引下推

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

查询

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

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

也就是说，对于这个索引(name, age)，在遍历这个索引的时候可以事先把age ≠10的排除掉，就不用去回表了。

没有索引下推：

Untitled

索引下推：

Untitled

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

全局锁

Flush tables with read lock (FTWRL)

全局备份的时候用，这是为了解决备份时事务不一致问题，所以需要加锁。举个例子，比如用户买课这个场景，会使得余额表-20，课程表多出来一门课。如果先备份了余额表，然后用户买课，这个时刻开始备份，买完课之后课程表多了一门课，并且扣了钱。所以备份的数据就会备份购买之前的余额，以及购买之后的课程表，就会使得事务不一致。

Untitled

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

为什么有了这个功能，还需要全局锁？

因为可能有不支持事务的引擎。

你也许会问，既然要全库只读，为什么不使用 set global readonly=true 的方式呢？确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有两个原因：一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

另外表级的锁是 MDL（metadata lock)MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

这个的作用就是在修改表结构的时候加写锁，增删改查的时候加读锁。所以修改表结构的时候就不能进行增删改查，但是增删改查之间不影响。

Untitled

上面这张图，Session A开启了事务，所以上了读锁。sessionB也需要读锁所以不影响。但是session C需要的是写锁，它就阻塞了。它阻塞会导致后面的需要加读锁的session D也阻塞了，整个表就不能读写了。

这里的解决方案是，

  1. 排查长事务问题。
  2. 执行改表操作的时候设定一个等待时间，如果超过了这个时间就自动放弃。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

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

两阶段锁

行锁在需要的时候添加，但是要在事务结束之后才释放。

死锁和死锁检测

Untitled

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

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

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

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

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

怎么解决由这种热点行更新导致的性能问题呢？

  1. 在客户端里控制并发度，比如同一个客户端最多10个线程同时更新。但这样也有问题，如果有600个客户端，每个客户端5个并发线程，这样也有3000个了。
  2. 修改mysql源码，对于相同行的更新，进入引擎之前排队。
  3. 你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。

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

这里说到可重复读是怎么实现的，以及可重复读的快照实现原理。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

Untitled

对于上面这个图，事务A查询到的是1，而事务B查询到的值是3。

先简单说一下我自己的理解：事务B在查询之前做了一次更新，这次更新要依赖于id=1这行数据的最新的已经提交的版本，所以最新版本是事务C的更新，所以最新版本是(1,2)，事务B更新之后，变成了（1,3），所以事务B读出来的结果是3。这就叫当前读。而事务A因为只做查询没做更新，所以按照可重复读的定义，它读的结果和它这个事务启动时的结果一致，就是1。

原理：

在InnoDB里，每个事务都有唯一的ID，叫transaction ID，是按照申请顺序唯一严格递增的。每一行数据也是有多个版本的，当事务修改数据时，把transaction ID赋值给版本，记为row trx_id。

Untitled

如图是一行数据被不同事物更新之后的不同版本，实际上v1，v2，v3不是物理存在的，当需要v2的时候会从v4开始回滚两次得到。按照可重复读的定义，对于事物启动之前所有提交的结果，事务是可见的，但是对于事物启动之后提交的结果，是不可见的。

InnoDB在事务启动的时候，为每一个事务维护了一个数组，表示事务启动瞬间所有活跃的事务。活跃指的是启动了还没提交。

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

Untitled

我们假设：

  1. 事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
  2. 事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；
  3. 三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。

这样，事务 A 的视图数组就是[99,100], 事务 B 的视图数组是[99,100,101], 事务 C 的视图数组是[99,100,101,102]。

Untitled

第一个有效更新是事务C把(1,1)改成了(1,2)，所以这行数据有一个版本是以c的transaction ID为准的102版本。

第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。

好，现在事务 A 要来读数据了，它的视图数组是[99,100]。当然了，读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。

事务B的更新，按照一致性读的原则，好像是不对的。这个是因为B去更新数据的时候，最新的版本已经是C更新的这个102版本的(1,2)了，它不能按照历史版本来更新，因为如果按照历史版本来更新的话事务C的更新就会丢失了。

所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101。

所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。

这里我们提到了一个概念，叫作当前读。其实，除了 update 语句外，select 语句如果加锁，也是当前读。所以，如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）

再往前一步，假设事务 C 不是马上提交的，而是变成了下面的事务 C’，会怎么样呢？

Untitled

事务 C’的不同是，更新后并没有马上提交，在它提交前，事务 B 的更新语句先发起了。前面说过了，虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。那么，事务 B 的更新语句会怎么处理呢？这时候，我们在上一篇文章中提到的“两阶段锁协议”就要上场了。事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。

如果隔离级别是读提交呢？

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

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

Untitled

在读提交级别下，因为C的修改是已经提交的，所以事务A读到的结果是1,2。B读到的结果很显然是自己的更新，所以是(1,3)

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

维护一张市民表，经常用身份证号来查名字，所以要给身份证号加索引。这里业务可以保证不会重复写入身份证号，所以你应该给身份证号加普通索引还是唯一索引呢？

在查询方面，唯一索引和写入索引区别不大。

对于唯一索引，因为有了唯一约束，在B+树里找到满足要求的叶子后便不再寻找。而对于普通索引，则需要再向后找，直到不满足为止，如果要找的值在数据页中，那就是一次指针挪动，可以忽略不计。当然如果正好要找的值是数据页的最后一项，就需要查下一个页，但是一个页有很多条索引数据，所以这个概率很低。

在写入方面，因为有change buffer的优化，所以会差别很大。

change buffer是做什么的呢，就是当我们要更新数据的时候，如果数据在内存中，就直接更新。如果不在内存中，则先将数据写入change buffer。这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

因为唯一索引需要保证唯一，所以不能用change buffer。

假如唯一索引现在已经有了3和5，要插入一个4。

第一种情况是，这个记录要更新的目标页在内存中。

这时，InnoDB 的处理流程如下：

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

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

重点是对于要更新的目标页不在内存中的。

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

回到我们文章开头的问题，普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

change buffer什么时候开关

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

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

  • mysql存在优化器选择错索引的情况，这个时候需要优化引导，或者加上force index来选择正确的索引。前缀索引的优点是节省空间。
  • 这里因为涉及到mysql的一些bug，所以作者没有展开讲原理。

11｜如何给字符串加索引？

  • 字符串索引是可以加前缀索引的。
  • 使用前缀索引后，可能会导致查询语句读数据的次数变多。
  • 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
  • 考虑前缀的区分度。算出前缀的区分度。
mysql> select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7,from SUser;

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

倒序存储：

第一种方式是使用倒序存储。如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：

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);

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
  • 都不支持范围查询，只能支持等值查询
  • 倒叙存储不会消耗

倒序存储和hash的异同。

它们的区别，主要体现在以下三个方面：从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

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

一个可能的原因是可能会碰到刷脏页的情况，也就是内存满了，redo log满了，这个时候需要将数据写到磁盘中，再相应用户的查询请求，所以会变得慢。这里可以通过设置一些参数来控制刷脏页的速度，以及脏页比例。

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

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。假设图 1 中 page A 已经满了，这时我要再插入一行数据，会怎样呢？

Untitled

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

重建表

试想一下，如果你现在有一个表 A，需要做空间收缩，为了把表中存在的空洞去掉，你可以怎么做呢？你可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。这里，你可以使用 alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

我给你简单描述一下引入了 Online DDL 之后，重建表的流程：建立一个临时文件，扫描表 A 主键的所有数据页；用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；用临时文件替换表 A 的数据文件。

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

count(*)对于MyISAM是计数的，但Innodb是取记录算出来的，因为它要支持事务所以它不能用变量计数。

对于计数问题：

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

对于计数问题我们可以用redis来做，但会出现逻辑上的不准确的问题。

对于这个场景，会话B插入读redis计数的时候还没有+1，但是最近100条已经有了一行新的数据。会话A的两个操作对调，则会出现redis计数确实+1了，但是记录还没有被插入。

Untitled

解决办法是用事务，并且是把计数存到单独一张表里

Untitled

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

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

但是 count() 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count() 肯定不是 null，按行累加。看到这里，你一定会说，优化器就不能自己判断一下吗，主键 id 肯定非空啊，为什么不能按照 count() 来处理，多么简单的优化啊。当然，MySQL 专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多了，而且 MySQL 已经优化过 count() 了，你直接使用这种用法就可以了。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以我建议你，尽量使用 count()。

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

追问 8：正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

回答：这个问题其实问得非常好。这里涉及到了，“redo log 里面到底是什么”的问题。实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。

在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

从这里可以看到，redo log的作用是用来防止WAL(Write-Ahead Logging)的时候把数据修改写到位于内存的数据页中后，还没落盘的时候发生了crash是用来恢复那些还没有被落盘的数据用的。

业务设计问题

问题是这样的（我文字上稍微做了点修改，方便大家理解）：

业务上有这样的需求，A、B 两个用户，如果互相关注，则成为好友。设计上是有两张表，一个是 like 表，一个是 friend 表，like 表有 user_id、liker_id 两个字段，我设置为复合唯一索引即 uk_user_id_liker_id。语句执行逻辑是这样的：

以 A 关注 B 为例：第一步，先查询对方有没有关注自己（B 有没有关注 A）select * from like where user_id = B and liker_id = A;

如果有，则成为好友insert into friend;

没有，则只是单向关注关系insert into like;

但是如果 A、B 同时关注对方，会出现不会成为好友的情况。因为上面第 1 步，双方都没关注对方。第 1 步即使使用了排他锁也不行，因为记录不存在，行锁无法生效。请问这种情况，在 MySQL 锁层面有没有办法处理？

CREATE TABLE `like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `liker_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;

CREATE TABLE `friend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_1_id` int(11) NOT NULL,
  `friend_2_id` int(11) NOT NULL,
  UNIQUE KEY `uk_friend` (`friend_1_id`,`friend_2_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

原来的逻辑：

Untitled

首先，要给“like”表增加一个字段，比如叫作 relation_ship，并设为整型，取值 1、2、3。

值是 1 的时候，表示 user_id 关注 liker_id;

值是 2 的时候，表示 liker_id 关注 user_id;

值是 3 的时候，表示互相关注。

然后，当 A 关注 B 的时候，逻辑改成如下所示的样子：

应用代码里面，比较 A 和 B 的（ID）大小，如果A < B 就执行下面的逻辑

mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;
/*代码中判断返回的 relation_ship，
  如果是1，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
  */
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;

如果 A>B，则执行下面的逻辑

mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;
/*代码中判断返回的 relation_ship，
  如果是2，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;

这两个代码的不同之处在于，relation_ship的值是1还是2，以及谁是user_id, 谁是like_id。在应用代码里保证里user_id永远小于like_id，所以两个用户相互关注的时候，在like里创建的user_id和like_id是一模一样的，都是小的在前。只是relation_ship不一样，如此一来，他们在关注的时候就会有唯一索引冲突，就会触发on duplicate

这个设计里，让“like”表里的数据保证 user_id < liker_id，这样不论是 A 关注 B，还是 B 关注 A，在操作“like”表的时候，如果反向的关系已经存在，就会出现行锁冲突。

然后，insert … on duplicate 语句，确保了在事务内部，执行了这个 SQL 语句后，就强行占住了这个行锁，之后的 select 判断 relation_ship 这个逻辑时就确保了是在行锁保护下的读操作。

操作符 “ ” 是按位或，连同最后一句 insert 语句里的 ignore，是为了保证重复调用时的幂等性。这样，即使在双方“同时”执行关注操作，最终数据库里的结果，也是 like 表里面有一条关于 A 和 B 的记录，而且 relation_ship 的值是 3， 并且 friend 表里面也有了 A 和 B 的这条记录。

16 | “order by”是怎么工作的？

全字段排序

  1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
  2. 从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序；按照排序结果取前 1000 行返回给客户端。

Untitled

rowid 排序

  1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
  2. 从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到不满足 city=’杭州’条件为止，也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据按照字段 name 进行排序；
  7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

Untitled

创建联合索引使得不用排序

所以，我们可以在这个市民表上创建一个 city 和 name 的联合索引，对应的 SQL 语句是：

alter table t add index city_user(city, name);

https://www.notion.so

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city=’杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。

  1. 从索引 (city,name) 找到第一个满足 city=’杭州’条件的主键 id；
  2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回
  3. 从索引 (city,name) 取下一个记录主键 id；
  4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

索引覆盖使得不用回表

alter table t add index city_user_age(city, name, age);

这时，对于 city 字段的值相同的行来说，还是按照 name 字段的值递增排序的，此时的查询语句也就不再需要排序了。这样整个查询语句的执行流程就变成了：

  1. 从索引 (city,name,age) 找到第一个满足 city=’杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
  2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
  3. 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

17 | 如何正确地显示随机消息？

需求是在单词表里随机取3个单词。

用order by rand()来实现的话，如果MySQL使用快排或者归并来实现，会导致没有必要的排序。但后来My SQL新增了优先队列排序算法，就是找出top K。

随机方法2

  1. 取得整个表的行数，并记为 C。
  2. 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
  3. 再用 limit Y,1 取得一行。
mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

随机方法3

  1. 得整个表的行数，记为 C；
  2. 根据相同的随机方法得到 Y1、Y2、Y3；
  3. 再执行三个 limit Y, 1 语句得到三行数据。
mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

事务遇到唯一索引冲突会马上回滚吗？

签到系统的设计

autocommit的作用？

mvcc是什么？

多版本并发控制

读提交是怎么实现的？

redo log是什么？

DDL 是什么