MySQL 中的临时表
|
在使用 explain 解析一个 sql 时，有时我们会发现在 extra 列上显示 using temporary ，这表示这条语句用到了临时表，那么临时表究竟是什么？它又会对 sql 的性能产生什么影响？又会在哪些场景中出现？本文根据 学习整理。 出现场景 其实临时表在之前的博客就已经出现过了，在 MySQL 中的排序 一文中就说到如果 order by 的列上没有索引，或者说没有用到索引，那么就需要进行额外排序（using filesort），而额外排序优先在一块 sort_buffer 空间中进行，如果这块空间大小小于要加载的字段总长度，那么就会用到临时文件辅助排序，这个临时文件就是临时表。临时表的作用就是作为中间表优化操作，比如 group by 作为分组的中间表， order by rand() （MySQL 中的排序 中的例子）作为中间表帮助运算等。 特点
1、建表语法是 create temporary table …。 2、一个临时表只能被创建它的 session 访问，对其他线程不可见，在会话结束后自动删除。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。（所以特别适合用于join 优化） 3、临时表可以与普通表同名。 4、session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。 5、show tables 命令不显示临时表。 种类 临时表分为磁盘临时表和内存临时表。磁盘临时表指的是存储在磁盘上的临时表，因为在磁盘上，所以执行效率比较低，优点结构可以是有序的，实现可以是 InnoDB（默认），MyISAM 引擎；内存临时表就是存储在内存中，执行效率高，常用的实现引擎是 Memory。 磁盘临时表和内存临时表的区别 1、相比于 InnoDB 表，使用内存表不需要写磁盘，往表 temp_t 的写数据的速度更快； 2、索引 b 使用 hash 索引，查找的速度比 B-Tree 索引快； 3、临时表数据只有 2000 行，占用的内存有限。 Memory 引擎 与 InnoDB 的区别 1、InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；关于这点可以通过创建 b+ 索引来进行排序，优化查询。alter table t1 add index a_btree_index using btree (id); 2、当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值； 3、数据位置发生变化的时候，InnoDB 表只需要修改主键索引mssql 临时表，而内存表需要修改所有索引； 4、InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。 5、InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。 6、内存表支持 hash 索引，并且数据存储在内存中，所以执行比数据存储在磁盘上的 Innodb 快。 缺点 1、锁粒度大，只支持表级锁，并发度低。
2、数据持久性差。因为是内存结构，所以在重启后数据会丢失 。由此会导致备库在硬件升级后数据就会丢失，并且如果主从库互为 "主备关系" ，备库在关闭后还会将删除数据记录进 binlog，重启后主机会执行备库发送过来的 binlog ，导致主库数据也会丢失。 虽然 Memory 引擎看起来缺点很多，但是因为其存储在内存中，并且关机后会自动清除数据，所以其是作为临时表的一个绝佳选择。 常见的应用场景 分库分表查询 将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上（水平分表）。一般情况下，这种分库分表系统都有一个中间层 proxy。不过，也有一些方案会让客户端直接连接数据库，也就是没有 proxy 这一层。假设分区键是 列 f 。 1、如果只使用分区键作为查询条件如 select v from ht where f=N，那么直接通过分表规则找到 N 所在的表，然后去该表上查询就可以了。 2、如果使用其他字段作为条件且需要排序如 select v from ht where k >= M order by t_modified desc limit 100，那么非但不能确定要查询的记录在哪张表上，而且因为默认使用的是分区键排序，所以得到的结果还是无序的，需要额外排序。 1）在 proxy 层完成排序。优势是速度快，缺点是开发工作量比较大，如果涉及复杂的操作如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高。并且还容易出现内存不够、CPU 瓶颈的问题。 2）将各个分区的查询结果（未排序）总结到一张临时表上进行排序。 Ⅰ、在汇总库上创建一个临时表 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 表中； Ⅳ、执行 select v from temp_ht order by t_modified desc limit 100; union 作为中间表 有表t1：create table t1(id int primary key, a int, b int, index(a)); 有记录(1,1,1) 到 (1000,1000,1000)执行 (select 1000 as f) union (select id from t1 order by id desc limit 2); 解析这条 sql：
可以知道： 1、左边语句没有进行查表操作2、右边语句使用了 id 索引3、联合时使用了临时表 具体过程： 1、创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。 2、执行第一个子查询，得到 1000 这个值，并存入临时表中。 3、执行第二个子查询： 1）拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行； 2）取到第二行 id=999，插入临时表成功。 4、从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。
排序返回的字段过大 举一个在 MySQL中的排序 中提到过的例子。 select word from words order by rand() limit 3; 表数据有10000行 SQL是从10000行记录中随机获取3条记录返回。 这个执行过程因为涉及到 rand() 且数据量比较大，所以单靠 sort_buffer 排序空间不够，所以还用到临时表。 过程： 1、从缓冲池依次读取记录，每次读取后都调用 rand() 函数生成一个 0-1 的数存入内存临时表，W 是 word 值，R 是 rand() 生成的随机数。到这扫描了 10000 行。 2、初始化 sort_buffer，从内存临时表中将 rowid（这张表自动生成的） 以及 排序数据 R 存入 sort_buffer。到这因为要遍历内存临时表所以又扫描了 10000 行。 3、在 sort_buffer 中根据 R 排好序，然后选择前三个记录的 rowid 逐条去内存临时表中查到 word 值返回。到这因为取了三个数据去内存临时表去查找所以又扫描了 3 行。总共 20003 行。
group by 作为中间表 执行：select id as m, count(*) as c from t1 group by m; 首先解析 SQL： 可以看到使用了临时表和额外排序，接下来来解析 执行过程： 1、创建内存临时表，表里有两个字段 m 和 c，主键是 m； 2、扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id 的结果，记为 x； 1）如果临时表中没有主键为 x 的行，就插入一个记录 (x,1); 2）如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1； 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。
排序的过程就按照排序规则进行，用到 sort_buffer ，可能用到临时表。 优化 BNL 排序 表结构： CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB; t1、t2 结构相等，t2 100万条数据，t1 1000行数据，t1 的数据在 t2 上都有对应，相等。执行语句：select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b=1 and b （编辑：衡阳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
