临时表 VS 内存表
- 内存表,指的是使用Memory引擎的表,建表语法:CREATE TABLE … ENGINE=Memory。所有数据都保存在内存中,系统重启时被清空,但表结构还在
- 临时表,可以使用各种引擎
- 如果使用的是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;(显示普通表) |
|
SELECT * FROM t;(返回1) |
|
SELECT * FROM t;(Empty set) |
- 建表语法:CREATE TEMPORARY TABLE
- 临时表只能被创建它的session访问,对其它线程是不可见的
- 临时表可以与普通表同名
- 同一个session内有同名的临时表和普通表时,SHOW CREATE语句以及增删改查语句访问的是临时表
- SHOW TABLES命令不显示临时表
- 在session结束时,会自动删除临时表,临时表特别适用于Join优化的场景
- 不同session的临时表可以重名,可以支持多个session并发执行Join优化
- 无需担心数据的删除问题,临时表是自动回收的
跨库查询
将一个大表ht,按照字段f,拆分成1024个表,然后分布到32个数据库实例,每个实例32张表
- 选择分区Key的依据:减少跨库查询和跨表查询,如果大部分语句都会包含f的等值条件,就要用f做分区键
- 在Proxy这一层解析完SQL语句后,就能确定将这条语句路由到哪一个分区表做查询
- 例如SELECT v FROM ht WHERE f=N;,通过分表规则来确认需要的数据被放到哪一个分表上
- 假如表上还有另外一个索引k,
- 对于SELECT v FROM ht WHERE k >= M ORDER BY t_modified DESC LIMIT 100;
- 没有用到字段f,只能到所有分区中去查找所有满足条件的行,然后再统一做ORDER BY操作
- 两种实现思路
- 在Proxy层的进程代码中实现排序:
- 优点:处理速度快
- 缺点:开发工作量大,对Proxy端压力较大(内存不足和CPU瓶颈)
- 从各个分库拿到数据,汇总到一个MySQL实例中的一个表,然后在汇总表上做逻辑操作
- 在Proxy层的进程代码中实现排序:
汇总表方案
- 在汇总库上创建一个临时表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
- 在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;
+----</