表結構如下，文章只有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) |
解決方法：
| 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) |
