mysql in 子查询 效率慢 优化

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

其它解决方法:(举例)

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 都进行了尝试,都有这个问题。