MySql调优（二）表分区

w_t_y_y

已于 2024-01-31 14:10:01 修改

阅读量357

点赞数

分类专栏：关系型数据库MySQL 文章标签： mysql android 数据库

于 2021-06-01 11:36:16 首次发布

本文链接：https://blog.csdn.net/w_t_y_y/article/details/117439882

版权

关系型数据库MySQL 专栏收录该内容

30 篇文章 1 订阅

订阅专栏

一、介绍：

1、概念：表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面，通过show variables like '%datadir%';可以查看（也可以通过my.cnf中的datadir来查看），一张表主要对应三个文件：frm存放表结构，myd存放表数据，myi存表索引。如果一张表的数据量太大的话，myd,myi就会很大，查找数据就会很慢。利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样查找数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这时，可以把数据分配到不同的磁盘里面去。

2、表分区与分表的区别：分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

3、表分区的优点：

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

（2）对于已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

（3）一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

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

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

4、分区表的限制因素：

（1）一个表最多只能有1024个分区。

（2） MySQL5.1中，分区表达式必须是整数或者返回整数的表达式。MySQL5.5提供了非整数表达式分区的支持。

（3）如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

（4）分区表中无法使用外键约束。

（5）MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

5、判断当前MySQL是否支持分区：show variables like '%partition%';

have_partintioning 的值为YES，表示支持分区。

6、MySQL支持的分区类型：

（1）RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

（2）LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

（3）HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

（4）KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

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

二、RANGE分区：根据范围分区，范围应该连续但是不重叠，使用PARTITION BY RANGE, VALUES LESS THAN关键字。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

1、语法：

（1）根据数值范围：

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=myisam   DEFAULT CHARSET=utf8mb4
partition by range(id)(
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p2 values less than (30),
    partition p3 values less than (40),
    partition p4 values less than MAXVALUE 
);

注意最后一句没有的话插入id>40的数据会报错。看下生成的文件：

测试：插入几条数据，可以看到分别插入了p0和p4分区。

insert into test(user_name) VALUES('测试1');
insert into test(id,user_name) VALUES(50,'测试50');

（2）根据TIMESTAMP范围：

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` timestamp not null default current_timestamp on update current_timestamp
) ENGINE=myisam   DEFAULT CHARSET=utf8mb4
partition by range(unix_timestamp(birthday))(
  partition p0 values less than (unix_timestamp('2000-01-01 00:00:00')),
  partition p1 values less than (unix_timestamp('2010-04-01 00:00:00')),
  partition p2 values less than maxvalue
);

（3）根据DATE、DATETIME范围：添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势：

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` date not null
) ENGINE=myisam   DEFAULT CHARSET=utf8mb4
partition by range columns(birthday)(
  partition p0 values less than ('2000-01-01'),
  partition p1 values less than ('2020-01-01'),
  partition p2 values less than maxvalue
);

（4）根据多列范围：

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` date not null
) ENGINE=myisam   DEFAULT CHARSET=utf8mb4
partition by range columns(id,user_age)(
  partition p0 values less than (0,10),
  partition p1 values less than (10,20),
  partition p2 values less than (20,30),
  partition p3 values less than (30,40),
  partition p4 values less than (40,50),
  partition p5 values less than (maxvalue,maxvalue)
);

2、RANGE分区在如下场合特别有用：

（1）当需要删除一个分区上的数据时，只删除分区即可。如使用”alter table test drop partition p0;”，比运行”delete from test where...”有效得多；

（2）想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列；

（3）经常运行直接依赖于用于分割表的列的查询。如执行”select count(*) from test where year(birthday) = 2020 ”,MySQL可以很迅速地确定只有分区p1需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

三、LIST分区：根据具体数值分区，每个分区数值不重叠，使用PARTITION BY LIST、VALUES IN关键字。跟Range分区类似，不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。与Range分区相同，添加COLUMNS关键字可支持非整数和多列。

1、语法：

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` date not null
) ENGINE=Innodb   DEFAULT CHARSET=utf8mb4
partition by list(user_age)(
  partition p0 values in (2,4,6,8,10),
  partition p1 values in (1,3,5,7,9)
);

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

1、语法：

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` date not null
) ENGINE=Innodb   DEFAULT CHARSET=utf8mb4
partition by hash(id)
partitions 4;

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` date not null
) ENGINE=Innodb   DEFAULT CHARSET=utf8mb4
partition by hash(year(birthday))
partitions 4;

Hash分区也存在与传统Hash分表一样的问题，可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法－线性Hash分区，只需要在定义分区时添加LINEAR关键字。线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` date not null
) ENGINE=Innodb   DEFAULT CHARSET=utf8mb4
partition by linear hash(year(birthday))
partitions 4;

五、KEY分区：Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。另外，当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错。

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL ,
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  `user_age` int(11) DEFAULT NULL COMMENT '年龄',
  `birthday` date not null
) ENGINE=Innodb   DEFAULT CHARSET=utf8mb4
partition by key(id)
partitions 4;