kxbin
          成功留給有準(zhǔn)備的人
          posts - 10,  comments - 35,  trackbacks - 0

          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 |    |                          |
          +----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
          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 |       | 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í)行 ,這在程序中是可行的。

          posted on 2012-03-10 22:17 kxbin 閱讀(271) 評(píng)論(0)  編輯  收藏 所屬分類: MYSQL
          你恨一個(gè)人是因?yàn)槟銗?ài)他;你喜歡一個(gè)人,是因?yàn)樗砩嫌心銢](méi)有的;你討厭一個(gè)人是因?yàn)樗砩嫌心阌械臇|西;你經(jīng)常在別人面前批評(píng)某人,其實(shí)潛意識(shí)中是想接近他。

          <2025年7月>
          293012345
          6789101112
          13141516171819
          20212223242526
          272829303112
          3456789

          常用鏈接

          留言簿(5)

          隨筆檔案

          文章分類

          文章檔案

          相冊(cè)

          收藏夾

          J2EE

          java技術(shù)網(wǎng)站

          Linux

          平時(shí)常去的網(wǎng)站

          數(shù)據(jù)庫(kù)

          電影網(wǎng)站

          網(wǎng)站設(shè)計(jì)

          搜索

          •  

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          主站蜘蛛池模板: 鸡东县| 奎屯市| 精河县| 延寿县| 光山县| 穆棱市| 保康县| 广汉市| 西乌珠穆沁旗| 扎赉特旗| 阜康市| 固阳县| 县级市| 尚志市| 德兴市| 随州市| 红安县| 龙州县| 云浮市| 淮安市| 诸暨市| 和政县| 大理市| 沈丘县| 庐江县| 石柱| 汉川市| 高阳县| 无锡市| 庄浪县| 丰顺县| 和林格尔县| 平谷区| 和田县| 宁化县| 湘潭县| 东莞市| 宾川县| 佛学| 凌海市| 舞钢市|