Mysql45讲-实践（三）

April 26, 2022 27144

如何判断一个数据库是不是出问题了？

select 1 判断

select 1 成功返回，只能说明这个库的进程还在，并不能说明主库没问题。

1
2
3
4
5
6
7
8
9
set global innodb_thread_concurrency=3;

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t values(1,1)

查询 blocked

设置 innodb_thread_concurrency 参数的目的是，控制 InnoDB 的并发线程上限。

select 1 是能执行成功的，但是查询表 t 的语句会被堵住。

如果用 select 1 来检测实例是否正常的话，是检测不出问题的。

通常情况下，建议把 innodb_thread_concurrency 设置为 64~128 之间的值。

并发连接和并发查询，并不是同一个概念：

在 show processlist 的结果里，看到的几千个连接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询。

并发连接数达到几千个影响并不大，就是多占一些内存而已。我们应该关注的是并发查询，因为并发查询太高才是 CPU 杀手。

在线程进入锁等待以后，并发线程的计数会减一

在这个例子中，同时在执行的语句超过了设置的 innodb_thread_concurrency 的值，这时候系统其实已经不行了，但是通过 select 1 来检测系统，会认为系统还是正常的。

因此，我们使用 select 1 的判断逻辑要修改一下。

查表判断

为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况，我们需要找一个访问 InnoDB 的场景。一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行：

1
mysql> select * from mysql.health_check;

使用这个方法，我们可以检测出由于并发线程过多导致的数据库不可用的情况。

问题：更新事务要写 binlog，而一旦 binlog 所在磁盘的空间占用率达到 100%，那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是，系统这时候还是可以正常读数据的。

因此，我们还是把这条监控语句再改进一下。把查询语句改成更新语句。

更新判断

既然要更新，就要放个有意义的字段，常见做法是放一个 timestamp 字段，用来表示最后一次执行检测的时间。这条更新语句类似于：

1
mysql> update mysql.health_check set t_modified=now();

节点可用性的检测都应该包含主库和备库。如果用更新来检测主库的话，那么备库也要进行更新检测。

但，备库的检测也是要写 binlog 的。由于我们一般会把数据库 A 和 B 的主备关系设计为双 M 结构，所以在备库 B 上执行的检测命令，也要发回给主库 A。

但是，如果主库 A 和备库 B 都用相同的更新命令，就可能出现行冲突，也就是可能会导致主备同步停止。所以，现在看来 mysql.health_check 这个表就不能只有一行数据了。

为了让主备之间的更新不产生冲突，我们可以在 mysql.health_check 表上存入多行数据，并用 A、B 的 server_id 做主键。

1
2
3
4
5
6
7
8
mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

由于 MySQL 规定了主库和备库的 server_id 必须不同（否则创建主备关系的时候就会报错），这样就可以保证主、备库各自的检测命令不会发生冲突。

问题：更新判断是一个相对比较常用的方案了，不过依然存在一些“判定慢”的问题。

涉及到的是服务器 IO 资源分配的问题

首先，所有的检测逻辑都需要一个超时时间 N。执行一条 update 语句，超过 N 秒后还不返回，就认为系统不可用。

IO 利用率 100% 表示系统的 IO 是在工作的，每个请求都有机会获得 IO 资源，执行自己的任务。而我们的检测使用的 update 命令，需要的资源很少，所以可能在拿到 IO 资源的时候就可以提交成功，并且在超时时间 N 秒未到达之前就返回给了检测系统。

检测系统一看，update 命令没有超时，于是就得到了“系统正常”的结论。

也就是说，这时候在业务系统上正常的 SQL 语句已经执行得很慢了，但是 DBA 上去一看，HA 系统还在正常工作，并且认为主库现在处于可用状态。

根本原因是我们上面说的所有方法，都是基于外部检测的。外部检测天然有一个问题，就是随机性。

内部统计

在 MySQL 内部发现数据库问题的方法

MySQL 5.6 版本以后提供的 performance_schema 库，就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。

event_name='wait/io/file/innodb/innodb_log_file’

performance_schema.file_summary_by_event_name 的一行

图中这一行表示统计的是 redo log 的写入时间，第一列 EVENT_NAME 表示统计的类型。

