测试4
测试大礼包
爬虫实战特训课

实战5：如何优化你的 SQL 查询

1. 前言

在前面的小节和实战中，我们一直在学习和讨论如何写 SQL，如何用 SQL 完成一个业务功能点。本小节，我们将以优化的角度来探讨一下如何优化 SQL，让 SQL 更加高效的运行。

SQL 优化是一个很大的专题，本节会介绍几种常见的 SQL 优化手段和一些好用的优化工具。

2. 工具

SQL 优化并不简单，因此我们可以利用一些工具来帮助我们。

2.1 soar

soar是小米开源的一款 SQL 优化和改写的工具，它使用简单而且特性十分丰富，你可以点击此链接来安装 soar，安装成功后，我们来一起使用一下 soar。

2.1.1 soar 实例

举个简单的例子：

soar -query 'SELECT * FROM imooc_user WHERE id=1;'

soar 的使用十分简单，通过query参数指定一条需要分析的SQL语句即可，调用成功后，soar会自动在控制台打印出分析结果，如下：

# Query: 93A5517F0971C47A

★ ★ ★ ★ ☆ 95分

​```sql

SELECT
  *
FROM
  imooc_user
WHERE
  id= 1
​```

## 不建议使用 SELECT * 类型查询

* **Item:**  COL.001

* **Severity:**  L1

* **Content:**  当表结构变更时，使用 * 通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据。

soar 分析的结果默认以markdown的格式展现，且分析结果十分丰富，不仅给出了格式化后易读的 SQL 和建议，还打了分。

其中Item是规则代码，每个规则都有相应的代号，Severity是等级，等级越高代表越危险，越需要优化，L1是较低的等级，Content指明了优化原因。

2.1.2 soar 优化 SQL

上面的语句中，建议不使用*，因为字段变更将导致数据发生变化，按照 soar 的提示我们优化一下 SQL：

soar -query 'SELECT id,username,age FROM imooc_user WHERE id=1;' > profile.md

我们不仅优化了*，且将分析结果保存到了本地的profile.md文件，内容如下：

# Query: 54BE4DEFF01C4432

★ ★ ★ ★ ★ 100分

​```sql

SELECT
  id, username, age
FROM
  imooc_user
WHERE
  id= 1
​```

## OK

优化后，直接获得了 100 分（满分）。

soar 是一款简单且好用的工具，它还有很多特性值得大家去挖掘和探索，你可以点开它的文档去观阅一番，对于它的介绍这里也将告一段落了。

2.2 EXPLAIN

explain是数据库自带的 SQL 分析工具，简单、实用且强大。下面我们以 MySQL 的explain工具为例来介绍一下它的使用。

请先执行一下语句方便进行测试：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

2.2.1 使用 explain

explain的使用很简单，在它的后面接上需要分析的 SQL 语句即可，如下：

EXPLAIN SELECT * FROM imooc_user WHERE id=1;

执行成功后，得到如下结果：

+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+
| id | select_type | table      | type  | possible_keys | key     | ref   | rows | filtered | Extra  |
+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | imooc_user | const | PRIMARY       | PRIMARY | const | 1    | 100.0    | <null> |
+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+

我们并未贴上全部结果，而是选取了其中重要的部分。id是SELECT语句的 id，select_type代表这次查询仅仅是一条简单的查询，table无需赘言，possible_keys表示可能用到的索引，extra是一些额外信息。

而剩下的就是一些比较重要的信息了：

  • type是针对单表的访问方法类型，const是常数类型，表示查询速度极快，在常数时间内即可返回；
  • key表示使用到的索引，PRIMARY表示用到了主键索引；
  • ref意思是使用索引等值查询时，与索引列比较的对象信息，这个比较抽象，大致的意思是，索引使用了何种类型进行比较，const即使用常数比较，id 1 就是常数；
  • rows是预估需要读取记录的条数，1代表只需要读取一行，rows 越小越好；
  • filtered表示查询过滤后未搜索到的记录百分比，100.0表示未搜索到的几乎占100%，filtered 越大越好。

