隨著業務需求的增加，資料量會伴隨著系統成長而增長。在儲存的資料越來越多的況下，勢必會產生一些存取效能上的問題。這時除了建立合適的索引之外，對於數據量多的資料表可透過將資料分割，表切分區的方式維護做查詢優化，使查詢時不但能縮小資料範圍(只要搜尋指定範圍內的分區資料)，還能方便管理舊資料的刪除作業(指定分區刪除)。

範例表結構:

CREATE TABLE `act` (
  `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活動ID',
  `status` bigint(20) DEFAULT NULL COMMENT '活動狀態 0(下架) 1(上架) 2(準備中)',
  `start_time` int(11) unsigned NOT NULL,
  `end_time` int(11) unsigned NOT NULL,
  `platform_id` bigint(20) DEFAULT NULL,
  `updated_at` int(11) unsigned NOT NULL,
  `created_at` int(11) unsigned NOT NULL,
  PRIMARY KEY (`actives_id`)
);

注意:

1. 當表有Primary key或unique index的狀況下，使用於分區的欄位必須包含在其中，不然會無法建立。
   

2. 當要插入的數據值超出partition設定範圍時是無法INSERT的。
   example.

介紹一下 MYSQL Partition以下種類:

• range

  • Range partition : 一般最常用的，根據定義的partition key做範圍分區。

  Example. 針對actives_id定義範圍做分區。

  ALTER TABLE act
  PARTITION BY RANGE (actives_id) (
      PARTITION p0 VALUES LESS THAN (5),
      PARTITION p1 VALUES LESS THAN (10),
      PARTITION p2 VALUES LESS THAN (15),
      PARTITION pmax VALUES LESS THAN MAXVALUE
  );
  

  • 根據以上設定，資料分區儲存的範圍(ps.目前表中已有10筆數據，看一下各筆資料分布。)

  查詢分區內資料:

  SELECT * FROM table_name PARTITION (partition_name);
  

  分區名稱	值範圍

  p0 | <5
  p1 | 5 ~ <10
  p2 | 10 ~ <15
  pmax | 15之後的值
  

  • 當我們查詢條件有包含到partition key時，語法分析中的partition欄位就會顯示使用了哪個分區內的資料去過濾縮小資料範圍的。

  

  • MYSQL數據目錄下將資料表分區(前/後)資料儲存的變化～
    

• list

  • List partition: 用選擇欄位的值選項做資料分類的分區。

  Example. 使用act表中的status欄位(判斷活動目前狀態)做list分區。

  (1.) 先將Primary key改成複合式PK -> (actives_id,status)

  ALTER TABLE `t2`.`act` DROP PRIMARY KEY, ADD PRIMARY KEY (`actives_id`,     `status`);
  

  (2.) 新增list分區

  ALTER TABLE act
  PARTITION BY LIST (status) (
     PARTITION p0 VALUES IN (0),
     PARTITION p1 VALUES IN (1),
     PARTITION p2 VALUES IN (2),
     PARTITION p3 VALUES IN (4,5)
  );
  

  (3.) 測試資料確認

  INSERT INTO `act` ( `status`, `start_time`, `end_time`, `platform_id`, `updated_at`, `created_at`) VALUES ( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '5', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'); 
  

  

• 分區使用，試一下比較差異點

刪除單一分區(該分區內資料會被刪除):

ALTER TABLE act DROP PARTITION p0;   //partition & 數據都被刪除

mysql> SELECT * FROM act PARTITION (p0);
ERROR 1735 (HY000): Unknown partition 'p0' in table 'act'

移除表分區定義(現有資料不會被刪除):

ALTER TABLE act REMOVE PARTITIONING;   //變回未分區前的表結構

查詢各分區資訊:

語法:
select table_schema, table_name, partition_name,partition_method, partition_description, table_rows from information_schema.partitions where table_schema = 'database_name' and table_name = 'table_name';

mysql> select table_schema, table_name, partition_name,partition_method, partition_description, table_rows from information_schema.partitions where table_schema = 'active' and table_name = 'act';
+--------------+------------+----------------+------------------+-----------------------+------------+
| table_schema | table_name | partition_name | partition_method | partition_description | table_rows |
+--------------+------------+----------------+------------------+-----------------------+------------+
| t1l          | act        | p1             | RANGE            | 10                    |          5 |
| t1l          | act        | p2             | RANGE            | 15                    |          0 |
| t1l          | act        | pmax           | RANGE            | MAXVALUE              |          0 |
+--------------+------------+----------------+------------------+-----------------------+------------+
3 rows in set (0.01 sec)

今天介紹了幾個分區使用，明天繼續把剩下的介紹完～ 在來透過procedure實作我們的自動切表流程。