实战分表查询汇总很慢-解决路程

最新推荐文章于 2024-01-25 15:55:12 发布

yanbing007

最新推荐文章于 2024-01-25 15:55:12 发布

阅读量1.2k

点赞数

分类专栏：分布分表文章标签： mysql

本文链接：https://blog.csdn.net/qq_37869243/article/details/112701136

版权

分布分表专栏收录该内容

4 篇文章 0 订阅

订阅专栏

需求：查询本组织及下级组织付款流水信息，按照支付方式/营业时间/POS机/组织分组，四个条件随机组合汇总

表结构：test_pos_sale销售表 test_pos_sale_pay销售付款明细表，各自都分40个表，都按组织id%40存放

CREATE TABLE `test_pos_sale` (
  `test_pos_sale_id` bigint(18) NOT NULL,
  `bill_no` varchar(100) DEFAULT NULL,
  `org_id` bigint(18) DEFAULT NULL COMMENT '门店ID',
  `sale_no` varchar(100) DEFAULT NULL,
  `biz_date` varchar(10) DEFAULT NULL,
  `detail_count` int(5) DEFAULT NULL,
  `detail_num` decimal(27,8) DEFAULT '0.00000000',
  `should_total` decimal(27,8) DEFAULT '0.00000000',
  `discount_total` decimal(27,8) DEFAULT '0.00000000',
  `actual_total` decimal(27,8) DEFAULT '0.00000000',
  `pay_total` decimal(27,8) DEFAULT '0.00000000',
  PRIMARY KEY (`test_pos_sale_id`)
) ENGINE=InnoDB

CREATE TABLE `test_pos_sale_pay` (
  `test_pos_sale_pay_id` bigint(18) NOT NULL,
  `bill_no` varchar(100) DEFAULT NULL,
  `org_id` bigint(18) DEFAULT NULL COMMENT '门店ID',
  `sale_no` varchar(100) DEFAULT NULL,
  `biz_type` varchar(100) DEFAULT NULL,
  `biz_item` varchar(100) DEFAULT NULL,
  `pay_code` varchar(100) DEFAULT NULL,
  `pay_name` varchar(300) DEFAULT NULL,
  `sttl_dir` varchar(10) DEFAULT NULL,
  `pay_total` decimal(27,8) DEFAULT '0.00000000',
  `actual_total` decimal(27,8) DEFAULT '0.00000000',
  `pay_std_total` decimal(27,8) DEFAULT '0.00000000',
  `is_local` varchar(1) DEFAULT NULL,
  `pay_no` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`test_pos_sale_pay_id`),
  KEY `BILL_NO` (`bill_no`)
) ENGINE=InnoDB

一开始自己想的增加索引：

1：各40个表索引添加：

ALTER TABLE `test_pos_sale` ADD INDEX `index_orgid` (`org_id`, `sal_bgn_time`) USING BTREE ,ADD INDEX `index_bill_no` (`bill_no`) USING BTREE ;
ALTER TABLE `test_pos_sale_pay` ADD INDEX `index_orgid` (`org_id`) USING BTREE ,ADD INDEX `index_bill_no` (`bill_no`) USING BTREE  ;

2：获取本组织及下级组织的orgIdList，然后执行下面的sql查询，groupSql是 GROUP BY psp.pay_code,ps.pos_no,psp.org_id,ps.sal_bgn_time 四个属性随机组合分组

SELECT
<if test="isOrg != null and isOrg=='1'.toString()">
	psp.org_id orgId,
	ps.org_code orgCode,
</if>
<if test="isRptDate != null and isRptDate =='1'.toString()">
	ps.sal_bgn_time as salBgnTime,
</if>
<if test="isPayCode != null and isPayCode =='1'.toString()">
	psp.pay_code as payCode,
</if>
<if test="isPosNo != null and isPosNo =='1'.toString()">
	ps.pos_no as posNo,
</if>
<choose>
	<when test=" isOrg =='1'.toString() or isRptDate =='1'.toString() or isPayCode =='1'.toString() or  isPosNo =='1'.toString() ">
		SUM(psp.actual_total)   as actualTotal
	</when>
	<otherwise>
		psp.org_id orgId,
		ps.org_code orgCode,
		ps.sal_bgn_time salBgnTime,
		psp.pay_code payCode,
		ps.pos_no as posNo,
		ps.csh_id cshId,
		ps.csh_code cshCode,
		psp.sale_no saleNo,
		psp.pay_name payName,
		psp.pay_no payNo,
		psp.actual_total
		actualTotal,
		ps.acc_date accDate
	</otherwise>
</choose>
FROM
test_pos_sale_pay psp left join test_pos_sale ps on	psp.bill_no = ps.bill_no
WHERE 1=1
<if test="startDate != null and startDate != ''">
	AND ps.sal_bgn_time &gt;= #{startDate}
