临时表与内存表

临时表：分两种，一种是用户自己创建的用户临时表。另一种是前面讲到的排序时用到的内部临时表。注意临时表可以使用各种引擎，包括InnoDB，MyISAM

内存表：指使用Memory引擎的表，表数据结构都保存在内存中，一旦重启就会被清空。

1.用户临时表

用户临时表一般指的是用户手动创建的临时表。建表语句create temporary table ...

1.1用户临时表特点

用户临时表具有以下特性：

• 临时表写操作会写在磁盘上

• 一个临时表只能被创建它的session访问，对其它线程不可见。

• 临时表可以与普通表同名，在这种情况下，执行show create和增删改查操作会优先选择临时表

• 线程会话结束后，会自动删除临时表。执行drop temporary table自动回收。

1.2用户临时表重名与可见性

• 物理磁盘上区分

创建临时表时，MySQL会创建一个frm文件保存表结构定义(放在临时文件夹目录下，而不是当前数据库目录下)，而文件名的前缀为”#sql进程id_线程id_序列号“。

因此不同创建的临时表名在磁盘上并不会重名。

• 内存上区分

在内存中每个表都对应一个table_def_key。普通表的table_def_key是通过”库名+表名“得到；而临时表的名称会在普通表的基础上添加”server_id+thread_id“，有了线程id作为区分后，不同线程创建的同名临时表也会被区分。

1.3用户临时表的主备复制

binlog在row格式下，不会记录所有在临时表上的操作日志。

💣问题一：关于临时表的所有操作记录为什么都需要记录到binlog中？

insert into t_normal select * from temp_t;

上面这条SQL语句中，因为是普通表的更新操作，因此会被记录到binlog中。当它需要在备库中应用binlog恢复时分几种情况：

• 如果当前binlog设置为row格式，该条日志会记录下所有插入的行数据，因此可以直接应用日志进行更新。
• 如果当前binlog设置为statment/mixed时，binlog就需要同时记录下所有临时表的所有操作。

反过来说对于删除操作也是如此，对于每个drop table操作(后面可以接多个表)，如果删除的表存在临时表，并且binlog格式为row，那么备库执行日志文件时就会报错。因此每个drop命令的binlog日志都会进行重写，保证特殊情况下不能有临时表。

💣问题二：主库上不同线程创建同名的临时表没有问题，那么在备库上有没有问题？

MySQL在记录主库操作时，会把执行这个语句的线程id页一并记录到binlog中，这样就可以在备库生成对应的table_def_key，因此备库上的临时表不存在临时表重名的问题。

1.4临时表应用——分库分表系统跨库查询

假设存在一个大表ht，那么分库分表可以这么做：将表按照字段 f 分成1024个表，然后将它们平均分配在32个MySQL实例上。一般来说会有一个中间层proxy，客户端需要访问数据库需要经过中间层。

考虑这样一条SQL语句，需要从大表查询到按照t_modified字段排序后的前100条记录。这种情况下因为每个数据表都是按照字段f进行区分的，所以只能将大表所有分区的的数据全都取出来，统一order by。具体有两方案：

①在proxy层实现排序逻辑

拿到所有数据后，直接在内存进行计算。这种方案的缺点在于，当表数据量大并且SQL操作复杂时，会给proxy的CPU和内存造成很大的压力。

②汇总实例上建立用户临时表

本质上有点类似于归并算法。

1. 创建一个新的MySQL实例来进行数据汇总
2. 在汇总库上创建一个临时表temp。
3. 在每个分库的分表上执行select语句，根据t_modified字段排序取出前100条记录。
4. 把分库的结果都插入到临时表中。
5. 最后在临时表执行select limit 100语句。

💡实际场景下，计算量往往都是不饱和的，因此可以直接在其中一个分库上进行临时表的汇总和计算。

2.内部临时表

2.1场景一、union执行流程

考虑SQL语句：Query1 union Query2；执行过程如下：

1. 创建一个内部临时表。执行第一个查询Query1，并将所有结果存入临时表。
2. 执行第二个子查询：
   • 如果某行记录插入内存临时表时，表中已经存在相同数据，违反了唯一性约束插入失败。
   • 插入成功。
3. 从临时表中取出数据，并返回结果，删除临时表。

