现在的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
)
mysql> select col1 from t1 where id in (select col1 from t2 );
67 rows in set (12.00 sec)
只有67行数据返回，却花了12秒，而系统中可能同时会有很多这样的查询，系统肯定扛不住。用desc看一下(注：explain也可)
mysql> desc select col1 from t1 where id in (select col2 from t2 );
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2679838 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | | | | | | 1 | |
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
2 rows in set (0.00 sec)
这样的子查询为什么这么慢？
看到 SQL 执行计划中 select_type 字段中出现“DEPENDENT SUBQUERY”
基础知识：Dependent Subquery意味着什么
官方含义为：
SUBQUERY：子查询中的第一个SELECT；
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询 。
换句话说，就是 子查询对 t2 的查询方式依赖于外层 t1 的查询。
什么意思呢？它意味着两步：
第一步，MySQL 根据 select col1 from t1 ; 得到一个大结果集 t1，其数据量就是上图中的 rows=2679838 了。
第二步，上面的大结果集 t1 中的每一条记录，都将与子查询 SQL 组成新的查询语句：select col2 from t2 where id=%t1.id%。等于说，子查询要执行267万次……即使这两步查询都用到了索引，但不慢才怪。
如此一来，子查询的执行效率居然受制于外层查询的记录数，那还不如拆成两个独立查询顺序执行呢。
mysql 在处理子查询时，会改写子查询。
通常情况下，我们希望由内到外，先完成子查询的结果，然后再用子查询来驱动外查询的表，完成查询。
例如：
select * from test where tid in(select fk_tid from sub_test where gid=10)
通常我们会感性地认为该 sql 的执行顺序是：
sub_test 表中根据 gid 取得 fk_tid(2,3,4,5,6)记录，
然后再到 test 中，带入 tid=2,3,4,5,6，取得查询数据。
但是实际mysql的处理方式为：
select * from test where exists (
select * from sub_test where gid=10 and sub_test.fk_tid=test.tid
)
mysql 将会扫描 test 中所有数据，每条数据都将会传到子查询中与 sub_test 关联，子查询不会先被执行，所以如果 test 表很大的话，那么性能上将会出现问题。
优化方案:
可以使用join代替，来优化 或使用 select * from t1,t2 where t1.col1=t2.col2
连接（JOIN） 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作
修改前：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';
效果不错，查询所用时间几乎为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';
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
| 1 | SIMPLE | b | ref | phone,number_id | phone | 66 | const | 6 | Using where; Using index |
| 1 | SIMPLE | a | ref | number_id | number_id | 4 | eap.b.number_id | 3 | |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
2 rows in set (0.00 sec)
小结：当子查询速度慢时，可用JOIN来改写一下该查询来进行优化。select_type 从DEPENDENT SUBQUERY 变成 SIMPLE
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