数据分库分表配置案例

一. 配置模板

1. 配置分库分表模板

  1. # 配置对外数据库名
  2. schemaName: big_db
  4. # 配置数据源
  5. dataSources:
  6.   # ds0 源
  7.   ds0:
  8.     url: jdbc:mysql://localhost:3315/ds0
  9.     username: root
  10.     password:
  11.     autoCommit: true
  12.     connectionTimeout: 30000
  13.     idleTimeout: 60000
  14.     maxLifetime: 1800000
  15.     maximumPoolSize: 65
  17.   # ds1 源
  18.   ds1:
  19.     url: jdbc:mysql://localhost:3306/ds1
  20.     username: root
  21.     password:
  22.     autoCommit: true
  23.     connectionTimeout: 30000
  24.     idleTimeout: 60000
  25.     maxLifetime: 1800000
  26.     maximumPoolSize: 65
  28. # 配置分表规则
  29. shardingRule:
  31.   # 配置数据表
  32.   tables:
  33.     # 配置 t_order 表
  34.     t_order:
  35.       # 配置数据源规则
  36.       actualDataNodes: ds${0..1}.t_order${0..1}
  37.       # 配置表分表规则
  38.       tableStrategy:
  39.         # 分表算法
  40.         inline:
  41.           # 分表字段
  42.           shardingColumn: order_id
  43.           # 分表定位到 mysql 实体表的路由规则
  44.           algorithmExpression: t_order${order_id % 2}
  45.       # 分布式 id 生成字段
  46.       keyGeneratorColumnName: order_id
  48.     # 配置 t_order_item 表
  49.     t_order_item:
  50.       actualDataNodes: ds${0..1}.t_order_item${0..1}
  51.       tableStrategy:
  52.         inline:
  53.           shardingColumn: order_id
  54.           algorithmExpression: t_order_item${order_id % 2}
  56.   # 注册到代理的数据表, 多个逗号分隔
  57.   bindingTables:
  58.     - t_order,t_order_item
  60.   # 默认分库策略
  61.   defaultDatabaseStrategy:
  62.     # 分库算法
  63.     inline:
  64.       # 分库字段
  65.       shardingColumn: user_id
  66.       # 分库规则
  67.       algorithmExpression: ds${user_id % 2}
  69.   # 默认分表策略
  70.   defaultTableStrategy:
  71.     none:
  73.   # 分布式 id 生成算法
  74.   defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator

2. 配套 SQL

  1. use big_db;
  3. show tables;
  5. DROP TABLE big_db.t_order;
  7. CREATE TABLE IF NOT EXISTS big_db.t_order (
  8.   id BIGINT NOT NULL AUTO_INCREMENT,
  9.   user_id INT NOT NULL,
  10.   order_id INT NOT NULL,
  11.   info VARCHAR(50),
  12.   PRIMARY KEY (id))
  13. ;
  15. INSERT INTO `t_order` (`user_id`,`order_id`, `info`) VALUES (2, 3, 'info');
  17. SELECT COUNT(*) FROM t_order;

二. 配置案例

1. 配置分库分表

  • 配置 conf/config-sharding_db.yaml 文件
  1. schemaName: big_db
  3. dataSources:
  4.   ds_app1:
  5.     url: jdbc:mysql://app1:3306/temp_db?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  6.     username: root
  7.     password:
  8.     autoCommit: true
  9.     connectionTimeout: 30000
  10.     idleTimeout: 60000
  11.     maxLifetime: 1800000
  12.     maximumPoolSize: 50
  14.   ds_app2:
  15.     url: jdbc:mysql://app2:3315/temp_db?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  16.     username: root
  17.     password:
  18.     autoCommit: true
  19.     connectionTimeout: 30000
  20.     idleTimeout: 60000
  21.     maxLifetime: 1800000
  22.     maximumPoolSize: 50
  24. shardingRule:
  25.   tables:
  27.     # 配置表 t_big_table
  28.     t_big_table:
  29.       actualDataNodes: ds_${['app2', 'app1']}.t_big_table_${['default']}
  30.       # 分表规则
  31.       tableStrategy:
  32.         # 分表算法
  33.         inline:
  34.           # 分表字段
  35.           shardingColumn: tb_partition_key
  36.           # 分表
  37.           algorithmExpression: t_big_table_${tb_partition_key}
  38.       keyGeneratorColumnName: id
  40.   # 注册表
  41.   bindingTables:
  42.     - t_big_table
  44.   # 默认连接数据库
  45.   defaultDataSourceName: ds_app2
  47.   # 默认分库策略
  48.   defaultDatabaseStrategy:
  49.     # 分库算法
  50.     inline:
  51.       # 分库字段
  52.       shardingColumn: db_partition_key
  53.       # 分库路由规则
  54.       algorithmExpression: ds_${db_partition_key}
  56.   # 默认分表策略
  57.   defaultTableStrategy:
  58.     none:
  60.   defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator

2. 配套 SQL

  1. use big_db;
  3. show tables;
  5. -- 删除 SQL
  6. DROP TABLE big_db.t_big_table;
  8. -- 创建数据表
  9. CREATE TABLE IF NOT EXISTS big_db.t_big_table (
  10.   id BIGINT NOT NULL AUTO_INCREMENT,
  11.   info varchar(100) NOT NULL DEFAULT 'info' COMMENT 'info',
  12.   db_partition_key varchar(100) NOT NULL DEFAULT 'app2' COMMENT 'database 分区',
  13.   tb_partition_key varchar(100) NOT NULL DEFAULT 'default' COMMENT 'table 分区',
  14.   PRIMARY KEY (id))
  15. ;
  17. -- 插入数据, 会写入到 数据库(app1) 表 t_big_table_(default) 中
  18. INSERT INTO `t_big_table` (`info`, `db_partition_key`, `tb_partition_key`) VALUES ('info', 'app1', 'default');
  20. -- 插入数据, 会写入到 数据库(app2) 表 t_big_table_(default) 中
  21. INSERT INTO `t_big_table` (`info`, `db_partition_key`, `tb_partition_key`) VALUES ('info', 'app2', 'default');
  23. -- 插入数据, 会写入到 数据库(app2) 表 t_big_table_(20181211) 中, 如果表不存在需要创建
  24. INSERT INTO `t_big_table` (`info`, `db_partition_key`, `tb_partition_key`) VALUES ('info', 'app2', '20181211');
  26. -- 查询总数据, 会有个 BUG, 需要提前把所有规则填写在 actualDataNodes 配置中, 才能读到数据
  27. SELECT COUNT(*) FROM big_db.t_big_table;
  29. -- 查询指定数据库, 指定数据表数据
  30. SELECT COUNT(*) FROM big_db.t_big_table WHERE db_partition_key='app2' AND tb_partition_key = '20181211';