这里临时表起到了”数据暂存“的功能。

另外如果使用了union all则表示结果不进行去重，因此不会使用到临时表，直接执行子查询发给客户端。

2.2场景二、group by

假设t1表中a字段存在索引，以下SQL语句按照id%10进行分组统计，并按照m的结果排序后输出。explain后可以发现Extra字段存在using index：temporary：filesort，并且使用上了a字段覆盖索引。

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

1. 首先创建临时表，表中存在两个字段m和c，其中m字段作为主键(因为group by的是m)
2. 扫描t1表上a索引字段(覆盖索引)B+树叶子节点，拿到id：
   • 如果id%10得到的结果在m字段下还没有这一条记录，那么插入新的记录(id%10,1)
   • 如果已经存在，则该行记录c字段值加1
3. 遍历完后，根据字段m进行排序。根据前面的内容，内存临时表会将数据一行一行放入sort_buffer，sort_buffer中包括排序字段+位置信息，排序完后从内存临时表按序取数据返回结果集。

如果group by不需要这个排序过程，那么可以直接在后面加上order by null。

tmp_table_size：表示内存临时表的大小，如果group by执行时内存临时表放不下所有数据，那么就会转化为磁盘临时表(InnoDB管理的磁盘表，按照索引排序)。因此可以通过调大这个值避免使用磁盘临时表。

2.2.1group by优化——直接排序

可以在SQL加入SQL_BIG_RESULT告诉优化器直接使用sort_buffer排序后的结果进行统计。相当于直接order by，但是因为可以直接利用sort_buffer里的结果返回结果集，因此不需要创建临时表，通过回表临时表生成结果集。

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

1. 扫描t1表a字段索引树，计算id%100的值并放入sort_buffer当中，此时sort_buffer仅有一个m字段
2. 全部计算并放入完后，对sort_buffer中的m字段值进行排序。
3. 这样就得到一个有序m字段的有序数组。之后直接统计有序数组内出现的字段值和次数即可。

整个过程不需要内存临时表，但还是需要进行排序。explain后Extra字段using index；filesort。

2.2.2group by优化——索引

原理：使用内存临时表的原因在于，需要一个临时表保存中间结果，如果当前计算id%10=6时，下一行记录id%10=1则需要回头更新数量。所以如果表内所有的行数据的id%10(也就是排序字段值)本身就是有序的，在统计的过程中，遇到第一个id%10=2，那么说明id%10=1的行数为当前的计数器值。

上一种优化方法可以省略了内存临时表的操作，进一步优化，有没有办法省略掉在sort_buffer中进行排序步骤？

索引就可以保证这个排序字段有序的条件，也就是说只需要创建一个排序字段的索引值，那么就可以省略内存临时表temporary+整个排序过程filesort

如果排序字段是某个函数映射时，可以通过给表t1创建一个新的列，并在该排序字段列上建立索引，SQL如下：

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

2.2.3group by优化——总结

无论是哪一种优化方式，假设group by 字段A，本质上都是要得到一个按照A字段排序后的表结构，这样就可以直接读一次这个“有序”表返回结果集，不需要临时表。

✨如果explain没有using filesort和temporary，则说明使用上了group by字段的索引。

2.3总结

什么情况下MySQL会使用内存临时表？

1. 语句执行过程可以只读一遍表就得到结果(可以是原表索引，也可以是内存sort_buffer)，那么就不需要中间表。否则就需要临时表保存中间结果。
2. 临时表是二维结构，如果需要用到二维表结构，一个group by作为主键，另一个字段作为其它统计的映射函数，则优先考虑临时表。

2.4group by与distinct性能

场景：只需要去重结果，不需要执行聚合函数和排序。

select a from t group by a order by null; /*Q1*/
select distinct a from t;/*Q2*/

Q1执行时不需要排序，并且不需要计数，因此性能上两个SQL语句是相同的。

1. 首先创建一个临时表，临时表仅有一个字段a(不需要聚合)，且在a上创建一个唯一索引
2. 遍历表t，依次取出行记录进临时表中进行判断：
   • 如果唯一性约束直接跳过返回，不需要额外计算
   • 否则插入a值作为新的行记录
3. 遍历完成后，返回结果集

3.内存表

