對(duì)底層表的封裝，意味著索引也是按照分區(qū)的子表定義的，而沒(méi)有全局索引。（所以即使有唯一性索引，在不同子表中可能會(huì)有重復(fù)數(shù)據(jù)）
成都創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括潼關(guān)網(wǎng)站建設(shè)、潼關(guān)網(wǎng)站制作、潼關(guān)網(wǎng)頁(yè)制作以及潼關(guān)網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，潼關(guān)網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到潼關(guān)省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
單表數(shù)據(jù)量超大時(shí)索引失效
將單表分區(qū)成數(shù)個(gè)區(qū)域，通過(guò)分區(qū)函數(shù)，可以快速地定位到數(shù)據(jù)的區(qū)域。而且相比于索引，分區(qū)不需要額外的數(shù)據(jù)結(jié)構(gòu)記錄每個(gè)分區(qū)的數(shù)據(jù)，代價(jià)更低。只需要一個(gè)簡(jiǎn)單的表達(dá)式就可以指向正確的分區(qū)
可以只是用簡(jiǎn)單的分區(qū)方式存放表，不要任何索引，只要將查詢定位到需要的大致數(shù)據(jù)位置，通過(guò)where條件，將需要的數(shù)據(jù)限制在少數(shù)分區(qū)中，則效率是很高的。WARNNING：查詢需要掃描的分區(qū)個(gè)數(shù)限制在一個(gè)很小的數(shù)量。
如果數(shù)據(jù)有明顯的“熱點(diǎn)”，可以將熱點(diǎn)數(shù)據(jù)單獨(dú)放在一個(gè)分區(qū)，讓這個(gè)分區(qū)的數(shù)據(jù)能夠有機(jī)會(huì)都緩存在內(nèi)存中。
如果分區(qū)表達(dá)式的值可以是NULL：第一個(gè)分區(qū)會(huì)使一個(gè)特殊分區(qū)。以partition by range year(order_date)為例，所有在order_date列為NULL或者非法值的數(shù)據(jù)都會(huì)被放到第一個(gè)分區(qū)。那么所有的查詢?cè)诙ㄎ环謪^(qū)后都會(huì)增加掃描第一個(gè)分區(qū)。而且如果第一個(gè)分區(qū)很大的時(shí)候，查詢的成本會(huì)被這個(gè)“拖油瓶”分區(qū)無(wú)情的增加。
創(chuàng)建一個(gè)無(wú)用的第一分區(qū)可以解決這個(gè)問(wèn)題，partition p_nulls values less than (0);
對(duì)于分區(qū)列和索引列不匹配的查詢，雖然查詢能夠使用索引，但是無(wú)法通過(guò)分區(qū)定位到目標(biāo)數(shù)據(jù)的分區(qū)（也就是數(shù)據(jù)分布相對(duì)更加分散）,需要遍歷每個(gè)分區(qū)內(nèi)的索引，除非查詢中的條件同時(shí)也包含分區(qū)條件。所以期望分區(qū)條件范圍被熱門查詢索引所包含。
對(duì)于 范圍分區(qū) 技術(shù)，需要適當(dāng)限制分區(qū)的數(shù)量，否則對(duì)于大量數(shù)據(jù)批量導(dǎo)入的場(chǎng)景，選擇分區(qū)的成本過(guò)高。對(duì)于大多數(shù)系統(tǒng)，100個(gè)左右的分區(qū)是沒(méi)有問(wèn)題的。
一、查詢mysql表是否為分區(qū)表：可以查看表具有哪幾個(gè)分區(qū)、分區(qū)的方法、分區(qū)中數(shù)據(jù)的記錄數(shù)等信息
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='xw_coobill_order';
二、查詢表有多少個(gè)分區(qū)
SELECT TABLE_NAME, COUNT(*) AS CNT
FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL
GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;
三、分析執(zhí)行語(yǔ)句
explain partitions select * from range_datetime where hiredate = '20151207124503' and hiredate='20151210111230';
四、分區(qū)管理
常規(guī)HASH和線性HASH的增加收縮分區(qū)的原理是一樣的。增加和收縮分區(qū)后原來(lái)的數(shù)據(jù)會(huì)根據(jù)現(xiàn)有的分區(qū)數(shù)量重新分布。HASH分區(qū)不能刪除分區(qū)，所以不能使用DROP PARTITION操作進(jìn)行分區(qū)刪除操作；
只能通過(guò)ALTER TABLE ... COALESCE PARTITION num來(lái)合并分區(qū)，這里的num是減去的分區(qū)數(shù)量；
可以通過(guò)ALTER TABLE ... ADD PARTITION PARTITIONS num來(lái)增加分區(qū)，這里是null是在原先基礎(chǔ)上再增加的分區(qū)數(shù)量。
一、分區(qū)概念
分區(qū)是將一個(gè)表分成多個(gè)區(qū)塊進(jìn)行操作和保存，從而降低每次操作的數(shù)據(jù)，提高性能。而對(duì)于應(yīng)用來(lái)說(shuō)則是透明的，從邏輯上看只有一張表，但在物理上這個(gè)表可能是由多個(gè)物理分區(qū)組成的，每個(gè)分區(qū)都是獨(dú)立的對(duì)象，可以進(jìn)行獨(dú)立處理。
二、分區(qū)作用
1.可以邏輯數(shù)據(jù)分割，分割數(shù)據(jù)能夠有多個(gè)不同的物理文件路徑。
2.可以存儲(chǔ)更多的數(shù)據(jù)，突破系統(tǒng)單個(gè)文件最大限制。
3.提升性能，提高每個(gè)分區(qū)的讀寫速度，提高分區(qū)范圍查詢的速度。
4.可以通過(guò)刪除相關(guān)分區(qū)來(lái)快速刪除數(shù)據(jù)
5.通過(guò)跨多個(gè)磁盤來(lái)分散數(shù)據(jù)查詢，從而提高磁盤I/O的性能。
6.涉及到例如SUM()、COUNT()這樣聚合函數(shù)的查詢，可以很容易的進(jìn)行并行處理。
7.可以備份和恢復(fù)獨(dú)立的分區(qū)，這對(duì)大數(shù)據(jù)量很有好處。
三、分區(qū)能支持的引擎
MySQL支持大部分引擎創(chuàng)建分區(qū)，入MyISAM、InnoDB等；不支持MERGE和CSV等來(lái)創(chuàng)建分區(qū)。同一個(gè)分區(qū)表中的所有分區(qū)必須是同一個(gè)存儲(chǔ)引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分區(qū)。
四、確認(rèn)MySQL支持分區(qū)
從MySQL5.1開(kāi)始引入分區(qū)功能，可以如下方式查看是否支持：
老版本用：SHOW VARIABLES LIKE '%partition%';
新版本用：show plugins;
五、分區(qū)類型
1. RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。
例如，可以將一個(gè)表通過(guò)年份劃分成兩個(gè)分區(qū)，2001 -2010年、2011-2020。
2. LIST分區(qū)：類似于RANGE分區(qū)，LIST是列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。
比如 根據(jù)字段 把值為1、3、5的放到一起，2、4、6的另外放到一起 等等...
3. HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值來(lái)進(jìn)行計(jì)算，這個(gè)函數(shù)必須產(chǎn)生非負(fù)整數(shù)值。
通過(guò)HASH運(yùn)算來(lái)進(jìn)行分區(qū)，分布的比較均勻
4. KEY分區(qū)：類似于按HASH分區(qū)，由MySQL服務(wù)器提供其自身的哈希函數(shù)。
按照KEY進(jìn)行分區(qū)類似于按照HASH分區(qū)
六、分區(qū)創(chuàng)建注意事項(xiàng)
1. 如果表中存在primary key 或者 unique key 時(shí)，分區(qū)的列必須是paimary key或者unique key的一個(gè)組成部分，也就是說(shuō)，分區(qū)函數(shù)的列只能從pk或者uk這些key中取子集
2. 如果表中不存在任何的paimary key或者unique key，則可以指定任何一個(gè)列作為分區(qū)列
3. 5.5版本前的RANGE、LIST、HASH分區(qū)要求分區(qū)鍵必須是int；MySQL5.5及以上，支持非整形的RANGE和LIST分區(qū)，即：range columns 和 list columns (可以用字符串來(lái)進(jìn)行分區(qū))。
七、分區(qū)命名
1. 分區(qū)的名字基本上遵循其他MySQL 標(biāo)識(shí)符應(yīng)當(dāng)遵循的原則，例如用于表和數(shù)據(jù)庫(kù)名字的標(biāo)識(shí)符。應(yīng)當(dāng)注意的是， 分區(qū)的名字是不區(qū)分大小寫的 。
2. 無(wú)論使用何種類型的分區(qū)，分區(qū)總是在創(chuàng)建時(shí)就自動(dòng)的順序編號(hào)，且從0開(kāi)始記錄。
八、 創(chuàng)建分區(qū)
1. RANGE分區(qū)：
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時(shí)放到p0分區(qū)下，uuid大于5且小于10放到p1分區(qū)下，uuid大于10且小于15放到p2分區(qū)下，uuid大于15 一直到最大值的存在p3分區(qū)下
2. LIST分區(qū)：
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時(shí)放到p0分區(qū)，7/9/10放到p1分區(qū)，11/15放到p2分區(qū)。當(dāng)時(shí)用insert into時(shí) 如果uuid的值不存在p0/p1/p2分區(qū)時(shí)，則會(huì)插入失敗而報(bào)錯(cuò)。
3. HASH分區(qū):
HASH分區(qū)主要用來(lái)確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在RANGE分區(qū)和LIST分區(qū)中必須明確指定一個(gè)指定的列值或列值集合以指定應(yīng)該保存在哪個(gè)分區(qū)中。而在HASH分區(qū)中，MySQL會(huì)自動(dòng)完成這些工作，要做的只是基于將要被哈希的列值指定一個(gè)表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量，如：
CREATE TABLE tbl_test (
uuid INT NOT NULL,
title VARCHAR(20)
))
PARTITION BY HASH (uuid) (
PARTITIONS 3
));
解讀：MySQL自動(dòng)創(chuàng)建3個(gè)分區(qū)，在執(zhí)行insert into時(shí)，根據(jù)插入的uuid通過(guò)算法來(lái)自動(dòng)分配區(qū)間。
注意：
(1) 由于每次插入、更新、刪除一行，這個(gè)表達(dá)式都要計(jì)算一次，這意味著非常復(fù)雜的表達(dá)式可能會(huì)引起性能問(wèn)題，尤其是在執(zhí)行同時(shí)影響大量行的運(yùn)算（例如批量插入）的時(shí)候。
(2) 最有效率的哈希函數(shù)是只對(duì)單個(gè)表列進(jìn)行計(jì)算，并且它的值隨列值進(jìn)行一致的增大或減小，因?yàn)檫@考慮了在分區(qū)范圍上的“修剪”。也就是說(shuō)，表達(dá)式值和它所基于的列的值變化越接近，就越能有效地使用該表達(dá)式來(lái)進(jìn)行HASH分區(qū)。
3.1：線性HASH分區(qū)
線性HASH分區(qū)在“PARTITION BY”子句中添加“LINEAR”關(guān)鍵字。
線性HASH分區(qū)的有點(diǎn)在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有及其大量數(shù)據(jù)的表。它的缺點(diǎn)在于各個(gè)分區(qū)間數(shù)據(jù)的分布不大可能均衡。
4. KEY分區(qū)
類似于HASH分區(qū)，HASH分區(qū)允許用戶自定義的表達(dá)式，而KEY分區(qū)則不允許使用用戶自定義的表達(dá)式；HASH分區(qū)只支持整數(shù)分區(qū)，KEY分區(qū)支持除了blob和text類型之外的其他數(shù)據(jù)類型分區(qū)。
與HASH分區(qū)不同，創(chuàng)建KEY分區(qū)表的時(shí)候，可以不指定分區(qū)鍵，默認(rèn)會(huì)選擇使用主鍵或唯一鍵作為分區(qū)鍵，沒(méi)有主鍵或唯一鍵，就必須指定分區(qū)鍵。
CREATE TABLE tbl_test (
uuid INT NOT NULL,
title VARCHAR(20)
))
PARTITION BY LINEAR Key (uuid)
PARTITIONS 3;
解讀：根據(jù)分區(qū)鍵來(lái)進(jìn)行分區(qū)
5. 子分區(qū)
子分區(qū)是分區(qū)表中，每個(gè)分區(qū)的再次分割，適合保存非常大量的數(shù)據(jù)。
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
);
解讀：主分區(qū)使用RANGE按照年來(lái)進(jìn)行分區(qū)，有3個(gè)RANGE分區(qū)。這3個(gè)分區(qū)中又被進(jìn)一步分成了2個(gè)子分區(qū)，實(shí)際上，整個(gè)表被分成了3 * 2 = 6個(gè)分區(qū)。每個(gè)子分區(qū)按照天進(jìn)行HASH分區(qū)。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。
注意：
(1) 在MySQL5.1中，對(duì)于已經(jīng)通過(guò)RANGE或LIST分區(qū)了的表在進(jìn)行子分區(qū)是可能的。子分區(qū)既可以使用HASH分區(qū)，也可以使用KEY分區(qū)。這也被稱為復(fù)合分區(qū)。
(2) 每個(gè)分區(qū)必須有相同數(shù)量的子分區(qū)。
(3) 如果在一個(gè)分區(qū)表上的任何分區(qū)上使用SUBPARTITION來(lái)明確定義任何子分區(qū)，那么就必須定義所有的子分區(qū)。
(4) 每個(gè)SUBPARTITION子句必須包含（至少）子分區(qū)的一個(gè)名字。
(5) 在每個(gè)子分區(qū)內(nèi)，子分區(qū)的名字必須是惟一的，目前在整個(gè)表中，也要保持唯一。例如：
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
)
)
子分區(qū)可以用于特別大的表，可以在多個(gè)磁盤間分配數(shù)據(jù)和索引。例如：
SUBPARTITION s0
DATA DIRECTORY = '/disk0/data'
INDEX DIRECTORY = '/disk0/idx'
,
,
SUBPARTITION s1
DATA DIRECTORY = '/disk1/data'
INDEX DIRECTORY = '/disk1/idx'
九、MySQL分區(qū)處理NULL值的方式
MySQL中的分區(qū)禁止空值NULL上沒(méi)有進(jìn)行處理，無(wú)論它是一個(gè)列值還是一個(gè)用戶定義表達(dá)式的值，一般而言，在這種情況下MySQL把NULL視為0。如果你希望回避這種做法，你應(yīng)該在設(shè)計(jì)表時(shí)聲明列“NOT NULL”。
十、分區(qū)管理概述
可以對(duì)分區(qū)進(jìn)行添加、刪除、重新定義、合并或拆分等管理操作。
① RANGE和LIST分區(qū)的管理
1. 刪除分區(qū)語(yǔ)句如：alter table tbl_test drop partition p0;
注意：
(1) 當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。
(2) 可以通過(guò)show create table tbl_test;來(lái)查看新的創(chuàng)建表的語(yǔ)句。
(3) 如果是LIST分區(qū)的話，刪除的數(shù)據(jù)不能新增進(jìn)來(lái)，因?yàn)檫@些行的列值包含在已經(jīng)刪除了的分區(qū)的值列表中。
2. 添加分區(qū)語(yǔ)句如：alter table tbl_test add partition(partition p3 values less than(50));
注意：
(1) 對(duì)于RANGE分區(qū)的表，只可以添加新的分區(qū)到分區(qū)列表的最高端。
(2) 對(duì)于LIST分區(qū)的表，不能添加已經(jīng)包含在現(xiàn)有分區(qū)值列表中的任意值。
3. 如果希望能不丟失數(shù)據(jù)的條件下重新定義分區(qū)，可以使用如下語(yǔ)句：
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)
(1) 拆分分區(qū)如：
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) 合并分區(qū)如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));
4. 刪除所有分區(qū)，但保留數(shù)據(jù)，形式：ALTER TABLE tbl_name remove partitioning;
② HASH和KEY分區(qū)的管理
1. 減少分區(qū)數(shù)量語(yǔ)句如：ALTER TABLE tbl_name COALESCE PARTITION 2;
2. 添加分區(qū)數(shù)量語(yǔ)句如：ALTER TABLE tbl_name add PARTITION partitions 2;
③ 其他分區(qū)管理語(yǔ)句
1. 重建分區(qū) ：類似于先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，可用于整理分區(qū)碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;
2. 優(yōu)化分區(qū) ：如果從分區(qū)中刪除了大量的行，或者對(duì)一個(gè)帶有可變長(zhǎng)度的行（也就是說(shuō)，有VARCHAR，BLOB或TEXT類型的列）做了許多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION來(lái)收回沒(méi)有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;
3. 分析分區(qū) ：讀取并保存分區(qū)的鍵分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;
4. 檢查分區(qū) ：檢查分區(qū)中的數(shù)據(jù)或索引是否已經(jīng)被破壞，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;
5. 修補(bǔ)分區(qū) ：修補(bǔ)被破壞的分區(qū)，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;
十、查看分區(qū)信息
1. 查看分區(qū)信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G;
2. 查看分區(qū)上的數(shù)據(jù)：select * from tbl_test partition(p0);
3. 查看MySQL會(huì)操作的分區(qū)：explain partitions select * from tbl_test where uuid = 2;
十一、 局限性
1. 最大分區(qū)數(shù)目不能超過(guò)1024，一般建議對(duì)單表的分區(qū)數(shù)不要超過(guò)50個(gè)。
2. 如果含有唯一索引或者主鍵，則分區(qū)列必須包含在所有的唯一索引或者主鍵在內(nèi)。
3. 不支持外鍵。
4. 不支持全文索引，對(duì)分區(qū)表的分區(qū)鍵創(chuàng)建索引，那么這個(gè)索引也將被分區(qū)。
5. 按日期進(jìn)行分區(qū)很合適，因?yàn)楹芏嗳掌诤瘮?shù)可以用。但是對(duì)字符串來(lái)說(shuō)合適的分區(qū)函數(shù)不太多。
6. 只有RANGE和LIST分區(qū)能進(jìn)行子分區(qū)，HASH和KEY分區(qū)不能進(jìn)行子分區(qū)。
7. 臨時(shí)表不能被分區(qū)。
8. 分區(qū)表對(duì)于單條記錄的查詢沒(méi)有優(yōu)勢(shì)。
9. 要注意選擇分區(qū)的成本，沒(méi)插入一行數(shù)據(jù)都需要按照表達(dá)式篩選插入的分區(qū)。
10. 分區(qū)字段盡量不要可以為null
重慶分公司
重慶分公司
