>>与软件开发有关的知识:操作系统,数据库,网络通信等 书籍支持  视频课程  卫琴专栏  在线测试  资源下载  联系我们
发表一个新主题 开启一个新投票 回复文章 您是本文章第 12931 个阅读者 刷新本主题
 * 贴子主题:  mysql分区(partition) 回复文章 点赞(0)  收藏  
作者:flybird    发表时间:2020-03-23 17:12:15     消息  查看  搜索  好友  邮件  复制  引用

                                                                                                

mysql分区(partition)

分区和性能

  数据库的应用分为两类:

1、OLTP 在线事务处理,如blog,电子商务,网络游戏
2、OLAP 在线分析处理,如数据仓库,数据集市
3、对于 OLAP的应用,分区的确是可以很好地提高查询的性能,因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表,其中有一个时间戳属性列,用户查询需要从这张表中获取一年的数据,如果按时间进行分区,则只需要扫描相应的分区即可。

4、但是对于OLTP的应用,分区应该非常小心,在这种应用下,通常不可能会获取一张达标中10%的数据,大部分都是通过索引返回几条记录即可。根据B+树索引的原理可知,对于一张大表,一般的B+树需要2~3次磁盘IO。因此B+树可以很好地完成操作,不需要分区的帮助。并且设计不好的分区会带来严重的性能问题,

比如1000行的表,对主键做10个hash分区,这样每个分区就只有100w的数据了,因此查询应该变得快了,但是有没有考虑这样一种情况:100w和1000w行的数据本身构成B+树层次都是一样的,可能是2层。那么走主键分区的索引并不会带来性能的提高。如果1000wB+树的高度是3层,100w的是2层,那么按照主键索引可以避免1次IO,从而提高了效率。这是对主键索引进行查询,但是如果对其他列进行查询,这时需要扫描10个分区,每个分区的开销是2次IO,一共20次IO。而对原来的单表设计,对于列的查询只有2~3次IO。

因此,对于使用innodb存储引擎作为OLTP应用的表在试用分区时应该十分小心,设计时确认数据的访问模式,否则在OLTP应用下分区可能不仅不会带来查询速度的提高,反而可能会是你的应用执行的更慢。

分区类型

range,list,hash,key这四种分区中,分区的条件是:数据必须是整型,如果不是整型,那应该通过函数将其转化为整型(如:YEAR(),TO_DAYS(),MONTH()等函数)。

range分区

  create table sales (
      money int unsigned not null,
      date datetime
  ) engine=innodb
  partition by range (TO_DAYS(date)) (
      partition p201001 values less than (TO_DAYS('2010-02-01')),
      partition p201002 values less than (TO_DAYS('2010-03-01')),
      partition p201003 values less than (TO_DAYS('2010-04-01'))
  );

对于range分区的查询,优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化分区。

list分区

   create table t1(
      a int,
      b int
  ) engine=innodb
  partition by list(b) (
      partition p0 values in (1,3,5,7,9),
      partition p1 values in (0,2,4,6,8)
  );

list分区,如果是插入多行数据时,有不符合分区规则情况下,myisam存储引擎会存放符合分区规则的,而innodb会把它看成一个事务,不会插入数据,会报错

hash分区

   --- hash 分 区  是 将 数 据 均 匀 的 分 配 到 预 先 定 义 的 各 个 分 区 中 , 保 证 各 分 区 的 数 量 大 致 都 是 一 样 的
  ---  要 使hash 分 区 来 分 割 一 个 表 , 要 在create table  语 句 上 添 加“partition by hash(expr)” 子 句 , 其 中expr 是
        一 个 返 回 一 个 整 数 的 表 达 式 , 它 可 以 仅 仅 是 字 段 类 型 为mysql 整 型 的 列 名 。
  ---  此 外 用 户 很 可 能 需 要 在 后 面 再 添 加 一 个"partitions num" 子 句 , 其 中num 是 一 个 非 负 整 数 , 表 示 表 将 要 分 割 成 分 区 的 数 量 , 没 有 这 个 子 句 , 默 认 是1
  
  create table t2(
      a int,
      b datetime
  ) engine=innodb
  partition by hash (year(b))
  partitions 4;

hash分区是取模算法:如b为2010-04-01时 mod(year('2010-04-01'), 4) = mod(2010, 4) = 2 因此记录会被放入分区p2中。

另一种hash分区——linear hash(hash算法不同)

这种分区算法的优点是:增加、删除、合并、和拆分分区将变得更加快捷,有利于含有处理大量数据的表。
缺点是:与使用hash分区得到的数据分布相比,各个分区间数据的分布可能不大均匀。

  create table t3(
      a int,
      b datetime
  )engine=innodb
  partition by linear hash (year(b))
  partitions 4;

