---
dir:
  order: 11
---

# 使用高效查询

## 参数是子查询

### 使用 EXISTS 代替 IN

一般来说，如果代码中用到了大量的 IN 谓词，只要对其优化，就能大幅度提升性能。

在大多数时候，`[NOT] IN` 和`[NOT] EXISTS`返回结果是相同的，但是当它们用于子查询时，EXISTS 的速度回快一些。

> [!tip]
>
> 如果连接列`id`上建立了索引，那么查询`class_b`时不用查询实际的表，只需要查询索引就可以了。
>
> 如果使用`EXISTS`，那么只要查到一行数据满足条件就会终止查询，不用像使用`IN`一样扫描全表。
>
> > [!important]
> > 在这一点上和 `NOT EXISTS`是一样的。

**慢查询**

> [!tip]
> 但是在可读性上`IN`要比`EXISTS`好

```sql
SELECT
  *
FROM
  class_a
WHERE
  id IN ( SELECT id FROM class_b )
```

**快查询**

> [!info]
> 使用 IN 查询时，数据库首先会执行子查询，然后将结果存储在一张临时表里（内联视图），接着再扫描整个视图。在很多情况下，这种做法非常耗资源，而且工作表中通常没有索引。

```sql
SELECT
  *
FROM
  class_a a
WHERE
  EXISTS ( SELECT * FROM class_b b WHERE a.id = b.id )
```

### 使用连接替代 IN

还可以像下面这扁平化。

> [!info]
>
> 这种写法至少可以用到一张表上的`id`列上的索引。而且，因为没有了子查询，所以数据库也不会生成中间表。
>
> 但是没有索引，那么和连接相比，可能`EXISTS`会略胜一筹。

```sql
SELECT
  a.id,
  a.NAME
FROM
  class_a a
  INNER JOIN class_b b ON a.id = b.id
```

## 避免排序

> [!caution]
> 排序如果只在内存中进行，那么还好；如果内存不足需要在硬盘上排序，性能会急剧恶化。

会排序的运算包含下面这些。

- `GROUP BY`子句
- `ORDER BY`子句
- 聚合函数（`SUM`、`COUNT`、`AVG`、`MAX`、`MIN`）
- `DISTINCT`
- 集合运算符（`UNION`、`INTERSECT`、`EXCEPT`）
- 窗口函数（`RANK`、`ROW_NUMBER`）

### 灵活使用 ALL

SQL 中有`UNION`、`INTERSECT`、`EXCEPT`三个集合运算符，在默认的使用方式下，这些运算符会为了排除重复数据进行排序。

```sql
SELECT * FROM class_a UNION SELECT * FROM class_b
```

如果不在乎结果中是否有重复数据，可以使用`UNION ALL`代替`UNION`。

> [!warning]
> 加上`ALL`可选项是优化性能的一个非常有效手段，但是各种 DBMS 对它实现情况参差不齐。

```sql
SELECT * FROM class_a UNION ALL SELECT * FROM class_b
```

### 使用 EXISTS 代替 DISTINCT

为了排除重复数据，`DISTINCT`也会进行排序。可以考虑使用`EXISTS`，以避免排序。

```sql
SELECT DISTINCT
  i.item_no
FROM
  items i
  INNER JOIN saleshistory sh ON i.item_no = sh.item_no
```

最好办法是用`EXISTS`

> [!tip]
> 这样执行过程中不会排序，而且使用`EXISTS`和使用连接一样高效

```sql
SELECT
  item_no
FROM
  items i
WHERE
  EXISTS ( SELECT * FROM saleshistory sh WHERE i.item_no = sh.item_no )
```

### 在极值函数中使用（MAX/MIN）

如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表。

**扫描整张表**

```sql
SELECT MAX(item) FROM items
```

**这样写可以用到索引**

> [!tip]
> 因为 item_no 是表 items 唯一索引，所以效果更好。

```sql
SELECT MAX(item_no) FROM items
```

### 能写在 WHERE 语句里就不要写 HAVING 里

