Mysql --- 分區表

標籤：font   int   time   select   plain   總結   code   速度   table   

概念：由一個或多個物理分區組成的表

類型：RANGE HASH LIST KEY

下面主要實現RANGE和HASH

一.RANGE類型的分區表1.按照主鍵分區

DROP TABLE IF EXISTS p_user;CREATE TABLE p_user(    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(225),    login_date DATETIME)ENGINE=INNODB PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (10),PARTITION p1 VALUES LESS THAN (20),PARTITION p2 VALUES LESS THAN (30),PARTITION p3 VALUES LESS THAN MAXVALUE)

 分區表p_user，小於10的id儲存在p0分區中，10到20之間的id儲存在p1中，以此類推，大於30的id儲存在p3中。

 如果去掉 PARTITION p3 VALUES LESS THAN MAXVALUE ，則插入id 超過30的資料會報錯：Table has no partition for value 30

 

2.按照日期分區

DROP TABLE IF EXISTS p_user;CREATE TABLE p_user(    name VARCHAR(225),    login_date DATETIME)ENGINE=INNODB PARTITION BY RANGE (YEAR(login_date))(PARTITION p_2014 VALUES LESS THAN (2015),PARTITION p_2015 VALUES LESS THAN (2016),PARTITION p_2016 VALUES LESS THAN (2017),PARTITION p_max VALUES LESS THAN MAXVALUE)

 

 查看分區查詢情況：

explain partitions select * from p_user where login_date >= ‘2017-1-1‘

 

 可見是從p_max分區中讀的資料

 

二.HASH類型的分區表1.按照id分區，且均勻分布資料

DROP TABLE IF EXISTS p_user;CREATE TABLE p_user(    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(225),    login_date DATETIME)ENGINE=INNODB PARTITION BY HASH (id)PARTITIONS 4

EXPLAIN PARTITIONS SELECT * FROM p_user WHERE id = 1015

 

選擇分區規則是：hash後面的計算值 除以 分區數目 餘數就是分區所在。（1015/4 餘數3 所以在p3）。所以hash類型的分區表可以保證資料均勻分布。

 

2.按照id分區，且以滿1000000資料為換分區的條件

說明：第一分區存滿1000000資料，則換下一分區，當四個分區都滿1000000資料後，回到第一分區重新開始，當第一分區再次滿1000000資料後，繼續換下一分區，以此迴圈

DROP TABLE IF EXISTS p_user;CREATE TABLE p_user(    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(225),    login_date DATETIME)ENGINE=INNODB PARTITION BY HASH (id DIV 1000000)PARTITIONS 4

 

三.效率測試

準備兩張表

-- 有分區的表DROP TABLE IF EXISTS p_user;CREATE TABLE p_user(    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(225),    login_date DATETIME)ENGINE=INNODB PARTITION BY HASH (id)PARTITIONS 4-- 沒有分區的表DROP TABLE IF EXISTS user;CREATE TABLE user(    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(225),    login_date DATETIME)ENGINE=INNODB

 

兩張表分別填充1600萬左右的資料

1.測試（1）

select * from p_user where id = 15000000;select * from user where id = 15000000;

 

查詢耗時相當，因為id是主鍵索引，所以無法體現分區表的優勢

 

2.測試（2）

把p_user 和 user 表id欄位的主鍵和自增屬性去掉，且把id為15000000行對應的name改成‘httt‘

select * from p_user where id = 15000000;select * from user where id = 15000000;

原因：第一條查詢會到對應的分區裡尋找資料，所以更快

explain partitions select * from p_user where id = 15000000;

 

3.測試（3）

和測試（2）的情境一樣

select * from p_user where name = ‘httt‘ and id = 15000000;select * from user where name = ‘httt‘ and id = 15000000;

 原因：第一條查詢會到對應的分區裡尋找資料，所以更快

 

4.測試（4）

和測試（2）的情境一樣

select * from p_user where name = ‘httt‘;select * from user where name = ‘httt‘;

 

 

可見分區表的耗時比多了兩秒，多次執行，結果都是分區表耗時間長度。原因：條件欄位不是分區欄位，所以查詢每個分區的資料

explain partitions select * from p_user where name = ‘httt‘;

 

 

5.測試總結

　　---按照索引列分區，那麼對查詢最佳化幾乎沒影響

　　---按照非索引列分區，則查詢條件中用了分區列，能提高查詢速度，反之，會降低查詢速度

 

Mysql --- 分區表