分区算法是:同样是2010-04-01
1、取大于分区数量num=4的下一个2的幂值V,V = power(2, ceiling(log(2, NUM))) = 4;即log以2为底num的指数作为2的幂值。2^log(2, num)
2、所在分区N = YEAR('2010-04-01') & (V - 1) = 2

key分区

  key分区和hash分区相似,不同之处在于hash分区使用用户定义的函数进行分区,key分区使用MySQL数据库提供的函数进行分区。对于NDB cluster引擎,MySQL数据库使用MD5函数分区,对于其他 的数据库引擎,MySQL数据库使用其内部的哈希函数,这些函数基于与password()一样的运算法则

  create table t4(
      a int,
      b datetime
  )engine=innodb
  partition by key (b)
  partitions 4;

在key分区中使用关键字linear和在hash分区中使用具有相同的效果,分区标号是通过2的幂算法(powers-of-two)得到的,而不是通过模数算法。

columns分区

  MySQL5.5版本开始支持columns分区,可以看做range分区和list分区的一种进化。
columns分区可以直接使用非整型的数据进行分区,分区根据类型直接比较而得,不需要转化为整型。

columns分区支持以下的数据类型:
1、所有的整数类型,如int,smallint,tinyint,mediumint,bigint。float和decimal则不予支持
2、日期类型,如date和datetime。其余的日期类型不予支持。
3、字符串类型,如char,varchar,binary和varbinary。blob和text类型不予支持。

range columns

create table t5(
      a int,
      b datetime
  )engine=innodb
  partition by range columns (b) (
      partition p0 values less than ('2009-01-01'),
      partition p1 values less than ('2010-01-01')
  );

range columns 分区可以对多个列的值进行分区。

  create table t5_rcx(
      a int,
      b int,
      c char(3),
      d int
  )engine=innodb
  partition by range columns (a,d,c) (
      partition p0 values less than (5, 10, 'ggg'),
      partition p1 values less than (10, 20, 'mmm'),
      partition p2 values less than (15, 20, 'sss'),
      partition p3 values less than (maxvalue, maxvalue, maxvalue)
  );

list columns

       中 文 也 可 以
  create table t6(
      first_name varchar(25),
      last_name varchar(25),
      street_1 varchar(30),
      street_2 varchar(30),
      city varchar(15),
      renewal date
  )engine=innodb
  partition by list columns (city) (
      partition pRegion_1 values in (' 北 京',' 上 海',' 广 州'),
      partition pRegion_2 values in (' 武 汉',' 郑 州',' 成 都')
  );

子分区

  子分区是指在分区的基础上再进行分区,也称该分区为复合分区。MySQL允许在range和list的分区上再进行hash和key的子分区。

      create table ts(
      a int,
      b date
  )engine=innodb
  partition by range (YEAR(b))
  subpartition by hash(TO_DAYS(b))
  subpartitions 2
  (
      partition p0 values less than (1990),
      partition p1 values less than (2000),
      partition p2 values less than maxvalue
  );
  b 列 进 行range 分 区 , 又 进 行 了 一 次hash 分 区 , 分 区 的 数 量 是(3 x 2 = 6)

我们也可以使用subpartition语法显示的指出各个子分区的名字:

      create table dspy_sub_name(
      a int,
      b date
  )engine=innodb
  partition by range (YEAR(b))
  subpartition by hash (TO_DAYS(b))
  (
      partition p0 values less than (2009) (
           subpartition s0,
           subpartition s1
      ),
      partition p1 values less than (2010) (
           subpartition s2,
           subpartition s3
      ),
      partition p2 values less than maxvalue (
           subpartition s4,
           subpartition s5
      )
  );

子分区建立需要注意以下几个问题:
1、每个子分区的数量必须相同
2、要在一个分区表的任何分区上使用subpartition明确定义任何子分区,就必须定义所有的子分区。
3、每个subpartition子句必须包括子分区的一个名字
4、子分区的名字必须是唯一的。

子分区可以用于特别大的表,在多个磁盘间分别分配数据和索引。假设有6个磁盘,分别为/disk0,/disk1,/disk2等,现在考虑下面的例子:

  create table ts1(
      a int,
      b date
  )engine=innodb
  partition by range (YEAR(b))
  subpartition by hash (TO_DAYS(b))
  (
      partition p0 values less than (2009) (
           subpartition s0
           DATA DIRECTORY = '/disk0/data'
           INDEX DIRECTORY = '/disk0/idx',
           subpartition s1
           DATA DIRECTORY = '/disk1/data'
           INDEX DIRECTORY = '/disk1/idx'
      ),
      partition p1 values less than (2010) (
           subpartition s2
           DATA DIRECTORY = '/disk2/data'
           INDEX DIRECTORY = '/disk2/idx',
           subpartition s3
           DATA DIRECTORY = '/disk3/data'
           INDEX DIRECTORY = '/disk3/idx'
      ),
      partition p2 values less than (2011) (
           subpartition s4
           DATA DIRECTORY = '/disk4/data'
           INDEX DIRECTORY = '/disk4/idx',
           subpartition s5
           DATA DIRECTORY = '/disk5/data'
           INDEX DIRECTORY = '/disk5/idx'
      )
  );