```sql
SELECT
  sale_date,
  SUM( quantity )
FROM
  saleshistory
GROUP BY
  sale_date
HAVING
  sale_date = '2018-10-01'
```

**聚合前使用 WHERE 子句过滤**

> [!important]
> HAVING 子句是针对聚合后生成的视图进行筛选的，但是很多时候，聚合后的视图并没有继承原表的索引结构。

```sql
SELECT
  sale_date,
  SUM( quantity )
FROM
  saleshistory
WHERE
  sale_date = '2018-10-01'
GROUP BY
  sale_date
```

### 在 GROUP BY 子句和 ORDER BY 中使用索引

一般来说，GROUP BY 子句和 RODER BY 子句都会进行排序，以对行进行排列和替换。不过，通过指定代索引的列作为 GROUP BY 和 ORDER BY 的列，可以实现高速查询。

## 真的用到索引了吗

### 在索引字段上进行运算

```sql
SELECT * FROM items WHERE col_1* 1.1 > 100
```

> [!tip]
>
> 人们普遍认为，SQL语言主要目的不是进行运算，但实际上，数据库大多连这种程度的转换也不会做。

把运算符放到查询条件右侧，就可以用到索引了。

```sql
SELECT * FROM items WHERE col_1 > 100 / 1.1
```

同样，在查询条件的左侧使用函数时，也不会用到索引

```sql
SELECT * FROM items WHERE SUBSTR(col_1,1,1) = 'a'
```

> [!warning]
>
> 使用索引时，列应该是原始字段。

### 索引字段存在NULL

索引中的NULL不好处理，对此实现也各种不同。这是因为如果使用`IS NULL`和`IS NOT NULL`，索引就无法使用了，而且`NULL`很多字段也无法使用索引。

```sql
SELECT * FROM table WHERE col_1 IS NULL
```

如果需要使用`IS NOT NULL`的功能，又想用到索引，那么可以使用下面的方法。这里默认

```sql
SELECT * FROM table WHERE col_1 > 0
```

原理很简单，只要不使用不等号制定一个比最小值最还小的数，就可以选出`col_1`的值。因为`col_  > NULL`的执行结果是`unknown`，所以当col_1列的值为`NULL`的行不会被选择。

### 使用否定形式

- `<>`
- `!=`
- `NOT IN`

> [!warning]
>
> 下面的SQL语句也不会用到索引。

```sql
SELECT * FROM table WHERE col_1 <> 100
```

### 使用OR

在`col_1`和 `col_2`上分别建立了不同的索引，或者建立了`(col_1,col_2)`这样联合索引，如果使用`OR`连接条件，那么要么不会用到索引，要么用到了，但是效率比`AND`要差很多。

> [!important]
>
> 如果无论如何都要使用OR，那么有一个办法是使用位图索引。但是如果使用这种索引，更新数据的性能开销会很大，而且索引本身用途也存在限制（通常用于在线更新处理较少的`BI/DWH`系统

```sql
SELECT * FROM table WHERE col_1	> 100 OR col_2 = 'abc'
```

### 使用联合索引，列的顺序错误

假设存在一个顺序是`col_1,col_2,col_3`的联合索引。设置条件索引顺序很重要。

> [!important]
>
> 联合索引中第一列(col_1)必须写在查询条件的开头，而且索引中列的顺序不能颠倒。有些数据库颠倒后也能使用索引，但是性能还是比顺序正确时差一些。
>
> 如果无法保证查询条件里的列顺序与索引一致，可以考虑将联合索引拆分为多个索引。

> [!tip]
>
> 如：Oracle，即使改变索引顺序，也可以通过SKIP SCAN的形式来使用索引，但是效率要比正常扫描索引时低。

```sql
○ SELECT * FROM table  WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500
○ SELECT * FROM table  WHERE col_1 = 10 AND col_2 = 100
× SELECT * FROM table  WHERE col_1 = 10 AND col_3 = 500
× SELECT * FROM table  WHERE col_2 = 100 AND col_3 = 500
```

### 使用LIKE谓词

> [!important]
>
> 使用LIKE谓词进行后方一致或中间一致的匹配。只有前方一致的匹配才能用到索引。

