正看资料看的过瘾，突然收到报警，说服务器负载太高，好吧，登录服务器看看，我擦嘞，还能不能愉快的玩耍了？下面是当时的负载情况
看见mysql使用cpu已经到了2000，io没有等待。说明应该没有大的临时表，或者文件排序，但是SQL语句肯定还是有问题的，好吧，那进数据库看看到底在干嘛，执行show full processlist后，发现有好几百个连接在执行同一条SQL语句，看见SQL也还好，不复杂，是子查询，我最恶心的子查询。那就EXPLAIN一下咯，线上的东东我就不在这里贴出来了，后面我会创建类似的表来解释。
表结构简单如下：
mysql> desc t1;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| role_id | int(11) | NO | | 0 | |
| referer | varchar(20) | NO | | | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec) mysql> desc t2;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| role_id | int(11) | NO | MUL | 0 | |
| privilege_id | int(11) | NO | | 0 | |
+--------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec) mysql>
索引如下：
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | id | A | 329 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec) mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 0 | PRIMARY | 1 | id | A | 12826 | NULL | NULL | | BTREE | | |
| t2 | 1 | role_id | 1 | role_id | A | 583 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec) mysql>
当时大量SQL语句如下：
mysql> select privilege_id from t2 where role_id in (select role_id from t1 where id=193);
那么你会觉得这语句有问题么？你会说这哪有什么问题啊。t2表role_id有索引，t1的表id是主键，肯定走索引啦！但是就是这么坑，那我们EXPLAIN一下，结果如下：
mysql> explain select privilege_id from t2 where role_id in (select role_id from t1 where id=193);
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 12826 | Using where |
| 2 | DEPENDENT SUBQUERY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
2 rows in set (0.00 sec) mysql>
what？发生了什么事情，怎么和想的不一样？这不科学啊。虽然数据量不多，但是执行频率非常高的情况下，也是一种悲剧。好吧，我本来就不喜欢子查询，我改成了join看看。
mysql> explain select a.privilege_id from t2 as a inner join t1 as b on a.role_id=b.role_id and b.id=193;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | a | ref | role_id | role_id | 4 | const | 128 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec) mysql>
这下更郁闷了，怎么join就走索引了呢？后来想到in后面接受多个值，但是我的t1表的id是主键肯定只有一条记录，那么我可以改成=，那么我们试试。
mysql> explain select privilege_id from t2 where role_id = (select role_id from t1 where id=193);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | t2 | ref | role_id | role_id | 4 | const | 128 | Using where |
| 2 | SUBQUERY | t1 | const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec) mysql>
我去，这样真的可以走索引，好吧。晕了。。这时突然想到或许优化器的问题，还是一个朋友提醒，这下才明白。原来子查询这里role_id（）有限制，这个括号里的查询要基于唯一索引或是主键。不过在更高版本已经修复了这个问题。下面给出例子。
percoan-5.5.38的版本
mysql> show variables like '%version';
+------------------+-------------+
| Variable_name | Value |
+------------------+-------------+
| innodb_version | 5.5.38-35.2 |
| protocol_version | 10 |
| version | 5.5.38-35.2 |
+------------------+-------------+
3 rows in set (0.01 sec) mysql> explain select privilege_id from t2 where role_id in (select role_id from t1 where id=193);
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 12826 | Using where |
| 2 | DEPENDENT SUBQUERY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
2 rows in set (0.00 sec) mysql>
percona-5.6.21版本
[root@localhost [test]> show variables like '%version';
+------------------+-----------------+
| Variable_name | Value |
+------------------+-----------------+
| innodb_version | 5.6.21-rel70.0 |
| protocol_version | 10 |
| version | 5.6.21-70.0-log |
+------------------+-----------------+
3 rows in set (0.00 sec) [root@localhost [test]> explain select privilege_id from t2 where role_id in (select role_id from t1 where id=193);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 1 | SIMPLE | t2 | ref | role_id | role_id | 4 | const | 129 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec) [root@localhost [test]>
可以看见5.6.21的版本已经不受影响了。后面让开发同学修改成join以后，负载慢慢的下来了。下面是zabbix监控到的负载情况：
总结：
子查询虽然写起来方便，且简单易懂，但是我们还是尽量的使用join，因为在5.6版本以前的子查询的性能实在不怎么样。