Mysql臨時表及分區(qū)表區(qū)別詳解

臨時表與內存表

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

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

臨時表特性

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

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

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);

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

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

一般分庫分表的場景，就是要把一個邏輯上的大表分散到不同的數(shù)據(jù)庫實例上。比如。將一個大 表ht，按照字段f，拆分成1024個分表，然后分布到32個數(shù)據(jù)庫實例上。

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

select v from ht where f=N;

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

但是，如果這個表上還有另外一個索引k，并且查詢語句是這樣的：

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

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

在proxy層的進程代碼中實現(xiàn)排序，對proxy端的壓力比較大，尤其是很容易出現(xiàn)內存不夠用和CPU瓶頸的問題。

把各個分庫拿到的數(shù)據(jù)，匯總到一個MySQL實例的一個表中，然后在這個匯總實例上做邏輯操作。

在匯總庫上創(chuàng)建一個臨時表temp_ht，表里包含三個字段v、k、t_modifified；

在各個分庫上執(zhí)行

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

把分庫執(zhí)行的結果插入到temp_ht表中；

執(zhí)行

select v from temp_ht order by t_modified desc limit 100;

為什么臨時表可以重名

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

執(zhí)行這個語句的時候，MySQL要給這個InnoDB表創(chuàng)建一個frm文件保存表結構定義，還要有地方保存表數(shù)據(jù)。

這個frm文件放在臨時文件目錄下，文件名的后綴是.frm，前綴是“#sql{進程id}_{線程id}_序列 號”。你可以使用select @@tmpdir命令，來顯示實例的臨時文件目錄。

這個進程的進程號是1234，session A的線程id是4，session B的線程id是5。所以session A和session B創(chuàng)建的臨時表，在磁盤上的文件不會重名

MySQL維護數(shù)據(jù)表，除了物理上要有文件外，內存里面也有一套機制區(qū)別不同的表，每個表都對應一個table_def_key。 對于臨時表，table_def_key在“庫名+表名”基礎上，又加入了“server_id+thread_id”。

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

分區(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ù)據(jù)，sessionA的select語句對ftime這兩個記錄之間的間隙加了鎖，間隙和加鎖狀態(tài)如圖：

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

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

對于MYISAM引擎：

因為在sessionA中，sleep了100秒，由于myisam只支持表鎖，所以這條update會鎖住整個表t的讀，但是結果是，B的第一條語句是可以執(zhí)行的，第二條語句才進入鎖等待狀態(tài)。

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

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

分區(qū)策略

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

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

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

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

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

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

小結：

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

分區(qū)表應用場景

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

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

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。