登录
  • 欢迎访问悠扬的技术博客,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站😉

分库分表各组件使用说明

Mysql 悠扬 786次浏览 已收录 0个评论

分库分表各组件使用说明

组件服务

分库分表组件矩阵
项目名称 地址 说明
分片辅助管理工具程序(分片接口测试) http://172.168.9.130:18099/index 功能两用,辅助建表,测试接口,需要修改数据源
haproxy监控程序 http://172.168.9.133:18082/admin sharding代理管理界面
MeterSphere http://172.168.9.130:8081/#/setting/personsetting 在线接口测试
Skyworking应用监控 http://172.168.9.135:18189/ 分片应用监控路由跟踪

准备工作

         抛开组件服务,首先要明确当前库表划分规则,目前库支持按照年限拆分,表支持按月、日、小时进行拆分,本次使用测试单库实现33张表按小时拆分单库表数量达33万,测试过程中发现几个严重干扰使用的问题:

  1. 表数量过大,sharding分片加载时无法及时加载所有表空间,使用工具连接HA代理服务时,表空间加载时间过长
  2. 33万表在mysql自己客户端,或者直接使用工具连接服务时发现不能进行表名检索相关操作,会卡死服务,工具失去响应
  3. 重启mysql服务时,会出现启动超时,导致mysql服务无法及时启动,需要进行多次重启,服务才会正常拉起

        基于上述原因,建议本次划分单库表数量不超过1万,单表数据量不超过1000万,需要进行详细划分。

一、分库分表辅助工具

1.1 配置说明

     该组件包含两个主要功能,按照配置规则进行库初始化、进行接口调用测试,两功能不能同时使用

1.1.1 配置启动分库分表辅助库初始化

   准备好要进行初始化的数据库

分库分表各组件使用说明

     上服务器修改数据源配置,服务器地址看组件服务说明

vi /home/logstash-rabbit/config/application-druid.yml

        修改数据源配置,修改slave对应节点数据源配置,指向对应mysql实例库,进行表初始化。
        修改组件slave配置执行对应要初始化的库,只能一个一个库进行初始化,组件服务目前小时分片单表创建平均在25-30分钟,按天分片在5-10分钟内,具体执行时间跟表字段数量有关。

分库分表各组件使用说明

         重启服务进入后台进行操作:

sh /home/logstash-rabbit/stop_service.sh
sh /home/logstash-rabbit/start_service.sh

分库分表各组件使用说明

分库分表各组件使用说明

分库分表各组件使用说明

分库分表各组件使用说明

1.1.2 配置进行接口测试

 修改slave数据源配置指向对应HA代理地址

这个不详细说明了,需要用的时候把数据源改了,接口服务点击测试就行

分库分表各组件使用说明

二、 haproxy监控程序

     关注服务在线就行,配置相关说明博客里面shardingsphere下面有

分库分表各组件使用说明

三、MeterSphere

     不进行详细说明,这个在分库分表接入时用不到

四、Skyworking应用监控

这玩意太耗资源,尤其是在我们读写频繁推送过多的时候经常卡死服务,不过还是挺好用的,快速排错,后期考虑看看接入GPE,目前来说,一般不需要使用,排错时可以启动

分库分表各组件使用说明

分库分表各组件使用说明

分库分表各组件使用说明

分库分表各组件使用说明

五、sharding服务表路由策略配置

     这里不单独写了,点击查看

5.1 分片规则配置

5.1.1 精确分片

id

#库分片id雪花算法反推时间
org.apache.shardingsphere.sharding.rules.precise.id.NxhzShardingDataBaseIdYearPreciseAlgorithm 
#表分片id雪花算法反推时间
#id 按天分片
org.apache.shardingsphere.sharding.rules.precise.id.NxhzShardingTableIdDayPreciseAlgorithm
#id 按小时分片
org.apache.shardingsphere.sharding.rules.precise.id.NxhzShardingTableIdHourPreciseAlgorithm
#id 按月分片
org.apache.shardingsphere.sharding.rules.precise.id.NxhzShardingTableIdMonthPreciseAlgorithm

date

#时间字段按年分库
org.apache.shardingsphere.sharding.rules.precise.date.NxhzShardingDataBaseYearPreciseAlgorithm
#时间字段按日分表
org.apache.shardingsphere.sharding.rules.precise.date.NxhzShardingTableDayPreciseAlgorithm
#时间字段按小时分表
org.apache.shardingsphere.sharding.rules.precise.date.NxhzShardingTableHourPreciseAlgorithm
#时间字段按月分表
org.apache.shardingsphere.sharding.rules.precise.date.NxhzShardingTableMonthPreciseAlgorithm

5.1.2 范围分片

id

#库分片id雪花算法反推时间 取时间范围
org.apache.shardingsphere.sharding.rules.rang.id.NxhzShardingDataBaseIdYearRangAlgorithm
#表分片id雪花算法反推时间 取时间范围
#id 按天分片范围取值
org.apache.shardingsphere.sharding.rules.rang.id.NxhzShardingTableIdDayRangAlgorithm
#id 按小时分片范围取值
org.apache.shardingsphere.sharding.rules.rang.id.NxhzShardingTableIdHourRangAlgorithm
#id 按月分片范围取值
org.apache.shardingsphere.sharding.rules.rang.id.NxhzShardingTableIdMonthRangAlgorithm

date

#时间字段范围分片
#库时间字段按年分片
org.apache.shardingsphere.sharding.rules.rang.date.NxhzShardingDataBaseYearRangAlgorithm
#表时间字段按日分片
org.apache.shardingsphere.sharding.rules.rang.date.NxhzShardingTableDayRangAlgorithm
#表时间字段按小时分片
org.apache.shardingsphere.sharding.rules.rang.date.NxhzShardingTableHourRangAlgorithm
#表时间字段按月分片
org.apache.shardingsphere.sharding.rules.rang.date.NxhzShardingTableMonthRangAlgorithm

