Mysql中的臨時(shí)表與分區(qū)表的區(qū)別有哪些

本篇文章給大家分享的是有關(guān)Mysql中的臨時(shí)表與分區(qū)表的區(qū)別有哪些，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

臨時(shí)表與內(nèi)存表

內(nèi)存表，指的是使用Memory引擎的表，建表語法是create table … engine=memory。這種 表的數(shù)據(jù)都保存在內(nèi)存里，系統(tǒng)重啟的時(shí)候會(huì)被清空，但是表結(jié)構(gòu)還在。除了這兩個(gè)特性看 上去比較“奇怪”外，從其他的特征上看，它就是一個(gè)正常的表

臨時(shí)表，可以使用各種引擎類型 。如果是使用InnoDB引擎或者M(jìn)yISAM引擎的臨時(shí)表，寫 數(shù)據(jù)的時(shí)候是寫到磁盤上的。當(dāng)然，臨時(shí)表也可以使用Memory引擎。

臨時(shí)表特性

• 建表語法是create temporary table …。
• 一個(gè)臨時(shí)表只能被創(chuàng)建它的session訪問，對(duì)其他線程不可見。所以，圖中session A創(chuàng)建的 臨時(shí)表t，對(duì)于session B就是不可見的。
• 臨時(shí)表可以與普通表同名。
• session A內(nèi)有同名的臨時(shí)表和普通表的時(shí)候，show create語句，以及增刪改查語句訪問的是臨時(shí)表。
• show tables命令不顯示臨時(shí)表。

由于臨時(shí)表只能被創(chuàng)建它的session訪問，所以在這個(gè)session結(jié)束的時(shí)候，會(huì)自動(dòng)刪除臨時(shí)表。 也正是由于這個(gè)特性，臨時(shí)表就特別適合join優(yōu)化這種場(chǎng)景。

create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

&#63847;同session的臨時(shí)表是可以重名的，如果有多個(gè)session同時(shí)執(zhí)&#64008;join優(yōu)化，&#63847;需要擔(dān)心表名重復(fù)導(dǎo)致建表失敗的問題。&#63847;需要擔(dān)心數(shù)據(jù)刪除問題。如果使用普通表，在流程執(zhí)&#64008;過程中客戶端發(fā)生&#63930;異常斷開，或者數(shù)據(jù)庫(kù)發(fā)生異常重啟，還需要專門來清&#63972;中間過程中生成的數(shù)據(jù)表。而臨時(shí)表由于會(huì)自動(dòng)回收，所以&#63847;需要這個(gè)額外的操作。臨時(shí)表的應(yīng)用

分庫(kù)分表系統(tǒng)的跨庫(kù)查詢

一般分庫(kù)分表的場(chǎng)景，就是要把一個(gè)邏輯上的大表分散到&#63847;同的數(shù)據(jù)庫(kù)實(shí)&#63925;上。比如。將一個(gè)大 表ht，按照字段f，拆分成1024個(gè)分表，然后分布到32個(gè)數(shù)據(jù)庫(kù)實(shí)&#63925;上。

分區(qū)key的選擇是以“減少跨庫(kù)和跨表查詢”為依據(jù)的。如果大部分的語&#63750;都會(huì)包 含f的等值條件，那么就要用f做分區(qū)鍵。這樣，在proxy這一層解析完SQL語&#63750;以后，就能確定將這條語&#63750;&#63799;由到哪個(gè)分表做查詢。 比如

select v from ht where f=N;

這時(shí)，我們就可以通過分表規(guī)則（比如，N%1024)來確認(rèn)需要的數(shù)據(jù)被放在&#63930;哪個(gè)分表上。這種語&#63750;只需要訪問一個(gè)分表，是分庫(kù)分表方案最歡迎的語&#63750;形式&#63930;。

但是，如果這個(gè)表上還有另外一個(gè)索引k，并且查詢語&#63750;是這樣的：

select v from ht where k >= M order by t_modified desc limit 100;

