MySQL 用户临时表

临时表 VS 内存表

1. 内存表，指的是使用Memory引擎的表，建表语法：CREATE TABLE … ENGINE=Memory。所有数据都保存在内存中，系统重启时被清空，但表结构还在
2. 临时表，可以使用各种引擎
   1. 如果使用的是InnoDB或者MyISAM引擎，数据需要写到磁盘上
   2. 当然也可以使用Memory引擎

特征

session A	session B
CREATE TEMPORARY TABLE t(c int) ENGINE=MyISAM;（创建临时表）
	SHOW CREATE TABLE t;(Table ‘test.t’ doesn’t exist)
CREATE TABLE t(id INT PRIMARY KEY) ENGINE=InnoDB;（创建普通表）
SHOW CREATE TABLE t;（显示临时表）
SHOW TABLES;（显示普通表）
	SELECT * FROM t;(返回1)
SELECT * FROM t;(Empty set)

1. 建表语法：CREATE TEMPORARY TABLE
2. 临时表只能被创建它的session访问，对其它线程是不可见的
3. 临时表可以与普通表同名
4. 同一个session内有同名的临时表和普通表时，SHOW CREATE语句以及增删改查语句访问的是临时表
5. SHOW TABLES命令不显示临时表
6. 在session结束时，会自动删除临时表，临时表特别适用于Join优化的场景
   1. 不同session的临时表可以重名，可以支持多个session并发执行Join优化
   2. 无需担心数据的删除问题，临时表是自动回收的

跨库查询

将一个大表ht，按照字段f，拆分成1024个表，然后分布到32个数据库实例，每个实例32张表

1. 选择分区Key的依据：减少跨库查询和跨表查询，如果大部分语句都会包含f的等值条件，就要用f做分区键
2. 在Proxy这一层解析完SQL语句后，就能确定将这条语句路由到哪一个分区表做查询
   1. 例如SELECT v FROM ht WHERE f=N;，通过分表规则来确认需要的数据被放到哪一个分表上
3. 假如表上还有另外一个索引k，
   1. 对于SELECT v FROM ht WHERE k >= M ORDER BY t_modified DESC LIMIT 100;
   2. 没有用到字段f，只能到所有分区中去查找所有满足条件的行，然后再统一做ORDER BY操作
4. 两种实现思路
   1. 在Proxy层的进程代码中实现排序:
      1. 优点：处理速度快
      2. 缺点：开发工作量大，对Proxy端压力较大（内存不足和CPU瓶颈）
   2. 从各个分库拿到数据，汇总到一个MySQL实例中的一个表，然后在汇总表上做逻辑操作

汇总表方案

1. 在汇总库上创建一个临时表temp_ht，表里包含三个字段v、k和t_modified
2. 在各个分库上执行
3. SELECT v,k,t_modified FROM ht_x WHERE k >= M ORDER BY t_modified DESC LIMIT 100;
4. 把分库的执行结果插入到临时表temp_ht
5. 在temp_ht上执行。SELECT v FROM temp_ht ORDER BY t_modified DESC LIMIT 100;

重名

CREATE TEMPORARY TABLE temp_t(id INT PRIMARY KEY) ENGINE=InnoDB;

mysql> SELECT @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp     |
+----------+

mysql> system ls -l /tmp
total 108
-rw-rw---- 1 mysql mysql  8556 Mar 12 05:00 #sql1_1_0.frm
-rw-rw---- 1 mysql mysql 98304 Mar 12 05:00 #sql1_1_0.ibd

1. 创建一个frm文件，用于保存表结构定义，放在临时文件目录
   1. 前缀为#sql{进程ID}{线程ID}{序号}，后缀为.frm
2. 表中数据的存放
   1. MySQL 5.6及之前，MySQL会在临时文件目录下创建一个相同的前缀，以.ibd为后缀
   2. 从MySQL 5.7开始，MySQL引入了一个临时文件表空间，用来存放临时文件的数据，不再需要ibd
