mysql> select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

為了節省篇幅，省略了輸出內容，下同。

67 rows in set (12.00 sec)

只有67行數據返回，卻花了12秒，而系統中可能同時會有很多這樣的查詢，系統肯定扛不住。用desc看一下(注：explain也可)

mysql> desc select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839'); 
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
| id | select_type        | table            | type   | possible_keys   | key   | key_len | ref        | rows    | Extra                    |
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
|  1 | PRIMARY            | abc_number_prop  | ALL    | NULL            | NULL  | NULL    | NULL       | 2679838 | Using where              |
|  2 | DEPENDENT SUBQUERY | abc_number_phone | eq_ref | phone,number_id | phone | 70      | const,func |       1 | Using where; Using index |
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
2 rows in set (0.00 sec)

從上面的信息可以看出，在執行此查詢時會掃描兩百多萬行，難道是沒有創建索引嗎，看一下

mysql> show index from abc_number_phone; 
+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name    | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abc_number_phone |          0 | PRIMARY     |            1 | number_phone_id | A         |       36879 |     NULL | NULL   |      | BTREE      |         |               |
| abc_number_phone |          0 | phone       |            1 | phone           | A         |       36879 |     NULL | NULL   |      | BTREE      |         |               |
| abc_number_phone |          0 | phone       |            2 | number_id       | A         |       36879 |     NULL | NULL   |      | BTREE      |         |               |
| abc_number_phone |          1 | number_id   |            1 | number_id       | A         |       36879 |     NULL | NULL   |      | BTREE      |         |               | 
| abc_number_phone |          1 | created_by  |            1 | created_by      | A         |       36879 |     NULL | NULL   |      | BTREE      |         |               |
| abc_number_phone |          1 | modified_by |            1 | modified_by     | A         |       36879 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.06 sec)

mysql> show index from abc_number_prop; 
+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name    | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abc_number_prop |          0 | PRIMARY     |            1 | number_prop_id | A         |      311268 |     NULL | NULL   |      | BTREE      |         |               |
| abc_number_prop |          1 | number_id   |            1 | number_id      | A         |      311268 |     NULL | NULL   |      | BTREE      |         |               | 
| abc_number_prop |          1 | created_by  |            1 | created_by     | A         |      311268 |     NULL | NULL   |      | BTREE      |         |               |
| abc_number_prop |          1 | modified_by |            1 | modified_by    | A         |      311268 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.15 sec)

從上面的輸出可以看出，這兩張表在number_id字段上創建了索引的。

看看子查詢本身有沒有問題。

mysql> desc select number_id from abc_number_phone where phone = '82306839'; 
+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table            | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | abc_number_phone | ref  | phone         | phone | 66      | const |    6 | Using where; Using index |
+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

沒有問題，只需要掃描幾行數據，索引起作用了。查詢出來看看

mysql> select number_id from abc_number_phone where phone = '82306839'; 
+-----------+
| number_id |
+-----------+
|      8585 |
|     10720 |
|    148644 |
|    151307 |
|    170691 |
|    221897 |
+-----------+
6 rows in set (0.00 sec)

直接把子查詢得到的數據放到上面的查詢中

mysql> select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897);

67 rows in set (0.03 sec)

速度也快，看來MySQL在處理子查詢的時候是不夠好。我在MySQL 5.1.42 和 MySQL 5.5.19 都進行了嘗試，都有這個問題。

搜索了一下網絡，發現很多人都遇到過這個問題：

參考資料1：使用連接（JOIN）來代替子查詢(Sub-Queries) mysql優化系列記錄
http://blog.csdn.net/hongsejiaozhu/article/details/1876181
參考資料2：網站開發日記(14)-MYSQL子查詢和嵌套查詢優化
http://dodomail.iteye.com/blog/250199

根據網上這些資料的建議，改用join來試試。

修改前：select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

修改后：select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';

mysql> select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';

67 rows in set (0.00 sec)

效果不錯，查詢所用時間幾乎為0。看一下MySQL是怎么執行這個查詢的

mysql> desc select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839'; 
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys   | key       | key_len | ref             | rows | Extra                    |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
|  1 | SIMPLE      | b     | ref  | phone,number_id | phone     | 66      | const           |    6 | Using where; Using index |
|  1 | SIMPLE      | a     | ref  | number_id       | number_id | 4       | eap.b.number_id |    3 |                          |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
2 rows in set (0.00 sec)

小結：當子查詢速度慢時，可用JOIN來改寫一下該查詢來進行優化。

網上也有文章說，使用JOIN語句的查詢不一定總比使用子查詢的語句快。

參考資料3：改變了對Mysql子查詢的看法
http://hi.baidu.com/yzx110/blog/item/e694f536f92075360b55a92b.html

正好手頭有本《高性能MySQL》，翻閱了一下，第4.4節“MySQL查詢優化器的限制”4.4.1小節“關聯子查詢”正好講到這個問題。

MySQL有時優化子查詢很差，特別是在WHERE從句中的IN()子查詢。像上面我碰到的情況，其實我的想法是MySQL會把

select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

變成下面的樣子

select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897);

但不幸的是，實際情況正好相反。MySQL試圖讓它和外面的表產生聯系來“幫助”優化查詢，它認為下面的exists形式更有效率

select * from abc_number_prop where exists (select * from abc_number_phone where phone = '82306839' and number_id = abc_number_prop.number_id);

mysql> select * from abc_number_prop where exists (select * from abc_number_phone where phone = '82306839' and number_id = abc_number_prop.number_id);

67 rows in set (10.89 sec)

mysql> desc select * from abc_number_prop where exists (select * from abc_number_phone where phone = '82306839' and number_id = abc_number_prop.number_id); 
+----+--------------------+------------------+--------+-----------------+-------+---------+-------------------------------------+---------+--------------------------+
| id | select_type        | table            | type   | possible_keys   | key   | key_len | ref                                 | rows    | Extra                    |
+----+--------------------+------------------+--------+-----------------+-------+---------+-------------------------------------+---------+--------------------------+
|  1 | PRIMARY            | abc_number_prop  | ALL    | NULL            | NULL  | NULL    | NULL                                |2660707 | Using where              |
|  2 | DEPENDENT SUBQUERY | abc_number_phone | eq_ref | phone,number_id | phone | 70      | const,eap.abc_number_prop.number_id |       1 | Using where; Using index |
+----+--------------------+------------------+--------+-----------------+-------+---------+-------------------------------------+---------+--------------------------+
2 rows in set (0.01 sec)

這種in子查詢的形式，在外部表（比如上面的abc_number_prop）數據量較大的時候效率是很差的。（如果對于較小的表，不會造成顯著地影響）

文中說到一種優化方式就是，手工將in里面的子查詢查詢出來，然后再拼裝執行 ，這在程序中是可行的。