mysql in語句子查詢效率慢的優(yōu)化技巧示例
表結(jié)構(gòu)如下，文章只有690篇。
文章表article(id,title,content) 標(biāo)簽表tag(tid,tag_name) 標(biāo)簽文章中間表article_tag(id,tag_id,article_id)
其中有個(gè)標(biāo)簽的tid是135，查詢標(biāo)簽tid是135的文章列表。
690篇文章，用以下的語句查詢，奇慢：
select id,title from article where id in( select article_id from article_tag where tag_id=135 )
其中這條速度很快：
select article_id from article_tag where tag_id=135
查詢結(jié)果是五篇文章，id為428,429,430,431,432
用下面sql來查文章也很快：
select id,title from article where id in( 428,429,430,431,432 )
解決方法：
select id,title from article where id in( select article_id from (select article_id from article_tag where tag_id=135) as tbt )
其它解決方法：（舉例）
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ì)掃描兩百多萬行，難道是沒有創(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)建了索引的。
看看子查詢本身有沒有問題。
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)
沒有問題，只需要掃描幾行數(shù)據(jù)，索引起作用了。
查詢出來看看：
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)
速度也快，看來MySQL在處理子查詢的時(shí)候是不夠好。我在MySQL 5.1.42 和 MySQL 5.5.19 都進(jìn)行了嘗試，都有這個(gè)問題。
搜索了一下網(wǎng)絡(luò)，發(fā)現(xiàn)很多人都遇到過這個(gè)問題：
參考資料1：MySQL優(yōu)化之使用連接(join)代替子查詢
參考資料2：MYSQL子查詢和嵌套查詢優(yōu)化實(shí)例解析
根據(jù)網(wǎng)上這些資料的建議，改用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)
效果不錯(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來改寫一下該查詢來進(jìn)行優(yōu)化。
網(wǎng)上也有文章說，使用JOIN語句的查詢不一定總比使用子查詢的語句快。
mysql手冊(cè)也提到過，具體的原文在mysql文檔的這個(gè)章節(jié)：
I.3. Restrictions on Subqueries
13.2.8. Subquery Syntax
摘抄：
1）關(guān)于使用IN的子查詢：
Subquery optimization for IN is not as effective as for the = operator or for IN(value_list) constructs.
A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.
The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
The optimizer rewrites the statement to a correlated subquery:
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.
An implication is that an IN subquery can be much slower than a query written using an IN(value_list) construct that lists the same values that the subquery would return.
2）關(guān)于把子查詢轉(zhuǎn)換成join的：
The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.
An exception occurs for the case where an IN subquery can be rewritten as a SELECT DISTINCT join. Example:
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);
That statement can be rewritten as follows:
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;
But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery
總結(jié)
以上就是本文關(guān)于mysql in語句子查詢效率慢的優(yōu)化技巧示例的全部?jī)?nèi)容，感興趣的朋友而可以參閱：淺談mysql的子查詢聯(lián)合與in的效率、企業(yè)生產(chǎn)MySQL優(yōu)化介紹等，有什么問題可以留言，歡迎大家一起交流參考。
希望本文所述對(duì)大家有所幫助。
相關(guān)文章
這篇文章主要介紹了MySQL EXPLAIN語句的使用示例，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-03-03
復(fù)合查詢語句是MySQL中一種非常重要的查詢方式,可以用于優(yōu)化查詢性能和控制查詢的結(jié)果集,具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10
MySQL的查詢計(jì)劃中ken_len的值計(jì)算方法
本文首先介紹了MySQL的查詢計(jì)劃中ken_len的含義；然后介紹了key_len的計(jì)算方法；最后通過一個(gè)偽造的例子,來說明如何通過key_len來查看聯(lián)合索引有多少列被使用2017-02-02
數(shù)據(jù)完整性約束是對(duì)關(guān)系性模型完整性規(guī)則做某種約束條件，這篇文章主要給大家介紹了關(guān)于MySQL完整性約束的相關(guān)資料，需要的朋友可以參考下2021-05-05
這篇文章主要介紹了MySQL之InnoDB下的鎖問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
文章講解了數(shù)據(jù)庫事務(wù)的隔離性及四個(gè)級(jí)別（讀未提交、讀提交、可重復(fù)讀、串行化）,不同級(jí)別解決不同并發(fā)問題,強(qiáng)調(diào)一致性需用戶與數(shù)據(jù)庫共同保障2025-08-08
phplib中常用的方法有set_file,set_block,set_var,parse,ppasre,p,get等。2010-04-04
通用SQL存儲(chǔ)過程分頁以及asp.net后臺(tái)調(diào)用的方法
MySQL asc、desc數(shù)據(jù)排序的實(shí)現(xiàn)