接下来的三组数据，显示的是 redo log 操作的时间统计。

第一组五列，是所有 IO 类型的统计。其中，COUNT_STAR 是所有 IO 的总次数，接下来四列是具体的统计项， 单位是皮秒；前缀 SUM、MIN、AVG、MAX，顾名思义指的就是总和、最小值、平均值和最大值。

第二组六列，是读操作的统计。最后一列 SUM_NUMBER_OF_BYTES_READ 统计的是，总共从 redo log 里读了多少个字节。

第三组六列，统计的是写操作。

最后的第四组数据，是对其他类型数据的统计。在 redo log 里，你可以认为它们就是对 fsync 的统计。

在 performance_schema 库的 file_summary_by_event_name 表里，binlog 对应的是 event_name = "wait/io/file/sql/binlog"这一行。各个字段的统计逻辑，与 redo log 的各个字段完全相同。

每一次操作数据库，performance_schema 都需要额外地统计这些信息，所以我们打开这个统计功能是有性能损耗。如果打开所有的 performance_schema 项，性能大概会下降 10% 左右。

建议只打开自己需要的项进行统计

把这个信息用在实例状态诊断上：可以通过 MAX_TIMER 的值来判断数据库是否出问题了

误删数据后除了跑路，还能怎么办？

对 MySQL 相关的误删数据，做下分类：

  1. 使用 delete 语句误删数据行；
  2. 使用 drop table 或者 truncate table 语句误删数据表；
  3. 使用 drop database 语句误删数据库；
  4. 使用 rm 命令误删整个 MySQL 实例。

误删行

如果是使用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据恢复回来。

Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。

需要确保 binlog_format=row 和 binlog_row_image=FULL

具体恢复数据时，对单个事务做如下处理：

  1. 对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可；
  2. 同理，对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；
  3. 而如果是 Update_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行

不建议你直接在主库上执行这些操作:恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。

更重要是要做到事前预防:

  1. 把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。

    如果你确定这个删除操作没问题的话，可以在 delete 语句中加上 where 条件，比如 where id>=0。

    但是，delete 全表是很慢的，需要生成回滚日志、写 redo、写 binlog。所以，从性能角度考虑，你应该优先考虑使用 truncate table 或者 drop table 命令。

    使用 delete 命令删除的数据，你还可以用 Flashback 来恢复。而使用 truncate /drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了

    因为，即使我们配置了 binlog_format=row，执行这三个命令时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。

  2. 代码上线前，必须经过 SQL 审计。

误删库 / 表

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。

假如有人中午 12 点误删了一个库，恢复数据的流程如下：

  1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
  2. 用备份恢复出一个临时库；
  3. 从日志备份里面，取出凌晨 0 点之后的日志；
  4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

数据恢复流程 -mysqlbinlog 方法

  1. 为了加速数据恢复，如果这个临时库上有多个数据库，你可以在使用 mysqlbinlog 命令时，加上一个–database 参数，用来指定误删表所在的库。这样，就避免了在恢复数据时还要应用其他库日志的情况。
  2. 在应用日志的时候，需要跳过 12 点误操作的那个语句的 binlog：
    1. 如果原实例没有使用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候，先用–stop-position 参数执行到误操作之前的日志，然后再用–start-position 从误操作之后的日志继续执行；
    2. 如果实例使用了 GTID 模式，就方便多了。假设误操作命令的 GTID 是 gtid1，那么只需要执行 set gtid_next=gtid1;begin;commit; 先把这个 GTID 加到临时实例的 GTID 集合，之后按顺序执行 binlog 的时候，就会自动跳过误操作的语句。

使用 mysqlbinlog 方法恢复数据还是不够快，主要原因有两个：

  1. 如果是误删表，最好就是只恢复出这张表，也就是只重放这张表的操作，但是 mysqlbinlog 工具并不能指定只解析一个表的日志；
  2. 用 mysqlbinlog 解析出日志应用，应用日志的过程就只能是单线程。

一种加速的方法是，在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库，这样：

  1. 在 start slave 之前，先通过执行change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；
  2. 这样做也可以用上并行复制技术，来加速整个数据恢复过程。

