Mysql基础篇--分区类型

小土豆Yuki

发布于 2020-06-15 17:25:32

8410

发布于 2020-06-15 17:25:32

文章被收录于专栏：洁癖是一只狗洁癖是一只狗

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

分区的优点

和单个磁盘或文件相比，可以储存更多数据
优化查询，当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> 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 2 */
1 row in set (0.00 sec)

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个分区。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-11-11，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

云数据库 SQL Server

本文分享自洁癖是一只狗微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

sql

云数据库 SQL Server

登录后参与评论

0 条评论

热度