实战5:如何优化你的SQL查询
1. 前言
在前面的小节和实战中,我们一直在学习和讨论如何写 ,如何用 完成业务点。本小节,我们将以优化
的角度来探讨一下如何优化 ,让 更加高效的运行。
优化是很大的专题,本节会介绍几种常见的 优化手段和一些好用的优化工具。
2. 工具
优化并不简单
,因此我们可以利用一些工具来帮助我们。
是小米开源的一款 优化和改写的工具,它使用简单而且特性十分丰富,你可以点击此来安装 soar,安装成功后,我们来一起使用一下 soar。
举个简单的例子:
soar -query 'SELECT * FROM imooc_user WHERE id=1;'
soar 的使用十分简单,通过query
参数指定一条需要分析的语句即可,成功后,soar
会在控制台打印出分析结果,如下:
# Query: 93A5517F0971C47A★ ★ ★ ★ ☆ 95分 ``` SELECT * FROM imooc_user WHERE id= 1```## 不建议使用 SELECT * 类型* **Item:** COL.001 * **Severity:** L1 * **Content:** 当表结构变更时,使用 \* 符选择所有列将导致的含义和行为会发生更改,可能导致返回更多的数据。
soar 分析的结果认以markdown
的格式展现,且分析结果十分丰富,不仅给出了格式化后易读的 和建议,还打了分。
其中Item
是规则,每个规则都有相应的代号,Severity
是等级,等级越高代表越危险,越需要优化,L1
是较低的等级,Content
指明了优化原因。
上面的语句中,建议不使用*
,因为字段变更将导致数据发生变化,按照 soar 的我们优化一下 :
soar -query 'SELECT id,username,age FROM imooc_user WHERE id=1;' > profile.md
我们不仅优化了*
,且将分析结果保存到了本地的profile.md
,如下:
# Query: 54BE4DEFF01C4432★ ★ ★ ★ ★ 100分 ``` SELECT id, username, age FROM imooc_user WHERE id= 1```## OK
优化后,直接获得了 100 分(满分)。
soar 是一款简单且好用的工具,它还有很多特性值得大家去挖掘和探索,你可以点开它的文档去观阅一番,对于它的介绍这里也将告一段落了。
explain
是的 分析工具,简单、实用且强大。下面我们以 的explain
工具为例来介绍一下它的使用。
请先执行一下语句方便进行测试:
DROP TABLE IF EXISTS imooc_user;CREATE TABLE imooc_user( id int PRIMARY KEY, username varchar(), age int);INSERT INTO imooc_user(id,username,age)VALUES (,'peter',),(,'pedro',),(,'jerry',),(,'mike',),(,'tom',);
explain
的使用很简单,在它的后面接上需要分析的 语句即可,如下:
EXPLAIN SELECT * FROM imooc_user WHERE id=;
执行成功后,得到如下结果:
+----+-------------+------------+-------+---------------+---------+-------+------+----------+--------+| 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 越大越好。
因此从分析结果可以看出,这条语句极好,除非波动,否则完全不用担心速度问题。
那么什么样的语句效率比较低了,我们看一下这个语句:
EXPLAIN SELECT * FROM imooc_user WHERE age=;
分析结果如下:
+----+-------------+------------+------+---------------+--------+--------+------+----------+-------------+| 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=;
+----+-------------+------------+------+---------------+-----------+-------+------+----------+--------+| 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. 实践
接下来,我们以实践的角度来看面试题——一条语句执行的很慢,导致慢的原因有哪些了?
。
首先,考虑到可能会有波动,我们来谈论这个问题。
一条语句在检测的情况下,大部分时间都比较快,只是偶尔会突然很慢,那么造成它慢的原因有很多种,我们挑几个常见的:
在刷新数据,写磁盘:是以页的形式来读、写数据的,突然有时候页需要更新或者了,就必须执行它,于是就慢了下来。
在同步、备份:有时候会找个特定的时间备份那么一次,刚好被你给撞到了,当然这个概率很低。
没有锁,我要等待别人释放锁:的数据被别人锁住了,我需要等待,自然就慢了。
如果出现某条语句一直都很慢的情况,那么大概率是语句本身或者数据表索引的问题了。
没有索引:如上面age
字段没有索引,全表扫描,当然很慢。
没走索引:有索引,可是因为使用或者模糊导致没有走索引;有索引,可是语句不明确,导致走错索引,应该优化语句,或者USING INDEX
强制使用索引。
语句本身:使用了POW
,CONTACT
等使没法走索引。
正如小节开头所说, 优化是很大的专题,一本极厚的书可能也无法全部囊括。不过这也不代表你无法学习,先熟练掌握几个好用的工具,如本小节提到的两个工具,然后慢慢的学习和实践,相信你能在优化的路上走的很远。
4. 小结
一般情况下, 优化的落脚点其实就是使用索引
,索引能够大幅加快速度,提高。
对于 语句本身的优化,除了soar
以外,你也可以查阅经验。