數據庫的數據量達到一定程度之后，為避免帶來系統性能上的瓶頸。需要進行數據的處理，采用的手段是分區、分片、分庫、分表。

一些問題的解釋：

1.為什么要分表和分區？
日常開發中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。
這樣的表過于龐大，導致數據庫在查詢和插入的時候耗時太長，性能低下，
如果涉及聯合查詢的情況，性能會更加糟糕。
分表和表分區的目的就是減少數據庫的負擔，提高數據庫的效率，通常點來講就是提高表的增刪改查效率。

2.什么是分表？
分表是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，
我們可以稱為子表，每個表都對應三個文件，MYD數據文件，.MYI索引文件，.frm表結構文件。
這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。
App讀寫的時候根據事先定義好的規則得到對應的子表名，然后去操作它。

3.什么是分區？
分區和分表相似，都是按照規則分解表。
不同在于分表將大表分解為若干個獨立的實體表，而分區是將數據分段劃分在多個位置存放，
可以是同一塊磁盤也可以在不同的機器。
分區后，表面上還是一張表，但數據散列到多個位置了。
app讀寫的時候操作的還是大表名字，db自動去組織分區的數據。

4.MySQL分表和分區有什么聯系呢？
（1）都能提高mysql的性高，在高并發狀態下都有一個良好的表現。
（2）分表和分區不矛盾，可以相互配合的，對于那些大訪問量，并且表數據比較多的表，
	我們可以采取分表和分區結合的方式，訪問量不大，但是表數據很多的表，我們可以采取分區的方式等。
（3）分表技術是比較麻煩的，需要手動去創建子表，app服務端讀寫時候需要計算子表名。
	采用merge好一些，但也要創建子表和配置子表間的union關系。
（4）表分區相對于分表，操作方便，不需要創建子表。

分區

MySQL的物理數據，存儲在表空間文件（.ibdata1和.ibd）中，這里講的分區的意思是指將同一表中不同行的記錄分配到不同的物理文件中，幾個分區就有幾個.idb文件。

MySQL在5.1時添加了對水平分區的支持。

分區是將一個表或索引分解成多個更小，更可管理的部分。

每個區都是獨立的，可以獨立處理，也可以作為一個更大對象的一部分進行處理。這個是MySQL支持的功能，業務代碼無需改動。

可以通過使用SHOW VARIABLES命令來確定MySQL是否支持分區。

MySQL分區類型

RANGE分區：基于一個給定區間邊界，得到若干個連續區間范圍，按照分區鍵的落點，把數據分配到不同的分區；
LIST分區：類似RANGE分區，區別在于LIST分區是基于枚舉出的值列表分區，RANGE是基于給定連續區間范圍分區；
HASH分區：基于用戶自定義的表達式的返回值，對其根據分區數來取模，從而進行記錄在分區間的分配的模式。這個用戶自定義的表達式，就是MySQL希望用戶填入的哈希函數。
KEY分區：類似于按HASH分區，區別在于KEY分區只支持計算一列或多列，且使用MySQL 服務器提供的自身的哈希函數。

RANGE分區

把連續區間按范圍劃分，是實戰最常用的一種分區類型，行數據基于屬于一個給定的連續區間的列值被放入分區。

但是記住，當插入的數據不在一個分區中定義的值的時候，會拋異常。

RANGE分區主要用于日期列的分區，比如交易表啊，銷售表啊等。可以根據年月來存放數據。

如果你分區走的唯一索引中date類型的數據，
那么注意了，優化器只能對YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()這類函數進行優化選擇。

實戰中可以用int類型的字段來存時間戳做分區列，那么只用存yyyyMM就好了，也不用關心函數了。

CREATE TABLE
    `Order` (
        `id`
        INT NOT NULL AUTO_INCREMENT,
        `partition_key`
        INT NOT NULL,
        `amt`
        DECIMAL(5) NULL) PARTITION BY RANGE(partition_key)
