优化查询，在where子句中包含分区条件时，可以只扫描必要的一个或多个分区表来提高查询效率；同时在涉及SUM()和COUNT()这类聚合函数查询的时候，可以在每个分区上并行计算，最后汇总结果即可。在Mysql 5.7中，可以通过select * from t partition (p0,p1)指定查询指定分区的数据
对于已经过期和不需要保存的数据，可以通过删除与这些数据有关的分区来删除数据
跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。
典型使用场景: 日志文件按一定时间，分区存放，不需要的日志文件可以通过时间范围按区删除
Mysql 5.7开始默认支持分区，通过下面的命令可以查看当前Mysql是否支持分区功能
select * from information_schema.plugins where PLUGIN_NAME='partition'G;
对于一个分区表的所有分区来说，必须使用同一个存储引擎，且分区数量不能超过8192
分区表设置存储引擎，只能用engine子句，并且该子句必须在其他任何分区选项之前。
例如:
create table emp(empid INT)
engine=innodb
partition by hash(MONTH(birth_date))
partition 6;
USE test;
CREATE TABLE emp(
id INT NOT NULL,
ename VARCHAR(30) NOT NULL
)
PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10)
);
员工id为1到4的保存在分区p0中。
员工id为5到9的保存在分区p1中。
大家思考如果员工id为10保存在哪里呢?
insert into emp values(2,"dhy");
insert into emp values(6,"like");
insert into emp values(10,"xpy");
ALTER TABLE emp ADD PARTITION (PARTITION p3 VALUES LESS THAN MAXVALUE);
maxvalue表示最大可能的整数值
查看每个分区中记录数:
select partition_name part,partition_expression expr,partition_description descr,table_rows
from information_schema.partitions
where table_schema =schema()
and table_name='test';
partition_name:分区名
partition_expression: 分区字段
partition_description: 分区的范围
table_rows：当前分区内的数据量
values less than 子句还支持使用表达式?
MySQL支持在values less than 子句中使用表达式，比如，以日期作为range分区的分区列.
CREATE TABLE `emp` (
`id` INT(11) NOT NULL,
`ename` VARCHAR(30) NOT NULL,
loveDate DATE NOT NULL DEFAULT "1970-01-01"
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(loveDate))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN (2010) ENGINE = INNODB,
PARTITION p3 VALUES LESS THAN (2022) ENGINE = INNODB)
NULL值算啥?
在range分区中，分区键如果是null值会被当做一个最小值来处理。
range columns 可以支持非整数分区
DROp TABLE IF EXISTS emp;
CREATE TABLE `emp` (
`id` INT(11) NOT NULL,
`ename` VARCHAR(30) NOT NULL,
loveDate DATE NOT NULL DEFAULT "1970-01-01"
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS (loveDate)
(PARTITION p0 VALUES LESS THAN ("1900-01-01") ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN ("2010-01-01") ENGINE = INNODB,
PARTITION p3 VALUES LESS THAN ("2022-01-01") ENGINE = INNODB);
range分区小结
使用场景:
当需要删除过期的数据时，只需要简单的alter table emp drop partition p0 来删除p0分区中的数据，对于具有上百万条记录的表来说，删除分区要比运行delete语句快得多
explain partitions select count(1) from emp where id=1G
range分区的顺序必须是严格递增的
LIST分区
List分区是通过枚举一组特定的值，将其划分到一个分区中。
List分区通过下面的语句实现:
partition by list(expr)
expr是某列整数值，或者可以是通过一个表达式将某列值计算后返回整数值。
通过下面的方式定义分区
values in(val1,val2,val3,...)
与range分区不同，list分区没有严格的递增顺序要求
DROp TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
ename VARCHAR(30) NOT NULL
)
PARTITION BY LIST (id)
(
PARTITION p1 VALUES IN (10,11),
PARTITION p0 VALUES IN (5,6)
);
如果试图插入的列值或者分区表达式的返回值不包含分区值列表中时，那么insert操作会失败报错。
list分区不存在values lesss than maxvlaue这样包含其他值在内的定义方式。
将匹配的任何值都必须在值列表中找得到。
如果要使用非整数分区，可以创建list columns分区:
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
ename VARCHAR(30) NOT NULL
)
PARTITION BY LIST COLUMNS(ename)
(
PARTITION p1 VALUES IN ("dhy","dhylikexpy"),
PARTITION p0 VALUES IN ("ly")
);
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
e_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS(id,e_id)
(
PARTITION p0 VALUES LESS THAN (10,20),
PARTITION p1 VALUES LESS THAN (10,30)
);
(
// 字段
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段)
SUBPARTITION BY HASH(分区字段)
SUBPARTITIONS NUM(
partition <分区名称> values less than (Value),
partition <分区名称> values less than (Value),
...
partition <分区名称> values less than maxvalue
)
分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是对某个字段进行表达式运算如year(create_time)，使用range最终的值必须是数字
分区名称: 要保证不同，也可以采用 p0、p1、p2 这样的分区名称，
less than : 表示小于
Value : 表示要小于某个具体的值，如 less than (10) 那么分区字段的值小于10的都会被分到这个分区
maxvalue: 表示一个最大的值
NUM:子分区数
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
)
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10)
);
INSERT INTO emp VALUES(1,"dhy");
INSERT INTO emp VALUES(2,"dhy2");
INSERT INTO emp VALUES(3,"dhy3");
INSERT INTO emp VALUES(6,"dhy6");
INSERT INTO emp VALUES(7,"dhy7");
INSERT INTO emp VALUES(8,"dhy8");
仿照分区表，创建一个结构一样的普通表，插入一些不同的测试数据:
CREATE TABLE test_emp LIKE emp;
#将分区表改为普通表
ALTER TABLE test_emp REMOVE PARTITIONING;
INSERT INTO test_emp VALUES(8,"xpy");
查看分区表e中的数据分布:
SELECT partition_name part,partition_expression expr,partition_description descr,table_rows
FROM information_schema.partitions
WHERe table_name='emp';
执行交换分区命令
alter table emp exchange partition p1 with table test_emp;
