mysql怎么設置分區嗎 mysql默認分區

十、MySQL表分區

??表分區是將?個表的數據按照?定的規則?平劃分為不同的邏輯塊，并分別進?物理存儲，這個規則就叫做分區函數，可以有不同的分區規則。5.7可以通過show plugins語句查看當前MySQL是否?持表分區功能。

創新互聯公司專業IDC數據服務器托管提供商，專業提供成都服務器托管，服務器租用，資陽服務器托管，資陽服務器托管，成都多線服務器托管等服務器托管服務。

??但當表中含有主鍵或唯?鍵時，則每個被?作 分區函數的字段必須是表中唯?鍵和主鍵的全部或?部分 ，否則就?法創建分區表。?如下?的表由于唯?鍵和主鍵沒有相同的字段，所以?法創建表分區

上述例?中刪除唯?鍵，確保主鍵中的字段包含分區函數中的所有字段，創建成功

或者將主鍵擴展為包含ref字段

表分區的主要優勢在于：

??可以允許在?個表?存儲更多的數據，突破磁盤限制或者?件系統限制

??對于從表?將過期或歷史的數據移除在表分區很容易實現，只要將對應的分區移除即可

??對某些查詢和修改語句來說，可以 ?動 將數據范圍縮?到?個或?個表分區上，優化語句執?效率。?且可以通過 顯示指定表分區 來執?語句，?如 SELECT * FROM t PARTITION (p0,p1) WHERE c 5

表分區類型分為：

范圍表分區，按照?定的范圍值來確定每個分區包含的數據，分區函數使?的字段必須只能是 整數類型，分區的定義范圍必須是連續的，且不能有重疊部分，通過使?VALUES LESS THAN來定義分區范圍，表分區的范圍定義是從?到?定義的

?如：

??Store_id6的數據被放在p0分區?，6=store_id10之間的數據被放在p1分區?，以此類推，當新插?的數據為(72, ‘Mitchell’, ‘Wilson’, ‘1998-06-25’, NULL, 13) 時，則新數據被插?到p2分區?，但當插?的數據的store_id為21時，由于沒有分區去容納此數據，所以會報錯，我們需要修改?下表的定義

報錯：

修改表的定義：

?? MAXVALUE關鍵詞的作?是表示可能的最?值，所以任何store_id=16的數據都會被寫?到p3分區?。分區函數中也可以使?表達式 ，?如：

?? 對timestamp字段類型可以使?的表達式?前僅有unix_timestamp ，其他的表達式都不允許

列表表分區，按照?個?個確定的值來確定每個分區包含的數據，通過PARTITION BY LIST(expr)分區函數表達式必須返回整數，取值范圍通過VALUES IN (value_list)定義

對List表分區來說，沒有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含，如果有未定義的取值則會報錯

同樣，當有主鍵或者唯?鍵存在的情況下，分區函數字段需要包含在主鍵或唯?鍵中

對range和list表分區來說，分區函數可以包含多個字段，分區多字段函數(column partition) 所涉及的字段類型可以包括：

范圍多字段分區函數與普通的范圍分區函數的區別在于：

a) 字段類型多樣化

b) 范圍多字段分區函數 不?持表達式，只能?字段名

c) 范圍多字段分區函數?持?個或多個字段

再?如創建如下的表分區：

對多列對?來說：

當然只要保證取值范圍是增?的，表分區就能創建成功，?如：

但如果 取值范圍不是增?的，就會返回錯誤 ：

對其他數據類型的?持：

list列表多字段表分區，例如：你有一個在12個城市客戶的業務, 為了銷售和市場的目的, 你的組織每3個城市劃分為一個區域針對LIST COLUMNS分區, 你可以基于城市的名稱創建一個客戶數據表并聲明4個分區當你的客戶在對應的這個區域:

使用日期分區

但是這種情況在日期增長到非常大的時候是很復雜的, 所以這種還是使用RANGE 分區方式比較好

??按照?個?定義的函數返回值來確定每個分區包含的數據，這個 ?定義函數也可以僅僅是?個字段名字

