精华内容
下载资源
问答
  • 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就意味着使用了临时表。

    展开全文
  • 1)MySQL临时表 2)dstat mysql 临时表监控插件开发

    临时表简介


    MySQL在执行SQL查询时可能会用到临时表,一般而言,用到临时表就意味着性能较低。MySQL临时表可分:内存临时表和磁盘临时表,磁盘临时表使用MyISAM存储,而内存临时表则使用Memory引擎。MySQL使用内存临时表来存放查询的中间结果集,如果中间结果集大于临时表的容量设定,又或者中间结果集含TEXT或BLOB列类型字段,则MySQL会把其转化为磁盘临时表。Linux平台缺省是/tmp,/tmp目录小的系统要注意啦。

    临时表特性


    下面列举几个内存临时表的特性

    1.表结构(.frm)在磁盘,数据在内存
    2.缺省使用哈希索引
    3.定长存储(BTW:即使是varchar也是定长)
    4.只支持表锁
    5.不支持TEXT和BLOB列类型

    相关参数


    1 大小参数
    MIN{tmp_table_size,max_heap_table_size}
    mysql> show global variables like '%table_size';
    +---------------------+----------+
    | Variable_name       | Value    |
    +---------------------+----------+
    | max_heap_table_size | 16777216 |
    | tmp_table_size      | 16777216 |
    +---------------------+----------+
    2 rows in set (0.00 sec)
    

    2 数量参数
    mysql> show global status like 'created_tmp%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Created_tmp_disk_tables | 7     |
    | Created_tmp_files       | 6     |
    | Created_tmp_tables      | 90    |
    +-------------------------+-------+
    3 rows in set (0.00 sec)

    通过ps_helper我们可以细化这些临时表到底是谁的临时表
    mysql> SELECT query, exec_count, memory_tmp_tables, disk_tmp_tables, avg_tmp_tables_per_query, tmp_tables_to_disk_pct FROM statements_with_temp_tables LIMIT 5;
    +-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+
    | query                                                             | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct |
    +-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+
    | SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  |          2 |                 4 |               2 |                        2 |                     50 |
    | SELECT IF ( ( `locate` ( ? , ` ... MPRESSED_SIZE` = ? ) , ? , ... |          2 |                 4 |               2 |                        2 |                     50 |
    | SELECT IF ( `isnull` ( `inform ... ` = `performance_schema` . ... |          2 |                 4 |               2 |                        2 |                     50 |
    | SELECT IF ( `isnull` ( `inform ... by_thread_by_event_name` . ... |          2 |                 4 |               2 |                        2 |                     50 |
    | SHOW FULL FIELDS FROM `stateme ... ` , `performance_schema` . ... |          2 |                 4 |               2 |                        2 |                     50 |
    +-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+
    5 rows in set (0.00 sec)
    


    优化临时表


    分2个阶段:系统设计初期和产品上线后
    在系统设计初期,优化却入点有:
    1.创建索引  <==对ORDER BY 或 GROUP BY的列上创建索引
    2.拆分表  <==大的列(如BLOB或TEXT)一般不会用作谓词,在表设计时可独立到另一张表
    而产品上线后,我们只能对业务或SQL进行优化
    1.拆分SQL <==临时表主要用于排序和分组,很多业务都是要求排序后再取出详细的数据,这种情况下可以把排序操作和查询所有信息的操作分开,以降低排序或分组时临时表的大小,提升排序或分组的效率
    2.优化业务,去掉排序分组等操作


    dstat MySQL 临时表监控插件开发


    ### Author: linwaterbin@gmail.com
    ### UPDATE: 2014-2-24
    ### FUNCTION: analyze mysql temp table use
    # init MySQL authority
    global mysql_user
    mysql_user = os.getenv('DSTAT_MYSQL_USER')
    global mysql_pwd
    mysql_pwd = os.getenv('DSTAT_MYSQL_PWD')
    global mysql_host
    mysql_host = os.getenv('DSTAT_MYSQL_HOST')
    global mysql_db
    mysql_db = os.getenv('DSTAT_MYSQL_DB')
    class dstat_plugin(dstat):
        """
        Plugin for MySQL 5 Temp Table Usage.
        """
        def __init__(self):
            self.name = 'mysql5 tmp usage'
            #self.format = ('d',12,50)
            self.nick = ('mem', 'disk','mem-disk-pct',)
            self.vars = ('memory_tmp_tables', 'disk_tmp_tables','avg_mem_to_disk_pct',)
            self.type = 's'
            self.width = 12 
            self.scale = 50
        def check(self): 
            global MySQLdb
            import MySQLdb
            try:
                self.db = MySQLdb.connect(user=mysql_user,passwd=mysql_pwd,host=mysql_host,db=mysql_db)
            except:
                raise Exception, 'Cannot interface with MySQL server'
    
        def extract(self):
            try:
                query="""select sum(memory_tmp_tables) as memory_tmp_tables,sum(disk_tmp_tables) as disk_tmp_tables,avg(tmp_tables_to_disk_pct) as avg_mem_to_disk_pct from statements_with_temp_tables;"""
                cur = self.db.cursor(MySQLdb.cursors.DictCursor)
                cur.execute(query)
                for record in cur.fetchall():
                      self.val['memory_tmp_tables'] =record['memory_tmp_tables']
                      self.val['disk_tmp_tables'] = record['disk_tmp_tables']
                      self.val['avg_mem_to_disk_pct'] = record['avg_mem_to_disk_pct']
    
                if step == op.delay:
                    self.set1.update(self.set2)
            except Exception, e:
                for name in self.vars:
                    self.val[name] = -1

    监控测试图如下:




    By DataHacker
    2014-2-26
    Good Luck!


    展开全文
  • MySQL临时表与内存表

    千次阅读 2019-01-13 12:03:18
    MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在MySQL 3.23版本中添加。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。如果你使用了其他MySQL客户端程序连接MySQL数据库...

    在MySQL中有三种虚拟表:临时表、内存表、视图。下面简单介绍一下临时表和内存表的使用。

    1、临时表

    MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在MySQL 3.23版本中添加。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然你也可以手动销毁。

    1.1 创建临时表

    在MySQL中创建临时表使用CREATE TEMPORARY TABLE语句,其语法格式如下:

    CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名
    (
    	...
    );

    示例:创建临时表。

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_user
    (
    	id INT NOT NULL DEFAULT 0,
    	name VARCHAR(10) NOT NULL
    );

    还可以通过复制表的方式来创建临时表。

    示例:通过复制表的方式来创建临时表。

    CREATE TEMPORARY TABLE temp_user
    AS SELECT * FROM tb_user;

    1.2 删除临时表

    默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。当然你也可以在当前MySQL会话中手动删除临时表。删除临时表与删除普通表的语句是一样的,使用 DROP TABLE语句。

    示例:删除临时表。

    DROP TABLE IF EXISTS temp_user;

    1.3 使用临时表的注意事项

    (1)临时表只在当前连接可见,当这个连接关闭的时候,会自动drop。比如打开mysql 就是一个连接会话。两个不同的连接可以使用相同名字的临时表,两个表之间不存在什么关系,如果临时表的名字和已经存在的磁盘表名字一样,那么临时表会暂时覆盖磁盘表。就是说,你select 查询,只会显示临时表里面的,不会显示磁盘表。

    (2)临时表的存储引擎:memor,myisam,merge,innodb,临时表不支持mysql cluster簇。

    (3)同一个查询语句,只能用一次临时表,就是说不能将表和自己做连接等。

    (4)重命名表,不能用rename 可以用alter table代替。

    (5)如果超出了临时表的容量,临时表会转换成磁盘表。

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

     

    2、内存表

    内存表的表结构建立在磁盘里面,数据放在内存里面;

    当MySQL断开当前连接后,临时表的表结构和表数据都没了,但内存表的表结构和表数据都存在;

    当MySQL服务重启之后,内存表的数据会丢失,但表结构依旧存。

    2.1 创建内存表

    创建内存表与创建普通表一样,使用CREATE TABLE语句,但需要将存储引擎设置为:ENGINE = MEMORY。其语法格式如下:

    CREATE TABLE [IF NOT EXISTS]  内存表名
    (
    	...
    ) ENGINE = MEMORY;

    示例:创建内存表。

    CREATE TABLE IF NOT EXISTS tmp_table (
    	id INT NOT NULL DEFAULT 0,
    	name VARCHAR(10) NOT NULL
    ) ENGINE = MEMORY; 

    2.2 删除内存表

    删除内存表与删除普通表的语句是一样的,使用 DROP TABLE语句。

    示例:删除内存表。

    DROP TABLE IF EXISTS tmp_table;

    2.3 使用内存表的注意事项

    (1)当MySQL服务重启之后,内存表的数据会丢失,表结构依旧存。

    (2)多个session,创建表的名字不能一样。

    (3)一个session创建会话后,对其他session也是可见的。

    (4)data目录下只有tmp_memory.frm,表结构放在磁盘上,数据放在内存中。

    (5)可以创建索引,删除索引,支持唯一索引。

    (6)不影响主备,主库上插入的数据,备库也可以查到。

    (7)show tables 语句可以查看得到表。

    (8)内存表不能包含BLOB或者TEXT列。

    (9)内存表支持AUTO_INCREMENT列。

     

    3、临时表与内存表区别

      临时表 内存表
    存储 表结构和数据都存储在内存中 表结构存储在磁盘中,表数据存储在内存中
    会话 单个会话独享的,是会话级别的 可以多个会话共享
    引擎 临时表默认,myisam 内存表默认,memory
    断开连接 表结构和表数据都没了 表结构和表数据都存在
    服务重启 表结构和表数据都没了 表结构存在,表数据不存在
    性能 由于表数据都是存放在内存中,所以相对来说,查询速度较快,但是数据的维护较为困难

     

    展开全文
  • MySQL临时表详细解释

    2020-06-04 17:08:44
    MySQL临时表详细解释 1.1 临时表简介 MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。临时表的作用仅限于...

    MySQL临时表详细解释

    1.1 临时表简介

    MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。临时表的作用仅限于本次会话,等连接关闭后重新打开连接临时表将不存在。

    1.2 临时表类型

    1.2.1 外部临时表

    通过create temporary table语句创建的临时表为外部临时表,在创建时可以手动指定临时表的存储引擎。

    create temporary table temp_table(
    	id int,
    	name varchar(10)
    ) ENGINE = InnoDB;
    insert into temp_table values (1,'1');
    
    select * from temp_table ;
    

    1.2.2 内部临时表

    通常在执行复杂SQL语句时,比如group bydistinctunion等语句,执行计划中如果包含Using temporary,那么MySQL内部将使用自动生成的临时表,以辅助SQL的执行。

    explain select username from userinfo group by username;
    
    explain select distinct age from userinfo ;
    
    explain select * from userinfo union select * from userinfo;
    

    在这里插入图片描述

    1.2.3 临时表说明

    1)临时表,顾名思义就是"临时"的表,不管是内部临时表还是外部临时表,都是临时的,我们查询不到;在服务器重启之后,所有的临时表将会被销毁。

    在这里插入图片描述

    不管是内部临时表还是外部临时表,我们都查询不到,但外部临时表可以查询到里面的数据。

    2)临时表是每个进程独享的,当前进程(客户端)创建的临时表,其他进程(客户端)是查不到临时表里面的数据的。

    • 再打开一个窗口,看是否能查询到我们刚刚那个窗口创建的临时表里面的数据:
      在这里插入图片描述

    发现查询不到其他客户端创建的临时表数据。

    1.3 临时表相关系统参数变量

    • 查看临时表相关系统变量:
    show variables like '%tmp%';
    

    在这里插入图片描述

    • tmpdir:临时表存储路径
    • tmp_table_size:内部临时表内存最大值,超过此值将转移到磁盘中存储。
    • max_heap_table_size:外部临时表内存最大值,超过此值将转移到磁盘中存储。
    • default_tmp_storage_engine:外部临时表默认采用的存储引擎。
    • internal_tmp_disk_storage_engine:内部临时表默认采用的存储引擎

    1.4 临时表相关状态参数

    show status like '%tmp%';
    

    在这里插入图片描述

    • Created_tmp_disk_tables:MySQL创建内部磁盘临时表次数。
    • Created_tmp_tables:MySQL创建内部临时表次数
    • Created_tmp_files:创建的临时表文件数(系统内部维护,不用我们管)。
    展开全文
  • mysql临时表

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

    千次阅读 2018-11-12 11:14:27
    MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间. 实例: 创建一个临时表 CREATE TEMPORARY TABLE SalesSummary ( product_name ...
  • MySQL 临时表与内存表

    千次阅读 2017-05-19 15:06:59
    MySQL临时表与内存表总结 1.临时表:表建在内存里,数据在内存里。 2.内存表:表建在磁盘里,数据在内存里 。 重启MySQL 数据库后,内存中的数据全部丢失。内存表的功能有部分的限制,有些属性不能像正常表一样...
  • MySQL临时表的功能

    千次阅读 2018-06-06 19:55:56
    摘要:MySQL临时表MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。临时表在MySQL3.23… MySQL 临时表在我们需要保存一些临时数据...
  • mysql 临时表使用

    千次阅读 2018-05-24 18:43:14
    语法 : create temporary table temptable_name 1.和实体表一样有主键、索引 ...4.临时表分内存临时表和磁盘临时表,当临时表存储空间大于 ‘tmp_table_size’ 值时,会在磁盘上创建临时表,从而效率下降...
  • mysql 临时表和内存表创建 查询 删除以及注意事项临时表和内存表的ENGINE 不同,临时表默认的是MyISAM,而内存表是MEMORY ,临时表只对当前会话可见,连接断开时,自动删除! mysql教程 临时表和内存表创建 ...
  • mysql临时表插入数据

    千次阅读 2020-01-06 10:48:06
    临时表 1.在数据库断开连接的时候会自动删除临时表。 create temporary table IF NOT EXISTS client.getstucou(  type_id int )Engine=InnoDB default charset utf8; 注意:在存储过程中创建了临时表的...
  • mysql临时表简介

    千次阅读 2019-06-21 13:41:48
    MySQL临时表 首先,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。 创建临时表的语法与创建表语法类似,不同之处...
  • sqlserver临时表与永久表类似,但不再使用时会自动删除。 sqlserver临时表特点如下: 创建表时在表名前加"前缀"符号。 临时表利用虚拟内存,减少硬盘I/O次数,提高系统效率。 sqlserver临时表...
  • Mysql 临时表详解(temporary table)

    千次阅读 2020-11-18 19:36:48
    1. 作用临时表用来保存一些 '临时数据' 2. 注意: (1) 临时表只在 '当前连接' 可见,当关闭连接时,Mysql 会 '自动删除表数据及表结构' (2) 临时表 和 普通表 用法一样,用关键字 'temporary' 予以区别 2 实例...
  • MySQL临时表的使用总结

    千次阅读 2018-09-27 15:35:59
    MySQL在很多情况下都会用到临时表,总结一下什么时候会用到临时表:  什么是临时表MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生...
  • 一、创建临时表可以将查询结果寄存。报表制作的查询sql中可以用到。(1)关于寄存方式,mysql不支持:select * into tmp from maintenanceprocess(2)可以使用:create table tmp (select ...)举例:#单个工位检修...
  • mysql临时表优化

    千次阅读 2013-08-28 15:00:41
    [MySQL优化案例]系列 -- 频繁创建临时表 By yejr on 17 二月 2009 作/译者:叶金荣(imysql#imysql.com>),来源:http://imysql.com,欢迎转载。 引言:某客户新上线一个项目,利用存储过程...
  • mysql 内存表和临时表学习

    千次阅读 2018-02-27 22:15:37
    临时表 内存表 视图 临时表 简介: 临时表是建立在系统临时文件夹中的表。临时表的数据和表结构都存储在内存之中,退出的时候所占的空间会被释放 创建临时表 create temporary table tmp_table( name ...
  • mysql 临时表和视图

    千次阅读 2017-04-26 10:50:27
    视图不会随着会话结束而消失临时表跟实体表是两个互不相干的两个表,因此不存在操作临时表会影响到实体表的情况。 会话结束临时表自动释放应用场景: 视图 应用场景1:保密工作,比如有一个员工工资表,如果你只...
  • mysql临时表的创建

    千次阅读 2014-03-28 20:20:12
    1、临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表: 1)定义字段 CREATE TEMPORARY TABLE tmp_table ( name VARCHAR(10) NOT NULL, value INTEGER NOT NULL ) ...
  • mysql 临时表和内存表

    千次阅读 2015-06-02 23:35:36
    内存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:...
  • mysql临时表在工作中的使用

    千次阅读 2016-09-28 18:15:21
    工作中业务需要用到mysql临时表,临时表是会话级别的,即使多个session创建的表名一样,都相互不影响,会话结束,表格自动删除。业务需求是:选择时间从多张表中查询软件的Top20下载和对应的安装数汇总,数据是动态...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 126,189
精华内容 50,475
关键字:

mysql临时表的作用

mysql 订阅