臨時(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í)表，可以使用各種引擎類型 。如果是使用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訪問(wèn)，對(duì)其他線程不可見(jiàn)。所以，圖中session A創(chuàng)建的 臨時(shí)表t，對(duì)于session B就是不可見(jiàn)的。 臨時(shí)表可以與普通表同名。 session A內(nèi)有同名的臨時(shí)表和普通表的時(shí)候，show create語(yǔ)句，以及增刪改查語(yǔ)句訪問(wèn)的是臨時(shí)表。 show tables命令不顯示臨時(shí)表。

由于臨時(shí)表只能被創(chuàng)建它的session訪問(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ò)程中客戶端發(fā)生了異常斷開(kāi)，或者數(shù)據(jù)庫(kù)發(fā)生異常重啟，還需要專門(mén)來(lái)清理中間過(guò)程中生成的數(shù)據(jù)表。而臨時(shí)表由于會(huì)自動(dòng)回收，所以不需要這個(gè)額外的操作。臨時(shí)表的應(yīng)用

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

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

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

select v from ht where f=N;

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

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

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

這時(shí)候，由于查詢條件里面沒(méi)有用到分區(qū)字段f，只能到所有的分區(qū)中去查找滿足條件的所有 行，然后統(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}_{線程id}_序列 號(hào)”。你可以使用select @@tmpdir命令，來(lái)顯示實(shí)例的臨時(shí)文件目錄。

這個(gè)進(jìn)程的進(jìn)程號(hào)是1234，session A的線程id是4，session B的線程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ū)表的引擎層行為

ATETABLE`t`(`ftime`datetimeNOTNULL,`c`int(11)DEFAULTNULL,KEY(`ftime`))ENGINE=InnoDBDEFAULTCHARSET=latin1PARTITIONBYRANGE(YEAR(ftime))Û ॔ګդᎱB (PARTITIONp_2017VALUESLESSTHAN(2017)ENGINE=InnoDB, PARTITIONp_2018VALUESLESSTHAN(2018)ENGINE=InnoDB, PARTITIONp_2019VALUESLESSTHAN(2019)ENGINE=InnoDB, PARTITIONp_othersVALUESLESSTHANMAXVALUEENGINE=InnoDB); insertintotvalues(’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í)行的查詢，落到其他分區(qū)的查詢不受影響。這樣看來(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問(wèn)一個(gè)分區(qū)表時(shí)，mysql需要把所有分區(qū)都訪問(wèn)一遍：如果分區(qū)很多，比如查過(guò)了1000個(gè)，mysql啟動(dòng)的時(shí)候，open_files_limit默認(rèn)為1024，那么就會(huì)在訪問(wèn)表的時(shí)候，由于打開(kāi)了所有文件，超過(guò)了上限而報(bào)錯(cuò)。

mysiam使用的分區(qū)策略成為通用分區(qū)策略，每次訪問(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持有整個(gè)表t的mdl鎖，導(dǎo)致了B的alter語(yǔ)句被堵住。如果是使用普通分表，不會(huì)跟另外一個(gè)分表上的查詢語(yǔ)句出現(xiàn)MDL沖突。

小結(jié)：

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

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

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

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

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