MySQL資料表range分區例子

標籤：charset   更改   value   表分區   create   har   cti   partition   查看   

某些行業資料量的增長速度極快，隨著資料庫中資料量的急速膨脹，資料庫的插入和查詢效率越來越低。此時，除了程式碼和查詢語句外，還得在資料庫的結構上做點更改；在一個主讀輔寫的資料庫中，當資料表資料超過1000w行後，那查詢效率真的很讓人抓狂。就算早前建了索引，也很難滿足使用者對於系統查詢效率的體驗。

最佳化方案是分表或分區。至於分區的原理以及分區和分表的區別，搜尋一下，都介紹的很詳細，這裡就不作冗餘介紹。簡單來講，分表旨在提高資料庫的並發能力，分區旨在最佳化磁碟的IO和資料的讀寫，所以採用什麼方案，還得根據業務再作斟酌。由於我們的系統對並發要求不高，所以便採用了分區。

分區是MySQL5.1以後實現的。其中分區類型有RANGE分區、LIST分區、HASH分區、KEY分區。我們這裡是使用RANGE分區來講解。

分區需要注意的一點是：要麼不定義主鍵，要麼把分區欄位添加到主鍵中。並且分區欄位不能為NULL，要不然就難以確定分區範圍。所以要設為NOT NULL。

首先執行一下show plugins; 查看partition這一欄是否為ACTIVE，是則表示資料庫支援分區。

1、建立一個資料表並分區：

CREATE TABLE ` table_name` (

  `id` INT(11) NOT NULL AUTO_INCREMENT,

  `uid` VARCHAR(50) DEFAULT NULL,

  `action` VARCHAR(10) DEFAULT NULL,

  ` channel` VARCHAR(20) DEFAULT NULL,

  `count_left` INT(11) DEFAULT NULL,

  `end_time` INT(11) DEFAULT ‘0‘,

  PRIMARY KEY (`id`,`end_time`),

  KEY `time` (`end_time`)

) ENGINE=MYISAM DEFAULT CHARSET=utf8

PARTITION BY RANGE(`end_time`) (

    PARTITION p161130 VALUES LESS THAN (1480550399),

    PARTITION p161231 VALUES LESS THAN (1483228799),

    PARTITION p170131 VALUES LESS THAN (1485907199),

    PARTITION p170228 VALUES LESS THAN (1488326399),

    PARTITION p170331 VALUES LESS THAN (1491004799),

    PARTITION p170430 VALUES LESS THAN (1493596799),

    PARTITION p170531 VALUES LESS THAN (1496275199),

    PARTITION p170631 VALUES LESS THAN (1498867199),

    PARTITION pnow VALUES LESS THAN MAXVALUE

);

 

2、修改一個資料表分區：

ALTER TABLE `table_name`

PARTITION BY RANGE(`end_time`) (

      PARTITION p161130 VALUES LESS THAN (1480550399),

　　PARTITION p161231 VALUES LESS THAN (1483228799),

　　PARTITION p170131 VALUES LESS THAN (1485907199),

　　PARTITION p170228 VALUES LESS THAN (1488326399),

　　PARTITION p170331 VALUES LESS THAN (1491004799),

　　PARTITION p170430 VALUES LESS THAN (1493596799),

　　PARTITION p170531 VALUES LESS THAN (1496275199),

　　PARTITION p170631 VALUES LESS THAN (1498867199),

　　PARTITION pnow VALUES LESS THAN MAXVALUE

);

 

說明：1、2中使用end_time (時間是以時間戳記的形式記錄的) 作為分區欄位對錶進行分區。分區的區分值為分區名中的時間的時間戳記形式，比如2016/11/30 23:59:59 轉為秒數為1480550399。以上的代碼中，我將資料表分為9個區，從16年11月30日到17年06月31日 共8個區加上pnow這個區存放17年6月31日以後的資料；如上所示，16年11月30日以前的資料，將會存放在p161130這個分區中16年12月01日至16年12月31日的資料將會存放在p161231分區中，以此類推…

 

分區後可以執行以下語句查看效果（後面也可以用該語句查看每個分區中有多少資料）：

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘table_name‘;

 

3、刪除一個分區：

執行語句：ALTER TABLE table_name DROP PARTITION p_name;

注意：刪除一個分區時，該分區內的所有資料也都會被刪除；

如果用這樣來刪除資料，要比用delete from table_name where …要有效得多；

 

4、新增一個分區：

執行語句：ALTER TABLE table_name ADD PARTITION (PARTITION p_name VALUES LESS THAN (xxxxxxxxx));

注意：如果原先最後一個分區是PARTITION pnow VALUES LESS THAN MAXVALUE; 那麼應該先刪除該分區，然後在執行新增分區語句，然後再新增回該分區；

 

MySQL資料表range分區例子