MySQL分區(qū)分表應(yīng)用場(chǎng)景分析和分區(qū)中的問題與實(shí)踐

1、 MySQL 分區(qū)分表應(yīng)用場(chǎng)景分析和分區(qū)中的問題與實(shí)踐 MySQL的分區(qū)和分表應(yīng)用場(chǎng)景分析在日常工作中當(dāng)我們的某張表的數(shù)據(jù)量過大的時(shí)候，首當(dāng)其沖的可能就是進(jìn)行分區(qū)和分表，但是是如何分區(qū)或者分表都要結(jié)合一點(diǎn)的業(yè)務(wù)場(chǎng)景下進(jìn)行分析，才會(huì)顯著的提升性能，來聊一聊大家都在什么樣的場(chǎng)景下做過怎么樣的操作吧。hotdb CTO：一、分表的前世今生MySQL5.0以前版本存在單表行數(shù)的性能下降拐點(diǎn)，以經(jīng)典的MySQL4.17或MySQL3.23為例，那個(gè)特定年代的存儲(chǔ)引擎主要是ISAM或類ISAM存儲(chǔ)引擎和BDB存儲(chǔ)引擎，運(yùn)行平臺(tái)數(shù)據(jù)庫服務(wù)器主要是2-4塊硬盤，內(nèi)存幾乎都是4G-16G為主。在此背景下，行業(yè)內(nèi)

2、公認(rèn)ISAM存儲(chǔ)引擎的性能拐點(diǎn)為單表500萬行記錄，故一般會(huì)超過拐點(diǎn)的數(shù)據(jù)量表就會(huì)做人為分表和應(yīng)用程序配合，常見是按日期（例如：每月份一張表）、HASH值等方式。進(jìn)入MySQL5.0時(shí)代，主要是MyISAM或類MyISAM存儲(chǔ)引擎和InnoDB存儲(chǔ)引擎，但MySQL同其他商業(yè)數(shù)據(jù)庫產(chǎn)品相比依然無表分區(qū)特性。部分業(yè)務(wù)場(chǎng)景存在單表數(shù)據(jù)刪除或數(shù)據(jù)冷熱明顯的需求，為方便定期刪除數(shù)據(jù)和歸檔數(shù)據(jù)，或進(jìn)行數(shù)據(jù)冷熱分離而提升性能。前者M(jìn)yISAM和InnoDB存儲(chǔ)引擎都存在，通常做法是定期切換表，業(yè)務(wù)低谷期加表級(jí)別鎖，完成表名稱的RENAME操作；后者主要是針對(duì)MyISAM存儲(chǔ)引擎，只有索引緩存區(qū)，此時(shí)就會(huì)直

3、接進(jìn)行分表，通常做法是MyISAM存儲(chǔ)引擎的表數(shù)據(jù)分表后，再借助MERG存儲(chǔ)引擎合并回來，減少應(yīng)用程序改造。二、分區(qū)的功能特性進(jìn)入MySQL5.1及以上的時(shí)代，也隨著硬件成本的降低和平民化，X86服務(wù)器硬盤更多和內(nèi)存更大（起步都是6塊SAS硬盤，64G內(nèi)存），InnoDB存儲(chǔ)引擎得到迅速的普及，且MySQL數(shù)據(jù)庫支持分區(qū)特性。InnoDB存儲(chǔ)引擎具備元數(shù)據(jù)和索引數(shù)據(jù)的自適應(yīng)調(diào)節(jié)數(shù)據(jù)活躍度的緩存功能，內(nèi)存也足夠大，同時(shí)具備分區(qū)功能和支持交換分區(qū)，此時(shí)往往不再使用分表的方法，而是借助分區(qū)功能實(shí)現(xiàn)數(shù)據(jù)的刪除和歸檔等。三、分庫的緣由分表和分區(qū)都是基于同一個(gè)數(shù)據(jù)庫里的數(shù)據(jù)分離技巧，對(duì)數(shù)據(jù)庫性能有一定提升

4、，對(duì)MySQL數(shù)據(jù)庫的吞吐量無質(zhì)的變化。當(dāng)業(yè)務(wù)系統(tǒng)的數(shù)據(jù)容量接近或超過單臺(tái)X86服務(wù)器的容量、QPS/TPS接近或超過單個(gè)MySQL數(shù)據(jù)庫實(shí)例的處理極限等，則重點(diǎn)在于擴(kuò)展MySQL數(shù)據(jù)庫的吞吐量和數(shù)據(jù)處理量。此時(shí)，往往是采用垂直和水平結(jié)合的數(shù)據(jù)拆分方法，把數(shù)據(jù)服務(wù)和數(shù)據(jù)存儲(chǔ)分布到多臺(tái)MySQL數(shù)據(jù)庫服務(wù)器上。分庫只是一個(gè)通俗說法，更標(biāo)準(zhǔn)名稱是數(shù)據(jù)分片，采用類似分布式數(shù)據(jù)庫理論指導(dǎo)的方法實(shí)現(xiàn)，對(duì)應(yīng)用程序達(dá)到數(shù)據(jù)服務(wù)的全透明和數(shù)據(jù)存儲(chǔ)的全透明，這個(gè)領(lǐng)域的知識(shí)內(nèi)容就更復(fù)雜，日后慢慢分篇幅介紹。四、總結(jié)補(bǔ)充說明：若MyISAM存儲(chǔ)引擎分表后，借助類MyISAM的MERG存儲(chǔ)引擎，則不需要改造應(yīng)用程序，

