本文共 4910 字,大约阅读时间需要 16 分钟。
接到一个项目,pm号称这个项目用户是千万级别,从来没接触过这么大用户,心里狂颤抖,借次机会玩一把分库分表,一直都是听人说,就是没有用过。
简单易用的组件:
当当sharding-jdbc
蘑菇街TSharding 强悍重量级的中间件:sharding
TDDL Smart Client的方式(淘宝) Atlas(Qihoo 360) alibaba.cobar(是阿里巴巴(B2B)部门开发) MyCAT(基于阿里开源的Cobar产品而研发) Oceanus(58同城数据库中间件) OneProxy(支付宝首席架构师楼方鑫开发) vitess(谷歌开发的数据库中间件) https://github.com/searchq=%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8 对比了一下,最后选择了 sharding-jdbc :水平和垂直分库,垂直分库一般不会台纠结,根据服务域都做相应的分库处理。而水平分库分表常用策略:
项目用到是spring boot 2.2 mybatis-plus, 数据源用HikariDataSource
org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.1.0
网上看到3.0 和4.0 配置文件很多都不一样,踩了很多坑,我们配置了一个默认数据源,读写分离,两个一主一从,贴出全部配置,
spring: shardingsphere: datasource: names: mds0,sds0,mds1,sds1,mdsm # 数据源ds0 mdsm: type: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://:3306/qinqing_dev?characterEncoding=utf8&useSSL=true&autoReconnect=true&serverTimezone=Asia/Shanghai username: root password: minimumIdle: 5 maximumPoolSize: 30 maximumWait: 10000 isAutoCommit: true idleTimeout: 30000 poolName: hikari-db0 maxLifetime: 900000 connectionTimeout: 15000 connectionTestQuery: SELECT 1 mds0: type: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql:/:3306/test?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true username: spring password: minimumIdle: 5 maximumPoolSize: 30 maximumWait: 10000 isAutoCommit: true idleTimeout: 30000 poolName: hikari-db0 maxLifetime: 900000 connectionTimeout: 15000 connectionTestQuery: SELECT 1 # 数据源ds1 sds0: type: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://:3306/test?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true username: spring password: minimumIdle: 5 maximumPoolSize: 30 maximumWait: 10000 isAutoCommit: true idleTimeout: 30000 poolName: hikari-db0 maxLifetime: 900000 connectionTimeout: 15000 connectionTestQuery: SELECT 1 mds1: type: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://:3306/test1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true username: spring password: minimumIdle: 5 maximumPoolSize: 30 maximumWait: 10000 isAutoCommit: true idleTimeout: 30000 poolName: hikari-db0 maxLifetime: 900000 connectionTimeout: 15000 connectionTestQuery: SELECT 1 # 数据源ds1 sds1: type: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://:3306/test1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true username: spring password: minimumIdle: 5 maximumPoolSize: 30 maximumWait: 10000 isAutoCommit: true idleTimeout: 30000 poolName: hikari-db0 maxLifetime: 900000 connectionTimeout: 15000 connectionTestQuery: SELECT 1 props: sql.show: true sharding: default-data-source-name: mdsm tables: dnstime: #t_user表 key-generator-column-name: id #主键 actual-data-nodes: mds − > 0..1. d n s t i m e ->{0..1}.dnstime −>0..1.dnstime->{0…1} #数据节点,均匀分布 database-strategy: #分库策略 inline: #行表达式 sharding-column: id #列名称,多个列以逗号分隔 algorithm-expression: mdsKaTeX parse error: Expected '}', got '#' at position 50: …ble-strategy: #̲分表策略 …->{id % 2} # t_address: # key-generator-column-name: id # actual-data-nodes: dsKaTeX parse error: Expected 'EOF', got '#' at position 22: ….t_address #̲ datab…{lit % 2} master-slave-rules: master-data-source-name: mds0: master-data-source-name: mds0 slave-data-source-names: sds0 mds1: master-data-source-name: mds1 slave-data-source-names: sds1完全支持非跨库事务,例如:仅分表,或分库但是路由的结果在单库中。
完全支持因逻辑异常导致的跨库事务。例如:同一事务中,跨两个库更新。更新完毕后,抛出空指针,则两个库的内容都能回滚。
不支持因网络、硬件异常导致的跨库事务。例如:同一事务中,跨两个库更新,更新完毕后、未提交之前,第一个库死机,则只有第二个库数据提交
TransactionInterceptor 中
public Object invoke(MethodInvocation invocation) throws Throwable 方法Connection 已被shardingconnection 接管
最后由ForceExecuteTemplate 提交执行 public void execute(final Collection targets, final ForceExecuteCallback callback) throws SQLException { Collection exceptions = new LinkedList<>(); for (T each : targets) { try { callback.execute(each); } catch (final SQLException ex) { exceptions.add(ex); } } throwSQLExceptionIfNecessary(exceptions); }转载地址:http://ynpob.baihongyu.com/