MySQL表分区技术介绍

齐天qaq

已于 2024-02-29 14:38:40 修改

阅读量1.4k

点赞数 19

分类专栏： Mysql 文章标签： mysql 数据库

于 2024-02-27 14:50:09 首次发布

本文链接：https://blog.csdn.net/weixin_44065196/article/details/136321074

版权

Mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

2. 如何判断当前MySQL是否支持分区？

3.2.2 根据TIMESTAMP范围分区

3.2.3 根据DATE、DATETIME分区

1. 分区概述

分区功能并不是在存储引擎层完成的，因此不是只有 InnoDB 存储引擎支持分区，常见的存储引擎 MyISAM、NDB 等都支持。但也并不是所有的存储引擎都支持，如CSV, FEDORATED, MERGE等就不支持。在使用分区功能前,应该对选择的存储引擎对分区的支持有所了解。MySQL数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。MySQL 数据库支持的分区类型为水平分区，并不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL 数据库还不支持全局分区。

注*： 1.水平分区，指将同一表中不同行的记录分配到不同的物理文件中。

2.垂直分区，指将同一表中不同列的记录分配到不同的物理文件中。

1.1 表分区

表分区是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

简单来说：就是把一张表数据分块存储,提升索引的查询效率

当一个表中的数据量太大时，会面临两个问题，一是对数据的操作会变慢，比如select、join、update、delete时，会对全表操作；二是不便于存储，可能会出现剩余磁盘空间存储不下这张表的情况。而分区就可以在一定程度上解决这两个问题。
mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大，则myd,myi也会很大，查找数据很慢，此时可以利用mysql的分区功能，在物理上将该表对应的三个文件，分割成许多个小块，如此在查找数据时，只要知道这条数据在哪一块，然后在那一块找就可以，不用全部查找。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。
简要的说，分区就是将表物理截断，但在逻辑上依然是一个整体，开发人员在数据操作时仍然是对这个整体大表进行操作，之后由数据库底层自己去寻找对应的分区进行操作，数据库底层寻找分区这个过程对开发人员来说是透明的，这样在数据操作时可以只对特定分区操作以提高效率，存储时也可以将不同分区的物理文件分开存放。

注：当过滤条件为分区的字段时才会自动寻找分区，否则还是全表扫描

1.2 表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。
分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

1.3 表分区的好处

1. 存储更多。与单个磁盘或文件系统分区相比，可以存储更多的数据。

2. 便于删除。删除数据时，可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

3. 查询优化。

（1）满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，在查找时无需查找其他分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案还不曾这么做时，可以重新组织数据，来提高常用查询的效率。

（2）聚合函数（例如SUM()、COUNT()）的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果为总计所有分区得到的结果。

（3）通过跨多个磁盘来分散数据查询，获得更大的查询吞吐量。

1.4 分区表的限制因素

一个表最多只能又1024个分区，5.7版本时可以支持8196个分区。 8196个分区只8196个文件，linux中cd proc/1064/fd 文件描述符太多会影响io效率；还有ulimit -a 中open files最大可以同时打开1024个文件。可以修改，但是要和内存匹配，1G内存最多打开10万个文件。
MySQL5.1中，分区表达式必须是整数或者是返回整数表达式，mysql5.5中，可以直接使用列进行分区。
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。（mysql技术内幕书中说，分区列是唯一索引的一个组成部分也可以，不需要整个唯一索引列都是分区列）
分区表中无法使用外键约束。
MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。
必须使用分区字段才行，不然分区查询就会失败。走所有分区。

2. 如何判断当前MySQL是否支持分区？

mysql> show plugins

即：看名为partition的插件是否为active，active表示支持分区。

并且同一个数据库，不同表支持分区可以是不同的存储引擎，但是表分区后所有的分区都必须和表使用相同引擎。

MyISAM和InnoDB都支持分区。
MySQL 8都无需插件即可支持分区，且只有InnoDB支持，MyISAM不支持分区。
MySQL 5.7 的NDB支持分区有自己的规则。
MySQL只支持水平分区，对垂直分区的支持无计划。

