Mysql实战笔记（九）实践（8）

奥兰治的威廉
205 阅读12分钟

二十五、为什么临时表可以重名？

可以看到，临时表在使用上有以下几个特点：

  1. 建表语法是 create temporary table ...。
  2. 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A创建的临时表 t，对于 session B 就是不可见的。
  3. 临时表可以与普通表同名。
  4. session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
  5. show tables 命令不显示临时表。

由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。也正是由于这个特性，临时表就特别适合我们文章开头的 join 优化这种场景。 原因主要包括以下两个方面：

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

临时表的应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。 一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如。将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上。如下图所示：

在这个架构中，分区 key 的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包含 f 的等值条件，那么就要用 f 做分区键。

但是，如果查询的时候没有用到分区Key，只能到所有的分区中去查找满足条件的所有行，然后统一做 order by 的操作。
select v from ht where k >= M order by t_modified desc limit 100;
这种情况下，有两种比较常用的思路。

  1. 在 proxy 层的进程代码中实现排序。 这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过，这个方案的缺点也比较明显：

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

  2. 把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。 在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；在各个分库上执行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;把分库执行的结果插入到 temp_ht 表中；执行select v from temp_ht order by t_modified desc limit 100;

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

为什么临时表可以重名？

create temporary table temp_t(id int primary key)engine=innodb;
在执行这个语句的时候，MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。

这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程id} 序列号” 。

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

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

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

临时表和主备复制

临时表也会写 binlog。

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

  1. session A 的临时表 t1，在备库的 table_def_key 就是：库名 +t1+“M 的serverid”+“session A 的 thread_id”;
  2. session B 的临时表 t1，在备库的 table_def_key 就是 ：库名 +t1+“M 的serverid”+“session B 的 thread_id”。

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

二十六、什么时候会使用内部临时表？

sort buffer、内存临时表和 join buffer。在排序的时候用到了 sort buffer，在使用 join 语句的时候用到了 join buffer。

union 执行流程

(select 1000 as f) union (select id from t1 order by id desc limit 2);
这条语句用到了 union，它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。

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

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

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

group by 执行流程

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

在 Extra 字段里面，我们可以看到三个信息： Using index，表示这个语句使用了覆盖索引，选择了索引 a，不需要回表； Using temporary，表示使用了临时表； Using filesort，表示需要排序。

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

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

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

group by 优化方法 -- 索引

可以看到，不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个 group by 语句执行起来就会很慢。

group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。如果扫描过程中可以保证出现的数据是有序的，就可以起到优化的效果。

在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引。
alter table t1 add column z int generated always as(id % 100), add index(z);

从 Extra 字段可以看到，这个语句的执行不再需要临时表，也不需要排序了。

group by 优化方法 -- 直接排序

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

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. 排序完成后，就得到了一个有序数组。

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

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

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

二十七、InnoDB和Memory引擎

二十八、自增主键为什么不是连续的？

由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。

自增值保存在哪儿？

在空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据，再执行 show create table 命令，就可以看到如下图所示的结果：

可以看到，表定义里面出现了一个 AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2。其实，这个输出结果容易引起这样的误解：自增值是保存在表结构定义里的。实际上，表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值。

不同的引擎对于自增值的保存策略不同。

  1. MyISAM 引擎的自增值保存在数据文件中。

  2. InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL 重启前的值”，具体情况是：
    在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

    举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。 也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redolog 恢复重启之前的值。

自增值修改机制

在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

  1. 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的AUTO_INCREMENT 值填到自增字段；
  2. 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是 X，当前的自增值是 Y。

  1. 如果 X<Y，那么这个表的自增值不变；
  2. 如果 X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法是：从 auto_increment_offset 开始，以auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。

备注：在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突。

自增值不连续的原因

唯一键冲突是导致自增主键 id 不连续的第一种原因。
假设，表 t 里面已经有了 (1,1,1) 这条记录，这时我再执行一条插入数据命令：
insert into t values(null, 1, 1);
这个语句的执行流程就是：

  1. 执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);
  2. InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；
  3. 将传入的行的值改成 (2,1,1);
  4. 将表的自增值改成 3；
  5. 继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。

可以看到，这个表的自增值改成 3，是在真正执行插入数据的操作之前。 这个语句真正执行的时候，因为碰到唯一键 c 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。 所以，在这之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是说，出现了自增主键不连续的情况。

第二种原因，事务回滚。

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
// 插入的行是 (3,2,2)

自增锁的优化

在生产上，尤其是有 insert ... select 这种批量插入数据的场景时，从并发插入数据性能的角度考虑，我建议你这样设置：innodb_autoinc_lock_mode=2 ，并且binlog_format=row。这样做，既能提升并发性，又不会出现数据一致性问题。

avatar
文章
阅读
粉丝