MySQL表分区

最新推荐文章于 2025-03-04 22:22:51 发布
原创 最新推荐文章于 2025-03-04 22:22:51 发布 · 174 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#分区 #表分区 #MySQL表分区 #分区表

分区表作为大型数据集管理的有效工具，通过将数据按粒度分布于不同物理表，简化数据维护，加速查询效率，尤其适用于历史数据管理和热点数据分离。然而，分区表也存在限制，如分区数量上限、维护成本及数据分布不均等问题。

分区表

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。
在下面的场景中,分区可以起到非常大的作用:

  • 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
  • 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清楚整个分区的方式，还可以对一个独立分区进行优化、检查、修复等操作。
  • 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
  • 使用分区表来避免某些特殊的瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争等。
  • 如果需要,还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。
    分区表本身也有一些限制，下面是其中比较重要的几点:
  • 一个表最多只能由1024个分区。
  • 在MySQL5.1中，分区表达式必须是整数,或者是返回整数的表达式。
  • 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
  • 分区表无法使用外键约束。

分区表的原理

  • SELECT查询:当查询一个分区表的时候，分区表先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
  • INSERT操作:当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应的底层表。
  • DELETE操作:当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。
  • UPDATE操作:当更新一条记录时，分区层先打开并锁住所有的底层表，MYSQL确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行

如何使用分区表

在数据量超大的时候，B-Tree索引就无法起作用了，因为产生大量的随机I/O。索引维护的代价也非常高。
分区可以看做是索引的最初形态，以代价非常小的方式定位到需要数据在哪一片区域。在这片区域中，你可以顺序扫描，可以建索引，还可以将数据存到内存中。
一般有下面两个策略:

  • 全量扫描数据，不要任何索引
  • 索引数据，并分离热点
    注意:上述讨论都是在建立在某个分区内。

分区可能出现的问题

  • NULL值会使分区过滤无效
    第一个分区是一个特殊的分区,假设按照PARTITION BY RANGE YEAR(order _date)分区，那么所有order_date为null或者一个非法值得时候，记录都会被存放到第一个分区中。如果第一个分区非常大，特别是当使用"全量扫描数据，不要任何索引"的策略时，代价会非常大。为了避免这种情况，可以创建一个“无用”分区的第一个分区。这样如果插入数据都是有效的，那么第一个分区就是空白的。这样即使要检查第一个分区，那么代价也非常小。
    在MySQL5.5中就不需要这个技巧了，因为可以直接使用列本身:PARTITION BY RANGE COLUMNS(order_date)。所以这个案例最好的解决方法是能够使用MySQL 5.5的这个语法。

  • 分区列和索引列不匹配
    假设在列a上定义了索引，在列b上进行分区。因为每个分区有其独立的索引，索引扫描列a上的索引时就需要扫描每个分区内对应的索引。

  • 选择分区的成本可能非常高，每次写入数据时都需要扫描所有的分区定义大的列表来找到正确答案，所以随着分区增多，成本增高。根据经验，对大多数系统来说，100个左右的分区是没有问题的。

  • 打开并锁住所有底层表的成本可能非常高。

  • 维护分区的成本可能很高。

使用分区来优化查询

利用分区函数的列找到对应分区，可以扫描更少的数据。where条件中带入的是分区列而不是基于分区列的表达式。