3. 从文件名的前缀规则可知，临时表和普通表的存储是不一样的，因此可以重名

磁盘存储

session A	session B
CREATE TEMPORARY TABLE t1(id INT);// #sql1_2_0.frm
CREATE TEMPORARY TABLE t2(id INT);// #sql1_2_1.frm
	CREATE TEMPORARY TABLE t1(id INT);// #sql1_3_0.frm

-- session A的线程ID为2，session B的线程ID为3
-- session A和session B创建的临时表，在磁盘上的文件是不会重名的
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | test | Sleep   |  127 |       | NULL             |
|  3 | root | localhost | test | Query   |    0 | init  | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+

内存区分

1. 每个表都有一个对应的table_def_key
2. 普通表的table_def_key：库名 + 表名\
3. 临时表的table_def_key：库名 + 表名 + server_id + thread_id
   1. session A和session B创建的临时表t1，磁盘文件名不同，table_def_key也不同，因此可以并存

实现

1. 每个线程都维护自己的临时表链表
2. 每次session内操作表的时候，先遍历链表，检查是否有同名的临时表（临时表优先）
3. 在session结束时，对链表里的每个临时表，执行删除表操作
   1. DROP TEMPORARY TABLE t1
   2. binlog中也记录了上面的删除命令

主备复制

写binlog，意味着备库需要

binlog_format

CREATE TABLE t_normal(id INT PRIMARY KEY, c INT) ENGINE=InnoDB; -- Q1
CREATE TEMPORARY TABLE temp_t LIKE t_normal; -- Q2
INSERT INTO temp_t VALUES(1,1); -- Q3
INSERT INTO t_normal SELECT * FROM temp_t; -- Q4

1. binlog_format=STATEMENT/MIXED
   1. 如果关于临时表的操作都不记录，那么记录到binlog的语句就只有Q1和Q4
   2. 备库执行到Q4时会报错：表temp_t不存在
   3. Q2会传到备库执行，备库的同步线程就会创建这个临时表
      1. 主库在线程退出时，就会自动删除临时表
      2. 但备库同步线程时持续运行的，因此需要为主库的binlog自动加上DROP TEMPORARY TABLE
2. binlog_format=ROW
   1. 与临时表相关的语句，都不会记录到binlog
   2. 记录Q4时，write_row enent里面记录的逻辑是：插入一行数据(1,1)
   3. DROP TABLE t_normal，temp_t，binlog只能重写成
      1. DROP TABLE t_normal /* generated by server */
      2. 这是因为备库上并没有temp_t，需要重写后再传到备库去执行，才不会导致备库同步线程停止

同名临时表

实例S是实例M的备库

时刻	M session A	M session B	S的应用日志线程
T1	CREATE TEMPORARY TABLE t1(id INT);
T2			CREATE TEMPORARY TABLE t1(id INT);
T3		CREATE TEMPORARY TABLE t1(id INT);
T4			CREATE TEMPORARY TABLE t1(id INT);

1. 主库上的两个session创建了同名的临时表t1，这两个语句都会被传到备库S上去执行
2. 但备库的应用日志线程是共用的（哪怕是多线程复制，两个语句也有可能被分配给同一个workder）
   1. CREATE TEMPORARY TABLE可能会被先后执行两次
3. MySQL在记录binlog的时候，会把主库执行这个语句的thread_id写到binlog
   1. 备库的应用线程就能知道每个语句的主库thread_id。 利用这个thread_id来构造临时表的table_def_key
   2. session A的临时表t1，在备库上的table_def_key。库名 + t1 + M的server_id + session A的thread_id
   3. session B的临时表t2，在备库上的table_def_key。库名 + t2 + M的server_id + session B的thread_id
   4. 由于table_def_key是不同，所以两个表在备库的应用线程里面不会冲突

参考资料 《MySQL实战45讲》