搜索
首页数据库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删除
解释InnoDB缓冲池及其对性能的重要性。解释InnoDB缓冲池及其对性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通过缓存数据和索引页来减少磁盘I/O,提升数据库性能。其工作原理包括:1.数据读取:从BufferPool中读取数据;2.数据写入:修改数据后写入BufferPool并定期刷新到磁盘;3.缓存管理:使用LRU算法管理缓存页;4.预读机制:提前加载相邻数据页。通过调整BufferPool大小和使用多个实例,可以优化数据库性能。

MySQL与其他编程语言:一种比较MySQL与其他编程语言:一种比较Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

学习MySQL:新用户的分步指南学习MySQL:新用户的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得学习,因为它是强大的开源数据库管理系统,适用于数据存储、管理和分析。1)MySQL是关系型数据库,使用SQL操作数据,适合结构化数据管理。2)SQL语言是与MySQL交互的关键,支持CRUD操作。3)MySQL的工作原理包括客户端/服务器架构、存储引擎和查询优化器。4)基本用法包括创建数据库和表,高级用法涉及使用JOIN连接表。5)常见错误包括语法错误和权限问题,调试技巧包括检查语法和使用EXPLAIN命令。6)性能优化涉及使用索引、优化SQL语句和定期维护数据库。

MySQL:初学者的基本技能MySQL:初学者的基本技能Apr 18, 2025 am 12:24 AM

MySQL适合初学者学习数据库技能。1.安装MySQL服务器和客户端工具。2.理解基本SQL查询,如SELECT。3.掌握数据操作:创建表、插入、更新、删除数据。4.学习高级技巧:子查询和窗口函数。5.调试和优化:检查语法、使用索引、避免SELECT*,并使用LIMIT。

MySQL:结构化数据和关系数据库MySQL:结构化数据和关系数据库Apr 18, 2025 am 12:22 AM

MySQL通过表结构和SQL查询高效管理结构化数据,并通过外键实现表间关系。1.创建表时定义数据格式和类型。2.使用外键建立表间关系。3.通过索引和查询优化提高性能。4.定期备份和监控数据库确保数据安全和性能优化。

MySQL:解释的关键功能和功能MySQL:解释的关键功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一个开源的关系型数据库管理系统,广泛应用于Web开发。它的关键特性包括:1.支持多种存储引擎,如InnoDB和MyISAM,适用于不同场景;2.提供主从复制功能,利于负载均衡和数据备份;3.通过查询优化和索引使用提高查询效率。

SQL的目的:与MySQL数据库进行交互SQL的目的:与MySQL数据库进行交互Apr 18, 2025 am 12:12 AM

SQL用于与MySQL数据库交互,实现数据的增、删、改、查及数据库设计。1)SQL通过SELECT、INSERT、UPDATE、DELETE语句进行数据操作;2)使用CREATE、ALTER、DROP语句进行数据库设计和管理;3)复杂查询和数据分析通过SQL实现,提升业务决策效率。

初学者的MySQL:开始数据库管理初学者的MySQL:开始数据库管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括创建数据库、表格,及使用SQL进行数据的CRUD操作。1.创建数据库:CREATEDATABASEmy_first_db;2.创建表格:CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据:INSERTINTObooks(title,author,published_year)VA

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无尽的。

热工具

Dreamweaver Mac版

Dreamweaver Mac版

视觉化网页开发工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

mPDF

mPDF

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

安全考试浏览器

安全考试浏览器

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

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

将Eclipse与SAP NetWeaver应用服务器集成。