3. 分区类型详解

3.1 MySQL支持分区类型

RANGE 分区：行数据基于属于一个给定连续区间的列值，把多行分配给分区。MySQL 5.5开始支持RANGE COLUMNS的分区。
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。MySQL 5.5开始支持 LIST COLUMNS 的分区。
HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，返回值不能为负数。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

唯一索引可以是允许NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。

如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。

说明：在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列(BLOB，TEXT类型除外)作为分区键。

3.2 RANGE分区

3.2.1 根据数值范围分区

示例如下：

创建表和分区逻辑，并插入数据

-- 建表
drop table if exists employees;
create table employees(
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null default 0,
    store_id int not null default 0
)engine=innodb default charset=utf8
partition by range(store_id)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21)
);
-- 插入数据
insert into employees (id,fname,lname,hired,store_id) values(1,'张三','张','2015-05-04',1);
insert into employees (id,fname,lname,hired,store_id) values(2,'李四','李','2016-10-01',5);
insert into employees (id,fname,lname,hired,store_id) values(3,'王五','王','2016-11-14',10);
insert into employees (id,fname,lname,hired,store_id) values(4,'赵六','赵','2017-08-24',15);
insert into employees (id,fname,lname,hired,store_id) values(5,'田七','田','2018-05-20',20);

查询数据如下：

mysql> SELECT * FROM employees;

查看data文件如下：

按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求。

对于表employees，由于我们定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。

例如：

增加了一个编号为第21的商店(7,‘周九’,‘周’,‘2018-07-24’,21)

执行：mysql> insert into employees (id,fname,lname,hired,store_id) values(7,'周九','周','2018-07-24',21);
执行结果：
ERROR 1526 (HY000): Table has no partition for value 21

对于上述问题，我们可以对分区添加一个MAXVALUE值的分区。MAXVALUE可以理解为正无穷，因此所有大于等于21且小于MAXVALUE的值被存放在p4分区。

partition by range(store_id)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21),
    partition p4 values less than MAXVALUE
);

3.2.2 根据TIMESTAMP范围分区

示例如下：

drop table if exists report;
create table report(
  r_id int not null,
  r_status varchar(20) not null,
  r_updated timestamp not null default current_timestamp on update current_timestamp
)
partition by range(unix_timestamp(r_updated))(
  partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')),
  partition p8 values less than (unix_timestamp('2010-01-01 00:00:00')),
  partition p9 values less than maxvalue
);

3.2.3 根据DATE、DATETIME分区

添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势：
示例如下：

create table member(
  firstname varchar(25) not null,
  lastname varchar(25) not null,
  username varchar(16) not null,
  email varchar(35),
  joined date not null
)
partition by range columns(joined)(
  partition p0 values less than ('1960-01-01'),
  partition p1 values less than ('1990-01-01'),
  partition p2 values less than maxvalue
);

3.2.4 根据多列范围分区

示例如下：

drop table if exists rd;
create table rd(
  a int,
  b int
)
partition by range columns(a,b)(
  partition p0 values less than (0,50),
  partition p1 values less than (50,100),
  partition p2 values less than (maxvalue,maxvalue)
)

3.2.5 RANGE分区适用场合

当需要删除一个分区上的“旧”数据时，只删除分区即可。
想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。
经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如”select count(*) from staff where year(separated) = 200 group by store_id;”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

3.3 LIST分区

list就是枚举的意思，list分区就是在创建各分区时具体指定哪些值属于这些分区。
根据具体数值分区，每个分区数值不重叠，使用PARTITION BY LIST、VALUES IN关键字。
跟Range分区类似，不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。
类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

示例：假定有20个音像店，分布在4个有经销权的地区，如下表所示。
+------------------+--------------------------------------+
| 地区 | 音像店 ID 号 |
+------------------+--------------------------------------+
| 北区 | 3, 5, 6, 9, 17 |
| 东区 | 1, 2, 10, 11, 19, 20 |
| 西区 | 4, 12, 13, 14, 18 |
| 中心区 | 7, 8, 15, 16 |
+------------------+--------------------------------------+

