SQL拆分实现与注意事项

SQL拆分的背景

任何系统的设计都是在不断的迭代中改进的,在系统最初的阶段,能够最快的完成功能是首要任务,这就会导致我们在写数据查询时使用了大量的关联查询。而当系统用户慢慢增加到我们不得不分库分表的时候,原先的关联查询就会失效,这就迫使我们不得不将之前的关联查询拆分开,然后利用代码逻辑进行关联操作

如何拆分SQL(left join 拆分)

其实,各种关联操作的拆分思想基本是一样的,只是在不同的情况下,我们可能会忽略掉某些问题而出错。所以我只说了除了第一个left操作。

原SQL:SELECT u.* ,o.time FROM user u LEFT JOIN order o ON o.userId = u.id
拆分步骤:

  1. 先查出user表的结果集:SELECT u.* FROM user
  2. 将user表中与order表关联的字段的值都取出来(这里是user表的主键):userIds
  3. 利用userIds进行 in()函数查回order表的关联字段及其他字段的结果集:SELECT o.time,o.userId FROM order o WHERE o.id IN (userIds)
  4. 在代码中利用关联字段的结果进行数据的left 操作:即给第1步的结果集中加上order表的time字段的值

简化拆分工作的办法是,写一个工具类,根据自身的项目情况,针对性的写出left 、inner、in、groupby、分页、orderby等函数的公共方法

拆分注意事项

1.关联子表有筛选条件的left join,在代码里的 left join操作需要变成inner join操作

原SQL:
SELECT u.* ,o.time FROM user u LEFT JOIN order o ON o.userId = u.id WHERE o.time>‘2019-3-14 AND u.id BETWEEN 100 AND 1000’
拆分伪代码:
List list1 = "SELECT u.* FROM user WHERE id BETWEEN 100 AND 1000’;
List list2 = "SELECT o.userId FROM order WHERE o.time>'2019-3-14 AND o.userId IN ( list1里的所有ID) ";
List result = list2与list1作inner join操作(而不是left 操作)
这是因为子表有条件之后,将不再以主表的数据为准了

2. 关联子表会将主表的数据筛选掉的情况(不论是inner、left), 分页操作需要将两个表的数据拿到后在代码里分页

  1. 子表有where条件的
  2. 子表中不存在关联字段对应的值

注:强烈不推荐在代码里分页,在代码里分页的最大问题是处理数据量会很大,会影响系统性能。
如果有分页,尽量想办法在SQL里完成;实在不行,可根据具体情况分析避免。

3. 如果主表与子表是 一对多的关系(不论是inner、left),子表的数据条数决定了主表的数据条数。

这个体现在代码里就是,在利用map建立主表与子表的对应关系时,我们可以用ArrayListMultimap:
伪代码:

		ArrayListMultimap<String, Object[]> map = ArrayListMultimap.create();
        for (Object[] arr : list) { // list数据是子表的数据,arr[0]是关联字段
            map.put(arr[0].toString(), arr);
        }

4. 在作in操作时特别需要注意, 如果in的参数个数为0,则不再需要查SQL

5. 要判断in条件参数个数,如果太大会报导致报错(这会是一个隐藏bug,直到数据量大了才会发现)。目前的解决办法分成多次in查询(太慢可考虑线程)

多线程in查询可参考:https://gitee.com/lovewx/codes/be51427wngzfrcmqsivua79

6. 拆分SQL时,要考虑索引失效的问题,即关联查询时可以用到索引,而拆开之后就不能用到索引了

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页