问题现象：
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》
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