??通過PARTITION BY HASH (expr)?句來表達哈希表分區，其中的 expr表達式必須返回?個整數，基于分區個數的取模（%）運算。根據余數插?到指定的分區

??對哈希表分區來說只需要定義分區的個數，其他的事情由內部完成

如果沒有寫明PARTITIONS字段，則默認為1，表達式可以是整數類型字段，也可以是?個函數，?如

?如： CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;

如果插??條數據對應的col3為‘2005-09-15’時，則插?數據的分區計算?法為：

??與哈希表分區類似，只不過哈希表分區依賴于?定義的函數，?key表分區的哈希算法是依賴MySQL本身， CREATE TABLE ... PARTITION BY KEY () 創建key表分區， 括號??可以包含0個或者多個字段，所引?的字段必須是主鍵或者主鍵的?部分 ，如果括號??沒有字段，則代表使?主鍵

如果表中沒有主鍵但有唯?鍵，則使?唯?鍵，但 唯?鍵字段必須定義為not null ，否則報錯

所引?的字段未必必須是整數類型，其他的類型也可以使?，?如：

?表分區，是在表分區的基礎上再創建表分區的概念， 每個表分區下的?表分區個數必須?致 ，?如：

ts表擁有三個范圍分區，同時每個分區都各?有兩個?分區，所以總共有6個分區

?表分區必須是范圍/列表分區+哈希/key?表分區的組合

?表分區也可以顯示的指定?表分區的名字，?如：

不同的表分區對NULL值的處理?式不同

對范圍表分區來說，如果插?的是NULL值，則將數據放到最?的分區表?

對list表分區來說，?持NULL值的唯?情況就是某個分區的允許值中包含NULL

對哈希表分區和Key表分區來說，NULL值會被當成0值對待

通過alter table命令可以執?增加，刪除，重新定義，合并或者拆分表分區的管理動作

對范圍表分區和列表表分區來說，刪除?個表分區命令如下：

刪除表分區的動作不光會把分區刪掉，也會把表分區?原來的數據給刪除掉

在原分區上增加?個表分區可以通過alter table … add partition語句來完成

但對范圍表分區來說，增加的表分區必須在尾部增加，在頭部或者在中間增加都會失敗：

為解決這個問題，可以使? REORGANIZE 命令：

對列表表分區來說，只要新增加的分區對應的值在之前的表分區中沒有出現過，就可以通過alter table… add partition來增加

當然， 也可以通過REORGANIZE命令將之前的多個分區合并成?個或?個分區，但要保持分區值?致：

更復雜的?如將多個分區重組成多個分區：

mysql 分區PARTITIONS之基本使用

當數據表中的數據量很大時，分區帶來的效率提升才會顯現出來。

只有檢索字段為分區字段時，分區帶來的效率提升才會比較明顯。因此， 分區字段的選擇很重要 ，并且 業務邏輯要盡可能地根據分區字段做相應調整 （盡量使用分區字段作為查詢條件）。

1、分區表對業務透明，只需要維護一個表的數據結構。

2、DML操作加鎖僅影響操作的分區，不會影響未訪問分區。

3、通過分區交換快速將數據換入和換出分區表。

4、通過TRUNCATE操作快速清理特定分區數據。

5、通過強制分區僅訪問特定分區數據，減少操作影響。

6、通過大數據量分區能有效降低索引層數，提高查詢性能。

1、創建表時指定分區

物理文件：

2、插入數據

3、查詢 information_schema.partitions 表得到該表的分區信息

4、查詢計劃分析

5、添加分區

若報錯：

6、修改/覆蓋/合并分區

報錯

所以需要添加pmax 分區，一同修改

7、刪除分區

8、查詢具體分區的下數據

MySQL分區的限制

mysql表分區使用及詳細介紹

一、分區概念

分區是將一個表分成多個區塊進行操作和保存，從而降低每次操作的數據，提高性能。而對于應用來說則是透明的，從邏輯上看只有一張表，但在物理上這個表可能是由多個物理分區組成的，每個分區都是獨立的對象，可以進行獨立處理。

