精华内容
下载资源
问答
  • MYSQL 临时表用法总结

    2020-12-14 17:30:32
    MYSQL 临时表用法总结 1.背景 老项目最近被吐槽性能不行,经排查发现mysql占用cpu过高,梳理流程发现一些经常被使用的字段需要多张表关联之后可获取,于是想到了临时表; 2.临时表用法总结 将正常的CREATE TABLE语句...
  • 主要介绍了mysql临时表用法,结合实例形式分析了MySQL将查询结果存储在临时表中的相关操作技巧,需要的朋友可以参考下
  • MySql临时表用法

    2021-04-13 15:07:10
    MySql临时表用法MySql临时表用法 MySql临时表用法 mysql临时表应用场景,报表sql中的多层嵌套 # mysql临时表应用场景,报表sql中的多层嵌套 # mysql临时表用法 DROP TABLE IF EXISTS su; CREATE TEMPORARY...

    MySql临时表的用法

    MySql临时表的用法

    mysql临时表应用场景,报表sql中的多层嵌套

    # mysql临时表应用场景,报表sql中的多层嵌套
    # mysql临时表的用法
    DROP TABLE IF EXISTS su;
    CREATE TEMPORARY TABLE su(
        SELECT  e.empno e_empno,e.ename e_ename,e1.empno e1_empno,e1.ename e1_ename from emp e, emp e1  WHERE e.mgr=e1.empno
    );
    SELECT *FROM su;
    # 注意最前面必须加drop table 不然每次执行会报 表或者视图不存在异常
    

    注意最前面必须加drop table 不然每次执行会报 表或者视图不存在异常

    展开全文
  • 给大家简单介绍了MySQL临时表的简单用法,对此有需要的朋友可以跟着学习操作下。
  • MySQL临时表

    千次阅读 2016-11-23 15:53:41
    1.临时表存储MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表默认使用MySQL的MEMORY存储引擎, default_tmp_storage_engine 可以控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型,默认是MEMORY...

    MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。

    1.临时表

    MySQL临时表分为“内存临时表”和“磁盘临时表”,并且任何临时表都是没有索引的。

    其中内存临时表默认使用MySQL的MEMORY存储引擎,default_tmp_storage_engine 可以控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型,默认是MEMORY。

    磁盘临时表使用MySQL的MyISAM存储引擎,internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB。

    一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表从内存导出到磁盘临时表。

    从磁盘临时表与内存临时表的差异中大家可以看到,磁盘临时表只是内存临时的一个替代品。这就好像操作系的虚拟内存一样。当内存不够用时,可以在硬盘上的一个空间作为其替代品,将内存中的部分数据转移到虚拟内存中。这个磁盘临时表也是相同的道理。

    可以通过 SHOW VARIABLES LIKE ‘tmpdir’; 来查看磁盘临时表的储存位置。

    但是值得注意的是,硬盘的效率与内存的效率是不同的。在执行相同的一个作业时,内存的性能要高于硬盘的性能,一般会高上百倍,甚至上千倍。从这里就可以看出,为了提高数据库系统的性能,最好选择内存临时表,而放弃使用磁盘临时表。

    2.什么时候会用到临时表

    1、GROUP BY xx,并且xx不能利用索引排序时。
    2、在关联查询中,ORDER BY或者GROUP BY使用了不是第一个表(驱动表)的列,例如:SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name。此时,根据嵌套循环查询的特点可以知道,无论是否有索引,最后得到的结果中第二张表中的字段都是乱序的,因此MySQL只能先将关联的结果存放在临时表中,然后在所有的关联都结束后,再进行排序或分组。此时会在 EXPLAIN的结果中看到“Using temporary; Using filesort”。而如果所有的 ORDER BY 和 GROUP BY都在第一张表时,MySQL在关联处理第一张表时就能进行文件排序了,因此此时能够利用索引(如果有的话)省去文件排序和临时表了。
    3、ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
    4、SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL,结果会很小,请直接使用内存临时表,不需要使用索引排序。SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用。一般情况下,没有必要使用这个选项,让MySQL服务器选择即可。
    5、某些子查询会将子查询的结果放入临时表中,然后继续之后的查询。比如 WHERE 子句里的子查询。
    6、UNION。

    3.直接使用磁盘临时表的场景

    1、表包含TEXT或者BLOB列;
    2、GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列;
    3、使用UNION或者UNION ALL时,SELECT子句中包含大于512字节的列;

    4.临时表相关配置

    tmp_table_size:指定系统创建的内存临时表最大大小;
    max_heap_table_size: 指定用户创建的内存表的最大大小;

    注意:最终的系统创建的内存临时表大小是取上述两个配置值的最小值。当内存临时表大小超过配置项后,就会导出到磁盘。

    5.表的设计原则

    使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有:
    1、创建索引:在ORDER BY或者GROUP BY的列上创建索引,并保证能利用到索引;
    2、分拆很长的列:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。

    6.如何判断使用了临时表?

    使用explain查看执行计划,Extra列看到Using temporary就意味着使用了临时表。

    展开全文
  • 主要介绍了Mysql临时表原理及创建方法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • 使用MySQL临时表,有时是可以加速查询的,下面就为您详细介绍使用MySQL临时表加速查询的方法。 把表的一个子集进行排序并创建MySQL临时表,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器...
  • Mysql临时表使用说明

    2010-09-01 11:10:40
    Mysql临时表使用说明Mysql临时表使用说明
  • mysql临时表

    千次阅读 2019-04-07 20:52:44
    首先,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。 创建临时表的语法与创建表语法类似,不同之处是增加...

      首先,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。

            创建临时表的语法与创建表语法类似,不同之处是增加关键字TEMPORARY,如:

                   CREATE TEMPORARY TABLE 表名 (…. )

     

           临时表使用有一些限制条件

      *  临时表在 memory、myisam、merge或者innodb上使用,并且不支持mysql cluster簇);

                   show tables语句不会列出临时表,在information_schema中也不存在临时表信息;show create table可以查看临时表;

      *  不能使用rename来重命名临时表。但是可以alter table rename代替:

                 mysql>ALTER TABLE orig_name RENAME new_name;

      *  可以复制临时表得到一个新的临时表,如:

                    mysql>create temporary table new_table select * from old_table;

      *  但在同一个query语句中,相同的临时表只能出现一次。如:

                   可以使用:mysql> select * from temp_tb;

                   但不能使用:mysql> select * from temp_tb, temp_tb as t;

                   错误信息:   ERROR 1137 (HY000): Can't reopen table: 'temp_tb'

            同样相同临时表不能在存储函数中出现多次,如果在一个存储函数里,用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,都会出现这个错误。

      *  但不同的临时表可以出现在同一个query语句中,如临时表temp_tb1, temp_tb2:

                     Mysql> select * from temp_tb1, temp_tb2;

            临时表可以手动删除:

                     DROP TEMPORARY TABLE IF EXISTS temp_tb;

     

           临时表主要用于对大数据量的表上作一个子集,提高查询效率

     

           在创建临时表时声明类型为HEAP,则Mysql会在内存中创建该临时表,即内存表:如:

                    CREATE TEMPORARY TABLE 表名 (。。。。) TYPE = HEAP

           因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。如:

    mysql> create temporary table temp_tb type='heap' select * from temptb;

    Query OK, 0 rows affected, 1 warning (0.01 sec)

    Records: 0  Duplicates: 0  Warnings: 0

     

    mysql> show create table temp_tb \G;

    *************************** 1. row ***************************

           Table: temp_tb

    Create Table: CREATE TEMPORARY TABLE `temp_tb` (

      `id` int(10) unsigned NOT NULL DEFAULT '0',

      `Name` char(20) NOT NULL,

      `Age` tinyint(4) NOT NULL

    ) ENGINE=MEMORY DEFAULT CHARSET=gbk

    1 row in set (0.00 sec)

     

    ERROR:

    No query specified

             可以看出来临时表和内存表的ENGINE 不同,临时表默认的是Mysql指定的默认Engine,而内存表是MEMORY

    官方手册:
    As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.

     

    内存表的建立还有一些限制条件
          MEMORY tables cannot contain        BLOB or TEXT columns. HEAP不支持BLOB/TEXT列。    
          The server needs sufficient memory to maintain all   MEMORY tables that are in use at the same time. 在同一时间需要足够的内存.
          To free memory used by a MEMORY table when   you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP        TABLE.为了释放内存,你应该执行DELETE FROM heap_table或DROP TABLE heap_table。

     

    临时表和内存表

            临时表主要是为了放一些中间大结果集的一些子集,内存表可以放一些经常频繁使用的数据。

            *  临时表:表建在内存里,数据在内存里
            *  内存表:表建在磁盘里,数据在内存里

            临时表和内存表所使用内存大小可以通过My.cnf中的max_heap_table_size、tmp_table_size指定:
                  [mysqld]
                  max_heap_table_size=1024M   #内存表容量
                  tmp_table_size=1024M              #临时表容量

            当数据超过临时表的最大值设定时,自动转为磁盘表,此时因需要进行IO操作,性能会大大下降,而内存表不会,内存表满后,则会提示数据满错误。

           show tables 命令不会显示临时表。

            以下是对内存表和临时表之间区别的总结:

       内存表:

            1.缺省存储引擎为MEMORY
            2.可以通过参数max_heap_table_size来设定内存表大小
            3.到达max_heap_table_size设定的内存上限后将报错
            4.表定义保存在磁盘上,数据和索引保存在内存中
            5.不能包含TEXT、BLOB等字段
       临时表:

            1.缺省存储引擎为MySQL服务器默认引擎,引擎类型只能是:memory(heap)、myisam、merge、innodb(memory临时表由于表的增大可能会转变为myisam临时表)
            2.可以通过参数 tmp_table_size 来设定临时表大小。
            3.到达tmp_table_size设定的内存上限后将在磁盘上创建临时文件
            4.表定义和数据都保存在内存中
            5.可以包含TEXT, BLOB等字段

            临时表一般比较少用,通常是在应用程序中动态创建或者由MySQL内部根据SQL执行计划需要自己创建。

            内存表则大多作为Cache来使用,特别在没有第三方cache使用时。如今随着memcache、NoSQL的流行,越来越少选择使用内存表。

     

    关于使用临时表可能会出现的 问题

    【问题现象】

    线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。

    SQL语句如下:

    SELECT DISTINCTg.*,cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROMgm_gameg LEFT JOINgm_cpcp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOINgm_categoryc ON c.id = g.category_id AND c.deleted = 0 LEFT JOINgm_typet ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;

    【问题分析】

    使用explain查看执行计划,结果如下:

    加载中...

    这条sql语句的问题其实还是比较明显的:

    查询了大量数据(包括数据条数、以及g.* ),然后使用临时表order by,但最终又只返回了20条数据。

    DBA观察到的IO高,是因为sql语句生成了一个巨大的临时表,内存放不下,于是全部拷贝到磁盘,导致IO飙升。

    【优化方案】

    优化的总体思路是拆分sql,将排序操作和查询所有信息的操作分开。

    第一条语句:查询符合条件的数据,只需要查询g.id即可

    SELECT DISTINCTg.idFROMgm_gameg LEFT JOINgm_cpcp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOINgm_categoryc ON c.id = g.category_id AND c.deleted = 0 LEFT JOINgm_typet ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;

    第二条语句:查询符合条件的详细数据,将第一条sql的结果使用in操作拼接到第二条的sql

    SELECT DISTINCTg.*,cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROMgm_gameg LEFT JOINgm_cpcp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOINgm_categoryc ON c.id = g.category_id AND c.deleted = 0 LEFT JOINgm_typet ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ;

    【实测效果】

    在SATA机器上测试,优化前大约需要50s,优化后第一条0.3s,第二条0.1s,优化后执行速度是原来的100倍以上,IO从100%降到不到1%

    在SSD机器上测试,优化前大约需要7s,优化后第一条0.3s,第二条0.1s,优化后执行速度是原来的10倍以上,IO从100%降到不到1%

    可以看出,优化前磁盘io是性能瓶颈,SSD的速度要比SATA明显要快,优化后磁盘不再是瓶颈,SSD和SATA性能没有差别。

    【理论分析】

    MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。

    临时表存储

    MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的MyISAM存储引擎;

    一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表导出到磁盘临时表;

    Linux平台上缺省是/tmp目录,/tmp目录小的系统要注意啦。

    使用临时表的场景

    1)ORDER BY子句和GROUP BY子句不同, 例如:ORDERY BY price GROUP BY name;

    2)在JOIN查询中,ORDER BY或者GROUP BY使用了不是第一个表的列 例如:SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

    3)ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)

    4)SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL,结果会很小,请直接使用内存临时表,不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下,我们没有必要使用这个选项,让MySQL服务器选择即可。

    直接使用磁盘临时表的场景

    1)表包含TEXT或者BLOB列;

    2)GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列;

    3)使用UNION或者UNION ALL时,SELECT子句中包含大于512字节的列;

    临时表相关配置

    tmp_table_size:指定系统创建的内存临时表最大大小;

    max_heap_table_size: 指定用户创建的内存表的最大大小;

    注意:最终的系统创建的内存临时表大小是取上述两个配置值的最小值。

    表的设计原则

    使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有:

    1)创建索引:在ORDER BY或者GROUP BY的列上创建索引;

    2)分拆很长的列:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。

    SQL优化

    如果表的设计已经确定,修改比较困难,那么也可以通过优化SQL语句来减少临时表的大小,以提升SQL执行效率。

    常见的优化SQL语句方法如下:

    1)拆分SQL语句

    临时表主要是用于排序和分组,很多业务都是要求排序后再取出详细的分页数据,这种情况下可以将排序和取出详细数据拆分成不同的SQL,以降低排序或分组时临时表的大小,提升排序和分组的效率,我们的案例就是采用这种方法。

    2)优化业务,去掉排序分组等操作

    有时候业务其实并不需要排序或分组,仅仅是为了好看或者阅读方便而进行了排序,例如数据导出、数据查询等操作,这种情况下去掉排序和分组对业务也没有多大影响。

    如何判断使用了临时表?

    使用explain查看执行计划,Extra列看到Using temporary就意味着使用了临时表。

    展开全文
  • 当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后在这些表运行查询。...

    当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后在这些表运行查询。

    创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字:

    CREATE TEMPORARY TABLE tmp_table (
    	name VARCHAR(10) NOT NULL,
    	value INTEGER NOT NULL
    )  

    临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

    DROP TABLE tmp_table

    如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在,临时表将有必要屏蔽(隐藏)非临时表tmp_table。

    如果你声明临时表是一个HEAP表,MySQL也允许你指定在内存中创建它:

    CREATE TEMPORARY TABLE tmp_table (   
    	name VARCHAR(10) NOT NULL,
    	value INTEGER NOT NULL
    ) TYPE = HEAP  

    因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。然而,HEAP表与一般的表有些不同,且有自身的限制。详见MySQL参考手册。

    正如前面的建议,你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引,临时表可能一点不快。

    1. 临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表:

    定义字段:

    CREATE TEMPORARY TABLE tmp_table (
    	name VARCHAR(10) NOT NULL,
    	value INTEGER NOT NULL 
    )

    2)直接将查询结果导入临时表

    CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

    2. 另外mysql也允许你在内存中直接创建临时表,因为是在内存中所有速度会很快,语法如下:

    CREATE TEMPORARY TABLE tmp_table (
    	name VARCHAR(10) NOT NULL,
    	value INTEGER NOT NULL
    ) TYPE = HEAP 

    3. 从上面的分析可以看出临时表的数据是会被清空的,你断开了连接就会被自动清空,但是你程序中不可能每发行一次sql就连接一次数据库吧(如果是这样的话,那就会出现你担心的问题,如果不是就没有问题),因为只有断开数据库连接才会被清空数据,在一个数据库连接里面发行多次sql的话系统是不会自动清空临时表数据的。

    展开全文
  • mysql临时表使用

    2020-04-27 10:39:57
    mysql使用temporary关键字可以创建出一个临时表。创建的这张表会在于服务器的会话终止时自动消失。 语法:create temporary table 表名 规则:每个会话只能看到自己创建的临时表,不同的会话可以创建相同表名称的...
  • 外部临时表 通过CREATE TEMPORARY TABLE 创建的临时表,这种临时...这种内部表对用户来说是不可见的,但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的
  • Mysql临时表

    2016-08-14 17:43:01
    当你创建临时表的时候,你可以使用temporary关键字。如:  create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)‘ 或  CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_...
  • 如果您使用的是旧版本的MySQL比3.23,可以不使用临时表,但可以使用堆表。 如前所述临时表将只持续只要的会话是存在的。如果运行一个PHP脚本中的代码,该临时表将被销毁时,会自动执行完脚本后。如果已连接到MySQL...
  • 出了这个方法再去找,会被mysql直接释放掉,所以再文档里面介绍的时候,只在当前连接可见,会有这几个字,再php里面我测试了一下,换个方法调用临时表名,失败报错,所以,我这里经常使用临时表的情况就是在报表,...
  • MySQL 临时表

    2021-07-05 23:28:30
    MySQL 临时表在我们需要保存一些...MySQL临时表只在当前连接可见,如果你使用PHP脚本来创建MySQL临时表,那每当PHP脚本执行完成后,该临时表也会自动销毁。 如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创
  • mysql临时表的产生

    2016-12-21 16:10:00
    MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的MyISAM存储引擎; 一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL...
  • JdbcTemplate 使用Mysql 临时表

    千次阅读 2012-05-27 10:35:08
    Mysql 临时表 是Session 级的 一个方法中若干个jdbcTemplate.execute 未必是唯一连接的 因此关键点在于创建唯一连接的JdbcTemplate SingleConnectionDataSource scds = null; try { scds = new ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 76,555
精华内容 30,622
关键字:

mysql临时表用法

mysql 订阅