</if>
<if test="endDate != null and endDate != ''">
	and ps.sal_bgn_time &lt;= #{endDate}
</if>
<if test="orgIdList != null and orgIdList.size > 0">
	AND psp.org_id IN 
	<foreach collection="orgIdList" open="(" close=")" item="orgId" separator=",">
	   ${orgId}
	</foreach>
</if>
<if test="cshCode != null and cshCode != ''">
	and ps.csh_code = #{cshCode}
</if>
<if test="posNo != null and posNo != ''">
	and ps.pos_no like '%${posNo}%'
</if>
<if test="payCode != null and payCode != ''">
	and psp.pay_code = #{payCode}
</if>
<if test="billNo != null and billNo != ''">
	and psp.bill_no  like '%${billNo}%'
</if>
<if test="groupSql != null and groupSql != ''">
	GROUP BY ${groupSql}
</if>
order by ps.sal_bgn_time desc

目前数据量不大查询时间都很慢，查询一天数据都要 134 秒；

日志：

<=============================【/posSale/findPosSalePayReport】【START】【POST】=============================>
[2021-01-07 11:53:59.684 10.2.31.114 hirop-service-sale DEBUG [0dbc78ca2c5dca54,0dbc78ca2c5dca54,]| http-nio-0.0.0.0-9082-exec-3| com.hisense.rop.sale.mapper.PosSalePayMapper.findPosSalePayReport_COUNT| debug| ==>  Preparing: SELECT count(0) FROM test_pos_sale_pay psp LEFT JOIN test_pos_sale ps ON psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (1, 10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118, 10001813, 10001816, 10001819, 10001831, 10001833, 10001821, 10001898, 10001824, 10001825, 10001834, 10001835, 10001865, 10001846, 10001847, 10001860, 10001861, 10001866, 10001867, 10001868, 10001899, 10001877, 10001878, 10001882, 10001883, 10001884, 10001885, 10001887, 10001888, 10001890, 10001891, 10001889, 10001892, 10001893, 10001903) 
[2021-01-07 11:53:59.685 10.2.31.114 hirop-service-sale DEBUG [0dbc78ca2c5dca54,0dbc78ca2c5dca54,]| http-nio-0.0.0.0-9082-exec-3| com.hisense.rop.sale.mapper.PosSalePayMapper.findPosSalePayReport_COUNT| debug| ==> Parameters: 2021-01-07 00:00:00(String), 2021-01-07 23:59:59(String)
[2021-01-07 11:54:25.713 10.2.31.114 hirop-service-sale INFO  [,,]| AsyncResolver-bootstrap-executor-0| com.netflix.discovery.shared.resolver.aws.ConfigClusterResolver| getClusterEndpoints| Resolving eureka endpoints via configuration
[2021-01-07 11:55:01.901 10.2.31.114 hirop-service-sale DEBUG [5d03d59028001351,5d03d59028001351,]| http-nio-0.0.0.0-9082-exec-8| com.hisense.rop.sale.mapper.PosSalePayMapper.findPosSalePayReport| debug| <==      Total: 6
[2021-01-07 11:55:01.910 10.2.31.114 hirop-service-sale INFO  [5d03d59028001351,5d03d59028001351,]| http-nio-0.0.0.0-9082-exec-8| com.hisense.rop.sale.service.impl.PosSalePayServiceImpl| findPosSalePayReport| 销售付款流水查询耗费时间： 134 秒
[2021-01-07 11:55:01.913 10.2.31.114 hirop-service-sale INFO  [5d03d59028001351,5d03d59028001351,]| http-nio-0.0.0.0-9082-exec-8| com.hisense.rop.sale.filter.VfreshXSSFilter| doFilter| <=============================【/posSale/findPosSalePayReport】【END】=============================>

目前我的想法是：orgIdList循环传单个orgId，sql改： AND psp.org_id =#{orgId}，后使用Java代码处理多表查询的数据进行条件分组

后续思考，这种方式不可行，sharding-jdbc应该有相应的解决方案，去sharding-jdbc官方查资料。

后续解决办法：

sharding-jdbc缺少绑定表的关系配置，导致笛卡尔积问题出现，
如下笛卡尔积日志：

[2021-01-07 14:20:48.535 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_21 ps LEFT JOIN test_pos_sale_pay_19 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.537 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_21 ps LEFT JOIN test_pos_sale_pay_36 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.541 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_21 ps LEFT JOIN test_pos_sale_pay_35 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.543 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_21 ps LEFT JOIN test_pos_sale_pay_16 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.546 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_21 ps LEFT JOIN test_pos_sale_pay_27 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.549 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_21 ps LEFT JOIN test_pos_sale_pay_38 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.551 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_21 ps LEFT JOIN test_pos_sale_pay_15 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.553 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_6 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.555 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_8 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.559 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_10 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.561 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_20 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.563 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_12 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.565 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_23 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.567 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_11 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.569 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_22 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.571 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_0 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.573 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_30 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.576 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_1 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.578 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_18 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.581 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_17 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.583 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_28 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.585 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_39 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.587 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_19 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.590 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_36 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.593 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_35 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.595 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_16 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.597 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_27 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.599 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_38 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:48.601 10.2.31.114 hirop-service-sale INFO  [6065532873b76345,6065532873b76345,]| http-nio-0.0.0.0-9082-exec-5| Sharding-Sphere-SQL| log| Actual SQL: ds0 ::: SELECT count(0) FROM test_pos_sale_20 ps LEFT JOIN test_pos_sale_pay_15 psp ON psp.org_id = ps.org_id AND psp.bill_no = ps.bill_no WHERE 1 = 1 AND ps.sal_bgn_time >= ? AND ps.sal_bgn_time <= ? AND psp.org_id IN (10001812, 10001440, 10001823, 10001818, 10001820, 10001822, 10001827, 10001828, 10001875, 10001830, 10002119, 10001848, 10001850, 10001851, 10001852, 10001855, 10001857, 10001858, 10001859, 10001863, 100018856, 10001876, 10001879, 10001881, 10001886, 10001896, 10001897, 10001900, 10001902, 10001817, 10001836, 10002118) ::: [[2021-01-07 00:00:00, 2021-01-07 23:59:59]]
[2021-01-07 14:20:59.738 10.2.31.114 hirop-service-sale INFO  [84654bb29df94c2c,84654bb29df94c2c,]| http-nio-0.0.0.0-9082-exec-6| Sharding-Sphere-SQL| log| Rule Type: sharding

添加binding-tables配置：
这一步很重要，避免迪尔卡的出现

binding-tables[0]: test_pos_sale,test_pos_sale_pay
binding-tables[1]: test_pos_sale,test_pos_sale_plu_clerk
binding-tables[2]: test_pos_sale,test_pos_sale_plu
binding-tables[3]: test_pos_sale,test_pos_sale_plu_dsc

或者 
binding-tables: test_pos_sale,test_pos_sale_pay，test_pos_sale_plu_clerk，test_pos_sale_plu，test_pos_sale_plu_dsc

全部绑定

官方绑定表文档
指分⽚规则⼀致的主表和⼦表。例如：t_order 表和t_order_item 表，均按照order_id 分⽚，则
此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将⼤⼤提
升。举例说明，如果SQL 为：

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.
 order_id in (10, 11);

数据分
在不配置绑定表关系时，假设分⽚键order_id 将数值10 路由⾄第0 ⽚，将数值11 路由⾄第1 ⽚，那
么路由后的SQL 应该为4 条，它们呈现为笛卡尔积：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.
order_id in (10, 11);
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.
order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.
order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.
order_id in (10, 11);

在配置绑定表关系后，路由的SQL 应该为2 条：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.
order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.
order_id in (10, 11);

其中t_order 在FROM 的最左侧，ShardingSphere 将会以它作为整个绑定表的主表。所有路由计算将
会只使⽤主表的策略，那么t_order_item 表的分⽚计算将会使⽤t_order 的条件。故绑定表之间的
分区键要完全相同。

之前没开启sharding-jdbc日志打印：
打开sql输出日志:spring.shardingsphere.props.sql.show=true
sql修改：主表放在左边，小表作为驱动表，,之前的org_id关联sql缺少

from	test_pos_sale ps  left join test_pos_sale_pay psp  on psp.org_id = ps.org_id and  psp.bill_no = ps.bill_no

后续还需要改进：不适用框架的分页，手动分页处理

解析：sql的执行顺序 ,from on join where group by having select order by limit
limit是最后执行的，如果我们要查询第100万页开始的10条数据，mysql的执行器会先找出10000010条数据，然后依次去找最后10条，非常浪费磁盘读取和内存容量
具体可以看下相关文章：https://www.cnblogs.com/wyq178/p/11576065.html

yanbing007

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
实战分表查询汇总很慢-解决路程

需求：查询本组织及下级组织付款流水信息，按照支付方式/营业时间/POS机/组织分组，四个条件随机组合汇总表结构：test_pos_sale销售表 test_pos_sale_pay销售付款明细表，各自都分40个表，都按组织id%40存放CREATE TABLE `test_pos_sale` ( `test_pos_sale_id` bigint(18) NOT NULL, `bill_no` varchar(100) DEFAULT NULL, `org_id` bigint(18)
复制链接

扫一扫