目前用户按时间将数据进行分片，但是在查询的时候遇到了查询比较慢的情况。分片规则、库表结构、查询语句分别如下：
分片规则：
库表结构：
-- table 日切表 balance_txns_yyyyMMdd，单库每日切分出来新表
CREATE TABLE `balance_txns` (
`balance_txn_id` bigint(20) UNSIGNED NOT NULL COMMENT '主键 Id,雪花算法生成' ,
`created_at` datetime(3) NOT NULL COMMENT '创建时间' ,
`trade_created_at` datetime(3) NOT NULL COMMENT '交易创建时间' ,
`updated_at` datetime(3) NOT NULL COMMENT '更新时间，仅差错处理可更新' ,
`req_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '网关生成的请求 Id' ,
`root_mch_id` bigint(20) UNSIGNED NOT NULL COMMENT '所属的平台商户 Id' ,
`balance_acct_id` bigint(20) UNSIGNED NOT NULL COMMENT '关联的余额账户 Id' ,
`acct_type` tinyint(3) UNSIGNED NOT NULL COMMENT '关联的余额账户类型' ,
`out_order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '关联的商户订单号' ,
`trade_id` bigint(20) NOT NULL COMMENT '关联的订单 Id' ,
`trade_type` tinyint(3) UNSIGNED NOT NULL COMMENT '交易类型' ,
`txn_type` tinyint(3) UNSIGNED NOT NULL COMMENT '明细类型' ,
`currency` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'CNY' COMMENT '币种。默认CNY' ,
`settled_amount` bigint(20) NOT NULL DEFAULT 0 COMMENT '已清算金额，允许为负，单位分' ,
`pending_amount` bigint(20) NOT NULL DEFAULT 0 COMMENT '在途金额，允许为负，单位分' ,
`expensing_amount` bigint(20) NOT NULL DEFAULT 0 COMMENT '消费在途金额，允许为负，单位分' ,
`frozen_pending_amount` bigint(20) NOT NULL DEFAULT 0 COMMENT '在途冻结金额，允许为负，单位分' ,
`frozen_settled_amount` bigint(20) NOT NULL DEFAULT 0 COMMENT '已清算冻结金额，允许为负，单位分' ,
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注' ,
`settled_balance` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '操作后可提现余额' ,
`pending_balance` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '操作后在途余额' ,
`expensing_balance` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '操作后不可用余额' ,
PRIMARY KEY (`balance_txn_id`),
UNIQUE INDEX `uniq_trade_txn_type` (`trade_id`, `txn_type`, `balance_acct_id`) USING BTREE ,
INDEX `idx_created_at` (`created_at`) USING BTREE COMMENT '创建时间索引',
INDEX `idx_trade_created_at_balance_acct_id` (`trade_created_at`,`balance_acct_id`) USING BTREE COMMENT '交易创建时间索引',
INDEX `idx_balance_acct_id_created_at` (`balance_acct_id`, `created_at`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
ROW_FORMAT=DYNAMIC
;
查询SQL：
-- sql 时间范围分页查询
select * from balance_txns where (created_at >= '2022-02-01 00:00:00.000' and created_at<= '2022-02-01 00:00:00.000' and trade_created_at >= '2022-02-01 00:00:00.000' and trade_created_at<= '2022-02-01 00:00:00.000' and balance_acct_id='123435454545433456' and trade_type not in(13, 14)) ORDER BY created_at desc limit 20;
-- 算法：依据分片键trade_created_at 找到对应的数据节点
-- springboot+mybatisplus
如需任何其他信息补充，请回复告知 
