搜索
首页数据库mysql教程MySQL中分区表的详细介绍

MySQL中分区表的详细介绍

Jan 19, 2019 am 10:35 AM
mysql

本篇文章给大家带来的内容是关于MySQL中分区表的详细介绍,有一定的参考价值,有需要的朋友可以参考一下,希望对你有所帮助。

对于用户而言,分区表是一个独立的逻辑表,但是在底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装,对分区表的请求都会通过句柄对象转化成对存储引擎的接口调用

意义

MySQL在创建表的时候可以通过使用 PARTITION BY 子句定义每个分区存放的数据。在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这样查询就可以无需扫描所有分区——只需要查找包含需要数据的分区即可。

分区的一个主要目的是 将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起,另外,当我们想要一次批量删除整个分区的数据也会变得很方便。

在以下的场景中,分区可以起到很大的作用:

  • 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据其他均是历史数据

  • 分区表的数据更容易维护

  • 分区表的数据可以分布在不同的物理设备上

  • 可以使用分区表来避免某些特殊的瓶颈

  • 如果需要,可以备份和回复独立的分区

分区表本身也有一些限制,下面几点尤为重要:

  • 一张表最多只能有1024个分区

  • 在MySQL5.1 中,分区表达式必须是整数,或者是返回整数的表达式。在MySQL5.5 中,某些场景可以直接使用列来进行分区

  • 分区表中无法使用外键约束

  • 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来

分区表的原理

存储引擎管理分区的各个底层表和管理普通表并没有什么区别(所有的底层表都必须使用相同的存储引擎)
,分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度看,底层表和一个普通表并没有什么区别,存储引擎也无需知道这是一个普通表还是一个分区表的一部分。

分区表上的操作按照下面的操作逻辑进行:

SELECT 查询

当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据

INSERT 操作

当写入一条记录的的时候,分区层先打开并锁住所有的底层表,然后确定哪个分区接收这条记录,再将记录写入对应底层表

DELETE 操作

当删除一条记录的的时候,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作

UPDATE 操作

当更新一条记录时,分区层先打开并锁住所有的底层表,MySQL先确定需要更新的记录再哪个分区,然后取出数据并更新,再判断更新后的数据应该放在哪个分区,最后对底层表进行写入操作,并对原数据所在的底层表进行删除操作。

这些操作都是支持过滤的。

虽然每个操作都会“先打开并锁住所有的底层表”, 但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁,则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。

分区表的类型

MySQL支持多种分区表,我们看到最多的就是根据范围进行分区,每个分区存储落在某个范围内的记录。分区表达式可以是列,也可以是包含列的表达式。

例如,如下表就将每一年的销售额都存放在不同的分区中:

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION 分区子句中可以使用各种函数。但是有一个要求, 表达式返回的值必须是一个确定的整数,且不能是一个常数。

MySQL还支持键值、哈希和列表分区等。

如何使用分区表

如果我们希望从一个非常大的表中查询出一段时间的记录,我们应该如何查询这个表,如何才能更加高效?

因为数据量非常大,肯定不能在每次查询的时候都扫描全表,考虑到索引在空间和维护上的消耗,我们也不希望使用索引。即使真的使用索引,也会发现数据并不是按照想要的方式进行聚集,会产生大量的碎片,最终导致一个查询产生成千上万的随机I/O。而事实上,当数据量超级大时,B-Tree索引就已经无法祈祷作用了。

因此我们可以选择一些更粗粒度但消耗更少的方式检索数据,例如在大量的数据上只索引对应的一小块元数据。

这正是分区要做的事情,理解分区可以将其当作索引的最初形态。 因为分区无需额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置,也就无须额外的数据结构——所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

为了保证大数据量的可扩展性,一般有以下两个策略:

  1. 全量扫描数据,不需要任何索引: 只要能够使用 WHERE 条件,将需要的数据限制在少数分区中,则效率是很高的。使用这种策略假设不用将数据完全放入内存中,同时还假设需要的数据全部都在磁盘上。因为内存相对较小,数据很快会被挤出内存,所以缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。

  2. 索引数据,并分离热点: 如果数据有明显的“热点”,而且除了这部分数据,其他数据很少被访问到,那么可以将这部分热点数据单独放在一个分区中,让这个分区的数据可以有机会都缓存在内存中。这样的查询可以只访问一个很小的分区表,能够使用索引,也能够有效的使用缓存。

什么情况下会出问题

上面介绍的两个分区策略都基于两个非常重要的假设:查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。

