临时表 VS 内存表

  1. 内存表,指的是使用Memory引擎的表,建表语法:CREATE TABLE ... ENGINE=Memory
    • 所有数据都保存在内存中,系统重启时被清空,但表结构还在
  2. 临时表,可以使用各种引擎
    • 如果使用的是InnoDB或者MyISAM引擎,数据需要写到磁盘上
    • 当然也可以使用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;
(显示普通表)
INSERT INTO t VALUES (1);
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优化的场景
    • 不同session的临时表可以重名,可以支持多个session并发执行Join优化
    • 无需担心数据的删除问题,临时表是自动回收

跨库查询

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

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

重名

1
2
3
4
5
6
7
8
9
10
11
12
13
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文件,用于保存表结构定义,放在临时文件目录
    • 前缀为#sql{进程ID}_{线程ID}_{序号},后缀为.frm
  2. 表中数据的存放
    • MySQL 5.6及之前,MySQL会在临时文件目录下创建一个相同的前缀,以.ibd为后缀
    • 从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
1
2
3
4
5
6
7
8
9
-- 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
    • session A和session B创建的临时表t1,磁盘文件名不同,**table_def_key也不同,因此可以并存**

实现

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

主备复制

写binlog,意味着备库需要

binlog_format

1
2
3
4
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
    • 如果关于临时表的操作都不记录,那么记录到binlog的语句就只有Q1和Q4
    • 备库执行到Q4时会报错:表temp_t不存在
    • Q2会传到备库执行,备库的同步线程就会创建这个临时表
      • 主库在线程退出时,就会自动删除临时表
      • 但备库同步线程时持续运行的,因此需要为主库的binlog自动加上DROP TEMPORARY TABLE
  2. binlog_format=ROW
    • 与临时表相关的语句,都不会记录到binlog
    • 记录Q4时,write_row enent里面记录的逻辑是:插入一行数据(1,1)
    • DROP TABLE t_normal,temp_t,binlog只能重写成
      • DROP TABLE t_normal /* generated by server */
      • 这是因为备库上并没有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)
    • CREATE TEMPORARY TABLE可能会被先后执行两次
  3. MySQL在记录binlog的时候,会把主库执行这个语句的thread_id写到binlog
    • 备库的应用线程就能知道每个语句的主库thread_id
      • 利用这个thread_id来构造临时表的table_def_key
    • session A的临时表t1,在备库上的table_def_key
      • 库名 + t1 + M的server_id + session A的thread_id
    • session B的临时表t2,在备库上的table_def_key
      • 库名 + t1 + M的server_id + session B的thread_id
    • 由于table_def_key是不同,所以两个表在备库的应用线程里面不会冲突

参考资料

《MySQL实战45讲》