mysql5.7分区表

一颗B+tree索引存储的数据大概是2000万，大概三四层左右，如果超过2000万数据，B+tree索引数会更高，可能达到五层六层。如果我们的数据过于庞大，可以考虑使用mysql分区存储，分区类型包括：

1）range分区

2）LIST分区

3）HASH分区

4）KEY分区

5）复合分区/子分区

 注意：分区表的总个数不能超过1024个，是在代码里写死的。

一、range分区

对用户表进行分区DDL：

CREATE TABLE t_user(
   id bigint(20) primary key not null,
   create_time datetime not null default now()
) ENGINE = InnoDB
PARTITION BY RANGE(YEAR(create_time))(
	PARTITION p_2010 VALUES LESS THAN (2010),
	PARTITION p_2011 VALUES LESS THAN (2011),
	PARTITION p_2012 VALUES LESS THAN (2012),
	PARTITION p_other VALUES LESS THAN MAXVALUE
);

在这个例子中，按照create_time的年份不同区间分区存储。

二、LIST分区

示例int类型列的LIST分区：

CREATE TABLE t_user(
    c1 int,
    c2 int
    PARTITION BY LIST(c1)(
    	PARTITION p0 VALUES IN (1,4,7),
        PARTITION p1 VALUES IN (2,5,8)
    )
)

示例varchar类型列的LIST分区，这里使用columns关键字，且columns括号内支持多个列：

CREATE TABLE T_USER(
	username varchar(30) not null primary key,
    category varchar(10),
    amount decimal(10,3)
)
PARTITION BY LIST COLUMNS(category)(
  PARTITION p0 VALUES IN ('a','b'),
  PARTITION p1 VALUES IN ('c','d')
);

三、HASH分区

如果对 age int(3) not null 这个列进行hash分区：  PARTITION BY HASH (age) PARTITIONS 4;  这里的4代表HASH分区为4个，所以是age取模4的值，如果是2就放到第2个HASH分区，以此类推。注意HASH(AGE) 这里的age可以进行运算，只要保证运算之后的结果值是整数即可。

四、KEY分区

如果对 age int(3) not null 这个列进行hash分区：  PARTITION BY KEY (age) PARTITIONS 4;  这里的4代表KEY分区为4个，但是注意HASH(AGE) 这里的age是不可以进行运算的。

五、复合分区/子分区

略...

不建议使用mysql分区表

1）如果插入的不是分区键，mysql会锁死全部分区表，性能会是很大问题。

2）分区表的实现完全由mysql内部实现，对于互联网行业来讲，可控性太差。

3）mysql对于分区表都是在同一个实例中一台机器上存储，天然就有物理上限，像磁盘IO能力就有上限，无法扩充。如果自行实现分库分表，我扩充机器就完了，几乎可以实现水平无限扩展。

4）使用分区表属于系统调优金字塔里的MySQL调优层，没有架构调优的效果好，架构调优成本更低，效果更好。

系统调优金字塔

成本越来越高： 硬件和OS调优 > MySQL调优 > 架构调优

效果越来越好：架构调优 > MySQL调优 > 硬件和OS调优

 

end.