如何优化MySQL反连接

这篇文章是关于如何优化MySQL反向连接的。我觉得边肖挺实用的，就分享给大家参考，和边肖一起来看看。

总的来说，环境相对繁忙，有200多个线程。

# mysqladmin pro|less|wc -l

235

带着好奇心看着慢日志，立即找到这个语句，并使其不敏感。

#时间： 161013 9:513364

# User @ host : root[root]@ localhost[]

# Thread _ id : 24630498 schema : test Last _ errno : 1160 killed : 0

# Query _ time : 61213.561106 Lock _ time : 0.000082 Rows _ sent : 7551 Rows _ inspect : 201945890920 Rows _ effected : 0 Rows _ read : 7551

# Bytes _ sent : 0 Tmp _ tables : 1 Tmp _ disk _ tables : 0 Tmp _ table _ sizes : 0

# InnoDB_trx_id: 2F8E5A82

SET时间戳=1476323505；

从t_fund_info选择帐户

其中货币=300，账户不在

(从t_user_login_record中选择distinct(login_account)，其中login_time='2016-06-01 ')

进入outfile '/tmp/data . txt '；

从慢日志来看，执行时间达到了61213s，相当惊人，也就是说语句运行了一整天。

这引起了我的好奇心和兴趣，这个问题有了先机。表t_fund_info数据量近200万，存在一个主键在id列，唯一性索引在account上。