5、存在少量限制。匿名用戶：隨著數(shù)據(jù)量的增加，一般的處理順序是分區(qū)：不同分區(qū)可以在不同的存儲(chǔ)介質(zhì)上，提供IO并發(fā)度，同時(shí)由于分區(qū)對(duì)應(yīng)用透明，因此應(yīng)用不需要改動(dòng)分表：根據(jù)業(yè)務(wù)主鍵對(duì)業(yè)務(wù)數(shù)據(jù)進(jìn)行分表，這樣如果出現(xiàn)故障，只是部分業(yè)務(wù)受影響，對(duì)于日期型日志表等進(jìn)行truncate也會(huì)簡單很多。分庫：可以應(yīng)用租戶的概念管理資源，將不同租戶的數(shù)據(jù)存放在不同數(shù)據(jù)庫實(shí)例中，馮帥 數(shù)據(jù)庫管理員：前面幾位說的都很好，大體而言的話數(shù)據(jù)庫數(shù)據(jù)越來越大，隨之而來的是單個(gè)表中數(shù)據(jù)太多。以至于查詢書讀變慢，而且由于表的鎖機(jī)制導(dǎo)致應(yīng)用操作也搜到嚴(yán)重影響，出現(xiàn)了數(shù)據(jù)庫性能瓶頸。分表，即將單個(gè)數(shù)據(jù)庫表進(jìn)行拆分，拆分成多個(gè)數(shù)據(jù)表，然后

6、用戶訪問的時(shí)候，根據(jù)一定的算法，讓用戶訪問不同的表，這樣數(shù)據(jù)分散到多個(gè)數(shù)據(jù)表中，減少了單個(gè)數(shù)據(jù)表的訪問壓力。提升了數(shù)據(jù)庫訪問性能。分區(qū)，即將單表拆成多個(gè)分區(qū)，然后用戶訪問的時(shí)候，根據(jù)分區(qū)鍵，讓用戶訪問某一個(gè)分區(qū)，這樣數(shù)據(jù)分散到多個(gè)分區(qū)中，減少了單分區(qū)的訪問壓力。提升了數(shù)據(jù)庫訪問性能。至于采取某一方案還是二者結(jié)合起來，主要還是看你的業(yè)務(wù)。qditz 項(xiàng)目總監(jiān)：大表可以按照業(yè)務(wù)規(guī)則來分解多個(gè)表，如range范圍，hash哈希，key鍵值，list預(yù)定義列表，composite復(fù)合模式等。也可以自定義規(guī)則。分表規(guī)則跟分區(qū)一樣的。MySQL分區(qū)中遇到哪些坑點(diǎn)和問題在MySQL 5.7.1之前的分區(qū)表不

7、支持HANDLER語句，有時(shí)候修改SQL模式可能會(huì)導(dǎo)致分區(qū)表不可用。mysql5.6中的分區(qū)總是有人說有很多坑，盡管5.7之后有了更好的支持，但是還是很多人都不太喜歡用，大家來分享一下使用mysql的分區(qū)的時(shí)候遇到的問題。韓成亮 數(shù)據(jù)庫架構(gòu)師：這邊應(yīng)該是說分區(qū)表。分區(qū)表的坑很多還是在數(shù)據(jù)量上，查詢性能上，維護(hù)性不是很可控，可能分區(qū)數(shù)據(jù)不均衡，很容易造成熱點(diǎn)數(shù)據(jù)。還有就是分區(qū)鍵的選擇很關(guān)鍵，要貼合業(yè)務(wù)查詢的規(guī)則，否則性能很差，對(duì)并發(fā)的支持不是很好，維護(hù)的成本很高。undefined：先說人。一般的MySQL開發(fā)不太關(guān)注數(shù)據(jù)量，只有在慢或者不可用的時(shí)候才會(huì)關(guān)注。這個(gè)時(shí)候需要DBA主動(dòng)去推，不過由于資料比oracle的少，所以還是需要看官方文檔，英文是一道坎。再說使用。分區(qū)的片鍵選擇很重要就像1樓所說的，另外需要改表結(jié)構(gòu)，把分區(qū)條件放在主鍵?？拥脑掗_發(fā)環(huán)境不要濫用，遇到過開發(fā)hash分區(qū)分了上百個(gè)出來；需要改表結(jié)構(gòu)，分區(qū)放在主鍵；還有老版本有handler的問題。馮帥 數(shù)據(jù)庫管理員：有一個(gè)先入為主的原則，我們都知道MySQL分區(qū)問題很多，導(dǎo)致了很多人不會(huì)考慮使用分區(qū)，這邊我想問下，對(duì)于