MySQL：表分区探讨

不念不忘少年蓝@ 2022-03-22 13:46 189阅读 0赞

官方介绍：[https://dev.mysql.com/doc/refman/5.6/en/partitioning.html][https_dev.mysql.com_doc_refman_5.6_en_partitioning.html]

一: Mysql 分区简介

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象分解成一些小对象。

MySQL分区即可以对数据进行分区也可以对索引进行分区。

分区类型

range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区  
list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区  
hash分区：基于给定的分区个数，把数据分配到不同的分区  
key分区：类似于hash分区  
注意：无论哪种分区，要么你分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其它字段分区。

MySQL分区的有限主要包括以下4个方面：

和单个磁盘或者文件系统分区相比，可以存储更多数据  
优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果  
对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据  
跨多个磁盘来分散数据查询，以获得更大的查询吞吐量  
分区和水平分表功能类似，将一个大表的数据分割到多张小表中去，由于查询不需要全表扫描了，只需要扫描某些分区，所以分区能提高查询速度。

水平分表需要用户预先手动显式创建出多张分表(如tbl\_user0, tbl\_user1, tbl\_user2)，在物理上实实在在的创建多张表，通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。

分区是MySQL的一个插件Plugin功能，将一张大表的数据在数据库底层分成多个分区文件(如tbl\_user\#P\#p0.ibd, tbl\_user\#P\#p1.ibd, tbl\_user\#P\#p2.ibd)，和水平分表不同的是分区不需要显式的创建“分表”,数据库会自动创建分区文件的，用户看到的只是一张普通的表，其实是对应的是多个分区，这个是对用户是屏蔽的、透明的，在使用上和使用一张表完全一样，不需要借助任何功能来实现。分区是一种逻辑上的水平分表，在物理层面还是一张表。

二：数据库文件

CREATE TABLE \`tbl\_user\_innodb\` (  
   \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
   \`username\` varchar(255) DEFAULT NULL,  
   \`email\` varchar(20) DEFAULT NULL,  
   \`age\` tinyint(4) DEFAULT NULL,  
   \`type\` int(11) DEFAULT NULL,  
   \`create\_time\` datetime DEFAULT CURRENT\_TIMESTAMP,  
   PRIMARY KEY (\`id\`)  
 ) ENGINE=InnoDB AUTO\_INCREMENT=5100002 DEFAULT CHARSET=utf8;

CREATE TABLE \`tbl\_user\_myisam\` (  
   \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
   \`username\` varchar(255) DEFAULT NULL,  
   \`email\` varchar(20) DEFAULT NULL,  
   \`age\` tinyint(4) DEFAULT NULL,  
   \`type\` int(11) DEFAULT NULL,  
   \`create\_time\` datetime DEFAULT CURRENT\_TIMESTAMP,  
   PRIMARY KEY (\`id\`)  
 ) ENGINE=myisam AUTO\_INCREMENT=5100002 DEFAULT CHARSET=utf8;  
  
通过show variables like ‘%datadir%’;命令查看mysql的data存放目录，进入所在的数据库目录(如test)，不同的引擎数据库文件格式不同

myisam   
.frm : 存储表结构  
.MYD : 存储表数据  
.MYI : 存储索引文件  
innodb: 只有设置成独立表空间才能做成功表分区   
.frm : 表结构  
.ibd : 数据 + 索引

三：插入500W条数据

CREATE TABLE \`tbl\_user\_no\_part\` (  
   \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
   \`username\` varchar(255) DEFAULT NULL,  
   \`email\` varchar(20) DEFAULT NULL,  
   \`age\` tinyint(4) DEFAULT NULL,  
   \`type\` int(11) DEFAULT NULL,  
   \`create\_time\` datetime DEFAULT CURRENT\_TIMESTAMP,  
   PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8;

\-- 修改mysql默认的结束符号，默认是分号；但是在函数和存储过程中会使用到分号导致解析不正确  
delimiter $$

\-- 随机生成一个指定长度的字符串  
create function rand\_string(n int) returns varchar(255)   
begin   
 \# 定义三个变量  
 declare chars\_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';  
 declare return\_str varchar(255) default '';  
 declare i int default 0;

while i < n do   
   set return\_str = concat(return\_str, substring(chars\_str, floor(1+rand()\*52), 1));  
   set i = i + 1;  
 end while;  
 return return\_str;  
end $$

\-- 创建插入的存储过程  
create procedure insert\_user(in start int(10), in max\_num int(10))  
begin  
    declare i int default 0;   
    set autocommit = 0;    
    repeat  
        set i = i + 1;  
        insert into tbl\_user\_no\_part values ((start+i) ,rand\_string(8), concat(rand\_string(6), '@random.com'), 1+FLOOR(RAND()\*100), 3, now());  
        until i = max\_num  
    end repeat;  
   commit;  
end $$

\-- 将命令结束符修改回来  
delimiter ;

\-- 调用存储过程，插入500万数据，需要等待一会时间，等待执行完成  
call insert\_user(100001,5000000);  
\-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G内存用了8分钟才执行完

select count(\*) from tbl\_user\_no\_part;  
  
四：range分区

MySQL有五种分区类型 range、list、hash、key、子分区，其中最常用的是range和list分区

\-- 查看mysql版本  
select version();

\-- 查看分区插件是否激活 partition active  
show plugins;

对于低版本的MySQL，如果InnoDB引擎要想分区成功，需要在my.conf中设置innodb\_file\_per\_table=1 设置成独立表空间  
独立表空间：每张表都有对应的.ibd文件  
innodb\_file\_per\_table=1  
  
range分区：给定一个连续区间的范围值进行分区，某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段，如 日期范围, 连续的数字

\-- 语法  
create table <table> (  
    // 字段  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO\_INCREMENT=1  
partition by range (分区字段) (  
  partition <分区名称> values less than (Value),  
  partition <分区名称> values less than (Value),  
  ...  
  partition <分区名称> values less than maxvalue  
);  
  
range：表示按范围分区   
分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是对某个字段进行表达式运算如year(create\_time)，使用range最终的值必须是数字   
分区名称: 要保证不同，也可以采用 p0、p1、p2 这样的分区名称，   
less than : 表示小于   
Value : 表示要小于某个具体的值，如 less than (10) 那么分区字段的值小于10的都会被分到这个分区   
maxvalue: 表示一个最大的值

注意：range 对应的分区键值必须是数字值，可以使用range columns(分区字段) 对非int型做分区，如字符串，对于日期类型的可以使用year()、to\_days()、to\_seconds()等函数

create table emp\_date(  
    id int not null,  
    separated date not null default '9999-12-31'  
)  
partition by range columns(separated) (  
    partiontion p0 values less than ('1990-01-01'),  
    partiontion p0 values less than ('2001-01-01'),  
    partiontion p0 values less than ('2018-01-01')  
);  
  
分区可以在创建表的时候进行分区，也可以在创建表之后进行分区

alter table <table> partition by RANGE(id) (  
    PARTITION p0 VALUES LESS THAN (1000000),  
    PARTITION p1 VALUES LESS THAN (2000000),  
    PARTITION p2 VALUES LESS THAN (3000000),  
    PARTITION p3 VALUES LESS THAN (4000000),  
    PARTITION p4 VALUES LESS THAN MAXVALUE   
);  
  
\-- 创建分区表  
CREATE TABLE \`tbl\_user\_part\` (  
   \`id\` int(11) NOT NULL ,  
   \`username\` varchar(255) DEFAULT NULL,  
   \`email\` varchar(20)     DEFAULT NULL,  
   \`age\` tinyint(4)        DEFAULT NULL,  
   \`type\` int(11)          DEFAULT NULL,  
   \`create\_time\` datetime  DEFAULT CURRENT\_TIMESTAMP  
   -- PRIMARY KEY (\`id\`,\`age\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8  
PARTITION BY RANGE (age) (  
    PARTITION p0 VALUES LESS THAN (20),  
    PARTITION p1 VALUES LESS THAN (40),  
    PARTITION p2 VALUES LESS THAN (60),  
    PARTITION p3 VALUES LESS THAN (80),  
    PARTITION p4 VALUES LESS THAN MAXVALUE  
);  
  
在创建分区的时候经常会遇到这个错误：A PRIMARY KEY must include all columns in the table’s partitioning function。意思是说分区的字段必须是要包含在主键当中。 可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。在做分区表时，选择分区的依据字段时要谨慎，需要仔细斟酌这个字段拿来做为分区依据是否合适，这个字段加入到主键中做为复合主键是否适合。

使用range分区时表结构要么没有主键，要么分区字段必须是主键。

\-- 将tbl\_user\_no\_part表中的数据复制到tbl\_user\_part表中(数据量比较多，可能要等几分钟)  
INSERT INTO tbl\_user\_part SELECT \* FROM tbl\_user\_no\_part;

SELECT count(\*) FROM tbl\_user\_no\_part WHERE age > 25 AND age < 30;  
SELECT count(\*) FROM tbl\_user\_part WHERE age > 25 AND age < 30;

从查询结果看，当查询条件中包括分区字段时，分区确实能提高查询效率

五：list 分区

设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列。list分区使用in表示一些固定的值的列表

\-- 语法  
create table <table> (  
    // 字段  
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO\_INCREMENT=1  
partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (  
  partition <分区名称> values IN (Value1，Value2, Value3),  
  ...  
  partition <分区名称> values IN (Value4, Value5),  
);  
columns分区

在mysql5.5之前range分区和list分区只支持整数分区，可以通过额外的函数运算或者额外的转换从而得到一个整数。columns分区分为 range columns 和 list columns 两种，支持整数(tinyint到bigint， 不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三大数据类型。

columns分区支持一个或者多个字段作为分区键，不支持表达式作为分区键，这点区别于range 和 list 分区。需要注意的是range columns 分区键的比较是基于元组的比较，也就是基于字段组的比较，这和range分区有差异。

create talbe rc3 (  
    a int,  
    b int  
)  
partition by range columns(a, b) (  
    partition p01 values less than (0, 10),  
    partition p02 values less than (10, 10),  
    partition p03 values less than (10, 20),  
    partition p04 values less than (10, 35),  
    partition p05 values less than (10, maxvalue),  
    partition p06 values less than (maxvalue, maxvalue),  
);

insert into rc3(a, b) values(1, 10);

select (1, 10) < (10, 10) from dual;

\-- 根据结果存放到p02分区上了  
select  
    partition\_name,  
    partition\_expression,  
    partition\_description,  
    table\_rows  
from information\_schema.partitions  
where table\_schema = schema() and table\_name = 'rc3';     
  
range columns分区键的比较(元组的比较)其实就是多列排序，先根据a字段排序再根据b字段排序，根据排序结果来分区存放数据，和range单字段的分区排序的规则实际上是一样的

六：hash分区

Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中可能的平均分布。对一个表执行Hash分区时，mysql会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区。

mysql支持两种hash分区，

常规hash分区和线性hash分区(linear hash分区)，常规hash分区使用的是取模算法，对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)  
线性hash分区使用的是一个线性的2的幂运算法则。  
对指定的字段(整型字段)进行哈希，将记录平均的分配到分区中，使得所有分区的数据比较平均。 hash分区只需要指定要分区的字段和要分成几个分区,   
expr是一个字段值或者基于某列值云散返回的一个整数，expr可以是mysql中有效的任何函数或者其它表达式，只要它们返回一个即非常熟也非随机数的整数。   
num 表示分区数量

\-- HASH  
create table <table> (  
    // 字段  
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO\_INCREMENT=1  
PARTITION BY HASH(expr)  
PARTITIONS <num>;

[https_dev.mysql.com_doc_refman_5.6_en_partitioning.html]: https://dev.mysql.com/doc/refman/5.6/en/partitioning.html