效能優化|多複雜表關聯查詢，你必須要知道的檢索姿勢！

ps:以下結果，測試環境都是基於
開發環境

mysql	springBoot	Mybatis plus	Redis
8.0.11	2.1.7.RELEASE	2.3	3.2.100

其他環境

記憶體	作業系統
16gb	win10

資料環境

dc_department	dc_material	dc_order	dc_project
10條	1000條	10w條	1000條

       驗證過程：使用最為基礎的時間戳相減（ms級別，程式結束時間戳-程式開始時間戳），得到程式在某種優化方案下的執行時間

多表查詢場景

       在實際專案中，我們總會涉及很多的實體對應關係（一對一，一對多等），往往通過主鍵（業務唯一欄位）做關聯，在查詢的時候往往需要跨表查詢多次，當一個表的關聯越來越的時候，就要考慮如何去優化了，特別是在查詢的時候，以下是探討優化方案的過程。

常規優化手段

• 快取
• 批量查詢
• sql優化（新增索引，執行計劃）
• 構建低時間複雜度的資料結構
• 設計冗餘欄位

具體應用場景

       以常見的實體對應關係一對多為例：在電商專案中需要根據具體的揀貨單來找到對應的物料商品，為此需要把物料單上關聯的所有物品都關聯查詢出來，表結構如下：

• 快取：提前預載入字表資料到快取中，需要關聯查詢再逐一取出匹配
• 批量查詢：提前把主表需要的欄位篩選，再把篩選的欄位全部放入字表中查詢
• sql優化（新增索引，執行計劃）：通過mysql手段，新增索引，減少sql執行時間
• 構建低時間複雜度的資料結構：以時間複雜度為中心，設計檢索速度快的資料結構去匹配字表元素
• 設計冗餘欄位：直接把需要查詢的欄位設定在主表中

       以上手段，在實際專案中都是常用的手段，有可能是幾種結合在一起使用，本博文主要驗證是否良好的資料結構，能減少檢索時間

       對於查詢來說，最為常見的莫過於使用快取來優化，快取有基本的兩種：本地快取和遠端快取，我們最為常見的快取元件自然是的redis，鑑於資料量的問題，資料量大（>10w）的查詢基本都不會採用本地快取，使用遠端快取優化的思路：專案啟動載入需要匹配的資料，查詢的時候使用快取中的資料做匹配

       載入dc_department、dc_material、dc_project三張字表資料進入快取

@Component
public class StartUpRunner implements ApplicationRunner {
    private final StringRedisTemplate stringRedisTemplate;
    private final DcMaterialService materialService;
    private final DcDepartmentService departmentService;
    private final DcProjectService projectService;

    public StartUpRunner(StringRedisTemplate stringRedisTemplate, DcMaterialService materialService, DcDepartmentService departmentService, DcProjectService projectService) {
        this.stringRedisTemplate = stringRedisTemplate;
        this.materialService = materialService;
        this.departmentService = departmentService;
        this.projectService = projectService;
    }

    @Override
    public void run(ApplicationArguments args) throws Exception {
        //初始化字表資料
        List<DcMaterial> dcMaterials = materialService.selectList(new EntityWrapper<>(null));
        List<DcDepartment> dcDepartments = departmentService.selectList(new EntityWrapper<>(null));
        List<DcProject> dcProjects = projectService.selectList(new EntityWrapper<>(null));
        stringRedisTemplate.opsForValue().set("material", JSON.toJSONString(dcMaterials));
        stringRedisTemplate.opsForValue().set("department", JSON.toJSONString(dcDepartments));
        stringRedisTemplate.opsForValue().set("project", JSON.toJSONString(dcProjects));

    }
}

手段1：快取 + list資料結構

       使用list結構做n^2時間複雜度的匹配

/**
 * (DcOrder)表服務實現類
 *
 * @author dengzhenxiang Email:dengzhenxiang@coll-mall.com
 * @since 2020-06-27 18:43:42
 */
@Service
@Slf4j
public class DcOrderServiceImpl extends ServiceImpl<DcOrderDao, DcOrder> implements DcOrderService {
    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private DcOrderDao orderDao;
    private static int index = 1;