不论是把 mysqlbinlog 工具解析出的 binlog 文件应用到临时库，还是把临时库接到备库上，这两个方案的共同点是：误删库或者表后，恢复数据的思路主要就是通过备份，再加上应用 binlog 的方式。

这两个方案都要求备份系统定期备份全量日志，而且需要确保 binlog 在被从本地删除之前已经做了备份。

延迟复制备库

虽然可以通过利用并行复制来加速恢复数据的过程，但是这个方案仍然存在“恢复时间不可控”的问题。

如果一个库的备份特别大，或者误操作的时间距离上一个全量备份的时间较长，比如一周一备的实例，在备份之后的第 6 天发生误操作，那就需要恢复 6 天的日志，这个恢复时间可能是要按天来计算的。

缩短恢复数据需要的时间:

如果有非常核心的业务，不允许太长的恢复时间，我们可以考虑搭建延迟复制的备库。这个功能是 MySQL 5.6 版本引入的。

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟。

预防误删库 / 表的方法

  1. 账号分离，避免写错命令
    1. 只给业务开发 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。
    2. 即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
  2. 制定操作规范，是避免写错要删除的表名
    1. 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
    2. 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。

rm 删除数据

对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。

为什么还有kill不掉的语句？

在 MySQL 中有两个 kill 命令:

  1. kill query + 线程 id，表示终止这个线程中正在执行的语句；
  2. kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

问题：使用了 kill 命令，却没能断开这个连接。再执行 show processlist 命令，看到这条语句的 Command 列显示的是 Killed

收到 kill 以后，线程做什么？

kill 并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了，可以开始“执行停止的逻辑了”。

当用户执行 kill 时，MySQL 里处理 kill 命令的线程做了两件事：

  1. 把运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；
  2. 给执行线程发一个信号。

所以：

  1. 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；
  2. 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；
  3. 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

在执行 show processlist 的时候，有一个特别的逻辑：如果一个线程的状态是KILL_CONNECTION，就把Command列显示成Killed。即使是客户端退出了，这个线程的状态仍然是在等待中。只有等到满足进入 InnoDB 的条件后，才有可能判断到线程状态已经变成了 KILL_QUERY 或者 KILL_CONNECTION，再进入终止逻辑阶段。

kill 无效：

  1. 线程没有执行到判断线程状态的逻辑
  2. 由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态
  3. 终止逻辑耗时较长
    1. 超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
    2. 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
    3. DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。

两个关于客户端的误解

  1. 如果库里面的表特别多，连接就会很慢

    当使用默认参数连接的时候，MySQL 客户端会提供一个本地库名和表名补全的功能。为了实现这个功能，客户端在连接成功后，需要多做一些操作：

    1. 执行 show databases；
    2. 切到库，执行 show tables；
    3. 把这两个命令的结果用于构建一个本地的哈希表。

    在这些操作中，最花时间的就是第三步在本地构建哈希表的操作。所以，当一个库中的表个数非常多的时候，这一步就会花比较长的时间。

    我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢。

    如果在连接命令中加上 -A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了。

  2. –quick 引起误会的参数

    加–quick(或者简写为 -q) 参数，也可以关掉这个自动补全的功能

    设置了这个参数可能会降低服务端的性能

    MySQL 客户端发送请求后，接收服务端返回结果的方式有两种：

    1. 一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。
    2. 另一种是不缓存，读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。

    MySQL 客户端默认采用第一种方式，而如果加上–quick 参数，就会使用第二种不缓存的方式。

    采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。

    使用–quick参数可以达到以下三点效果：

    1. 第一点，就是前面提到的，跳过表名自动补全功能。
    2. 第二点，mysql_store_result 需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能；
    3. 第三点，是不会把执行命令记录到本地的命令历史文件。

    –quick参数的意思，是让客户端变得更快

我查这么多数据，会不会把数据库内存打爆？

全表扫描对 server 层的影响

假设，我们现在要对一个 200G 的 InnoDB 表 db1.t，执行一个全表扫描。当然，你要把扫描结果保存在客户端，会使用类似这样的命令：

1
mysql -h
host -P


port -u
user -p


pwd -e "select * from db1.t" > $target_file

InnoDB 的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表 t 的主键索引。这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

  1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
  2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
  3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
  4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
  1. 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；
  2. socket send buffer 也不可能达到 200G（默认定义 /proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。