這時(shí)候，由于查詢條件&#63977;面沒有用到分區(qū)字段f，只能到所有的分區(qū)中去查找滿足條件的所有 &#64008;，然后統(tǒng)一做order by 的操作。這種情況下，有兩種比較常用的思&#63799;：

在proxy層的進(jìn)程代碼中實(shí)現(xiàn)排序，對(duì)proxy端的壓&#63882;比較大，尤其是很容&#63968;出現(xiàn)內(nèi)存&#63847;夠用和CPU瓶頸的問題。

把各個(gè)分庫(kù)拿到的數(shù)據(jù)，匯總到一個(gè)MySQL實(shí)&#63925;的一個(gè)表中，然后在這個(gè)匯總實(shí)&#63925;上做邏輯操作。

在匯總庫(kù)上創(chuàng)建一個(gè)臨時(shí)表temp_ht，表&#63977;包含三個(gè)字段v、k、t_modifified；

在各個(gè)分庫(kù)上執(zhí)&#64008;

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

把分庫(kù)執(zhí)&#64008;的結(jié)果插入到temp_ht表中；

執(zhí)行

select v from temp_ht order by t_modified desc limit 100;

為什么臨時(shí)表可以重名

create temporary table temp_t(id int primary key)engine=innodb;

執(zhí)行這個(gè)語&#63750;的時(shí)候，MySQL要給這個(gè)InnoDB表創(chuàng)建一個(gè)frm文件保存表結(jié)構(gòu)定義，還要有地方保存表數(shù)據(jù)。

這個(gè)frm文件放在臨時(shí)文件目錄下，文件名的后綴是.frm，前綴是“#sql{進(jìn)程id}_{線程id}_序&#63900; 號(hào)”。你可以使用select @@tmpdir命令，來顯示實(shí)&#63925;的臨時(shí)文件目錄。

這個(gè)進(jìn)程的進(jìn)程號(hào)是1234，session A的線程id是4，session B的線程id是5。所以session A和session B創(chuàng)建的臨時(shí)表，在磁盤上的文件&#63847;會(huì)重名

MySQL維護(hù)數(shù)據(jù)表，除&#63930;物&#63972;上要有文件外，內(nèi)存&#63977;面也有一套機(jī)制區(qū)別&#63847;同的表，每個(gè)表都對(duì)應(yīng)一個(gè)table_def_key。 對(duì)于臨時(shí)表，table_def_key在“庫(kù)名+表名”基礎(chǔ)上，又加入&#63930;“server_id+thread_id”。

也就是說，session A和sessionB創(chuàng)建的兩個(gè)臨時(shí)表t1，它們的table_def_key&#63847;同，磁盤文件名 也&#63847;同，因此可以并存。

分區(qū)表的引擎層行為

ATE	TABLE	`t`	(
		`ftime`	datetime	NOT	NULL,
		`c`	int(11)	DEFAULT	NULL,
		KEY	(`ftime`)
)	ENGINE=InnoDB	DEFAULT	CHARSET=latin1
PARTITION	BY	RANGE	(YEAR(ftime))
Û ॔ګդᎱ
B
 (PARTITION	p_2017	VALUES	LESS	THAN	(2017)	ENGINE	=	InnoDB,
 	PARTITION	p_2018	VALUES	LESS	THAN	(2018)	ENGINE	=	InnoDB,
 	PARTITION	p_2019	VALUES	LESS	THAN	(2019)	ENGINE	=	InnoDB,
 PARTITION	p_others	VALUES	LESS	THAN	MAXVALUE	ENGINE	=	InnoDB);
 insert	into	t	values('2017-4-1',1),('2018-4-1',1);

初始化表的時(shí)候，只插入了兩行數(shù)據(jù)，sessionA的select語句對(duì)ftime這兩個(gè)記錄之間的間隙加了鎖，間隙和加鎖狀態(tài)如圖：

