现在的CMS系统、博客系统、BBS等都喜欢使用标签tag作交叉链接,因此我也尝鲜用了下。但用了后发现我想查询某个tag的文章列表时速度很慢,达到5秒之久!百思不解(后来终于解决),我的表结构是下面这样的,文章只有690篇。
文章表article(id,title,content) 后来我找到了解决方法:
select id,title from article where id in(
其它解决方法:(举例) mysql> select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = ‘82306839’); 为了节省篇幅,省略了输出内容,下同。 67 rows in set (12.00 sec) 只有67行数据返回,却花了12秒,而系统中可能同时会有很多这样的查询,系统肯定扛不住。用desc看一下(注:explain也可)
mysql> desc select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = ‘82306839’); 从上面的信息可以看出,在执行此查询时会扫描两百多万行,难道是没有创建索引吗,看一下
mysql>show index from abc_number_phone;
mysql>show index from abc_number_prop; 从上面的输出可以看出,这两张表在number_id字段上创建了索引的。 看看子查询本身有没有问题。
mysql> desc select number_id from abc_number_phone where phone = ‘82306839’; 没有问题,只需要扫描几行数据,索引起作用了。查询出来看看
mysql> select number_id from abc_number_phone where phone = ‘82306839’; 直接把子查询得到的数据放到上面的查询中 mysql> select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897); 67 rows in set (0.03 sec) 速度也快,看来MySQL在处理子查询的时候是不够好。我在MySQL 5.1.42 和 MySQL 5.5.19 都进行了尝试,都有这个问题。 搜索了一下网络,发现很多人都遇到过这个问题:
参考资料1:使用连接(JOIN)来代替子查询(Sub-Queries) mysql优化系列记录 根据网上这些资料的建议,改用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) 效果不错,查询所用时间几乎为0。看一下MySQL是怎么执行这个查询的
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’; 小结:当子查询速度慢时,可用JOIN来改写一下该查询来进行优化。 网上也有文章说,使用JOIN语句的查询不一定总比使用子查询的语句快。
参考资料3:改变了对Mysql子查询的看法
mysql手册也提到过,具体的原文在mysql文档的这个章节: I.3. Restrictions on Subqueries 13.2.8. Subquery Syntax 摘抄: 1)关于使用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)关于把子查询转换成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 |
用户登录
还没有账号?立即注册
用户注册
投稿取消
文章分类: |
|
还能输入300字
上传中....