```sql
× SELECT * FROM table WHERE col_1 LIKE '%a'
× SELECT * FROM table WHERE col_1 LIKE '%a%'
○ SELECT * FROM table WHERE col_1 LIKE 'a%'
```

### 进行默认的类型转换

下面是对字符串类型的`col_1`列指定条件的实例。

> [!tip]
>
> 默认类型转换不仅会增加额外性能开销，还会导致索引不可用，可以说是百害而无一利。虽然这有些还不至于出错，但还是不要嫌麻烦，在需要类型转换时显示斤西瓜类型转换。

>[!info]
>
>别忘了转换要写在一边，而不是另一边。
>
>在有些DBMS（如PostgreSQL）中，数据类型写在表达式的不同侧就会发生错误，显式进行类型转换还有利于我们在开发时注意避免编写性能低下查询。

```sql
× SELECT * FROM table WHERE col_1 = 10
× SELECT * FROM table WHERE col_1 = '10'
○ SELECT * FROM table WHERE col_1 = CAST(10, AS CHAR(2))
```

## 减少中间表

频繁使用中间表会带来两个问题：一是展开数据需要耗费内存（或存储器）资源，二是原始表中的索引不容易被用到（特别是聚合时）。因此，尽量减少中间表的使用也是一个提升性能的重要方法。

### 灵活使用HAVING子句

对聚合结果指定筛选条件时，使用HAVING子句是基本原则。不习惯使用HAVING 子句的数据库工程师可能会倾向于像下面这样先生成一张中间表，然后在 WHERE 子句中指定筛选条件。

```sql
SELECT
  * 
FROM
  ( SELECT sale_date, MAX( quantity ) AS max_qty FROM saleshistory GROUP BY sale_date ) WHBRE max_qty >= 10;
```

**优化后**

HAVING 子句和聚合操作是同时执行的，所以比起先生成中间表，然后再执行的 WHERE 子句，效率会更高一些，而且代码看起来也更简洁。

```sql
SELECT
  sale_date,
  MAX( quantity ) 
FROM
  saleshistory 
GROUP BY
  sale_date 
HAVING
  MAX( quantity ) >= 10;
```

### 需要对多个字段使用 IN 谓词时，先将它们汇总到一处

SQL-92 中加入了行与行比较的功能。这样一来，比较谓词`=、<、>`和 IN 谓词的参数就不能是标量值，而应是值列表了。我们来看看下面这道例题。这里对多个字段使用了`IN` 谓词，id 列是主键。

```sql
SELECT
  id,
  state,
  city 
FROM
  Addressesl A1 
WHERE
  state IN ( SELECT state FROM Addresses2 A2 WHERE A1.id = A2.id ) 
  AND city IN ( SELECT ity FROM Addresses2 A2 WHERE A1.id = A2.id );
```

这段代码中用到了两个子查询。但是，如果像下面这样把字段连接在起，就能把逻辑写在一处了。

```sql
SELECT
  * 
FROM
  Addressesl A1 
WHERE
  id || state || city IN ( SELECT id || statel || city ) 
FROM
  Addresses2 A2 
  );
```

### 先进行连接再进行聚合

连接和聚合同时使用时，先进行连接操作可以避免产生中间表。原因是，从集合运算的角度来看，连接做的是“乘法运算”连接表双方是一对一、一对多的关系时，连接运算后数据的行数不会增加。而且，因为在很多设计中多对多的关系可以分解成两个一对多的关系，所以这个技巧可以应用在大部分情况中。

### 合理地使用视图

> [!warning]
>
> 一般来说，我们需要格外注意，避免在视图中进行聚合操作。

视图是非常方便的工具，相信有很多人会在日常工作中频繁地使用它。但是，如果没有经过深入思考就定义复杂的视图，可能会带来巨大的性能问题。特别是当视图的定义语句中包含以下运算时，SQL会非常低效，执行速度也会变得非常慢。

- 聚合函数(`AVG、COUNT、SUM、MIN、MAX`)
- 集合运算符(`UNION、INTERSECT、EXCEPT`等)