MySQL 是“边读边发的”。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，建议使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存。

全表扫描对 InnoDB 的影响

内存的数据页是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上，Buffer Pool 还有一个更重要的作用，就是加速查询。

Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即：内存命中率。

可以在 show engine innodb status 结果中，查看一个系统当前的 BP 命中率。一般情况下，一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率要在 99% 以上。

InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。

InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。

基础的未改进的LRU算法

基本 LRU 算法

  1. 在状态 1 里，链表头部是 P1，表示 P1 是最近刚刚被访问过的数据页；假设内存里只能放下这么多数据页；
  2. 这时候有一个读请求访问 P3，因此变成状态 2，P3 被移到最前面；
  3. 状态 3 表示，这次访问的数据页是不存在于链表中的，所以需要在 Buffer Pool 中新申请一个数据页 Px，加到链表头部。但是由于内存已经满了，不能申请新的内存。于是，会清空链表末尾 Pm 这个数据页的内存，存入 Px 的内容，然后放到链表头部。
  4. 从效果上看，就是最久没有被访问的数据页 Pm，被淘汰了。

问题：假设按照这个算法，我们要扫描一个 200G 的表，而这个表是一个历史数据表，平时没有业务访问它。那么，按照这个算法扫描的话，就会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。对于一个正在做业务服务的库，这可不妙。你会看到，Buffer Pool 的内存命中率急剧下降，磁盘压力增加，SQL 语句响应变慢。

InnoDB 对 LRU 算法做了改进

改进的 LRU 算法

改进的 LRU 算法

在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。

  1. 图中状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
  2. 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
  3. 处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
    1. 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
    2. 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

到底可不可以使用join？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

这两个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储过程 idata() 往表 t2 里插入了 1000 行数据，在表 t1 里插入的是 100 行数据。

Index Nested-Loop Join

1
select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。

所以，为了便于分析执行过程中的性能问题，改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。

执行流程：

  1. 从表 t1 中读入一行数据 R；
  2. 从数据行 R 中，取出 a 字段到表 t2 里去查找；
  3. 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
  4. 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

这个过程是先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ。

Index Nested-Loop Join 算法的执行流程

在这个流程里：

  1. 对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行；
  2. 而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描 100 行；
  3. 所以，整个执行流程，总扫描行数是 200。

使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

如果使用 join 语句的话，需要让小表做驱动表（前提是“可以使用被驱动表的索引”）。

Simple Nested-Loop Join

1
select * from t1 straight_join t2 on (t1.a=t2.b);

由于表 t2 的字段 b 上没有索引，每次到 t2 去匹配的时候，就要做一次全表扫描。

由于表 t2 的字段 b 上没有索引，因此再用图 2 的执行流程时，每次到 t2 去匹配的时候，就要做一次全表扫描。

这样算来，这个 SQL 请求就要扫描表 t2 多达 100 次，总共扫描 100*1000=10 万行。

Block Nested-Loop Join

1
select * from t1 straight_join t2 on (t1.a=t2.b);

被驱动表上没有可用的索引，算法的流程是这样的：

  1. 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
  2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

Block Nested-Loop Join 算法的执行流程

在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做 100 次判断，总共需要在内存中做的判断次数是：100*1000=10 万次

如果使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是 10 万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。

驱动表选择：

假设小表的行数是 N，大表的行数是 M，那么在这个算法里：

  1. 两个表都做一次全表扫描，所以总的扫描行数是 M+N；
  2. 内存中的判断次数是 M*N。

可以看到，调换这两个算式中的 M 和 N 没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。

问题：要是表 t1 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。

执行过程就变成了：

  1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，中途join_buffer 满了，继续第 2 步；
  2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
  3. 清空 join_buffer；
  4. 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

Block Nested-Loop Join -- 两段

驱动表选择：

假设，驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M。

注意，这里的 K 不是常数，N 越大 K 就会越大，因此把 K 表示为λ*N，显然λ的取值范围是 (0,1)。

所以，在这个算法的执行过程中：

  1. 扫描行数是 N+λNM；
  2. 内存判断 N*M 次。

应该让小表当驱动表。

Simple Nested Loop Join 的性能问题

