'良心文章 | MySQL性能優化分區之實戰(最全面最完整的分區)'

MySQL 數據庫 文章 物理 Java實用技術 2019-08-21
"

本號主要用於分享企業中常用的技術,更加側重於實用,歡迎關注,便於瀏覽其它更多實用的歷史文章。

一: 分區簡介

分區是根據一定的規則,數據庫把一個表分解成多個更小的、更容易管理的部分。就訪問數據庫應用而言,邏輯上就只有一個表或者一個索引,但實際上這個表可能有N個物理分區對象組成,每個分區都是一個獨立的對象,可以獨立處理,可以作為表的一部分進行處理。分區對應用來說是完全透明的,不影響應用的業務邏輯。

分區有利於管理非常大的表,它採用分而治之的邏輯,分區引入了分區鍵的概念,分區鍵用於根據某個區間值(或者範圍值)、特定值列表或者hash函數值執行數據的聚集,讓數據根據規則分佈在不同的分區中,讓一個大對象碧昂城一些小對象。

MySQL分區即可以對數據進行分區也可以對索引進行分區。

分區類型

  1. range分區:基於一個給定的連續區間範圍(區間要求連續並且不能重疊),把數據分配到不同的分區
  2. list分區:類似於range分區,區別在於list分區是居於枚舉出的值列表分區,range是基於給定的連續區間範圍分區
  3. hash分區:基於給定的分區個數,把數據分配到不同的分區
  4. key分區:類似於hash分區

注意:無論哪種分區,要麼你分區表上沒有主鍵/唯一鍵,要麼分區表的主鍵/唯一鍵都必須包含分區鍵,也就是說不能使用主鍵/唯一鍵字段之外的其它字段分區。

MySQL分區的有限主要包括以下4個方面:

  1. 和單個磁盤或者文件系統分區相比,可以存儲更多數據
  2. 優化查詢。在where子句中包含分區條件時,可以只掃描必要的一個或者多個分區來提高查詢效率;同時在涉及sum()和count()這類聚合函數的查詢時,可以容易的在每個分區上並行處理,最終只需要彙總所有分區得到的結果
  3. 對於已經過期或者不需要保存的數據,可以通過刪除與這些數據有關的分區來快速刪除數據
  4. 跨多個磁盤來分散數據查詢,以獲得更大的查詢吞吐量

分區和水平分表功能類似,將一個大表的數據分割到多張小表中去,由於查詢不需要全表掃描了,只需要掃描某些分區,所以分區能提高查詢速度。

  1. 水平分表需要用戶預先手動顯式創建出多張分表(如tbl_user0, tbl_user1, tbl_user2),在物理上實實在在的創建多張表,通過客戶端代理(Sharding-JDBC等)或者中間件代理(Mycat等)來實現分表邏輯。
  2. 分區是MySQL的一個插件Plugin功能,將一張大表的數據在數據庫底層分成多個分區文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd),和水平分表不同的是分區不需要顯式的創建“分表”,數據庫會自動創建分區文件的,用戶看到的只是一張普通的表,其實是對應的是多個分區,這個是對用戶是屏蔽的、透明的,在使用上和使用一張表完全一樣,不需要藉助任何功能來實現。分區是一種邏輯上的水平分表,在物理層面還是一張表。

二:數據庫文件

