mysql面试我们发现他是报错的，因此我可以使用values less than maxvalue语句添加分区，maxv

Mysql基础篇--分区类型原创：洁癖汪洁癖是一只狗昨天分区是按照一定规则把一个表分解成多个更小的表，更容易管理的部分，当访问数据库应用而言，逻辑上是一个表或一个索引，实际上是可以有数个物理对象组成，每个分区都是一个独立的对象，可以独自处理作为表的一部分进行数据处理

分区的优点

和单个磁盘或文件相比，可以储存更多数据

优化查询，当where子句中包含分区条件的时候，可以扫描一个或几个分区提高查询条件，同时处理sum() ,count()聚合函数的查询可以容易在每个分区进行处理，最后汇总得到结果

对于不用的数据即将过期的数据，可以删除有关数据的某个分区

多个磁盘分散数据的查询，获得更大的查询的吞吐量

mysql 分区类型

RANGE分区：基于一个给定连续区间分为，把数据分配到不通风分区 LIST :和RANGE类似，是基于给定枚举值，把数据分配到不同的分区 HASH:基于分区的个数，把数据分配到不同的分区 KEY:和HASH类似无论哪种分区类型，要么分区表不包含主键或唯一键，要么分区表的主键或唯一键包含分区键，并且分区的名称是区分大小写的

RANGE分区

按照range分区表示利用取值范围将数据分成分区，区间要连续且不能重叠，使用values less than 进行分区定义，如下 mysql> create table emp( -> id int not null, -> name varchar(10), -> store_id int not null -> ) -> partition by range (store_id)( -> partition p0 values less than (10), -> partition p1 values less than (20), -> partition p2 values less than (30) -> ); Query OK, 0 rows affected (0.07 sec) mysql> insert into emp values (1,'jiepi',1),(2,'jiepi2',2),(3,'jiepi3',3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 我们把1-9存储到p0分区，10-19存储到p1,一次类推，但是当store_id大于30的时候会发生什么呢？

mysql> insert into emp values (50,'jiepi50',50); ERROR 1526 (HY000): Table has no partition for value 50 我们发现他是报错的，因此我可以使用values less than maxvalue语句添加分区，maxvalue 代表的是最大的可能的整数值,当服务器不知道把数据放到哪个分区的时候，我们就把这个数据放到这个分区。

mysql> alter table emp add partition (partition p3 values less than maxvalue); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into emp values (50,'jiepi50',50); Query OK, 1 row affected (0.01 sec) 分区使用的场景当需要删除过期的数据，只要简单的执行 alter table emp drop partition po来删除p0中的数据，对于上百万的记录表来说，删除一个分区的数据，往往比使用delete 有效的多.

经常运行包含分区键的查询，mysql可以快速的明确只有某一个或者某些分区需要扫面，因为并不是所有分区都要相关的数据,例如我们要查询store_id大于等25的数据，可能只要扫描p2分区。

LIST分区 list分区建立在离散的值列表告诉数据库应该放到个分区，list分区很多方面是和range分区相似，区别在于list分区从属于一个枚举列表的值的集合，range是一个连续区间的集合，

list分区使用 partition by list(expr) 实现，expr 是某列值，或一个基于某列值得表达式，然后通过 values in (value_list) 方式定义分区，始终value_list是用逗号分隔的整数列表，他也不必按照上面顺序声明。 mysql> create table expenses( -> id int not null, -> category int, -> amout decimal (10,3) -> )partition by list (category) ( -> partition p0 values in (3,5), -> partition p1 values in (1,10), -> partition p2 values in (4,9), -> partition p3 values in (2), -> partition p4 values in (6) -> ); Query OK, 0 rows affected (0.15 sec)

mysql> insert into expenses values (1,1,12.9),(2,2,12.8); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 注意的是list分区没有像range分区有values less than maxvalue,如果数据在list分区中不到会报错，所以定分区的时候必须包含多有可能的值。

mysql> insert into expenses values (1,11,12.9),(2,2,12.8); ERROR 1526 (HY000): Table has no partition for value 11 columns分区

Columns分区是在mysql5.5引进的分区类型，上面的分区是都是基于整形分区，是为了解决之前版本要进行函数或者表达式转换成整形，他分为 list columns 和 range columns ,他们支持整形日期，字符串，

整形:tinyint smallint ,mediumint ,int ,bigint ,其他类型不支持

日期：data ,datatime

字符串:char ,varcahr ,binary ,varbinary 不支持 text和blob 类型做分区键

除了添加了类型支持，并且还支持多列分区. mysql> CREATE TABLE m_num( -> a INT, -> b INT -> ) -> PARTITION BY RANGE COLUMNS(a,b)( -> PARTITION p0 VALUES LESS THAN (0,10), -> PARTITION p1 VALUES LESS THAN (10,20), -> PARTITION p2 VALUES LESS THAN (10,MAXVALUE), -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE) -> ); Query OK, 0 rows affected (0.16 sec) 他的分区规则优点稍微不一样，他是按照字段组的比较