虽然 BNL 算法和 Simple Nested Loop Join 算法都是要判断 M*N 次（M 和 N 分别是 join 的两个表的行数），但是 Simple Nested Loop Join 算法的每轮判断都要走全表扫描，因此性能上 BNL 算法执行起来会快很多。

BNL 算法的执行逻辑是：

  1. 首先，将驱动表的数据全部读入内存 join_buffer 中，这里 join_buffer 是无序数组；
  2. 然后，顺序遍历被驱动表的所有行，每一行数据都跟 join_buffer 中的数据进行匹配，匹配成功则作为结果集的一部分返回。

Simple Nested Loop Join 算法的执行逻辑是：

顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。

MySQL 中索引结构和 Buffer Pool 的相关知识点：

  1. 在对被驱动表做全表扫描的时候，如果数据没有在 Buffer Pool 中，就需要等待这部分数据从磁盘读入；

    从磁盘读入数据到内存中，会影响正常业务的 Buffer Pool 命中率，而且这个算法天然会对被驱动表的数据做多次访问，更容易将这些数据页放到 Buffer Pool 的头部；

  2. 即使被驱动表数据都在内存中，每次查找“下一个记录的操作”，都是类似指针操作。而 join_buffer 中是数组，遍历的成本更低。

所以说，BNL 算法的性能会更好

能不能使用 join 语句？

  1. 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
  2. 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

  1. 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
  2. 如果是 Block Nested-Loop Join 算法：
    1. 在 join_buffer_size 足够大的时候，是一样的；
    2. 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

join语句怎么优化？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

在表 t1 里，插入了 1000 行数据，每一行的 a=1001-id 的值。也就是说，表 t1 中字段 a 是逆序的。同时，在表 t2 中插入了 100 万行数据。

Multi-Range Read 优化

Multi-Range Read 优化 (MRR)：这个优化的主要目的是尽量使用顺序读盘。

1
select * from t1 where a>=1 and a<=100;

主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。因此，回表肯定是一行行搜索主键索引的，基本流程如图所示

如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。(a虽然是倒叙存放在表中，但a上有B+tree索引，是顺序存储的，但是a索引的叶子节点存放的id的值是倒叙的，a索引的范围查询通过id回表查询，b+tree索引都是顺序存储的，那么通过a索引获取到的ID是倒序的，不是按照索引顺序查询就变成了随机查询。)

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

使用 MRR 优化，语句的执行流程变成了这样：

  1. 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
  2. 将 read_rnd_buffer 中的 id 进行递增排序；
  3. 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access

MySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法。是对 NLJ 算法的优化。

NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。

把表 t1 的数据取出来一部分，先放到一个临时内存(join_buffer)，复用 join_buffer 到 BKA 算法中

Batched Key Access 流程

如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置

1
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

前两个参数的作用是要启用 MRR

BNL 算法的性能问题

大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响，你可以考虑增大 join_buffer_size 的值，减少对被驱动表的扫描次数

BNL 算法对系统的影响主要包括三个方面：

  1. 可能会多次扫描被驱动表，占用磁盘 IO 资源；
  2. 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
  3. 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

优化的常见做法是，给被驱动表的 join 字段加上索引，把 BNL 算法转成 BKA 算法。

让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能

总结

  1. BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
  2. BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
  3. 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
  4. MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

问题：如果用 left join 的话，左边的表一定是驱动表吗？

问题：如果两个表的 join 包含多个条件的等值匹配，是都要写到 on 里面呢，还是只把一个条件写到 on 里面，其他条件写到 where 部分？

1
2
3
4
create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

表 a 和 b 都有两个字段 f1 和 f2，不同的是表 a 的字段 f1 上有索引。然后，往两个表中都插入了 6 条记录，其中在表 a 和 b 中同时存在的数据有 4 行。

1
2
select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

第二个问题的两种写法

