(3) MySQL分割槽表使用方法

聽風。發表於2019-01-07
MySql

1. 確認MySQL伺服器是否支援分割槽表

命令：

show plugins;

(3) MySQL分割槽表使用方法

2. MySQL分割槽表的特點

  • 在邏輯上為一個表，在物理上儲存在多個檔案中

HASH分割槽（HASH）

HASH分割槽的特點

  • 根據MOD(分割槽鍵，分割槽數)的值把資料行儲存到表的不同分割槽中
  • 資料可以平均的分佈在各個分割槽中
  • HASH分割槽的鍵值必須是一個INT型別的值，或是通過函式可以轉為INT型別

如何建立HASH分割槽表

以INT型別欄位 customer_id為分割槽鍵

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) 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 `登入型別:0未成功 1成功`
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`使用者登入日誌表`

PARTITION BY HASH(customer_id)  PARTITIONS 4;

以非INT型別欄位 login_time 為分割槽鍵(需要先轉換成INT型別)

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) 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 `登入型別:0未成功 1成功`
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`使用者登入日誌表`

PARTITION BY HASH(UNIX_TIMESTAMP(login_time))  PARTITIONS 4;

customer_login_log 表如果不分割槽，在物理磁碟上檔案為

customer_login_log.frm    # 儲存表原資料資訊
customer_login_log.ibd    # Innodb資料檔案

如果按上面的建HASH分割槽表，則有五個檔案

customer_login_log.frm    
customer_login_log#P#p0.ibd
customer_login_log#P#p1.ibd
customer_login_log#P#p2.ibd
customer_login_log#P#p3.ibd

演示

(3) MySQL分割槽表使用方法

(3) MySQL分割槽表使用方法

(3) MySQL分割槽表使用方法

使用起來和不分割槽是一樣的，看起來只有一個資料庫，其實有多個分割槽檔案，比如我們要插入一條資料，不需要指定分割槽，MySQL會自動幫我們處理

(3) MySQL分割槽表使用方法

查詢

(3) MySQL分割槽表使用方法

範圍分割槽（RANGE）

RANGE分割槽特點

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

如何建立RANGE分割槽

(3) MySQL分割槽表使用方法

如果沒有定義p3分割槽，當插入的customer_id大於29999時會報錯，定義了則超過的資料都存入p3中

RANGE分割槽的適用場景

  • 分割槽鍵為日期或是時間型別 （可以使得各個分割槽表的資料比較均衡，如果按上面的例子中以整型id為分割槽鍵，假如活躍使用者集中在10000-19999之間，則p1中的資料量就會比其他分割槽的資料量大很多，這就失去了分割槽的意義；而且按時間型別分割槽，如果要按時間順序進行資料的歸檔，則只需要對某一個分割槽進行歸檔就可以了）
  • 所有查詢中都包括分割槽鍵（避免跨分割槽查詢）
  • 定期按分割槽範圍清理歷史資料

LIST分割槽

LIST分割槽的特點

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

如何建立LIST分割槽

(3) MySQL分割槽表使用方法

如果插入一條login_type為10的資料行，則會報錯

3. 如何為登入日誌表（customer_login_log）分割槽

業務場景

  • 使用者每次登入都會記錄customer_login_log日誌
  • 使用者登入日誌儲存一年，1年後可以刪除或者歸檔

登入日誌表的分割槽型別及分割槽鍵

  • 使用RANGE分割槽
  • 以login_time為分割槽鍵

分割槽後的使用者登入日誌表

按年份分割槽儲存，所以用YEAR函式進行了轉化

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT `登入使用者ID`,
  `login_time` DATETIME NOT NULL COMMENT `使用者登入時間`,
  `login_ip` int(10) unsigned NOT NULL COMMENT `登入IP`,
  `login_type` tinyint(4) NOT NULL COMMENT `登入型別:0未成功 1成功`
) ENGINE=InnoDB 
PARTITION BY RANGE (YEAR(login_time))(
PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN (2019)
)

插入並查詢資料

(3) MySQL分割槽表使用方法

查詢指定表中的分割槽資料情況

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

(3) MySQL分割槽表使用方法

再插入2條18年的日誌，會存入p2表中

(3) MySQL分割槽表使用方法

之前說過建立分割槽表時，最好建立一個MAXVALUE的分割槽，這裡之所以沒有建立，是為了資料維護的方便，如果我們建立了MAXVALUE分割槽，很容易忽視一個問題，當我們2019年有的資料插入時，會自動存入那個MAXVALUE分割槽中，之後在做資料維護時會不方便，所以沒有建立MAXVALUE分割槽
而是通過計劃任務的方式，在每年年底的時候增加這個分割槽，比如我們現在在2018年年底，我們需要在日誌表中為2019年建立日誌分割槽，否則2019年的日誌都會插入失敗