mysql> insert into m_num values (1,10); Query OK, 1 row affected (0.01 sec)

mysql> select (1,10)<(10,10) from m_num; +----------------+ | (1,10)<(10,10) | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql> select -> partition_name part , -> partition_expression expr, -> partition_description descr, -> table_rows -> from -> information_schema.partitions -> where table_schema=schema() -> and table_name='m_num'; +------+---------+-------------------+------------+ | part | expr | descr | table_rows | +------+---------+-------------------+------------+ | p0 | a,b | 0,10 | 0 | | p1 | a,b | 10,20 | 1 | | p2 | a,b | 10,MAXVALUE | 0 | | p3 | a,b | MAXVALUE,MAXVALUE | 0 | +------+---------+-------------------+------------+ 4 rows in set (0.01 sec) 他的比较原则如下

我们在看一个例子 mysql> insert into m_num values (10,25); Query OK, 1 row affected (0.01 sec)

mysql> select partition_name part , partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name='m_num'; +------+---------+-------------------+------------+ | part | expr | descr | table_rows | +------+---------+-------------------+------------+ | p0 | a,b | 0,10 | 0 | | p1 | a,b | 10,20 | 1 | | p2 | a,b | 10,MAXVALUE | 1 | | p3 | a,b | MAXVALUE,MAXVALUE | 0 | +------+---------+-------------------+------------+ 4 rows in set (0.00 sec) Hash分区

hash分区主要用来分散热点读，确保数据在预先知道分区数目，尽可能的平均分布，在数据进行分区的时候，使用一个散列函数，计算数据到那个分区.

hash分区分为两类常规hash和线性hash分区，常规分区是使用模运算计算，而线性hash是一个线性的2的幂运算规则。

我们使用 partition by hash (expr) partitions num实现

mysql> create table emp_hash( -> id int not null, -> name varchar(20), -> store_id int not null -> ) -> partition by hash (store_id) partitions 4; Query OK, 0 rows affected (0.14 sec) hash分区的数据是按照N=MOD(expr,num)计算的，比如我插入一个store_id=234的数据，那么他存储的数据计算在N=MOD(234,4)=2 分区。

mysql> insert into emp_hash values (1,'jiepi',234); Query OK, 1 row affected (0.00 sec)

mysql> explain partitions select * from emp_hash where store_id=234 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: emp_hash partitions: p2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec) expr可以是一个表达式也可以是一某列的值，当进行插入删除更新操作的时候，这个表达式都要重新计算一次，所以在表达式比较复杂的时候，还是很消耗性能的，建议不要使用这种分区方式。

Hash分区在增加分区也是一个比较麻烦的事情，因为要把以前的数据重新计算分配到新的分区的需求，因此我们还有一种线性Hash分区，分区函数是一个线性的2的幂的运算规则。比常规hash分区多了一个linear.

mysql> create table emp_hash_linear( -> id int not null, -> name varchar(20), -> store_id int not null -> ) -> partition by linear hash (store_id) partitions 4; 他的就算方式如下公式首先，找到下一个大于等于num的2的幂，这个值为V，V通过下面公式计算，V=Power(2,Ceiling(Log(2,num)))

