一、shardingjdbc概述
shardingjdbc在当前分库分表领域占有很大市场份额。可能和其宣传有关系,其文档突出自己的优点,对自己的缺点写的很少或者很不容易被注意到。所以,我们在使用shardingjdbc做业务的时候要关注他最终生成的sql是什么?是否符合我们的业务需求。这里不是黑shardingjdbc,不是shardingjdbc的问题,这是计算机行业难以跨越的问题。
二、shardingjdbc SQL转义
我们就拿一份按照季度的分库分表场景做演示吧。这里可以看出shardingjdbc取了intmax的分表记录,然后汇聚取top10.可见有多么大的网络开销。所以涉及排序的场景要做好技术测试。
2.1、原始SQL
select station_id,count(station_id) as stationNum from tms_delivery_order_2021
where delivery_start_time between '2021-01-01 01:41:36' and '2021-12-30 01:41:36'
group by station_id order by stationNum desc LIMIT 10
2.2、被转义SQL
#sql1 第一季度
select station_id,count(station_id) as stationNum from tms_delivery_order_2021_q1
where delivery_start_time between '2021-01-01 01:41:36' and '2021-12-30 01:41:36'
group by station_id order by stationNum desc LIMIT 2147483647
#sql2 第二季度
select station_id,count(station_id) as stationNum from tms_delivery_order_2021_q2
where delivery_start_time between '2021-01-01 01:41:36' and '2021-12-30 01:41:36'
group by station_id order by stationNum desc LIMIT 2147483647
#sql3 第三季度
select station_id,count(station_id) as stationNum from tms_delivery_order_2021_q3
where delivery_start_time between '2021-01-01 01:41:36' and '2021-12-30 01:41:36'
group by station_id order by stationNum desc LIMIT 2147483647
#sql1 第四季度
select station_id,count(station_id) as stationNum from tms_delivery_order_2021_q4
where delivery_start_time between '2021-01-01 01:41:36' and '2021-12-30 01:41:36'
group by station_id order by stationNum desc LIMIT 2147483647