两个 join 的查询结果

  1. 语句 Q1 返回的数据集是 6 行，表 a 中即使没有满足匹配条件的记录，查询结果中也会返回一行，并将表 b 的各个字段值填成 NULL。

    Q1 的 explain 结果

    1. 驱动表是表 a，被驱动表是表 b；
    2. 由于表 b 的 f1 字段上没有索引，所以使用的是 Block Nested Loop Join（简称 BNL） 算法。

    这条语句的执行流程:

    1. 把表 a 的内容读入 join_buffer 中。因为是 select * ，所以字段 f1 和 f2 都被放入 join_buffer 了。
    2. 顺序扫描表 b，对于每一行数据，判断 join 条件（也就是 (a.f1=b.f1) and (a.f1=1)）是否满足，满足条件的记录, 作为结果集的一行返回。如果语句中有 where 子句，需要先判断 where 部分满足条件后，再返回。
    3. 表 b 扫描完成后，对于没有被匹配的表 a 的行（在这个例子中就是 (1,1)、(2,2) 这两行），把剩余字段补上 NULL，再放入结果集中。

    left join -BNL 算法

  2. 语句 Q2 返回的是 4 行。从逻辑上可以这么理解，最后的两行，由于表 b 中没有匹配的字段，结果集里面 b.f2 的值是空，不满足 where 部分的条件判断，因此不能作为结果集的一部分。

    Q2 的 explain 结果

    1. 这条语句是以表 b 为驱动表的
    2. 如果一条 join 语句的 Extra 字段什么都没写的话，就表示使用的是 Index Nested-Loop Join（简称 NLJ）算法。

    语句 Q2 的执行流程:

    1. 顺序扫描表 b，每一行用 b.f1 到表 a 中去查
    2. 匹配到记录后判断 a.f2=b.f2 是否满足，满足条件的话就作为结果集的一部分返回。

    语句 Q2 里面 where a.f2=b.f2 表示，查询结果里面不会包含 b.f2 是 NULL 的行，这样这个 left join 的语义就是“找到这两个表里面，f1、f2 对应相同的行。对于表 a 中存在，而表 b 中匹配不到的行，就放弃”。

    这条语句虽然用的是 left join，但是语义跟 join 是一致的

    因此，优化器就把这条语句的 left join 改写成了 join，然后因为表 a 的 f1 上有索引，就把表 b 作为驱动表，这样就可以用上 NLJ 算法。在执行 explain 之后，再执行 show warnings，就能看到这个改写的结果:

    Q2 的改写结果

    这个例子说明，即使我们在 SQL 语句中写成 left join，执行过程还是有可能不是从左到右连接的。也就是说，使用 left join 时，左边的表不一定是驱动表。

如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面。

join 将判断条件是否全部放在 on 部分没有区别

为什么临时表可以重名？

1
2
3
4
create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);
  1. 内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
  2. 临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。

临时表的特性

临时表在使用上有以下几个特点：

  1. 建表语法是 create temporary table …。
  2. 一个临时表只能被创建它的 session 访问，对其他线程不可见。由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。
  3. 临时表可以与普通表同名。
  4. 有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
  5. show tables 命令不显示临时表。

临时表就特别适合我们文章开头的 join 优化这种场景:

  1. 不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。
  2. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

临时表的应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。

一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如。将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上。如下图所示：

一般情况下，这种分库分表系统都有一个中间层 proxy。不过，也有一些方案会让客户端直接连接数据库，也就是没有 proxy 这一层。

在这个架构中，分区 key 的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包含 f 的等值条件，那么就要用 f 做分区键。这样，在 proxy 这一层解析完 SQL 语句以后，就能确定将这条语句路由到哪个分表做查询。

比如下面这条语句：

1
select v from ht where f=N;

这时，我们就可以通过分表规则（比如，N%1024) 来确认需要的数据被放在了哪个分表上。这种语句只需要访问一个分表，是分库分表方案最欢迎的语句形式了。

但是，如果这个表上还有另外一个索引 k，并且查询语句是这样的：

1
select v from ht where k >= M order by t_modified desc limit 100;

这时候，由于查询条件里面没有用到分区字段 f，只能到所有的分区中去查找满足条件的所有行，然后统一做 order by 的操作。这种情况下，有两种比较常用的思路。

第一种思路是，在 proxy 层的进程代码中实现排序。

这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过，这个方案的缺点也比较明显：

  1. 需要的开发工作量比较大。我们举例的这条语句还算是比较简单的，如果涉及到复杂的操作，比如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高；
  2. 对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。

另一种思路就是，把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。