CREATE TABLE `tbl_user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_user_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=myisam AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

通過show variables like ‘%datadir%’;命令查看mysql的data存放目錄,進入所在的數據庫目錄(如test),不同的引擎數據庫文件格式不同

  • myisam
  • .frm : 存儲表結構
  • .MYD: 存儲表數據
  • .MYI: 存儲索引文件
  • innodb: 只有設置成獨立表空間才能做成功表分區
  • .frm : 表結構
  • .ibd : 數據 + 索引
"

本號主要用於分享企業中常用的技術,更加側重於實用,歡迎關注,便於瀏覽其它更多實用的歷史文章。

一: 分區簡介

分區是根據一定的規則,數據庫把一個表分解成多個更小的、更容易管理的部分。就訪問數據庫應用而言,邏輯上就只有一個表或者一個索引,但實際上這個表可能有N個物理分區對象組成,每個分區都是一個獨立的對象,可以獨立處理,可以作為表的一部分進行處理。分區對應用來說是完全透明的,不影響應用的業務邏輯。

分區有利於管理非常大的表,它採用分而治之的邏輯,分區引入了分區鍵的概念,分區鍵用於根據某個區間值(或者範圍值)、特定值列表或者hash函數值執行數據的聚集,讓數據根據規則分佈在不同的分區中,讓一個大對象碧昂城一些小對象。

MySQL分區即可以對數據進行分區也可以對索引進行分區。

分區類型

  1. range分區:基於一個給定的連續區間範圍(區間要求連續並且不能重疊),把數據分配到不同的分區
  2. list分區:類似於range分區,區別在於list分區是居於枚舉出的值列表分區,range是基於給定的連續區間範圍分區
  3. hash分區:基於給定的分區個數,把數據分配到不同的分區
  4. key分區:類似於hash分區

注意:無論哪種分區,要麼你分區表上沒有主鍵/唯一鍵,要麼分區表的主鍵/唯一鍵都必須包含分區鍵,也就是說不能使用主鍵/唯一鍵字段之外的其它字段分區。

MySQL分區的有限主要包括以下4個方面:

  1. 和單個磁盤或者文件系統分區相比,可以存儲更多數據
  2. 優化查詢。在where子句中包含分區條件時,可以只掃描必要的一個或者多個分區來提高查詢效率;同時在涉及sum()和count()這類聚合函數的查詢時,可以容易的在每個分區上並行處理,最終只需要彙總所有分區得到的結果
  3. 對於已經過期或者不需要保存的數據,可以通過刪除與這些數據有關的分區來快速刪除數據
  4. 跨多個磁盤來分散數據查詢,以獲得更大的查詢吞吐量

分區和水平分表功能類似,將一個大表的數據分割到多張小表中去,由於查詢不需要全表掃描了,只需要掃描某些分區,所以分區能提高查詢速度。

  1. 水平分表需要用戶預先手動顯式創建出多張分表(如tbl_user0, tbl_user1, tbl_user2),在物理上實實在在的創建多張表,通過客戶端代理(Sharding-JDBC等)或者中間件代理(Mycat等)來實現分表邏輯。
  2. 分區是MySQL的一個插件Plugin功能,將一張大表的數據在數據庫底層分成多個分區文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd),和水平分表不同的是分區不需要顯式的創建“分表”,數據庫會自動創建分區文件的,用戶看到的只是一張普通的表,其實是對應的是多個分區,這個是對用戶是屏蔽的、透明的,在使用上和使用一張表完全一樣,不需要藉助任何功能來實現。分區是一種邏輯上的水平分表,在物理層面還是一張表。

二:數據庫文件

CREATE TABLE `tbl_user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_user_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=myisam AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

通過show variables like ‘%datadir%’;命令查看mysql的data存放目錄,進入所在的數據庫目錄(如test),不同的引擎數據庫文件格式不同

  • myisam
  • .frm : 存儲表結構
  • .MYD: 存儲表數據
  • .MYI: 存儲索引文件
  • innodb: 只有設置成獨立表空間才能做成功表分區
  • .frm : 表結構
  • .ibd : 數據 + 索引
良心文章 | MySQL性能優化分區之實戰(最全面最完整的分區)

三:插入500W條數據

