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)

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

Oracle 12.2新特性掌上手册 - 第六卷 ADG的性能与诊断

编辑手记:在Oracle 12.2中,ADG有许多惊人的改进,通过ADG standby数据库的性能数据收集和诊断、快照standby数据库的应用,以及实时的数...

3857
来自专栏沃趣科技

ASM 翻译系列第二十一弹:ASM Attributes Directory

原作者:Bane Radulovic 译者: 郭旭瑞 审核: 魏兴华 DBGeeK社群联合出品 ASM Attributes Directory A...

3334
来自专栏CaiRui

Zabbix监控详解

Zabbix是什么 Zabbix 是由Alexei Vladishev创建,目前由Zabbix SIA在持续开发和支持。 Zabbix 是一个企业级的分布式开源...

1.8K8
来自专栏云计算教程系列

让你的PostgreSQL更安全

PostgreSQL是自由的对象-关系型数据库服务器,在灵活的BSD风格许可证下发行。它在其他开放源代码数据库系统和专有系统之外,为用户又提供了一种选择。 我们...

2086
来自专栏哲学驱动设计

Rafy 框架 - 幽灵插件(假删除)

Rafy 框架又添新成员:幽灵插件。本文将解释该插件的场景、使用方法、原理。 场景 在开发各类数据库应用系统时,往往需要在删除数据时不是真正地删除数据,而只是把...

2278
来自专栏IT技术精选文摘

MySQL高可用方案

第一种:主从复制+读写分离 客户端通过Master对数据库进行写操作,slave端进行读操作,并可进行备份。Master出现问题后,可以手动将应用切换到slav...

3988
来自专栏性能与架构

InnoDB 日志文件大小设为多大合适?

InnoDB 日志文件的作用 Innodb 数据表崩溃后,再次启动时,MySQL会扫描日志文件,看哪些记录不在表空间中,对其进行 redo 操作,从而完成数据恢...

77811
来自专栏张善友的专栏

体验SQL Server 2008 Express

1、在安装上和SQL Server 2005比较上更加丰富的选项设置,下面贴两个界面: ? ? 2、SQL Server 2008的新特性FILESTREAM ...

2066
来自专栏杨建荣的学习笔记

关于视图和存储过程的权限问题探究 (r9笔记第87天)

今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式 ? 数据库中存在一个属主用户,表,存储过程等对象...

35710
来自专栏数据和云

DB2 Vs MySQL系列 | 体系架构对比

前些日子,我们做了DB2 VS MySQL的数据类型的对比,今天我们将体系架构的对比分享给大家,让大家对这两类数据库有更深刻的认识。 ? DB2体系结构 ? ?...

3105

扫码关注云+社区

领取腾讯云代金券