优化：子查询改写成关联查询
线上遇到问题，查询较慢，如为对应SQL的查询执行计划：
localhost.user>explain EXTENDED select uid from bar_user_6 where bid='9279' and uid in (SELECT a_uid FROM `user_atten_3` WHERE uid='1400696003') limit 12G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: bar_user_6 type: ref possible_keys: bid key: bid key_len: 4 ref: const rows: 581295 filtered: 100.00 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: user_atten_3 type: eq_ref possible_keys: uid key: uid key_len: 30 ref: const,func rows: 1 filtered: 100.00 Extra: Using where; Using index 2 rows in set, 1 warning (0.00 sec)
优化方案，改写成关联查询
localhost.user>explain extended select bar.uid from bar_user_6 as bar ,user_atten_3 as user where bid='9279' and bar.uid = user.a_uid and user.uid='1400696003' limit 12G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user type: ref possible_keys: uid key: uid key_len: 22 ref: const rows: 1 filtered: 100.00 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: bar type: eq_ref possible_keys: bid key: bid key_len: 12 ref: const,user.user.a_uid rows: 1 filtered: 100.00 Extra: Using index
子查询的查询过程（循环嵌套）：
for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } }
参考文档：
http://dev.mysql.com/doc/refman/5.5/en/nested-loop-joins.html