sql优化-mysql执行计划调优实践

第一版sql

explain
 SELECT COUNT(*) AS waitFinancialReviewCount
  FROM clai_base
 WHERE clai_base.obj_status = 1
   AND EXISTS (
    SELECT clai_financial_review_process.id FROM clai_financial_review_process
    INNER JOIN user_rel_permission
      ON user_rel_permission.obj_status = 1
        AND user_rel_permission.user_id = 1
        AND user_rel_permission.permission_id = clai_financial_review_process.permission_id
        AND user_rel_permission.status = 1
         WHERE clai_financial_review_process.obj_status = 1
        AND clai_financial_review_process.review_node_id = clai_base.financial_review_node_id
        AND clai_base.financial_review_status = 2
        AND clai_financial_review_process.review_status = 0
        AND (clai_financial_review_process.review_user_id IS NULL
        OR clai_financial_review_process.review_user_id = 1
         )
   )
    AND EXISTS (
       SELECT organization_id FROM user_rel_organization_view
        WHERE user_rel_organization_view.user_id = 1
          AND user_rel_organization_view.organization_id = clai_base.organization_id
          AND user_rel_organization_view.obj_status = 1
   )

可以看出 用了exists，看看它的执行计划

可以看出，存在全表。再看看查询耗时：

做个优化，解决全表；

第二版sql

explain
SELECT COUNT(*) AS waitFinancialReviewCount
  FROM clai_base
 INNER JOIN clai_financial_review_process on clai_financial_review_process.review_node_id = clai_base.financial_review_node_id 
  inner join user_rel_permission on user_rel_permission.permission_id = clai_financial_review_process.permission_id
  inner join user_rel_organization_view on user_rel_organization_view.organization_id = clai_base.organization_id
 WHERE clai_base.obj_status = 1
and clai_base.financial_review_status = 2 
 and user_rel_permission.obj_status = 1
 AND user_rel_permission.user_id = 1
  AND user_rel_permission.status = 1
  and clai_financial_review_process.obj_status = 1
  AND clai_financial_review_process.review_status = 0
        AND (clai_financial_review_process.review_user_id IS NULL
        OR clai_financial_review_process.review_user_id = 1
         )
  and user_rel_organization_view.user_id = 1
 and user_rel_organization_view.obj_status = 1;

可以看出，全都调整为inner join方式，看看执行计划：

可以看出，没有全表了，再看看查询耗时：

耗时变长了。再看看数据筛选情况：

调整下执行计划，想办法把index_merge 对应的一行放到后面。把rows 353计划调整到前面，再调整sql。

第三版sql

	 explain
SELECT COUNT(*) AS waitFinancialReviewCount
  FROM clai_base
 INNER JOIN clai_financial_review_process on clai_financial_review_process.review_node_id = clai_base.financial_review_node_id 
  #inner join user_rel_permission on 
  inner join user_rel_organization_view on user_rel_organization_view.organization_id = clai_base.organization_id
 WHERE clai_base.obj_status = 1
and clai_base.financial_review_status = 2 
 
  and clai_financial_review_process.obj_status = 1
  AND clai_financial_review_process.review_status = 0
        AND (clai_financial_review_process.review_user_id IS NULL
        OR clai_financial_review_process.review_user_id = 1
         )
  and user_rel_organization_view.user_id = 1
 and user_rel_organization_view.obj_status = 1
 and exists(select * from user_rel_permission 
 where user_rel_permission.permission_id = clai_financial_review_process.permission_id
 and user_rel_permission.obj_status = 1
 AND user_rel_permission.user_id = 1
  AND user_rel_permission.status = 1
 )

可以看出，跟index_merge相关的表逻辑调整到exists中了，来看看执行计划：

再看看数据筛选情况：

执行计划按照预期调整。精准的数据筛选放在了前面，再看看执行时间：

时间有较大提升，同第一个版本，提升20倍查询效率。
继续优化，干掉index_merge。
我们看看user_rel_permission的索引情况：

我们看到有这三个索引，mysql在执行中，分别通过这些索引查找，再做合并筛选。这里索引做个调整，对这个三个字段做聚合索引。调整如下：

再看看索引调整后的执行计划：

同预期一样，没有了index_merge。再看看执行时间：

执行时间又缩短了些。

总结

1、对于sql的处理，涉及到多表操作，一定得考虑执行效率；
2、对于sql的分析，基于执行计划的调整，选择最优。一定想办法优先最大化筛选掉不要的数据，或者反过来讲，优先筛选出最小需要的数据。
另外对于尽量不要使用复杂sql，尽量在应用层完成拆分和逻辑处理。
以上sql只做案例展示，不推荐。