5.1.3 复合分片

id+时间字段 进行复合策略划分,策略同上相同

注意:复合策略使用和 单策略配置使用要求 id与时间字段解析时间误差在分钟内

org.apache.shardingsphere.sharding.rules.complex.NxhzShardingDataBaseYearComplexAlgorithm
org.apache.shardingsphere.sharding.rules.complex.NxhzShardingTableDayComplexAlgorithm
org.apache.shardingsphere.sharding.rules.complex.NxhzShardingTableHourComplexAlgorithm
org.apache.shardingsphere.sharding.rules.complex.NxhzShardingTableMonthComplexAlgorithm

六、分库分表SQL语法支持

查询相关

类型说明 是否支持 备注
select * from table 不支持 不允许在代码中使用
select * from table where id > >= < <= 雪花ID 支持 分片规则说明: 1.按小时分片 规则为时间跨度1小时,可以通过and 进行范围查询 2.按日分片 规则为时间跨度1天,可以通过and进行范围查询 3.按月分片 规则为时间跨度1月,可以通过and进行查询
select max(id) from table select min(id) from table 支持 小时分片规则不推荐使用,分片跨度太大,时间返回超时
select max,min(id) from table where id > >= < <= 雪花ID 支持 有条件,时间跨度规则相同
inner join 、left join 、right join 、join 有限支持 不允许使用,时间跨度太大
SELECT * FROM tbl_name WHERE (col1 = ? or col2 = ?) and col3 = ? 支持 需要优先带分片字段
SELECT * FROM tbl_name WHERE col1 = ? ORDER BY col2 DESC LIMIT ? 支持 需要优先带分片字段
SELECT COUNT(*), SUM(col1), MIN(col1), MAX(col1), AVG(col1) FROM tbl_name WHERE col1 = ? 支持 需要优先带分片字段
SELECT COUNT(col1) FROM tbl_name WHERE col2 = ? GROUP BY col1 ORDER BY col3 DESC LIMIT ?, ? 支持 需要优先带分片字段

插入相关

INSERT INTO tbl_name (col1, col2,…) VALUES (?, ?, ….) 支持
INSERT INTO tbl_name VALUES (?, ?,….) 支持
INSERT INTO tbl_name (col1, col2, …) VALUES (?, ?, ….), (?, ?, ….) 支持

修改相关

UPDATE tbl_name SET col1 = ? WHERE col2 = ? 支持 需要条件分片字段
     

表相关操作

DELETE FROM tbl_name WHERE col1 = ? 需要条件分片字段
CREATE TABLE tbl_name (col1 int, …) 不允许在代码中使用
ALTER TABLE tbl_name ADD col1 varchar(10) 不允许在代码中使用
DROP TABLE tbl_name 不允许在代码中使用
TRUNCATE TABLE tbl_name 不允许在代码中使用
CREATE INDEX idx_name ON tbl_name 不允许在代码中使用
DROP INDEX idx_name ON tbl_name 不允许在代码中使用
DROP INDEX idx_name 不允许在代码中使用

统计相关

SELECT DISTINCT * FROM tbl_name WHERE col1 = ? 需要条件分片字段
SELECT COUNT(DISTINCT col1) FROM tbl_name 需要条件分片字段

DISTINCT支持情况详细说明

支持的SQL(均需跟分片字段作为条件,否则会导致扫全表)

SQL
SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
SELECT DISTINCT col1 FROM tbl_name
SELECT DISTINCT col1, col2, col3 FROM tbl_name
SELECT DISTINCT col1 FROM tbl_name ORDER BY col1
SELECT DISTINCT col1 FROM tbl_name ORDER BY col2
SELECT DISTINCT(col1) FROM tbl_name
SELECT AVG(DISTINCT col1) FROM tbl_name
SELECT SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1
SELECT COUNT(DISTINCT col1 + col2) FROM tbl_name
SELECT COUNT(DISTINCT col1), SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1), col1 FROM tbl_name GROUP BY col1
SELECT col1, COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1

不支持的SQL

SQL 不支持原因
INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …) VALUES语句不支持运算表达式
INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ? INSERT .. SELECT
SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ? HAVING
SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 UNION
SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 UNION ALL
SELECT * FROM ds.tbl_name1 包含schema
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name 详见DISTINCT支持情况详细说明
SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ? 会导致全路由
SQL 不支持原因
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name 同时使用普通聚合函数和DISTINCT聚合函数

版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明分库分表各组件使用说明
喜欢 (1)
支付宝[]
分享 (0)
悠扬
关于作者:
10年以上工作经验,从事2年微服务架构搭建工作,有大数据处理相关工作经验,使用spring全家桶包括:Spring,SpringBoot,SpringCloud 数据层组件服务使用SpringDataJpa,Mybatis以及其他第三方组件Sharding-JDBC,Sharding-Proxy分库分表。熟悉微服务,服务降级,限流,分流,做过项目源码修改,有cat,apollo,nacos使用经验,有Lostash,Elasticsearch,kibana,mysqlMHA生产实践经验,使用开源代码Apache Sarding项目,修改源码支持mysql分库分表使用年月日小时分库分表,docker做集群服务,Jekins做项目发布,GitLab做项目管理,使用docker容器部署,熟悉消息队列RabbitMQ,Kafka,ActiveMQ。RuoYi-Vue-Atomikos项目开源加入生态圈组件,项目支持分布式事务,界面添加多数据源,数据源动态配置,切面切换,多数据源事务支持,支持区域数据源配置,用于区域数据切分,数据层次分库。项目地址:https://gitee.com/zsiyang/ruoyi-vue-atomikos
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址