MySQL 分區、分庫、分表

MySQL的分區

所謂分區就是将一個表分解成多個區塊進行操作和儲存，進而降低每次操作的資料，提高性能。而對應用來說是透明的，從邏輯上看是隻有一個表(這裡跟分庫分表的訪 問不一樣)，但在實體上這個表可能是由多個實體分區組成的，每個分區都是一個獨立的 對象，可以進行獨立處理。

分區能幹什麼

1:進行邏輯資料分割，分割資料能夠有多個不同的實體檔案路徑

2:可以存儲更多的資料，突破系統單個檔案最大限制

3:提升性能，提高每個分區的讀寫速度，提高分區範圍查詢的速度

4:可以通過删除相關分區來快速删除資料

5:通過跨多個磁盤來分散資料查詢，進而提高磁盤I/O的性能

6:涉及到例如SUM()和COUNT()這樣聚合函數的查詢，可以很容易地進行并行處理

7:可以備份和恢複獨立的分區，這對大資料量很有好處

分區能支援的引擎

MySQL支援大部分的存儲引擎建立分區，如MyISAM、InnoDB等;

不支援MERGE和CSV等 來建立分區。

同一個分區表中的所有分區必須是同一個存儲引擎。

确認MySQL支援分區 從MySQL5.1開始引入分區功能，可以如下方式檢視是否支援:

1:“老”的版本用:SHOW VARIABLES LIKE '%partition%';

2:新的版本用:show plugins;

分區類型

1:RANGE分區:基于屬于一個給定連續區間的列值，把多行配置設定給分區

2:LIST分區:類似于按RANGE分區，LIST是列值比對一個離散值集合中的某個值來進行選擇

3:HASH分區:基于使用者定義的表達式的傳回值來進行選擇的分區，該表達式使用将要插入到表中的這些行的列值進行計算，這個函數必須産生非負整數值

4:KEY分區:類似于按HASH分區，由MySQL伺服器提供其自身的哈希函數

但是不論什麼類型的分區，都要注意以下問題:

1，如果表中存在primary key或者unique key時，分區的列必須是primary key或者unique key的一個組成部分，也就是說，分區函數的列隻能從pk或者uk這些key中取子集

2，如果表中不存在任何的primary key或者unique key，則可以指定任何一個列作為分區列 3:5.5版本前的Range、List、Hash分區要求分區鍵必須是int;MySQL5.5及以上，支援非整型的Range和List分區，即:range columns 和 list columns。

分區命名

分區的名字基本上遵循其他MySQL 辨別符應當遵循的原則，例如用于表和資料庫名字的辨別符。但是應當注意，分區的名字是不區分大小寫的。 無論使用何種類型的分區，分區總是在建立時就自動的順序編号，且從0開始記錄。

建立分區

1:RANGE分區

CREATE TABLE tbl_users (

uuid INT NOT NULL,

customerId VARCHAR(20),

pwd VARCHAR(20),

showName VARCHAR(100),

trueName VARCHAR(100),

registerTime VARCHAR(100)

)

