XYZ的筆記本: MySQL 資料表分區(partition)

當資料表的資料越來越多，檔案越來越大，存取速度越來越慢。
這時，MySQL 可以使用分區(partition)的功能，進行優化、加快速度。

分區(partition)是將同一個資料表的資料，分成不同小檔案儲存，甚至可以指定每個小檔案儲存的位置，例如放在不同磁碟，增加存取的速度。

[Partition 的使用方式]
若安裝的 MySQL 有支援 Partition，只須在建立資料表時，設定好分區規則，
例如將 2015年1月的資料獨立放一個檔案、2015年2月的資料獨立放一個檔案....
如此 MySQL 便會依據設定的規則，將資料存放到適當的檔案，
之後當我們想查詢 2015年1月的資料時，
MySQL 會直接到存放 2015年1月資料的檔案過濾，而不用處理所有資料，速度自然加快了。

[檢查是否有支援 Partition]
方法一：

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| ....................................(略)...................................... |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+

方法二：查看 INFORMATION_SCHEMA.PLUGINS 資料表

mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
|...................(略)..................|
| partition          | 1.0     | ACTIVE   |
+--------------------+---------+----------+

註1：MySQL 5.6.1 之前的版本，可查看 have_partitioning 變數，但較新的版本此變數已被移除。

mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+

註2：自行編譯 mysql 時，開啟 partition 支援的方法
http://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html#option_cmake_storage_engine_options

[Partition 啟動與關閉]
啟動：若安裝的 mysql 有支援 partition，不須特別在 my.cnf 做任何設定，預設即會啟用。
關閉：若要關閉 partition 支援，可在 my.cnf 加上 skip-partition，再重新啟動。

[Partition分區種類 (分區規則資料表建立範例)]

RANGE 分區(RANGE Partitioning)：連續值橫向分區
```
CREATE TABLE aa (
    id INT NOT NULL,
    data varchar(255) NOT NULL
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (11),
    PARTITION p1 VALUES LESS THAN (21),
    PARTITION p2 VALUES LESS THAN (31),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
```
id：< 11，分在 p0 區
id：11~20，分在 p1 區
id：21~30，分在 p2 區
id：31~最大值，分在 p3 區
如果寫入的資料不在這些範圍會出錯。

資料表建好後，到硬碟查看，
若是MyISAM，可發現產生4組分區的檔案如下
aa#P#p0.MYD、aa#P#p0.MYI
aa#P#p1.MYD、aa#P#p1.MYI
aa#P#p2.MYD、aa#P#p2.MYI
aa#P#p3.MYD、aa#P#p3.MYI
若是InnoDB，且 innodb_file_per_table 有開啟，產生的4個分區的檔案會如下
aa#p#p0.ibd、aa#p#p1.ibd、aa#p#p2.ibd、aa#p#p3.ibd

註：若用來分區的欄位不是主鍵，會出現錯誤訊息
A PRIMARY KEY must include all columns in the table's partitioning function
解決方法，可將該欄位跟主鍵做成複合鍵。
LIST 分區(LIST Partitioning)：列舉值橫向分區
```
CREATE TABLE aa (
    id INT NOT NULL,
    data varchar(255) NOT NULL
)
PARTITION BY LIST (id) (
    PARTITION pA VALUES IN (1,3,5),
    PARTITION pB VALUES IN (12,15)
);
```
id 等於 1、3、5，分在 pA 區
id 等於 12、15，分在 pB 區
如果寫入的資料不在這些範圍會出錯。
HASH 分區：讓資料在設定好的分區均勻分布
```
CREATE TABLE aa (
    id INT NOT NULL,
    mydate DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY HASH(id)
PARTITIONS 3;
```
依據 mydate 進行 HASH 分區在 3 個 partition。

也可以使用函式，例如 PARTITION BY HASH(YEAR(mydate))
```
CREATE TABLE aa (
    id INT NOT NULL,
    mydate DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY HASH(YEAR(mydate))
PARTITIONS 3;
```
依據 YEAR(mydate) 進行 HASH 分區在 3 個 partition，但每次有異動，YEAR()都會執行一次，所以複雜的運算會影響效能。
KEY 分區(KEY Partitioning)：跟 HASH 分區類似，差在 HASH 分區使用使用者自訂的運算方式，KEY 分區使用 MySQL server 內部的運算方式。
```
CREATE TABLE aa (
    mydate DATE NOT NULL DEFAULT '1970-01-01',
    data varchar(255) NOT NULL
)
PARTITION BY KEY(mydate)
PARTITIONS 3;
```

子分區(Subpartitioning)：分區的分區
例如在 Range 分區底下，再建立 Range 分區

