mysql面试--分区

Mysql基础篇--分区类型

原创： 洁癖汪 洁癖是一只狗 昨天

分区是按照一定规则把一个表分解成多个更小的表，更容易管理的部分，当访问数据库应用而言，逻辑上是一个表或一个索引，实际上是可以有数个物理对象组成，每个分区都是一个独立的对象，可以独自处理作为表的一部分进行数据处理

分区的优点

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

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

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

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

mysql 分区类型

RANGE分区：基于一个给定连续区间分为，把数据分配到不通风分区

LIST :和RANGE类似，是基于给定枚举值，把数据分配到不同的分区

HASH:基于分区的个数，把数据分配到不同的分区

KEY:和HASH类似

无论哪种分区类型，要么分区表不包含主键或唯一键，要么分区表的主键或唯一键包含分区键，并且分区的名称是区分大小写的

RANGE分区

按照range分区表示利用取值范围将数据分成分区，区间要连续且不能重叠，使用values  less than 进行分区定义，如下

mysql> create table emp(

->id intnotnull,

->name varchar(10),

->store_id intnotnull

-> )

-> partitionbyrange (store_id)(

->partition p0 values less than (10),

->partition p1 values less than (20),

->partition p2 values less than (30)

-> );

Query OK,0rows affected (0.07sec)

mysql> insert into emp values (1,'jiepi',1),(2,'jiepi2',2),(3,'jiepi3',3);

Query OK,3rows affected (0.01sec)

Records:3Duplicates:0Warnings:0

我们把1-9存储到p0分区，10-19存储到p1,一次类推，但是当store_id大于30的时候会发生什么呢？

mysql>insertintoempvalues(50,'jiepi50',50);

ERROR1526(HY000): Table has no partitionforvalue50

我们发现他是报错的，因此我可以使用values less than maxvalue语句添加分区，maxvalue 代表的是最大的可能的整数值,当服务器不知道把数据放到哪个分区的时候，我们就把这个数据放到这个分区。

mysql>alter table empaddpartition(partition p3 values less than maxvalue);

Query OK,0rowsaffected(0.08sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> insertintoempvalues(50,'jiepi50',50)

;

Query OK,1rowaffected(0.01sec)

分区使用的场景

当需要删除过期的数据，只要简单的执行 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 intnotnull,

->category int,

->amout decimal (10,3)

-> )partitionbylist (category) (

->partition p0 valuesin(3,5),

->partition p1 valuesin(1,10),

->partition p2 valuesin(4,9),

->partition p3 valuesin(2),

->partition p4 valuesin(6)

-> );

Query OK,0rows affected (0.15sec)

mysql> insert into expenses values (1,1,12.9),(2,2,12.8);

Query OK,2rows affected (0.01sec)

Records:2Duplicates:0Warnings:0

注意的是list分区没有像range分区有values less than maxvalue,如果数据在list分区中不到会报错，所以定分区的时候必须包含多有可能的值。

mysql>insertintoexpensesvalues(1,11,12.9),(2,2,12.8);

ERROR1526(HY000): Table has no partitionforvalue11

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,0rows affected (0.16sec)

他的分区规则优点稍微不一样，他是按照字段组的比较

mysql> insert into m_num values (1,10);

Query OK,1row affected (0.01sec)

mysql> select (1,10)<(10,10) from m_num;

+----------------+

| (1,10)<(10,10) |

+----------------+

|              1 |

+----------------+

1rowinset (0.00sec)

mysql> select

-> partition_name part ,

-> partition_expression expr,

-> partition_description descr,

-> table_rows

-> from

-> information_schema.partitions

-> where table_schema=schema()

->andtable_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 rowsinset (0.01 sec)

他的比较原则如下

我们在看一个例子

mysql> insert into m_num values (10,25);

Query OK,1row affected (0.01sec)

mysql> select  partition_name part , partition_expression expr, partition_description descr, table_rows from information_schema.partitions  where table_schema=schema()andtable_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 rowsinset (0.00 sec)

Hash分区

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

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

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

mysql> create table emp_hash(

->id intnotnull,

->name varchar(20),

->store_id intnotnull

-> )

-> partitionbyhash (store_id) partitions4;

Query OK,0rows affected (0.14sec)

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 intnotnull,

->name varchar(20),

->store_id intnotnull

-> )

-> partitionbylinear hash (store_id) partitions4;

他的就算方式如下公式

首先，找到下一个大于等于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 intnotnull,

->name varchar(20),

->job varchar(20)

-> )

-> partitionbykey (job) partitions4

-> ;

Query OK,0rows affected (0.11sec)

mysql> create table emp_key_primary(

->id intnotnull,

->name varchar(20),

->job varchar(20),

->primary key (id)

-> )

-> partitionbykey () partitions4;

Query OK,0rows affected (0.10sec)

mysql> create table emp_key_unique(

->id intnotnull,

->name varchar(20),

->job varchar(20),

->unique key (id)

-> )

-> partitionbykey () partitions4;

Query OK,0rows affected (0.11sec)

需要注意的是，我们可以不指定分区间，默认会选取主键，其次是唯一键作为分区间，如果没有主键和唯一键，就不能不指定分区键了。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 intnotnull,

->  separated datenotnull default'9999-12-31',

->  store_id intnotnull

-> )

