MySQL分區表的使用

确認MySQL伺服器是否支援分區表

需要有上圖紅色标記這個參數

MySQL分區表的特點

在邏輯上為一個表，在實體上存儲在多個檔案中

CREATE TABLE `user_login_log`(
`user_id` INT(0) UNSIGNED NOT NULL COMMENT '使用者登入id',
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '使用者登入時間',
`login_ip` INT(10) UNSIGNED NOT NULL COMMENT '使用者登入ip',
`login_type`TINYINT(4) NOT NULL COMMENT '登入類型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='使用者登入日志表'
PARTITION BY HASH(user_id)
    PARTITIONS 4
           

非分區表中包含的檔案

user_login_log.frm 結構檔案

user_login_log.ibd 資料檔案

分區表中包含的檔案

user_login_log.frm 結構檔案

user_login_log#P#p0.ibd 資料檔案

user_login_log#P#p1.ibd 資料檔案

user_login_log#P#p2.ibd 資料檔案

user_login_log#P#p3.ibd 資料檔案

按HASH分區

HASH分區的特點

• 根據MOD(分區鍵,分區數)的值把資料行存儲到表的不同分區中
• 資料可以平均的分布到各個分區中
• HASH分區的鍵值必須是一個INT類型的值，或是通過函數可以轉為INT整型

按範圍分區（RANGE）

RANGE分區特點

• 根據分區鍵值的範圍把資料行存儲到表的不同分區中
• 多個分區的範圍要連續，但是不能重疊
• 預設情況下使用VALUES LESS THAN 屬性，即每個分區不包括指定的那個值

CREATE TABLE `user_login_log`(
`user_id` INT(0) UNSIGNED NOT NULL COMMENT '使用者登入id',
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '使用者登入時間',
`login_ip` INT(10) UNSIGNED NOT NULL COMMENT '使用者登入ip',
`login_type`TINYINT(4) NOT NULL COMMENT '登入類型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='使用者登入日志表'
PARTITION BY RANGE (user_id)
    PARTITION p0 VALUES LESS THAN(10000), //0-9999
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN MAXVALUE;	//所有大于30000的數(需要注意 )
           

RANGE分區的适用場景

• 分區鍵為日期或是時間類型
• 所有查詢中都包括分區鍵
• 定期按分區範圍清理曆史資料

LIST分區

LIST分區特點

• 按分區鍵取值的清單進行分區
• 同範圍分區一樣，各分區的清單值不能重複
• 每一行資料必須能找到對應的分區清單，否則資料插入失敗

CREATE TABLE `user_login_log`(
`user_id` INT(0) UNSIGNED NOT NULL COMMENT '使用者登入id',
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '使用者登入時間',
`login_ip` INT(10) UNSIGNED NOT NULL COMMENT '使用者登入ip',
`login_type`TINYINT(4) NOT NULL COMMENT '登入類型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='使用者登入日志表'
PARTITION BY LIST (login_type)
    PARTITION p0 VALUES in (1,3,5,7,9),
    PARTITION p1 VALUES in (2,4,6,8),
           

檢視分區表資料

SELECT
	table_name,
	partition_name,
	partition_description,
	table_rows,
FROM
	information_schema.`PARTITIONS`
WHERE table_name = 'user_login_log';
           

添加分區

删除分區

ALTER TABLE user_login_log DROP PARTITION p0 
           

曆史資料歸檔分區遷移

• 隻支援5.7以上的版本
• 先建立一個結構一樣的非分區表

ALTER TABLE user_login_log exchange PARTITION p1 WITH TABLE temp_user_login_log
           

使用分區表注意事項

• 結合業務場景選擇分區鍵，避免跨分區查詢
• 對分區表進行查詢最好在WHERE從句中包含的分區鍵
• 具有主鍵或唯一索引的表，主鍵或唯一索引必須是分區鍵的一部分