分區(qū)表是什么

分區(qū)表就是把一張表的數(shù)據(jù)，按照設(shè)置好的條件，單獨(dú)存儲在磁盤的不同位置，也就是不同分區(qū)的數(shù)據(jù)是獨(dú)立的，互不影響的。

在沒有分區(qū)表的情況下，一張表的數(shù)據(jù)就是存儲在一個(gè)文件中，用了分區(qū)表之后，單張表的數(shù)據(jù)會在硬盤上分開存儲。對表的操作來說，沒有什么區(qū)別。

分區(qū)表的優(yōu)點(diǎn)

• 更少的數(shù)據(jù)檢索范圍
• 拆分超級大的表，將部分?jǐn)?shù)據(jù)加載至內(nèi)存
• 分區(qū)表的數(shù)據(jù)更容易維護(hù)
• 分區(qū)表數(shù)據(jù)文件可以分布在不同的硬盤上，并發(fā) IO
• 減少鎖的范圍，避免大表鎖表
• 可獨(dú)立備份，恢復(fù)分區(qū)數(shù)據(jù)

什么時(shí)候創(chuàng)建分區(qū)表

當(dāng)單張表的數(shù)據(jù)量較大，且因?yàn)閿?shù)據(jù)量大，導(dǎo)致查詢無法滿足要求。

不想做分庫分表這樣大的改動。

未創(chuàng)建分區(qū)表的情況

看下如果不創(chuàng)建分區(qū)表，查詢是怎么樣的，可以和創(chuàng)建分區(qū)表的情況做對比，這樣更好理解。

CREATE TABLE test_partition (
  id int(11) NOT NULL,
  create_time datetime NOT NULL,
	cyear int,
  PRIMARY KEY (id,create_time , cyear)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 insert into test_partition values (1,"20130722000000",2013);
 insert into test_partition values (2,"20140722000000",2014);
 insert into test_partition values (3,"20150722000000",2015);
 insert into test_partition values (4,"20160722000000",2016);
 insert into test_partition values (5,"20170722000000",2017);
 insert into test_partition values (6,"20180722000000",2018);
 insert into test_partition values (7,"20190722000000",2019);
 insert into test_partition values (8,"20200722000000",2020);
 insert into test_partition values (9,"20210722000000",2021);
 insert into test_partition values (10,"20220722000000",2022);

執(zhí)行上面的SQL，創(chuàng)建表，并插入記錄。

查詢年份大于2016的記錄，語句如下：

這個(gè)查詢，如果想優(yōu)化，首先想到的就是在年份字段上添加索引，因?yàn)槟攴葑侄巫鳛椴樵儣l件的一個(gè)字段。

但實(shí)際操作就會發(fā)現(xiàn)，添加了索引，最終查詢并沒有使用這個(gè)索引，因?yàn)镸ySQL執(zhí)行器會推斷，當(dāng)結(jié)果集的數(shù)量占總記錄數(shù)的比例較大時(shí)，不會使用索引，因?yàn)闊o論是否使用索引，掃描的記錄總數(shù)差不多。

解決方法就是在磁盤的檢索范圍上進(jìn)行優(yōu)化，那就是創(chuàng)建分區(qū)表來解決。

分區(qū)表的創(chuàng)建

執(zhí)行下面的語句，可以刪除上面創(chuàng)建的表，重新創(chuàng)建帶有分區(qū)的表。

drop table test_partition;

CREATE TABLE test_partition (
  id int(11) NOT NULL,
  create_time datetime NOT NULL,
	cyear int,
  PRIMARY KEY (id,create_time , cyear)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE (cyear)
(
 PARTITION y14before VALUES LESS THAN (2014) ,
 PARTITION y15 VALUES LESS THAN (2015) ,
 PARTITION y16 VALUES LESS THAN (2016) ,
 PARTITION y17 VALUES LESS THAN (2017) ,
 PARTITION y18 VALUES LESS THAN (2018) ,
 PARTITION y19 VALUES LESS THAN (2019) ,
 PARTITION y20 VALUES LESS THAN (2020) ,
 PARTITION y20after VALUES LESS THAN maxvalue );

PARTITION BY RANGE 表示根據(jù)字段進(jìn)行范圍分區(qū)。

PARTITION就是分區(qū)表的關(guān)鍵字，y15代表分區(qū)的名稱，LESS THAN條件。

當(dāng)進(jìn)行數(shù)據(jù)插入時(shí)，年份為2015年的數(shù)據(jù)，就會存儲在y15這個(gè)分區(qū)中。年份為2016的數(shù)據(jù)就會存儲在y16這個(gè)分區(qū)中。以此類推。

分區(qū)表的使用

插入數(shù)據(jù)

 insert into test_partition values (1,"20130722000000",2013);
 insert into test_partition values (2,"20140722000000",2014);
 insert into test_partition values (3,"20150722000000",2015);
 insert into test_partition values (4,"20160722000000",2016);
 insert into test_partition values (5,"20170722000000",2017);
 insert into test_partition values (6,"20180722000000",2018);
 insert into test_partition values (7,"20190722000000",2019);
 insert into test_partition values (8,"20200722000000",2020);
 insert into test_partition values (9,"20210722000000",2021);
 insert into test_partition values (10,"20220722000000",2022);

可以看到在數(shù)據(jù)庫安裝目錄的data目錄中，test這個(gè)文件夾下面有這樣一些文件，這就是test數(shù)據(jù)庫中test_partition表的不同分區(qū)數(shù)據(jù)。文件名稱對應(yīng)的就是表名稱+分區(qū)的名稱。

在進(jìn)行查詢時(shí)，MySQL會根據(jù)查詢條件，從指定的分區(qū)表中獲取數(shù)據(jù)。這樣就縮小了數(shù)據(jù)的檢索范圍。

查詢這個(gè)執(zhí)行計(jì)劃，可以看到partitions字段值就是數(shù)據(jù)涉及到的分區(qū)名稱。MySQL只去查找涉及到的分區(qū)，然后從中獲取數(shù)據(jù)，并不會把所有表數(shù)據(jù)全部去掃描，從物理層面減少掃描范圍。

因?yàn)椴樵儣l件是年份大于2016，所以只查詢2017及其之后年份的數(shù)據(jù)，2017年的數(shù)據(jù)存儲在y18的分區(qū)中，所有就可以看到y(tǒng)18,y19,y20after，這三個(gè)分區(qū)。

分區(qū)表數(shù)據(jù)統(tǒng)計(jì)

通過下面的查詢，可以看到表的每個(gè)分區(qū)中數(shù)據(jù)分布情況：

select 
	PARTITION_NAME as "分區(qū)",
	TABLE_ROWS as "行數(shù)" 
from information_schema.partitions 
where table_schema="test" #數(shù)據(jù)庫名稱
and table_name="test_partition"; #表名

分區(qū)表的使用限制

• 查詢必須包含分區(qū)列（上面例子中的cyear列），不允許對分區(qū)列進(jìn)行計(jì)算。
• 分區(qū)列必須是數(shù)字類型。
• 分區(qū)表不支持建立外鍵索引。
• 建表時(shí)主鍵必須包含所有的列（上面例子中，PRIMARY KEY (id,create_time , cyear)）。
• 最多1024個(gè)分區(qū)。

到此這篇關(guān)于MySQL分區(qū)表使用保姆級教程的文章就介紹到這了,更多相關(guān)MySQL分區(qū)表使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論