2015年7月6日 星期一

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語句在新舊資料表的性能差異。



參考:
MySQL ::   MySQL 5.7 Reference Manual :: 18 Partitioning
http://twpug.net/docs/mysql-5.1/partitioning.html
MYSQL的分区字段,必须包含在主键字段内
RANGE和LIST分區的管理

沒有留言:

張貼留言