mysql in语句子查询效率慢的优化技巧示例

2023-09-23 08:39:32 147

表结构如下，文章只有690篇。

文章表article(id,title,content)
标签表tag(tid,tag_name)
标签文章中间表article_tag(id,tag_id,article_id)

其中有个标签的tid是135，查询标签tid是135的文章列表。

690篇文章，用以下的语句查询，奇慢：

selectid,titlefromarticlewhereidin(
selectarticle_idfromarticle_tagwheretag_id=135
)

其中这条速度很快：

selectarticle_idfromarticle_tagwheretag_id=135

查询结果是五篇文章，id为428,429,430,431,432

用下面sql来查文章也很快：

selectid,titlefromarticlewhereidin(
428,429,430,431,432
)

解决方法：

selectid,titlefromarticlewhereidin(
selectarticle_idfrom(selectarticle_idfromarticle_tagwheretag_id=135)astbt
)

其它解决方法：（举例）

mysql>select*fromabc_number_propwherenumber_idin(selectnumber_idfromabc_number_phonewherephone='82306839');

为了节省篇幅，省略了输出内容，下同。

67rowsinset(12.00sec)

只有67行数据返回，却花了12秒，而系统中可能同时会有很多这样的查询，系统肯定扛不住。用desc看一下(注：explain也可)

mysql>descselect*fromabc_number_propwherenumber_idin(selectnumber_idfromabc_number_phonewherephone='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|Usingwhere|
|2|DEPENDENTSUBQUERY|abc_number_phone|eq_ref|phone,number_id|phone|70|const,func|1|Usingwhere;Usingindex|
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
2rowsinset(0.00sec)

可以看出，在执行此查询时会扫描两百多万行，难道是没有创建索引吗，看一下

mysql>showindexfromabc_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|||
+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6rowsinset(0.06sec)
mysql>showindexfromabc_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|||
+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4rowsinset(0.15sec)

从上面的输出可以看出，这两张表在number_id字段上创建了索引的。
看看子查询本身有没有问题。

mysql>descselectnumber_idfromabc_number_phonewherephone='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|Usingwhere;Usingindex|
+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+
1rowinset(0.00sec)

没有问题，只需要扫描几行数据，索引起作用了。

查询出来看看：

mysql>selectnumber_idfromabc_number_phonewherephone='82306839';
+-----------+
|number_id|
+-----------+
|8585|
|10720|
|148644|
|151307|
|170691|
|221897|
+-----------+
6rowsinset(0.00sec)

直接把子查询得到的数据放到上面的查询中

mysql>select*fromabc_number_propwherenumber_idin(8585,10720,148644,151307,170691,221897);
67rowsinset(0.03sec)

速度也快，看来MySQL在处理子查询的时候是不够好。我在MySQL5.1.42和MySQL5.5.19都进行了尝试，都有这个问题。

搜索了一下网络，发现很多人都遇到过这个问题：

参考资料1：MySQL优化之使用连接(join)代替子查询

参考资料2：MYSQL子查询和嵌套查询优化实例解析

根据网上这些资料的建议，改用join来试试。
修改前：

select*fromabc_number_propwherenumber_idin(selectnumber_idfromabc_number_phonewherephone='82306839');

修改后：

selecta.*fromabc_number_propainnerjoinabc_number_phonebona.number_id=b.number_idwherephone='82306839';
mysql>selecta.*fromabc_number_propainnerjoinabc_number_phonebona.number_id=b.number_idwherephone='82306839';
67rowsinset(0.00sec)

效果不错，查询所用时间几乎为0。看一下MySQL是怎么执行这个查询的

mysql>descselecta.*fromabc_number_propainnerjoinabc_number_phonebona.number_id=b.number_idwherephone='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|Usingwhere;Usingindex|
|1|SIMPLE|a|ref|number_id|number_id|4|eap.b.number_id|3||
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
2rowsinset(0.00sec)

小结：当子查询速度慢时，可用JOIN来改写一下该查询来进行优化。

网上也有文章说，使用JOIN语句的查询不一定总比使用子查询的语句快。

mysql手册也提到过，具体的原文在mysql文档的这个章节：
I.3.RestrictionsonSubqueries
13.2.8.SubquerySyntax

摘抄：

1）关于使用IN的子查询：

SubqueryoptimizationforINisnotaseffectiveasforthe=operatororforIN(value_list)constructs.

AtypicalcaseforpoorINsubqueryperformanceiswhenthesubqueryreturnsasmallnumberofrowsbuttheouterqueryreturnsalargenumberofrowstobecomparedtothesubqueryresult.

Theproblemisthat,forastatementthatusesanINsubquery,theoptimizerrewritesitasacorrelatedsubquery.Considerthefollowingstatementthatusesanuncorrelatedsubquery:

SELECT...FROMt1WHEREt1.aIN(SELECTbFROMt2);

Theoptimizerrewritesthestatementtoacorrelatedsubquery:

SELECT...FROMt1WHEREEXISTS(SELECT1FROMt2WHEREt2.b=t1.a);

IftheinnerandouterqueriesreturnMandNrows,respectively,theexecutiontimebecomesontheorderofO(M×N),ratherthanO(M+N)asitwouldbeforanuncorrelatedsubquery.

AnimplicationisthatanINsubquerycanbemuchslowerthanaquerywrittenusinganIN(value_list)constructthatliststhesamevaluesthatthesubquerywouldreturn.

2）关于把子查询转换成join的：

Theoptimizerismorematureforjoinsthanforsubqueries,soinmanycasesastatementthatusesasubquerycanbeexecutedmoreefficientlyifyourewriteitasajoin.

AnexceptionoccursforthecasewhereanINsubquerycanberewrittenasaSELECTDISTINCTjoin.Example:

SELECTcolFROMt1WHEREid_colIN(SELECTid_col2FROMt2WHEREcondition);

Thatstatementcanberewrittenasfollows:

SELECTDISTINCTcolFROMt1,t2WHEREt1.id_col=t2.id_colANDcondition;

Butinthiscase,thejoinrequiresanextraDISTINCToperationandisnotmoreefficientthanthesubquery

总结

以上就是本文关于mysqlin语句子查询效率慢的优化技巧示例的全部内容，感兴趣的朋友而可以参阅：浅谈mysql的子查询联合与in的效率、企业生产MySQL优化介绍等，有什么问题可以留言，欢迎大家一起交流参考。

希望本文所述对大家有所帮助。

mysql in语句子查询效率慢的优化技巧示例

热门推荐

随机推荐