MySQL 临时表的原理以及优化方法

目录
  • 1 临时表
  • 2 union临时表优化
  • 3 group by临时表优化

1 临时表

sort buffer、内存临时表和join buffer，这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助SQL语句的执行的。其中，在排序的时候用到了sort buffer，在使用join语句的时候用到了join buffer。

而使用临时表的时候，Explain的Extra字段中具有Using temporary标记。union、group by、distinct等等查询都有可能使用到临时表。

2 union临时表优化

使用union的时候，就需要用到内存临时表来进行去重。

union语句的执行流程为：

  • 创建一个内存临时表。
  • 执行第一个子查询，得到值，并存入临时表中。
  • 执行第二个子查询：依次拿每一行数据和临时表中的每一行数据比较，如果重复则不会插入，这样就实现了去重的功能
  • 从临时表中按行取出数据，返回结果，并删除临时表。在最后这一步还可以对临时表进行其他操作，比如limit、ORDER BY。

如果使用union all，则不需要去重，也就不需要临时表了。在执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此，除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时我的数据做唯一性检查。这样做的代价非常高。如果不需要这些检查，那么甚至都不需要临时表。

另外，避免对于union之后的结果集进行操作，也能避免临时表的使用，通常需要手工地将MHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化，使得union的结果就是最终的结果（例如，直接将这些子句冗余地写一份到各个子查询)。

3 group by临时表优化

另外一个使用临时表的例子是group by，group by还具有隐藏的排序的语句，即在对某些字段进行分组之后，将数据再根据这些字段进行排序，最后返回排序后的结果。

如下sql：

  select id%10 as m, count(*) as c from t1 group by m;

这个语句的执行流程是这样的：

  • 创建内存临时表，表里有两个字段m和c，主键是m；
  • 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；
    • 如果临时表中没有主键为x的行，就插入一个记录(x,1);
    • 如果表中有主键为x的行，就将x这一行的c值加1；
    • 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

此时，Explain的Extra字段中具有Using temporary; Using filesort标记。

如果并不需要对结果进行排序，那可以在SQL语句末尾增加order by null，即：

  select id%10 as m, count(*) as c from t1 group by m order by null;

这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M。如果要处理的数据超过了最大大小，那么MySQL会把内存临时表转成磁盘临时表，而磁盘临时表默认使用的引擎是InnoDB，因此会按主键顺序存储数据，所以最终取出的结果还是默认有序的。

对于Group By的临时表的优化，同样是使用索引：因为如果进行Group By字段是有序的，那么在处理时（比如计算每组数量、个数等等），因为跟着的字段有索引，那么相同的值肯定是在一起的、连续的，所以直接顺序扫描输入的数据即可，不需要临时表，也不需要再额外排序。

总结：

  • 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
  • join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；

如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。

另外，对于distinct查询来说，如果无法使用索引，则也会使用到临时表，也会进行分组，它和group by的区别是不需要排序。

