新手如何做网站运营,仕德伟做的网站,深圳网站建设服务商,如何自己搭建vps上外网欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享#xff0c;与更多的人进行学习交流 本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习#xff0c;有基础也有进阶#xff0c;有MySQL也有Oracle 分区表 • MySQL版 一、分区表1.非分区表2.分区表2… 欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享与更多的人进行学习交流 本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习有基础也有进阶有MySQL也有Oracle 分区表 • MySQL版 一、分区表1.非分区表2.分区表2.1 概念2.2 MySQL数据库表分区2.2.1 InnoDB 逻辑存储结构2.2.2 段(segment)2.2.3 区(extent)2.2.4 页(page) 2.3 MySQL数据库分区的由来2.4 为什么对表进行分区2.4.1 表分区要解决的问题2.4.2 表分区有如下优点 2.5 MySQL的分区形式2.5.1 水平分区(HorizontalPartitioning)2.5.2 垂直分区(VerticalPartitioning) 2.6 MySQL分区的类型2.6.1 range分区:范围表分区,按照一定的范围值来确定每个分区包含的数据 一、分区表
1.非分区表 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) ) ;注意 数据库名、表名、字段名反勾号 是系统导出DDL语句自带格式也可以不写。
2.分区表
2.1 概念
分区是一种表的设计模式通俗地讲表分区是将一大表根据条件分割成若干个小表。 但是对于应用程序来讲分区的表和没有分区的表是一样的。 换句话来讲分区对于应用是透明的只是数据库对于数据的重新整理。
MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候优化器根据分区定义过滤那些没有我们需要的数据的分区这样查询就可以无需扫描所有分区只需要查找包含需要数据的分区即可。
分区的另一个目的是将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起另外当我们想要一次批量删除整个分区的数据也会变得很方便(可以单独truncate分区) delete 要记录日志,如果开启事务的话,可以进行回滚,一行一行的删除,效率慢 truncate 直接删除底层的数据页,MySQL的物理结构底层是数据页 2.2 MySQL数据库表分区
2.2.1 InnoDB 逻辑存储结构
InnoDB存储引擎的逻辑存储结构和Oracle大致相同所有数据都被逻辑地存放在一个空间中我们称之为表空间(tablespace。表空间又由段(segment)、区(extent)、页(page) 组成。页在一些文档中有时也称为块(block)1 extent 64 pages
2.2.2 段(segment)
表空间是由各个段组成的常见的段有数据段、索引段、回滚段等。
对于回滚段进行delete后可以回滚数据所以delete既占空间也耗时间,truncate相当于直接将页格式化了(不要再讲truncate是讲表删除后又重建了一个,不太恰当) 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数据库分区的由来
传统不分区数据库痛点 mysql数据库中的数据是以文件的形式存在磁盘上的默认放在/mysql/data下面可以通过my.cnf中的datadir来查看一张表主要对应着三个文件一个是frm存放表结构的一个是myd存放表数据的一个是myi存表索引的。这是myisam引攀如果是innodb则是frm和ibd文件索引和数据在一起
2.4 为什么对表进行分区
为了改善大型表以及具有各种访问模式的表的可伸缩性可管理性和提高数据库效率。
2.4.1 表分区要解决的问题
当表非常大或者表中有大量的历史记录而“热数据“却位于表的末尾。如日志系统、新闻…此时就可以考虑分区表。(热数据就是经常使用的数据) 【注此处也可以使用分表但是会增加业务的复杂性】
2.4.2 表分区有如下优点
与单个磁盘或文件系统分区相比可以存储更多的数据对于那些已经失去保存意义的数据通常可以通过删除与那些数据有关的分区很容易地删除那些数据。 相反地在某些情况下添加新数据的过程又可以通过为那些新数据专门增加一个新的分区来很方便地实现。同样的你可以很快的通过删除分区来移除旧数据还可以优化、检查、修复个别分区 一些查询可以得到极大的优化。可以把一些归类的数据放在一个分区中可以减少服务器检查数据的数量加快查询。 这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内这样在查找时就不用查找其他剩余的分区。 PS因为分区可以在创建了分区表后进行修改所以在第一次配置分区方案时还不曾这么做时可以重新组织数据来提高那些常用查询的效率。 涉及到例如SUM()和COUNT()这样聚合函数的查询可以很容易地进行并行处理。 通过“并行”这意味着该查询可以在每个分区上同时进行最终结果只需通过总计所有分区得到的结果。 这种查询的一个简单例子如 SELECT salesperson_id,COUNT (orders) as order_total
FROM sales
GROUP BY salesperson_id通过跨多个磁盘来分散数据查询来获得更大的查询吞吐量
2.5 MySQL的分区形式
2.5.1 水平分区(HorizontalPartitioning)
这种形式的分区是对根据表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分别(单分区)或集体分别(1个或多个分区)所有在表中定义的列在每个数据集中都能找到所以表的特性依然得以保持水平分区一定要通过某个属性列来分别常见的有年份、日期
2.5.2 垂直分区(VerticalPartitioning)
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度是某些特定的列被划分到特定的分区每个分区都包含了其中的列所对应的所有行
2.6 MySQL分区的类型 根据所使用的不同分区规则可以分成几大分区类型 MySql默认是支持表分区的可以通过语句查询是否开启表分区功能show plugins
2.6.1 range分区:范围表分区,按照一定的范围值来确定每个分区包含的数据
语法如下 partition by range(id) partition p0 values less than()示例
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范围~-9partition p1 values less than(20), -- 第二个分区p1范围10-19partition p2 values less than(30), -- 第三个分区p2范围20-29partition p3 values less than maxvalue -- 第四个分区p3范围30-~
) -- 需要注意的是分区字段“id”的取值范围等于分区取值范围maxvalue只是可以这么做,但是实际情况不可能把后面的所有数据都放在同一个分区,如果进行删除的话,那就是直接将后面的所有数据都删除了,不符合业务逻辑range分区一般用于生产运维、比较固化的调度场景很少进行补数据的操作如果涉及到补数据 根据上面的分区假设要把范围15-19的放在一个新的分区这就需要用到其他的手段如重组分区recognation如果是Oracle的话还需要进行split,对分区进行一个分割以后想补旧的数据的时候假设对第一个分区进行再分割很不方便之后会用存储过程进行存取数据的操作存储过程的变量定义都是写死的很难去增加一个范围分区(往后增加可以比如上面的maxvalue但是不能往前增加比如上面的第一个分区的前面)
