MySQL> select * from abc_number_PRop where number_id in (select number_id from abc_number_phone where phone = '82306839');

為了節(jié)省篇幅，省略了輸出內(nèi)容，下同。

67 rows in set (12.00 sec)

只有67行數(shù)據(jù)返回，卻花了12秒，而系統(tǒng)中可能同時(shí)會(huì)有很多這樣的查詢，系統(tǒng)肯定扛不住。用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)

從上面的信息可以看出，在執(zhí)行此查詢時(shí)會(huì)掃描兩百多萬(wàn)行，難道是沒(méi)有創(chuàng)建索引嗎，看一下

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字段上創(chuàng)建了索引的。

看看子查詢本身有沒(méi)有問(wèn)題。

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)

沒(méi)有問(wèn)題，只需要掃描幾行數(shù)據(jù)，索引起作用了。查詢出來(lái)看看

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)

直接把子查詢得到的數(shù)據(jù)放到上面的查詢中

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

67 rows in set (0.03 sec)

速度也快，看來(lái)MySQL在處理子查詢的時(shí)候是不夠好。我在MySQL 5.1.42 和 MySQL 5.5.19 都進(jìn)行了嘗試，都有這個(gè)問(wèn)題。

搜索了一下網(wǎng)絡(luò)，發(fā)現(xiàn)很多人都遇到過(guò)這個(gè)問(wèn)題：

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

根據(jù)網(wǎng)上這些資料的建議，改用join來(lái)試試。

修改前：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)

效果不錯(cuò)，查詢所用時(shí)間幾乎為0?？匆幌翸ySQL是怎么執(zhí)行這個(gè)查詢的

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)

小結(jié)：當(dāng)子查詢速度慢時(shí)，可用JOIN來(lái)改寫(xiě)一下該查詢來(lái)進(jìn)行優(yōu)化。