专栏首页杨建荣的学习笔记MySQL半连接的攻略式思考

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的反向操作,对于这个小的发现也是如获至宝,至少对于我处理一些半连接的问题有了更多的思路和借鉴,后续可以看看代码里的解析方法。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-06-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL replace into的使用细则(r10笔记第48天)

    在Oracle中有merge into的语法,可以达到一个语句完成同时修改,添加数据的功能,MySQL里面没有merge into的语法,却有replace i...

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

    SQL中的半连接在MySQL和Oracle还是存在一些差距,从测试的情况来看,Oracle的处理要更加全面。 首先我们来看看在MySQL中怎么测试,对于MySQ...

    jeanron100
  • 数据双向复制中的6个数据冲突场景和解决思路

    在双向复制,数据多活中,核心的一个部分就是数据处理,如何保证数据的如下几个问题,是整个方案设计的关键技术。

    jeanron100
  • Qlogic光纤卡安装配置记录及参考

    Last Updated: Saturday, 2004-11-06 19:59 Eygle

    数据和云01
  • PHP PDO & Injection Bypass

    PDO默认支持多语句查询,如果php版本小于5.5.21或者创建PDO实例时未设置PDO::MYSQL_ATTR_MULTI_STATEMENTS为false时...

    贝塔安全实验室
  • 数据库MySQL-设计规范

    a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_'组成; b、命名简洁明确(长度不能超过30个字符); c、例如:user,...

    cwl_java
  • 【漏洞预警】WinRAR代码执行漏洞威胁预警

    近日,有安全研究人员使用WinAFL fuzzer发现WinRAR中存在逻辑漏洞,利用该漏洞攻击者可完全控制受害者计算机。攻击者只需利用此漏洞构造恶意的压缩文件...

    绿盟科技安全情报
  • RHEL 8使用的内核版本

        已知RHEL/CentOS内核使用情况如下,详细可见版本信息可见:CentOS(RHEL)内核版本与系统版本对应关系。

    党志强
  • 电商网站架构探索|SOA分布式架构详解

    目前很多的企业都有自己的电商网站,但随着业务量的增长,并发量高了。由于平台架构的一些不足,会导致一系列严重的问题,电子商务平台的安全性,承受能力也经受着严峻的考...

    数商云
  • spark集群模式下textFile读取file本地文件报错解决

    如果在spark-shell中使用textFile(“file://path”)演示,在local模式下是没有问题的,因为毕竟就是在本机运行,读取本地的文件。但...

    十里桃花舞丶

扫码关注云+社区

领取腾讯云代金券