您好,登錄后才能下訂單哦!
這篇文章主要介紹了mysql臨時(shí)表怎么使用的相關(guān)知識(shí),內(nèi)容詳細(xì)易懂,操作簡(jiǎn)單快捷,具有一定借鑒價(jià)值,相信大家閱讀完這篇mysql臨時(shí)表怎么使用文章都會(huì)有所收獲,下面我們一起來(lái)看看吧。
在mysql中,臨時(shí)表指的是臨時(shí)使用的一張表,是用于存儲(chǔ)一些中間結(jié)果集的表;臨時(shí)表只在當(dāng)前連接可見(jiàn),當(dāng)關(guān)閉連接時(shí),Mysql會(huì)自動(dòng)刪除表并釋放所有空間。
本教程操作環(huán)境:windows7系統(tǒng)、mysql8版本、Dell G3電腦。
臨時(shí)表,是臨時(shí)使用的一張表。
臨時(shí)表是MySQL用于存儲(chǔ)一些中間結(jié)果集的表,臨時(shí)表只在當(dāng)前連接可見(jiàn),當(dāng)關(guān)閉連接時(shí),Mysql會(huì)自動(dòng)刪除表并釋放所有空間。
使用其他MySQL客戶端程序連接MySQL數(shù)據(jù)庫(kù)服務(wù)器來(lái)創(chuàng)建臨時(shí)表,那么只有在關(guān)閉客戶端程序時(shí)才會(huì)銷毀臨時(shí)表,當(dāng)然也可以手動(dòng)刪除。
注:臨時(shí)表在MySQL 3.23版本中添加,如果你的MySQL版本低于 3.23版本就無(wú)法使用MySQL的臨時(shí)表。不過(guò)現(xiàn)在一般很少有再使用這么低版本的MySQL數(shù)據(jù)庫(kù)服務(wù)了
MySQL中的兩種臨時(shí)表
外部臨時(shí)表
通過(guò)CREATE TEMPORARY TABLE 創(chuàng)建的臨時(shí)表,這種臨時(shí)表稱為外部臨時(shí)表。這種臨時(shí)表只對(duì)當(dāng)前用戶可見(jiàn),當(dāng)前會(huì)話結(jié)束的時(shí)候,該臨時(shí)表會(huì)自動(dòng)關(guān)閉。這種臨時(shí)表的命名與非臨時(shí)表可以同名(同名后非臨時(shí)表將對(duì)當(dāng)前會(huì)話不可見(jiàn),直到臨時(shí)表被刪除)。
內(nèi)部臨時(shí)表
內(nèi)部臨時(shí)表是一種特殊輕量級(jí)的臨時(shí)表,用來(lái)進(jìn)行性能優(yōu)化。這種臨時(shí)表會(huì)被MySQL自動(dòng)創(chuàng)建并用來(lái)存儲(chǔ)某些操作的中間結(jié)果。這些操作可能包括在優(yōu)化階段或者執(zhí)行階段。這種內(nèi)部表對(duì)用戶來(lái)說(shuō)是不可見(jiàn)的,但是通過(guò)EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了內(nèi)部臨時(shí)表用來(lái)幫助完成某個(gè)操作。內(nèi)部臨時(shí)表在SQL語(yǔ)句的優(yōu)化過(guò)程中扮演著非常重要的角色, MySQL中的很多操作都要依賴于內(nèi)部臨時(shí)表來(lái)進(jìn)行優(yōu)化。但是使用內(nèi)部臨時(shí)表需要?jiǎng)?chuàng)建表以及中間數(shù)據(jù)的存取代價(jià),所以用戶在寫SQL語(yǔ)句的時(shí)候應(yīng)該盡量的去避免使用臨時(shí)表。
內(nèi)部臨時(shí)表有兩種類型:
一種是HEAP臨時(shí)表,這種臨時(shí)表的所有數(shù)據(jù)都會(huì)存在內(nèi)存中,對(duì)于這種表的操作不需要IO操作。
另一種是OnDisk臨時(shí)表,顧名思義,這種臨時(shí)表會(huì)將數(shù)據(jù)存儲(chǔ)在磁盤上。OnDisk臨時(shí)表用來(lái)處理中間結(jié)果比較大的操作。
如果HEAP臨時(shí)表存儲(chǔ)的數(shù)據(jù)大于MAX_HEAP_TABLE_SIZE,HEAP臨時(shí)表將會(huì)被自動(dòng)轉(zhuǎn)換成OnDisk臨時(shí)表。
OnDisk臨時(shí)表在5.7中可以通過(guò)INTERNAL_TMP_DISK_STORAGE_ENGINE系統(tǒng)變量選擇使用MyISAM引擎或者InnoDB引擎。
外部臨時(shí)表的常見(jiàn)用法
外部臨時(shí)表是通過(guò)CREATE TEMPORARY TABLE及DROP TABLE來(lái)操作的,但是SHOW TABLES命令顯示數(shù)據(jù)表列表時(shí),你將無(wú)法看到自己創(chuàng)建的臨時(shí)表的。并且在退出當(dāng)前會(huì)話后,臨時(shí)表就會(huì)被自動(dòng)銷毀。當(dāng)然也可以手動(dòng)(DROP TABLE)銷毀。
1、引擎類型:只能是:memory(heap)、myisam、merge、innodb ,不支持mysql cluster(簇)。
2、外部臨時(shí)表使用時(shí)注意幾點(diǎn):
1)、自己所用的數(shù)據(jù)庫(kù)賬號(hào)要有建立臨時(shí)表的權(quán)限;
2)、在同一條sql中,不能關(guān)聯(lián)2次相同的臨時(shí)表,不然,就會(huì)報(bào)如下錯(cuò)誤;
mysql> select * from temp_table, temp_table as t2; error 1137: can't reopen table: 'temp_table'
3)、臨時(shí)表在建立連接時(shí)可見(jiàn),關(guān)閉時(shí)會(huì)清除空間,刪除臨時(shí)表;
4)、show tables 不會(huì)列出臨時(shí)表;
5)、不能使用rename重命名臨時(shí)表。但是,你可以alter table代替:只能使用alter table old_tp_table_name rename new_tp_table_name;
6)、影響使用replication功能;
7)、如果你為一個(gè)表聲明了別名,當(dāng)你指向這個(gè)表的時(shí)候,就必須使用這個(gè)別名。見(jiàn)《MySQL 多表關(guān)聯(lián)更新及刪除》
示例:
mysql> CREATE TEMPORARY TABLE SalesSummary ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SalesSummary -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); mysql> SELECT * FROM SalesSummary; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber | 100.25 | 90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE SalesSummary; mysql> SELECT * FROM SalesSummary; ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist
3、mybatis中臨時(shí)表操作
<update id="createTempTable"> CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT * FROM settlement_temp WHERE settle_date=#{settleDate} AND LENGTH(operator) IN(16,32) AND pay_status IN ('01','06') ORDER BY settle_date,merchant_no </update>
<!-- 4、刪除臨時(shí)表 --> <update id="dropTempTable"> DROP TEMPORARY TABLE IF EXISTS settlement_temp; </update>
內(nèi)部臨時(shí)表的常見(jiàn)用法
如果用戶在書(shū)寫SQL語(yǔ)句的時(shí)候能夠盡量少的使用內(nèi)部臨時(shí)表進(jìn)行查詢優(yōu)化,將有效的提高查詢執(zhí)行的效率。
首先我們定義一個(gè)表t1,
CREATE TABLE t1( a int, b int); INSERT INTO t1 VALUES(1,2),(3,4);
下面所有的操作都是基于表t1進(jìn)行舉例的。
在SQL語(yǔ)句中使用SQL_BUFFER_RESULT hint
SQL_BUFFER_RESULT主要用來(lái)讓MySQL盡早的釋放表上的鎖。因?yàn)槿绻麛?shù)據(jù)量很大的話,需要較長(zhǎng)時(shí)間將數(shù)據(jù)發(fā)送到客戶端,通過(guò)將數(shù)據(jù)緩沖到臨時(shí)表中可以有效的減少讀鎖對(duì)表的占用時(shí)間。SQL_BUFFER_RESULT見(jiàn)《mysql查詢優(yōu)化之三:查詢優(yōu)化器提示(hint)》
例如:
mysql> explain format=json select SQL_BUFFER_RESULT * from t1; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2.00" }, "buffer_result": { "using_temporary_table": true, "table": { "table_name": "t1", "access_type": "ALL", ...
如果SQL語(yǔ)句中包含了DERIVED_TABLE。
在5.7中,由于采用了新的優(yōu)化方式,我們需要使用 set optimizer_switch=’derived_merge=off’來(lái)禁止derived table合并到外層的Query中。
例如:
mysql> explain format=json select * from (select * from t1) as tt; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2.40" }, "table": { "table_name": "tt", "access_type": "ALL", ... "materialized_from_subquery": { "using_temporary_table": true, ...
如果我們查詢系統(tǒng)表的話,系統(tǒng)表的數(shù)據(jù)將被存儲(chǔ)到內(nèi)部臨時(shí)表中。
我們當(dāng)前不能使用EXPLAIN來(lái)查看是否讀取系統(tǒng)表數(shù)據(jù)需要利用到內(nèi)部臨時(shí)表,但是可以通過(guò)SHOW STATUS來(lái)查看是否利用到了內(nèi)部臨時(shí)表。
例如:
mysql> select * from information_schema.character_sets; mysql> show status like 'CREATE%';
如果DISTINCT語(yǔ)句沒(méi)有被優(yōu)化掉,即DISTINCT語(yǔ)句被優(yōu)化轉(zhuǎn)換為GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 內(nèi)部臨時(shí)表將會(huì)被使用。
mysql> explain format=json select distinct a from t1; EXPLAIN { { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.60" }, "duplicates_removal": { "using_temporary_table": true, ...
如果查詢帶有ORDER BY語(yǔ)句,并且不能被優(yōu)化掉。下面幾種情況會(huì)利用到內(nèi)部臨時(shí)表緩存中間數(shù)據(jù),然后對(duì)中間數(shù)據(jù)進(jìn)行排序。
1)如果連接表使用BNL(Batched Nestloop)/BKA(Batched Key Access)
例如:
1))BNL默認(rèn)是打開(kāi)的
mysql> explain format=json select * from t1, t1 as t2 order by t1.a; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "22.00" }, "ordering_operation": { "using_temporary_table": true, ...
2))關(guān)掉BNL后,ORDER BY將直接使用filesort。
mysql> set optimizer_switch='block_nested_loop=off'; Query OK, 0 rows affected (0.00 sec) mysql> explain format=json select * from t1, t1 as t2 order by t1.a; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "25.00" }, "ordering_operation": { "using_filesort": true, ...
2)ORDER BY的列不屬于執(zhí)行計(jì)劃中第一個(gè)連接表的列。
例如:
mysql> explain format=json select * from t as t1, t as t2 order by t2.a; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "25.00" }, "ordering_operation": { "using_temporary_table": true, ...
3)如果ORDER BY的表達(dá)式是個(gè)復(fù)雜表達(dá)式。
那么什么樣的ORDER BY表達(dá)式,MySQL認(rèn)為是復(fù)雜表達(dá)式呢?
1))如果排序表達(dá)式是SP或者UDF。
例如:
drop function if exists func1; delimiter | create function func1(x int) returns int deterministic begin declare z1, z2 int; set z1 = x; set z2 = z1+2; return z2; end| delimiter ; explain format=json select * from t1 order by func1(a); { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2.20" }, "ordering_operation": { "using_temporary_table": true, ...
2))ORDER BY的列包含聚集函數(shù)
為了簡(jiǎn)化執(zhí)行計(jì)劃,我們利用INDEX來(lái)優(yōu)化GROUP BY語(yǔ)句。
例如:
create index idx1 on t1(a); explain format=json SELECt a FROM t1 group by a order by sum(a); | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.20" }, "ordering_operation": { "using_temporary_table": true, "using_filesort": true, "grouping_operation": { "using_filesort": false, ... drop index idx1 on t1;
3))ORDER BY的列中包含有SCALAR SUBQUERY,當(dāng)然該SCALAR SUBQUERY沒(méi)有被優(yōu)化掉。
例如:
explain format=json select (select rand() from t1 limit 1) as a from t1 order by a; | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.20" }, "ordering_operation": { "using_temporary_table": true, "using_filesort": true, ...
4) 如果查詢既帶有ORDER BY同時(shí)也有GROUP BY語(yǔ)句,但是兩個(gè)語(yǔ)句使用的列不相同。
注意: 如果是5.7,我們需要將sql_mode設(shè)置為非only_full_group_by模式,否則會(huì)報(bào)錯(cuò)。
同樣為了簡(jiǎn)化執(zhí)行計(jì)劃,我們利用INDEX來(lái)優(yōu)化GROUP BY語(yǔ)句。
例如:
set sql_mode=''; create index idx1 on t1(b); explain format=json select t1.a from t1 group by t1.b order by 1; | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.40" }, "ordering_operation": { "using_temporary_table": true, "using_filesort": true, "grouping_operation": { "using_filesort": false, ... drop index idx1 on t1;
如果查詢帶有GROUP BY語(yǔ)句,并且不能被優(yōu)化掉。下面幾種情況會(huì)利用到內(nèi)部臨時(shí)表緩存中間數(shù)據(jù),然后對(duì)中間數(shù)據(jù)進(jìn)行GROUP BY。
1)如果連接表使用BNL(Batched Nestloop)/BKA(Batched Key Access)。
例如:
explain format=json select t2.a from t1, t1 as t2 group by t1.a; | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "8.20" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "4.00" ...
2) 如果GROUP BY的列不屬于執(zhí)行計(jì)劃中的第一個(gè)連接表。
例如:
explain format=json select t2.a from t1, t1 as t2 group by t2.a; | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "8.20" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "nested_loop": [ ...
3) 如果GROUP BY語(yǔ)句使用的列與ORDER BY語(yǔ)句使用的列不同。
例如:
set sql_mode=''; explain format=json select t1.a from t1 group by t1.b order by t1.a; | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.40" }, "ordering_operation": { "using_filesort": true, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, ...
4) 如果GROUP BY帶有ROLLUP并且是基于多表外連接。
例如:
explain format=json select sum(t1.a) from t1 left join t1 as t2 on true group by t1.a with rollup; | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "7.20" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "4.00" }, ...
5) 如果GROUP BY語(yǔ)句使用的列來(lái)自于SCALAR SUBQUERY,并且沒(méi)有被優(yōu)化掉。
例如:
explain format=json select (select avg(a) from t1) as a from t1 group by a; | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3.40" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "2.00" }, ...
IN表達(dá)式轉(zhuǎn)換為semi-join進(jìn)行優(yōu)化
1) 如果semi-join執(zhí)行方式為Materialization
例如:
set optimizer_switch='firstmatch=off,duplicateweedout=off'; explain format=json select * from t1 where a in (select b from t1); | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.60" }, "nested_loop": [ { "rows_examined_per_scan": 1, "materialized_from_subquery": { "using_temporary_table": true, "query_block": { "table": { "table_name": "t1", "access_type": "ALL", ...
2) 如果semi-join執(zhí)行方式為Duplicate Weedout
例如:
set optimizer_switch='firstmatch=off'; explain format=json select * from t1 where a in (select b from t1); | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "4.80" }, "duplicates_removal": { "using_temporary_table": true, "nested_loop": [ { ...
如果查詢語(yǔ)句帶有UNION,MySQL將利用內(nèi)部臨時(shí)表幫助UNION操作消除重復(fù)。
例如:
explain format=json select * from t1 union select * from t1; | { "query_block": { "union_result": { "using_temporary_table": true, "table_name": "<union1,2>", ...
如果查詢語(yǔ)句使用多表更新。
這里Explain不能看到內(nèi)部臨時(shí)表被利用,所以需要查看status。
例如:
update t1, t1 as t2 set t1.a=3; show status like 'CREATE%';
如果聚集函數(shù)中包含如下函數(shù),內(nèi)部臨時(shí)表也會(huì)被利用。
1) count(distinct *) 例如: explain format=json select count(distinct a) from t1; 2) group_concat 例如: explain format=json select group_concat(b) from t1;
總之,上面列出了10種情況,MySQL將利用內(nèi)部臨時(shí)表進(jìn)行中間結(jié)果緩存,如果數(shù)據(jù)量比較大的話,內(nèi)部臨時(shí)表將會(huì)把數(shù)據(jù)存儲(chǔ)在磁盤上,這樣顯然會(huì)對(duì)性能有所影響。為了盡可能的減少性能損失,我們需要盡量避免上述情況的出現(xiàn)。
MySQL在以下幾種情況會(huì)創(chuàng)建臨時(shí)表:
1、UNION查詢; 2、用到TEMPTABLE算法或者是UNION查詢中的視圖; 3、ORDER BY和GROUP BY的子句不一樣時(shí); 4、表連接中,ORDER BY的列不是驅(qū)動(dòng)表中的; 5、DISTINCT查詢并且加上ORDER BY時(shí); 6、SQL中用到SQL_SMALL_RESULT選項(xiàng)時(shí); 7、FROM中的子查詢; 8、子查詢或者semi-join時(shí)創(chuàng)建的表;
EXPLAIN 查看執(zhí)行計(jì)劃結(jié)果的 Extra 列中,如果包含 Using Temporary 就表示會(huì)用到臨時(shí)表。
當(dāng)然了,如果臨時(shí)表中需要存儲(chǔ)的數(shù)據(jù)量超過(guò)了上限( tmp-table-size 或 max-heap-table-size 中取其大者),這時(shí)候就需要生成基于磁盤的臨時(shí)表了。
在以下幾種情況下,會(huì)創(chuàng)建磁盤臨時(shí)表:
1、數(shù)據(jù)表中包含BLOB/TEXT列; 2、在 GROUP BY 或者 DSTINCT 的列中有超過(guò) 512字符 的字符類型列(或者超過(guò) 512字節(jié)的 二進(jìn)制類型列,在5.6.15之前只管是否超過(guò)512字節(jié)); 3、在SELECT、UNION、UNION ALL查詢中,存在最大長(zhǎng)度超過(guò)512的列(對(duì)于字符串類型是512個(gè)字符,對(duì)于二進(jìn)制類型則是512字節(jié)); 4、執(zhí)行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令,因?yàn)樗鼈兊膱?zhí)行結(jié)果用到了BLOB列類型。
從5.7.5開(kāi)始,新增一個(gè)系統(tǒng)選項(xiàng) internal_tmp_disk_storage_engine 可定義磁盤臨時(shí)表的引擎類型為 InnoDB,而在這以前,只能使用 MyISAM。而在5.6.3以后新增的系統(tǒng)選項(xiàng) default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 創(chuàng)建的臨時(shí)表的引擎類型,在以前默認(rèn)是MEMORY,不要把這二者混淆了。
見(jiàn)下例:
mysql> set default_tmp_storage_engine = "InnoDB"; -rw-rw---- 1 mysql mysql 8558 Jul 7 15:22 #sql4b0e_10_0.frm -- InnoDB引擎的臨時(shí)表 -rw-rw---- 1 mysql mysql 98304 Jul 7 15:22 #sql4b0e_10_0.ibd -rw-rw---- 1 mysql mysql 8558 Jul 7 15:25 #sql4b0e_10_2.frm mysql> set default_tmp_storage_engine = "MyISAM"; -rw-rw---- 1 mysql mysql 0 Jul 7 15:25 #sql4b0e_10_2.MYD -- MyISAM引擎的臨時(shí)表 -rw-rw---- 1 mysql mysql 1024 Jul 7 15:25 #sql4b0e_10_2.MYI mysql> set default_tmp_storage_engine = "MEMORY"; -rw-rw---- 1 mysql mysql 8558 Jul 7 15:26 #sql4b0e_10_3.frm -- MEMORY引擎的臨時(shí)表
關(guān)于“mysql臨時(shí)表怎么使用”這篇文章的內(nèi)容就介紹到這里,感謝各位的閱讀!相信大家對(duì)“mysql臨時(shí)表怎么使用”知識(shí)都有一定的了解,大家如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。