***** ## MySQL分區表 ### 分區表的特點 在邏輯上為一個表,在物理上存儲在多個文件中 ``` create table `login_log`( login_id int(10) unsigned not null comment '登錄用戶id', login_time timestamp not null default current_timestamp, login_ip int(10) unsigned not null comment '登錄類型' )engine=innodb default charset=utf8 partition by hash(login_id) partitions 4; ``` ### 分區鍵 分區引入了分區鍵的概念,分區鍵用于根據某個區間值、特定值、或者HASH函數值執行數據的聚集,讓數據根據規則分布在不同的分區中。 ### 分區類型 * RANGE分區 * LIST分區 * HASH分區 無論那種分區類型,要么分區表上沒有主鍵/唯一鍵,要么分區表的主鍵/唯一鍵都必須包括分區鍵,也就是說不能使用主鍵/唯一字段之外的其他字段分區 ### RANGE分區 #### RANGE分區特點 * 根據分區鍵值的范圍把數據行存儲到表的不同分區中 * 多個分區的范圍要連續,但是不能重疊 * 分區不包括上限,取不到上限值 #### 建立RANGE分區 ~~~ create table `login_log_range`( login_id int(10) unsigned not null comment '登錄用戶ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登錄ip' )engine=innodb partition by range(login_id)( partition p0 values less than(10000), # 實際范圍0-9999 partition p1 values less than(20000), # 實際范圍10000-19999 partition p2 values less than(30000), partition p3 values less than maxvalue # 存儲大于30000的數據 ); ~~~ #### RANGE分區使用場景 * 分區鍵為日期或是時間類型 * 經常運行包含分區鍵的查詢,MySQL可以很快的確定只有某一個或某些分區需要掃描,例如檢索商品login\_id小于10000的記錄數,MySQL只需要掃描p0分區即可 * 定期按分區范圍清理歷史數據 ### HASH分區 #### HASH分區的特點 * 根據MOD(分區鍵,分區值)的值把數據行存儲到表的不同分區內 * 數據可以平均的分布在各個分區中 * HASH分區的鍵值必須是一個INT類型的值,或是通過函數可以轉為INT類型 #### 如何建立HASH分區表 ~~~ create table `login_log`( login_id int(10) unsigned not null comment '登錄用戶ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登錄ip' )engine=innodb default charset=utf8 partition by hash(login_id) partitions 4; create table `login_log`( login_id int(10) unsigned not null comment '登錄用戶ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登錄ip' )engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(login_time)) partitions 4; ~~~ ### LIST分區 #### LIST分區特點 * 按分區鍵取值的列表進行分區 * 同范圍分區一樣,各分區的列表值不能重復 * 每一行數據必須能找到對應的分區列表,否則數據插入失敗 #### 建立LIST分區 ~~~ create table `login_log_list`( login_id int(10) unsigned not null comment '登錄用戶ID', login_time timestamp not null default CURRENT_TIMESTAMP, login_ip int(10) unsigned not null comment '登錄ip', login_type int(10) not null )engine=innodb partition by list(login_type)( partition p0 values in(1,3,5,7,9), partition p1 values in(2,4,6,8) ); ~~~ ### 如何選擇合適的分區方式 業務場景 1.用戶每次登陸都會記錄到日志表中 2.用戶登錄日志保存一年,一年后可以刪除 ~~~ create table `login_log_range`( login_id int(10) unsigned not null comment '登錄用戶id', login_time datetime not null default current_timestamp, login_ip int(10) unsigned not null comment '登錄ip' )engine=innodb partition by range(year(login_time))( partition p0 values less than(2015), partition p1 values less than(2016), partition p2 values less than(2017) ); ~~~ 插入數據 ~~~ insert into login_log_range values (1,'2015-01-25',1), (2,'2015-07-25',2), (3,'2015-06-25',3), (4,'2016-03-25',2), (5,'2016-02-25',1); ~~~ 查詢表 ~~~ select table_name,partition_name,partition_description,table_rows from information_schema.`partitions` where table_name = 'login_log_range' ~~~ ### 修改分區-添加分區 ~~~ alter table login_log_range add partition (partition p4 values less than(2018)) ~~~ ### 分區刪除 ~~~ alter table login_log_range drop partition p0; ~~~ ### 使用分區表的注意事項 * 結合業務場景選擇分區鍵,避免跨分區查詢 * 對分區表進行查詢最好在where從句中包含分區鍵 * 具有主鍵或唯一索引的表,主鍵或唯一索引必須是分區鍵的一部分