EXPLAIN优化慢SQL

项目中发现数据查询很慢，导致前端超时等待的问题。经过日志打印发现，查询sql耗时10秒以上，相关sql如下：

select distinct
        tablemodel.*
        from pjtask_model tablemodel
        JOIN buss_type_permission a ON (
        tablemodel.fields_data_id = a.db_category_id)
        where
        a.status = '0'
        AND
        a.del_flag = '0'
        AND
        tablemodel.del_flag = '0'
        AND (
        a.validity_time IS NULL

        OR
        a.validity_time > sysdate())

        AND (
        a.permission_user_id = 1
        OR

        a.permission_role_id IN (
        SELECT
        b.role_id
        FROM
        sys_user_role b
        WHERE

        b.user_id =1)

        OR
        a.permission_dept_id IN (
        SELECT
        c.dept_id
        FROM
        sys_user c
        WHERE

        c.user_id =1)
        )
        and (a.permission_type = 0 or tablemodel.pj_flag = 2)

        and tablemodel.status  in
            ("JDZT100010","JDZT10000","JDZT10001","JDZT10002","JDZT10006","JDZT10007","JDZT100061")

        order by
        tablemodel.create_time desc

经过查看此sql，决定查看执行计划。在sql前添加EXPLAIN。代码如下：

EXPLAIN select distinct
        tablemodel.*
        from pjtask_model tablemodel
        JOIN buss_type_permission a ON (
        tablemodel.fields_data_id = a.db_category_id)
        where
        a.status = '0'
        AND
        a.del_flag = '0'
        AND
        tablemodel.del_flag = '0'
        AND (
        a.validity_time IS NULL

        OR
        a.validity_time > sysdate())

        AND (
        a.permission_user_id = 1
        OR

        a.permission_role_id IN (
        SELECT
        b.role_id
        FROM
        sys_user_role b
        WHERE

        b.user_id =1)

        OR
        a.permission_dept_id IN (
        SELECT
        c.dept_id
        FROM
        sys_user c
        WHERE

        c.user_id =1)
        )
        and (a.permission_type = 0 or tablemodel.pj_flag = 2)

        and tablemodel.status  in
            ("JDZT100010","JDZT10000","JDZT10001","JDZT10002","JDZT10006","JDZT10007","JDZT100061")

        order by
        tablemodel.create_time desc

 运行结果：

mysql索引介绍可以参考：

MYSQL explain详解-CSDN博客

可以看出，联表条件为ON (tablemodel.fields_data_id = a.db_category_id)，但执行计划展示出来的信息，表示联表没有使用到索引查询，而是全量扫描表信息。

select distinct
        tablemodel.*
        from pjtask_model tablemodel
        JOIN buss_type_permission a ON (
        tablemodel.fields_data_id = a.db_category_id)

查看buss_type_permission（a） 表结构，发现确实没有db_category_id字段的索引。

所以我们添加索引：

再来运行执行计划语句，结果如下：

可以看到表buss_type_permission（a）的 key列，使用了db_category_id索引，rows也从6984改为了9，极大缩小了扫描数据的范围，提升sql查询效率。

原文地址：https://blog.csdn.net/qq_36068521/article/details/143800574

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！