手机网站 教程,深圳电子网站建设,中企高呈高端网站建设费用,建程网是干嘛的文章目录 1.垂直分表的背景2.垂直分表案例实战2.1.垂直分表规划2.2.配置Mycat实现垂直分表2.3.重启Mycat2.4.在Mycat命令行中导入数据结构2.5.查看由Mycat分表后每个分片上存储的表2.6.Mycat垂直分表后可能遇到的问题2.7.垂直分表完成 1.垂直分表的背景
我们的商城系统数据库目前是单点数据库随着业务量越来越大每日产生的数据量越来越多单台数据库的存储能力和计算能力是有限的为了保证用户的体验度和满意度在数据库性能到达瓶颈之前我们先对数据进行性能优化目前的优化方案是对商城库进行垂直分表扩展数据库节点将不同业务的表存储在多个数据库节点中提高数据库的性能。
垂直分库指的是将一个库中的多个表拆分到多个数据库实例中也就是拆分到了多台不同的数据库服务器上缓解了单台数据库所承担的压力。 2.垂直分表案例实战
2.1.垂直分表规划
为了保证数据库的高可用性和读写分离我们在前面准备了2套双主双从的集群我们按照双主双从集群进行划分每一套双主双从充当一个数据节点也就是分片将商品库的这些表分别存放在不同的节点分片中从而达到分表的目的。
如下图所示商品库的所有表的划分情况如下
将tb_goods_base、tb_goods_brand、tb_goods_cat、tb_goods_desc、tb_goods_item、tb_user、tb_user_address这7张表划分到分片1这个双主双从集群中。将tb_order_item、tb_order_master、tb_order_pay_log、tb_areas_city、tb_areas_region、tb_areas_provinces这6张表划分到分片2这个双主双从集群中。 利用Mycat实现垂直分表的思路
首先声明一个schema定义逻辑库逻辑库就是这个商品库。然后定义逻辑表table根据不同的表划分到不同的数据节点dataNode上。然后定义dataNode关联数据库节点中真实的数据库。最后定义dataHost也就是分片一共定义2组分片分别指向各自的双主双从复制集群。
此架构实现后我们的商品库就从单点架构升级达到了双主双从高可用读写分离的架构并且也从逻辑上进行了分库分表操作提供数据库性能。
2.2.配置Mycat实现垂直分表
1在Schema配置文件中配置垂直分表 关于配置参数有几点需要说明一下 如果在schema标签中指定了dataNode数据节点那么该逻辑库下所有的表都会被存储到指定数据节点的数据库实例上。如果单独在table逻辑表标签中定义了dataNode数据节点那么该数据节点的优先级将大于schema中的数据节点会根据逻辑表标签中的定义的dataNode将该表存放在指定的数据库实例中。由于我们的分片一共有两个因此我们要定义两个dataNode数据节点一个数据节点相当于是一个分片然后将指定的表、库按照需求划分到对应的分片上。定义好分片后就需要去定义dataHost数据主机了一个分片对应一个dataHost因此我们需要定义两组dataHost第一套双主双从集群分片主机为mysqlcluster-1第二套双主双从集群分片主机为mysqlcluster-2。然后在dataHost中去指定数据存储的具体数据库实例也就是我们的双主双从集群双主双从集群也是两套主从复制集群我们配置成两组writeHost双主双从集群只有一个主库承担写操作另一个主库充当备用主库当主库故障后备用主库直接切换成主库形成高可用集群双从全部承担读操作。 Schema配置文件的逻辑调用关系如下 schema中包含table定义要对那些库和表进行操作逻辑库和逻辑表都会关联数据节点dataNode在数据节点中关联数据主机dataHost和真实数据库名称在dataHost中定义数据库实例信息。 一个逻辑库或者逻辑表的分库分表操作是根据关联数据节点确定要将库、表分在哪个数据库节点上然后关联的数据节点找到具体的数据主机最后库、表就分在了关联的数据主机上。
[rootmysql-1 ~]# vim /data/mycat/conf/schema.xml
?xml version1.0?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycathttp://io.mycat/!--定义逻辑库 库名叫做db_shopping 该逻辑库关联dn1这个数据节点-- schema namedb_shopping checkSQLschemafalse sqlMaxLimit100 dataNodedn1!--定义逻辑表 将部分表划分到dn1这个分片--table nametb_goods_base dataNodedn1 primaryKeyid/table nametb_goods_brand dataNodedn1/table nametb_goods_cat dataNodedn1/table nametb_goods_desc dataNodedn1/table nametb_goods_item dataNodedn1/table nametb_user dataNodedn1/table nametb_user_address dataNodedn1/!--定义逻辑表 将部分表划分到dn2这个分片--table nametb_order_item dataNodedn2/table nametb_order_master dataNodedn2/table nametb_order_pay_log dataNodedn2/table nametb_areas_city dataNodedn2/table nametb_areas_region dataNodedn2/table nametb_areas_provinces dataNodedn2//schema !--定义数据节点 也就是分片 一个分片会关联一个数据主机组 然后对应真实的数据库名称--dataNode namedn1 dataHostmysqlcluster-1 database db_shopping / dataNode namedn2 dataHostmysqlcluster-2 database db_shopping / !--定义数据主机 在这个标签下定义具体的读写操作路由的数据库实例地址 schema、table划分如何指定的是该数据主机关联的数据节点 那么对应的库、表都会被存储在数据主机定义的数据库实例中--dataHost namemysqlcluster-1 maxCon1000 minCon10 balance1 writeType0 dbTypemysql dbDrivernative switchType1 heartbeatselect user()/heartbeat !--定义写操作路由的数据库实例--writeHost hostc1-1-master3306 url192.168.20.11:3306 userroot password123456!--定义读操作路由的数据库实例--readHost hostc1-1-slave3308 url192.168.20.11:3308 userroot password123456 //writeHost !--备用的主库 也是提供写操作的数据库当主库c1-1-master3306故障后 备用库开始提供写操作--writeHost hostc1-2-master3306 url192.168.20.12:3306 userroot password123456!--备用主库的从库 从始至终 只要备用主库不故障 会一直提供读服务--readHost hostc1-2-slave3308 url192.168.20.12:3308 userroot password123456 //writeHost /dataHost dataHost namemysqlcluster-2 maxCon1000 minCon10 balance1 writeType0 dbTypemysql dbDrivernative switchType1 heartbeatselect user()/heartbeat writeHost hostc2-1-master3307 url192.168.20.11:3307 userroot password123456readHost hostc2-1-slave3309 url192.168.20.11:3309 userroot password123456 //writeHost !--备用主库db3 主库db1故障后 开始提供写操作--writeHost hostc2-2-master3307 url192.168.20.12:3307 userroot password123456!--备用主库的从库 从始至终 只要备用主库不故障 会一直提供读服务--readHost hostc2-2-slave3309 url192.168.20.12:3309 userroot password123456 //writeHost /dataHost /mycat:schema2在Server配置文件中定义通过Mycat连接后允许访问的逻辑库
[rootmysql-1 ~]# vim /data/mycat/conf/server.xml
······user nameroot defaultAccounttrue!--登录用户的密码--property namepassword123456/property!--该用户登录后可以显示那些Schema--property nameschemasTESTDB/property/user
······2.3.重启Mycat
垂直分表策略规则配置完成后下面就可以重启Mycat了。
[rootmysql-1 ~]# mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...2.4.在Mycat命令行中导入数据结构
Mycat已经配置完垂直分表了当有数据要写入到我们分的表时对应的数据库实例上要保证这个库、表都存在否则用户的数据将写入失败无论是生产环境还是测试环境使用Mycat分库分库后需要先在分片节点上将数据库创建出来然后再通过Mycat将不同的表划分存储到不同的分片节点上。
1在两个分片节点中分别创建出db_shopping数据库
分片节点是双主双从集群只需要连接上双主双从的任意一个主库上创建数据库即可会同步到所有的从库、主库上。
#分片1的主库操作
[rootmysql-1 sql]# mysql -uroot -p123456 -P3306 -h 192.168.20.11
mysql create database db_shopping character set utf8mb4;#分片2的主库操作
[rootmysql-1 sql]# mysql -uroot -p123456 -P3307 -h 192.168.20.11
mysql create database db_shopping character set utf8mb4;2登陆Mycat导入商品库的所有表以及数据
数据库需要在每个数据库实例上独立创建出来数据表我们是通过Mycat进行垂直分表的因此无需在每个实例上创建只需要登陆Mycat在Mycat中执行建表语句和插入数据这时Mycat就会根据配置的分表策略将不同的表写入到不同的数据库实例上。
当线上生产库要进行分表时也是按照这种套路在Mycat上执行多个表备份的数据Mycat会自动路由到指定的数据库实例分片上。
[rootmysql-1 sql]# mysql -uroot -p123456 -P8066 -h 192.168.20.11
mysql use db_shopping;
mysql source /root/sql/shopping-table.sql
mysql source /root/sql/shopping-insert.sql2.5.查看由Mycat分表后每个分片上存储的表
我们一共有2个分片每个分片都是双主双从的集群模式我们只需要看每个分片的任意一个主库即可因为从库会同步主库的数据接下来我们去查看每个分片上所存储的商品库的表有那些是否是我们规划的样子。
1分片mysqlcluster-1
查看该分片节点中的任意一个主库即可看到全部信息。
分片1mysqlcluster-1包含了tb_goods_base、tb_goods_brand、tb_goods_cat、tb_goods_desc、tb_goods_item、tb_user、tb_user_address这7张表垂直分表成功。 2分片mysqlcluster-2
查看该分片节点中的任意一个主库即可看到全部信息。
分片2mysqlcluster-2包含了tb_order_item、tb_order_master、tb_order_pay_log、tb_areas_city、tb_areas_region、tb_areas_provinces这6张表垂直分表成功。 2.6.Mycat垂直分表后可能遇到的问题
程序连接数据库都是直接配置Mycat的地址Mycat中的数据库、表都是逻辑性的对于程序而言、开发同事而言他们并不知道Mycat后端对应了那些数据库实例并且当Mycat进行垂直分库、分表后表与表之间可能都不在一个数据库实例上。
这时如果我们有多表联查的操作可能联查的表与表并没有分在同一个数据库实例里此时就会报错了提示Mycat路由找不到对应的表如下图所示 mysql select ua.user_id, ua.contact, p.province, c.city, r.area , ua.address from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r where ua.province_id p.provinceid and ua.city_id c.cityid and ua.town_id r.areaid ;tb_user_address、tb_areas_city这几张表都是在数据库中存在的但是依旧报错说找不到该表回想一下在我们分库分表时并没有将这两张表放在一个分片中此时Mycat就不知道这两张表究竟位于哪一个分片中因此就会报错找不到这张表。 解决办法就是将要进行联查的表在Mycat分表时设置成全局表全局表会在指定的分片节点上创建可以指定多个分片节点并数据也是一样的配置如下:
除了tb_user_address这张表以外联查的其他表都在一个分片里因此我们只对tb_user_address这张表设置全局表即可。 table nametb_user_address dataNodedn1,dn2 typeglobal/虽然指定了全局表即使重启Mycat也不会生效因为涉及到修改数据分布了就需要将数据库实例上的库全部删除然后重新备份还原非常麻烦因此建议一开始分库分表时就将有联查动作的表划分到一个分片节点中避免出错。 配置全局表后重新删库导入表之后在每个分片节点上都会存在全局表全局表很鸡肋后期设置需要重新刷Mycat配置很麻烦不建议使用前期尽可能规划好。 经过一系列删库还原生效全局表后联查成功。 2.7.垂直分表完成
此时垂直分表已经完成了说一些实战性的经验。
db_shopping库的部分表分在了分片节点1上部分表分在分片节点2上以后有新表创建时如果没有在Mycat中为新表单独进行配置默认会被分片到逻辑库关联的分片节点上。
如果对于新表就要求说存储在分片节点2上那么在创建这张新表时就在Mycat上配置好然后重启Mycat最后在Mycat上创建这张新表字段路由到分片节点2上。
一定要最初就规划好每个分片锁存储的表尽可能不使用全局表。