CREATE TABLE aa (
    id INT NOT NULL,
    data varchar(255) NOT NULL
)
PARTITION BY RANGE(id)
SUBPARTITION BY RANGE(id)(
    PARTITION p0 VALUES LESS THAN (10)(
        PARTITION s0 VALUES LESS THAN (6),
        PARTITION s1 VALUES LESS THAN MAXVALUE
    ),
    PARTITION p1 VALUES LESS THAN MAXVALUE(
        PARTITION s2 VALUES LESS THAN (30),
        PARTITION s3 VALUES LESS THAN MAXVALUE
    )
);

註：若一個分區有子分區，則其他分區也都要設定同樣數量的子分區。

[RANGE分區管理]

查看分區資訊
```
mysql> SHOW CREATE TABLE aa\G;
```
刪除分區(刪除後，儲存在該分區的資料也會不見)
```
ALTER TABLE aa DROP PARTITION p0;
```

新增分區

ALTER TABLE aa ADD PARTITION (PARTITION p0 VALUES LESS THAN(11));

拆開分區(p0 拆成 so、s1)

ALTER TABLE aa REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (6),
    PARTITION s1 VALUES LESS THAN (11)
);

合併相鄰分區(so、s1 合併成 p0)

ALTER TABLE aa REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (11)
);

[將分區的小檔案存放在不同位置]

用 DATA DIRECTORY、INDEX DIRECTORY 指定分區小檔案存放位置

CREATE TABLE aa (
    id INT NOT NULL,
    data varchar(255) NOT NULL
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (11)
        DATA DIRECTORY = '/test/p0.data'
        INDEX DIRECTORY = '/test/p0.idx',
    PARTITION p VALUES LESS THAN MAXVALUE
        DATA DIRECTORY = '/test/p.data'
        INDEX DIRECTORY = '/test/p.idx'
);

[將現有資料表轉換成有分區結構的資料表]
我是先建好有分區結構的資料表，再將資料複製過去

INSERT 新的分區資料表 SELECT * FROM 原資料表;

轉換完，可以使用explain分析常用的SQ語句在新舊資料表的性能差異。

[分區維護]

重建分區(Rebuilding partitions)：
先刪除保存在分區中的所有記錄，再重新插入。可用於整理分區碎片。
```
ALTER TABLE t1 REBUILD PARTITION p0, p1；
```

最佳化分區(Optimizing partitions)：
若分區中刪除了大量的行，或者對一個帶有可變長度的資料(VARCHAR、BLOB、TEXT)做了許多修改，可用此指令回收未使用到的空間、整理分區檔案碎片，使分區在磁碟中的檔案變小。

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

注意：InnoDB 不支援個別分區的最佳化，當使用執行最佳化時，會將對整個表分析(analyze)與重建(rebuild)，
若要只針對某個分區處理，可用重建分區(Rebuilding partitions)+分析分區(Analyzing partitions)取代。

測試1：對 InnoDB 資料表 p1 分區執行 OPTIMIZE

mysql> ALTER TABLE `aa` OPTIMIZE PARTITION p1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| test.aa | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.aa | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.63 sec)

最佳化執行完，若去看 mysql/data 看該資料庫的 aa 資料表檔案，可發現所有分區檔案的修改時間檔案都異動，因為最佳化指令將整個表都重新分析重建。

測試2：對 InnoDB 資料表 p1 分區先執行 REBUILD，再執行 Analyzing

mysql> ALTER TABLE `aa` REBUILD PARTITION p1;
Query OK, 2 rows affected (0.22 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `aa` ANALYZE PARTITION p1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.aa | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

執行完，若去看 mysql/data 看該資料庫的 aa 資料表檔案，可發現只有 aa#p#p1.ibd 時間檔案有異動，可知只針對 p1 分區處理。

分析分區(Analyzing partitions)：
This reads and stores the key distributions for partitions
```
ALTER TABLE t1 ANALYZE PARTITION p3;
```
修復分區(Repairing partitions)：
修復損壞的分區。
```
ALTER TABLE t1 REPAIR PARTITION p0,p1;
```
若因重複鍵導致失敗(duplicate key)，可用 ALTER IGNORE TABLE 處理，但因重複鍵不能移動的資料，將從分區中移除。
檢查分區(Checking partitions)：
檢查分區是否損壞。
```
ALTER TABLE trb3 CHECK PARTITION p1;
```

參考：
MySQL :: MySQL 5.7 Reference Manual :: 18 Partitioning
http://twpug.net/docs/mysql-5.1/partitioning.html
MYSQL的分区字段，必须包含在主键字段内
RANGE和LIST分區的管理
MySQL :: MySQL 5.7 Reference Manual :: 21.3.4 Maintenance of Partitions
mysql分区表---range partition - 程序园
MySQL分区表的管理~2 - iVictor - 博客园

XYZ的筆記本

網頁

2015年7月6日星期一

MySQL 資料表分區(partition)

沒有留言:

張貼留言

網頁

2015年7月6日 星期一

MySQL 資料表分區(partition)

沒有留言:

張貼留言

2015年7月6日星期一