1.分区表
(1).简介
将InnoDB一张表分为多张表,从InnoDB层看是多张表,Server层依然看作一张表,而水平分表类似分区表,但Server层也分了。
(2).分区方式
- 范围分区
- Hash分区
- List分区
(3).优势
- 降低B+树的层级,搜索加速
- 一个数据表物理上分为多个文件,方便处理
(4).缺陷
- 第一次需要访问所有分区,分区多的话,加载文件有压力
- 共用元数据锁
- 分区之后,所有分区依然位于同一节点,如果数据库性能出现问题,无法解决
2.实操
(1).表定义
CREATE TABLE `t` (
`time` datetime NOT NULL,
`content` varchar(100) DEFAULT NULL,
KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(time))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
(2).Server层看作一张表,客户端也看作一张表
(3).存储引擎层分为4张表
3.扩容
通过INDEX DIRECTORY = '/data1’和DATA DIRECTORY = '/data2’指定存储位置。
CREATE TABLE `t` (
`time` datetime NOT NULL,
`content` varchar(100) DEFAULT NULL,
KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(time))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB, INDEX DIRECTORY = '/data1' DATA DIRECTORY = '/data2'
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);