网站导航
> 文章列表 > MySQL运维12-分区表

MySQL运维12-分区表

网友: 文章列表 2024-03-21 22:30:42

MySQL运维12-分区表

文章目录

  • 1、分区表概述
  • 2、分区类型
  • 3、分区表实操
  • 4、分区表的优缺点和使用建议
    • 4.1、优点
    • 4.2、缺点
    • 4.3、使用建议
  • 5、总结

1、分区表概述

  • 分区表技术允许按照设置的规则，将一个表存储到多个文件中，甚至多个文件系统的多个文件中。实际上，分区表是将一个逻辑表的不同部分在不同的位置被存储为单独的表。
  • 用户所选择的、实现数据分割的规则被称为分区函数。

2、分区类型

  1. RANGE分区：基于一个连续区间的列值，把多行分配给分区，例如某个时间段的值属于某个分区，某个数值范围的值应该属于某个分区。
  2. LIST分区：LIST分区类似RANGE分区，区别在于LIST分区中每个分区的定义和选择是基于值列表，也就是“离散值”，而RANGE分区是基于连续值的。
  3. HASH分区
    • 基于用户定义的表达式的返回值选择分区。它主要用来确保数据在预先确定了数目的分区中是平均分布的。
    • 在RANGE分区和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL将自动完成这些工作，你所要做的只是为将要被散列的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
  4. KEY分区：KEY分区类似于HASH分区，只是HASH分区用的是用户自定义表达式，而KEY分区的散列函数是由MySQL服务器提供的。
  5. 子分区：子分区是分区表中每个分区的再次分割。

3、分区表实操

  • 创建一个RANGE分区表：
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)PARTITION BY RANGE( YEAR(purchased) ) (PARTITION p0 VALUES LESS THAN (1990),PARTITION p1 VALUES LESS THAN (1995),PARTITION p2 VALUES LESS THAN (2000),PARTITION p3 VALUES LESS THAN (2005));
  • 删除分区（需要DROP权限）
ALTER TABLE trb3 DROP PARTITION p2;
  • 增加分区，对于RANGE分区，只能从分区列表的最高端开始增加。
CREATE TABLE members (id INT,fname VARCHAR(25),lname VARCHAR(25),dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (PARTITION p0 VALUES LESS THAN (1970),PARTITION p1 VALUES LESS THAN (1980),PARTITION p2 VALUES LESS THAN (1990)
);
#增加一个分区。
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
#增加多个分区
ALTER TABLE members ADD PARTITION (PARTITION p5 VALUES LESS THAN (2010),PARTITION p6 VALUES LESS THAN MAXVALUE
);
# 增加的分区必须从最高端开始增加，以下增加会报错：
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));
  • 调整分区：如果想要调整分区，比如在分区列表中加入一个分区，或者忘记增加分区了，所有的数据都落入了最后一个分区，这时想重新定义最后的分区，那么你可以使用重整分区的功能。
ALTER TABLE membersREORGANIZE PARTITION p0 INTO (PARTITION n0 VALUES LESS THAN (1960),PARTITION n1 VALUES LESS THAN (1970)
);
  • 合并分区：对于RANGE分区，合并的分区必须是相邻的分区。
ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO (PARTITION p0 VALUES LESS THAN (1970)
);
  • 重建分区（rebui lding parti tion）：相当于删除所有的数据，再INSERT所有的数据，整理碎片可用
ALTER TABLE members REBUILD PARTITION p0, p1;
  • 优化分区（optimizing parti tion）：如果某个分区中删除了大量数据，或者频繁修改了表（有可变字段），那么可以考虑优化该分区
ALTER TABLE members  OPTIMIZE PARTITION p0, p1;
  • 分析分区（analyzing parti tion）：分析分区的key分布信息。
ALTER TABLE members ANALYZE PARTITION p3;
  • 检查分区（checking parti tion）：检查表，如果坏了，则用REPAIR命令修复
ALTER TABLE members CHECK PARTITION p1;
  • 修复分区（repai ring parti tion）
ALTER TABLE members  REPAIR PARTITION p0,p1;
  • 如果需要对所有分区进行操作，那么可加入Al l关键字
ALTER TABLE  members ANALYZE PARTITION ALL;

4、分区表的优缺点和使用建议

4.1、优点

  • 可存储更多数据：与单个磁盘或文件系统分区相比，可以存储更多的数据。表分区物理上被存储为单独的表，所以可以把分区存储到不同的磁盘或文件系统中。在现实生产环境中，这样使用还是比较少见的。选择分区表更常见的是基于业务的需要，是否能够更高效地查询数据和维护数据。
  • 易删除数据：对于那些已经失去了保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除掉那些数据。
  • 分区内查询得到极大优化：一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用再查找剩余的其他分区了。

4.2、缺点

  • 跨分区查询效率有问题：MySQL的分区表不像Oracle那么灵活和成熟可靠，也不像Oracle那样可以有全局的索引，MySQL的索引对于每个表来说都是单独的。这样如果有跨越多个分区的查找，那么效率可能就会有问题。
  • 不成熟：分区表在MySQL 5.6后才趋向成熟。

4.3、使用建议

  • 不推荐在生产环境中使用分区表：虽然系统设计人员在碰到一些有“分区”特征的数据时，可能就会倾向于分区，比如一些按时间记录的流水账。但一是分区表不能跨越MySQL的实例，也就是说不能超过单机，扩展性仍然有限；二是分区表还只是一项不是很成熟的技术，可能会给整个系统带来隐患。
  • 只有大表才可能需要分区，几百万笔记录的表并不算大，对于一些高配置的数据库主机，几千万甚至上亿条数据的表也不算大。
  • 分区数不能过多，很难想象大于500的分区数。
  • 查询的时候，不要跨越多个分区，建议最多跨越1~2个分区。
  • 索引的列应该是分区的列，或者有其他条件限制的分区，否则访问所有分区上面的索引进行查找，开销会比较大。

5、总结

  1. MySQL的分区表是将表的数据按条件分开存储到不同的文件里，本质上实际是将一个大表拆分成了多个子表。
  2. MySQL表的分区可以有RANGE连续值分区、LIST离散值分区、HASH自定义哈希分区和KEY自动哈希分区四种分区类型，且可以在分区上继续子分区。
  3. 建议不要在生产环境使用MySQL分区表，原因是：MySQL的分区表不成熟，特别是跨分区查询性能差。另外MySQL的分区表还是基于单个MySQL实例的，所以性能差。
网络标签：

相关问题