-> partition by range(year(separated))(

->   partition p0values less than (1995),

->   partition p1 values less than (2000),

->   partition p2 values less than (2005),

->   partition p3 values less than (2015)

-> );

Query OK,0rows affected (0.10sec)

mysql> insert into range_test values (1,'2002-12-01',1);

Query OK,1row affected (0.00sec)

mysql> select  partition_name part ,

->  partition_expression expr,

->  partition_description descr,

->  table_rows from

->  information_schema.partitions

->   where table_schema=schema()

->andtable_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 rowsinset (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()

->andtable_name='range_test';

+------+-----------------+-------+------------+

|

part| expr            |descr| table_rows |

+------+-----------------+-------+------------+

| p0   |year(separated)| 1995  |0|

|

p1| year(separated) |2000|          0 |

| p3   |year(separated)| 2015  |0|

+------+-----------------+-------+------------+

3 rowsinset (0.00 sec)

2.range添加分区

 注意的是range分区只能在最大端增加分区，否则会报错

mysql> alter table range_test add partition ( partition p4 values less than (2030));

Query OK,0rows affected (0.07sec)

Records:0Duplicates:0Warnings:0

mysql> show create table range_test\G

***************************1.row ***************************

Table: range_test

Create Table: CREATE TABLE `range_test` (

`id`int(11)NOTNULL,

`separated`dateNOTNULLDEFAULT'9999-12-31',

`store_id`int(11)NOTNULL

) ENGINE=InnoDBDEFAULTCHARSET=latin1

/*!50100PARTITION 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) */

1rowinset(0.00sec)

mysql> alter table range_test add partition ( partition p5 values less than (2025));

ERROR1493(HY000): VALUES LESS THAN value must be strictly increasingforeachpartition

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)NOTNULL,

`separated`dateNOTNULLDEFAULT'9999-12-31',

`store_id`int(11)NOTNULL

) ENGINE=InnoDBDEFAULTCHARSET=latin1

/*!50100PARTITION BY LIST (store_id)

(PARTITION p0 VALUESIN(3,5) ENGINE = InnoDB,

PARTITION p1 VALUESIN(1,2) ENGINE = InnoDB,

PARTITION p2 VALUESIN(4,7) ENGINE = InnoDB,

PARTITION p3 VALUESIN(6) ENGINE = InnoDB) */

1rowinset(0.00sec)

mysql> alter table list_test add partition (partition p4 valuesin(8));

Query OK,0rows affected (0.06sec)

Records:0Duplicates:0Warnings:0

mysql> show create table list_test \G

***************************1.row ***************************

Table: list_test

Create Table: CREATE TABLE `list_test` (

`id`int(11)NOTNULL,

`separated`dateNOTNULLDEFAULT'9999-12-31',

`store_id`int(11)NOTNULL

) ENGINE=InnoDBDEFAULTCHARSET=latin1

/*!50100PARTITION BY LIST (store_id)

(PARTITION p0 VALUESIN(3,5) ENGINE = InnoDB,

PARTITION p1 VALUESIN(1,2) ENGINE = InnoDB,

PARTITION p2 VALUESIN(4,7) ENGINE = InnoDB,

PARTITION p3 VALUESIN(6) ENGINE = InnoDB,

PARTITION p4 VALUESIN(8) ENGINE = InnoDB) */

1rowinset(0.00sec)

mysql> alter table list_test reorganize partition p2 ,p3,p4 into (

-> partition p2 valuesin(4),

-> partition p3 valuesin(6),

-> partition p4 valuesin(7,8));

Query OK,0rows affected (0.19sec)

Records:0Duplicates:0Warnings:0

mysql> show create table list_test\G

***************************1.row ***************************

Table: list_test

Create Table: CREATE TABLE `list_test` (

`id`int(11)NOTNULL,

`separated`dateNOTNULLDEFAULT'9999-12-31',

`store_id`int(11)NOTNULL

) ENGINE=InnoDBDEFAULTCHARSET=latin1

/*!50100PARTITION BY LIST (store_id)

(PARTITION p0 VALUESIN(3,5) ENGINE = InnoDB,

PARTITION p1 VALUESIN(1,2) ENGINE = InnoDB,

PARTITION p2 VALUESIN(4) ENGINE = InnoDB,

PARTITION p3 VALUESIN(6) ENGINE = InnoDB,

PARTITION p4 VALUESIN(7,8) ENGINE = InnoDB) */

1rowinset(0.01sec)

HASH和KEY分区管理

1.减少Hash分区

mysql>  create table hash_test(

->   idintnotnull,

->   name varchar(10),

->   store_idintnotnull

->   )

->   partition by hash(store_id) partitions4;

Query OK,0rows affected (0.09sec)

mysql> alter table hash_test coalesce partition2;

Query OK,0rows affected (0.16sec)

Records:0Duplicates:0Warnings:0

mysql> show create table hash_test \G

***************************1.row ***************************

Table: hash_test

Create Table: CREATE TABLE `hash_test` (

`id`int(11)NOTNULL,

`name` varchar(10)DEFAULTNULL,

`store_id`int(11)NOTNULL

) ENGINE=InnoDBDEFAULTCHARSET=latin1

/*!50100PARTITION BY HASH (store_id)

PARTITIONS2*/

1rowinset(0.00sec)

2.增加hash分区

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，即获取相关资料。

扫码关注

微信扫一扫

关注该公众号