PARTITIONS 5(
    PARTITION part0 VALUES LESS THAN(201901),
    PARTITION part1 VALUES LESS THAN(201902),
    PARTITION part2 VALUES LESS THAN(201903),
    PARTITION part3 VALUES LESS THAN(201904),
    PARTITION part4 VALUES LESS THAN(201905),
    PARTITION part4 VALUES LESS THAN MAXVALUE;
    
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

RANGE分區通過使用PARTITION BY RANGE(expr)實現，其中“expr” 可以是某個列值，或一個基于某個列值并返回一個整數值的表達式，如YEAR(date)。

不過值得注意的是，expr的返回值，不可以為NULL。

VALUES LESS THAN的排列必須從小到大順序列出，這樣MySQL才能識別一個一個的區間段。

涉及聚合函數SUM()、COUNT()的查詢時，如果不指定分區，那么會在每個分區上并行處理。

LIST分區

MySQL中的LIST分區在很多方面類似于RANGE分區。

和RANGE分區一樣，LIST分區的每個分區必須明確定義。

它們的主要區別在于，LIST分區是基于枚舉出的值列表分區，RANGE是基于給定連續區間范圍分區；

LIST分區通過使用PARTITION BY LIST(expr)來實現。

例如：

create table user(
    a int(11),
    b int(11)
)
partition by list(b)(
    partition p0 values in (1,3,5,7,9),
    partition p1 values in (2,4,6,8,0)
);

如果試圖插入字段值（或分區表達式的返回值）不在分區值列表中的任何一行時，那么“INSERT”查詢將失敗并報錯。

要重點注意的是，LIST分區沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內的定義。所以將要匹配的任何值都必須在值列表中能夠找到。

HASH分區

HASH分區主要用來確保數據在預先確定數目的分區中平均分布。

在RANGE和LIST分區中，我們必須明確指定一個給定的區間或列值集合，來指定哪些記錄進入哪些分區；

而在HASH分區中，MySQL自動完成分配記錄到區間的工作，你所要做的只是確定一個用來做哈希的字段或者表達式，以及指定被分區的表將要被分割成的分區數量。

PARTITION BY HASH

例如：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4；

如果沒有包括一個PARTITIONS子句，那么分區的數量將默認為1。

最有效率的哈希函數是只對單個表列進行計算，并且它的結果值隨字段值進行一致地增大或減小，因為這考慮了在分區范圍上的“修剪”。

也就是說，表達式值和它所基于的列的值變化越接近，MySQL就可以越有效地使用該表達式來進行HASH分區。

當使用了“PARTITION BY HASH”時，MySQL將基于用戶提供的函數結果的模數來確定使用哪個編號的分區。換句話，對于一個表達式“expr”，將要保存記錄的分區編號為N ，其中“N = MOD(expr, num)”。

KEY分區

按照KEY進行分區類似于按照HASH分區，除了HASH分區使用的用戶定義的表達式，而KEY分區的哈希函數是由MySQL 服務器提供。

MySQLCluster使用函數MD5()來實現KEY分區；對于使用其他存儲引擎的表，服務器使用其自己內部的哈希函數，這些函數是基于與PASSword()一樣的運算法則。

“CREATE TABLE ... PARTITION BY KEY”的語法規則類似于創建一個通過HASH分區的表的規則。它們唯一的區別在于使用的關鍵字是KEY而不是HASH，并且KEY分區只采用一個或多個列名的一個列表。

create table user(
    a int(11),
    b datetime
)
partition by key(b)
partitions 4;

子分區

子分區是分區表中每個分區的再次分割。

例如：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    (
        PARTITION p0 VALUES LESS THAN (1990)
        (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

注意的語法項：

每個分區必須有相同數量的子分區。
如果在一個分區表上的某個分區上使用SUBPARTITION來明確定義子分區，那么就必須定義其他所有分區的子分區。

子分區可以用于特別大的表，在多個磁盤間分配數據和索引。

然后就可以根據具體的情況來持久化：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    (
        PARTITION p0 VALUES LESS THAN (1990)
        (
            SUBPARTITION s0a
                DATA DIRECTORY = '/disk0'
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b
                DATA DIRECTORY = '/disk2' 
                INDEX DIRECTORY = '/disk3'
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s1a
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b
                DATA DIRECTORY = '/disk5/data' 
                INDEX DIRECTORY = '/disk5/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s2a,
            SUBPARTITION s2b
        )
    );

DATA DIRECTORY表示數據的物理文件的存放目錄
INDEX DIRECTORY表示索引的物理文件的存放目錄

分區的管理

MySQL提供了許多修改分區表的方式。添加、刪除、重新定義、合并或拆分已經存在的分區是可能的。

所有這些操作都可以通過使用ALTER TABLE命令的分區擴展來實現。

新增分區

為已創建的未分區表創建分區：

RANGE：ALTER TABLE tb PARTITION BY RANGE (expr) ( range_partitions_exprs(n>0) );
LIST：ALTER TABLE tb PARTITION BY LIST (expr) ( list_partitions_exprs(n>0) );
HASH：ALTER TABLE tb PARTITION BY HASH(expr) PARTITIONS 2;
KEY：ALTER TABLE tb PARTITION BY KEY(expr) PARTITIONS 2;

為分區表添加n個分區：

RANGE：ALTER TABLE tb ADD PARTITION ( range_partitions_exprs(n>0) );
LIST：ALTER TABLE tb ADD PARTITION ( list_partitions_exprs(n>0) );
HASH & KEY：ALTER TABLE tb ADD PARTITION PARTITIONS n;

調整分區

reorganize

數據不丟失的前提下，將m個分區合并為n個分區（m>n），即減量重新組織分區

RANGE：ALTER TABLE tb REORGANIZE PARTITION s0,s1,... INTO ( range_partitions_exprs(n) )
LIST：ALTER TABLE tb REORGANIZE PARTITION s0,s1,... INTO ( list_partitions_exprs(n) )
HASH & KEY：ALTER TABLE clients COALESCE PARTITION n; (n小于原有分區數)

數據不丟失的前提下，將分區表的m個分區拆分為n個分區（m<n），即增量重新組織分區

RANGE：ALTER TABLE tb REORGANIZE PARTITION p0,p1,... INTO ( range_partitions_exprs(n) )
LIST：ALTER TABLE tb REORGANIZE PARTITION p0,p1,... INTO ( list_partitions_exprs(n) )

不能使用REORGANIZE PARTITION來改變表的分區類型；也就是說。

重建分區，即先刪除分區中的所有記錄，然后重新插入。可用于整理分區碎片。

ALTER TABLE tb REBUILD PARTITION p0, p1;

優化分區，整理分區碎片 optimize

ALTER TABLE tb OPTIMIZE PARTITION p0, p1;

如從分區中刪除了大量的行，或者對一個帶有可變長度字段（VARCHAR、BLOB、TEXT類型）的行作了許多修改，可以使用優化分區來收回沒有使用的空間，并整理分區數據文件的碎片。

修復分區，修補被破壞的分區。

ALTER TABLE tb REPAIR PARTITION p0,p1;

檢查分區，這個命令可以告訴你分區中的數據或索引是否已經被破壞，如果被破壞，請使用修復分區來修補

ALTER TABLE tb CHECK PARTITION p1;

刪除分區

刪除一個分區，以及分區內的所有數據：

ALTER TABLE tb DROP PARTITION p2;

刪除一個分區，但保留分區內的所有數據(MySQL 5.5引入)： truncate

ALTER TABLE tb TRUNCATE PARTITION p2;

查看分區

查看某個schema下某個表的分區信息

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'xxx' AND TABLE_NAME LIKE 'xxxx';

分析某個分區,主要看行數和名稱以及狀態

ALTER TABLE tb ANALYZE PARTITION p3;

分表

分表顧名思義，就是把一張超大的數據表，拆分為多個較小的表，得到有效的緩解。

超大表會帶來如下的影響：

單表數據量太大，會被頻繁讀寫，加鎖操作密集，導致性能降低。
單表數據量太大，對應的索引也會很大，查詢效率降低，增刪操作的性能也會降低。

分表和分區看起來十分類似，確實，分區已經能夠在磁盤層面將一張表拆分成多個文件了，理論上前面提到的大表的問題都能得到有效解決。因為分區就是分表的數據庫實現版本。

在MySQL 5.1分區功能出現以前，要想解決超大表問題，只能采用分表操作，因為這類問題十分常見，MySQL才自帶了一個分區功能，以達到相同的效果。

所以你可以直接說分區就是分表的替代，分表是分區出現以前的做法。不過這不代表我們就沒有必要學習分表了，相反，水平分表的功能或許可以用更加便捷的分區來替代，但是垂直分表的功能，分區卻無法替代。

分表只能通過程序代碼來實現，目前市面上有許多分表的框架。（ Apache ShardingSphere ）

分表和分區的區別

分區只是一張表中的數據和索引的存儲位置發生改變，分表則是將一張表分成多張表，是真實的有多套表的配套文件
分區沒法突破數據庫層面，不論怎么分區，這些分區都要在一個數據庫下。而分表可以將子表分配在同一個庫中，也可以分配在不同庫中，突破數據庫性能的限制。
分區只能替代水平分表的功能，無法取代垂直分表的功能。

分表的類型

分表分為水平分表和垂直分表。

水平分表

水平分表和分區很像，或者說分區就是水平分表的數據庫實現版本，它們分的都是行記錄。

但是需要注意，如果這些表還是在同一個庫中，所以庫級別的數據庫操作還是有IO瓶頸。分表可以將單張表的數據切分到多個服務器上去，每個服務器具有相應的庫與子表，這是分區所不能有的優勢。

水平分表的切分規則一般有如下幾種：

范圍切分

可以根據某個字段的范圍做劃分，比如訂單號字段，從0到10000一個表，10001到20000一個表。

HASH取模

可以根據某個字段的HASH取模做劃分，比如將一個用戶表分成10個子表，可以取用戶id，然后hash后取10的模，從而分配到不同的數據庫上。不過這種劃分一旦確定后，就無法改變子表數量了。

地理/國籍/類型等

比如按照華東，華南，華北這樣來區分業務表，或者Android/ target=_blank class=infotextkey>安卓用戶，IOS用戶等來區分用戶表。

時間

按照時間切分，比如將6個月前，甚至一年前的數據切出去放到另外的一張表，因為隨著時間流逝，這些表的數據被查詢的概率變小，所以沒必要和“熱數據”放在一起，這個也是“冷熱數據分離”。

垂直分表

水平分表分的是行記錄，而垂直分表，分的是列字段，它就像用一把刀，垂直的將一個表切成多張表一樣。

垂直分表是基于列字段進行的。一般是表中的字段較多，或者有數據較大長度較長（比如text，blob，varchar(1000)以上的字段）的字段時，我們將不常用的，或者數據量大的字段拆分到“擴展表”上。這樣避免查詢時，數據量太大造成的“跨頁”問題。

垂直分表的切分規則很好理解，一般是“不常用”或者“字段數據量大”這兩點來做切割

分庫

分庫同樣是為了應對超大數據帶來的巨大的IO需求，如果不拆庫，那么單庫所能支持的吞吐能力和磁盤空間，就會成為制衡業務發展的瓶頸。

分庫的主要目的是為突破單節點數據庫服務器的I/O能力限制，解決數據庫水平擴展性問題。

分庫作用

分區和分表可以把單表分到不同的硬盤上，但不能分配到不同服務器上。一臺機器的性能是有限制的，用分庫可以解決單臺服務器性能不夠，或者成本過高問題。

將一個庫分成多個庫，并在多個服務器上部署，就可以突破單服務器的性能瓶頸，這是分庫必要性的最主要原因。

分庫的類型

分庫同樣分為水平分庫和垂直分庫。

水平分庫

水平分庫和水平分表相似，并且關系緊密，水平分庫就是將單個庫中的表作水平分表，然后將子表分別置于不同的子庫當中，獨立部署。
因為庫中內容的主要載體是表，所以水平分庫和水平分表基本上如影隨形。
例如用戶表，我們可以使用注冊時間的范圍來分表，將2020年注冊的用戶表usrtb2020部署在usrdata20中，2021年注冊的用戶表usrtb2021部署在usrdata21中。

垂直分庫

同樣的，垂直分庫和垂直分表也十分類似，不過垂直分表拆分的是字段，而垂直分庫，拆分的是表。
垂直分庫是將一個庫下的表作不同維度的分類，然后將其分配給不同子庫的策略。
例如，我們可以將用戶相關的表都放置在usrdata這個庫中，將訂單相關的表都放置在odrdata中，以此類推。
垂直分庫的分類維度有很多，可以按照業務模塊劃分（用戶/訂單...），按照技術模塊分（日志類庫/圖片類庫...），或者空間，時間等等。

問題

事務問題。

問題描述：在執行分庫分表之后，由于數據存儲到了不同的庫上，數據庫事務管理出現了困難。如果依賴數據庫本身的分布式事務管理功能去執行事務，將付出高昂的性能代價；如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。
解決方法：利用分布式事務，協調不同庫之間的數據原子性，一致性。

跨庫跨表的join問題。

問題描述：在執行了分庫分表之后，難以避免會將原本邏輯關聯性很強的數據劃分到不同的表、不同的庫上，這時，表的關聯操作將受到限制，我們無法join位于不同分庫的表，也無法join分表粒度不同的表，結果原本一次查詢能夠完成的業務，可能需要多次查詢才能完成。
解決方法：tddl、MyCAT等都支持跨分片join。但是我們應該盡力避免跨庫join，如果一定要整合數據，那么請在代碼中多次查詢完成。

額外的數據管理負擔和數據運算壓力。

問題描述：額外的數據管理負擔，最顯而易見的就是數據的定位問題和數據的增刪改查的重復執行問題，這些都可以通過應用程序解決，但必然引起額外的邏輯運算，例如，對于一個記錄用戶成績的用戶數據表userTable，業務要求查出成績最好的100位，在進行分表之前，只需一個order by語句就可以搞定，但是在進行分表之后，將需要n個order by語句，分別查出每一個分表的前100名用戶數據，然后再對這些數據進行合并計算，才能得出結果。
解決方法：無解，這是水平拓展的代價。