由于innodb存储引擎使用表空间自动的进行数据和索引的管理,因此会忽略DATA DIRECTORY 和INDEX DIRECTORY语法,因此上述分区表的数据和索引文件的分开放置对其是无效的。

分区中的null值

  mysql数据库允许对null值做分区。MySQL数据库的分区总是把null值看做是小于任何一个非null值,这和MySQL数据库中处理null值得order by操作是一样的。因此对于不同的分区类型,MySQL数据库对于null值的处理也是不相同的。
1、对于range分区,如果向分区中插入null值,则MySQL数据库会将该值放入最左边的分区,另外注意的是如果删除最左边的分区,则会删除该分区的记录包括null值的记录
2、list分区下要使用null值,则必须显示地指出哪个分区中放入null值,否则会报错。

  create table t(
      int a,
      int b
  )engine=innodb
  partition by list (b) (
      partition p0 values in (1,3,5,7,9,null),
      partition p1 values in (0,2,4,6,8)
  );

3、hash和key分区对于null的处理方式和range分区、list分区不一样。任何分区的函数都会讲含有null值得记录返回为0

在表和分区间交换数据

  MySQL5.6开始支持alter table   table_name  exchange partition   partition_name  with table   table_name的语法。该语法允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果纷纷去表中的数据为空,那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空,则相当于将外部表中的数据导入到分区中。
要使用这个语法要满足以下条件:

1、要交换的表必须和分区表有相同的表结构,但是要交换的表不能含有分区
2、在非分区表中的数据必须在交换的分区定义内
3、被交换的表中不能含有外键或者其他的表含有对该表的外键引用
4、用户除了需要alter,insert,create权限外,还需要有drop权限

另外,有两个小细节需要注意:

1、使用该语句时不会触发交换表和被交换表上的触发器
2、auto_increment列将被重置

      create table e(
      id int not null,
      fname varchar(30),
      lname varchar(30)
  )engine=innodb
  partition by range (id) (
      partition p0 values less than (50),
      partition p1 values less than (100),
      partition p2 values less than (150),
      partition p3 values less than maxvalue
  );

  ---  插 入 数 据
  insert into e values
      (1669, 'Jim', 'Smith'),
      (337, 'Mary', 'Jones'),
      (16, 'Frank', 'White'),
      (2005, 'Jerry', 'Tom');
  
  ---  创 建e2 表 , 复 制e 表 结 构 , 并 清 除partition
  create table e2 like e;
  alter table e2 remove partitioning;
  
  ---  交 换 数 据
  alter table e exchange partition p0 with table e2;

结果:
点击在新窗口中浏览原图
CTRL+鼠标滚轮放大或缩小

                                                                                        
----------------------------
原文链接:https://blog.csdn.net/xstongwandou/article/details/79497820

程序猿的技术大观园:www.javathinker.net



[这个贴子最后由 flybird 在 2020-03-25 13:41:56 重新编辑]
  Java面向对象编程-->内部类
  JavaWeb开发-->自定义JSP标签(Ⅱ)
  JSP与Hibernate开发-->立即检索和延迟检索策略
  Java网络编程-->用Spring整合CXF发布Web服务
  精通Spring-->WebFlux响应式编程
  Vue3开发-->计算属性和数据监听
  Https的1.0、2.0协议及长短链接区别
  针对 MySQL IO 特点进行的存储优化揭秘
  Mysql--所有版本安装以及完全卸载
  MySQL 运算符的用法
  从MySQL数据库中导出数据
  SQL NOT NULL 约束
  SQL FULL OUTER JOIN 关键字
  Linux 云服务器
  解决电脑网络受限制或无连接造成无法上网的方法
  MySQL5.7慢查询日志时间与系统时间差8小时原因
  MySQL的启动选项和系统变量该如何配置?
  linux系列之常用运维命令整理
  深入浅出一致性Hash原理
  MySQL的开发必会的SQL语句
  mysql主从复制的步骤
  更多...
 IPIP: 已设置保密
楼主      
1页 0条记录 当前第1
发表一个新主题 开启一个新投票 回复文章


中文版权所有: JavaThinker技术网站 Copyright 2016-2026 沪ICP备16029593号-2
荟萃Java程序员智慧的结晶,分享交流Java前沿技术。  联系我们
如有技术文章涉及侵权,请与本站管理员联系。