CREATE TABLE `t _ fund _ info。

主键(` id `),

唯一密钥“帐户”(“帐户”)

)ENGINE=InnoDB AUTO _ INCREMENT=1998416 DEFAULT CHARSET=utf8表t_user_login_record数据量2千多万，存在主键列id

CREATE TABLE ` t _ user _ log in _ record ` s。

主键(` id `)

)ENGINE=InnoDB AUTO _ INCREMENT=22676193 DEFAULT CHARSET=utf8

从语句中可以看到，我们在做一个大批量的查询，希望从查询结果中生成一个文本文件，但是过滤条件非常有限。目前根据查询，肯定是全表扫描。

简单看一下过滤条件，从t_fund_info的表中，按照一个过滤条件，可以过滤掉大部分数据，可以得到一万多个数据，相当理想。

从t_fund_info中选择count(*)，其中money=300

-

|计数(*) |

-

| 13528 |

-

1行一组(0.99秒)

问题的瓶颈似乎是后面的子查询。

将以下语句放入一个SQL脚本中

从t_user_login_record中选择distinct(login_account)，其中log in _ time=' 2016-06-01 '；

导出数据大约需要1分钟。

time mysql测试查询. sql query_rt.log

real 0m59.149s

用户0m0.394s

sys 0m0.046s

过滤的数据有50多万条，是比较理想的过滤情况。

# less query_rt.log|wc -l

548652

让我们解析这个语句，看看不在条件是如何被解析的。

解释t_fund_info中的扩展选择帐户

其中货币=300，账户不在

(从以下选项中选择不同的(login_account)

t_user_login_record where login_time >='2016-06-01')；
show warnings;
结果如下：
Message: select `test`.`t_fund_info`.`account` AS `account` from `test`.`t_fund_info` where ((`test`.`t_fund_info`.`money` >= 300) and (not((`test`.`t_fund_info`.`account`,(select distinct 1 from `test`.`t_user_login_record` where ((`test`.`t_user_login_record`.`login_time` >= '2016-06-01') and (((`test`.`t_fund_info`.`account`) = `test`.`t_user_login_record`.`login_account`) or isnull(`test`.`t_user_login_record`.`login_account`))) having (`test`.`t_user_login_record`.`login_account`))))))
可以看到整个解析的过程非常复杂，原本简单的一个语句，经过解析，竟然变得如此复杂。

因为MySQL里面的优化改进空间相比Oracle还是少很多，我决定循序渐进来尝试优化。因为这个环境还是很重要的，所以我在从库端使用mysqldump导出数据，导入到另外一个测试环境，放开手脚来测试了。
首先对于not in的部分，是否是因为生成临时表的消耗代价太高导致，所以我决定建立一个临时表来缓存子查询的数据。
> create table test_tab as select distinct(login_account) login_account from t_user_login_record where login_time >='2016-06-01';
Query OK, 548650 rows affected (1 min 3.78 sec)
Records: 548650  Duplicates: 0  Warnings: 0
这样查看这个临时表就很轻松了，不到1秒就出结果。
> select count(*)from test_tab;
+----------+
| count(*) |
+----------+
|   548650 |
+----------+
1 row in set (0.38 sec)
然后再次查看使用临时表后的查询是否解析会有改善。
explain extended select account from t_fund_info
where money >=300 and account not in(select login_account from test_tab);
show warnings;
发现还是一样，可见临时表的改进效果不大。
| Note  | 1003 | select `test`.`t_fund_info`.`account` AS `account` from `test`.`t_fund_info` where ((`test`.`t_fund_info`.`money` >= 300) and (not((`test`.`t_fund_info`.`account`,(select 1 from `test`.`test_tab` where (((`test`.`t_fund_info`.`account`) = `test`.`test_tab`.`login_account`) or isnull(`test`.`test_tab`.`login_account`)) having (`test`.`test_tab`.`login_account`)))))) |
是否是因为子查询中的数据量太大导致整个反连接的查询过程中回表太慢，那我缩小一下子查询的数据条数。
select account from t_fund_info
where money >=300 and  not exists (select login_account from test_tab where login_account=t_fund_info.account limit 1,10);
这种方式依旧很卡，持续了近半个小时还是没有反应，所以果断放弃。
是不是t_fund_info的过滤查询导致了性能问题，我们也创建一个临时表
> create table test_tab1 as select account from t_fund_info
    -> where money >=300;
Query OK, 13528 rows affected (1.38 sec)
Records: 13528  Duplicates: 0  Warnings: 0
再次查询效果依旧很不理想。
select account from test_tab1
where  not exists (select login_account from test_tab where login_account=test_tab1.account limit 1,10);
持续了20多分钟还是没有反应，所以还是果断放弃。
这个时候能想到就是索引了，我们在临时表test_tab上创建索引。
> create index ind_tmp_login_account on test_tab(login_account);
Query OK, 0 rows affected (4.31 sec)
Records: 0  Duplicates: 0  Warnings: 0
在临时表test_tab1上也创建索引。
> create index ind_tmp_account on test_tab1(account);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0
再次查看性能就变得很好了，运行时间0.15秒，简直不敢相信。
explain select account from test_tab1
where  not exists (select login_account from test_tab where login_account=test_tab1.account );
11364 rows in set (0.15 sec)

执行计划如下：

可见通过这种拆分，不断的猜测和排除，已经找到了一些思路。
我们开始抓住问题的本质。
首先删除test_tab1上的索引，看看执行效果如何。
> alter table test_tab1 drop index ind_tmp_account;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
语句如下，执行时间0.15秒
select account from test_tab1
     where  not exists (select login_account from test_tab where login_account=test_tab1.account );    
+--------------------------------+
11364 rows in set (0.15 sec)
是否not in的方式会有很大的差别呢，持续0.18秒，有差别，但差别不大。
select account from test_tab1
     where account not in (select login_account from test_tab  );
+--------------------------------+
11364 rows in set (0.18 sec)
我们逐步恢复原来的查询，去除临时表test_tab1,整个查询持续了1.12秒。
select account from t_fund_info
where money >=300 and account not in(select login_account from test_tab);
+--------------------------------+
11364 rows in set (1.12 sec)
使用explain extended解析的内容如下：
Message: select `test`.`t_fund_info`.`account` AS `account` from `test`.`t_fund_info` where ((`test`.`t_fund_info`.`money` >= 300) and (not((`test`.`t_fund_info`.`account`,(((`test`.`t_fund_info`.`account`) in test_tab on ind_tmp_login_account checking NULL having (`test`.`test_tab`.`login_account`)))))))
这个时候，问题已经基本定位了。在反连接的查询中，在这个问题场景中，需要对子查询的表添加一个索引基于login_account，可以和外层的查询字段映射，提高查询效率。
当然在一个数据量庞大，业务相对繁忙的系统中，添加一个临时需求的索引可能不是一个很好的方案。不过我们还是在测试环境体验一下。
> create index ind_tmp_account1 on t_user_login_record(login_account);
Query OK, 0 rows affected (4 min 45.48 sec)
Records: 0  Duplicates: 0  Warnings: 0
添加索引的过程持续了近4分钟，在这个时候我们使用最开始的查询语句，性能如何呢。
select account from t_fund_info where money >=300 and account not in  (select distinct(login_account) from t_user_login_record where);
+--------------------------------+
11364 rows in set (2.52 sec)
只要2.52秒就可以完成之前20多个小时查询结果，性能简直就是天壤之别。
不过话说回来，跑批查询可以在从库上执行，从库上创建一个这样的索引，用完再删掉也是不错的选择，要么就是创建一个临时表，在临时表上创建索引，临时表的意义就在于此，不是为了做查询结果缓存而是创建索引来提高数据过滤效率。
在此有个问题就是临时表只有一个字段，创建索引的意义在哪里呢。
我画一个图来解释一下。

首先这个查询的数据是以t_fund_info的过滤条件为准，从200万数据中过滤得到1万条数据，然后两个字段通过account=login_account的条件关联，而不是先关联子查询的过滤条件 login_time,过滤完之后account的值之后再过滤login_time，最后根据not in的逻辑来取舍数据，整个数据集就会大大减少。如此一来，子查询的表千万行，性能的差别就不会是指数级的。

感谢各位的阅读！关于“如何优化MySQL反连接”这篇文章就分享到这里了，希望