MySQL分区分库分表(2) --- 实操篇

上一篇主要讲到了分区分库分表的概念,其实在不影响性能的情况下,我们完全可以使用单分区单库单表。但是业务量大的情况下,受到性能限制我们不得不选择使用分区分库分表。本篇是上一篇的拓展,本篇主要讲讲十几种我们如何使用分区分库分表。如果还未看过上一篇文章建议先阅读概念篇:Mysql分库分表(1) --- 概念篇

查看MySQL是否支持分区

并不是所有版本都支持分区,所以需要先查看当前MySQL版本是否支持分区操作,在5.6以上版本使用命令:

show  plugins;

当查询结果显示partition的状态为active则表示当前MySQL版本支持分区。我们上篇也讲过了,分区方案一般有四种:

  • RANGE分区:RANGE分区主要用于日期列的分区,是基于连续区间的列值来进行分区的。RANGE分区也是最常用的分区方式。
  • LIST分区:LIST分区列的值是散列的,也正是由于分区列的值是散列的所以需要手动定义分区列的值。
  • HASH分区:HASH分区就是通过自定义算法将数据均匀分布到不同分区,保证所有分区中数据大致相同。HASH分区只支持整数分区。
  • KEY分区:key分区使用数据库自带函数进行分区。KEY分区可支持BLOBTEXT列之外的数据列分区。

由于RANGE分区是最常用的分区方案,其他三种分区方案用的相对很少。所以分区我主要介绍RANGE方案。在开始实际分区前我们得了解下分区的几点限制:

  • 数据表分区上限最多只能有1024个分区。
  • 同一个表的所有分区必须使用相同存储引擎。
  • 数据库使用分区则不能使用外键约束,会报错。

RANGE分区

RANGE分区就是用于连续区间的字段分区,接下来我们可以来实践一下创建一个order_info表并进行RANGE分区。首先创建表并进行分区:

这样我们就成功创建一个表并创建了5个分区,接下来我们查看order_info表分区情况,看是否成功形成5个分区:

可以看到上面的创建分区我们使用values less than语句,我们将id<10的数据保存在order1分区,将id范围在10-20保存在order2分区,以此类推创建了五个分区。现在我们可以写个存储过程插入50条数据测试下:

可以明显看到报错了,报没有分区可以存放id为50的值的错误信息。因为我们设置的第五个分区范围为40-50,右边不包含,所以超过50直接报错无法存放。这很不合理,所以说我们得对分区做一下调整,我们使用alter table命令新增分区order6保存大于50的值:

我们增加了order6分区,值设置为maxvalue,表示如果值大于order5设置的上限则保存进入order6分区。接下来我们插入500条数据到数据库中测试下效果:

我们查看下分区下各自含有几条数据

我们可以看到完全按照我们设置的分区进行数据存放到不同的分区。可能有人会说我怎么确认order1分区存放的9条数据一定是id为1-10的呢?这个其实测试很简单,我们直接删除order1分区,然后查看数据库数据:

可以看到我们删除掉order1分区之后,id10以下的数据也跟着被删除了,所以可以表示id在10以下的确实保存在分区order1中。而我们完全可以按照时间来进行分区,然后将半年以上的数据移到备用表然后删除分区,这样就可以提高我们单表的性能。在删除数据时直接删除分区会远远比delete数据效率高很多。

Mycat实现分库分表

myCat是一个分布式事务中间件,我们可以简单理解为是一个代理,其核心功能是分库分表,所以接下来我们来看下如何使用Mycast实现分库分表。首先我准备了两个数据库服务:

ip:49.235.28.88  这是主库
ip: 49.232.154.38 这是分库,并且Mycat也装在这台服务器

Mycat安装

/*下载tar包*/
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz


/*解压tar包*/  
tar Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

首先进入/usr/local/mycat/conf目录,我们需要使用的一共有三个文件:

  • schema.xml:主要配置物理数据库信息以及表和路由之间的关系。
  • rule.xml:配置路由策略及拆分规则。
  • server.xml:配置逻辑数据库连接信息。
  • sequence_db_conf.properties:配置主键自增说明

