查詢一：

select distinct email from mybigtable where account_id=345

耗時 0.1s

查詢 2：

Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above result>)

耗時 0.2s

查詢 3：

Select count(*) as total from mybigtable where account_id=123 and email IN (select distinct email from mybigtable where account_id=345)

需要 22 分鐘，90% 處於“準備”狀態。為什麼這需要這麼多時間。

表是 innodb，在 MySQL 5.0 上有 320 萬行

在查詢 3 中，您基本上是針對自己的 mybigtable 的每一行執行一個子查詢。

為避免這種情況，您需要進行兩項重大更改：

重大變化 #1：重構查詢

這是您的原始查詢

Select count(*) as total from mybigtable
where account_id=123 and email IN
(select distinct email from mybigtable where account_id=345)

你可以試試

select count(*) EmailCount from
(
   select tbl123.email from
   (select email from mybigtable where account_id=123) tbl123
   INNER JOIN
   (select distinct email from mybigtable where account_id=345) tbl345
   using (email)
) A;

或者每封電子郵件的計數

select email,count(*) EmailCount from
(
   select tbl123.email from
   (select email from mybigtable where account_id=123) tbl123
   INNER JOIN
   (select distinct email from mybigtable where account_id=345) tbl345
   using (email)
) A group by email;

主要變化#2：正確的索引

我想你已經有了這個，因為查詢 1 和查詢 2 執行得很快。確保您在 (account_id,email) 上有一個複合索引。做SHOW CREATE TABLE mybigtable\G並確保你有一個。如果你沒有它或者你不確定，那麼無論如何都要創建索引：

ALTER TABLE mybigtable ADD INDEX account_id_email_ndx (account_id,email);

更新 2012-03-07 13:26 EST

如果要執行 NOT IN()，請將其更改INNER JOIN為 aLEFT JOIN並檢查右側是否為 NULL，如下所示：

select count(*) EmailCount from
(
   select tbl123.email from
   (select email from mybigtable where account_id=123) tbl123
   LEFT JOIN
   (select distinct email from mybigtable where account_id=345) tbl345
   using (email)
   WHERE tbl345.email IS NULL
) A;

更新 2012-03-07 14:13 EST

請閱讀這兩個關於做 JOIN 的連結

• http://en.wikipedia.org/wiki/Sql_join
• http://dev.mysql.com/doc/refman/5.5/en/join.html

這是一個很棒的 YouTube 影片，在那裡我學會了重構查詢和它所基於的書

• http://youtu.be/ZVisY-fEoMw
• http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/B006TR0ZEO

引用自：https://dba.stackexchange.com/questions/14565