比如上面这条语句，执行流程可以类似这样：

  1. 在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；

  2. 在各个分库上执行

    1
    select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

  3. 把分库执行的结果插入到 temp_ht 表中；

  4. 执行

    1
    select v from temp_ht order by t_modified desc limit 100;

  5. 得到结果。

在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表 temp_ht 放到 32 个分库中的某一个上。

为什么临时表可以重名？

1
create temporary table temp_t(id int primary key)engine=innodb;

这个语句的时候，MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。

这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id} 序列号”。你可以使用 select @@tmpdir 命令，来显示实例的临时文件目录

而关于表中数据的存放方式，在不同的 MySQL 版本中有着不同的处理方式：

  1. 在 5.6 以及之前的版本里，MySQL 会在临时文件目录下创建一个相同前缀、以.ibd 为后缀的文件，用来存放数据文件；
  2. 而从 5.7 版本开始，MySQL 引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，我们就不需要再创建 ibd 文件了。

从文件名的前缀规则，我们可以看到，其实创建一个叫作 t1 的 InnoDB 临时表，MySQL 在存储上认为我们创建的表名跟普通表 t1 是不同的，因此同一个库下面已经有普通表 t1 的情况下，还是可以再创建一个临时表 t1 的。

MySQL 维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个 table_def_key。

  1. 一个普通表的 table_def_key 的值是由“库名 + 表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现 table_def_key 已经存在了。
  2. 而对于临时表，table_def_key 在“库名 + 表名”基础上，又加入了“server_id+thread_id”。

在实现上，每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session 结束的时候，对链表里的每个临时表，执行 “DROP TEMPORARY TABLE + 表名”操作。

临时表和主备复制

binlog 中也记录了 DROP TEMPORARY TABLE 这条命令

既然写 binlog，就意味着备库需要。

如果当前的 binlog_format=row，那么跟临时表有关的语句，就不会记录到 binlog 里。也就是说，只在 binlog_format=statment/mixed 的时候，binlog 中才会记录临时表的操作。

这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。

问题：主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的呢？

MySQL 在记录 binlog 的时候，会把主库执行这个语句的线程 id 写到 binlog 中。这样，在备库的应用线程就能够知道执行每个语句的主库线程 id，并利用这个线程 id 来构造临时表的 table_def_key。

由于 table_def_key 不同，所以这两个表在备库的应用线程里面是不会冲突的。

问题：为什么不能用 rename 修改临时表的改名。

在实现上，执行 rename table 语句的时候，要求按照“库名 / 表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的，并且文件名的规则是“#sql{进程 id}{线程 id} 序列号.frm”，因此会报“找不到文件名”的错误。

什么时候会使用内部临时表？

union 执行流程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;

  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();
1
(select 1000 as f) union (select id from t1 order by id desc limit 2);

这条语句用到了 union，它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。

下图是这个语句的 explain 结果:

union 语句 explain 结果

  1. 第二行的 key=PRIMARY，说明第二个子句用到了索引 id。
  2. 第三行的 Extra 字段，表示在对子查询的结果集做 union 的时候，使用了临时表 (Using temporary)。

这个语句的执行流程是这样的：

  1. 创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。
  2. 执行第一个子查询，得到 1000 这个值，并存入临时表中。
  3. 执行第二个子查询：
    1. 拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；
    2. 取到第二行 id=999，插入临时表成功。
  4. 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。

union 执行流程

这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键 id 的唯一性约束，实现了 union 的语义。

如果把上面这个语句中的 union 改成 union all 的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

group by 执行流程

1
select id%10 as m, count(*) as c from t1 group by m;

这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序后输出。它的 explain 结果如下：

group by 的 explain 结果

  1. Using index，表示这个语句使用了覆盖索引，选择了索引 a，不需要回表；
  2. Using temporary，表示使用了临时表；
  3. Using filesort，表示需要排序。

这个语句的执行流程是这样的：

  1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m；
  2. 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；
    1. 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);
    2. 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；
  3. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

group by 执行流程

图中最后一步，对内存临时表的排序

group by 优化方法 – 索引

group by 逻辑需要构造一个带唯一索引的表，执行代价是比较高的

执行 group by 语句为什么需要临时表？

