如何优化这个MySQL查询

Question

有人能帮我优化这个查询吗？

SELECT(X1,      
    X2   
FROM TABLEAA   
 WHERE   
        Y IN (SELECT Y FROM TABLEBB WHERE Z=SELECTED)   
    AND Y  IN  (SELECT Y  FROM TABLECC  WHERE ZZ=SELECTED)    
)

作为

TABLEAA : 1 million enteries    
TABLEBB : 22 million enteries    
TABLECC : 1.2 million enteries  

它能够工作，但是需要太长时间，几乎要30秒

有没有其他更好的方法？

编辑：Z和ZZ是完全不同的两列

- wiki

---

当然，将其更改为使用连接和正确的列索引将减少执行时间。 - Cᴏʀʏ

有谁能在没有主键/外键的情况下创建或维护一个拥有数百万条目的表呢？ - wildplasser

只是好奇，我们能否在@wiki上发布有关运行时间改进的文章？ - Dan Ciborowski - MSFT

Answer 1

我会使用 JOIN：

SELECT DISTINCT
    A.X1,      
    A.X2   
FROM TABLEAA A
   JOIN TABLEBB B ON A.Y = B.Y AND B.Z='SELECTED'
   JOIN TABLECC C ON A.Y = C.Y AND C.Z='SELECTED'

此外，确保在A.Y、B.Y和C.Y上有适当的索引。通过在Z列上添加索引（取决于表结构和其他几个因素），您可能会获得更好的性能。

- sgeddes

Answer 2

不要使用子查询，而是将TABLEBB和TABLECC与TABLEAA连接，并在WHERE子句中检查ZZ=SLECTED，对于两个连接的表都是如此。

确保参与外连接的列已经建立索引。

- Robert Harvey

---

优化器不会以同样的方式优化子查询吗？ - zerkms

http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ --- 即使是三年前也可以，不是吗？ - zerkms

@zerkms：我没有读完整篇文章，但也许有些列是可空的。我认为这并不像你想象的那么简单；子查询以速度较慢而闻名于世，与其连接的对应查询则相对快速。 - Robert Harvey

nullability 不应影响 IN（我已经好几年没用 SQL Server 了，但如果它不能很好地优化 IN (subquery)，我会感到惊讶的）。而且在这种情况下，子查询甚至都不相关。 - zerkms

Answer 3

SELECT X1, X2 FROM TABLEAA 
JOIN TABLEBB ON Y = Y JOIN TABLECC ON Y = Y 
WHERE TABLEBB.Z = SLECTED && TABLECC.ZZ = SLECTED

- A.O.

Answer 4

索引...

• 在TABLEBB中为Z添加索引
• 在TABLECC中为ZZ添加索引
• 在TABLEAA中为Y添加索引

- D Stanley

---

实际上，对于 TABLEBB，建立一个复合索引 (Z, Y) 是个好主意。对于 TABLECC 也是一样。 - zerkms

不确定这是否有帮助，因为它仍然需要扫描每一行，其中 Z = SLECTED。但我可能是错的。 - D Stanley

@D Stanley：如果你有Z索引 - 数据库管理系统首先找到行ID，然后提取数据。如果索引中已经包含了所有需要的数据 - 它就不需要在数据上执行查找操作（因为数据已经在索引中可用）。 - zerkms

聪明 - 我会在 MS-SQL 中使用 INCLUDE 来实现这个。 - D Stanley

@D Stanley：哦，这是一个不错的扩展，我不知道（我也不用 SQL ；-）但可能值得在答案中提一下。 - zerkms