解决mysql in 子查询速度慢 优化查询效率

2016年07月18日 作者:王金阁

现在的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篇

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

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

我用写死的方式用下面sql来查文章也很快

 select id,title from article where id in(
 428,429,430,431,432
 )

我在SqlServer中好像不会这样慢,不知MySQL怎样写好点,也想不出慢在哪里。

后来我找到了解决方法:

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′);

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

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′);
 +—-+——————–+——————+——–+—————–+——-+———+————+———+————————–+
 | 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)

从上面的信息可以看出,在执行此查询时会扫描两百多万行,难道是没有创建索引吗,看一下

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字段上创建了索引的。

看看子查询本身有没有问题。

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)

没有问题,只需要扫描几行数据,索引起作用了。查询出来看看

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)

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

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优化系列记录
http://blog.csdn.net/hongsejiaozhu/article/details/1876181
参考资料2:网站开发日记(14)-MYSQL子查询和嵌套查询优化

http://dodomail.iteye.com/blog/250199

根据网上这些资料的建议,改用join来试试。

修改前:select * from abc_number_

本页面生成使用了0.73194695秒

版权申明:本站原创内容版权遵循 CC-BY-NC-SA 协议规定。转载请标明出处。

王金阁

王金阁

关注站长、网络营销、搜索引擎、系统运维、网络安全的博客

发表评论

电子邮件地址不会被公开。