mysqlin子查询效率慢优化

mysqlin子查询效率慢优化现在的CMS系统、博客系统、BBS等都喜欢使用标签tag作交叉链接，因此我也尝鲜用了下。但用了后发现我想查询某个tag的文章列表时速度很慢，达到5秒之久！百思不解(后来终于解决)，我的表结构是下面这样的，文章只有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)我在SqlServer中好像不会这样慢，不知MySQL怎样写好点，也想不出慢在哪里。后来我找到了解决方法：selectid,titlefromarticlewhereidin(selectarticle_idfrom(selectarticle_idfromarticle_tagwheretag_id=135)astbt)其它解决方法：（举例）mysqlselect*fromabc_number_propwherenumber_idin(selectnumber_idfromabc_number_phonewherephone='82306839');为了节省篇幅，省略了输出内容，下同。67rowsinset(12.00sec)只有67行数据返回，却花了12秒，而系统中可能同时会有很多这样的查询，系统肯定扛不住。用desc看一下(注：explain也可)mysqldescselect*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)从上面的信息可以看出，在执行此查询时会扫描两百多万行，难道是没有创建索引吗，看一下mysqlshowindexfromabc_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)mysqlshowindexfromabc_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字段上创建了索引的。看看子查询本身有没有问题。mysqldescselectnumber_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)没有问题，只需要扫描几行数据，索引起作用了。查询出来看看mysqlselectnumber_idfromabc_number_phonewherephone='82306839';+-----------+|number_id|+-----------+|8585||10720||148644||151307||170691||221897|+-----------+6rowsinset(0.00sec)直接把子查询得到的数据放到上面的查询中mysqlselect*fromabc_number_propwherenumber_idin(8585,10720,148644,151307,170691,221897);67rowsinset(0.03sec)速度也快，看来MySQL在处理子查询的时候是不够好。我在MySQL5.1.42和MySQL5.5.19都进行了尝试，都有这个问题。搜索了一下网络，发现很多人都遇到过这个问题：参考资料1：使用连接（JOIN）来代替子查询(Sub-Queries)mysql优化系列记录参考资料2：网