java - 有没有更有效的方法来提高嵌套循环查询的性能

Question

我的问题

我有一个应用程序生成一个列表，其中包含 multiple layers of nested lists .我当前的实现生成了所需的结果，但它也执行了大量查询，exponential to the number of layers depth , 到数据库。所以整体表现很差(花费太多时间)。我正在努力寻找改进它的方法，但说实话，我不知道哪种方法更好。

所以我想知道的是:

1. 我有进步。我的改进可以接受吗？ (不会产生其他问题)

2. 还有其他方法可以改进吗，比如:

   一个。非规范化数据库表

   使用其他支持更好的ORM框架

   其他更好的数据库设计或 Java 代码实现(真的需要你们的帮助)

原始需求

我有一些具有一对多关系的表:

Family
id       name
1        Smiths
2        Johnson
3        Williams

Member
id       family_id  name
1        1          David Smiths
2        1          Mary Smiths
3        1          William Smiths
3        2          David Johnson
4        3          David Williams

Asset
id       member_id  type     value
1        1          cash     100.00
2        1          share    200.00
3        1          security 100.00
4        2          cash     50.00

我需要的是生成a list of Families and their Asset .这应该是 Family 的列表, 每个 Family包含 Member 的列表, 和每个 Member包含 Asset 的列表:

class Family{
    Integer id;
    String name;
    List<Member> members;
    // Getter and setters
}

class Member{
    Integer id;
    Integer family_id;
    String name;
    List<Asset> assets;
    // Getter and setters
}

class Asset{
    Integer id;
    Integer member_id;
    String type;
    BigDecimal value;
}

结果应该是List<Family> families .输出到 JSON:

{    
"families": [{
    "id": 1,
    "name": "Smiths",
    "members": [{
        "id": 1,
        "family_id": 1,
        "name": "David Smiths",
        "assets": [{
            "id": 1,
            "member_id": 1,
            "type": "cash",
            "value": "100.00"
        }, {
            "id": 2,
            "member_id": 1,
            "type": "share",
            "value": "200.00"
        }, {
            "id": 3,
            "member_id": 1,
            "type": "security",
            "value": "100.00"
        }]
    }]
}]
}

到目前为止，我有两种方法可以做到这一点:

方案一嵌套循环查询

Java代码

List<Family> generateFamilyList(){
    List<Family> families = resultMapper.selectFamily();

    // select Member for each Family
    for(Family family: families){

        List<Member> membersInFamily = resultMapper.selectMemberByFamily(family.getId());

        //select Asset for each Member
        for(Member member: membersInFamily){

            List<Asset> assetsOfMember = resultMapper.selectAssetByMember(member.getId());
            member.setAssets(assetsOfMember);
        }
        family.setMembers(membersInFamily );
    }

    return families;
}

和报表，使用Mybatis .

<select id="selectFamily" resultType="Family">
    select id, name from family;
</select>

<select id="selectMemberByFamily" resultType="Member">
    select id, family_id, name from member
    where family_id = #{familyId};
</select>

<select id="selectAssetByMember" resultType="Asset">
    select id, member_id, type, value from asset
    where member_id = #{memberId};
</select>

此方法完成其工作并生成正确的 JSON。但它也会创建 N^3 times of query to the database在最里面选择 selectAssetByMember(member.getId())

我切换到使用 Mybatis collection select在映射器中，但发现这只会使 Mybatis Mapper执行 for 循环查询而不是在 java 代码中执行。数据库仍然收到 N^3 查询。

所以我想到了另一个解决方案:

解决方案2 在查询中全选，然后在Java中过滤

Java代码

List<Family> generateFamilyList(){
    List<Family> families = resultMapper.selectFamily();

    List<Integer> allFamilyIds = familes.stream()
        .map(Family::getId)
        .collect(Collectors.toList());

    if(familyIds.isEmpty())
        //throw exception or return 

    // select all members in one query.
    // !Watch out! for Max Number of Allowable Parameters
    List<Member> allMembers = resultMapper.selectMemberByFamilyIds(allFamilyIds);

    List<Integer> allMemberIds = allMembers.stream()
        .map(Member::getId)
        .collect(Collectors.toList());

    if(allMemberIds.isEmpty())
        //throw exception or 
        //return a new empty ArrayList for this member's asset

    // select all ssets in one query.
    // !Watch out! for Max Number of Allowable Parameters
    List<Asset> allAssets = resultMapper.selectAssetByMemberIds(allMemberIds );

    // filter and set Members for each Family       
    for(Family family: families){

        List<Member> membersInFamily = allMembers.stream()
            .filter(member -> member.getFamilyId().equals(family.getId()))
            .collect(Collectors.toList());

        //select Asset for each Member
        for(Member member: membersInFamily){

            List<Asset> assetsOfMember = allAssets.stream()
                .filter(asset -> asset.getMemberId().equals(member.getId()))
                .collect(Collectors.toList());

            member.setAssets(assetsOfMember);
        }
        family.setMembers(membersInFamily );
    }

    return families;
}

和声明。 mybatis 支持参数列表:

<select id="selectFamily" resultType="Family">
    select id, name from family;
</select>

<select id="selectMemberByFamilyIds" resultType="Member">
    select id, family_id, name from member
    where family_id IN 
    <foreach> #{familyId} </foreach>;  
    <!-- ( 1, 2, 3, ....); the input list familyIds-->
    <!-- could cause SQLException if the ids more than the Max Number of Allowable Parameters of database -->
</select>

<select id="selectAssetByMemberIds" resultType="Asset">
    select id, member_id, type, value from asset
    where member_id IN
    <foreach> #{memberId} </foreach>
    <!-- ( 1, 2, 3, ....); the input list memberIds-->
    <!-- could cause SQLException if the ids more than the Max Number of Allowable Parameters of database -->
</select>

结果与问题

1. 查询数量减少

2. Java 应用程序中的内存使用量增加

3. 整体应用程序响应时间显着减少(在我的案例中减少到 20% - 10%)

4. 新一期:Max Number of Allowable Parameters .如果参数列表memberIds需要在查询之前进行分页尺寸太大。最大数量是有限的，并且因数据库类型而异。

这就是我现在得到的全部。而且我有点卡住了，不知道我应该在哪里改进我的代码。

谢谢大家看我长长的问题。

Answer 1

这实际上取决于我们谈论的数据量、数据保存位置等。

对于真正的大数据解决方案，我们可能会说类似 cassandra 中的图形数据库，通过 akka 连接。

对于中等负载，数据库将位于其自己的服务器上，因此您需要减少连接量和网络使用量，因此最好在本地进行一次调用和排序，

对于非常小的数据，应用程序和数据库在同一台服务器上，多次调用数据库是可以的，数据库在排序时会更好更快。