目录
显示
组件服务
| 分库分表组件矩阵 | ||
| 项目名称 | 地址 | 说明 |
| 分片辅助管理工具程序(分片接口测试) | 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万，测试过程中发现几个严重干扰使用的问题：
- 表数量过大，sharding分片加载时无法及时加载所有表空间，使用工具连接HA代理服务时，表空间加载时间过长
- 33万表在mysql自己客户端，或者直接使用工具连接服务时发现不能进行表名检索相关操作，会卡死服务，工具失去响应
- 重启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，目前来说，一般不需要使用，排错时可以启动
这里不单独写了，点击查看
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协议进行授权 , 转载请注明分库分表各组件使用说明！
