我在有许多嵌套在子句中的查询中遇到缓慢的问题。
查询如下：
select SA0_.sid as stid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid=19)))))))))));
我已经检查了相同的解释，发现如下：
+----+--------------+-------------------+------------+------------+----------------------------------------+---------------------+---------+----------------------------------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------------+------------+------------+----------------------------------------+---------------------+---------+----------------------------------------------------+------+----------+--------------------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where |
| 1 | SIMPLE | SA0_ | NULL | ref | table_starea_ix01 | table_starea_ix01 | 5 | <subquery2>.sid | 1 | 100.00 | Using index |
| 1 | SIMPLE | AC1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SA0_.acid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | AC0_ | NULL | ref | table_arstaff_ix01,tab_attach_fk2 | table_arstaff_ix01 | 4 | const | 4 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | SB1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.AC0_.sid | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | SG2_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SB1_.sgid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | SA0_ | NULL | ref | FKFA890976F203761F,table_starea_ix01 | table_starea_ix01 | 5 | ain2013Realdb.AC0_.sid | 1 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | AC1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SA0_.acid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | AC0_ | NULL | ref | table_arstaff_ix01,tab_attach_fk2 | table_arstaff_ix01 | 4 | ain2013Realdb.SA0_.acid | 2 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | SB1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.AC0_.sid | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | SG2_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SB1_.sgid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | SA0_ | NULL | ref | FKFA890976F203761F,table_starea_ix01 | table_starea_ix01 | 5 | ain2013Realdb.AC0_.sid | 1 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | AC1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SA0_.acid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | AC0_ | NULL | ref | table_arstaff_ix01,tab_attach_fk2 | table_arstaff_ix01 | 4 | ain2013Realdb.SA0_.acid | 2 | 100.00 | Using index |
| 2 | MATERIALIZED | SB1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.AC0_.sid | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | SG2_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SB1_.sgid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | SA0_ | NULL | ref | FKFA890976F203761F,table_starea_ix01 | table_starea_ix01 | 5 | ain2013Realdb.AC0_.sid | 1 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | AC1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SA0_.acid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | AC0_ | NULL | ref | table_arstaff_ix01,tab_attach_fk2 | table_arstaff_ix01 | 4 | ain2013Realdb.SA0_.acid | 2 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | SB1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.AC0_.sid | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | SG2_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SB1_.sgid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | SA0_ | NULL | ref | FKFA890976F203761F,table_starea_ix01 | table_starea_ix01 | 5 | ain2013Realdb.AC0_.sid | 1 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | AC1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SA0_.acid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | AC0_ | NULL | ref | table_arstaff_ix01,tab_attach_fk2 | table_arstaff_ix01 | 4 | ain2013Realdb.SA0_.acid | 2 | 100.00 | Using index |
| 2 | MATERIALIZED | SB1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.AC0_.sid | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | SG2_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SB1_.sgid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | SA0_ | NULL | ref | FKFA890976F203761F,table_starea_ix01 | table_starea_ix01 | 5 | ain2013Realdb.AC0_.sid | 1 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | AC1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SA0_.acid | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | AC0_ | NULL | ref | table_arstaff_ix01,tab_attach_fk2 | table_arstaff_ix01 | 4 | ain2013Realdb.SA0_.acid | 2 | 100.00 | Using index |
| 2 | MATERIALIZED | SB1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.AC0_.sid | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | SG2_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ain2013Realdb.SB1_.sgid | 1 | 100.00 | Using index |
+----+--------------+-------------------+------------+------------+----------------------------------------+---------------------+---------+----------------------------------------------------+------+----------+--------------------------+
任何人都可以建议一个解决方案来使这个查询快速执行吗？
我使用 INNER JOIN 重写了查询，如下所示：
从 SA0_.acid=AC1_.acid table_arcons AC1_table_starea SA0_左侧外部连接中选择 SA0_.sid 作为 STID，其中 SA0_.sid 在 (SB1_从 AC0_.sid=SB1_.sid 上选择table_arstaff AC0_左侧外部连接table_stf SB1_中选择不同的 SID AC0_.SID SB1_.sgid=SG2_.sgid 上的左侧外部连接table_stfgr SG2_( TAB19 内部连接(从 ((选择 AC1_.acid，SA0_.sid 从 SA0_.acid=AC1_.acid 上table_starea SA0_左外连接table_arcons AC1_( TAB17 内部连接 (从 ((选择 SB1_.sid， AC0_.acid table_arstaff AC0_从 AC0_.sid=SB1_.table_stfgr SG2_sid 的左外连接中选择 table_stf SB1_ SB1_.sgid=SG2_.sgid( TAB15 内部连接 (从 ((选择 AC1_.酸， SA0_.sid 从 table_starea SA0_ 左外连接table_arcons AC1_ SA0_.acid=AC1_.acid( TAB13 内部连接 (从 ((选择 SB1_.sid， AC0_.acid 从 AC0_.sid=SB1_.sid 的左table_stfgr SG2_外table_stf SB1_连接中选择 table_arstaff AC0_ SB1_.sgid=SG2_.sgid( TAB11 内部连接 (从 ((选择 AC1_.酸， SA0_.sid 从 SA0_.acid=AC1_.acid 上的table_arcons AC1_table_starea SA0_左外连接( TAB9 内部连接 (从 ((选择 SB1_.sid， AC0_.acid 从 AC0_.sid=SB1_.sid 的左外连接中选择 table_arstaff AC0_ 左外table_stf SB1_连接 table_stfgr SG2_ 在 SB1_.sgid=SG2_.sgid( TAB7 内部连接 (从 ((选择 AC1_.酸， SA0_.sid 从 table_starea SA0_ 左外连接 table_arcons AC1_ 在 SA0_.acid=AC1_.acid( TAB5 内部连接 (SB1_从 AC0_.sid=SB1_.sid 上选择 AC0_.acid table_arstaff AC0_左侧外部连接table_stf SB1_ .sid=.sid 左外连接 table_stfgr SG2_ 在 SB1_.sgid=SG2_.sgid( TAB3 内部连接(从(选择 AC1_.acid， SA0_.sid 从 table_starea SA0_ 左外连接table_arcons AC1_ 上 SA0_.acid=AC1_.acid( TAB2 内部连接(从左外连接中选择 SB1_.sid table_arstaff AC0_ table_stf SB1_ 在 AC0_.sid=SB1_.sid 上选择 SB1_.sgid=SG2_.sgid 上的左外连接table_stfgr SG2_，其中 AC0_.acid=19( TAB1 在 TAB2.sid = TAB1.sid( TAB4 在 TAB3.acid = TAB4.acid( TAB6 在 TAB5.sid = TAB6.sid(( TAB8 在 TAB7.acid = TAB8.acid(( TAB10 在 TAB9.sid = TAB10.sid(( TAB12 上TAB11.acid = TAB12.acid(( TAB14 on TAB13.sid = TAB14.sid(( TAB16 on TAB15.acid = TAB16.acid(( TAB18 on TAB17.sid = TAB18.sid(( TAB20 on TAB19.acid = TAB20.acid((;
这会带来优化吗？
性能的第一个建议.. 每个 IN 子句都可以轻松更改大多数性能 内部连接子句，例如为您更深的嵌套级别，您可以更改代码
....
select AC1_.acid
from table_starea SA0_
left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid
where SA0_.sid in (
select SB1_.sid
from table_arstaff AC0_
left outer join table_stf SB1_ on AC0_.sid=SB1_.sid
left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid
where AC0_.acid=19 )
....)))))))));
跟
......
select AC1_.acid
from table_starea SA0_
left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid
) t2 on AC0_.acid = t2.acid
INNER JOIN (
select SB1_.sid
from table_arstaff AC0_
left outer join table_stf SB1_ on AC0_.sid=SB1_.sid
left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid
where AC0_.acid=19
) t1 on t1.sid = SA0_.sid
....)))))))));
Resul 是相同的，但内部连接应该更快..
你可以从更深层次开始重构你的代码，然后一个接一个地重构所有IN子句，并带有相应的INNER JOIN来重构所有IN子句