MySQL分區(qū)摘要

MySQL支持的分區(qū)類型為水平分區(qū)，并不支持垂直分區(qū)。

水平分區(qū)，以行為單位，劃分到不同的物理文件中；垂直分區(qū)即針對(duì)列劃分。

MySQL的分區(qū)，除了InnoDB支持，MyISAM也支持，所以分區(qū)并不是引擎級(jí)別的事兒。

分區(qū)并不一定會(huì)讓訪問變得更快，尤其是OLTP應(yīng)用，OLAP應(yīng)用使用分區(qū)的效果往往更好。

MySQL支持的如下幾種分區(qū)類型。

• RANGE 行數(shù)據(jù)基于一個(gè)給定連續(xù)區(qū)間的列值劃分。
• LIST 和RANGE類似，只是LIST面向的不是連續(xù)區(qū)間值，而是離散的集合。
• HASH 根據(jù)自定義的表達(dá)式返回值進(jìn)行分區(qū)，不支持負(fù)數(shù)返回。
• KEY 根據(jù)MySQL提供的hash函數(shù)進(jìn)行分區(qū)

不論按照哪種類型進(jìn)行分區(qū)，如果當(dāng)前表有主鍵或者唯一索引，分區(qū)列必須是唯一索引的組成部分。反過來，如果當(dāng)前表沒有定義主鍵也沒有唯一索引，分區(qū)列可以是任何列。

分區(qū)操作示例：

create table sales(id int, price decimal(9,2), season int, finyear datetime) 
partition by range(season) (
prtition s1 values less than (4),
partition s2 values less than (7),
partition s3 values less than (10),
partition s4 values less than (13));

使用函數(shù)獲取range值

create table sales(id int, price decimal(9,2), season int, finyear datetime) 
partition by range(year(finyear)) ( 
partition s1 values less than (2016), 
partition s2 values less than (2017), 
partition s3 values less than (2018),
partition s4 values less than (2020));

create table sales(id int, price decimal(9,2), season int, finyear datetime) 
partition by range(year(finyear)*100+ month(finyear)) ( 
partition s1 values less than (201804), 
partition s2 values less than (201807), 
partition s3 values less than (201810),
partition s4 values less than (201813));

LIST分區(qū)示例

create table sales(id int, price decimal(9,2), season int, finyear datetime) 
partition by LIST(id) ( 
partition s1 values in (1,3,5,7,9), 
partition s2 values in (2,4,6,8,10));

HASH分區(qū)示例

create table sales(id int, price decimal(9,2), season int, finyear datetime) engine=innodb
partition by hash(year(finyear));

KEY分區(qū)示例

create table sales(id int, price decimal(9,2), season int, finyear datetime) engine=innodb
partition by key(finyear);

分區(qū)后的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)及使用效果

以RANGE分區(qū)為例

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

insert into sales select 1,10.05,1,'2018-01-01';
insert into sales select 2,10.05,3,'2018-03-01';
insert into sales select 3,10.05,4,'2018-04-01';

執(zhí)行查詢

explain partitions 
select * from sales where finyear < '2016-01-01';

可以看到，查詢優(yōu)化器，直接跳過第四個(gè)分區(qū)，在前三個(gè)分區(qū)中查詢，這就是要的效果。

除了分區(qū)，還有分表、分庫(kù)、分片操作，每一個(gè)都很重要，也都有各自的適用場(chǎng)景。