MySQL 临时表

内存表，指的使用Memory引擎的表，建表语法是create table… engine = memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。

临时表，可以使用各种引擎（MySQL、Memory），写数据的时候写到磁盘上。

1.建表语法是create temporary table ….

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

3.临时表可以与普通表同名

4.session A内有同名的临时表和普通表的时候，show create语句，以及增删改查访问的临时表。

5.show tables命令不显示临时表。

由于临时表只能被创建它的sesssion访问，session结束会自动删除，所以临时表就特别适合优化join的场景。

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

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

但是如果查询条件中没有分区字段，只能到所有的分区中查询满足条件的所有行，然后统一做order by的操作。两种常见的思路。

1.在proxy层的进程代码中实现排序。

优点：处理速度快。缺点：需要开发的工作量比较大，对proxy端的压力比较大。

2.在各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作。

在汇总库中创建一个临时库temp_ht，包含三个字段 v、k、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个分库中的某一个上。

MySQL用一个frm文件保存表结构的定义，这个frm文件放在临时文件下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id}_序列号”

5.6以及之前的版本里，MySQL会在临时文件目录下创建一个相同前缀，以.ibd为后缀的文件，用来存放数据文件。

5.7版本开始，MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。

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

一个普通的table_def_key的值是由“库名+表名“得到的。

临时表，则是在”库名+表名“基础上，又加入了”server_id+thread_id”

在实现上，每个线程都维护了自己的临时表链表。

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

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

不同线程创建同名给的临时表是没问题的，但是传到备库是怎么执行的呢？

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

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

如果需要并不需要对结果进行排序，那可以在SQL语句末尾增加 order by null。

group by 优化方法 – 索引

MySQL 5.7 版本支持了generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列z，然后在z 列上创建一个索引。

group by 优化方法 – 直接排序

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

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