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

来源:脚本之家  责任编辑:小易  
目录
1 临时表2 union临时表优化3 group by临时表优化

1 临时表

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

而使用临时表的时候,ExplainExtra字段中具有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 临时表 内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:MySQL内部临时表的具体使用浅谈Mysql在什么情况下会使用内部临时表MySQL 内存表和临时表的用法详解Mysql 存储过程中使用游标循环读取临时表Mysql临时表及分区表区别详解Mysql临时表原理及创建方法解析mysql临时表用法分析【查询结果可存在临时表中】MySQL 5.7临时表空间如何玩才能不掉坑里详解MySQL问答系列之什么情况下会用到临时表

  • 本文相关:
  • mysql数据库中数据表的优化、外键与三范式用法实例分析
  • mysql 获得当前日期时间的函数小结
  • mysql窗口函数的具体使用
  • centos7.6安装mysql8.0的步骤详解
  • 基于sql语句的一些常用语法积累总结
  • 批量清除128组节点db上面过期的binlog释放磁盘空间实现思路
  • mysql 8.0新特性之隐藏字段的深入讲解
  • mysql事务中update是否会锁表?
  • mac下mysql 8.0.22 找回密码的方法
  • mysql中select语句介绍及使用示例
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql内部临时表的具体使用浅谈mysql在什么情况下会使用内部临时表mysql 内存表和临时表的用法详解mysql 存储过程中使用游标循环读取临时表mysql临时表及分区表区别详解mysql临时表原理及创建方法解析mysql临时表用法分析【查询结果可存在临时表中】mysql 5.7临时表空间如何玩才能不掉坑里详解mysql问答系列之什么情况下会用到临时表mysql数据库中数据表的优化、外键与三范式用法实例分析mysql 获得当前日期时间的函数小结mysql窗口函数的具体使用centos7.6安装mysql8.0的步骤详解基于sql语句的一些常用语法积累总结批量清除128组节点db上面过期的binlog释放磁盘空间实现思路mysql 8.0新特性之隐藏字段的深入讲解mysql事务中update是否会锁表?mac下mysql 8.0.22 找回密码的方法mysql中select语句介绍及使用示例mysql安装图解 mysql图文安装教程(详细说明)can""""t connect to mysql servwindows下mysql5.6版本安装及配置过程附有截图和mysql字符串截取函数substring的用法说明mysql提示:the server quit withoumysql之timestamp(时间戳)用法详解mysql创建用户与授权方法mysql——修改root密码的4种方法(以windows为mysql查看版本号的几种方式mysql日期数据类型、时间类型使用总结mysql drop database删除数据库命令实例讲解mysql中escape关键字的用法详解mysql数据库安装完成后需要进行的6个后续操作windows版mysql?8.0.28?安装配置方法图文教程sysbench-0.4.12编译安装和cpu测试例子分享mysql索引使用说明(单列索引和多列索引)ubuntu安装mysql+启用远程连接的完整过程mysql压力测试工具mysqlslap的使用win10下mysql 8.0.11 压缩版安装教程mysql删除关联表的实操方法
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved