登录/注册

实战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> |
+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+

我们并未贴上全部结果,而是选取了其中重要的部分。idSELECT语句的 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 |
+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+

我们仍然截取了部分信息,我们将目光聚焦在typerows上,这里的type不再是const而是ALLALL表示全表扫描,是最慢的一个级别,rows5,表示这次查询将会扫描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> |
+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+

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

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

3. 实践

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

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

3.1 偶尔很慢,平时都 OK

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

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

3.2 一直很慢

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

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

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

4. 小结

  • 一般情况下,SQL 优化的落脚点其实就是使用索引,索引能够大幅加快查询速度,提高性能。
  • 对于 SQL 语句本身的优化,除了soar以外,你也可以查阅相关的资料获取经验。
原文地址:https://blog.csdn.net/q7825103/article/details/105379906
文章来源与用户投稿或网络采集,如果您是本文的作者,发现版权或转载方式问题请右侧邮箱联系管理员。

抢沙发

共同学习,写下你的评论
姓名
电子邮件
个人主页