MySQL 里面有哪些子查询呢？ 标量子查询 内联视图 半连接/反连接 本篇主要讲解半连接查询 半连接？可以这么理解 where 条件后面有In/EXISTS这样的子查询称为semi jion 格式：select .

【云栖社区】  【mysql】  【code】  【子查询】  【semijoin】   点击查看原文>

0、导读

有个采用子查询的DELETE执行得非常慢，改写成SELECT后执行却很快，最后把这个子查询DELETE改写成JOIN优化过程

1、问题描述

朋友遇到一个怪事，一个用子查询的DELETE，执行效率非常低。把DELETE改成SELECT后执行起来却很快，百思不得其解。

下面就是这个用了子查询的DELETE了：

[yejr@imysql.com]mydb > EXPLAIN delete from trade_info where id in (

select id from (

select a.id from trade_info a, order_info b, user c where

b.buyer = c.id and c.itv_account=’90000248′ and a.order_id = b.id) temp)\G

几个表的DDL是这样的：

上面这个SQL的执行耗时是：31.74秒

Query OK, 5 rows affected (31.74 sec)

…

通常，我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL：

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或者像下面这个不带任何条件的分页SQL：

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

一般而言，分页SQL的耗时随着 start 值的增加而急剧增加，我们来看下面这2个不同起始值的分页SQL执行耗时：

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;
…

10 rows in set (0.05 sec)


yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;
…

10 rows in set (2.39 sec)

可以看到，随着分页数量的增加，SQL查询耗时也有数十倍增加，显然不科学。今天我们就来分析下，如何能优化这个分页方案。 …

众所周知，在MySQL中，如果直接 ORDER BY RAND() 的话，效率非常差，因为会多次执行。事实上，如果等值查询也是用 RAND() 的话也如此，我们先来看看下面这几个SQL的不同执行计划和执行耗时。
首先，看下建表DDL，这是一个没有显式自增主键的InnoDB表：

[yejr@imysql]> show create table t_innodb_random\G
*************************** 1. row ***************************
Table: t_innodb_random
Create Table: CREATE TABLE `t_innodb_random` (
`id` int(10) unsigned NOT NULL,
`user` varchar(64) NOT NULL DEFAULT '',
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

往这个表里灌入一些测试数据，至少10万以上， id 字段也是乱序的。

[yejr@imysql]> select count(*) from t_innodb_random\G
*************************** 1. row ***************************
count(*): 393216

1、常量等值检索：

 …