mysql-> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT NOT NULL,
-> store_id INT NOT NULL
-> ) ENGINE=Myisam DEFAULT CHARSET=utf8
-> PARTITION BY LIST (store_id) (
-> PARTITION pNorth VALUES IN (3, 5, 6, 9, 17),
-> PARTITION pEast VALUES IN (1, 2, 10, 11, 19, 20),
-> PARTITION pWest VALUES IN (4, 12, 13, 14, 18),
-> PARTITION pCentral VALUES IN (7, 8, 15, 16)
-> );

        这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE staff DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE（删除）“DELETE FROM staff WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。
        如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。
        当插入多条数据出错时，如果表的引擎支持事务（Innodb），则不会插入任何数据；如果不支持事务，则出错前的数据会插入，后面的不会执行。
        与Range分区相同，添加COLUMNS关键字可支持非整数和多列。

3.4 HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。
示例：

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by hash(year(hired))
partitions 4;

Hash分区也存在与传统Hash分表一样的问题，可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法－线性Hash分区，只需要在定义分区时添加LINEAR关键字。

线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。

LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by linear hash(year(hired))
partitions 4;

3.5 KEY分区

Key分区与Hash分区很相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。

示例如下：

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by key(store_id)
partitions 4;

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

另外，当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错。

3.6 子分区（组合分区）

子分区（subpartitioning）是在分区的基础上再进行分区，也称为复合分区（composite partitioning）MySQL数据库允许在RANGE和LIST的分区上在进行HASH或者KEY的子分区。

使用SUBPARTITION子句显式地定义子分区，为各个子分区指定选项。注意事项如下：

1. 每个子分区的数量必须相同。

2. 要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区。

3. 每个SUBPARTITION子句必须包括子分区的一个名字。

4.子分区的名字必须是唯一的。

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

5. 子分区可以用于特别大的表，在多个磁盘间分别分配数据和索引。假设有6个磁盘，分别为/disk0、/disk1、/disk2等。考虑如下示例：

CREATE TABLE ts (id INT, purchased DATE)
    ENGINE = MYISAM
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0
                DATA DIRECTORY = '/disk0/data'
                INDEX DIRECTORY = '/disk0/idx',
            SUBPARTITION s1
                DATA DIRECTORY = '/disk1/data'
                INDEX DIRECTORY = '/disk1/idx'
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2
                DATA DIRECTORY = '/disk2/data'
                INDEX DIRECTORY = '/disk2/idx',
            SUBPARTITION s3
                DATA DIRECTORY = '/disk3/data'
                INDEX DIRECTORY = '/disk3/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4
                DATA DIRECTORY = '/disk4/data'
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s5
                DATA DIRECTORY = '/disk5/data'
                INDEX DIRECTORY = '/disk5/idx'
        )
    );

由于Innodb存储引擎使用表空间自动进行数据和索引的管理，因此会忽略DATA DIRECTORY和INDEX DIRECTORY语法，因此上述的分区表的数据和索引文件分开放置对其是无效的。

4. 分区管理

4.1 删除分区

mysql> alter table employees drop partition pWest;

4.2 新增分区

#range添加新分区 
mysql> alter table employees add partition ( partition p4 values less than (26) ); 
  
#list添加新分区 
mysql> alter table employees add partition( partition pSouth values in (21, 22, 23) ); 
  
#hash重新分区 
mysql> alter table employees add partition partitions 5;

齐天qaq

关注

19
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
MySQL表分区技术介绍

分区功能并不是在存储引擎层完成的，因此不是只有 InnoDB 存储引擎支持分区，常见的存储引擎 MyISAM、NDB 等都支持。但也并不是所有的存储引擎都支持，如CSV, FEDORATED, MERGE等就不支持。在使用分区功能前,应该对选择的存储引擎对分区的支持有所了解。MySQL数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。
复制链接

扫一扫