mysql分區之range分割區的詳細介紹

隨著網路的發展，各方面的數據越來越多，從最近兩年大數據越來越強的呼聲中就可見一斑。 

我們所做的項目雖然不算是什麼大項目，但由於業務量的問題，資料也是相當的多。
資料一多，就很容易出現效能問題，而為了解決這個問題我們通常很容易想到叢集、分片等。
但是在某些時候卻不一定必須用群集、分片，也可以適當的使用資料分區。

什麼是分割區？

MySQL在未啟用分區功能時，資料庫的單一表格內容是以單一檔案的形式存放在檔案系統上的。當啟用分區功能後，MySQL將依照使用者指定的規則將單一表格內容分割成幾個檔案存放在檔案系統上。分區分為水平分區和垂直分區，水平分區是將表的資料按行分割成不同的資料文件，而垂直分區則是將表的資料按列分割成不同的資料文件。分片要遵循完備原則、可重構性原則與不相交原則。完備性代表所有資料必須映射到某個片段。可重構性表示所有分片資料必須可以重新構成全域資料。不相交性表示不同分片上的資料沒有重複（除非你是刻意做的冗餘）。

大概是介於各方面的考慮，我們用的的表中就用到了range分區，資料庫是其他人在管理，但是因為用到了這個表，因此我便抽時間進行了簡單的學習。

據我的了解，要使用分區的話，必須在創建表結構的時候就使用創建分區的語句，不能再後期更改。
例如我建立一個簡單的emp表，有id、name、age三個字段，然後根據id分區。正確的建表語句基本上如下：

CREATE TABLE emp(
id INT NOT NULL,
NAME VARCHAR(20),
age INT)
PARTITION BY RANGE(ID)(
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION pmax VALUES LESS THAN maxvalue
);

這裡我是設定把整個表的資料分成三個區，id小於6的是一個區，區名稱p0；id介於6到11的屬於一個區，區名稱p1；則所有id大於11的一個區，區名稱pmax。
整理一個語法，基本上如下：

create table tablename（
 字段名 数据类型...）
partition by range(分区依赖的字段名)（
partition 分取名 values less than （分区条件的值），...）

這裡要注意的是例子中的最後一行partition pmax values less than maxvalue，這一句中只有代表分區名的pmax是可以自己任意取得，剩下的單字不能變，maxvalue代表上邊分區條件的最大值。
這樣的話能保證所有資料都能正常入庫，否則，假如沒有這一句的話，那麼id大於等於11的資料便無法存入庫中，將會報錯。

表格結構創建好以後，為了測試分區是否成功，我向表中插入了一些數據，語句如下：

INSERT INTO emp VALUES(1,'test1',22);INSERT INTO emp VALUES(2,'test2',25);INSERT INTO emp VALUES(3,'test3',27);
INSERT INTO emp VALUES(4,'test4',20);INSERT INTO emp VALUES(5,'test5',22);INSERT INTO emp VALUES(6,'test6',25);
INSERT INTO emp VALUES(7,'test7',27);INSERT INTO emp VALUES(8,'test8',20);INSERT INTO emp VALUES(9,'test9',22);
INSERT INTO emp VALUES(10,'test10',25);INSERT INTO emp VALUES(11,'test11',27);INSERT INTO emp VALUES(12,'test12',20);
INSERT INTO emp VALUES(13,'test13',22);INSERT INTO emp VALUES(14,'test14',25);INSERT INTO emp VALUES(15,'test15',27);
INSERT INTO emp VALUES(16,'test16',20);INSERT INTO emp VALUES(17,'test17',30);INSERT INTO emp VALUES(18,'test18',40);
INSERT INTO emp VALUES(19,'test19',20);

數據插入完成後，要驗證是否對應id的數據保存在了對應的分區，可以使用查詢分區的命令，如下：