也就是說，2017-4-1和2018-4-1這兩個(gè)記錄之間的間隙會(huì)被鎖住，那么sessionB的兩條插入語句都應(yīng)該進(jìn)入鎖等待狀態(tài)。但是從效果上看，第一個(gè)insert語句是可以執(zhí)行成功的，因?yàn)閷?duì)于引擎來說，p2018和p2019是不同的表，2017的下一個(gè)記錄不是2018-4-1而是p2018中的supremum，所以在t1時(shí)刻索引如圖：

由于分區(qū)表的規(guī)則，sessionA只操作了p2018，sessionB要插入2018-2-1是可以的但要寫入2017-12-1要等待sessionA的間隙鎖。

對(duì)于MYISAM引擎：

因?yàn)樵趕essionA中，sleep了100秒，由于myisam只支持表鎖，所以這條update會(huì)鎖住整個(gè)表t的讀，但是結(jié)果是，B的第一條語句是可以執(zhí)行的，第二條語句才進(jìn)入鎖等待狀態(tài)。

這是myisam表鎖只在引擎層實(shí)現(xiàn)的，sessionA加的表鎖，是所在p2018上，因此只會(huì)堵住分區(qū)上執(zhí)行的查詢，落到其他分區(qū)的查詢不受影響。這樣看來，分區(qū)表還不錯(cuò)，為什么不用呢，我們使用分區(qū)表的一個(gè)原因就是單表過大，那么不使用分區(qū)表，就要使用手動(dòng)分表的方式。

手動(dòng)分表需要?jiǎng)?chuàng)建t_2017,t_2018,t_2019，也就是找到需要更新的所有分表，依次執(zhí)行，這和分區(qū)表無實(shí)質(zhì)的差別，兩者一個(gè)由serverceng決定使用哪個(gè)分區(qū)，一個(gè)由應(yīng)用層代碼決定使用哪個(gè)分表，因此，從引擎層看無實(shí)際差別。其實(shí)主要區(qū)別是在server層：打開表行為。

分區(qū)策略

每當(dāng)?shù)谝淮卧L問一個(gè)分區(qū)表時(shí)，mysql需要把所有分區(qū)都訪問一遍：如果分區(qū)很多，比如查過了1000個(gè)，mysql啟動(dòng)的時(shí)候，open_files_limit默認(rèn)為1024，那么就會(huì)在訪問表的時(shí)候，由于打開了所有文件，超過了上限而報(bào)錯(cuò)。

mysiam使用的分區(qū)策略成為通用分區(qū)策略，每次訪問分區(qū)都是有server層控制。有比較嚴(yán)重的性能問題。

innodb引擎引入了本地分區(qū)策略，是在innodb內(nèi)部自己管理打開分區(qū)的行為。

分區(qū)表的server層行為

從server層看，一個(gè)分區(qū)表就是一個(gè)表。

雖然B只操作2017分區(qū)，但是由于A持有整個(gè)表t的mdl鎖，導(dǎo)致了B的alter語句被堵住。如果是使用普通分表，不會(huì)跟另外一個(gè)分表上的查詢語句出現(xiàn)MDL沖突。

小結(jié)：

• mysql在第一次打開分區(qū)表的時(shí)候，需要訪問所有分區(qū)
• 在server層，認(rèn)為這是同一張表，因此所有分區(qū)公用MDL鎖
• 在引擎層，認(rèn)為這是不同的表，因此在MDL鎖之后，會(huì)根據(jù)分區(qū)表規(guī)則，只訪問必要的分區(qū)。

分區(qū)表應(yīng)用場(chǎng)景

分區(qū)表的優(yōu)勢(shì)是對(duì)業(yè)務(wù)透明，相對(duì)于用戶分表來說，使用分區(qū)表的業(yè)務(wù)代碼更簡(jiǎn)潔，分區(qū)表可以很方便的清理歷史數(shù)據(jù)。

alter table t drop partition 操作是刪除分區(qū)文件，效果跟drop類似，與delete相比，優(yōu)勢(shì)是速度快，對(duì)系統(tǒng)影響小。

以上就是Mysql中的臨時(shí)表與分區(qū)表的區(qū)別有哪些，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。