因此从分析结果可以看出，这条语句性能极好，除非数据库波动，否则完全不用担心查询速度问题。

2.2.2 explain 优化 SQL

那么什么样的语句查询效率比较低了，我们看一下这个语句：

EXPLAIN SELECT * FROM imooc_user WHERE age=22;

分析结果如下：

+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+
| id | select_type | table      | type | possible_keys | key    | ref    | rows | filtered | Extra       |
+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+
| 1  | SIMPLE      | imooc_user | ALL  | <null>        | <null> | <null> | 5    | 20.0     | Using where |
+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+

我们仍然截取了部分信息，我们将目光聚焦在type和rows上，这里的type不再是const而是ALL，ALL表示全表扫描，是最慢的一个级别，rows为5，表示这次查询将会扫描5条记录，而我们总共才5条记录。

这个查询的性能是极为糟糕的，试想一下，如果该表的数据是几万行乃至几十万行，一次查询得扫描全部，那得多慢啊。

既然这么慢，可以优化吗？当然可以，如果你有相关的经验，第一个想到的就是建索引。

CREATE INDEX age_index ON imooc_user(age);

索引建立完毕后，我们再次分析：

EXPLAIN SELECT * FROM imooc_user WHERE age=22;

+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+
| id | select_type | table      | type | possible_keys | key       | ref   | rows | filtered | Extra  |
+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+
| 1  | SIMPLE      | imooc_user | ref  | age_index     | age_index | const | 1    | 100.0    | <null> |
+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+

type从ALL变成了ref，rows也仅仅只有1行；ref也是一种速度很快的类型，即查询使用到了常数匹配索引，在结果中key字段也指明了，该次查询有使用到我们新建的索引age_index。

explain 的内容很多，而且不同的数据库的实现也不同，如果你需要使用它，请按照你使用的数据库查阅该数据库权威的文档来学习。

3. 实践

接下来，我们以实践的角度来看一个面试题——一条SQL语句执行的很慢，导致慢的原因有哪些了？。

首先，考虑到数据库可能会有波动，我们分类来谈论这个问题。

3.1 偶尔很慢，平时都 OK

一条语句在检测的情况下，大部分时间都比较快，只是偶尔会突然很慢，那么造成它慢的原因有很多种，我们挑几个常见的：

  • 数据库在刷新数据，写磁盘：数据库是以页的形式来读、写数据的，突然有时候页需要更新或者删除了，数据库就必须执行它，于是查询就慢了下来。
  • 数据库在同步、备份：有时候数据库会找个特定的时间备份那么一次，刚好被你给撞到了，当然这个概率很低。
  • 没有锁，我要等待别人释放锁：查询的数据被别人锁住了，我需要等待，自然就慢了。

3.2 一直很慢

如果出现某条语句一直都很慢的情况，那么大概率是语句本身或者数据表索引的问题了。

  • 没有索引：如上面age字段没有索引，全表扫描，当然很慢。
  • 没走索引：有索引，可是因为使用函数或者模糊搜索导致查询没有走索引；有索引，可是SQL语句不明确，导致数据库走错索引，应该优化SQL语句，或者USING INDEX强制使用索引。
  • 语句本身：使用了POW，CONTACT等函数使数据库没法走索引。

正如小节开头所说，SQL 优化是一个很大的专题，一本极厚的书可能也无法全部囊括。不过这也不代表你无法学习，先熟练掌握几个好用的工具，如本小节提到的两个工具，然后慢慢的学习和实践，相信你能在优化的路上走的很远。

4. 小结

  • 一般情况下，SQL 优化的落脚点其实就是使用索引，索引能够大幅加快查询速度，提高性能。

  • 对于 SQL 语句本身的优化，除了soar以外，你也可以查阅相关的资料获取经验。