临时表与内存表的区别:
- 内存表,指的是使用Memory引擎的表,建表语法是create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。
- 临时表,可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用Memory引擎。
临时表的特性
看看以下的操作序列:
临时表在使用上有以下几个特点:
1. 建表语法是create temporary table …。
2. 一个临时表只能被创建它的session访问,对其他线程不可见。所以,图中session A创建的临时表t,对于session B就是不可见的。
3. 临时表可以与普通表同名。
4. session A内有同名的临时表和普通表的时候,show create语句,以及增删改查语句访问的是临时表。
5. show tables命令不显示临时表。
临时表的应用
由于不用担心线程之间的重名冲突,临时表经常会被用在复杂查询的优化过程中。比如分库分表系统的跨库查询。现有一个大表ht,按照字段f,拆分成1024个分表,然后分布到32个数据库实例上。如下图所示
在这个架构中,分区key的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包含f的等值条件,那么就要用f做分区键。这样,在proxy这一层解析完SQL语句以后,就能确定将这条语句路由到哪个分表做查询。
select v from ht where f=N;
这样我们就可以利用N来确定数据分配到哪个分表上了,比如N%1024。但是如果是另外一个语句,在表上还有一个索引k,例如
select v from ht where k >= M order by t_modified desc limit 100;
这个时候,就要到所有的分区上去执行,然后做一把order by的操作。这时候就有两种思路。
一、在proxy层的进程代码中实现排序。
处理速度快,拿到分库的数据以后,直接在内存中参与计算。不过,这个方案的缺点也比较明显:
1. 需要的开发工作量比较大。
2. 对proxy端的压力比较大,尤其是很容易出现内存不够用和CPU瓶颈的问题。
二、临时表方法
执行流程可以类似这样:
- 在汇总库上创建一个临时表temp_ht,表里包含三个字段v、k、t_modified;
-
在各个分库上执行 select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;
3. 把分库执行的结果插入到temp_ht表中;
4. 执行select v from temp_ht order by t_modified desc limit 100;
使用临时表的场景
Union语句
Group by(索引、排序)