其次，设置N=F(column_list)&(V-1),

当N>=num,使用V=Ceiling(V/2),设置N=N&(V-1),N就是分区的位置，否则，上一步计算的N就是分区的位置。

mysql> insert into emp_hash_linear values (1,'jiepi',234); Query OK, 1 row affected (0.01 sec)

mysql> explain partitions select * from emp_hash_linear where store_id=234 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: emp_hash_linear partitions: p2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec) Key分区 KEY分区和HASH分区类似，使用hash进行分区，只不过Hash分区支持自定义表达式，而key不支持，使用的是mysql服务器提供的HASH函数，同时hash只支持整数分区，而key分区除了Text和BLOB其他类型都支持， mysql> create table emp_key( -> id int not null, -> name varchar(20), -> job varchar(20) -> ) -> partition by key (job) partitions 4 -> ; Query OK, 0 rows affected (0.11 sec)

mysql> create table emp_key_primary( -> id int not null, -> name varchar(20), -> job varchar(20), -> primary key (id) -> ) -> partition by key () partitions 4; Query OK, 0 rows affected (0.10 sec)

mysql> create table emp_key_unique( -> id int not null, -> name varchar(20), -> job varchar(20), -> unique key (id) -> ) -> partition by key () partitions 4; Query OK, 0 rows affected (0.11 sec) 需要注意的是，我们可以不指定分区间，默认会选取主键，其次是唯一键作为分区间，如果没有主键和唯一键，就不能不指定分区键了。key分区也是使用线性2的幂计算出数据在哪个分区。当我们处理大量记录时，能够有效的分散热点。 Mysql分区处理NULL值的方式

mysql不禁止在分区键上使用null,mysql是把null值按照最小值，或者零值进行处理，range分区是按照最小值处理，list分区中，null值必须出现在枚举中，否则不被接受，Hash和key分区，把null按照零值处理。

分区管理

添加，删除，重新定义分区处理上，range和list 语法基本一直，我们来来看一下

range删除分区

先创建range分区,再插入数据，查看数据在p2,在使用

alter table range_test drop partition p2; mysql> create table range_test( -> id int not null, -> separated date not null default '9999-12-31', -> store_id int not null -> ) -> partition by range(year(separated))( -> partition p0 values less than (1995), -> partition p1 values less than (2000), -> partition p2 values less than (2005), -> partition p3 values less than (2015) -> ); Query OK, 0 rows affected (0.10 sec)

mysql> insert into range_test values (1,'2002-12-01',1); Query OK, 1 row affected (0.00 sec)

mysql> select partition_name part , -> partition_expression expr, -> partition_description descr, -> table_rows from -> information_schema.partitions -> where table_schema=schema() -> and table_name='range_test'; +------+-----------------+-------+------------+ | part | expr | descr | table_rows | +------+-----------------+-------+------------+ | p0 | year(separated) | 1995 | 0 | | p1 | year(separated) | 2000 | 0 | | p2 | year(separated) | 2005 | 1 | | p3 | year(separated) | 2015 | 0 | +------+-----------------+-------+------------+ 4 rows in set (0.00 sec)

mysql> alter table range_test drop partition p2; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> select partition_name part , -> partition_expression expr, -> partition_description descr, -> table_rows from -> information_schema.partitions -> where table_schema=schema() -> and table_name='range_test'; +------+-----------------+-------+------------+ | part | expr | descr | table_rows | +------+-----------------+-------+------------+ | p0 | year(separated) | 1995 | 0 | | p1 | year(separated) | 2000 | 0 | | p3 | year(separated) | 2015 | 0 | +------+-----------------+-------+------------+ 3 rows in set (0.00 sec) 2.range添加分区