内存表建表语句为create table ... engine=memory。

3.1内存表的特性

与InnoDB引擎不同，Memory引擎的数据和索引是分开的：

• 数据部分以数组的方式单独存放。并且内存表数据存放顺序按照写入顺序存放，是无序的。
• 索引上存的是每个数据所在的位置，即“堆组织表”。查找每个索引地位等同，走一次索引后就能拿到数据。

因此在增删改查上，Memory引擎和InnoDB存在一下不同：

• insert数据前删了前面某个id的记录，当数据文件有空洞时，InnoDB为了保证有序性，只能在固定位置插入新的数据；而对于内存表则可以直接插入数据。
• 数据位置发生改变时，InnoDB只需要调整主键索引树；而对于内存表，则需要修改所有的索引结构，因为每个索引都保存同一个数据的位置。
• InnoDB支持边长数据；而内存表中每行数据的长度都是固定的。
• 内存表的主键索引是哈希索引，不保证有序，因此如果是范围查询则会进行全表扫描。

基于以上特性，假设t1是内存表，执行以下语句SQL：

delete from t1 where id=5;
insert into t1 values(10,10);
select * from t1;

id=10的记录发现id=5位置存在空洞后，会插入到空洞位置。因此输出时会发现id=10出现在id=4后面

3.2内存表B-Tree索引

在内存表中，使用普通索引或者是主键索引查询时，会到内存里的数据数组(按照写入顺序组织)进行全表扫描。那么有没有办法像InnoDB一样输出按照某个索引字段排序后的结果？

内存表也支持B-Tree索引：

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

此时执行select * from t1 where id<5优化器会选择B-Tree索引。除非使用force index(primary)id索引，又会变回原本的扫描方式。

3.3内存表在生产环境下使用的弊端

内存表具有如下优势：

• Memory引擎支持hash索引
• 内存表数据都在内存，因此读写速度肯定要比磁盘快

然而实际上在生产环境并不推荐使用内存表，主要从以下几个方面进行讨论。

3.3.1内存表锁粒度问题

内存表不支持行锁，只支持表锁。因此一旦一张表有更新，那么整张表上的读写操作都会被锁住。

在下面例子中sessionA更新操作会锁住t1内存表，导致sessionB进入锁等待状态。

3.3.2数据持久化问题

内存表一旦重启，数据就会清空，因此在高可用架构下，数据丢失会影响到其它节点。场景如下：

1. 备库硬件需要升级，因此需要重启，删除内存表t1。此时MySQL为了防止重启后主备不一致，会往binlog写入一条删除t1内存表的操作日志。
2. 此时客户端向主库发送一条update更新语句，同步时备库就会发现找不到t1内存表，从而主备同步线程停止。
3. 如果是双M结构，主库还需要同步应用备库发送过来的删除日志，就会导致此时主库的内存表也自己删除了。

3.4内存表使用场景

①既然内存表存在以上问题，因此高并发情况下一般建议使用InnoDB表代替内存表，从读写性能上考虑：

• 写操作来说，在高并发场景最重要的就是并发度。内存表是表锁，InnoDB支持行锁，因此InnoDB表>内存表
• 读操作而言，因为能放进内存表的表本身就不会大，而对于小表来说InnoDB同样也可以直接利用buffer pool的数据页命中来加速读；和内存表直接读内存相比不会差，唯一的区别也就在buffer pool命中率上。因此InnoDB表≈内存表。

②对于数据量可控，不会耗费过多内存的情况下，比如前面join语句优化，可以使用内存表。

前面在join语句优化时，使用的用户临时表是写在磁盘上的，而如果这里使用“内存临时表”，那么它就可以同时具有内存表优点(快速读写)+临时表优点(线程隔离，主备复制)。具体SQL只需要在创建临时表时设置Memory引擎即可，具体如下：

create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;
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);

与前面使用的用户临时表对比，优势如下：

• 插入数据时写内存后，不需要回写磁盘，因此速度更快
• 内存临时表主键使用哈希索引，因此相比于innoDB临时表的B-Tree索引，查找速度更快。

✨在线上无论是主库还是备库，需要设置一个自动巡检的工具，只要发现存在内存表，都需要马上把该表的引擎改为InnoDB，变成磁盘表。