站長資訊網
        最全最豐富的資訊網站

        MySQL臨時表深入理解

        MySQL臨時表深入理解

        概述

        MySQL中臨時表主要有兩類,包括外部臨時表和內部臨時表。外部臨時表是通過語句create temporary table…創建的臨時表,臨時表只在本會話有效,會話斷開后,臨時表數據會自動清理。內部臨時表主要有兩類,一類是information_schema中臨時表,另一類是會話執行查詢時,如果執行計劃中包含有“Using temporary”時,會產生臨時表。內部臨時表與外部臨時表的一個區別在于,我們看不到內部臨時表的表結構定義文件frm。而外部臨時表的表定義文件frm,一般是以#sql{進程id}_{線程id}_序列號組成,因此不同會話可以創建同名的臨時表。

        臨時表

        臨時表與普通表的主要區別在于是否在實例,會話,或語句結束后,自動清理數據。比如,內部臨時表,我們在一個查詢中,如果要存儲中間結果集,而查詢結束后,臨時表就會自動回收,不會影響用戶表結構和數據。另外就是,不同會話的臨時表可以重名,所有多個會話執行查詢時,如果要使用臨時表,不會有重名的擔憂。5.7引入了臨時表空間后,所有臨時表都存儲在臨時表空間(非壓縮)中,臨時表空間的數據可以復用。臨時表并非只支持Innodb引擎,還支持myisam引擎,memory引擎等。因此,臨時表我們看不到實體(idb文件),但其實不一定是內存表,也可能存儲在臨時表空間中。

        臨時表 VS 內存表

        臨時表既可以innodb引擎表,也可以是memory引擎表。這里所謂的內存表,是說memory引擎表,通過建表語句create table …engine=memory,數據全部在內存,表結構通過frm管理,同樣的內部的memory引擎表,也是看不到frm文件中,甚至看不到information_schema在磁盤上的目錄。在MySQL內部,information_schema里面的臨時表就包含兩類:innodb引擎的臨時表和memory引擎的臨時表。比如TABLES表屬于memory臨時表,而columns,processlist,屬于innodb引擎臨時表。內存表所有數據都在內存中,在內存中數據結構是一個數組(堆表),所有數據操作都在內存中完成,對于小數據量場景,速度比較快(不涉及物理IO操作)。但內存畢竟是有限的資源,因此,如果數據量比較大,則不適合用內存表,而是選擇用磁盤臨時表(innodb引擎),這種臨時表采用B+樹存儲結構(innodb引擎),innodb的bufferpool資源是共享的,臨時表的數據可能會對bufferpool的熱數據有一定的影響,另外,操作可能涉及到物理IO。memory引擎表實際上也是可以創建索引的,包括Btree索引和Hash索引,所以查詢速度很快,主要缺陷是內存資源有限。

        使用臨時表的場景

        前面提到執行計劃中包含有“Using temporary”時,會使用臨時表,這里列兩個主要的場景。

        測試表結構如下:

        mysql> show create table t1_normalG *************************** 1. row ***************************        Table: t1_normal Create Table: CREATE TABLE `t1_normal` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `c1` int(11) DEFAULT NULL,   `c2` int(11) DEFAULT NULL,   `c3` int(11) DEFAULT NULL,   `c4` int(11) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=770023 DEFAULT CHARSET=utf8

        場景1:union

        mysql> explain select * from t1_normal union select * from t1_normal;  +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |  +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+  | 1 | PRIMARY | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | 2 | UNION | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

        union操作的含義是,取兩個子查詢結果的并集,重復的數據只保留一行,通過建立一個帶主鍵的臨時表,就可以解決“去重”問題,通過臨時表存儲最終的結果集,所以能看到執行計劃中Extra這一項里面有“Using temporary”。與union相關的一個操作是union all,后者也是將兩個子查詢結果合并,但不解決重復問題。所以對于union all,沒有“去重”的含義,因此也就不需要臨時表了。

        mysql> explain select * from t1_normal  union  all select * from t1_normal; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+ | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+ |  1 | PRIMARY     | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  | |  2 | UNION       | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

        場景2:group by

        mysql> explain select c1,count(*) as count from t1_normal group by c1; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+ | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                           | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+ |  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary; Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+

        group by的含義是按指定列分組,并默認按照指定列有序。上面的SQL語句含義是將t1_normal中的數據按c1列的值分組,統計每種c1列值的記錄數目。從執行計劃中我們看到了"Using temporary;Using filesort",對于group by而言,我們首先需要統計每個值出現的數目,這就需要借助臨時表來快速定位,如果不存在,則插入一條記錄,如果存在,并累加計數,所以看到了"Using temporary";然后又因為group by隱含了排序含義,所以還需要按照c1列進行對記錄排序,所以看到了"Using filesort"。

        1).消除filesort

        實際上,group by也可以顯示消除“排序含義”。

        mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ |  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

        可以看到,語句中加上“order by null”后,執行計劃中,不再出現“Using filesort”。

        2).消除臨時表

        mysql> explain select SQL_BIG_RESULT c1,count(*) as count from t1_normal group by c1; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

        可以看到執行計劃中已經沒有了“Using temporary”,所以group by并非一定依賴臨時表,臨時表在group by中的作用主要是“去重”。所以,實際上有另外一種方式,不使用臨時表,直接利用sort_buffer排序(sort_buffer不夠時,進行文件排序,具體而言是每一個有序數組作為一個單獨文件,然后進行外排歸并),然后再掃描得到聚合后的結果集。

        3).SQL_BIG_RESULT

        同時我們語句中用到了“SQL_BIG_RESULT”這個hint,正是因為這個hint導致了我們沒有使用臨時表,先說說SQL_BIG_RESULT和SQL_SMALL_RESULT的含義。

        SQL_SMALL_RESULT:顯示指定用內存表(memory引擎)

        SQL_BIG_RESULT:顯示指定用磁盤臨時表(myisam引擎或innodb引擎)

        兩者區別在于,使用磁盤臨時表可以借助主鍵做去重排序,適合大數據量;使用內存表寫入更快,然后在內存中排序,適合小數據量。下面是從MySQL手冊中摘錄的說明。

        SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively.

        For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements.

        For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting.

        This should not normally be needed.

        回到問題本身,這里MySQL優化器根據hint知道需要使用磁盤臨時表,而最終直接選擇了數組存儲+文件排序這種更輕量的方式。

        如何避免使用臨時表

        通常的SQL優化方式是讓group by 的列建立索引,那么執行group by時,直接按索引掃描該列,并統計即可,也就不需要temporary和filesort了。

        mysql> alter table t1_normal add index idx_c1(c1); Query OK, 0 rows affected (1 min 23.82 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null; +----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ | id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       | +----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+ |  1 | SIMPLE      | t1_normal | NULL       | index | idx_c1        | idx_c1 | 5       | NULL | 523848 |   100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

        相關參數與狀態監控

        1).參數說明

        max_heap_table_size

        This variable sets the maximum size to which user-created MEMORY tables are permitted to grow,The value of the variable is used to calculate MEMORY table MAX_ROWS values.

        這個參數主要針對用戶創建的MEMORY表,限制內存表最大空間大小,注意不是記錄數目,與單條記錄的長度有關。如果超出閥值,則報錯。ERROR 1114 (HY000): The table 'xxx' is full

        tmp_table_size

        The maximum size of internal in-memory temporary tables.

        對于用戶手工創建的內存表,只有參數max_heap_table_size起作用;對于內部產生的內存表,則參數max_heap_table_size和tmp_table_size同時起作用。對于內部產生的內存表(比如union,group by等產生的臨時表),先是采用內存表(memory表),然后超過設置的閥值(max_heap_table_size,tmp_table_size)就會轉為磁盤表,使用innodb引擎或者myisam引擎,通過參數internal_tmp_disk_storage_engine指定。

        tmpdir

        如果內存臨時表超出了限制,MySQL就會自動地把它轉化為基于磁盤的MyISAM表,存儲在指定的tmpdir目錄下

        2.狀態監控

        Created_tmp_tables,內部臨時表數目

        Created_tmp_disk_tables,磁盤臨時表數目

        3.information_schema相關

        mysql> create temporary table t1_tmp(id int primary key,c1 int); Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO; +----------+---------------+--------+-------+----------------------+---------------+ | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | +----------+---------------+--------+-------+----------------------+---------------+ | 10063 | #sql693d_29_0 | 5 | 45 | FALSE | FALSE | +----------+---------------+--------+-------+----------------------+---------------+

        總結

        本文詳細介紹了MySQL中臨時表的核心特征,按需創建并且自動銷毀,對于純內存的數據特別適合,但為了避免內存不可控,實際上不僅僅有內存臨時表,還有磁盤臨時表。臨時表和內存表本沒有直接關聯,因為臨時表既可以是memory引擎,又可以innodb引擎將兩者聯系到了一起,實際上不同類別的臨時表也是用到了不同引擎的優勢。臨時表使用的典型場景是union和group by。為了消除臨時表,我們需要對group by列添加索引,或者對于大結果集,使用SQL_BIG_RESULT等。最后本文介紹了臨時表相關的參數和狀態變量,以及information_schema中的臨時表信息。

        推薦教程:《MySQL教程》

        贊(0)
        分享到: 更多 (0)
        網站地圖   滬ICP備18035694號-2    滬公網安備31011702889846號
        主站蜘蛛池模板: 人妻VA精品VA欧美VA| 久久精品国产99久久香蕉| 亚洲综合精品香蕉久久网97| 亚洲国产精品无码AAA片| 国产欧美日韩综合精品二区| 99精品免费视品| 亚洲国产精品无码久久久不卡 | 国产精品水嫩水嫩| 麻豆国内精品欧美在线| 99久久精品国产一区二区| 国产福利视精品永久免费 | 国产亚洲美女精品久久久久狼 | 中文字幕无码精品三级在线电影 | 亚洲无删减国产精品一区| 国产精品免费大片| 亚洲日韩精品一区二区三区无码| 久久精品成人| 精品人妻少妇一区二区三区在线 | 亚洲精品视频免费| 精品久久久久久无码中文字幕一区| 久久久久亚洲精品无码网址 | 久久精品国产第一区二区三区| 国产精品99久久久久久人| 无码囯产精品一区二区免费| 亚洲国产精品无码久久九九| 黑巨人与欧美精品一区 | 精品露脸国产偷人在视频| 99精品人妻少妇一区二区| 国产91精品一区二区麻豆网站 | 午夜一级日韩精品制服诱惑我们这边| 国产在AJ精品| 精品视频在线观看你懂的一区| 2022精品国偷自产免费观看| 久久99国产精品久久| 国产在线精品网址你懂的| 国产精品1024香蕉在线观看| 高清免费久久午夜精品| 久久国产乱子伦精品免费强| 日本人精品video黑人| 亚洲国产精品婷婷久久| 2021国产三级精品三级在专区|