Mysql Join语句查询的优化
Mysql Join语句查询的优化
提问于 2014-03-03 19:40:37
SELECT GV.ID
, XS.SymbolId
, GS.ID
, XS.SymbolExchangeId
, XS.IssueId
, GE.ID
, XD.ACTIVE
, XD.ExchangeId
FROM TB_GDS_SECURITY GS
, WSOD_Vanilla.WSOD_XrefIssueSymbols XS
, WSOD_Vanilla.WSOD_XrefIssueData XD
, TB_GDS_EXCHANGE GE
, TB_GDS_VENDOR GV
WHERE XD.CompositeIssueID = GS.ISSUE_ID_TEMP
AND XD.IssueId = XS.IssueId
AND XD.ACTIVE = 'True'
AND GV.VENDOR_SHORT_NAME = XS.SymbolsetId
AND GE.EXCHANGE_SHORT_NAME = XD.ExchangeId
AND NOT EXISTS (SELECT ID
FROM TB_GDS_VENDOR_VENUE_SYMBOL VVS
WHERE VVS.ISSUE_ID = XS.IssueId
AND (SELECT ID
FROM TB_GDS_VENDOR GV
WHERE GV.VENDOR_SHORT_NAME = XS.SymbolsetId
)
);这个特定的查询需要一分钟以上的时间。我希望它能在较短的时间内完成。
explain语句如下：
mysql> EXPLAIN SELECT GV.ID, XS.SymbolId, GS.ID, XS.SymbolExchangeId, XS.IssueId, GE.ID, XD.ACTIVE, XD.ExchangeId FROM TB_GDS_SECURITY GS, WSOD_Vanilla.WSOD_XrefIssueSymbols XS, WSOD_Vanilla.WSOD_XrefIssueData XD, TB_GDS_EXCHANGE GE, TB_GDS_VENDOR GV WHERE XD.CompositeIssueID = GS.ISSUE_ID_TEMP AND XD.IssueId = XS.IssueId AND XD.ACTIVE = 'True' AND GV.VENDOR_SHORT_NAME=XS.SymbolsetId AND GE.EXCHANGE_SHORT_NAME = XD.ExchangeId AND NOT exists (SELECT ID FROM TB_GDS_VENDOR_VENUE_SYMBOL VVS
-> WHERE VVS.ISSUE_ID = XS.IssueId AND (SELECT ID FROM TB_GDS_VENDOR GV WHERE GV.VENDOR_SHORT_NAME = XS.SymbolsetId));
+----+--------------------+-------+------+--------------------------------------------------------+-----------------------------+---------+----------------------------------+---------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+--------------------------------------------------------+-----------------------------+---------+----------------------------------+---------+------------------------------------+
| 1 | PRIMARY | XD | ref | WSOD_XrefIssueData_UINDX1,WSOD_XrefIssueData_INDX1 | WSOD_XrefIssueData_INDX1 | 21 | const | 3981788 | Using index condition; Using where |
| 1 | PRIMARY | GE | ref | TB_GDS_EXCHANGE_INDX1 | TB_GDS_EXCHANGE_INDX1 | 63 | WSOD_Vanilla.XD.ExchangeId | 1 | Using where; Using index |
| 1 | PRIMARY | GS | ref | TB_GDS_SECURITY_INDX1 | TB_GDS_SECURITY_INDX1 | 5 | WSOD_Vanilla.XD.CompositeIssueID | 1 | Using where; Using index |
| 1 | PRIMARY | XS | ref | PRIMARY | PRIMARY | 8 | WSOD_Vanilla.XD.IssueId | 1 | Using where |
| 1 | PRIMARY | GV | ref | TB_GDS_VENDOR_INDX1 | TB_GDS_VENDOR_INDX1 | 62 | WSOD_Vanilla.XS.SymbolsetId | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | VVS | ref | TB_GDS_VVENUE_SYMBOL_UINDX1,TB_GDS_VVENUE_SYMBOL_INDX2 | TB_GDS_VVENUE_SYMBOL_UINDX1 | 5 | WSOD_Vanilla.XS.IssueId | 3 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | GV | ref | TB_GDS_VENDOR_INDX1 | TB_GDS_VENDOR_INDX1 | 62 | WSOD_Vanilla.XS.SymbolsetId | 1 | Using where; Using index |
+----+--------------------+-------+------+--------------------------------------------------------+-----------------------------+---------+----------------------------------+---------+------------------------------------+
7 rows in set (0.01 sec)优化后，仍然需要大约1.35分钟的时间来执行。请帮帮我。
回答 1
Stack Overflow用户
发布于 2014-03-03 19:54:12
根据其他地方的评论，您还没有向我们展示索引定义。
这里最有可能的沼泽成本是NOT EXISTS子句。MySQL不能很好地处理推式预测，而且你也有一个笛卡尔乘积。我很难理解这到底是做什么用的。MySQL (实际上没有我熟悉的数据库管理系统)可以有效地将索引用于NOT EXISTS、<>或NOT LIKE。嵌套的嵌套查询似乎没有任何功能，因为您已经在外部查询中的这些表之间进行了显式联接：
WHERE...AND GV.VENDOR_SHORT_NAME = XS.SymbolsetId...
AND NOT EXISTS ( [a condition] AND
SELECT ID FROM TB_GDS_VENDOR GV
WHERE GV.VENDOR_SHORT_NAME = XS.SymbolsetId除了连接和这个丑陋的不存在之外，你唯一的过滤是"AND XD.ACTIVE = 'True'“-如果这个列只有2/3的值，那么使用索引的效率可能会非常低。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22145849
复制相关文章
相似问题
腾讯云开发者