PARTITION BY RANGE (uuid) (

PARTITION p0 VALUES LESS THAN (5),

PARTITION p1 VALUES LESS THAN (10),

PARTITION p2 VALUES LESS THAN (15),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

CREATE TABLE tbl_users7(

uuid INT NOT NULL,

customerId VARCHAR(20),

pwd VARCHAR(20),

showName VARCHAR(100),

trueName VARCHAR(100),

registerTime Date

)

PARTITION BY RANGE(YEAR(registerTime))

SUBPARTITION BY HASH(TO_DAYS(registerTime))

(

PARTITION p0 VALUES LESS THAN (2008)(

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN (2015)(

SUBPARTITION s2,

SUBPARTITION s3

),

PARTITION p2 VALUES LESS THAN MAXVALUE(

SUBPARTITION s4,

SUBPARTITION s5

)

);

(1)到存放資料的地方檢視檔案，路經配置在/usr/bin/mysql_config裡面的ldata。

(2)可以通過使用形如:select * from information_schema.partitions where

table_schema=‘arch1’ and table_name=‘tbl_users’ \G; 的語句來檢視分區資訊

(3)可以通過形如select * from tbl_users partition(p0);的語句來檢視分區上的資料

(4)可以使用形如explain partitions select * from tbl_users where uuid=2;的語句來檢視MySQL會操作的分區

2:List分區

PARTITION BY List (uuid) (

PARTITION p0 VALUES in (1,2,3,5),

PARTITION p1 VALUES in (7,9,10),

PARTITION p2 VALUES in (11,15)

);

(1)如果試圖操作的列值不在分區值清單中時，那麼會失敗并報錯。要注意的是，LIST分區沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在内的定義，将要比對的任何值都必須在值清單中找到。

(2)LIST分區除了能和RANGE分區結合起來生成一個複合的子分區，與HASH和KEY分區結合起

來生成複合的子分區也是可以的。

3:Hash分區

HASH分區主要用來確定資料在預先确定數目的分區中平均分布。在RANGE和LIST分區中，必須明确指定一個給定的列值或列值集合以指定應該儲存在哪個分區中;而在HASH分區中，MySQL自動完成這些工作，要做的隻是基于将要被哈希的列值指定一個表達式，以及指定被分區的表将要被分割成的分區數量，

如:

PARTITION BY HASH (uuid)

PARTITIONS 3;

(1)由于每次插入、更新、删除一行，這個表達式都要計算一次;這意味着非常複雜的表達 式可能會引起性能問題，尤其是在執行同時影響大量行的運算(例如批量插入)的時候。

(2)最有效率的哈希函數是隻對單個表列進行計算，并且它的值随列值進行一緻地增大或減 小，因為這考慮了在分區範圍上的“修剪”。也就是說，表達式值和它所基于的列的值變 化越接近，就能越有效地使用該表達式來進行HASH分區。

3.1:線性Hash分區

線性哈希分區在“PARTITION BY” 子句中添加“LINEAR”關鍵字。

線性哈希分區的優點在于增加、删除、合并和拆分分區将變得更加快捷，有利于處理含有極其大量資料的表。它的缺點在于，各個分區間資料的分布不大可能均衡。

4:Key分區

類似于按照HASH分區，Hash分區允許使用者自定義的表達式，而Key分區不允許使用使用者自定義的表達式;

Hash分區隻支援整數分區，Key分區支援除了blob或text類型之外的 其他資料類型分區。

與Hash分區不同，建立Key分區表的時候，可以不指定分區鍵，預設會選擇使用主鍵或唯一鍵作為分區鍵，沒有主鍵或唯一鍵，就必須指定分區鍵。

CREATE TABLE tbl_users4 (

    uuid INT NOT NULL,

    customerId VARCHAR(20),

    pwd VARCHAR(20),

    showName VARCHAR(100),

    trueName VARCHAR(100),

    registerTime VARCHAR(100)

)

PARTITION BY LINEAR Key (uuid)

PARTITIONS 3;

5:子分區

子分區是分區表中每個分區的再次分割，适合儲存非常大量的資料。

CREATE TABLE tbl_users5 (

......

registerTime Date )

PARTITION BY RANGE(YEAR(registerTime))

SUBPARTITION BY HASH(TO_DAYS(registerTime))

SUBPARTITIONS 2

(

PARTITION p0 VALUES LESS THAN (2008),

PARTITION p1 VALUES LESS THAN (2015),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

(1)在MySQL 5.1中，對于已經通過RANGE或LIST分區了的表再進行子分區是可能的。子分區既可以使用HASH希分區，也可以使用KEY分區。這也被稱為複合分區

(2)每個分區必須有相同數量的子分區

(3)如果在一個分區表上的任何分區上使用SUBPARTITION 來明确定義任何子分區，那麼就必須定義所有的子分區

(4)每個SUBPARTITION 子句必須包括 (至少)子分區的一個名字

(5)在每個分區内，子分區的名字必須是唯一的，目前在整個表中，也要保持唯一。 例如: 

PARTITION BY RANGE(YEAR(registerTime))

SUBPARTITION BY HASH(TO_DAYS(registerTime)) (

PARTITION p0 VALUES LESS THAN (2008)(

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN (2015)(

SUBPARTITION s2,

SUBPARTITION s3

),

PARTITION p2 VALUES LESS THAN MAXVALUE(

SUBPARTITION s4,

SUBPARTITION s5

)

);

子分區可以用于特别大的表，可以在多個磁盤間配置設定資料和索引。 例如: 

SUBPARTITION s0

DATA DIRECTORY = '/disk0/data'

INDEX DIRECTORY = '/disk0/idx',

SUBPARTITION s1

DATA DIRECTORY = '/disk1/data'

NDEX DIRECTORY = '/disk1/idx'

MySQL分區處理NULL值的方式 MySQL中的分區在禁止空值NULL上沒有進行處理，無論它是一個列值還是一

個使用者定義表達式的值，一般而言，在這種情況下MySQL把NULL視為0。如果你希望回避這種做法，你應該在設計表時聲明列“NOT NULL”

分區管理概述 可以對分區進行添加、删除、重新定義、合并或拆分等管理操作。

RANGE和LIST分區的管理

1:删除分區語句如:alter table tbl_users drop partition p0;

(1)當删除了一個分區，也同時删除了該分區中所有的資料

(2)可以通過show create table tbl_users;來檢視新的建立表的語句

(3)如果是List分區的話，删除的資料不能新增進來，因為這些行的列值包含在已經删除了的分區的值清單中

2:添加分區語句如:alter table tbl_users add partition(partition p3 values less than(50));

(1)對于RANGE分區的表，隻可以添加新的分區到分區清單的高端

(2)對于List分區的表，不能添加已經包含在現有分區值清單中的任意值

3:如果希望能不丢失資料的條件下重新定義分區，可以使用如下語句: ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO

(partition_definitions)

(1)拆分分區如:

alter table tbl_users REORGANIZE PARTITION p1 INTO(

partition s0 values less than(5),

partition s1 values less than(10));

或者如:

alter table tbl_users2 REORGANIZE PARTITION p0 INTO(

partition s0 values in(1,2,3),

partition s1 values in(4,5));

(2)合并分區如:alter table tbl_users2 REORGANIZE PARTITION s0,s1 INTO(

partition p0 values in(1,2,3,4,5));

4:删除所有分區，但保留資料，

形如: alter table tbl_users remove partitioning;

HASH和KEY分區的管理

1:減少分區數量語句如: alter table tbl_users3 COALESCE PARTITION 2;

2:添加分區數量語句如:alter table tbl_users3 add PARTITION partitions 2;

其它分區管理語句

1:重建分區: 類似于先删除儲存在分區中的所有記錄，然後重新插入它們，可用于整理分區碎片。如:alter table tbl_users REBUILD PARTITION p2,p3;

2:優化分區:如果從分區中删除了大量的行，或者對一個帶有可變長度的行(也就是說，有VARCHAR，BLOB，或TEXT類型的列)作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區資料檔案的碎片。如:alter table tbl_users OPTIMIZE PARTITION p2,p3;

3:分析分區:讀取并儲存分區的鍵分布，如:alter table tbl_users ANALYZE PARTITION p2,p3;

4:檢查分區:檢查分區中的資料或索引是否已經被破壞，如:alter table tbl_users CHECK PARTITION p2,p3;

5:修補分區: 修補被破壞的分區，如:alter table tbl_users REPAIR PARTITION p2,p3;

其它

1:最大分區數目不能超過1024，一般建議對單表的分區數不要超過150個

2:如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在内

3:不支援外鍵

4:不支援全文索引，對分區表的分區鍵建立索引，那麼這個索引也将被分區

5:按日期進行分區很合适，因為很多日期函數可以用。但是對于字元串來說合适的分區函數不太多

6:隻有RANG和LIST分區能進行子分區，HASH和KEY分區不能進行子分區

7:臨時表不能被分區

8:分區表對于單條記錄的查詢沒有優勢

9:要注意選擇分區的成本，每插入一行資料都需要按照表達式篩選插入的分區

10:分區字段盡量不要可以為null

分庫分表

為什麼要分庫分表 資料庫的複制能解決通路問題，并不能解決大規模的并發寫入問題，由于無法進行分布式部署，而一台伺服器的資源(CPU、磁盤、記憶體、IO等)是有限 的，最終資料庫所能承載的資料量、資料處理能力都将遭遇瓶頸。

要解決這個問題就要考慮對資料庫進行分庫分表了，

它有如下好處:

1:解決磁盤系統最大檔案限制，比如常見的有:

FAT16(最大分區2GB,最大檔案2GB)

FAT32(最大分區32GB，最大容量2TB，最大檔案32G)

NTFS(最大分區2TB，最大容量，最大檔案2TB)

EXT3(最大檔案大小: 2TB，最大檔案極限: 僅受檔案系統大小限制，最大分區/ 檔案系統大小: 4TB，最大檔案名長度: 255 字元)

2:減少增量資料寫入時的鎖對查詢的影響，減少長時間查詢造成的表鎖，影響寫入操作等鎖競争的情況，節省排隊的時間開支，增加呑吐量。

3:由于單表數量下降，常見的查詢操作由于減少了需要掃描的記錄，使得單表單次 查詢所需的檢索行數變少，減少了磁盤IO，時延變短。

什麼是分庫

分庫又叫垂直切分，就是把原本存儲于一個庫的表拆分存儲到多個庫上，通常是将表按照功能子產品、關系密切程度劃分出來，部署到不同的庫上。

如果資料庫是因為表太多而造成海量資料，并且項目的各項業務邏輯劃厘清晰、低耦合，那麼規則簡單明了、容易實施的首選就是分庫。 分庫的優點是:實作簡單，庫與庫之間界限分明，便于維護，缺點是不利于頻繁跨

庫操作，單表資料量大的問題解決不了。

什麼是分表

分表又叫水準切分，是按照一定的業務規則或邏輯，将一個表的資料拆分成多份， 分别存儲在多個表結構一樣的表中，這多個表可以存在一到多個庫中。

分表又分成垂直分表和水準分表:

垂直分表:将本來可以在同一個表的内容，人為劃分為多個表。(所謂的本來，是指按照關系型資料庫的第三範式要求，是應該在同一個表的。)

水準分表，也被稱為資料分片:是把一個表複制成同樣表結構的不同表，然後把數 據按照一定的規則劃分，分别存儲到這些表中，進而保證單表的容量不會太大，提升性能;當然這些結構一樣的表，可以放在一個或多個資料庫中。

分表的優點是:

能解決分庫的不足點，但是缺點是實作起來比較複雜，特别是分表規則的劃分，程式的編寫，以及後期的資料庫拆分移植維護。

一般都是先分庫再分表，兩者結合使用，取長補短，這樣能發揮擴充的最大優勢， 但是缺點是架構很大，很複雜，應用程式的編寫也比較複雜。

如何分庫

基本的思路就是分析業務功能，以及表間的聚合關系，把關系緊密的表放在一起。

分庫的粒度指的是在做切分時允許幾級的關聯表放在一起，這個問題對應用程式實作有着很大的影響。關聯打斷的越多，則受影響的join操作越多，應用程式為此做出的妥協就越大，但單表的路由會越簡單，與業務的關聯性會越小，就越容易使用統一機制處理。

實際的粒度掌控需要結合“業務緊密程度”和“表的資料量”兩個因素綜合考慮， 一般來說:若劃歸到一起的表關系緊密，且資料量并不大，增速也非常緩慢，則适宜放在一起，不需要再進行水準切分;若劃歸到一起的表的資料量巨大且增速迅猛，則勢必要在 分庫的基礎上再進行分表，這就意味着原單一的庫還可能會被拆分成多個庫，這會導緻更多的複雜性，一開始最好就要考慮進去。

如何分表

對于垂直分表，通常是按照業務功能的使用頻次，把主要的、熱門的字段放在一起做為主要表;然後把不常用的，按照各自的業務屬性進行聚集，拆分到不同的次要表中;主要表和次要表的關系一般都是一對一的。

對于水準分表，通常是按照具體的業務規則和資料的格式，選擇能夠把資料進行合理拆分的業務資料做為拆分标準，以此來對資料進行拆分。

常見的一些拆分方式:按業務屬性、按時間、按區間、Hash、按資料的活 躍度、按資料量等，不管采用什麼方式，都要結合具體的業務場景進行分析和考 量。

當然這個過程中要考慮很多問題，比如:預估的資料量大小、資料量增長速度、分表的數量多少、在多表中資料的均衡、多表負載的均衡、擴容、通路表的導航資訊等

分表的表現又分成:單庫單表、單庫多表、多庫多表幾種。

分庫分表後的問題

1:分布式事務的問題，資料的完整性和一緻性問題 2:資料操作的次元問題 例如儲存交易記錄，是按照使用者的緯度分表儲存，還是按照産品的緯度來分表儲存。

3:跨庫聯合查詢的問題，可能需要兩次查詢

4:跨節點的count、order by、group by以及聚合函數問題，可能需要分别在各個

節點上得到結果，然後在應用程式端進行合并 5:額外的資料管理負擔，如:通路資料表的導航定位 6:額外的資料運算壓力，如:需要在多個節點執行，然後再合并計算 7:程式複雜度上升 8:後期維護難度上升，包括:程式的維護和更新、資料庫的擴容、資料的遷移等

水準分表的實作

概述

水準分表的實作面臨一系列問題:切分政策、庫節點路由、表路由、全局主鍵生成、跨節點排序

/分組/分頁/表關聯等操作、多資料源事務處理、資料庫擴容等。

部分相關開源産品一覽(排名不分先後)

1:MySQL Fabric:官方産品，非代理方式，目前不太穩定，性能也不夠好，但很有前景，綜合了HA和水準

分表的功能，是未來的首選。

2:Atlas:360開源，代理方式，基于MySQL-Proxy二次開發的，主要支援兩個特性:分表和讀寫分離，但是

分表的話隻支援單庫多表，事實上是不支援分布式分表的

3:Cobar:阿裡開源，代理方式，支援分布式分表，但是不支援單庫分多表，不支援讀寫分離，事務支援也

比較麻煩

4:TDDL (Taobao Distributed Data Layer ):阿裡部分開源，非代理方式，提供分庫分表對應用的透明

化，實作異構資料庫之間的資料複制，具有主備，讀寫分離，動态資料庫配置等功能。但複雜度相對較

高，公布的文檔少，核心部分不開源，還需要依賴Diamond

5:MySQL Proxy:官方提供，基于MySQL協定接口，主要提供負載平衡，讀寫分離，failover等，但性能較

差，不支援大資料量的分庫分表

6:Amoeba:支援分資料庫執行個體，每個資料相同的表，不支援事務;類似MySQL Proxy，相對更簡單

7:Hibernate Shards:支援分資料庫執行個體，比較複雜，需事先規劃資料規模，對HQL的支援非常有限

8:mybatis shardbatis:主要通過插件機制來實作分表，但是插件機制控制不到多資料源的連接配接;離開插

件層又失去了對sql進行集中解析和路由的機會

現狀——靠天靠地，不如靠自己

基本的實作思路

1:解析路由:根據業務功能指定;根據SQL解析等 不管采用何種方式，要獲得需要通路的資料源，以及分别要通路的表

2:分别在資料源和表上去執行功能

3:如果涉及到傳回結果集的話，就需要做結果集的合并，并按照需要進行二次處理，比如:排序、分頁等

4:如果需要事務的話，就得考慮是使用分布式事務，還是自行實作兩階段送出，或者采用補償性業務處理的方式等

可實作的層面

1:DAO層

2:Spring資料通路封裝層，介于DAO與JDBC之間

3:JDBC驅動層

4:介于應用伺服器與資料庫之間的代理伺服器