配置server.xml

server.xml是配置逻辑数据库的配置信息,也就是后端连接mycat的链接信息,我们可以随便配置,但是schema值要与schema.xml中配置一致:

schema.xml配置 接下来我们进入schema.xml中配置物理数据库的配置,首先配置dataHost节点,该节点配置我们真正数据库的连接信息:

我们看下dataHost根标签参数的含义:

heartbeat标签实际上就是用于和后端数据库进行心跳检查,mysql使用select user(),这个默认值不需要修改。接下来看看writeHost和readHost标签,从名字就可以看出writeHost用于指定写实例,readHost用于指定读实例:

配置完成dataHost标签,下一步配置dataNode标签:

dataNode定义了Mycat数据节点,也就是数据分片。我们看下三个参数的含义,dataHost需要和dataHost标签的name属性设置一致:

配置完成dataNode标签,最后配置schema标签:

schema标签的name值需要与server.xml文件我们配置的schema值一致,可以使用多个schema链接不同的逻辑库。我们重点看下table标签参数含义:

rule.xml配置

在rule.xml中使用tableRule定义表规则,name值需要与schema.xml中table标签中的rule参数设置一致,columns指定需要定义规则的列名,algorihm定义规则,名称和function的name值一一对应。逻辑表会按照指定的规则进行分片。然后在function标签中使用class属性设置路由算法的类名称,在使用property字标签设置算法需要使用的一些属性。

启动Mycat

cd /usr/local/mycat/bin/mycat start

然后使用server.xml文件配置的mycat登录信息登录mysql,然后查看user数据库下的数据表,然后插入三条记录到user下的users数据表在mycat上是可以全部查询:

但是我登录主库可以看到缺少了中间的数据

登录从库发现只有中间的记录:

可以发现我们数据成功分库分表了,将不同的数据按照我们定义的拆分规则保存到不同的库中。当然这就是最简单的使用Mycat,Mycat有更大强大的功能,有兴趣的可以自行去体验体验。

本文分享自微信公众号 - 程序猿周先森(zhanyue_org)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-10-22

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏小白帽学习之路

集成环境phpstudy后门利用复现

phpStudy是一个PHP调试环境的程序集成包。该程序包集成最新的Apache+PHP+MySQL+phpMyAdmin+ZendOptimizer,一次性安...

20320
来自专栏Android技术分享

Android:你要了解的自定义View基础概念都在这里了!

自定义View原理是Android开发者必须了解的基础,在了解自定义View之前,你需要有一定的知识储备。

6410
来自专栏自学测试之道

python+pytest单元测试框架之生成各种格式测试报告

前面讲到使用pytest执行单个、多个或指定的测试用例。接下来讲解下通过运行测试用例,怎么来生成不同格式的测试报告

9820
来自专栏一名白帽的成长史

【SQL注入】关于报错注入的一些测试

我们这里使用sqli-labs靶机来进行测试,这是一个练习sql注入的专用靶机,如下:

6920
来自专栏程序员的成长之路

重量级!Maven史上最全教程,看了必懂

如果项目非常庞大,就不适合使用package来划分模块,最好是每一个模块对应一个工程,利于分工协作。

16120
来自专栏Java研发军团

精选些 Java SSM 框架基础面试题

作用:Ioc解决对象之间的依赖问题,把所有Bean的依赖关系通过配置文件或注解关联起来,降低了耦合度。

6030
来自专栏IT那个小笔记

Spring基本使用

他解决的是业务逻辑层和其他各层的松耦合问题,因此它将面向接口的编程思想贯穿整个系统应用。

10820
来自专栏大前端

微信小程序 Utils丨wxParse 实现HTML解析、图文混排

配置文件下载 https://pan.baidu.com/s/1eKZzhWsK3LW0rqrEkNfr0g

15520
来自专栏知了一笑

Spring 框架基础(03):核心思想 IOC 容器总结,案例演示

7720
来自专栏书山有路勤为径

ROS Topic

6730

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励