文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

【SQL应知应会】表分区(一)• MySQL版

2023-08-20 14:00

关注

请添加图片描述

欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享,与更多的人进行学习交流

本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习,有基础也有进阶,有MySQL也有Oracle

请添加图片描述

  CREATE TABLE IF NOT EXISTS student        (id INT, name VARCHAR(50), age INT, address VARCHAR(100));CREATE TABLE IF NOT EXISTS `student`        ( `id` INT, `name` VARCHAR(50), `age` INT, `address` VARCHAR(100) ) ;

2.1 概念

分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表。
但是对于应用程序来讲,分区的表和没有分区的表是一样的。
换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理。

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

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

delete 要记录日志,如果开启事务的话,可以进行回滚,一行一行的删除,效率慢
truncate 直接删除底层的数据页,MySQL的物理结构底层是数据页

2.2 MySQL数据库表分区

2.2.1 InnoDB 逻辑存储结构

2.2.2 段(segment)

表空间是由各个段组成的,常见的段有数据段、索引段、回滚段等。

  • InnoDB存储引擎表是索引组织的(index
    organized),因此数据即索引,索引即数据。那么数据段即为B+树的页节点(上图的leaf node
    segment),索引段即为B+树的非索引节点(上图的non-leaf node segment)。

  • 与Oracle不同的是,InnoDB存储引擎对于段的管理是由引擎本身完成,这和Oracle的自动段空间管理(ASSM)类似,没有手动段空间管理(MSSM)的方式,这从一定程度上简化了DBA的管理。

2.2.3 区(extent)

区是由64个连续的页组成的,每个页大小为16KB,即每个区的大小为1MB。对于大的数据段,InnoDB存储引擎最多每次可以申请4个区,以此来保证数据的顺序性能。

在我们启用了参数innodb_file_per_talbe后,创建的表默认大小是96KB。

区是64个连续的页,那创建的表的大小至少是1MB才对啊?其实这是因为在每个段开始时,先有32个页大小的碎片页(fragment page)来存放数据,当这些页使用完之后才是64个连续页的申请。这样做得目的是,对于一些小表或者undo类的段,可以开始申请较小的空间,节约磁盘开销

2.2.4 页(page)

页就是上图的page区域,也可以叫块。

页是InnoDB磁盘管理的最小单位。默认大小为16KB,可以通过参数innodb_page_size来设置

常见的页类型有:数据页,undo页,系统页,事务数据页,插入缓冲位图页,插入暖冲空闲列表页,未压缩的二进制大对象页,压缩的二进制大对象页等。

2.3 MySQL数据库分区的由来

2.4 为什么对表进行分区?

为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。

2.4.1 表分区要解决的问题

当表非常大,或者表中有大量的历史记录,而“热数据“却位于表的末尾。如日志系统、新闻…此时就可以考虑分区表。(热数据就是经常使用的数据)
【注:此处也可以使用分表,但是会增加业务的复杂性】

2.4.2 表分区有如下优点:

PS:因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。

这种查询的一个简单例子如

SELECT salesperson_id,COUNT (orders) as order_total FROM sales GROUP BY salesperson_id

2.5 MySQL的分区形式

2.5.1 水平分区(HorizontalPartitioning)

2.5.2 垂直分区(VerticalPartitioning)

2.6 MySQL分区的类型

2.6.1 range分区:范围表分区,按照一定的范围值来确定每个分区包含的数据

create table user(id int(11) not null,name varchar(32) not null)  -- 正常的创建语句partition by range(id)   -- 根据表字段id来创建分区  <--分区的定义( -- 分区实例 -->  partition p0 values less than(10),       -- 第一个分区p0,范围~-9  partition p1 values less than(20),       -- 第二个分区p1,范围10-19  partition p2 values less than(30),       -- 第三个分区p2,范围20-29  partition p3 values less than maxvalue   -- 第四个分区p3,范围30-~)   -- 需要注意的是分区字段“id”的取值范围等于分区取值范围

在这里插入图片描述

来源地址:https://blog.csdn.net/qq_40332045/article/details/131625396

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