专栏首页吴伟祥MySQL的分表与分区(转)

MySQL的分表与分区(转)

MySQL分表分区是解决大数据量导致MySQL性能低下的两种方法。

什么是MySQL分表

从表面意思上看,MySQL分表就是将一个表分成多个表,数据和数据结构都有可能会变。MySQL分表分为垂直分表和水平分表。

1、垂直分表

垂直分表是按表中的字段来划分的,如下图所示。

在上图中,我们将本来分布在同一张表中的C1、C2、C3、C4四个字段垂直划分到两个表中。第一张表中分布C1、C3、C4三个字段,第二张表中分布C1、C2两个字段。拆分后的两个表通过C1这个共同的字段关联起来。

2、水平分表

水平分表是按表中的记录来划分的。如下图所示。

在上图中,我们将本来分布在同一张表中的四条记录,水平拆分到两个表中。第一张表中,分布两条记录;第二张表中,分布两条记录。

3、分表操作

MySQL分表既可以自定义规则,也可以使用业内通用规则,还可以使用merge存储引擎来实现。

1)自定义规则

按照用户或业务的编号分表。对与用户或业务可以按照编号%n,进行分成n表。

按照日期分表。对于日志或统计类等的表。可以按照年,月,日,周分表。

2)使用Merge存储引擎

使用Merge存储引擎实现MySQL分表比较适合那些没有事先考虑分表,随着数据的增多,已经出现了数据查询慢的情况。使用Merge存储引擎实现MySQL分表可以避免改代码。使用Merge实现MySQL分表可以按如下形式操作:

在上图中,ENGINE = MERGE表示,使用merge引擎。另外ENGINE = MRG_MyISAM是一样的意思。UNION = (user1, user2)表示,挂接了user1、user2表,INSERT_METHOD = LAST表示插入方式:0不允许插入,FIRST插入到UNION中的第一个表,LAST插入到UNION中的最后一个表。

使用Merge存储引擎实现MySQL分表,分表后的结果会分为主表和子表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。如下图所示。

上图是对user表进行merge分表的结果,alluser是总表,user1和user2是分表。每一个表都有自己的表结构,子表而且还保存了数据和索引,总表没有保存数据和索引,总表只保存了分表的关系,以及插入数据的方式。

4、分表查询

对于分表后的查询操作,依然是联合查询,视图等基本操作,或者使用merge引擎合并数据并在此表中查询。复杂一些操作需要借助存储过程来完成,借助外部工具实现对分表的管理。如:

  • 垂直分表的使用join连接、水平分表的使用union连接。
  • 对于使用Merge存储引擎实现的MySQL分表,可以直接查询总表。

5、注意事项

1)重复记录 / 重复索引

若建立Merge表前,分表t1 / t2已经存在,并且t1 / t2中存在重复记录。查询时,遇到满足记录的条目就会返回。意思就是只会显示一条记录,同时不会报错。若建立Merge表后,insert / update时,出现重复索引,则会提示错误。MERGE表只对建表之后的操作负责。

2)如何删除一个分表

不能直接删除一个分表,这样会破坏Merge表。正确的方法是:

alter table t ENGINE = MRG_MyISAM UNION = (t1) INSERT_METHOD = LAST;  

drop table t1;

3)误删Merge总表

误删Merge表,是不会造成数据丢失的,只需重新创建总表。

什么是MySQL分区

从表面意思看,MySQL分区就是将一张表的数据分成多个存储区块,而数据结构不变。另外,这些存储区块既可以在同一个磁盘上,也可以在不同的磁盘上。如下图所示。

上图是对表aa进行分区后,磁盘上的文件分布。从图中我们可以看到,分区后aa表的数据结构没有发生变化,而数据和索引存储的位置由原来的一个变成了两个。另外,多出了一个.par文件,打开.par文件后你可以看出他记录了这张表的分区信息。

1、分区操作

