mysql建立分区表指令(MySQL高级特性——数据表分区的概念及机制详解)
mysql建立分区表指令
MySQL高级特性——数据表分区的概念及机制详解mysql 的分区的实现方式是对数据表进行一层包装,这意味着索引实际是基于每个分区定义的,而不是整张表。这个特性和 oracle 是不同的,在 oracle 中的索引和数据表可以使用更灵活和更复杂的方式进行分区。
mysql 的分区通过定义 patition by 子句的条件来决定数据行所属分区的归属。在执行查询的时候,查询优化器会区分所在分区,这意味着查询不会检查全部分区,而仅仅是那些包含索要查询数据所在的分区。
分区的主要目的是对数据表进行大致形式的索引和聚集。这样可以减少数据表的过大范围的访问,并可以将相关的数据行临近存储。分区的收益是显著的,尤其是对于下面的场景:
- 当数据表过大导致内存空间难以承载时,或者一张数据表中有很多历史数据以及热区行。
- 分区数据相比为分区数据更容易维护。例如,通过删除整个分区很容易将旧的数据清除,同时对于单个分区也可以很方便地进行优化、检查和修复操作。
- 分区数据在物理上可以分布存储,这使得服务器可以更高效地使用多个硬盘驱动器。
- 可以使用分区避免某些工作负荷的瓶颈。
- 对于数据备份而言,可以单独备份或恢复单个分区,这对于大的数据集来说十分有益。
mysql 的分区实现细节十分复杂,弄清楚是很难的,我们只需要关注它的性能即可。如果想进一步了解,可以翻阅 mysql 手册中关于分区的部分。有了分区后,也带来了其他问题以及限制:
- 创建表和更改表的命令更为复杂。
- 每张表最多只能有1024个分区。
- 在 mysql 5.1版本中,分区表达式必须是整数或者返回一个整数;在 mysql 5.5以后,在某些情况下可以使用列进行分区。
- 任主键或唯一索引都必须包含分区表达式中的全部列。
- 不可以使用外键约束。
分区机制
如前所述,分区表实际有多个隐藏的物理存储表,这通过句柄对象呈现。我们不能直接访问分区。通常,每个分区是通过存储引擎管理的(因此要求所有分区的存储引擎相同),而数据表中的索引实际上是隐藏的物理存储表的索引。从存储引擎的角度来看,分区也是数据表。存储引擎实际并不知道数据表是独立的还是一个大的数据表的一个分区。对于分区表的操作通过如下的逻辑操作实现:
select 查询
当对分区表进行查询时,分区层会打开和锁定全部的隐藏分区,查询优化器会决定那些隐藏分区可以忽略,然后分区层通过句柄 api 调用管理分区的存储引擎获取查询结果。
insert 操作
当插入一行数据时,分区层会打开和锁定全部分区,然后决定那个分区存储当前的数据行,并将该数据行存入对应分区。
delete 操作
删除一行数据时,分区层会打开和锁定全部分区,检查哪个分区包含该行数据,再将删除请求发送到该分区。
update 操作
修改一行数据时,分区层打开和锁定所有分区,检查哪个分区包含该行数据,并获取该行数据进行修改,然后再确定哪个分区应当包含新的数据行,并把插入请求发送到该分区,同时发送删除请求到旧的分区。
上面的有些操作支持分区过滤(即忽略无关的分区)。例如,删除一行时,服务器需要首先定位数据行位置。如果在 where 条件中指定了匹配的分区表达式条件,服务器可以忽略掉不包含该行的分区。对于 update 操作也是类似,而 insert 操作本身就是如此,服务器会只查找需要插入的一个分区,而不是全部。
虽然分区层打开和锁定了全部分区,但并不意味着分区会保持锁定。像 innodb 的存储引擎,可以支持行级别的锁定,会只是分区层解除分区的锁定。这个加锁和解锁的过程和普通的 innodb 数据表的锁定过程类似。
分区的类型
mysql 支持几种类型的分区,最常用的类型是范围分区——也就是针对某些列的的值或表达式按不同的范围进行分区。例如,下面的语句就是根据年份将销售数据分到不同的分区中:
|
create table sales ( order_date datetime not null --其他列定义 ) engine=innodb partition by range( year (order_date)) ( partition p_2018 values less than (2018), partition p_2019 values less than (2019), partition p_2020 values less than (2020), partition p_other values less than maxvalue); |
可以在分区子句中使用多种函数。最主要的要求是必须返回一个非常量的,确定的整数。在上面的例子中使用的是 year 函数,也可以使用其他函数,例如 to_days()。使用时间间隔进行分区是基于日期数据的常用方式。
mysql 也支持键,哈希以及列表的分区方法,有些还支持子分区(实际很少用)。在 mysql 5.5以后,可u一使用 range columns 的分区类型直接按基于日期的列进行分区,而不需要使用函数将日期转换为整数。 其他常见的分区技巧包括:
- 使用键进行分区以减少 innodb 的互斥量的竞争;
- 可以使用取余计算的方法来循环构建范围的分区,例如如果只需要保持最近几天的数据,可以通过对日期对7取余,或者使用所在的周天数进行分区。
- 假设数据表没有自增的主键,但是也想对聚集在一起的热区数据分区。由于时间戳不在主键里,也无法使用时间戳分区。这时候可以使用 hash(id li 1000000),这会在每1000000行数据进行分区。这使得无需更改主键也能完成我们要的效果。同时这样还有附加的效果。那就是我们无需创建分区的常量去保留新的数据。
以上就是mysql高级特性——数据表分区的概念及机制详解的详细内容,更多关于mysql高级特性 数据表分区的资料请关注开心学习网其它相关文章!
- mysql怎么看脱机数据(MYSQL电话号码,身份证数据脱敏的实现)
- mysql学习之索引介绍及其原理(MySQL学习教程之聚簇索引)
- 如何让mysql强制设置复杂的密码
- docker怎样安装mysql8(docker-compose安装db2数据库操作)
- windows下mysql密码重置(Windows10系统下Mysql8.0.13忘记root密码的操作方法)
- mysql的默认事务隔离级别(啥是 MySQL 事务隔离级别?)
- mysql中group_concat
- MySQL中查询数据库的大小
- mysql给字段加默认值(MySQL 如何处理隐式默认值)
- ubuntu20.2安装mysql(Ubuntu 14.04下mysql安装配置教程)
- mysql主从复制时突然来了一批数据(MySQL主从复制断开的常用修复方法)
- mysql主从复制配置(Mysql实现主从配置和多主多从配置)
- mysql架构方案讲解(MySQL 搭建MHA架构部署的步骤)
- mysql剩余表空间大小(MySQL 表空间碎片的概念及相关问题解决)
- 查看mysql索引缓存(MySQL查询缓存的小知识)
- MySQL中对varchar类型的排序
- 贾怀胤唱《白龙马》 炸场 了 没想到京剧还能这么玩(贾怀胤唱白龙马)
- 白龙马的改编学生版,快来看看(白龙马的改编学生版)
- 萌娃唱《白龙马》走红,那生动的小表情,网友直呼 简直是戏精(萌娃唱白龙马走红)
- 朱鹤松被不断认可,凤凰传奇玲花喊话岳云鹏,索要老朱演出门票(朱鹤松被不断认可)
- 元宵晚会槽点多,芒果台上来就假唱,岳云鹏不说相声改评书了(元宵晚会槽点多)
- 岳云鹏跟凤凰传奇谈心,说出了人生中最重要的三个人,这才成功(岳云鹏跟凤凰传奇谈心)
热门推荐
- C#中typeof 与GetType()的区别
- sql server事务回滚(SQL Server 添加Delete操作回滚日志方式)
- python简易版学生管理系统(python3.6实现学生信息管理系统)
- explain语句详解(聊聊explain查看执行计划)
- canvas怎么获取内容(详解canvas.toDataURL报错的解决方案全都在这了)
- yii2支持的数据库(Yii框架常见缓存应用实例小结)
- 云服务器系统怎么选择(云服务器选择什么操作系统?)
- css标签及属性的语法规则(详解CSS 伪元素及Content 属性)
- laravel 获取数据库操作异常(Laravel Eloquent ORM 多条件查询的例子)
- hive分析后如何录入mysql(hive从mysql导入数据量变多的解决方案)