SELECT partition_name,partition_expression,partition_description,table_rows 
FROM information_schema.PARTITIONS 
WHERE table_schema = SCHEMA() AND table_name='emp'

查詢出的結果如圖：

可以看出partition_name是分區名，partition_expression是分區依賴的字段，partition_description可以理解成該分區的條件，table_rows表示該分區中現在有的資料量。

從上邊的資料可以看出分區是成功的，但是如上分區雖然可以避免無法插入的問題，卻又出現了一個新的問題。
那就是最後一個pmax區的資料有可能非常的大，這樣一來，資料並不平均，不成比例，有可能使得查詢最後一個區的資料時依舊出現效能問題。所以，解決辦法大致有這樣三個：

一是在能控制分區字段資料的情況下，比如說這裡的id，假如能明確的知道什麼時候會是多大的值，那麼就可以一開始的時候不要這個pmax，而是定期的增加分區。例如這裡存在了p0、p1，那麼可以在id即將到達11的時候增加p2、p3甚至更多。增加分區的語句範例如下：

ALTER TABLE emp ADD PARTITION(PARTITION p2 VALUES LESS THAN (16))

語法整理就是：

alter table tablename add partition（partition 分区名 values lessthan （分区条件））

上邊這個辦法可以解決資料不成比例的這個問題，只不過也同時存在隱患，那就是假如什麼時候忘了增加後邊的分區，亦或者說是分區依賴的字段值超出了預料，那麼就又可能導致資料無法入庫的問題。這樣一來又有兩種方法可以解決：
一是可以使用mysql的事務機制和預存程序等，做一個mysql的定時任務，然後使資料庫系統自己在特定的時間增加分區。這樣一來基本上不會出現第一個方法所說的問題，只不過這種方法需要對mysql的事務和預存程序也有一定的理解，操作起來有一定的難度。
我知道這個方法，暫時還沒有著手去實現，等後邊進一步了解事務和預存程序後再給出相關的例子。

那麼除開上邊這種定時任務的方法外，還有一個就是拆分分區的辦法，也就是還是使用之前有pmax分區的這個表結構，然後用拆分分區的語句來拆分pmax。範例如下：

ALTER TABLE emp REORGANIZE PARTITION pmax INTO(
PARTITION p2 VALUES LESS THAN (16),
PARTITION pmax VALUES LESS THAN maxvalue
)

然后我们再用查询分区情况的语句查询，便可以看到结果变成这样：

很显然，多出来了一个p2分区，拆分成功的同事不影响其他的功能。
那么这里分区拆分的语法整理如下：

alter table tablename reorganize partition 要拆分的分区名 into（
partition 拆分后的分区名1 values less than （条件），
partition 拆分后的分区名2 values lessthan （条件），...)

好了，到这里基本上算是完成了，但是我们知道数据库一般的操作都是增删改查，我们这里已经有了增改查，却自然也不能少了删。
按理说正常的生产环境的数据库应该是不能随意删除数据的，但是并不代表就不能删，反而有的时候还必须要删。
就比如我们项目中那个库，由于数据量太大，即便是分区了也依旧会在大量数据的情况下变慢。而与此同时，我们是按时间分区的，实际使用过程中只需要用到几天的数据，那么实际上很早以前的数据是可以删除不要的，或者说备份以后删除这个表的，这样就需要用到删除语句。
当然了，删除可以用delete，但是这样的话分区信息还在库中，实际上也是没必要要的，完全可以直接删除分区，因为删除分区的时候也同时会删除这个区内的所有数据。
示例之前我们先查一下之前插入的所有数据，如图：

这里示例删除p0分区代码如下：

ALTER TABLE emp  DROP PARTITION p0

然后先用查询分区的代码看一下，如图

可以看到p0区不见了，在select * 一下，如图：

可以看到id小于6的数据已经没有了，数据删除成功。

 以上就是mysql分区之range分区的详细介绍的内容，更多相关内容请关注PHP中文网（www.php.cn）！