MySQL性能优化(六)-- in和exists

栏目: 数据库 · 发布时间: 4年前

内容简介:上面的sql中 订单表中(orders) 存在user_id,而又有用户表(users),所以我们用orders表中user_id和user表中的id 来in 和 exists。1.where后面是小表(1)select count(1) from orders o where o.user_id in(select u.id from users u);

sql脚本:

/*建库*/
create database testdb6;
use testdb6;
/* 用户表 */
drop table if exists users;
create table users(
	id int primary key auto_increment,
	name varchar(20)
);
insert into users(name) values ('A');
insert into users(name) values ('B');
insert into users(name) values ('C');
insert into users(name) values ('D');
insert into users(name) values ('E');
insert into users(name) values ('F');
insert into users(name) values ('G');
insert into users(name) values ('H');
insert into users(name) values ('I');
insert into users(name) values ('J');

/* 订单表 */
drop table if exists orders;
create table orders(
	id int primary key auto_increment,/*订单id*/
	order_no varchar(20) not null,/*订单编号*/
	title varchar(20) not null,/*订单标题*/
	goods_num int not null,/*订单数量*/
	money decimal(7,4) not null,/*订单金额*/
	user_id int not null    /*订单所属用户id*/
)engine=myisam default charset=utf8 ;

delimiter $$
drop procedure batch_orders $$

/* 存储过程 */
create procedure batch_orders(in max int)
begin
declare start int default 0;
declare i int default 0;
set autocommit = 0;  
while i < max do
   set i = i + 1;
   insert into orders(order_no,title,goods_num,money,user_id) 
   values (concat('NCS-',floor(1 + rand()*1000000000000 )),concat('订单title-',i),i%50,(100.0000+(i%50)),i%10);
   end while;
commit;
end $$
delimiter ;

/*插入1000万条订单数据*/
call batch_orders(10000000);     /*插入数据的过程根据机器的性能 花费的时间不同,有的可能3分钟,有的可能10分钟*/
复制代码

上面的 sql 中 订单表中(orders) 存在user_id,而又有用户表(users),所以我们用orders表中user_id和user表中的id 来in 和 exists。

结果

1.where后面是小表

(1)select count(1) from orders o where o.user_id in(select u.id from users u);

MySQL性能优化(六)-- in和exists

(2)select count(1) from orders o where exists (select 1 from users u where u.id = o.user_id);

MySQL性能优化(六)-- in和exists

2.where后面是大表

(1)select count(1) from users u where u.id in (select o.user_id from orders o);

MySQL性能优化(六)-- in和exists

(2)select count(1) from users u where exists (select 1 from orders o where o.user_id = u.id);

MySQL性能优化(六)-- in和exists

分析

我们用下面的这两条语句分析:

select count(1) from orders o where o.user_id in(select u.id from users u);
select count(1) from orders o where exists (select 1 from users u where u.id = o.user_id);
复制代码

1.in:先查询in后面的users表,然后再去orders中过滤,也就是先执行子查询,结果出来后,再遍历主查询,遍历主查询是根据user_id和id相等查询的。

即查询users表相当于外层循环,主查询就是外层循环

小结:in先执行子查询,也就是 in() 所包含的语句。子查询查询出数据以后,将前面的查询分为n次普通查询(n表示在子查询中返回的数据行数)

2.exists:主查询是内层循环,先查询出orders,查询orders就是外层循环,然后会判断是不是存在order_id和 users表中的id相等,相等才保留数据,查询users表就是内层循环

这里所说的外层循环和内层循环就是我们所说的嵌套循环,而嵌套循环应该遵循“外小内大”的原则,这就好比你复制很多个小文件和复制几个大文件的区别

小结:如果子查询查到数据,就返回布尔值true;如果没有,就返回布尔值false。返回布尔值true则将该条数据保存下来,否则就舍弃掉。也就是说exists查询,是查询出一条数据就执行一次子查询

结论

小表驱动大表。

in适合于外表大而内表小的情况,exists适合于外表小而内表大的情况。

欢迎关注我的公众号,第一时间接收最新文章~ 搜索公众号:码咖

或者 扫描下方二维码:

MySQL性能优化(六)-- in和exists

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

JavaScript高级程序设计:第2版

JavaScript高级程序设计:第2版

Nicholas Zakas / 李松峰、曹力 / 人民邮电出版社 / 2010-7 / 89.00元

《JavaScript高级程序设计(第2版)》在上一版基础上进行了大幅度更新和修订,融入了近几年来JavaScript应用发展的最新成果,几乎涵盖了所有需要理解的重要概念和最新的JavaScript应用成果。从颇具深度的JavaScript语言基础到作用域(链),从引用类型到面向对象编程,从极其灵活的匿名函数到闭包的内部机制,从浏览器对象模型(BOM)、文档对象模型(DOM)到基于事件的Web脚本......一起来看看 《JavaScript高级程序设计:第2版》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具