mysql表分區實驗總結

標籤：mysql 分區表測試總結

mysql表分區技術能有效解決水平分割和垂直分割的不足，可操作性和效率都更優。以下是一些實驗總結。

InnoDB引擎需先在設定檔中設定： innodb_file_per_table=1

--innodb : 主表.frm 儲存表結構和分區數目一致的 .ibd 檔案，用於儲存資料和索引。

--myisam：主表.frm儲存表結構定義，主表.par儲存分區資訊，  和分區數目一致的 .MYD檔案，用於儲存資料，.MDI檔案用於索引。

range分區：

CREATE TABLE a(   

id INT PRIMARY KEY AUTO_INCREMENT,

NAME CHAR(20))ENGINE=INNODB CHARSET=utf8

PARTITION BY RANGE(id)(

PARTITION p1 VALUES  LESS THAN (100),

PARTITION p2 VALUES  LESS THAN (200),

PARTITION p3 VALUES  LESS THAN (300),

PARTITION p4 VALUES  LESS THAN  MAXVALUE);

 ----------建立一個以id 區間來劃分的分別，當id 小於100時資料儲存到p1分區，100到199時儲存到p2分區，200到299時儲存到p3分區，大於300時儲存到p4分區； 

[[email protected] test]#dir

a.frm  a#P#p1.ibd  a#P#p2.ibd  a#P#p4.ibd  db.opt 

List分區：

CREATE TABLE bc(

id INT NOT NULL AUTO_INCREMENT,

par_no INT NOT NULL DEFAULT '1',

a_name CHAR(20) NOT NULL,

PRIMARY KEY(id,par_no)) ENGINE=MYISAM CHARSET=gbk PARTITION BY LIST(par_no)(

PARTITION p0 VALUES IN (10,20,30),

PARTITION p1 VALUES IN (40,50,60),

PARTITION p2 VALUES IN (70,80,100));

---插入資料時，par_no的值必須在分區定義中存在，否則不能插入並報錯。

[[email protected] test]# dir nb*

nb.frmnb.parnb#P#p0.MYD  nb#P#p0.MYI  nb#P#p1.MYD  nb#P#p1.MYI  nb#P#p2.MYD  nb#P#p2.MYI  nb#P#p3.MYD  nb#P#p3.MYInb#P#p4.MYD  nb#P#p4.MYI

提示警告：意思大概mysql分區以後的版本不支援myisam引擎吧，換成innodb就可以了。

Warning Code : 1287

The partition engine, used by table 'test.bc', is deprecated and will be removed in a future release. Please use native partitioning instead.

           
Hash分區：

CREATE TABLE nb(

id INT NOT NULL AUTO_INCREMENT,

par_no INT NOT NULL DEFAULT '1',

a_name CHAR(20) NOT NULL,

PRIMARY KEY(id,par_no)) ENGINE=MYISAM CHARSET=gbk PARTITION BY HASH(id)

PARTITIONS 5;           --partition 多了個s ,讓mysql自動id的Hash 值儲存到5個分區裡。

查詢分區表中存在的資料量：

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 

FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='bcd'

分區管理--測試總結：刪除分區：ALTER TABLE tablename REMOVE PARTITIONING ; --ALTER TABLE bc DROP PARTITION p1;           --修改分區：ALTER TABLE nb PARTITION BY HASH(id) PARTITIONS 2;  --按兩個hash分區進行重新整理資料；合并分區：ALTER TABLE  bc  REORGANIZE  PARTITION   p1,p0 INTO ( PARTITION p6 VALUES IN (10,20,30,40));  --將BC表中的p0,p1分區合并到p6分區；添加分區:未有分區的情況下:ALTER TABLE nb PARTITION BY HASH(id) PARTITIONS 2; --用id 列給表分兩個hash分區;如果有unique鍵,要先刪除.ALTER TABLE a  PARTITION BY RANGE(id)(            --a 表無分區.PARTITION p0  VALUES LESS THAN (1000),PARTITION p1  VALUES  LESS THAN (2000),PARTITION  p2  VALUES LESS THAN  maxvalue)--------------------------------------------------------------------ALTER TABLE bc  PARTITION BY LIST(par_no) (PARTITION p1             ----為未定義分區表的BC表添加兩個list分區；VALUES IN (10,20,30),PARTITION p2 VALUES IN (40,50,60,70,80));已有分區的情況下:ALTER TABLE abc  REORGANIZE  PARTITION  p2 INTO (PARTITION p2 VALUES LESS THAN (6000),PARTITION p3 VALUES LESS THAN maxvalue);           --重新定義Range分區p2，並新加一個p3分區,p2分區的析值不能小於現有的最大值，且須新境一個包含最大值（Maxvalue）的新分區，否則報錯ALTER TABLE bc  ADD PARTITION  (PARTITION p3 VALUES IN (20,30))；--添加一個list分區ALTER TABLE nb ADD PARTITION PARTITIONS 2;  --(Hash分區),新加後現有分區裡的資料會平滑分攤到新分區,myisam引擎在上面的查詢語句中可以體現出來,innodb引擎則計數資訊丟失,從0開始重新計數,但表中資料變不會丟失.

注意：刪除分區同時會將分區中的資料刪除，同時枚舉的list值也被刪除，後面無法往表中插入該值的資料。

mysql表分區實驗總結