到此这篇关于MySQL 临时表的原理以及优化方法的文章就介绍到这了,更多相关MySQL 临时表 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 浅谈Mysql在什么情况下会使用内部临时表

    浅谈Mysql在什么情况下会使用内部临时表

    union执行 为了便于分析,使用一下sql来进行举例 CREATE TABLE t1 ( id INT PRIMARY KEY, a INT, b INT, INDEX ( a ) ); delimiter ;; CREATE PROCEDURE idata ( ) BEGIN DECLARE i INT; SET i = 1; WHILE ( i <= 1000 ) DO INSERT INTO t1 VALUES ( i, i, i ); SET i = i + 1; END WHILE;

  • MySQL内部临时表的具体使用

    MySQL内部临时表的具体使用

    目录 UNION 表初始化 执行语句 UNION RESULT UNION ALL GROUP BY 内存充足 执行语句 执行过程 排序过程 ORDER BY NULL 内存不足 执行语句 优化方案 优化索引 直接排序 执行过程 对比DISTINCT 小结 参考资料 UNION UNION语义:取两个子查询结果的并集,重复的行只保留一行 表初始化 CREATE TABLE t1(id INT PRIMARY KEY, a INT, b INT, INDEX(a)); DELIMITER ;; C

  • MySQL 内存表和临时表的用法详解

    内存表: session 1 $ mysql -uroot root@(none) 10:05:06>use test Database changed root@test 10:06:06>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY; Query OK, 0 rows affected (0.00 sec) root@test 10:08:46>insert into tmp_memory values (1); Query OK,

  • mysql临时表用法分析【查询结果可存在临时表中】

    本文实例讲述了mysql临时表用法.分享给大家供大家参考,具体如下: 一.创建临时表可以将查询结果寄存.报表制作的查询sql中可以用到. (1)关于寄存方式,mysql不支持: select * into tmp from maintenanceprocess (2)可以使用: create table tmp (select ...) 举例: #单个工位检修结果表上部 drop table if EXISTS tmp_单个工位检修结果表(检查报告)上部; create table tmp_单个

  • Mysql临时表原理及创建方法解析

    这篇文章主要介绍了Mysql临时表原理及创建方法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 mysql 利用 temporary 关键字就可以创建出一个临时表.创建的这张表会在与服务器的会话终止时自动消失 语法:create temporary table tbl_name...; 规则:每个会话只能看到自己创建的临时表,不同的会话可以创建相同表名称的临时表.临时表的表名可以和永久表的名字相同. 好处:可以利用临时表保存一些临时数据,断

  • MySQL问答系列之什么情况下会用到临时表

    临时表介绍 什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间.为什么会产生临时表:一般是由于复杂的SQL导致临时表被大量创建 临时表分为两种,一种是内存临时表,一种是磁盘临时表.内存临时表采用的是memory存储引擎,磁盘临时表采用的是myisam存储引擎(磁盘临时表也可以使用innodb存储引擎,通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎,从mysql5.7.6之后

  • Mysql 存储过程中使用游标循环读取临时表

    游标 游标(Cursor)是用于查看或者处理结果集中的数据的一种方法.游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力. 游标的使用方式 定义游标:Declare 游标名称 CURSOR for table;(table也可以是select出来的结果集) 打开游标:Open 游标名称; 从结果集获取数据到变量:fetch 游标名称 into field1,field2; 执行语句:执行需要处理数据的语句 关闭游标:Close 游标名称; BEGIN # 声明自定义变量 declare

  • MySQL 5.7临时表空间如何玩才能不掉坑里详解

    导读 MySQL 5.7的目标是成为发布以来最安全的MySQL服务器,其在SSL/TLS和全面安全开发方面有一些重要的改变. MySQL 5.7起支持独立临时表空间,但个别时候也可能会踩坑的. MySQL 5.7起,开始采用独立的临时表空间(和独立的undo表空间不是一回事哟),命名ibtmp1文件,初始化12M,且默认无上限. 选项 innodb_temp_data_file_path 可配置临时表空间相关参数. innodb_temp_data_file_path = ibtmp1:12M:

  • Mysql临时表及分区表区别详解

    临时表与内存表 内存表,指的是使用Memory引擎的表,建表语法是create table - engine=memory.这种 表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在.除了这两个特性看 上去比较"奇怪"外,从其他的特征上看,它就是一个正常的表 临时表,可以使用各种引擎类型 .如果是使用InnoDB引擎或者MyISAM引擎的临时表,写 数据的时候是写到磁盘上的.当然,临时表也可以使用Memory引擎. 临时表特性 建表语法是create temporary ta

  • MySQL 临时表的原理以及优化方法

    目录 1 临时表 2 union临时表优化 3 group by临时表优化 1 临时表 sort buffer.内存临时表和join buffer,这三个数据结构都是用来存放语句执行过程中的中间数据,以辅助SQL语句的执行的.其中,在排序的时候用到了sort buffer,在使用join语句的时候用到了join buffer. 而使用临时表的时候,Explain的Extra字段中具有Using temporary标记.union.group by.distinct等等查询都有可能使用到临时表.

  • Mysql账户管理原理与实现方法详解

    Mysql账户管理原理与实现方法详解

    本文实例讲述了Mysql账户管理原理与实现方法.分享给大家供大家参考,具体如下: 账户管理 在生产环境下操作数据库时,绝对不可以使用root账户连接,而是创建特定的账户,授予这个账户特定的操作权限,然后连接进行操作,主要的操作就是数据的crud MySQL账户体系:根据账户所具有的权限的不同,MySQL的账户可以分为以下几种 服务实例级账号:,启动了一个mysqld,即为一个数据库实例:如果某用户如root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库.连同这些库中的表 数据库级别

  • 深入解析MySQL索引的原理与优化策略

    目录 索引的概念 索引的原理 索引的类型 索引的使用 索引的使用方式 注意事项 索引优化技巧 索引的概念 MySQL索引是一种用于加速数据库查询的数据结构,它类似于书籍的目录,能够快速指导我们找到需要的信息.MySQL索引可以根据一定的算法和数据结构进行排序和存储,从而实现高效的数据查找和访问.在数据库中,索引可以加速数据的查询和更新操作,提高系统性能. MySQL支持多种索引类型,常见的包括B-tree索引.哈希索引和全文索引等.其中,B-tree索引是最常用的一种,它是一种平衡树结构,可以将

  • MySQL数据库索引原理及优化策略

    目录 1 索引 索引概念 索引作用 索引的使用场景 2 索引分类 B树索引和B+树索引区别 3 索引操作 创建主键索引 唯一索引的创建 普通索引的创建 查询索引 删除索引 索引创建原则 1 索引 索引概念 索引是一种特殊的文件,包含着对数据表里所有记录的引用指针.可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现. 索引作用 数据库中的表.数据.索引之间的关系,类似于书架上的图书.书籍内容和书籍目录的关系,索引所起的作用类似书籍目录,可用于快速定位.检索数据.索引可以

  • MySQL主从复制的原理及配置方法（比较详细）

    一.复制的原理 MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新.删除等等).每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新,以便从服务器可以对其数据拷贝执行相同的更新. 将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句.请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作.并且,该语句将获得全局读锁定. MySQL 使用3个线程来执行复制功能,其中1个在主服

  • 详细分析mysql视图的原理及使用方法

    前言: 在MySQL中,视图可能是我们最常用的数据库对象之一了.那么你知道视图和表的区别吗?你知道创建及使用视图要注意哪些点吗?可能很多人对视图只是一知半解,想详细了解视图的同学看过来哟,本篇文章会详细介绍视图的概念.创建及使用方法. 1.视图定义及简单介绍 视图是基于 SQL 语句的结果集的可视化的表,即视图是一个虚拟存在的表,可以包含表的全部或者部分记录,也可以由一个表或者多个表来创建.使用视图就可以不用看到数据表中的所有数据,而是只想得到所需的数据.当我们创建一个视图的时候,实际上是在数据

  • MYSQL开发性能研究之批量插入数据的优化方法

    MYSQL开发性能研究之批量插入数据的优化方法

    一.我们遇到了什么问题 在标准SQL里面,我们通常会写下如下的SQL insert语句. INSERT INTO TBL_TEST (id) VALUES(1); 很显然,在MYSQL中,这样的方式也是可行的.但是当我们需要批量插入数据的时候,这样的语句却会出现性能问题.例如说,如果有需要插入100000条数据,那么就需要有100000条insert语句,每一句都需要提交到关系引擎那里去解析,优化,然后才能够到达存储引擎做真的插入工作. 正是由于性能的瓶颈问题,MYSQL官方文档也就提到了使用批

  • MySQL order by性能优化方法实例

    前言 工作过程中,各种业务需求在访问数据库的时候要求有order by排序.有时候不必要的或者不合理的排序操作很可能导致数据库系统崩溃.如何处理好order by排序呢?本文从原理以及优化层面介绍 order by . 一 MySQL中order by的原理 1 利用索引的有序性获取有序数据 当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且索引访问方式为 rang,ref 或者 index 的时候,MySQL 可以利用索引顺序而

  • MySQL几点重要的性能指标计算和优化方法总结

    1 QPS计算(每秒查询数) 针对MyISAM引擎为主的DB MySQL> show GLOBAL status like 'questions'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | Questions | 2009191409 | +---------------+------------+ 1 row in set (0.00 sec) mysql

随机推荐