二、分區作用

1.可以邏輯數據分割，分割數據能夠有多個不同的物理文件路徑。

2.可以存儲更多的數據，突破系統單個文件最大限制。

3.提升性能，提高每個分區的讀寫速度，提高分區范圍查詢的速度。

4.可以通過刪除相關分區來快速刪除數據

5.通過跨多個磁盤來分散數據查詢，從而提高磁盤I/O的性能。

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

7.可以備份和恢復獨立的分區，這對大數據量很有好處。

三、分區能支持的引擎

MySQL支持大部分引擎創建分區，入MyISAM、InnoDB等；不支持MERGE和CSV等來創建分區。同一個分區表中的所有分區必須是同一個存儲引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分區。

四、確認MySQL支持分區

從MySQL5.1開始引入分區功能，可以如下方式查看是否支持：

老版本用：SHOW VARIABLES LIKE '%partition%';

新版本用：show plugins;

五、分區類型

1. RANGE分區：基于屬于一個給定連續區間的列值，把多行分配給分區。

例如，可以將一個表通過年份劃分成兩個分區，2001 -2010年、2011-2020。

2. LIST分區：類似于RANGE分區，LIST是列值匹配一個離散值集合中的某個值來進行選擇。

比如 根據字段 把值為1、3、5的放到一起，2、4、6的另外放到一起 等等...

3. HASH分區：基于用戶定義的表達式的返回值來進行選擇分區，該表達式使用將要插入到表中的這些行的列值來進行計算，這個函數必須產生非負整數值。

通過HASH運算來進行分區，分布的比較均勻

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

按照KEY進行分區類似于按照HASH分區

六、分區創建注意事項

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

2. 如果表中不存在任何的paimary key或者unique key，則可以指定任何一個列作為分區列

3. 5.5版本前的RANGE、LIST、HASH分區要求分區鍵必須是int；MySQL5.5及以上，支持非整形的RANGE和LIST分區，即：range columns 和 list columns (可以用字符串來進行分區)。

七、分區命名

1. 分區的名字基本上遵循其他MySQL 標識符應當遵循的原則，例如用于表和數據庫名字的標識符。應當注意的是， 分區的名字是不區分大小寫的 。

2. 無論使用何種類型的分區，分區總是在創建時就自動的順序編號，且從0開始記錄。

八、 創建分區

1. RANGE分區：

CREATE TABLE `test01` (

`dayid` int(11) DEFAULT NULL,

`mac` varchar(32) NOT NULL DEFAULT '',

`dtype` varchar(50) NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY LIST (dayid)

(PARTITION p20171205 VALUES IN (20171205) ENGINE = InnoDB,

PARTITION p20171204 VALUES IN (20171204) ENGINE = InnoDB,

PARTITION p20171206 VALUES IN (20171206) ENGINE = InnoDB,

PARTITION p20171207 VALUES IN (20171207) ENGINE = InnoDB) */

解讀：以上為 uuid小于5時放到p0分區下，uuid大于5且小于10放到p1分區下，uuid大于10且小于15放到p2分區下，uuid大于15 一直到最大值的存在p3分區下

2. LIST分區：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

)

)

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)

)

);

解讀：以上為uuid 等于1/2/3/5時放到p0分區，7/9/10放到p1分區，11/15放到p2分區。當時用insert into時 如果uuid的值不存在p0/p1/p2分區時，則會插入失敗而報錯。

3. HASH分區:

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

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY HASH (uuid) (

PARTITIONS 3

));

解讀：MySQL自動創建3個分區，在執行insert into時，根據插入的uuid通過算法來自動分配區間。

注意：

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

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

3.1：線性HASH分區

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

線性HASH分區的有點在于增加、刪除、合并和拆分分區將變得更加快捷，有利于處理含有及其大量數據的表。它的缺點在于各個分區間數據的分布不大可能均衡。

4. KEY分區

類似于HASH分區，HASH分區允許用戶自定義的表達式，而KEY分區則不允許使用用戶自定義的表達式；HASH分區只支持整數分區，KEY分區支持除了blob和text類型之外的其他數據類型分區。

