使用IN()子查询性能下降的例子及优化（特定在mysql5.5版本线）

1、查询需求：

 知道某个用户的gameid 为 101190 查询用户的name （这两个字段分别在两个表中 通过id字段关联)

如果使用IN子查询 mysql语句可以这样写：


mysql> select name from lee where id IN (select id from lee1 where gameid=101190);
+-------+
| name  |
+-------+
| lee19 |
+-------+
1 row in set (0.00 sec)


2、分析语句影响到的行数：


mysql> explain select name from lee where id IN (select id from lee1 where gameid=101190);
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table | type            | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | lee   | ALL             | NULL          | NULL    | NULL    | NULL | 1198 | Using where |
|  2 | DEPENDENT SUBQUERY | lee1  | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)


3、分析：可以看出对lee表做了一次全表扫描。上述语句是需要根据id来关联外部表lee，因为需要id字段，所以Mysql认为无法先执行这个子查询，所以对lee表进行全表扫描，然后根据返回的id逐个执行IN子查询。如果是非常大的表这个查询性能会很低。


4、重写上述查询需要用到连接查询：


mysql> select name from lee left join lee1 on lee.id=lee1.id where gameid=101190;
+-------+
| name  |
+-------+
| lee19 |
+-------+
1 row in set (0.00 sec)


5、分析语句影响到的行数：


mysql> explain select name from lee left join lee1 on lee.id=lee1.id where gameid=101190;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | lee1  | ALL    | PRIMARY       | NULL    | NULL    | NULL          |  200 | Using where |
|  1 | SIMPLE      | lee   | eq_ref | PRIMARY       | PRIMARY | 4       | cr_db.lee1.id |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
2 rows in set (0.00 sec)


6、说明：虽然连接查询在数据库知识中是相当耗费资源的（连接中所涉及到的表做笛卡尔乘积），但是mysql优化器对连接查询做了很好的优化，从执行所影响到的行数可以看出首先扫描lee1表，只会返回200条记录进行后面的嵌套循环查询（即连接查询）。实际上影响连接查询的是所连接的表的执行顺序（如果有多余两张表参与连接查询），关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。