快捷導(dǎo)航

MySQL分區(qū)表使用保姆級教程

更新時間：2026年03月11日 10:28:26 作者：梁萌

分區(qū)表是將大表數(shù)據(jù)按條件拆分存儲在不同磁盤位置的數(shù)據(jù)庫優(yōu)化技術(shù),它通過將數(shù)據(jù)分散到多個獨立分區(qū)中,顯著減少查詢掃描范圍,提升查詢性能,下面就來詳細(xì)的介紹一下

分區(qū)表是什么

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

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

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

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

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

當(dāng)單張表的數(shù)據(jù)量較大，且因為數(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的記錄，語句如下：

這個查詢，如果想優(yōu)化，首先想到的就是在年份字段上添加索引，因為年份字段作為查詢條件的一個字段。

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

解決方法就是在磁盤的檢索范圍上進行優(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ù)字段進行范圍分區(qū)。

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

當(dāng)進行數(shù)據(jù)插入時，年份為2015年的數(shù)據(jù)，就會存儲在y15這個分區(qū)中。年份為2016的數(shù)據(jù)就會存儲在y16這個分區(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這個文件夾下面有這樣一些文件，這就是test數(shù)據(jù)庫中test_partition表的不同分區(qū)數(shù)據(jù)。文件名稱對應(yīng)的就是表名稱+分區(qū)的名稱。

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

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

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

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

通過下面的查詢，可以看到表的每個分區(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"; #表名