MYSQL数据库中表的进阶玩法——表分区

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MYSQL数据库中表的进阶玩法——表分区

🔎这里是MYSQL加油站
👍如果对你有帮助,给博主一个免费的点赞以示鼓励
欢迎各位🔎点赞👍评论收藏⭐️

什么是表分区?

  • mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。
  • 表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。

表分区与分表的区别?

  • 分表:指的是通过一定规则,将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。
  • 分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表

表分区有什么好处

  1. 与单个磁盘或文件系统分区相比,可以存储更多的数据。
  2. 对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。
  3. 一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
  4. 涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BYsalesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
  5. 通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。

表分区的限制因素

  1. 一个表最多只能有1024个分区。
  2. MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
  3. 如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。
  4. 分区表中无法使用外键约束。
  5. MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。

MYSQL支持的分区类型有哪些?

  1. RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
  2. LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  3. HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
  4. KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
  • [ ] 说明:在MySQL5.1版本中,RANGE,LIST,HASH分区要求分区键必须是INT类型,或者通过表达式返回INT类型。但KEY分区的时候,可以使用其他类型的列(BLOB,TEXT类型除外)作为分区键。

RANGE创建分区表实例

上面讲了那么多概念,应该都似懂非懂。接下来用实例来彻底搞懂。
1、先建立分区表,代码里有详细注释,正文就不多解释。

-- 建表t1,字段 id name store_id
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30),
store_id int NOT NULL DEFAULT 0
)-- 设置引擎,字符集
ENGINE=INNODB DEFAULT CHARSET=utf8
-- 设置分区:这里以store_id字段的值范围设置了四个分区,分别是p0,p1,p2,p3,p4 分区的范围是0-6,6-11,11-16,16-21
PARTITION by RANGE(store_id)(
PARTITION p0 VALUES less than (6),
PARTITION p1 VALUES less than (11),
PARTITION p2 VALUES less than (16),
PARTITION p3 VALUES less than (21)
);

接下来向表中插入数据:

INSERT INTO t1 VALUES(1,'王',3);
INSERT INTO t1 VALUES(2,'王',7);
INSERT INTO t1 VALUES(3,'王',13);
INSERT INTO t1 VALUES(4,'王',20);
INSERT INTO t1 VALUES(5,'王',1);
INSERT INTO t1 VALUES(6,'王',11);
INSERT INTO t1 VALUES(7,'王',18);

再来看一下结果
在这里插入图片描述
**如果不分区,那么插入后的结果应该是按照id排序存放。
现在分区后,数据存放先根据分区存放,在按照id的顺序。**

这里只举例了RANGE分区的创建方法,还有三种分区方式后续会继续详细介绍。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5月前
|
存储 SQL 关系型数据库
MySQL语句详解:从基础到进阶的全面指南
MySQL语句详解:从基础到进阶的全面指南
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶第五篇(锁)
MySQL数据库进阶第五篇(锁)
|
6月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
6月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
4月前
|
存储 关系型数据库 MySQL
(十九)MySQL之表分区篇:涨知识了!携手共探鲜为人知的表分区!
分库分表相信大家都听说过,但(partitioning)表分区这个概念却鲜为人知,MySQL在5.1版本中开始支持了表分区技术,同时在MySQL5.5中进行了优化,自从MySQL支持的绝大部分引擎都开启了表分区功能。
534 2
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库开发进阶:精通数据库表的创建与管理22
【7月更文挑战第22天】数据库的创建与删除,数据表的创建与管理
52 1
|
6月前
|
JSON 关系型数据库 MySQL
MySQL常用函数解读:从基础到进阶的全方位指南
MySQL常用函数解读:从基础到进阶的全方位指南
|
6月前
|
SQL 关系型数据库 MySQL
Python进阶第二篇(Python与MySQL数据库)
Python进阶第二篇(Python与MySQL数据库)