MySQL半连接的攻略式思考

在此说是攻略式思考，是因为仅供参考，说是攻略，是因为暂时还没有严谨的结论，目前只能说对结论有帮助。

MySQL和Oracle中的半连接测试总结（一）(r10笔记第31天)

问题简单复现下：

创建一个表users,然后插入一些数据之后，使用两种方式来对比下：

create table users( userid int(11) unsigned not null, user_name varchar(64) default null, primary key(userid) )engine=innodb default charset=UTF8; 如果要插入数据，可以使用存储过程的方式。比如先插入20000条定制数据。 delimiter $$ drop procedure if exists proc_auto_insertdata$$ create procedure proc_auto_insertdata() begin declare init_data integer default 1; while init_data<=20000 do insert into users values(init_data,concat('user' ,init_data)); set init_data=init_data+1; end while; end$$ delimiter; call proc_auto_insertdata(); 初始化的过程会很快，最后一步即插入数据花费了近6秒的时间。 [test]>source insert_proc.sql Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (5.63 sec) 然后我们使用如下的半连接查询数据，实际上执行了6秒左右。 select u.userid,u.user_name from users u where u.user_name in (select t.user_name from users t where t.userid<2000); 1999 rows in set (6.36 sec)

在SSD的环境上，时间在2.58秒左右。 为了简化测试条件和查询结果，我们使用count的方式来完成对比测试。 [test]>select count(u.userid) from users u where u.user_name in (select t.user_name from users t where t.userid<2000); +-----------------+ | count(u.userid) | +-----------------+ | 1999 | +-----------------+ 1 row in set (6.38 sec) 然后使用如下的方式来查看，当然看起来这种结构似乎有些多余，因为userid<-1的数据是不存在的。 select count(u.userid) from users u where (u.user_name in (select t.user_name from users t where t.userid<2000) or u.user_name in (select t.user_name from users t where userid<-1) )； +-----------------+ | count(u.userid) | +-----------------+ | 1999 | +-----------------+ 1 row in set (0.03 sec)

其实对于第二种方法来说，我们似乎只看到了结论，但是没有一个基本的参考点。如果按照这个思路，应该会得出MySQL优化器很low的印象。

对于这个问题该怎么解释呢。这个条件 or u.user_name in (select t.user_name from users t where userid<-1) 是不是巧合或者有什么特别的规律。

其实是有的，如果我这么写这个SQL:

mysql> select count(u.userid) from users u where (u.user_name in (select t.user_name from users t where t.userid<2000) or isnull(null));

+-----------------+

| count(u.userid) |

+-----------------+

| 20000 |

+-----------------+

1 row in set (0.00 sec)

这个逻辑还是能够基本接受的，其实算是找到了一个基本的规律吧。

当然这个问题为什么这么解读呢。

我们使用explain extended来解读，常规的语句会被解析成为标准的semijoin的格式。

| Note | 1003 | /* select#1 */ select `test_bug`.`u`.`userid` AS `userid`,`test_bug`.`u`.`user_name` AS `user_name` from `test_bug`.`users` `u` semi join (`test_bug`.`users` `t`) where ((`test_bug`.`u`.`user_name` = `<subquery2>`.`user_name`) and (`test_bug`.`t`.`userid` < 2000))

其实这种方式我们无法得到semijoin的更多信息。我想起了之前处理一个反连接的问题时，通过explain extended得到的查询重写信息。

这是一个反连接的语句，即not in

原来的语句如下：

select account from t_fund_info where money >=300 and account not in(select distinct(login_account) from t_user_login_record where login_time >='2016-06-01')into outfile '/tmp/data.txt';

解析后的结果如下，可以明显看到解析后的结果比原语句要复杂了好多。

其中or isnull（）的部分引起了我的好奇。这个不就是我们之前有效果的半连接场景嘛，这里是反连接，只是在外部多了一个not的反向操作，对于这个小的发现也是如获至宝，至少对于我处理一些半连接的问题有了更多的思路和借鉴，后续可以看看代码里的解析方法。