與HASH分區不同，創建KEY分區表的時候，可以不指定分區鍵，默認會選擇使用主鍵或唯一鍵作為分區鍵，沒有主鍵或唯一鍵，就必須指定分區鍵。

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY LINEAR Key (uuid)

PARTITIONS 3;

解讀：根據分區鍵來進行分區

5. 子分區

子分區是分區表中，每個分區的再次分割，適合保存非常大量的數據。

CREATE TABLE tbl_test (

registerTime Date

))

PARTITION BY GANGE(YEAR(registerTime))

SUBPARTITION BY HASH (TO_DAYS(registerTime))

SUBPARTITIONS 2

(

PARTITION p0 VALUES LESS THAN (2017),

PARTITION p1 VALUES LESS THAN (2020),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

解讀：主分區使用RANGE按照年來進行分區，有3個RANGE分區。這3個分區中又被進一步分成了2個子分區，實際上，整個表被分成了3 * 2 = 6個分區。每個子分區按照天進行HASH分區。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。

注意：

(1) 在MySQL5.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 (2017) (

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN (2020) (

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'

INDEX DIRECTORY = '/disk1/idx'

九、MySQL分區處理NULL值的方式

MySQL中的分區禁止空值NULL上沒有進行處理，無論它是一個列值還是一個用戶定義表達式的值，一般而言，在這種情況下MySQL把NULL視為0。如果你希望回避這種做法，你應該在設計表時聲明列“NOT NULL”。

十、分區管理概述

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

① RANGE和LIST分區的管理

1. 刪除分區語句如：alter table tbl_test drop partition p0;

注意：

(1) 當刪除了一個分區，也同時刪除了該分區中所有的數據。

(2) 可以通過show create table tbl_test;來查看新的創建表的語句。

(3) 如果是LIST分區的話，刪除的數據不能新增進來，因為這些行的列值包含在已經刪除了的分區的值列表中。

2. 添加分區語句如：alter table tbl_test 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_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10));

或者如：

ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5));

(2) 合并分區如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));

4. 刪除所有分區，但保留數據，形式：ALTER TABLE tbl_name remove partitioning;

② HASH和KEY分區的管理

1. 減少分區數量語句如：ALTER TABLE tbl_name COALESCE PARTITION 2;

2. 添加分區數量語句如：ALTER TABLE tbl_name add PARTITION partitions 2;

③ 其他分區管理語句

1. 重建分區 ：類似于先刪除保存在分區中的所有記錄，然后重新插入它們，可用于整理分區碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;

2. 優化分區 ：如果從分區中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB或TEXT類型的列）做了許多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION來收回沒有使用的空間，并整理分區數據文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;

3. 分析分區 ：讀取并保存分區的鍵分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;

4. 檢查分區 ：檢查分區中的數據或索引是否已經被破壞，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;

5. 修補分區 ：修補被破壞的分區，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;

十、查看分區信息

1. 查看分區信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G;

2. 查看分區上的數據：select * from tbl_test partition(p0);

3. 查看MySQL會操作的分區：explain partitions select * from tbl_test where uuid = 2;

十一、 局限性

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

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

3. 不支持外鍵。

4. 不支持全文索引，對分區表的分區鍵創建索引，那么這個索引也將被分區。

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

6. 只有RANGE和LIST分區能進行子分區，HASH和KEY分區不能進行子分區。

7. 臨時表不能被分區。

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

9. 要注意選擇分區的成本，沒插入一行數據都需要按照表達式篩選插入的分區。

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

MySQL-分區表

對底層表的封裝，意味著索引也是按照分區的子表定義的，而沒有全局索引。（所以即使有唯一性索引，在不同子表中可能會有重復數據）

單表數據量超大時索引失效

將單表分區成數個區域，通過分區函數，可以快速地定位到數據的區域。而且相比于索引，分區不需要額外的數據結構記錄每個分區的數據，代價更低。只需要一個簡單的表達式就可以指向正確的分區