    @Override
    @SuppressWarnings("unchecked")
    public List<DcOrder> getOrder() {
        long timeMillis = System.currentTimeMillis();
        List<DcOrder> dcOrders = orderDao.selectList(new EntityWrapper<>(null));
        List<DcMaterial> materials = JSON.parseArray(((JSONArray) Objects.requireNonNull(redisTemplate.opsForValue().get(RedisKey.MATERIAL))).toJSONString(), DcMaterial.class);
        List<DcDepartment> departments = JSON.parseArray(((JSONArray) Objects.requireNonNull(redisTemplate.opsForValue().get(RedisKey.DEPARTMENT))).toJSONString(), DcDepartment.class);
        List<DcProject> projects = JSON.parseArray(((JSONArray) Objects.requireNonNull(redisTemplate.opsForValue().get(RedisKey.PROJECT))).toJSONString(), DcProject.class);
        //快取 + List資料結構
        for (DcOrder dcOrder : dcOrders) {
            materials.forEach(v -> {
                if (v.getCode().equals(dcOrder.getMaterialCode())) {
                    dcOrder.setMaterialName(v.getName());
                }
            });
            departments.forEach(v -> {
                if (v.getCode().equals(dcOrder.getDepartmentCode())) {
                    dcOrder.setDepartmentName(v.getName());
                }
            });
            projects.forEach(v -> {
                if (v.getCode().equals(dcOrder.getMaterialCode())) {
                    dcOrder.setProjectName(v.getName());
                }
            });
        }
        log.info("快取 + list資料結構,第" + index++ + "次：{}", System.currentTimeMillis() - timeMillis);
        return dcOrders;
    }
}

       耗時：

手段1：快取 + map資料結構

       使用Map結構做n時間複雜度的匹配

/**
 * (DcOrder)表服務實現類
 *
 * @author dengzhenxiang Email:dengzhenxiang@coll-mall.com
 * @since 2020-06-27 18:43:42
 */
@Service
@Slf4j
public class DcOrderServiceImpl extends ServiceImpl<DcOrderDao, DcOrder> implements DcOrderService {
    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private DcOrderDao orderDao;
    private static int index = 1;

    @Override
    @SuppressWarnings("unchecked")
    public List<DcOrder> getOrder() {
        long timeMillis = System.currentTimeMillis();
        List<DcOrder> dcOrders = orderDao.selectList(new EntityWrapper<>(null));
        List<DcMaterial> materials = JSON.parseArray(((JSONArray) Objects.requireNonNull(redisTemplate.opsForValue().get(RedisKey.MATERIAL))).toJSONString(), DcMaterial.class);
        List<DcDepartment> departments = JSON.parseArray(((JSONArray) Objects.requireNonNull(redisTemplate.opsForValue().get(RedisKey.DEPARTMENT))).toJSONString(), DcDepartment.class);
        List<DcProject> projects = JSON.parseArray(((JSONArray) Objects.requireNonNull(redisTemplate.opsForValue().get(RedisKey.PROJECT))).toJSONString(), DcProject.class);
        // 快取 + map資料結構
        Map<String, DcMaterial> materialMap = materials.stream().collect(Collectors.toMap(DcMaterial::getCode, Function.identity()));
        Map<String, DcDepartment> departmentMap = departments.stream().collect(Collectors.toMap(DcDepartment::getCode, Function.identity()));
        Map<String, DcProject> projectMap = projects.stream().collect(Collectors.toMap(DcProject::getCode, Function.identity()));
        for (DcOrder dcOrder : dcOrders) {
            dcOrder.setMaterialName(Optional.ofNullable(materialMap.get(dcOrder.getMaterialCode())).isPresent() ? materialMap.get(dcOrder.getMaterialCode()).getName() : "")
                    .setProjectName(Optional.ofNullable(projectMap.get(dcOrder.getProjectCode())).isPresent() ? projectMap.get(dcOrder.getProjectCode()).getName() : "")
                    .setDepartmentName(Optional.ofNullable(departmentMap.get(dcOrder.getDepartmentCode())).isPresent() ? departmentMap.get(dcOrder.getDepartmentCode()).getName() : "");
        }
        log.info("快取 + list資料結構,第" + index++ + "次：{}", System.currentTimeMillis() - timeMillis);
        return dcOrders;
    }
}

       耗時：

總結

1. 檢索過程優先創造良好的資料檢索結構做檢索，這樣可以極大的降低時間複雜度，通常的時間複雜度演算法計算都是捨去係數得到最高冪次方。計算方式見資料結構與演算法|10分鐘速成資料結構基本知識點中的一些經驗性的結論
2. Map的containsKey(),get()等方法都是n時間複雜度，在經常有外迴圈的前提下使用map做匹配通常時間複雜度都是n（雜湊演算法）,相比n^2時間複雜度的匹配，太耗時間，優化手段首要考慮map結構
3. 對於快取，設計冗餘欄位方式，雖然本文沒有佐證，但按照博主一般的經驗，通常該手段多用於對欄位的改動不大的前提下，可以使用該方法。
4. 資料索引方式，雖然也沒有佐證，但需要滿足一些條件，才可以設定索引：①肯定在where條件經常使用 ②該欄位的內容不是唯一的幾個值（離散性） ③欄位內容不是頻繁變化（離散性）