CREATE TABLE `tbl_user_no_part` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 修改mysql默認的結束符號,默認是分號;但是在函數和存儲過程中會使用到分號導致解析不正確
delimiter $$
-- 隨機生成一個指定長度的字符串
create function rand_string(n int) returns varchar(255)
begin
# 定義三個變量
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$
-- 創建插入的存儲過程
create procedure insert_user(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
until i = max_num
end repeat;
commit;
end $$
-- 將命令結束符修改回來
delimiter ;
-- 調用存儲過程,插入500萬數據,需要等待一會時間,等待執行完成
call insert_user(100001,5000000);
-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G內存用了8分鐘才執行完
select count(*) from tbl_user_no_part;

四:range分區

MySQL有五種分區類型 range、list、hash、key、子分區,其中最常用的是range和list分區
-- 查看mysql版本
select version();
-- 查看分區插件是否激活 partition active
show plugins;
對於低版本的MySQL,如果InnoDB引擎要想分區成功,需要在my.conf中設置innodb_file_per_table=1 設置成獨立表空間
獨立表空間:每張表都有對應的.ibd文件
innodb_file_per_table=1

ange分區:給定一個連續區間的範圍值進行分區,某個字段的值滿足這個範圍就會被分配到該分區。適用於字段的值是連續的區間的字段,如 日期範圍, 連續的數字

-- 語法
create table <table> (
\t// 字段
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分區字段) (
partition <分區名稱> values less than (Value),
partition <分區名稱> values less than (Value),
...
partition <分區名稱> values less than maxvalue
);

range:表示按範圍分區

分區字段:表示要按照哪個字段進行分區,可以是一個字段名,也可以是對某個字段進行表達式運算如year(create_time),使用range最終的值必須是數字

分區名稱: 要保證不同,也可以採用 p0、p1、p2 這樣的分區名稱,

less than : 表示小於

Value : 表示要小於某個具體的值,如 less than (10) 那麼分區字段的值小於10的都會被分到這個分區

maxvalue: 表示一個最大的值

注意:range 對應的分區鍵值必須是數字值,可以使用range columns(分區字段) 對非int型做分區,如字符串,對於日期類型的可以使用year()、to_days()、to_seconds()等函數

create table emp_date(
\tid int not null,
\tseparated date not null default '9999-12-31'
)
partition by range columns(separated) (
\tpartiontion p0 values less than ('1990-01-01'),
\tpartiontion p0 values less than ('2001-01-01'),
\tpartiontion p0 values less than ('2018-01-01')
);

分區可以在創建表的時候進行分區,也可以在創建表之後進行分區

alter table <table> partition by RANGE(id) (
\tPARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000),
PARTITION p2 VALUES LESS THAN (3000000),
PARTITION p3 VALUES LESS THAN (4000000),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
-- 創建分區表
CREATE TABLE `tbl_user_part` (
`id` int(11) NOT NULL ,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP
-- PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (age) (
PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (60),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
"

本號主要用於分享企業中常用的技術,更加側重於實用,歡迎關注,便於瀏覽其它更多實用的歷史文章。

一: 分區簡介

分區是根據一定的規則,數據庫把一個表分解成多個更小的、更容易管理的部分。就訪問數據庫應用而言,邏輯上就只有一個表或者一個索引,但實際上這個表可能有N個物理分區對象組成,每個分區都是一個獨立的對象,可以獨立處理,可以作為表的一部分進行處理。分區對應用來說是完全透明的,不影響應用的業務邏輯。

分區有利於管理非常大的表,它採用分而治之的邏輯,分區引入了分區鍵的概念,分區鍵用於根據某個區間值(或者範圍值)、特定值列表或者hash函數值執行數據的聚集,讓數據根據規則分佈在不同的分區中,讓一個大對象碧昂城一些小對象。

MySQL分區即可以對數據進行分區也可以對索引進行分區。

分區類型

  1. range分區:基於一個給定的連續區間範圍(區間要求連續並且不能重疊),把數據分配到不同的分區
  2. list分區:類似於range分區,區別在於list分區是居於枚舉出的值列表分區,range是基於給定的連續區間範圍分區
  3. hash分區:基於給定的分區個數,把數據分配到不同的分區
  4. key分區:類似於hash分區

注意:無論哪種分區,要麼你分區表上沒有主鍵/唯一鍵,要麼分區表的主鍵/唯一鍵都必須包含分區鍵,也就是說不能使用主鍵/唯一鍵字段之外的其它字段分區。

MySQL分區的有限主要包括以下4個方面:

  1. 和單個磁盤或者文件系統分區相比,可以存儲更多數據
  2. 優化查詢。在where子句中包含分區條件時,可以只掃描必要的一個或者多個分區來提高查詢效率;同時在涉及sum()和count()這類聚合函數的查詢時,可以容易的在每個分區上並行處理,最終只需要彙總所有分區得到的結果
  3. 對於已經過期或者不需要保存的數據,可以通過刪除與這些數據有關的分區來快速刪除數據
  4. 跨多個磁盤來分散數據查詢,以獲得更大的查詢吞吐量

分區和水平分表功能類似,將一個大表的數據分割到多張小表中去,由於查詢不需要全表掃描了,只需要掃描某些分區,所以分區能提高查詢速度。

  1. 水平分表需要用戶預先手動顯式創建出多張分表(如tbl_user0, tbl_user1, tbl_user2),在物理上實實在在的創建多張表,通過客戶端代理(Sharding-JDBC等)或者中間件代理(Mycat等)來實現分表邏輯。
  2. 分區是MySQL的一個插件Plugin功能,將一張大表的數據在數據庫底層分成多個分區文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd),和水平分表不同的是分區不需要顯式的創建“分表”,數據庫會自動創建分區文件的,用戶看到的只是一張普通的表,其實是對應的是多個分區,這個是對用戶是屏蔽的、透明的,在使用上和使用一張表完全一樣,不需要藉助任何功能來實現。分區是一種邏輯上的水平分表,在物理層面還是一張表。

二:數據庫文件

CREATE TABLE `tbl_user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_user_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=myisam AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

通過show variables like ‘%datadir%’;命令查看mysql的data存放目錄,進入所在的數據庫目錄(如test),不同的引擎數據庫文件格式不同

  • myisam
  • .frm : 存儲表結構
  • .MYD: 存儲表數據
  • .MYI: 存儲索引文件
  • innodb: 只有設置成獨立表空間才能做成功表分區
  • .frm : 表結構
  • .ibd : 數據 + 索引
良心文章 | MySQL性能優化分區之實戰(最全面最完整的分區)

三:插入500W條數據

CREATE TABLE `tbl_user_no_part` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 修改mysql默認的結束符號,默認是分號;但是在函數和存儲過程中會使用到分號導致解析不正確
delimiter $$
-- 隨機生成一個指定長度的字符串
create function rand_string(n int) returns varchar(255)
begin
# 定義三個變量
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$
-- 創建插入的存儲過程
create procedure insert_user(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
until i = max_num
end repeat;
commit;
end $$
-- 將命令結束符修改回來
delimiter ;
-- 調用存儲過程,插入500萬數據,需要等待一會時間,等待執行完成
call insert_user(100001,5000000);
-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G內存用了8分鐘才執行完
select count(*) from tbl_user_no_part;

四:range分區

MySQL有五種分區類型 range、list、hash、key、子分區,其中最常用的是range和list分區
-- 查看mysql版本
select version();
-- 查看分區插件是否激活 partition active
show plugins;
對於低版本的MySQL,如果InnoDB引擎要想分區成功,需要在my.conf中設置innodb_file_per_table=1 設置成獨立表空間
獨立表空間:每張表都有對應的.ibd文件
innodb_file_per_table=1

ange分區:給定一個連續區間的範圍值進行分區,某個字段的值滿足這個範圍就會被分配到該分區。適用於字段的值是連續的區間的字段,如 日期範圍, 連續的數字

-- 語法
create table <table> (
\t// 字段
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分區字段) (
partition <分區名稱> values less than (Value),
partition <分區名稱> values less than (Value),
...
partition <分區名稱> values less than maxvalue
);

range:表示按範圍分區

分區字段:表示要按照哪個字段進行分區,可以是一個字段名,也可以是對某個字段進行表達式運算如year(create_time),使用range最終的值必須是數字

分區名稱: 要保證不同,也可以採用 p0、p1、p2 這樣的分區名稱,

less than : 表示小於

Value : 表示要小於某個具體的值,如 less than (10) 那麼分區字段的值小於10的都會被分到這個分區

maxvalue: 表示一個最大的值

注意:range 對應的分區鍵值必須是數字值,可以使用range columns(分區字段) 對非int型做分區,如字符串,對於日期類型的可以使用year()、to_days()、to_seconds()等函數

create table emp_date(
\tid int not null,
\tseparated date not null default '9999-12-31'
)
partition by range columns(separated) (
\tpartiontion p0 values less than ('1990-01-01'),
\tpartiontion p0 values less than ('2001-01-01'),
\tpartiontion p0 values less than ('2018-01-01')
);

分區可以在創建表的時候進行分區,也可以在創建表之後進行分區

alter table <table> partition by RANGE(id) (
\tPARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000),
PARTITION p2 VALUES LESS THAN (3000000),
PARTITION p3 VALUES LESS THAN (4000000),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
-- 創建分區表
CREATE TABLE `tbl_user_part` (
`id` int(11) NOT NULL ,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP
-- PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (age) (
PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (60),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
良心文章 | MySQL性能優化分區之實戰(最全面最完整的分區)

在創建分區的時候經常會遇到這個錯誤:A PRIMARY KEY must include all columns in the table’s partitioning function。意思是說分區的字段必須是要包含在主鍵當中。 可以使用PRIMARY KEY (id,xxx)來將多個字段作為主鍵。在做分區表時,選擇分區的依據字段時要謹慎,需要仔細斟酌這個字段拿來做為分區依據是否合適,這個字段加入到主鍵中做為複合主鍵是否適合。

使用range分區時表結構要麼沒有主鍵,要麼分區字段必須是主鍵。

-- 將tbl_user_no_part表中的數據複製到tbl_user_part表中(數據量比較多,可能要等幾分鐘)
INSERT INTO tbl_user_part SELECT * FROM tbl_user_no_part;
SELECT count(*) FROM tbl_user_no_part WHERE age > 25 AND age < 30;
SELECT count(*) FROM tbl_user_part WHERE age > 25 AND age < 30;
"

本號主要用於分享企業中常用的技術,更加側重於實用,歡迎關注,便於瀏覽其它更多實用的歷史文章。

一: 分區簡介

分區是根據一定的規則,數據庫把一個表分解成多個更小的、更容易管理的部分。就訪問數據庫應用而言,邏輯上就只有一個表或者一個索引,但實際上這個表可能有N個物理分區對象組成,每個分區都是一個獨立的對象,可以獨立處理,可以作為表的一部分進行處理。分區對應用來說是完全透明的,不影響應用的業務邏輯。

分區有利於管理非常大的表,它採用分而治之的邏輯,分區引入了分區鍵的概念,分區鍵用於根據某個區間值(或者範圍值)、特定值列表或者hash函數值執行數據的聚集,讓數據根據規則分佈在不同的分區中,讓一個大對象碧昂城一些小對象。

MySQL分區即可以對數據進行分區也可以對索引進行分區。

分區類型

  1. range分區:基於一個給定的連續區間範圍(區間要求連續並且不能重疊),把數據分配到不同的分區
  2. list分區:類似於range分區,區別在於list分區是居於枚舉出的值列表分區,range是基於給定的連續區間範圍分區
  3. hash分區:基於給定的分區個數,把數據分配到不同的分區
  4. key分區:類似於hash分區

注意:無論哪種分區,要麼你分區表上沒有主鍵/唯一鍵,要麼分區表的主鍵/唯一鍵都必須包含分區鍵,也就是說不能使用主鍵/唯一鍵字段之外的其它字段分區。

MySQL分區的有限主要包括以下4個方面:

  1. 和單個磁盤或者文件系統分區相比,可以存儲更多數據
  2. 優化查詢。在where子句中包含分區條件時,可以只掃描必要的一個或者多個分區來提高查詢效率;同時在涉及sum()和count()這類聚合函數的查詢時,可以容易的在每個分區上並行處理,最終只需要彙總所有分區得到的結果
  3. 對於已經過期或者不需要保存的數據,可以通過刪除與這些數據有關的分區來快速刪除數據
  4. 跨多個磁盤來分散數據查詢,以獲得更大的查詢吞吐量

分區和水平分表功能類似,將一個大表的數據分割到多張小表中去,由於查詢不需要全表掃描了,只需要掃描某些分區,所以分區能提高查詢速度。

  1. 水平分表需要用戶預先手動顯式創建出多張分表(如tbl_user0, tbl_user1, tbl_user2),在物理上實實在在的創建多張表,通過客戶端代理(Sharding-JDBC等)或者中間件代理(Mycat等)來實現分表邏輯。
  2. 分區是MySQL的一個插件Plugin功能,將一張大表的數據在數據庫底層分成多個分區文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd),和水平分表不同的是分區不需要顯式的創建“分表”,數據庫會自動創建分區文件的,用戶看到的只是一張普通的表,其實是對應的是多個分區,這個是對用戶是屏蔽的、透明的,在使用上和使用一張表完全一樣,不需要藉助任何功能來實現。分區是一種邏輯上的水平分表,在物理層面還是一張表。

二:數據庫文件

CREATE TABLE `tbl_user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_user_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=myisam AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

通過show variables like ‘%datadir%’;命令查看mysql的data存放目錄,進入所在的數據庫目錄(如test),不同的引擎數據庫文件格式不同

  • myisam
  • .frm : 存儲表結構
  • .MYD: 存儲表數據
  • .MYI: 存儲索引文件
  • innodb: 只有設置成獨立表空間才能做成功表分區
  • .frm : 表結構
  • .ibd : 數據 + 索引
良心文章 | MySQL性能優化分區之實戰(最全面最完整的分區)

三:插入500W條數據

CREATE TABLE `tbl_user_no_part` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 修改mysql默認的結束符號,默認是分號;但是在函數和存儲過程中會使用到分號導致解析不正確
delimiter $$
-- 隨機生成一個指定長度的字符串
create function rand_string(n int) returns varchar(255)
begin
# 定義三個變量
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$
-- 創建插入的存儲過程
create procedure insert_user(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
until i = max_num
end repeat;
commit;
end $$
-- 將命令結束符修改回來
delimiter ;
-- 調用存儲過程,插入500萬數據,需要等待一會時間,等待執行完成
call insert_user(100001,5000000);
-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G內存用了8分鐘才執行完
select count(*) from tbl_user_no_part;

四:range分區

MySQL有五種分區類型 range、list、hash、key、子分區,其中最常用的是range和list分區
-- 查看mysql版本
select version();
-- 查看分區插件是否激活 partition active
show plugins;
對於低版本的MySQL,如果InnoDB引擎要想分區成功,需要在my.conf中設置innodb_file_per_table=1 設置成獨立表空間
獨立表空間:每張表都有對應的.ibd文件
innodb_file_per_table=1

ange分區:給定一個連續區間的範圍值進行分區,某個字段的值滿足這個範圍就會被分配到該分區。適用於字段的值是連續的區間的字段,如 日期範圍, 連續的數字

-- 語法
create table <table> (
\t// 字段
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分區字段) (
partition <分區名稱> values less than (Value),
partition <分區名稱> values less than (Value),
...
partition <分區名稱> values less than maxvalue
);

range:表示按範圍分區

分區字段:表示要按照哪個字段進行分區,可以是一個字段名,也可以是對某個字段進行表達式運算如year(create_time),使用range最終的值必須是數字

分區名稱: 要保證不同,也可以採用 p0、p1、p2 這樣的分區名稱,

less than : 表示小於

Value : 表示要小於某個具體的值,如 less than (10) 那麼分區字段的值小於10的都會被分到這個分區

maxvalue: 表示一個最大的值

注意:range 對應的分區鍵值必須是數字值,可以使用range columns(分區字段) 對非int型做分區,如字符串,對於日期類型的可以使用year()、to_days()、to_seconds()等函數

create table emp_date(
\tid int not null,
\tseparated date not null default '9999-12-31'
)
partition by range columns(separated) (
\tpartiontion p0 values less than ('1990-01-01'),
\tpartiontion p0 values less than ('2001-01-01'),
\tpartiontion p0 values less than ('2018-01-01')
);

分區可以在創建表的時候進行分區,也可以在創建表之後進行分區

alter table <table> partition by RANGE(id) (
\tPARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000),
PARTITION p2 VALUES LESS THAN (3000000),
PARTITION p3 VALUES LESS THAN (4000000),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
-- 創建分區表
CREATE TABLE `tbl_user_part` (
`id` int(11) NOT NULL ,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP
-- PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (age) (
PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (60),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
良心文章 | MySQL性能優化分區之實戰(最全面最完整的分區)

在創建分區的時候經常會遇到這個錯誤:A PRIMARY KEY must include all columns in the table’s partitioning function。意思是說分區的字段必須是要包含在主鍵當中。 可以使用PRIMARY KEY (id,xxx)來將多個字段作為主鍵。在做分區表時,選擇分區的依據字段時要謹慎,需要仔細斟酌這個字段拿來做為分區依據是否合適,這個字段加入到主鍵中做為複合主鍵是否適合。

使用range分區時表結構要麼沒有主鍵,要麼分區字段必須是主鍵。

-- 將tbl_user_no_part表中的數據複製到tbl_user_part表中(數據量比較多,可能要等幾分鐘)
INSERT INTO tbl_user_part SELECT * FROM tbl_user_no_part;
SELECT count(*) FROM tbl_user_no_part WHERE age > 25 AND age < 30;
SELECT count(*) FROM tbl_user_part WHERE age > 25 AND age < 30;
良心文章 | MySQL性能優化分區之實戰(最全面最完整的分區)

五:list 分區

設置若干個固定值進行分區,如果某個字段的值在這個設置的值列表中就會被分配到該分區。適用於字段的值區分度不高的,或者值是有限的,特別是像枚舉這樣特點的列。list分區使用in表示一些固定的值的列表

-- 語法
create table <table> (
\t// 字段
) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by LIST (分區字段或者基於該字段的返回的整數值的表達式) (
partition <分區名稱> values IN (Value1,Value2, Value3),
...
partition <分區名稱> values IN (Value4, Value5),
);

columns分區

在mysql5.5之前range分區和list分區只支持整數分區,可以通過額外的函數運算或者額外的轉換從而得到一個整數。columns分區分為 range columns 和 list columns 兩種,支持整數(tinyint到bigint, 不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三大數據類型。

columns分區支持一個或者多個字段作為分區鍵,不支持表達式作為分區鍵,這點區別於range 和 list 分區。需要注意的是range columns 分區鍵的比較是基於元組的比較,也就是基於字段組的比較,這和range分區有差異。

create talbe rc3 (
\ta int,
\tb int
)
partition by range columns(a, b) (
\tpartition p01 values less than (0, 10),
\tpartition p02 values less than (10, 10),
\tpartition p03 values less than (10, 20),
\tpartition p04 values less than (10, 35),
\tpartition p05 values less than (10, maxvalue),
\tpartition p06 values less than (maxvalue, maxvalue),
);
insert into rc3(a, b) values(1, 10);
select (1, 10) < (10, 10) from dual;
-- 根據結果存放到p02分區上了
select
\tpartition_name,
\tpartition_expression,
\tpartition_description,
\ttable_rows
from information_schema.partitions
where table_schema = schema() and table_name = 'rc3';\t

ange columns分區鍵的比較(元組的比較)其實就是多列排序,先根據a字段排序再根據b字段排序,根據排序結果來分區存放數據,和range單字段的分區排序的規則實際上是一樣的

六:hash分區

Hash分區主要用來分散熱點讀,確保數據在預先確定個數的分區中可能的平均分佈。對一個表執行Hash分區時,mysql會對分區鍵應用一個散列函數,以此確定數據應當放在N個分區中的哪個分區。

mysql支持兩種hash分區,

  1. 常規hash分區和線性hash分區(linear hash分區),常規hash分區使用的是取模算法,對應一個表達式expr是可以計算出它被保存到哪個分區中,N = MOD(expr, num)
  2. 線性hash分區使用的是一個線性的2的冪運算法則。

對指定的字段(整型字段)進行哈希,將記錄平均的分配到分區中,使得所有分區的數據比較平均。 hash分區只需要指定要分區的字段和要分成幾個分區,

expr是一個字段值或者基於某列值雲散返回的一個整數,expr可以是mysql中有效的任何函數或者其它表達式,只要它們返回一個即非常熟也非隨機數的整數。

num 表示分區數量

-- HASH
create table <table> (
\t// 字段
) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(expr)
PARTITIONS <num>;

常規hash分區方式看上去挺不錯的,通過取模的方式來數據儘可能平均分佈在每個分區,讓每個分區管理的數據都減少,提高查詢效率,可是當我們要增加分區時或者合併分區,問題就來了,假設原來是5個常規hash分區,現在需要增加一個常規分區,原來的取模算法是MOD(expr, 5), 根據餘數0~4分佈在5個分區中,現在新增一個分區後,取模算法變成MOD(expr, 6),根據餘數0~6分區在6個分區中,原來5個分區的數據大部分都需要通過重新計算進行重新分區。

常規hash分區在管理上帶來了的代價太大,不適合需要靈活變動分區的需求。為了降低分區管理上的代價,mysql提供了線性hash分區,分區函數是一個線性的2的冪的運算法則。同樣線性hash分區的記錄被存在那個分區也是能被計算出來的。線性hash分區的優點是在分區維護(增加、刪除、合併、拆分分區)時,mysql能夠處理的更加迅速,缺點是:對比常規hash分區,線性hash各個分區之間數據的分佈不太均衡。

-- LINEAR HASH
create table <table> (
\t// 字段
) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LINEAR HASH(expr)
PARTITIONS <num>;

七:key分區

按照key進行分區非常類似於按照hash進行分區,只不過hash分區允許使用用戶自定義的表達式,而key分區不允許使用用於自定義的表達式,需要使用mysql服務器提供的hash函數,同時hash分區只支持整數分區,而key分區支持使用出blob or text類型外的其他類型的列作為分區鍵。

和hash功能一樣,不同的是分區的字段可以是非int類型,如字符串、日期等類型。

可以使用partition by key(expr)子句來創建一個key分區表,expr是零個或者多個字段名的列表。key分區也支持線性分區linear key

partition by key(expr) partitions num;
-- 不指定默認首選主鍵作為分區鍵,在沒有主鍵的情況下會選擇非空唯一鍵作為分區鍵
partition by key() partitions num;
-- linear key
partition by linear key(expr)
create table <table> (
\t// 字段
) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(分區字段名)
PARTITIONS <count>;

八:子分區

子分區(subpartition):是分區表中對每個分區的再次分割,又被稱為複合分區,支持對range和list進行子分區,子分區即可以使用hash分區也可以使用key分區。複合分區適用於保存非常大量的數據記錄。

-- 根據年進行分區

-- 再根據天數分區

-- 3個range分區(p0,p1,p2)又被進一步分成2個子分區,實際上整個分區被分成了 3 x 2 = 6個分區

create table ts (
\tid int,
\tpurchased date
)
partition by range(year(purchased))
subpartition by hash(to_days(purchased)) subpartitions 2
(
\tpartition p0 values less than (1990),
\tpartition p0 values less than (2000),
\tpartition p0 values less than maxvalue
);
CREATE TABLE IF NOT EXISTS `sub_part` (
`news_id` int(11) NOT NULL COMMENT '新聞ID',
`content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新聞內容',
`u_id` int(11) NOT NULL DEFAULT 0s COMMENT '來源IP',
`create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '時間'
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(create_time))
SUBPARTITION BY HASH(TO_DAYS(create_time))
(
PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1, SUBPARTITION s2),
PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3, SUBPARTITION s4, SUBPARTITION good),
PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION tank0, SUBPARTITION tank1, SUBPARTITION tank3)
);

九:管理分區

mysql不禁止在分區鍵值上使用null,分區鍵可能是一個字段或者一個用戶定義的表達式,一般情況下,mysql的分區把null值當做零值或者一個最小值進行處理。range分區中,null值會被當做最小值來處理;list分區中null值必須出現在枚舉列表中,否則不被接受;hash/key分區中,null值會被當做領值來處理。

mysql提供了添加、刪除、重定義、合併、拆分分區的命令,這些操作都可以通過alter table 命令來實現

-- 刪除list或者range分區(同時刪除分區對應的數據)
alter table <table> drop partition <分區名稱>;
-- 新增分區
-- range添加新分區
alter table <table> add partition(partition p4 values less than MAXVALUE);
-- list添加新分區
alter table <table> add partition(partition p4 values in (25,26,28));
-- hash重新分區
alter table <table> add partition partitions 4;
-- key重新分區
alter table <table> add partition partitions 4;
-- 子分區添加新分區,雖然我沒有指定子分區,但是系統會給子分區命名的
alter table <table> add partition(partition p3 values less than MAXVALUE);
-- range重新分區
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
-- list重新分區
ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));

分區優點

1,分區可以分在多個磁盤,存儲更大一點

2,根據查找條件,也就是where後面的條件,查找只查找相應的分區不用全部查找了

3,進行大數據搜索時可以進行並行處理。

4,跨多個磁盤來分散數據查詢,來獲得更大的查詢吞吐量

"

相關推薦

推薦中...