(3) MySQL分割槽表使用方法

我們可以通過下面語句

增加分割槽

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))

增加分割槽，並插入資料

(3) MySQL分割槽表使用方法

刪除分割槽

假如我們現在要刪除2016年到2017年間一年的資料，因為我們已經做了分割槽，所以只需要通過一條語句，刪除p0分割槽即可

ALTER TABLE customer_login_log DROP PARTITION p0;

(3) MySQL分割槽表使用方法

可以發現p0分割槽已被刪除，且2016年的日誌全部被清除了

歸檔分割槽歷史資料

我們可能有另一種需求對資料進行歸檔

Mysql版本>=5.7，歸檔分割槽歷史資料非常方便，提供了一個交換分割槽的方法

分割槽資料歸檔遷移條件：

  1. MySQL>=5.7
  2. 結構相同
  3. 歸檔到的資料表一定要是非分割槽表
  4. 非臨時表；不能有外來鍵約束
  5. 歸檔引擎要是：archive

建表並交換分割槽

CREATE TABLE `arch_customer_login_log` (
  `customer_id` INT unsigned NOT NULL COMMENT `登入使用者ID`,
  `login_time` DATETIME NOT NULL COMMENT `使用者登入時間`,
  `login_ip` INT unsigned NOT NULL COMMENT `登入IP`,
  `login_type` TINYINT NOT NULL COMMENT `登入型別:0未成功 1成功`
) ENGINE=InnoDB ;

ALTER TABLE customer_login_log 
    exchange  PARTITION p1 WITH TABLE arch_customer_login_log;

(3) MySQL分割槽表使用方法

(3) MySQL分割槽表使用方法

(3) MySQL分割槽表使用方法

可以發現，原customer_login_log表中的2017年的資料（p1分割槽中的資料）已轉移到了arch_customer_login_log表中，但是p1分割槽未刪除，只是資料轉移了,所以我們還需要執行DROP命令刪除分割槽，以免有資料插入其中

將歸檔資料的儲存引擎改為歸檔引擎

最後我們將歸檔資料的儲存引擎改為歸檔引擎，命令為

ALTER TABLE customer_login_log  ENGINE=ARCHIVE;

使用歸檔引擎的好處是：它比Innodb所佔用的空間更少，但是歸檔引擎只能進行查詢操作，不能進行寫操作

4. 使用分割槽表的主要事項

  • 結合業務場景選擇分割槽鍵，避免跨分割槽查詢
  • 對分割槽表進行查詢最好在WHERE從句中包含分割槽鍵
  • 具有主鍵或唯一索引的表，主鍵或唯一索引必須是分割槽鍵的一部分（這也是為什麼我們上面分割槽時去掉了主鍵登入日誌id(login_id)的原因，不然就無法按照上面的按年份進行分割槽，所以分割槽表其實更適合在MyISAM引擎中）

關於MyISAM和Innodb的索引區別

1.關於自動增長

myisam引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據前面幾列進行排序後遞增。

innodb引擎的自動增長咧必須是索引，如果是組合索引也必須是組合索引的第一列。

2.關於主鍵

myisam允許沒有任何索引和主鍵的表存在，

myisam的索引都是儲存行的地址。

innodb引擎如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6位元組的主鍵(使用者不可見)

innodb的資料是主索引的一部分，附加索引儲存的是主索引的值。

3.關於count()函式

myisam儲存有表的總行數，如果select count(*) from table;會直接取出出該值

innodb沒有儲存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了wehre 條件後，myisam和innodb處理的方式都一樣。

4.全文索引

myisam支援 FULLTEXT型別的全文索引

innodb不支援FULLTEXT型別的全文索引，但是innodb可以使用sphinx外掛支援全文索引，並且效果更好。（sphinx 是一個開源軟體，提供多種語言的API介面，可以優化mysql的各種查詢）

5.delete from table

使用這條命令時，innodb不會從新建立表，而是一條一條的刪除資料，在innodb上如果要清空儲存有大量資料的表，最 好不要使用這個命令。(推薦使用truncate table，不過需要使用者有drop此表的許可權)

6.索引儲存位置

myisam的索引以表名+.MYI檔案分別儲存。

innodb的索引和資料一起儲存在表空間裡。

相關文章