可以只是用簡單的分區方式存放表，不要任何索引，只要將查詢定位到需要的大致數據位置，通過where條件，將需要的數據限制在少數分區中，則效率是很高的。WARNNING：查詢需要掃描的分區個數限制在一個很小的數量。

如果數據有明顯的“熱點”，可以將熱點數據單獨放在一個分區，讓這個分區的數據能夠有機會都緩存在內存中。

如果分區表達式的值可以是NULL：第一個分區會使一個特殊分區。以partition by range year(order_date)為例，所有在order_date列為NULL或者非法值的數據都會被放到第一個分區。那么所有的查詢在定位分區后都會增加掃描第一個分區。而且如果第一個分區很大的時候，查詢的成本會被這個“拖油瓶”分區無情的增加。

創建一個無用的第一分區可以解決這個問題，partition p_nulls values less than (0);

對于分區列和索引列不匹配的查詢，雖然查詢能夠使用索引，但是無法通過分區定位到目標數據的分區（也就是數據分布相對更加分散）,需要遍歷每個分區內的索引，除非查詢中的條件同時也包含分區條件。所以期望分區條件范圍被熱門查詢索引所包含。

對于 范圍分區 技術，需要適當限制分區的數量，否則對于大量數據批量導入的場景，選擇分區的成本過高。對于大多數系統，100個左右的分區是沒有問題的。

MySQL數據庫性能優化之分區分表分庫

分表是分散數據庫壓力的好方法。

分表，最直白的意思，就是將一個表結構分為多個表，然后，可以再同一個庫里，也可以放到不同的庫。

當然，首先要知道什么情況下，才需要分表。個人覺得單表記錄條數達到百萬到千萬級別時就要使用分表了。

分表的分類

**1、縱向分表**

將本來可以在同一個表的內容，人為劃分為多個表。（所謂的本來，是指按照關系型數據庫的第三范式要求，是應該在同一個表的。）

分表理由：根據數據的活躍度進行分離，（因為不同活躍的數據，處理方式是不同的）

案例：

對于一個博客系統，文章標題，作者，分類，創建時間等，是變化頻率慢，查詢次數多，而且最好有很好的實時性的數據，我們把它叫做冷數據。而博客的瀏覽量，回復數等，類似的統計信息，或者別的變化頻率比較高的數據，我們把它叫做活躍數據。所以，在進行數據庫結構設計的時候，就應該考慮分表，首先是縱向分表的處理。

這樣縱向分表后：

首先存儲引擎的使用不同，冷數據使用MyIsam 可以有更好的查詢數據。活躍數據，可以使用Innodb ,可以有更好的更新速度。

其次，對冷數據進行更多的從庫配置，因為更多的操作時查詢，這樣來加快查詢速度。對熱數據，可以相對有更多的主庫的橫向分表處理。

其實，對于一些特殊的活躍數據，也可以考慮使用memcache ,redis之類的緩存，等累計到一定量再去更新數據庫。或者mongodb 一類的nosql 數據庫，這里只是舉例，就先不說這個。

**2、橫向分表**

字面意思，就可以看出來，是把大的表結構，橫向切割為同樣結構的不同表，如，用戶信息表，user_1,user_2等。表結構是完全一樣，但是，根據某些特定的規則來劃分的表，如根據用戶ID來取模劃分。

分表理由：根據數據量的規模來劃分，保證單表的容量不會太大，從而來保證單表的查詢等處理能力。

案例：同上面的例子，博客系統。當博客的量達到很大時候，就應該采取橫向分割來降低每個單表的壓力，來提升性能。例如博客的冷數據表，假如分為100個表，當同時有100萬個用戶在瀏覽時，如果是單表的話，會進行100萬次請求，而現在分表后，就可能是每個表進行1萬個數據的請求（因為，不可能絕對的平均，只是假設），這樣壓力就降低了很多很多。

延伸：為什么要分表和分區？

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

什么是分表？

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

什么是分區？

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

**MySQL分表和分區有什么聯系呢？**

1、都能提高mysql的性高，在高并發狀態下都有一個良好的表現。