注意的是range分区只能在最大端增加分区，否则会报错 mysql> alter table range_test add partition ( partition p4 values less than (2030)); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table range_test\G *************************** 1. row *************************** Table: range_test Create Table: CREATE TABLE range_test ( id int(11) NOT NULL, separated date NOT NULL DEFAULT '9999-12-31', store_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */ 1 row in set (0.00 sec)

mysql> alter table range_test add partition ( partition p5 values less than (2025)); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition 3.range重新定义分区

mysql> show create table range_test\G; *************************** 1. row *************************** Table: range_test Create Table: CREATE TABLE range_test ( id int(11) NOT NULL, separated date NOT NULL DEFAULT '9999-12-31', store_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified

mysql> alter table range_test reorganize partition p3 into( -> partition p2 values less than (2005), -> partition p3 values less than (2015)); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table range_test\G *************************** 1. row *************************** Table: range_test Create Table: CREATE TABLE range_test ( id int(11) NOT NULL, separated date NOT NULL DEFAULT '9999-12-31', store_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */ 1 row in set (0.00 sec) 4.list分区重新定义分区

mysql> show create table list_test\G *************************** 1. row *************************** Table: list_test Create Table: CREATE TABLE list_test ( id int(11) NOT NULL, separated date NOT NULL DEFAULT '9999-12-31', store_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (store_id) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,2) ENGINE = InnoDB, PARTITION p2 VALUES IN (4,7) ENGINE = InnoDB, PARTITION p3 VALUES IN (6) ENGINE = InnoDB) */ 1 row in set (0.00 sec)

mysql> alter table list_test add partition (partition p4 values in (8)); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table list_test \G *************************** 1. row *************************** Table: list_test Create Table: CREATE TABLE list_test ( id int(11) NOT NULL, separated date NOT NULL DEFAULT '9999-12-31', store_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (store_id) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,2) ENGINE = InnoDB, PARTITION p2 VALUES IN (4,7) ENGINE = InnoDB, PARTITION p3 VALUES IN (6) ENGINE = InnoDB, PARTITION p4 VALUES IN (8) ENGINE = InnoDB) */ 1 row in set (0.00 sec)

mysql> alter table list_test reorganize partition p2 ,p3,p4 into ( -> partition p2 values in (4), -> partition p3 values in (6), -> partition p4 values in (7,8)); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table list_test\G *************************** 1. row *************************** Table: list_test Create Table: CREATE TABLE list_test ( id int(11) NOT NULL, separated date NOT NULL DEFAULT '9999-12-31', store_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (store_id) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,2) ENGINE = InnoDB, PARTITION p2 VALUES IN (4) ENGINE = InnoDB, PARTITION p3 VALUES IN (6) ENGINE = InnoDB, PARTITION p4 VALUES IN (7,8) ENGINE = InnoDB) */ 1 row in set (0.01 sec) HASH和KEY分区管理 1.减少Hash分区

mysql> create table hash_test( -> id int not null, -> name varchar(10), -> store_id int not null -> ) -> partition by hash(store_id) partitions 4; Query OK, 0 rows affected (0.09 sec)

mysql> alter table hash_test coalesce partition 2; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table hash_test add partition partitions 8; Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table hash_test\G *************************** 1. row *************************** Table: hash_test Create Table: CREATE TABLE hash_test ( id int(11) NOT NULL, name varchar(10) DEFAULT NULL, store_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (store_id) PARTITIONS 10 */ 1 row in set (0.00 sec) 注意alter table add partition partitions n 新增hash分区或key分区是在原表上再添加n个分区，不是增加到n个分区。希望此文对大家有所帮助，也希望大家持续关注转载。关注公众号获取相关资料请回复：typescript，springcloud，springboot，nodejs，nginx，mq，javaweb，java并发实战,java并发高级进阶,实战java并发,极客时间dubbo，kafka，java面试题，ES，zookeeper，java入门到精通，区块链，java优质视频，大数据，kotlin，瞬间之美，HTML与CSS，深入体验java开发，web开发CSS系列，javaweb开发详解，springmvc，java并发编程，spring源码，python，go，redis，docker，即获取相关资料。

扫码关注

微信扫一扫关注该公众号