问题现象：
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 || NULL | ALL | NULL | NULL | NULL | NULL | 4335 | 100.00 | Using where; Using filesort | | 1 | PRIMARY | | NULL | ref | | | 387 | T1.PROCESSINSTANCEID | 10 | 100.00 | NULL | | 1 | PRIMARY | | NULL | ref | | | 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 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 (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 (instanceid=t1.PROCESSINSTANCEID) -> Materialize -> Table scan on -> 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 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 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 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 || NULL | ALL | NULL | NULL | NULL | NULL | 4335 | 100.00 | Using where; Using filesort | | 1 | PRIMARY | | NULL | ref | | | 387 | T1.PROCESSINSTANCEID | 10 | 100.00 | NULL | | 1 | PRIMARY | | NULL | ref | | | 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 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 (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 (instanceid=t1.PROCESSINSTANCEID) -> Materialize -> Table scan on -> 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#####