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。看一下MySQL是怎么執(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)改寫一下該查詢來(lái)進(jìn)行優(yōu)化。
網(wǎng)上也有文章說(shuō),使用JOIN語(yǔ)句的查詢不一定總比使用子查詢的語(yǔ)句快。
參考資料3:改變了對(duì)Mysql子查詢的看法
http://hi.baidu.com/yzx110/blog/item/e694f536f92075360b55a92b.html
正好手頭有本《高性能MySQL》,翻閱了一下,第4.4節(jié)“MySQL查詢優(yōu)化器的限制”4.4.1小節(jié)“關(guān)聯(lián)子查詢”正好講到這個(gè)問(wèn)題。
MySQL有時(shí)優(yōu)化子查詢很差,特別是在WHERE從句中的IN()子查詢。像上面我碰到的情況,其實(shí)我的想法是MySQL會(huì)把
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);
但不幸的是,實(shí)際情況正好相反。MySQL試圖讓它和外面的表產(chǎn)生聯(lián)系來(lái)“幫助”優(yōu)化查詢,它認(rèn)為下面的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)數(shù)據(jù)量較大的時(shí)候效率是很差的。(如果對(duì)于較小的表,不會(huì)造成顯著地影響)
文中說(shuō)到一種優(yōu)化方式就是,手工將in里面的子查詢查詢出來(lái),然后再拼裝執(zhí)行 ,這在程序中是可行的。