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

最新推荐文章于 2024-06-19 14:24:44 发布
最新推荐文章于 2024-06-19 14:24:44 发布
阅读量1.3k 收藏
点赞数
分类专栏： 分布分表 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37869243/article/details/112701136
版权

需求：查询本组织及下级组织付款流水信息，按照支付方式/营业时间/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
    评论
专栏目录
JAVA shardingsphere 加载慢 优化
weixin_44810547的博客
11-16 558
那是因为默认扫描1张表.一张一张加载巨慢,添加以下配置增加到20张表同时扫描。启动速度变慢(元数据扫描耗时较长) 占用内存增多(大量单表和元数据对象)如果你数据库最大连接不到20就会报错，请按照数据库最大连接来设置。
解决 ShardingSphere 第一次请求慢的喷血日子
闲狗的博客
05-05 6390
学习起因：整合了 ShardingSphere 的项目第一次访问的时候很慢，第二次访问的时候速度就快了，然后老大让我去一探究竟，然后下面就是探索的血泪史。 通过 Arthas 生成火焰图，定位到慢在ShardingSphere sql 解析，第一次请求使用 ANTLR 解析 SQL 耗时较长，而第二次请求由于缓存原因耗时很小 curl -O https://arthas.aliyun.com/arthas-boot.jar java -jar arthas-boot.jar profiler s.
Springboot 集成 Shardingsphere-JDBC
最新发布
m0_63164811的博客
06-19 1312
Springboot 集成 Shardingsphere-JDBC
Sharding JDBC分页查询
热门推荐
小熙的博客
05-11 4万+
概要：虽然数据分片解决了性能、可用性以及单点备份恢复等问题，但分布式的架构在获得了收益的同时，也引入了新的问题。 Sharding JDBC默认分页 sharding JDBC 默认使用limit进行分页，在不指定路由字段时，分页将会全库全表扫描，然后进行排序。 2020-05-11 14:30:04.525 DEBUG 2956 --- [nio-8080-exec-2] c.e.d.m.UserMapper.selectPageByEnable : ==> Preparing: SEL
项目使用Sharding-Sphere分表测试经验总结
lemon_linaa的博客
07-11 718
背景： 项目运行4年，数据量较大，现平台查询数据较慢，因此决定重构系统，采用Sharding-Sphere进行数据分表，提升查询效率，之前测试的多为单表业务，分表的业务
分表过多引起的问题/Apache ShardingSphere元数据加载慢
瑶山的博客
01-25 2133
当max.connections.size.per.query小于真实sql数量时，走的是连接限制模式（通俗理解：因为连接不够用，需要把sql执行完后，将查询结果先放到内存，然后释放连接用于查询其他sql），反之走的是内存限制模式（连接足够用，每个sql占据一个连接，查询结果不需要一次性放到内存，而是分批次拉取数据，在内存中做归并聚合）。max.connections.size.per.query默认值是1，此时元数据加载是单线程串行加载。而配置大于1时，会根据该配置的值，采用多线程并行加载。
shardingsphere5.0 解决第一次执行sql慢的问题
奥法vx的笔记
02-15 3261
springboot项目集成了shardingsphere5.0项目启动后，第一次执行sql查询特别慢，而之后的查询就响应比较快速。此处用了一个投机取巧的方法解决第一次查询慢的问题，就是项目启动后，执行一个select x 命令 import org.antlr.v4.runtime.tree.ParseTree; import org.apache.shardingsphere.infra.database.type.DatabaseType; import org.apache.shardingsph
JAVA面试题分享二百九十六：分库分表后，查询太慢了，如何优化？
之乎者也·的博客
12-18 1421
但由于每个结果集的记录是有序的，因此Sharding-JDBC每次仅获取各个分片的当前结果集记录，驻留在内存中的记录仅为当前路由到的分片的结果集的当前游标指向而已。因此进行结果归并时，只能从获取的90，80，85和75之中进行归并，那么，无论怎么实现，结果归并之后，都不可能获得正确的结果。第一遍的目标是啥，查出来的最小的 create_time 和最大的 create_time 找出来，然后再去三个表里查询，通过图中所示，想要取得两个表中共同的按照分数排序的第2条和第3条数据，理论上，应该是95和90。
mysql 分表 条件查询,面试过关斩将：分库分表-sharding-jdbc分页，排序，条件查询优化...
weixin_42433412的博客
03-17 1751
之前讲了利用sharding-jdbc 3.1进行分表的情况，也讲了利用一致性hash去做分表的高可用。今天讲下分表后的分页，排序，条件查询优化。其实本身sharding-jdbc是提供了分页功能的，我们方便期间是可以直接用的，但是不推荐用它。亲测翻页深度越深性能消耗越大，慢到让你抓狂。简单介绍下他的分页原理，不做太多讲解：分页和排序都用到了结果归并器：排序举例 我们的sql是 select * ...
sql注入问题
qq_43633916的博客
07-17 271
JDBC:Java DataBase Connectivity 可以为多种关系型数据库DBMS 提供统一的访问方式，用Java来操作数据库 而通过Statement（PreparedStatement）来实现增删改查 sql注入出现的原因： 将客户输入的内容 和 开发人员的SQL语句 混为一体 首先我们了解一下PreparedStatement与Statement在使用时的区别： 1.Sta...
Sharding-JDBC 分库分表-分页查询优化方案
小哇
04-25 1万+
Sharding支持分页查询的数据库？ （1）完全支持MySQL、PostgreSQL和Oracle的分页查询; （2）SQLServer由于分页查询较为复杂，仅部分支持。 Sharding-JDBC分页修正 从多个数据库获取分页数据与单数据库的场景是不同的。 假设每10条数据为一页，取第2页数据。在分片环境下获取LIMIT 10, 10，归并之后再根据排序条件取出前10条数据是不正确的。 举例...
分库分表中间件 Apache shardingsphere
qq_34910843的博客
03-20 373
分库分表中间件 Apache shardingsphere 前一段时间,发现一个查表的数据特别慢,使用id查询都要3s,查了一下数据量1亿4千万,震惊!!!!随着数据量的增加,磁盘IO瓶颈和CPU瓶颈,都会导致数据库连接池可用连接数减少,最后达到峰值,接下来就是崩溃了!没办法了,只能对表进行拆分了. IO瓶颈: ①表字段过多,导致查询的热点数据过多，数据库缓存不够用了,没次都会产生大量io,降低查询速度。解决方案：垂直拆分表,将热点字段与使用频率低的字段分开,查询确定查询项。 ②网络IO，请求数.
解读分库分表中间件Sharding-JDBC
weixin_33918114的博客
12-10 1051
【编者按】数据库分库分表从互联网时代开启至今，一直是热门话题。在NoSQL横行的今天，关系型数据库凭借其稳定、查询灵活、兼容等特性，仍被大多数公司作为首选数据库。因此，合理采用分库分表技术应对海量数据和高并发对数据库的冲击，是各大互联网公司不可避免的问题。 虽然很多公司都致力于开发自己的分库分表中间件，但截止目前，仍无完美的开源解决方案覆盖此领域。 分库分表适用场景 分库分表用于应对当前互联...
难题来了：分库分表后，查询太慢了，如何优化？
浩浩的博客
11-07 745
难题来了：分库分表后，查询太慢了，如何优化？
ShardingSphere官网操作指南补充和重点整理-数据分片-使用规范（六）
astra_zhao的专栏
09-17 2803
使用规范 背景 虽然ShardingSphere希望能够完全兼容所有的SQL以及单机数据库，但分布式为数据库带来了更加复杂的场景。ShardingSphere希望能够优先解决海量数据OLTP的问题，OLAP的相关支持，会一点一点的逐渐完善。 请进一步阅读ShardingSphere所支持和不支持的SQL类型以及针对分页这类性能相关度很高的问题。 SQL 由于SQL语法灵活复杂，分布式数据...
基于ShardingSphere-JDBC的MySQL分库分表
qq_25863973的博客
03-06 2669
ShardingSphere-JDBC分库分表 1、ShardingSphere-JDBC 1.1、官网地址 https://shardingsphere.apache.org/ 1.2、概念 1.2.1、逻辑表 相同结构的水平拆分数据库（表）的逻辑名称，是 SQL 中表的逻辑标识。 例：订单数据根据主键尾数拆分为 5 张表，分别是 t_order_0 到 t_order_4，他们的逻辑表名为 t_order 1.2.2、真实表 在水平拆分的数据库中真实存在的物理表。 即上个示例中的 t_order_0 到
mybatis-plus两表联查带分页带模糊查询所有订单信息
同一片天空
07-23 871
mapper.xml <resultMap id="orderMap" type="com.tenli.entity.Order"> <id column="id" property="id"/> <result column="order_no" property="orderNo"/> <result column="course_id" property="courseId"/>
Spring MySQl分表_分库分表(3) ---SpringBoot + ShardingSphere 实现读写分离
06-03
好的，我可以为您解答关于SpringBoot和ShardingSphere实现读写分离的问题。 首先，您需要在pom.xml文件中添加ShardingSphere相关依赖： ```xml <groupId>org.apache.shardingsphere <artifactId>sharding-jdbc-...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值