MySQL从5.1.3开始支持Partition,你可以使用如下命令来确认你的版本是否支持Partition:

MySQL支持的分区类型包括Range、List、Hash、Key,其中Range比较常用:

1)Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。如下:

在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录。

2)List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA根据用户的类型进行分区。

3)Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

4)Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。

2、注意事项

1)以上每一种分区方式,都可以将这些分区所在的物理磁盘分开完全独立,以提高磁盘IO吞吐量。如下:

上图就是对Range(范围)分区类型进行物理空间的分离操作。

2)分区虽然很爽,但目前的实现还有很多限制:

  • 主键或者唯一索引必须包含分区字段:如PRIMARY KEY(i,created)。
  • 很多时候,使用了分区就不要再使用主键,否则可能影响性能。
  • 只能通过int类型的字段或者返回int类型的表达式来分区:通常使用YEAR或TO_DAYS等函数。
  • 每个表最多1024个分区:不可能无限制的扩展分区,而且过度使用分区往往会消耗大量系统内存。
  • 采用分区的表不支持外键:相关的约束逻辑必须通过程序来实现。

MySQL分表和分区的异同

都能提高mysql的性高,在高并发状态下都有一个良好的表现。

分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式(如果merge这种分表方式,不能和分区配合的话,可以用其他的分表试),访问量不大,但是表数据很多的表,我们可以采取分区的方式等。

分表技术是比较麻烦的,需要手动去创建子表,app服务端读写时候需要计算子表名。采用merge好一些,但也要创建子表和配置子表间的union关系。

表分区相对于分表,操作方便,不需要创建子表。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 了解Docker 原

    docker入门教程是由王春生翻译的docer官方的相应资料,旨在为大家提供docker的中文资料,方便大家了解学习docker。 菜鸟教程:http://...

    wuweixiang
  • Docker安装Jenkins实现项目自动部署(Java Web项目) 顶

    Jenkins是一个开源软件项目,是基于Java开发的一种持续集成工具,用于监控持续重复的工作,旨在提供一个开放易用的软件平台,使软件的持续集成变成可能。

    wuweixiang
  • Docker学习总结——理解Docker与安装(一) 顶

    Docker 是一个开源的应用容器引擎(软件工业上的集装箱技术),让开发者可以打包他们的应用以及依赖包到一个可移植的容器中,然后发布到任何流行的Linux机器上...

    wuweixiang
  • 腾讯给内部员工的16个防疫贴士

    ? 五一放假期间,我们拍了一支高清有码的片子: 是想让你知道,在全面战疫的两个月里,腾讯人是怎么做的? 认真洗手戴口罩,保持距离勤消毒,电梯化身“九宫格”,点...

    鹅老师
  • Centos7下JDK1.8的安装

    https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151....

    sunny1009
  • clip-path

    用户7873631
  • FZU 2092 收集水晶(记忆化搜索)

    Problem 2092 收集水晶 Accept: 101 Submit: 439 Time Limit: 5000 mSec Memory...

    ShenduCC
  • 奔赴一场关于青春和腾讯的“约会”——记南开大学腾讯之行

    ?       立夏刚过,北京的天气就变得炎热了起来,而一批朝气蓬勃的小伙伴们的到来为腾讯带来了一丝清凉,他们是腾讯高校合作中心的老朋友——南开大学腾讯创新俱...

    腾讯高校合作
  • 3分钟速读原著《高性能MySQL》(四)

    Induction是一款用于理解数据关系的开源管理工具,它可用来探索行/列,运行查询和数据可视化等方面。该工具支持多种数据库,包括PostgreSQL,My...

    cwl_java
  • 采用Mono进行移动开发图书推荐

    今天在新浪微博上csumathboy发的微博,我也把它微博中提到的一本书在腾讯微博上发了下,引来几十条的讨论,具体参见http://t.qq.com/p/t/2...

    张善友

扫码关注云+社区

领取腾讯云代金券