group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

group by 优化方法 : 扫描过程中可以保证出现的数据是有序的

InnoDB 的索引，就可以满足这个输入有序的条件

在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引（如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引，来解决这个问题）。

1
alter table t1 add column z int generated always as(id % 100), add index(z);

上面的 group by 语句就可以改成：

1
select z, count(*) as c from t1 group by z;

优化后的 group by 语句的 explain 结果，如下图所示：

group by 优化的 explain 结果

group by 优化方法 – 直接排序

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

1
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

执行流程就是这样的：

  1. 初始化 sort_buffer，确定放入一个整型字段，记为 m；
  2. 扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；
  3. 扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；
  4. 排序完成后，就得到了一个有序数组。

使用 SQL_BIG_RESULT 的执行流程图

使用 SQL_BIG_RESULT 的 explain 结果

从 Extra 字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法。

group by 使用的指导原则

  1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
  2. 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
  3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
  4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

MySQL 什么时候会使用内部临时表？

  1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
  2. join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；
  3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

问题：如果只需要去重，不需要执行聚合函数，distinct 和 group by 哪种效率高一些呢？

1
2
select a from t group by a order by null;
select distinct a from t;

表 t 的字段 a 上没有索引

不需要执行聚合函数时，distinct 和 group by 这两条语句的语义和执行流程是相同的，因此执行性能也相同。

这两条语句的执行流程是下面这样的:

  1. 创建一个临时表，临时表有一个字段 a，并且在这个字段 a 上创建一个唯一索引；
  2. 遍历表 t，依次取数据插入临时表中：
    1. 如果发现唯一键冲突，就跳过；
    2. 否则插入成功；
  3. 遍历完成后，将临时表作为结果集返回给客户端。

都说InnoDB好，那还要不要使用Memory引擎？

内存表的数据组织结构

1
2
3
4
create table t1(id int primary key, c int) engine=Memory;
create table t2(id int primary key, c int) engine=innodb;
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

表 t1 使用 Memory 引擎， 表 t2 使用 InnoDB 引擎。

分别执行 select from t1 和 select from t2

两个查询结果 -0 的位置

内存表 t1 的返回结果里面 0 在最后一行，而 InnoDB 表 t2 的返回结果里 0 在第一行。

InnoDB 引擎，它的主键索引 id 的组织方式：InnoDB 表的数据就放在主键索引树上，主键索引是 B+ 树。所以表 t2 的数据组织方式如下图所示：

表 t2 的数据组织

主键索引上的值是有序存储的

在执行 select * 的时候，就会按照叶子节点从左到右扫描，所以得到的结果里，0 就出现在第一行。

Memory 引擎的数据和索引是分开的

表 t1 的数据组织

内存表的数据部分以数组的方式单独存放，而主键 id 索引里，存的是每个数据的位置。主键 id 是 hash 索引，可以看到索引上的 key 并不是有序的。

在内存表 t1 中，当执行 select * 的时候，走的是全表扫描，也就是顺序扫描这个数组。因此，0 就是最后一个被读到，并放入结果集的数据。

InnoDB 和 Memory 引擎的数据组织方式是不同的：

  1. InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
  2. Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

两个引擎的一些典型不同：

  1. InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
  2. 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
  3. 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
  4. InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
  5. InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

hash 索引和 B-Tree 索引

内存表也是支持 B-Tree 索引的。

在 id 列上创建一个 B-Tree 索引，SQL 语句可以这么写：

1
alter table t1 add index a_btree_index using btree (id);

表 t1 的数据组织形式就变成了这样：

表 t1 的数据组织 -- 增加 B-Tree 索引

使用 B-Tree 和 hash 索引查询返回结果对比

为什么不建议生产环境上使用内存表?

  1. 锁粒度问题；

    内存表不支持行锁，只支持表锁。因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作。

  2. 数据持久化问题。

    数据库重启的时候，所有的内存表都会被清空。

    由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。

内存临时表刚好可以无视内存表的两个不足，主要是下面的三个原因：

  1. 临时表不会被其他线程访问，没有并发性的问题；
  2. 临时表重启后也是需要删除的，清空数据这个问题不存在；
  3. 备库的临时表也不会影响主库的用户线程。
查看评论