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

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

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

臨時(shí)表特性

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

由于臨時(shí)表只能被創(chuàng)建它的session訪(fǎng)問(wèn)，所以在這個(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);

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

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

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

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

select v from ht where f=N;

這時(shí)，我們就可以通過(guò)分表規(guī)則（比如，N%1024)來(lái)確認(rèn)需要的數(shù)據(jù)被放在了哪個(gè)分表上。這種語(yǔ)句只需要訪(fǎng)問(wèn)一個(gè)分表，是分庫(kù)分表方案最歡迎的語(yǔ)句形式了。

但是，如果這個(gè)表上還有另外一個(gè)索引k，并且查詢(xún)語(yǔ)句是這樣的：

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

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

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

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

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

在各個(gè)分庫(kù)上執(zhí)行

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

把分庫(kù)執(zhí)行的結(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è)語(yǔ)句的時(shí)候，MySQL要給這個(gè)InnoDB表創(chuàng)建一個(gè)frm文件保存表結(jié)構(gòu)定義，還要有地方保存表數(shù)據(jù)。

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

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

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

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

分區(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語(yǔ)句對(duì)ftime這兩個(gè)記錄之間的間隙加了鎖，間隙和加鎖狀態(tài)如圖：

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

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

對(duì)于MYISAM引擎：

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

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

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

分區(qū)策略

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

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

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

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

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

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

小結(jié)：

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

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

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

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

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。