20211130-MySQL SQL优化案例(一)

在这里插入图片描述

问题现象:

XX系统—首页—点击"抄送给我" 耗时较长,需要12秒左右。

环境说明:

DB:MySQL 8.0.20 双主
OS:Redhat 7.5

问题定位:

1.查看当前执行的SQL

show full processlist;

2.查看历史执行慢SQL

show variables like '%slow%';

超过2秒的SQL写入到slow_query_log_file对应日志。

查看慢SQL:

tail -1000 slow-query.log|more

慢SQL如下:耗时11秒

# Time: 2021-11-29T09:14:48.534686Z

# User@Host: cjccc[cjccc] @ host-10-4-0-36.openstacklocal [10.4.0.36]  Id: 2404310

# Query_time: 11.832960  Lock_time: 0.000328 Rows_sent: 200  Rows_examined: 4009

SET timestamp=1638177276;

SELECT
                        T1.WORKSHEETID,
                        T1.WFTYPE_NAME,
                        T1.PROCESSINSTNAME,
                        T1.WFCREATOR_NAME,
                        T1.CREATEDDATE,
                        T1.STATE_DESC,
                        T2.PARTICIPANT_NAME,
                        T2.PARTICIPANT,
                        T2.ACTIVITYDEFNAME,
                        T1.COMPLETEDDATE,
                        T3.ccid,
                        T3.DEAL_STATE_KEY,
                        T3.ACTIVITYDEFNAME AS CC_NODE_NAME,
                        T3.ACCT_NM AS CUR_DEAL_USER,
                        T1.PROCESSINSTANCEID,
                        T1.PROCESSDEFINITIONID,
                        T1.WFCREATOR,
                        T1.STATE,
                        T2.ACTIVITYDEFINITIONID
                FROM
                (
                        SELECT DISTINCT
                                C.WORKSHEETID,
                                A.PROCESSINSTANCEID,
                                A.PROCESSINSTNAME,
                                B.WFCREATOR,
                                B.PROCESSDEFINITIONID,
                                B.CREATEDDATE,
                                B.COMPLETEDDATE,
                                B.STATE,
                                D.ACCT_NM AS WFCREATOR_NAME,
                                T.WFTYPE_NAME,
                                S.STATE_DESC
                        FROM
                                IWF_WORKITEM A,
                                IWF_PROCESSINSTANCE B,
                                ITSM_PROINSTANCE_WORKSHEET_REL C,
                                SYS_ACCOUNT D,
                                ITSM_WF_TYPE T,
                                ITSM_WF_STATE S
                        WHERE
                                A.PROCESSINSTANCEID = B.PROCESSINSTANCEID
                        AND B.PROCESSINSTANCEID = C.PROCESSINSTANCEID
                        AND B.WFCREATOR = D.ACCT_ID
                        AND C.WORKSHEETTYPE = T.WFTYPE_NO
                        AND B.STATE = S.STATE_KEY
                        AND S.STATE_TYPE = 1
                        GROUP BY
                                C.WORKSHEETID
                ) T1
                LEFT JOIN (
                        SELECT
                                A1.PROCESSINSTANCEID,
                                A1.ACTIVITYDEFINITIONID,
                                A1.ACTIVITYDEFNAME,
                GROUP_CONCAT(A1.PARTICIPANT SEPARATOR ',') AS PARTICIPANT,
                                CASE
                        WHEN W.SCRAMBLE_NODE IS NOT NULL
                        OR W.SCRAMBLE_NODE = '' THEN
                                (
                                        SELECT
                                                ROLE_NM
                                        FROM
                                                SYS_ROLE
                                        WHERE
                                                ROLE_ID = W.SCRAMBLE_ROLE
                                )
                        ELSE
                                GROUP_CONCAT(B1.ACCT_NM SEPARATOR ',')
                        END AS PARTICIPANT_NAME
                        FROM
                                IWF_WORKITEM A1
                        LEFT JOIN SYS_ACCOUNT B1 ON A1.PARTICIPANT = B1.ACCT_ID
                        LEFT JOIN ITSM_WORKFLOW_DEFINITION W ON (
                                A1.ACTIVITYDEFINITIONID = W.SCRAMBLE_NODE
                                AND W.WORKFLOW_ID = A1.PROCESSDEFINITIONID
                        )
                        WHERE
                                A1.STATE IN (1, 2)
                        AND A1.NEXTWORKITEMID IS NULL
                        GROUP BY
                                A1.PROCESSINSTANCEID
                ) T2 ON T1.PROCESSINSTANCEID = T2.PROCESSINSTANCEID
                INNER JOIN (
                        SELECT DISTINCT
                                A.ccid,
                                A.instanceid,
                                A.DEAL_STATE_KEY,
                                B.ACTIVITYDEFNAME,
                                C.ACCT_NM
                        FROM
                                itsm_cc_record AS A
                        LEFT JOIN iwf_workitem AS B ON A.instanceid = B.PROCESSINSTANCEID
                        AND A.ACTIVITYDEFINITIONID = B.ACTIVITYDEFINITIONID
                        LEFT JOIN sys_account AS C ON A.CUR_DEAL_USER = C.acct_id
                        WHERE
                                A.userid = '10000222'
                ) AS T3 ON T3.instanceid = T1.PROCESSINSTANCEID
                ORDER BY
                        T1.CREATEDDATE DESC
                        LIMIT 0, 200;

