没有合适的资源?快使用搜索试试~ 我知道了~
MySQL分区管理工具
资源推荐
资源详情
资源评论
MYSQL 分区管理工具
--李田龙
--2017.08.01
一、 MYSQL 的分区表简介
1. 什么是表分区?
是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是
底层却是由多个物理分区组成。
例如:
CREATE TABLE `rangeyear_t1_kafka_consume_log` (
`id` int(11) NOT NULL COMMENT 'id',
`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '消息接收时间',
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(create_time))
(PARTITION p2012 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB);
cnsz22pl0015:cmdpsit > ll|grep rangeyear_t1_kafka_consume_log
-rw-rw---- 1 mysql mysql 8889 Jun 18 13:55 rangeyear_t1_kafka_consume_log.frm
-rw-rw---- 1 mysql mysql 128 Jun 18 13:55 rangeyear_t1_kafka_consume_log.par
-rw-rw---- 1 mysql mysql 98304 Jun 18 11:27 rangeyear_t1_kafka_consume_log#P#p2012.ibd
-rw-rw---- 1 mysql mysql 98304 Jun 18 11:27 rangeyear_t1_kafka_consume_log#P#p2013.ibd
-rw-rw---- 1 mysql mysql 98304 Jun 18 11:27 rangeyear_t1_kafka_consume_log#P#p2014.ibd
2. 表分区有什么好处?
1)分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。
2)和单个磁盘或者文件系统相比,可以存储更多数据
3) 优化查询。在 where 语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及 sum 和 count
语句时,也可以在多个分区上并行处理,最后汇总结果,类似 oracle 库中的分区修剪技术。
4)分区表更容易维护。例如:想批量删除大量数据可以 truncate 或者 drop 整个分区。
5)可以使用分区表来避免某些特殊的瓶颈,例如 InnoDB 的单个索引的互斥访问。
例如:
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
3. 分区表的限制因素
1)一个表最多只能有 1024 个分区
2)MySQL5.1 中,分区表达式必须是整数,或者返回整数的表达式。在 MySQL5.5 中提供了非整数表达式分区的支
持。MYSQL5.5 之后支持整数、日期时间、字符串。
3)如果分区表有唯一索引(主键),所有分区表达式中的列必须包含在唯一索引(主键)中
4)分区表中无法使用外键约束
5)MySQL 的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而
不对表分区,也不能只对表的一部分数据分区;即只有局部索引,没有全局索引。
4. 分区表的分类
1)range 分区, 按照数据的区间范围分区(连续递增)
partition by range(exp)( //exp可以为列名或者表达式,比如to_date(created_date)
partition p0 values less than(num)
)
# range范围覆盖问题:当插入的记录中对应的分区键的值不在分区定义的范围中的时候,插入失败
#分区键的值如果是NULL,将被作为一个最小值来处理
2)LIST 分区:按照 List 中的值分区(枚举)
partition by list(exp)( //exp为列名或者表达式
partition p0 values in (3,5) //值为3和5的在p0分区
)
#不适合分区经常变动的需求
3)HASH 分区 :主要用来分散热点读,使用取模算法
partition by hash(store_id) partitions 4;
#
数据尽可能的均匀分布。
缺点:不适合分区经常变动的需求
#mysql
提供了线性
hash
分区,与普通
hash
分区相比起对分区变更处理更迅速,但数据分布不大均衡
partition by LINER hash(store_id) partitions 4;
4)Key 分区,类似 Hash 分区,Hash 分区允许使用用户自定义的表达式,但 Key 分区不允许使用用户自定义的表
达式。Hash 仅支持整数分区,而 Key 分区支持除了 Blob 和 text 的其他类型的列作为分区键。
partition by key(exp) partitions 4;//exp是零个或多个字段名的列表
5) Composite(复合模式)
6) Columns 分区,MySQL5.5 中引入的分区类型,解决了 5.5 版本之前 range 分区和 list 分区只支持整数分区的
问题。 Columns 分区可以细分为 range columns 分区和 list columns 分区,他们都支持整数,日期时间,字符
串三大数据类型。(不支持 text 和 blob 类型作为分区键) columns 分区还支持多列分区。
PARTITION BY RANGE COLUMNS(create_time)
(PARTITION p2015 VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
PARTITION p20180211 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
二、 MYSQL 分区管理 procedure 的架构
1. 分区管理的架构逻辑
根据对总部所有 mysql 库中的分区表的汇总和分析,主要的分区类型为 range、range columns、hash;其中 range
分区占到 62%,range columns 分区占到 23%;而这两种分区类型中 99%的分区键是按照时间字段作为分区的依据。
所以针对 SF 分区表的使用情况,编写了一套统一的分区管理工具。
由上一节分区表的基础知识介绍可得知 range 和 range columns 的区别,首先,两种分区的关键字不同;其次 range
分区的依据可以是一个表达式 range(exp),并且该表达式的值必须是一个整数,而 range columns 的分区依据不
支持表达式,但可以是多个列 RANGE COLUMNS(create_time),其中列的类型也不限于整数,可支持时间,字
符串。
根据以上信息,mysql 分区管理工具的架构逻辑如下图所示
上图中红色小字标识得为存储过程的名字,首先,整个管理工具是由 proc_main_partition 发起调用,其次,
剩余10页未读,继续阅读
资源评论
xiedy001
- 粉丝: 47
- 资源: 29
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功