2、分表和分區不矛盾，可以相互配合的，對于那些大訪問量，并且表數據比較多的表，我們可以采取分表和分區結合的方式（如果merge這種分表方式，不能和分區配合的話，可以用其他的分表試），訪問量不大，但是表數據很多的表，我們可以采取分區的方式等。

3、分表技術是比較麻煩的，需要手動去創建子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要創建子表和配置子表間的union關系。

4、表分區相對于分表，操作方便，不需要創建子表。

我們知道對于大型的互聯網應用，數據庫單表的數據量可能達到千萬甚至上億級別，同時面臨這高并發的壓力。Master-Slave結構只能對數據庫的讀能力進行擴展，寫操作還是集中在Master中，Master并不能無限制的掛接Slave庫，如果需要對數據庫的吞吐能力進行進一步的擴展，可以考慮采用分庫分表的策略。

**1、分表**

在分表之前，首先要選中合適的分表策略（以哪個字典為分表字段，需要將數據分為多少張表），使數據能夠均衡的分布在多張表中，并且不影響正常的查詢。在企業級應用中，往往使用org_id(組織主鍵)做為分表字段，在互聯網應用中往往是userid。在確定分表策略后，當數據進行存儲及查詢時，需要確定到哪張表里去查找數據，

數據存放的數據表 = 分表字段的內容 % 分表數量

**2、分庫**

分表能夠解決單表數據量過大帶來的查詢效率下降的問題，但是不能給數據庫的并發訪問帶來質的提升，面對高并發的寫訪問，當Master無法承擔高并發的寫入請求時，不管如何擴展Slave服務器，都沒有意義了。我們通過對數據庫進行拆分，來提高數據庫的寫入能力，即所謂的分庫。分庫采用對關鍵字取模的方式，對數據庫進行路由。

數據存放的數據庫=分庫字段的內容%數據庫的數量

**3、即分表又分庫**

數據庫分表可以解決單表海量數據的查詢性能問題，分庫可以解決單臺數據庫的并發訪問壓力問題。

當數據庫同時面臨海量數據存儲和高并發訪問的時候，需要同時采取分表和分庫策略。一般分表分庫策略如下：

中間變量 = 關鍵字%（數據庫數量*單庫數據表數量）

庫 = 取整（中間變量/單庫數據表數量）

表 = （中間變量%單庫數據表數量）

實例：

1、分庫分表

很明顯，一個主表（也就是很重要的表，例如用戶表）無限制的增長勢必嚴重影響性能，分庫與分表是一個很不錯的解決途徑，也就是性能優化途徑，現在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢，同事的做法是將其散列到100個表中，分別從members0到members99，然后根據mid分發記錄到這些表中，牛逼的代碼大概是這樣子：

復制代碼 代碼如下:

?php

for($i=0;$i 100; $i++ ){

//echo "CREATE TABLE db2.members{$i} LIKE db1.members

";

echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

";

}

?

2、不停機修改mysql表結構

同樣還是members表，前期設計的表結構不盡合理，隨著數據庫不斷運行，其冗余數據也是增長巨大，同事使用了下面的方法來處理：

先創建一個臨時表：

/*創建臨時表*/

CREATE TABLE members_tmp LIKE members

然后修改members_tmp的表結構為新結構，接著使用上面那個for循環來導出數據，因為1000萬的數據一次性導出是不對的，mid是主鍵，一個區間一個區間的導，基本是一次導出5萬條吧，這里略去了

接著重命名將新表替換上去：

/*這是個頗為經典的語句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members;

就是這樣，基本可以做到無損失，無需停機更新表結構，但實際上RENAME期間表是被鎖死的，所以選擇在線少的時候操作是一個技巧。經過這個操作，使得原先8G多的表，一下子變成了2G多。

網頁名稱：mysql怎么設置分區嗎 mysql默認分區
轉載來于：http://m.kartarina.com/article20/hjocjo.html

成都網站建設公司_創新互聯，為您提供定制網站、網站導航、搜索引擎優化、手機網站建設、、移動網站建設

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