子查询

子查询语法

子查询外部的查询称为外部查询。

select 子查询结果集
```
SELECT (SELECT m1 FROM t1 LIMIT 1)
```

from 子查询结果集

SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;

where、on 子查询结果集

SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

order by ,group by 子查询结果集

语法上支持，实际上在业务场景中意义不大。

子查询分类

按与外层查询关系来区分子查询

不相关子查询
- 子查询 不依赖外层查询的值

子查询在布尔表达式中的使用

一般来说，子查询是用在：

作为结果集临时表
作为筛选条件

本处描述的为筛选条件。

子查询与基础比较符号（comparison_operator ）

`=`、`>`、`<`、`>=`、`<=`、`<>`、`!=`、`<=>

这些符号可以和子查询结果集相互作用，例如：

SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);

SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

根据这几个基础比较符号的定义，容易得知：

此处子查询的结果集 必须只有一个值（更准确些的描述为：一条记录）

[NOT] IN/ANY/SOME/ALL子查询

in

范围查询。

需要注意：in可以对多个值进行匹配：

SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

ANY/SOME（二者同义）

语法：

操作数 comparison_operator ANY/SOME(子查询)

操作结果为：

只要匹配子查询中的 一条记录结果，就返回true。

例如：
```
SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);
```
这个case就代表着：
- 只要M1的值大于子查询中的一个值，就通过检测。
- 因此这个case等价为：
```
SELECT * FROM t1 WHERE m1 > (SELECT MIN(m2) FROM t2);
```

ALL

语法：

操作数 comparison_operator ALL(子查询)

操作含义为：
- 操作数和子查询结果集中的所有记录，都满足操作符的定义。
例如：
```
SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);
```
等价于
```
SELECT * FROM t1 WHERE m1 > (SELECT MAX(m2) FROM t2);
```

EXISTS

语法：

[NOT] EXISTS (子查询)

操作含义为：

子查询中有记录

例如：
```
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);
```

子查询执行

case：

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

为了方便，我们假设有两个表s1、s2与这个single_table表的构造是相同的，而且这两个表里边儿有10000条记录，除id列外其余的列都插入随机值。下边正式开始我们的表演。

标量子查询、行子查询

我们经常在下边两个场景中使用到标量子查询或者行子查询：

SELECT子句中，我们前边说过的在查询列表中的子查询必须是标量子查询。
子查询使用=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成一个布尔表达式，这样的子查询必须是标量子查询或者行子查询。

对于上述两种场景中的不相关标量子查询或者行子查询来说，它们的执行方式是简单的

select子句

比方说下边这个查询语句：

  SELECT * FROM s1 
      WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

执行流程：

先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询。
然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = ...。

布尔表达式

比如下边这个查询：

SELECT * FROM s1 WHERE 
    key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

执行流程：

先从外层查询中获取一条记录，本例中也就是先从s1表中获取一条记录。
然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执行子查询。
最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
再次执行第一步，获取第二条外层查询中的记录，依次类推～

InnoDB四大特性

插入缓冲

二次写

自适应哈希

预读

InnoDB提供了一个看起来比较贴心的服务——预读（英文名：read ahead）。所谓预读，就是InnoDB认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到Buffer Pool中。根据触发方式的不同，预读又可以细分为下边两种：

线性预读

设计InnoDB的大叔提供了一个系统变量innodb_read_ahead_threshold，如果顺序访问了某个区（extent）的页面超过这个系统变量的值，就会触发一次异步读取下一个区中全部的页面到Buffer Pool的请求，注意异步读取意味着从磁盘中加载这些被预读的页面并不会影响到当前工作线程的正常执行。这个innodb_read_ahead_threshold系统变量的值默认是56，我们可以在服务器启动时通过启动参数或者服务器运行过程中直接调整该系统变量的值，不过它是一个全局变量，注意使用SET GLOBAL命令来修改哦。

小贴士： InnoDB是怎么实现异步读取的呢？在Windows或者Linux平台上，可能是直接调用操作系统内核提供的AIO接口，在其它类Unix操作系统中，使用了一种模拟AIO接口的方式来实现异步读取，其实就是让别的线程去读取需要预读的页面。
随机预读

如果Buffer Pool中已经缓存了某个区的13个连续的页面，不论这些页面是不是顺序读取的，都会触发一次异步读取本区中所有其的页面到Buffer Pool的请求。设计InnoDB的大叔同时提供了innodb_random_read_ahead系统变量，它的默认值为OFF，也就意味着InnoDB并不会默认开启随机预读的功能，如果我们想开启该功能，可以通过修改启动参数或者直接使用SET GLOBAL命令把该变量的值设置为ON。

row_id（见：10-表空间，21-redo日志）

如果我们没有为某个表显式的定义主键，并且表中也没有定义Unique键，那么InnoDB会自动的为表添加一个称之为row_id的隐藏列作为主键。为这个row_id隐藏列赋值的方式如下：

服务器会在内存中维护一个全局变量，每当向某个包含隐藏的row_id列的表中插入一条记录时，就会把该变量的值当作新记录的row_id列的值，并且把该变量自增1。
每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为7的页面中一个称之为Max Row ID的属性处（我们前边介绍表空间结构时详细说过）。
当系统启动时，会将上边提到的Max Row ID属性加载到内存中，将该值加上256之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于Max Row ID属性值）。

这个Max Row ID属性占用的存储空间是8个字节，当某个事务向某个包含row_id隐藏列的表插入一条记录，并且为该记录分配的row_id值为256的倍数时，就会向系统表空间页号为7的页面的相应偏移量处写入8个字节的值。

[MySQL]额外知识

子查询