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#####
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2845009/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle 某行系統SQL優化案例(一)OracleSQL優化
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- 【MySQL】NOT EXISTS優化的一個案例MySql優化
- MySQL之SQL優化詳解（一）MySql優化
- SQL優化案例-使用with as優化Subquery Unnesting（七）SQL優化
- MySQL-SQL優化MySql優化
- SQL優化案例-union代替or（九）SQL優化
- SQL優化之多表關聯查詢-案例一SQL優化
- MYSQL SQL語句優化MySql優化
- MySQL之SQL優化技巧MySql優化
- SQL Server一次SQL調優案例SQLServer
- MySQL之SQL語句優化MySql優化
- Mysql慢SQL分析及優化MySql優化
- MYSQL資料庫------SQL優化MySql資料庫優化
- Oracle某行系統SQL優化案例(三)OracleSQL優化
- Oracle某行系統SQL優化(案例五)OracleSQL優化
- Oracle某行系統SQL優化案例(二)OracleSQL優化
- SQL優化案例-單表分頁語句的優化（八）SQL優化
- MySQL案例05：CPU負載優化MySql負載優化
- MySQL 資料庫與 SQL 優化MySql資料庫優化
- 十七、Mysql之SQL優化查詢MySql優化
- MySQL之SQL優化詳解（二）MySql優化
- MySQL之SQL優化詳解（三）MySql優化
- MySQL的SQL效能優化總結MySql優化
- SQL優化案例-自定義函式索引（五）SQL優化函式索引
- SQL優化案例-正確的使用索引（二）SQL優化索引
- mysql優化（一）MySql優化
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- Mysql索引優化（一）MySql索引優化
- Mysql效能優化一MySql優化
- MySQL 52個SQL效能優化策略SQL語句彙總MySql優化
- mysql優化 | 儲存引擎，建表，索引，sql的優化建議MySql優化儲存引擎索引
- MySQL效能優化之簡單sql改寫MySql優化
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引