MySQL表分区管理：创建分区表、自动分区清理与分区迁移实战指南
私聊前往站内信：https://i.youkuaiyun.com/#/msg/chat/weixin_44976692
10-31 1万+
在MySQL中，分区是一种将表中的数据按规则分割成若干逻辑部分存储的机制。分区表的数据会存储在不同的分区中，但在应用程序看来，这些数据仍然属于同一个表。分区表的操作和普通表一致，但在底层优化了数据的存储结构，适合处理大数据表。创建分区表：在表中按月分区，为未来的数据管理打下基础。自动分区创建与清理：使用存储过程和事件调度器，自动添加新分区和清理旧分区，减少维护工作量。非分区表的分区迁移：通过创建新分区表并迁移数据的方式，将已有数据表转化为分区表。
B+Tree介绍及在数据库中的使用
running_pork的博客
11-20 687
文章目录一 B+Tree 和 B-Tree二 InnoDB中一个3层B+树最多大概可以存放多少行数数据？问题1: 因此，我们首先解决一个简单一点的问题：那么如果是2层的B+树，最多可以存储多少行数据？问题2 最后再回到原来的问题：一个三层的B+树可以存放多少行数据呢？问题3：为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？ 转载： https://www.cnblogs.com/shengguorui/p/10695646.html 一 B+Tree 和 B-Tree 在B+Tree中，所有
mysql表分区
maodou95838的专栏
03-04 1363
本文简述了mysql表分区的作用和特点，又演示了mysql表分区的创建过程，详细演示了指定不同分区目录时的处理办法。由于表分区对crud操作是透明的，对于普通开发同学其实不用过多关注，但是本着学习的态度，在分库分表等高大上的技术面前，mysql分区技术依然是一个不可或缺的提升数据库读写能力的关键技术。
MySQL表分区技术介绍
先知三日
02-27 2386
分区功能并不是在存储引擎层完成的，因此不是只有 InnoDB 存储引擎支持分区，常见的存储引擎 MyISAM、NDB 等都支持。但也并不是所有的存储引擎都支持，如CSV, FEDORATED, MERGE等就不支持。在使用分区功能前,应该对选择的存储引擎对分区的支持有所了解。MySQL数据库在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。
Mysql 表分区创建方法
AstarCloud
05-31 2987
查看表分区信息 SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM information_schema.PARTITIONS WHERE table_schema = SCHEMA () AND table_name = '表名'; 创建range分区 —— 分区字段为整型 DROP TABLE IF EXISTS `range.
MySql表分区类型
分享DotNet技术和日常开发笔记，DotNet，Python为主。
12-08 776
在MySQL中，表分区是一种将大型表拆分成更小、更易管理的部分的技术。表分区可以帮助提高查询性能、简化数据管理和维护等方面。在MySQL中，有几种不同类型的分区可以用于对表进行分区。
MySQL 表分区原理详解
jkzyx123的博客
12-01 1740
MySQL表分区是将大型表数据划分为多个小块的一种技术，以提高查询效率、简化维护并优化性能。分区对用户透明，实际上数据被物理存储在不同的文件或目录中。支持的分区类型包括范围分区（RANGE）、列表分区（LIST）、哈希分区（HASH）和键分区（KEY）。分区能够带来性能提升、维护简便、快速备份恢复和高并发处理等优点，但也有如统一存储引擎、外键限制和主键要求等限制。分区是在创建或修改表时定义的，需要仔细规划以适应特定场景。
MySQL分区表：万字详解与实践指南
热门推荐
码到三十五
05-17 10万+
MySQL分区是一种数据库优化的技术，它允许将一个大的表或一个索引分割成多个较小的、更易于管理的片段，称为分区。这种技术可以显著提高查询性能、维护的方便性以及数据管理效率。本文将详细介绍MySQL分区的基本概念、工作原理、使用场景以及操作。
MySQL 表分区：优化数据库性能的利器
我爱娃哈哈的专栏
09-29 1471
在实际应用中，可以根据具体的业务需求和数据特点，选择合适的分区类型和策略，充分发挥表分区的优势。在查询时，也可以并行地在各个分区中进行查找，提高查询性能。例如，如果不同的分区存储在不同的物理磁盘上，那么写入操作可以并行进行，从而提高整体的写入性能。例如，我们可以按照时间、地域、业务类型等规则对一个大表进行分区，将不同的数据存储在不同的分区中。这样，在查询数据时，可以只查询特定的分区，而不是整个表，从而提高查询性能。这样，当我们查询某个月的订单时，只需要扫描对应的分区，而不是整个订单表，大大提高了查询性能。
MySql表分区详解
王树伦的博客
01-16 4万+
一、MySQL分区表介绍 分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。 分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。在
创建mysql表分区的方法
12-15
3. **添加数据**：向分区表中插入数据，MySQL会自动根据分区规则分配。 4. **管理分区**：可以添加、删除或重定义分区，以适应数据变化。 **示例：RANGE分区创建** ```sql CREATE TABLE p_range ( id INT(10) NOT...
Undo log与 MVCC机制
Xjzzon的博客
03-11 1711
Undo日志 INSERT语句的undo log日志 对应的类型是TRX_UNDO_INSERT_REC 这条日志的开始位置 主键的各列长度和值 表id undo log日志编号 undo log日志类型 这条日志的结束位置 假设现在需要回滚一条insert语句，只需要把undo log拿出来，就知道在哪个表里插入的数据，主键是什么，直接定位到哪个表和主键对应的缓存页，从里面删除掉之前insert语句插入进去的数据就可以了。 MySQL运行时多个事务同时执行 read uncommitted，读未提交
order by使用索引
Xjzzon的博客
10-15 1059
Mysql中SQL语句中包含"IS NULL"对查询的影响
Xjzzon的博客
04-11 1005
IS NULL 的影响 不影响使用索引，MySQL可以对col_name IS NULL执行与col_name=constant_value相同的优化。例如，MySQL可以使用索引和范围来使用IS NULL搜索NULL。 例子 SELECT * FROM tbl_name WHERE key_col IS NULL; SELECT * FROM tbl_name WHERE key_col ...
联合索引查询原理以及全值匹配规则
Xjzzon的博客
03-12 892
联合索引查询原理以及全值匹配规则 等值匹配规则，和where字段的顺序无关 最左侧列匹配，意味着不能跳过字段 最左前缀匹配，意味着like只能匹配左确定部分 范围查找规则，对联合索引最左侧的列进行范围查询才能使用索引。 等值匹配+范围匹配 当我们在SQL里进行分组时，如何才能使用索引 对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按顺序排列开来，这样的话，可以运用上索引来直接提取一组一组的数据，然后针对每一组的数据执行聚合函数就可以。本质上就是利用索引已经排好序的特点，避免针对
MySQL生产环境部署架构
Xjzzon的博客
03-12 696
MySQL生产环境部署架构 常用的分库分表架构 按业务id分库分表，建立索引映射表同时进行分库分表，数据同步到ES做复杂搜索。 分库分表下如何分页 假设用户现在要查询自己的订单，同时订单要求要支持分页，该怎么做？ 方案一:因为同一个用户的订单可能是分布在不同的库不同表的，需要建立一个索引表， (user_id, order_id),这个表对user_id进行分库分表后，同一个用户的数据就会进入到同一张表里， 既然在同一张表里就可以利用mysql自带的分页机制。 方案二:加载到内存之中，在内存中分页。 对于主
Mysql的日期类型
Xjzzon的博客
12-24 620
Mysql的日期类型 mysql有三种日期类型:Date/DateTime/Timestamp Date格式 数据存储格式 YYYY-MM-DD，它支持的范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’ 如果插入带了time字段，它会丢弃该部分的内容，并提示一个warning Datetime格式 数据存储格式YYYY-MM-DD HH:MM:SS...
mysql 表分区
最新发布
05-26
### MySQL 表分区的使用指南与示例 #### 基本概念 MySQL 分区是一种数据库优化技术，允许将一个大表或索引分割成多个较小的部分，这些部分被称为分区。通过这种方式，可以显著提升查询性能、简化维护过程以及增强数据管理效率[^1]。 #### 工作原理 在 MySQL 中，可以通过定义不同的分区策略来实现表分区。常见的分区类型包括 RANGE、LIST、HASH 和 KEY 等。每种类型的适用场景不同，合理选择分区键和分区策略对于最大化性能至关重要[^2]。 #### 创建分区表的语法 以下是几种常见分区方式的具体语法： ##### 1. **RANGE 分区** 适用于按某个字段范围划分数据的情况。 ```sql CREATE TABLE sales ( id INT NOT NULL, sale_date DATE NOT NULL, amount DECIMAL(10, 2) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021), PARTITION p2 VALUES LESS THAN (2022), PARTITION p3 VALUES LESS THAN MAXVALUE ); ``` ##### 2. **LIST 分区** 用于基于离散值集合进行分区。 ```sql CREATE TABLE employees ( emp_id INT NOT NULL, department VARCHAR(20) NOT NULL ) PARTITION BY LIST (department) ( PARTITION p_northwest VALUES IN ('North', 'West'), PARTITION p_southeast VALUES IN ('South', 'East') ); ``` ##### 3. **HASH 分区** 适合均匀分布数据的场景。 ```sql CREATE TABLE logs ( log_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) PARTITION BY HASH(YEAR(created_at)) PARTITIONS 4; ``` ##### 4. **KEY 分区** 类似于 HASH 分区，但由存储引擎自动计算哈希值。 ```sql CREATE TABLE users ( user_id INT NOT NULL, username VARCHAR(50) ) PARTITION BY KEY(user_id) PARTITIONS 8; ``` #### 查看分区信息 要查看已创建的分区及其元数据，可利用 `information_schema.PARTITIONS` 表中的信息。 ```sql SELECT PARTITION_NAME AS 分区名称, PARTITION_METHOD AS 分区方法, TABLE_ROWS AS 数据行数, PARTITION_DESCRIPTION AS 描述 FROM information_schema.PARTITIONS WHERE table_name = 'sales'; ``` 如果涉及子分区，则需替换为 `SUBPARTITION_*` 字段以获取详细信息[^4]。 #### 维护分区 随着业务发展，可能需要动态调整分区结构。例如新增分区： ```sql ALTER TABLE sales ADD PARTITION ( PARTITION p_new VALUES LESS THAN (2023) ); ``` 或者删除不再使用的旧分区： ```sql ALTER TABLE sales DROP PARTITION p_old; ``` #### 注意事项 - 提前规划好分区方案非常重要，尤其是在初始化阶段就应考虑未来扩展性[^3]。 - 不同版本间可能存在兼容性差异，请参照官方文档确认支持的功能列表[^3]。 --- ###
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值