MySQL SQL优化案例：相关子查询(dependent subquery)优化

原始语句：

SELECT
  t1.*
FROM
  t_payment_bank_account_info t1
WHERE
  EXISTS (
    SELECT
      1
    FROM
      t_payment_account_dtl t2
    WHERE
      t1.account_no = t2.account_no
    AND t2.parent_account_no = '7311810182600115231'
    AND t2.txn_Date >= '2015-12-23'
    AND t2.account_no != t2.opp_acc_no
  );

执行计划

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4552 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL | 7924 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

语句在hotfix环境运行时间：14 rows in set (27.98 sec)

第一个问题：select * 语句在生产环境严格禁止，需明确指明查询字段。

第二个问题：相关子查询，尤其是使用不到索引时效率或非常低，可改写成join方式。

select t1.*
from t_payment_bank_account_info t1
join t