搜索
首页数据库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架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

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脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前By尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前By尊渡假赌尊渡假赌尊渡假赌

热工具

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SecLists

SecLists

SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

安全考试浏览器

安全考试浏览器

Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

EditPlus 中文破解版

EditPlus 中文破解版

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

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),