查看SQL执行计划

explain ...
+----+--------------------+------------+------------+--------+-------------------------------------------------+-------------------+---------+------------------------------+-------+----------+------------------------------------------------+
| id | select_type        | table      | partitions | type   | possible_keys                                   | key               | key_len | ref                          | rows  | filtered | Extra                                          |
+----+--------------------+------------+------------+--------+-------------------------------------------------+-------------------+---------+------------------------------+-------+----------+------------------------------------------------+
|  1 | PRIMARY            | <derived2> | NULL       | ALL    | NULL                                            | NULL              | NULL    | NULL                         |  4335 |   100.00 | Using where; Using filesort                    |
|  1 | PRIMARY            | <derived3> | NULL       | ref    | <auto_key0>                                     | <auto_key0>       | 387     | T1.PROCESSINSTANCEID         |    10 |   100.00 | NULL                                           |
|  1 | PRIMARY            | <derived5> | NULL       | ref    | <auto_key0>                                     | <auto_key0>       | 153     | T1.PROCESSINSTANCEID         |   358 |   100.00 | Using where                                    |
|  5 | DERIVED            | A          | NULL       | ALL    | NULL                                            | NULL              | NULL    | NULL                         |  1044 |    10.00 | Using where; Using temporary                   |
|  5 | DERIVED            | B          | NULL       | ALL    | PROCESSINSTANCEID                               | NULL              | NULL    | NULL                         | 14894 |   100.00 | Range checked for each record (index map: 0x2) |
|  5 | DERIVED            | C          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | cjccc.A.CUR_DEAL_USER        |     1 |   100.00 | Using where                                    |
|  3 | DERIVED            | A1         | NULL       | ALL    | PROCESSINSTANCEID                               | NULL              | NULL    | NULL                         | 14894 |     2.00 | Using where; Using filesort                    |
|  3 | DERIVED            | B1         | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | cjccc.A1.PARTICIPANT         |     1 |   100.00 | Using where                                    |
|  3 | DERIVED            | W          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 98      | cjccc.A1.PROCESSDEFINITIONID |     1 |   100.00 | Using where                                    |
|  4 | DEPENDENT SUBQUERY | SYS_ROLE   | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | func                         |     1 |   100.00 | Using where                                    |
|  2 | DERIVED            | S          | NULL       | ref    | PRIMARY                                         | PRIMARY           | 4       | const                        |     7 |   100.00 | Using temporary                                |
|  2 | DERIVED            | B          | NULL       | ALL    | PRIMARY,key_wfcreator,key_processinstanceid     | NULL              | NULL    | NULL                         |  1052 |    10.00 | Using where; Using join buffer (hash join)     |
|  2 | DERIVED            | D          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | cjccc.B.WFCREATOR            |     1 |   100.00 | Using index condition                          |
|  2 | DERIVED            | C          | NULL       | eq_ref | PRIMARY,key_worksheettype,key_processinstanceid | PRIMARY           | 386     | cjccc.B.PROCESSINSTANCEID    |     1 |   100.00 | NULL                                           |
|  2 | DERIVED            | T          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 4       | cjccc.C.WORKSHEETTYPE        |     1 |   100.00 | NULL                                           |
|  2 | DERIVED            | A          | NULL       | ref    | PROCESSINSTANCEID                               | PROCESSINSTANCEID | 387     | cjccc.B.PROCESSINSTANCEID    |     5 |   100.00 | NULL                                           |
+----+--------------------+------------+------------+--------+-------------------------------------------------+-------------------+---------+------------------------------+-------+----------+------------------------------------------------+
16 rows in set, 4 warnings (0.00 sec)
explain format = tree ...

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 200 row(s)
    -> Nested loop inner join
        -> Nested loop left join
            -> Sort: t1.CREATEDDATE DESC
                -> Filter: (t1.PROCESSINSTANCEID is not null)
                    -> Table scan on T1
                        -> Materialize
                            -> Table scan on <temporary>
                                -> Temporary table with deduplication
                                    -> Nested loop inner join  (cost=1306.47 rows=434)
                                        -> Nested loop inner join  (cost=179.33 rows=74)
                                            -> Nested loop inner join  (cost=145.67 rows=74)
                                                -> Nested loop inner join  (cost=112.01 rows=74)
                                                    -> Inner hash join (b.STATE = s.state_key)  (cost=78.34 rows=74)
                                                        -> Filter: (b.WFCREATOR is not null)  (cost=2.04 rows=105)
                                                            -> Table scan on B  (cost=2.04 rows=1052)
                                                        -> Hash
                                                            -> Index lookup on S using PRIMARY (state_type=1)  (cost=0.95 rows=7)
                                                    -> Single-row index lookup on D using PRIMARY (acct_id=b.WFCREATOR), with index condition: (b.WFCREATOR = d.acct_id)  (cost=0.04 rows=1)
                                                -> Single-row index lookup on C using PRIMARY (PROCESSINSTANCEID=b.PROCESSINSTANCEID)  (cost=0.04 rows=1)
                                            -> Single-row index lookup on T using PRIMARY (wftype_no=c.WORKSHEETTYPE)  (cost=0.04 rows=1)
                                        -> Index lookup on A using PROCESSINSTANCEID (PROCESSINSTANCEID=b.PROCESSINSTANCEID)  (cost=1.47 rows=6)
            -> Index lookup on T2 using <auto_key0> (PROCESSINSTANCEID=t1.PROCESSINSTANCEID)
                -> Materialize
                    -> Group aggregate: group_concat(b1.acct_nm separator ','), group_concat(a1.PARTICIPANT separator ',')
                        -> Nested loop left join  (cost=4818.02 rows=14894)
                            -> Nested loop left join  (cost=3141.52 rows=14894)
                                -> Sort: a1.PROCESSINSTANCEID  (cost=1577.65 rows=14894)
                                    -> Filter: ((a1.STATE in (1,2)) and (a1.NEXTWORKITEMID is null))
                                        -> Table scan on A1
                                -> Filter: (a1.PARTICIPANT = b1.acct_id)  (cost=0.25 rows=1)
                                    -> Single-row index lookup on B1 using PRIMARY (acct_id=a1.PARTICIPANT)  (cost=0.25 rows=1)
                            -> Filter: ((a1.ACTIVITYDEFINITIONID = w.SCRAMBLE_NODE) and (w.WORKFLOW_ID = a1.PROCESSDEFINITIONID))  (cost=0.63 rows=1)
                                -> Single-row index lookup on W using PRIMARY (WORKFLOW_ID=a1.PROCESSDEFINITIONID)  (cost=0.63 rows=1)
                    -> Select #4 (subquery in projection; dependent)
                        -> Filter: (sys_role.role_id = w.SCRAMBLE_ROLE)  (cost=0.35 rows=1)
                            -> Single-row index lookup on SYS_ROLE using PRIMARY (role_id=w.SCRAMBLE_ROLE)  (cost=0.35 rows=1)
        -> Filter: (t3.instanceid = t1.PROCESSINSTANCEID)
            -> Index lookup on T3 using <auto_key0> (instanceid=t1.PROCESSINSTANCEID)
                -> Materialize
                    -> Table scan on <temporary>
                        -> Temporary table with deduplication
                            -> Nested loop left join  (cost=311209.26 rows=1554934)
                                -> Nested loop left join  (cost=155689.80 rows=1554934)
                                    -> Filter: (a.userid = '10000222')  (cost=106.65 rows=104)
                                        -> Table scan on A  (cost=106.65 rows=1044)
                                    -> Filter: ((a.instanceid = b.PROCESSINSTANCEID) and (a.ACTIVITYDEFINITIONID = b.ACTIVITYDEFINITIONID))  (cost=15.13 rows=14894)
                                        -> Index range scan on B (re-planned for each iteration)  (cost=15.13 rows=14894)
                                -> Filter: (a.CUR_DEAL_USER = c.acct_id)  (cost=0.00 rows=1)
                                    -> Single-row index lookup on C using PRIMARY (acct_id=a.CUR_DEAL_USER)  (cost=0.00 rows=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL拆分:

第一部分SQL:
耗时0.22 sec,无需优化。

SELECT DISTINCT
                                C.WORKSHEETID,
                                A.PROCESSINSTANCEID,
                                A.PROCESSINSTNAME,
                                B.WFCREATOR,
                                B.PROCESSDEFINITIONID,
                                B.CREATEDDATE,
                                B.COMPLETEDDATE,
                                B.STATE,
                                D.ACCT_NM AS WFCREATOR_NAME,
                                T.WFTYPE_NAME,
                                S.STATE_DESC
                        FROM
                                IWF_WORKITEM A,
                                IWF_PROCESSINSTANCE B,
                                ITSM_PROINSTANCE_WORKSHEET_REL C,
                                SYS_ACCOUNT D,
                                ITSM_WF_TYPE T,
                                ITSM_WF_STATE S
                        WHERE
                                A.PROCESSINSTANCEID = B.PROCESSINSTANCEID
                        AND B.PROCESSINSTANCEID = C.PROCESSINSTANCEID
                        AND B.WFCREATOR = D.ACCT_ID
                        AND C.WORKSHEETTYPE = T.WFTYPE_NO
                        AND B.STATE = S.STATE_KEY
                        AND S.STATE_TYPE = 1
                        GROUP BY
                                C.WORKSHEETID;

第二部分SQL:
耗时0.03秒,无需优化。

SELECT
                                A1.PROCESSINSTANCEID,
                                A1.ACTIVITYDEFINITIONID,
                                A1.ACTIVITYDEFNAME,
                GROUP_CONCAT(A1.PARTICIPANT SEPARATOR ',') AS PARTICIPANT,
                                CASE
                        WHEN W.SCRAMBLE_NODE IS NOT NULL
                        OR W.SCRAMBLE_NODE = '' THEN
                                (
                                        SELECT
                                                ROLE_NM
                                        FROM
                                                SYS_ROLE
                                        WHERE
                                                ROLE_ID = W.SCRAMBLE_ROLE
                                )
                        ELSE
                                GROUP_CONCAT(B1.ACCT_NM SEPARATOR ',')
                        END AS PARTICIPANT_NAME
                        FROM
                                IWF_WORKITEM A1
                        LEFT JOIN SYS_ACCOUNT B1 ON A1.PARTICIPANT = B1.ACCT_ID
                        LEFT JOIN ITSM_WORKFLOW_DEFINITION W ON (
                                A1.ACTIVITYDEFINITIONID = W.SCRAMBLE_NODE
                                AND W.WORKFLOW_ID = A1.PROCESSDEFINITIONID
                        )
                        WHERE
                                A1.STATE IN (1, 2)
                        AND A1.NEXTWORKITEMID IS NULL
                        GROUP BY
                                A1.PROCESSINSTANCEID;

第三部分SQL:

耗时12.23 sec,需要优化

SELECT DISTINCT
                                A.ccid,
                                A.instanceid,
                                A.DEAL_STATE_KEY,
                                B.ACTIVITYDEFNAME,
                                C.ACCT_NM
                        FROM
                                itsm_cc_record AS A
                        LEFT JOIN iwf_workitem AS B ON A.instanceid = B.PROCESSINSTANCEID
                        AND A.ACTIVITYDEFINITIONID = B.ACTIVITYDEFINITIONID
                        LEFT JOIN sys_account AS C ON A.CUR_DEAL_USER = C.acct_id
                        WHERE
                                A.userid = '10000222';

耗时12秒,需要优化

单独查看第三部分SQL执行计划:

explain ...

+----+-------------+-------+------------+--------+-------------------+---------+---------+-----------------------+-------+----------+------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys     | key     | key_len | ref                   | rows  | filtered | Extra                                          |
+----+-------------+-------+------------+--------+-------------------+---------+---------+-----------------------+-------+----------+------------------------------------------------+
|  1 | SIMPLE      | A     | NULL       | ALL    | NULL              | NULL    | NULL    | NULL                  |  1044 |    10.00 | Using where; Using temporary                   |
|  1 | SIMPLE      | B     | NULL       | ALL    | PROCESSINSTANCEID | NULL    | NULL    | NULL                  | 14894 |   100.00 | Range checked for each record (index map: 0x2) |
|  1 | SIMPLE      | C     | NULL       | eq_ref | PRIMARY           | PRIMARY | 194     | cjccc.A.CUR_DEAL_USER |     1 |   100.00 | Using where                                    |
+----+-------------+-------+------------+--------+-------------------+---------+---------+-----------------------+-------+----------+------------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)
###explain format = tree ...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>
    -> Temporary table with deduplication
        -> Nested loop left join  (cost=311209.26 rows=1554934)
            -> Nested loop left join  (cost=155689.80 rows=1554934)
                -> Filter: (a.userid = '10000222')  (cost=106.65 rows=104)
                    -> Table scan on A  (cost=106.65 rows=1044)
                -> Filter: ((a.instanceid = b.PROCESSINSTANCEID) and (a.ACTIVITYDEFINITIONID = b.ACTIVITYDEFINITIONID))  (cost=15.13 rows=14894)
                    -> Index range scan on B (re-planned for each iteration)  (cost=15.13 rows=14894)
            -> Filter: (a.CUR_DEAL_USER = c.acct_id)  (cost=0.00 rows=1)
                -> Single-row index lookup on C using PRIMARY (acct_id=a.CUR_DEAL_USER)  (cost=0.00 rows=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

查看表数据量

select count(*) from itsm_cc_record; ---7548
select count(*) from iwf_workitem; ---25574
select count(*) from sys_account; ---834
select count(*) from itsm_cc_record where userid = '10000222'; ---971

两个表关联后,结果集比左表全表大,说明关联关系一对一,关联效率也是极差。

SELECT count(*) from itsm_cc_record A LEFT JOIN iwf_workitem B ON A.instanceid = B.PROCESSINSTANCEID; ---27829

查看userid列数据分布不均匀

select count(*),userid from itsm_cc_record group by userid order by 1 desc;

查看instanceid列数据分布均匀

select count(*),instanceid from itsm_cc_record group by instanceid order by 1;

查看PROCESSINSTANCEID列数据分布均匀

select count(*),PROCESSINSTANCEID from iwf_workitem group by PROCESSINSTANCEID order by 1;

查看表索引信息

mysql> show index from itsm_cc_record;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| itsm_cc_record |          0 | PRIMARY  |            1 | ccid        | A         |        1044 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
mysql> show index from iwf_workitem;
+--------------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name           | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| iwf_workitem |          0 | PRIMARY            |            1 | WORKITEMID         | A         |       14884 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| iwf_workitem |          1 | PROCESSINSTANCEID  |            1 | PROCESSINSTANCEID  | A         |        2530 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| iwf_workitem |          1 | ACTIVITYINSTANCEID |            1 | ACTIVITYINSTANCEID | A         |        8818 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| iwf_workitem |          1 | key_participant    |            1 | PARTICIPANT        | A         |         238 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

尝试优化:

优化:

对itsm_cc_record表,userid,instanceid列分分别创建索引

create index i_itsm_userid  on itsm_cc_record(userid);
create index i_itsm_instanceid  on itsm_cc_record(instanceid);
###drop index i_itsm_userid on itsm_cc_record;
###drop index i_itsm_instanceid on itsm_cc_record;

再次查询SQL,
可以自动走userid列对应的索引,但是选择性也不好,
instanceid列即使创建了索引,由于选择性极差,没有走这个索引。
最终,加完索引后,性能没有任何提升,执行时间还是12秒。
再次查看第三部分SQL执行计划:

###explain format = tree ...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>
    -> Temporary table with deduplication
        -> Nested loop left join  (cost=311209.26 rows=1554934)
            -> Nested loop left join  (cost=155689.80 rows=1554934)
                -> Filter: (a.userid = '10000222')  (cost=106.65 rows=104)
                    -> Table scan on A  (cost=106.65 rows=1044)
                -> Filter: ((a.instanceid = b.PROCESSINSTANCEID) and (a.ACTIVITYDEFINITIONID = b.ACTIVITYDEFINITIONID))  (cost=15.13 rows=14894)
                    -> Index range scan on B (re-planned for each iteration)  (cost=15.13 rows=14894)
            -> Filter: (a.CUR_DEAL_USER = c.acct_id)  (cost=0.00 rows=1)
                -> Single-row index lookup on C using PRIMARY (acct_id=a.CUR_DEAL_USER)  (cost=0.00 rows=1)
 |
+------------------------------------

通过执行计划可知,执行顺序如下:

1.A表通过userid = '10000222'条件,全表扫描方式查出结果集。
2.B表通过((a.instanceid = b.PROCESSINSTANCEID) and (a.ACTIVITYDEFINITIONID = b.ACTIVITYDEFINITIONID))条件,Index range scan方式查出结果集。
3.将第1,2步骤结果集进行Nested loop left join。
4.A表和C表通过a.CUR_DEAL_USER = c.acct_id条件,Single-row index lookup on C using PRIMARY方式得出结果集。
5.第3,4步骤结果集执行Nested loop left join。
6.去重,得出最终结果集。

其中在第3步骤,两张表执行Nested loop left join效率极差,需要多次进行全表扫描操作。
考虑将第3步骤A,B表关联关系,由Nested loop left join改成Left hash join,提高表关联效率。
尝试添加提示,强制走hash jion。

SELECT /*+ HASH_JOIN(A,B)*/ DISTINCT
                                A.ccid,
                                A.instanceid,
                                A.DEAL_STATE_KEY,
                                B.ACTIVITYDEFNAME,
                                C.ACCT_NM
                        FROM
                                itsm_cc_record AS A
                        LEFT JOIN iwf_workitem AS B ON A.instanceid = B.PROCESSINSTANCEID
                        AND A.ACTIVITYDEFINITIONID = B.ACTIVITYDEFINITIONID
                        LEFT JOIN sys_account AS C ON A.CUR_DEAL_USER = C.acct_id
                        WHERE
                                A.userid = '10000222';

仍然走Nested loop left join,由于B表关联列PROCESSINSTANCEID存在索引,导致表关联不走hash join。

尝试忽略B表关联列PROCESSINSTANCEID索引。

explain FORMAT=TREE
SELECT DISTINCT
                                A.ccid,
                                A.instanceid,
                                A.DEAL_STATE_KEY,
                                B.ACTIVITYDEFNAME
                        FROM
                                itsm_cc_record AS A 
                        LEFT JOIN iwf_workitem AS B ignore index(PROCESSINSTANCEID) ON A.instanceid = B.PROCESSINSTANCEID
                        AND A.ACTIVITYDEFINITIONID = B.ACTIVITYDEFINITIONID
                        WHERE
                                A.userid = '10000222';

----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>
    -> Temporary table with deduplication
        -> Left hash join (a.instanceid = b.PROCESSINSTANCEID), (a.ACTIVITYDEFINITIONID = b.ACTIVITYDEFINITIONID)  (cost=155598.11 rows=1554934)
            -> Filter: (a.userid = '10000222')  (cost=106.65 rows=104)
                -> Table scan on A  (cost=106.65 rows=1044)
            -> Hash
                -> Table scan on B  (cost=15.12 rows=14894)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以正常走hash join了。
执行速度也是由12秒,下降到0.06秒。

......
978 rows in set (0.06 sec) 						

再次执行一遍原SQL:
执行速度也是由12秒,下降到0.27秒。

SQL改写:

SELECT
                        T1.WORKSHEETID,
                        T1.WFTYPE_NAME,
                        T1.PROCESSINSTNAME,
                        T1.WFCREATOR_NAME,
                        T1.CREATEDDATE,
                        T1.STATE_DESC,
                        T2.PARTICIPANT_NAME,
                        T2.PARTICIPANT,
                        T2.ACTIVITYDEFNAME,
                        T1.COMPLETEDDATE,
                        T3.ccid,
                        T3.DEAL_STATE_KEY,
                        T3.ACTIVITYDEFNAME AS CC_NODE_NAME,
                        T3.ACCT_NM AS CUR_DEAL_USER,
                        T1.PROCESSINSTANCEID,
                        T1.PROCESSDEFINITIONID,
                        T1.WFCREATOR,
                        T1.STATE,
                        T2.ACTIVITYDEFINITIONID
                FROM
                (
                        SELECT DISTINCT
                                C.WORKSHEETID,
                                A.PROCESSINSTANCEID,
                                A.PROCESSINSTNAME,
                                B.WFCREATOR,
                                B.PROCESSDEFINITIONID,
                                B.CREATEDDATE,
                                B.COMPLETEDDATE,
                                B.STATE,
                                D.ACCT_NM AS WFCREATOR_NAME,
                                T.WFTYPE_NAME,
                                S.STATE_DESC
                        FROM
                                IWF_WORKITEM A,
                                IWF_PROCESSINSTANCE B,
                                ITSM_PROINSTANCE_WORKSHEET_REL C,
                                SYS_ACCOUNT D,
                                ITSM_WF_TYPE T,
                                ITSM_WF_STATE S
                        WHERE
                                A.PROCESSINSTANCEID = B.PROCESSINSTANCEID
                        AND B.PROCESSINSTANCEID = C.PROCESSINSTANCEID
                        AND B.WFCREATOR = D.ACCT_ID
                        AND C.WORKSHEETTYPE = T.WFTYPE_NO
                        AND B.STATE = S.STATE_KEY
                        AND S.STATE_TYPE = 1
                        GROUP BY
                                C.WORKSHEETID
                ) T1
                LEFT JOIN (
                        SELECT
                                A1.PROCESSINSTANCEID,
                                A1.ACTIVITYDEFINITIONID,
                                A1.ACTIVITYDEFNAME,
                GROUP_CONCAT(A1.PARTICIPANT SEPARATOR ',') AS PARTICIPANT,
                                CASE
                        WHEN W.SCRAMBLE_NODE IS NOT NULL
                        OR W.SCRAMBLE_NODE = '' THEN
                                (
                                        SELECT
                                                ROLE_NM
                                        FROM
                                                SYS_ROLE
                                        WHERE
                                                ROLE_ID = W.SCRAMBLE_ROLE
                                )
                        ELSE
                                GROUP_CONCAT(B1.ACCT_NM SEPARATOR ',')
                        END AS PARTICIPANT_NAME
                        FROM
                                IWF_WORKITEM A1
                        LEFT JOIN SYS_ACCOUNT B1 ON A1.PARTICIPANT = B1.ACCT_ID
                        LEFT JOIN ITSM_WORKFLOW_DEFINITION W ON (
                                A1.ACTIVITYDEFINITIONID = W.SCRAMBLE_NODE
                                AND W.WORKFLOW_ID = A1.PROCESSDEFINITIONID
                        )
                        WHERE
                                A1.STATE IN (1, 2)
                        AND A1.NEXTWORKITEMID IS NULL
                        GROUP BY
                                A1.PROCESSINSTANCEID
                ) T2 ON T1.PROCESSINSTANCEID = T2.PROCESSINSTANCEID
                INNER JOIN (
                        SELECT DISTINCT
                                A.ccid,
                                A.instanceid,
                                A.DEAL_STATE_KEY,
                                B.ACTIVITYDEFNAME,
                                C.ACCT_NM
                        FROM
                                itsm_cc_record AS A
                        LEFT JOIN iwf_workitem AS B ignore index(PROCESSINSTANCEID) ON A.instanceid = B.PROCESSINSTANCEID
                        AND A.ACTIVITYDEFINITIONID = B.ACTIVITYDEFINITIONID
                        LEFT JOIN sys_account AS C ON A.CUR_DEAL_USER = C.acct_id
                        WHERE
                                A.userid = '10000222'
                ) AS T3 ON T3.instanceid = T1.PROCESSINSTANCEID
                ORDER BY
                        T1.CREATEDDATE DESC
                        LIMIT 0, 200;

查看执行计划

###explain ...

+----+--------------------+------------+------------+--------+-------------------------------------------------+-------------------+---------+------------------------------+-------+----------+--------------------------------------------+
| id | select_type        | table      | partitions | type   | possible_keys                                   | key               | key_len | ref                          | rows  | filtered | Extra                                      |
+----+--------------------+------------+------------+--------+-------------------------------------------------+-------------------+---------+------------------------------+-------+----------+--------------------------------------------+
|  1 | PRIMARY            | <derived2> | NULL       | ALL    | NULL                                            | NULL              | NULL    | NULL                         |  4335 |   100.00 | Using where; Using filesort                |
|  1 | PRIMARY            | <derived3> | NULL       | ref    | <auto_key0>                                     | <auto_key0>       | 387     | T1.PROCESSINSTANCEID         |    10 |   100.00 | NULL                                       |
|  1 | PRIMARY            | <derived5> | NULL       | ref    | <auto_key0>                                     | <auto_key0>       | 153     | T1.PROCESSINSTANCEID         |   358 |   100.00 | Using where                                |
|  5 | DERIVED            | A          | NULL       | ALL    | NULL                                            | NULL              | NULL    | NULL                         |  1044 |    10.00 | Using where; Using temporary               |
|  5 | DERIVED            | B          | NULL       | ALL    | NULL                                            | NULL              | NULL    | NULL                         | 14894 |   100.00 | Using where; Using join buffer (hash join) |
|  5 | DERIVED            | C          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | cjccc.A.CUR_DEAL_USER        |     1 |   100.00 | Using where                                |
|  3 | DERIVED            | A1         | NULL       | ALL    | PROCESSINSTANCEID                               | NULL              | NULL    | NULL                         | 14894 |     2.00 | Using where; Using filesort                |
|  3 | DERIVED            | B1         | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | cjccc.A1.PARTICIPANT         |     1 |   100.00 | Using where                                |
|  3 | DERIVED            | W          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 98      | cjccc.A1.PROCESSDEFINITIONID |     1 |   100.00 | Using where                                |
|  4 | DEPENDENT SUBQUERY | SYS_ROLE   | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | func                         |     1 |   100.00 | Using where                                |
|  2 | DERIVED            | S          | NULL       | ref    | PRIMARY                                         | PRIMARY           | 4       | const                        |     7 |   100.00 | Using temporary                            |
|  2 | DERIVED            | B          | NULL       | ALL    | PRIMARY,key_wfcreator,key_processinstanceid     | NULL              | NULL    | NULL                         |  1052 |    10.00 | Using where; Using join buffer (hash join) |
|  2 | DERIVED            | D          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 194     | cjccc.B.WFCREATOR            |     1 |   100.00 | Using index condition                      |
|  2 | DERIVED            | C          | NULL       | eq_ref | PRIMARY,key_worksheettype,key_processinstanceid | PRIMARY           | 386     | cjccc.B.PROCESSINSTANCEID    |     1 |   100.00 | NULL                                       |
|  2 | DERIVED            | T          | NULL       | eq_ref | PRIMARY                                         | PRIMARY           | 4       | cjccc.C.WORKSHEETTYPE        |     1 |   100.00 | NULL                                       |
|  2 | DERIVED            | A          | NULL       | ref    | PROCESSINSTANCEID                               | PROCESSINSTANCEID | 387     | cjccc.B.PROCESSINSTANCEID    |     5 |   100.00 | NULL                                       |
+----+--------------------+------------+------------+--------+-------------------------------------------------+-------------------+---------+------------------------------+-------+----------+--------------------------------------------+
16 rows in set, 3 warnings (0.00 sec)
###explain format = tree ...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 200 row(s)
    -> Nested loop inner join
        -> Nested loop left join
            -> Sort: t1.CREATEDDATE DESC
                -> Filter: (t1.PROCESSINSTANCEID is not null)
                    -> Table scan on T1
                        -> Materialize
                            -> Table scan on <temporary>
                                -> Temporary table with deduplication
                                    -> Nested loop inner join  (cost=1306.47 rows=434)
                                        -> Nested loop inner join  (cost=179.33 rows=74)
                                            -> Nested loop inner join  (cost=145.67 rows=74)
                                                -> Nested loop inner join  (cost=112.01 rows=74)
                                                    -> Inner hash join (b.STATE = s.state_key)  (cost=78.34 rows=74)
                                                        -> Filter: (b.WFCREATOR is not null)  (cost=2.04 rows=105)
                                                            -> Table scan on B  (cost=2.04 rows=1052)
                                                        -> Hash
                                                            -> Index lookup on S using PRIMARY (state_type=1)  (cost=0.95 rows=7)
                                                    -> Single-row index lookup on D using PRIMARY (acct_id=b.WFCREATOR), with index condition: (b.WFCREATOR = d.acct_id)  (cost=0.04 rows=1)
                                                -> Single-row index lookup on C using PRIMARY (PROCESSINSTANCEID=b.PROCESSINSTANCEID)  (cost=0.04 rows=1)
                                            -> Single-row index lookup on T using PRIMARY (wftype_no=c.WORKSHEETTYPE)  (cost=0.04 rows=1)
                                        -> Index lookup on A using PROCESSINSTANCEID (PROCESSINSTANCEID=b.PROCESSINSTANCEID)  (cost=1.47 rows=6)
            -> Index lookup on T2 using <auto_key0> (PROCESSINSTANCEID=t1.PROCESSINSTANCEID)
                -> Materialize
                    -> Group aggregate: group_concat(b1.acct_nm separator ','), group_concat(a1.PARTICIPANT separator ',')
                        -> Nested loop left join  (cost=4818.02 rows=14894)
                            -> Nested loop left join  (cost=3141.52 rows=14894)
                                -> Sort: a1.PROCESSINSTANCEID  (cost=1577.65 rows=14894)
                                    -> Filter: ((a1.STATE in (1,2)) and (a1.NEXTWORKITEMID is null))
                                        -> Table scan on A1
                                -> Filter: (a1.PARTICIPANT = b1.acct_id)  (cost=0.25 rows=1)
                                    -> Single-row index lookup on B1 using PRIMARY (acct_id=a1.PARTICIPANT)  (cost=0.25 rows=1)
                            -> Filter: ((a1.ACTIVITYDEFINITIONID = w.SCRAMBLE_NODE) and (w.WORKFLOW_ID = a1.PROCESSDEFINITIONID))  (cost=0.63 rows=1)
                                -> Single-row index lookup on W using PRIMARY (WORKFLOW_ID=a1.PROCESSDEFINITIONID)  (cost=0.63 rows=1)
                    -> Select #4 (subquery in projection; dependent)
                        -> Filter: (sys_role.role_id = w.SCRAMBLE_ROLE)  (cost=0.35 rows=1)
                            -> Single-row index lookup on SYS_ROLE using PRIMARY (role_id=w.SCRAMBLE_ROLE)  (cost=0.35 rows=1)
        -> Filter: (t3.instanceid = t1.PROCESSINSTANCEID)
            -> Index lookup on T3 using <auto_key0> (instanceid=t1.PROCESSINSTANCEID)
                -> Materialize
                    -> Table scan on <temporary>
                        -> Temporary table with deduplication
                            -> Nested loop left join  (cost=311117.74 rows=1554934)
                                -> Left hash join (a.instanceid = b.PROCESSINSTANCEID), (a.ACTIVITYDEFINITIONID = b.ACTIVITYDEFINITIONID)  (cost=155598.28 rows=1554934)
                                    -> Filter: (a.userid = '10000222')  (cost=106.65 rows=104)
                                        -> Table scan on A  (cost=106.65 rows=1044)
                                    -> Hash
                                        -> Table scan on B  (cost=15.13 rows=14894)
                                -> Filter: (a.CUR_DEAL_USER = c.acct_id)  (cost=0.00 rows=1)
                                    -> Single-row index lookup on C using PRIMARY (acct_id=a.CUR_DEAL_USER)  (cost=0.00 rows=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

#####chenjuchao 2021-11-30 21:30#####
欢迎关注我的公众号《IT小Chen

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值