事实证明,这两个假设在某些场景下会有问题:

  • 分区列和索引列不匹配: 如果定义的索引列和分区列不匹配,会导致可查询无法进行分区过滤。

  • 选择分区的成本可能很高: 不同类型的分区的实现方式也不同,所以它们的性能也各不相同。尤其是范围分区,对于查询符合条件的行在哪些分区的成本可能会非常高,因为服务器需要扫描所有的分区定义的列表来找到正确的答案。

  • 打开并锁住所有底层表的成本可能很高: 当查询访问分区表的时候,MySQL需要打开并锁住所有的底层表,这是分区表的另一个开销。

  • 维护分区的成本可能很高: 某些分区维护操作的速度会非常快,例如新增或者删除分区。而有些操作,比如重组分区或者类似ALTER语句的操作成本可能会很高,因为这类操作需要复制数据。

以上是MySQL中分区表的详细介绍的详细内容。更多信息请关注PHP中文网其他相关文章!

声明
本文转载于:博客园。如有侵权,请联系admin@php.cn删除
MySQL的许可与其他数据库系统相比如何?MySQL的许可与其他数据库系统相比如何?Apr 25, 2025 am 12:26 AM

MySQL使用的是GPL许可证。1)GPL许可证允许自由使用、修改和分发MySQL,但修改后的分发需遵循GPL。2)商业许可证可避免公开修改,适合需要保密的商业应用。

您什么时候选择InnoDB而不是Myisam,反之亦然?您什么时候选择InnoDB而不是Myisam,反之亦然?Apr 25, 2025 am 12:22 AM

选择InnoDB而不是MyISAM的情况包括:1)需要事务支持,2)高并发环境,3)需要高数据一致性;反之,选择MyISAM的情况包括:1)主要是读操作,2)不需要事务支持。InnoDB适合需要高数据一致性和事务处理的应用,如电商平台,而MyISAM适合读密集型且无需事务的应用,如博客系统。

在MySQL中解释外键的目的。在MySQL中解释外键的目的。Apr 25, 2025 am 12:17 AM

在MySQL中,外键的作用是建立表与表之间的关系,确保数据的一致性和完整性。外键通过引用完整性检查和级联操作维护数据的有效性,使用时需注意性能优化和避免常见错误。

MySQL中有哪些不同类型的索引?MySQL中有哪些不同类型的索引?Apr 25, 2025 am 12:12 AM

MySQL中有四种主要的索引类型:B-Tree索引、哈希索引、全文索引和空间索引。1.B-Tree索引适用于范围查询、排序和分组,适合在employees表的name列上创建。2.哈希索引适用于等值查询,适合在MEMORY存储引擎的hash_table表的id列上创建。3.全文索引用于文本搜索,适合在articles表的content列上创建。4.空间索引用于地理空间查询,适合在locations表的geom列上创建。

您如何在MySQL中创建索引?您如何在MySQL中创建索引?Apr 25, 2025 am 12:06 AM

toCreateAnIndexinMysql,usethecReateIndexStatement.1)forasingLecolumn,使用“ createIndexIdx_lastNameEnemployees(lastName); 2)foracompositeIndex,使用“ createIndexIndexIndexIndexIndexDx_nameOmplayees(lastName,firstName,firstName);” 3)forauniqe instex,creationexexexexex,

MySQL与Sqlite有何不同?MySQL与Sqlite有何不同?Apr 24, 2025 am 12:12 AM

MySQL和SQLite的主要区别在于设计理念和使用场景:1.MySQL适用于大型应用和企业级解决方案,支持高性能和高并发;2.SQLite适合移动应用和桌面软件,轻量级且易于嵌入。

MySQL中的索引是什么?它们如何提高性能?MySQL中的索引是什么?它们如何提高性能?Apr 24, 2025 am 12:09 AM

MySQL中的索引是数据库表中一列或多列的有序结构,用于加速数据检索。1)索引通过减少扫描数据量提升查询速度。2)B-Tree索引利用平衡树结构,适合范围查询和排序。3)创建索引使用CREATEINDEX语句,如CREATEINDEXidx_customer_idONorders(customer_id)。4)复合索引可优化多列查询,如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。5)使用EXPLAIN分析查询计划,避

说明如何使用MySQL中的交易来确保数据一致性。说明如何使用MySQL中的交易来确保数据一致性。Apr 24, 2025 am 12:09 AM

在MySQL中使用事务可以确保数据一致性。1)通过STARTTRANSACTION开始事务,执行SQL操作后用COMMIT提交或ROLLBACK回滚。2)使用SAVEPOINT可以设置保存点,允许部分回滚。3)性能优化建议包括缩短事务时间、避免大规模查询和合理使用隔离级别。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

WebStorm Mac版

WebStorm Mac版

好用的JavaScript开发工具

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中

SublimeText3 英文版

SublimeText3 英文版

推荐:为Win版本,支持代码提示!

EditPlus 中文破解版

EditPlus 中文破解版

体积小,语法高亮,不支持代码提示功能

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器