分组统计分页总数错误问题

Apache ShardingSphere
#1

ss版本：5.1.1

ss配置

spring:
  shardingsphere:
    props:
      sql:
        show: true
#    schema:
#      name: sharding_db
#    mode:
#      type: Cluster
#      repository:
#        type: ZooKeeper
#        props:
#          namespace: governance_ds
#          server-lists: localhost:2181
#          retryIntervalMilliseconds: 500
#          timeToLiveSeconds: 60
#          maxRetries: 3
#          operationTimeoutMilliseconds: 500
#      overwrite: false
    datasource:
      names: ds_0,ds_1
      ds_0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://gz-cdb-fanm6d3b.sql.tencentcdb.com:57569/demo_ds_0?useUnicode=true&characterEncoding=UTF-8&autoReconnect =true&failOverReadOnly=false&maxReconnects=100&serverTimezone=GMT%2B8&noAccessToProcedureBodies=true
        username: root
        password: 
      ds_1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://gz-cdb-fanm6d3b.sql.tencentcdb.com:57569/demo_ds_1?useUnicode=true&characterEncoding=UTF-8&autoReconnect =true&failOverReadOnly=false&maxReconnects=100&serverTimezone=GMT%2B8&noAccessToProcedureBodies=true
        username: root
        password: 
    rules:
      sharding:
        tables:
          t_order:
            actualDataNodes: ds_$->{0..1}.t_order_$->{0..1}

表结构和数据如下：

CREATE TABLE `t_order` (
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `amount` decimal(11,2) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

库ds_0的数据
INSERT INTO `demo_ds_0`.`t_order_0`(`order_id`, `user_id`, `status`, `create_time`, `amount`) VALUES (2, 2, '1', '2022-06-13 17:21:34', 1.00);
INSERT INTO `demo_ds_0`.`t_order_0`(`order_id`, `user_id`, `status`, `create_time`, `amount`) VALUES (3, 2, '1', '2022-06-18 15:09:57', 1.00);
INSERT INTO `demo_ds_0`.`t_order_1`(`order_id`, `user_id`, `status`, `create_time`, `amount`) VALUES (1, 1, '1', '2022-06-18 15:10:07', 21.00);
INSERT INTO `demo_ds_0`.`t_order_1`(`order_id`, `user_id`, `status`, `create_time`, `amount`) VALUES (4, 1, '1', '2022-06-18 15:10:15', 3.00);

库ds_1的数据
INSERT INTO `demo_ds_1`.`t_order_0`(`order_id`, `user_id`, `status`, `create_time`, `amount`) VALUES (5, 1, '1', '2022-06-18 15:13:26', 4.00);
INSERT INTO `demo_ds_1`.`t_order_1`(`order_id`, `user_id`, `status`, `create_time`, `amount`) VALUES (6, 1, '1', '2022-06-18 15:13:37', 334.00);

分片： ds_$->{0…1}.t_order_$->{0…1}

分组统计分页sql如下：

SELECT SUM(amount) as amount,user_id as userId FROM t_order GROUP BY user_id LIMIT 0,10

总数total是4，但实际条数是2。总数计算的原理是两个库分组count之后再相加吗？像这种问题有解决方案吗？

#2

有对应的分片键吗？

#3

线上都是按时间分片

#4

@tuichenchuxin 有一个办法是可以解决这个问题，就是按分组字段进行分片 但是这样数据分布不均匀

#5

应该不会出现这样的错误才对，我不知道你具体的分片键，可以使用

preview SELECT SUM(amount) as amount,user_id as userId FROM t_order GROUP BY user_id LIMIT 0,10

语法看一下路由是否正确

京ICP备2021015875号