mysql从5.0版本开始引入分区,对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理字表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求,都会通过句柄对象转化成为存储引擎的接口调用。索引分区对于SQL层来说是一个完全封装底层实现的黑盒子,对于应用是透明的,但是从底层的文件系统很容易发现,每个分区表都有一个使用#分割命名的表文件。
1、分区表的实现
mysql实现分区表的方式----对底层表的封装---意味索引也是按照分区的字表定义的,而没有全局索引。这个oracle不同,在oracle中可以更加灵活的定义索引和表是否进行分区。
mysql在创建表时使用partition by定义每个分区存在的数据。在执行查询的时候,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询就无须扫描所有分区--只需查找所包含的数据的分区就可以了。
2、分区表的作用
分区在一下场景中起到非常大的作用:
- 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据;
- 分区表的数据更容易维护。例如:想批量删除大量数据可以使用清除整个分区的方式。另外还可以对一个独立分区进行优化、检查、修改等操作。
- 分区表的数据可以分布在不同的物理设备上,从而高效的利用多个硬件设备。
- 可以使用分区表来避免特殊的瓶颈,例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
- 如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好。
3、分区的限制
- 一个表最多只能有1024个分区;
- 在mysql 5.1中,分区表达式必须是整数,或者是返回整数的表达式。在mysql5.5中,某些场景中可以直接使用列来进行分区。
- 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来。
- 分区表中无法使用外键约束。
4、分区表的原理
分区表有多个相关的底层表实现,这些地层表也是有句柄对象表示,所以我们也可以直接访问各个分区。存储引擎管理分区的各个地层表和管理普通表一样,分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看,底层表和一个普通表没有任何不同,存储引擎也无需知道这是一个普通表还是一个分区表的一部分。
分区表上的操作可以按照下面的操作逻辑进行:
- select查询:当查询一个分区表的时候,分区层先打开并锁住所有底层表,优化器判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据。
- insert操作:当写入一条记录时,分区层先打开并锁住所有的底层表,然后确定那个分区接受这条记录,再将记录写入对应底层表。
- delete操作:当删除一条记录时,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应表进行删除操作。
- update操作:当更新一条记录时,分区层先打开并锁住所有的底层表,mysql先确定需要更新的记录在那个分区,然后取出数据并更新,在判断更新后的数据该放在那个分区,然后对底层表进行写入操作,并对原数据所在的底层表进行删除操作。
虽然每个操作都会“先打开并锁住所有底层表”,但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁,例如InnoDB,则会在分区层释放对应的表锁。
5、分区表的类型
mysql支持多种分区表,最多的是根据范围进行分区,每个分区存储落在某个范围的记录,分区表达式可以是列,也可以是包含列的表达式。mysql还支持键值、哈希和列表分区。还有一些其他的分区技术包括:
- 根据键值进行分区,来减少InnoDB的互斥竞争;
- 使用数学模函数来进行分区,然后将数据轮询放入不同的分区。
- 假设表有一个自增的主键列ID,希望根据时间将最近的热点数据集中存放。那么必须将时间戳包含在主键当中才行,而这和主键本身的意义相矛盾。这种情况下也可以使用这样的分区来实现相同的目的:HASH(id div 1000000),这将为100万数据建立一个分区。这样一方面实现了当初的分区目的,另一方面比起使用时间范围分区避免了一个问题,就是当超过一定阀值时,如果使用时间范围分区就必须新增分区。
6、如何使用分区表
如果一张表数据量巨大,肯定不能每次查询都进行扫描全表。考虑到索引在空间和维护上的消耗,也不希望使用索引。这个时候两条路可选:让所有的查询只在数据表上做顺序扫描,或者将数据表和索引全部都缓存在内存里。
在数据量超大的时候,b-tree索引就无法起作用了。除非是索引覆盖查询,否则数据库需要根据扫描的结果回表,查询所有符合条件的记录,如果数据量巨大,这将产生大量随机I/O,随之,数据库的响应时间将大到不可接受的程度。意识到这一点,于是就完全抛弃使用b-tree索引,而选择一些更粗粒度的但消耗更少的方式检索数据。
这正是分区要做的事情。理解分区时还可以将其当做索引的最初形态,以代价非常小的方式定位到需要的数据在那一片“区域”。在这片“区域”中,你可以做顺序扫描,可以建索引,还可以将数据缓存到内存。因为分区无须额外的数据结构记录每个分区有那些数据,所以代价非常低,只需要一个简单的表达式就可以表达每个分区存放的是什么数据。
7、分区带来的问题
- null值会使分区过滤无效:一个容易让人误解的地方就是分区表达式的值可以是null:第一个分区是一个特殊分区。假如按照partition by range year(order_date)分区,那么所有order_date为null或者一个非法值的时候,记录都会被存放在第一个分区。当查询的时候,例如:where order_date between '2012-01-01' and '2012-01-31'。实际上,mysql会检查两个分区,而不是之前猜想的一个。即检查2012分区和第一个分区。
- 分区列和索引列不匹配:如果定义的索引列和分区列不匹配,会导致查询无法进行分区过滤。假设列a上定义了索引,而在列b上进行分区。因为每个分区都有其独立的索引,所以扫描列b上的索引就需要扫描每个分区内对应的索引。
- 选择分区的成本可能很高:分区的类型很多,不同类型的分区实现也不相同,所以他们的性能也各不相同。尤其是范围分区,在查询过程中成本会很高,且随着分区数量的增长,成本会越来越高。
- 打开并锁住所有底层表的成本可能很高:当查询访问分区表时,mysql需要打开并锁住所有的底层表,这是分区表的另一个开销。这个操作在分区过滤之前发生,索引无法通过分区过滤降低次开销,并且该开销也和分区类型无关,会影响所有的查询。
- 维护分区的成本可能会很高:某些分区维护操作的速度会非常快(如add、delete),而有些操作,会开销很大(alter语句需要复制数据)
相关推荐
MySQL分区是一种数据库优化技术,它将大型表分成更小、更易管理的部分,每个部分称为一个分区。这种技术有助于提高查询性能,特别是对于那些需要处理大量数据或执行复杂查询的应用程序。MySQL支持多种分区类型,包括...
然而,值得注意的是,尽管MySQL分区提供了许多优势,但在实际应用中,需要根据具体业务需求和查询模式来决定是否使用分区,以及如何设计分区策略。在创建分区表后,可以通过调整分区方案来优化常见查询的性能。此外...
#### 一、MySQL分区概述与应用场景 在当今互联网行业中,MySQL数据库作为最常用的关系型数据库之一,在数据管理方面扮演着极其重要的角色。随着数据量的不断增长,对数据库性能的要求也日益提高。为了提高查询效率...
【MySQL 表分区详解】 MySQL 表分区是一种高级的数据组织技术,它允许将大型表分成较小、更易管理和处理的部分。这种技术对于处理海量数据,尤其是超过亿级别的数据集非常有效,可以显著提升查询性能和数据管理效率...
### MySQL分区表:万字详解与实践指南 #### 一、引言 在现代数据库管理中,随着数据量的不断增长,如何高效管理和查询数据成为了一个重要的课题。MySQL作为广泛使用的开源关系型数据库管理系统之一,提供了多种强大...
Oracle 和 MySQL 数据库在分区方面的区别主要体现在分区策略和适用场景上。分区是一种数据库优化技术,它将大表分成更小、更易管理的部分,从而提高查询性能和数据管理效率。下面详细介绍这两种数据库的分区类型及其...
MySQL交换分区的实例详解 前言 在介绍交换分区之前,我们先了解一下 mysql 分区。 数据库的分区有两种:水平分区和垂直分区。而MySQL暂时不支持垂直分区,因此接下来说的都是水平分区。水平分区即:以行为单位对表...
【MySQL分区表详解】 分区表是数据库管理中一种高级的数据组织策略,主要目的是为了提高大表的查询效率和数据管理的便利性。在MySQL中,分区表是通过`PARTITION BY`子句来实现的,它允许我们将一个大的表根据特定的...
MySQL 5.1是MySQL数据库管理系统的一个重要版本,它在5.0的基础上进行了多项改进和增强,为开发者提供了更高效...这个“mysql5.1详解简体中文版”的教程文件,将帮助读者深入理解这些概念,提升数据库管理和开发技能。
以下是关于MySQL分区功能的详细解释和实例分析: 一、什么是数据库分区 数据库分区是将一张大表逻辑上视为一个整体,但在物理存储上将其分散到不同的磁盘或文件组中,以便更有效地管理和访问数据。在MySQL中,数据...
MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS),它基于结构化查询语言(SQL)标准,具有高效、稳定和易用的特点。在Linux操作系统环境下,MySQL被广泛应用于各种规模的Web应用程序,从个人项目到大型企业...
9. **分区和复制**:对于大型数据库,分区可以提高查询性能,而主从复制则提供了高可用性和灾难恢复的解决方案。 10. **JSON支持**:MySQL 5.7及以上版本开始支持JSON数据类型,手册将介绍如何存储、查询和操作JSON...
### MySQL安装过程详解 #### 一、安装准备与启动 MySQL 是一款非常流行的开源关系型数据库管理系统,在企业和个人开发中有着广泛的应用。本篇详细介绍了MySQL的安装过程,特别是针对初次接触MySQL的新手用户,提供...
MySQL 5.6是一个非常流行的开源关系型数据库管理...通过以上内容,我们可以看到MySQL 5.6官方教程详解覆盖了从基础到高级的广泛知识。无论是对于初学者还是资深数据库管理员,它都是获取MySQL 5.6深入知识的宝贵资源。
从给定的文件标题“mysql5.5详解”和描述“MySQL 5.5新特性详解及参数优化,好资料!!!!”,我们可以深入探讨MySQL 5.5版本中引入的重要特性和性能优化策略。 ### MySQL 5.5新特性 #### 1. **InnoDB作为默认...