共计 6129 个字符,预计需要花费 16 分钟才能阅读完成。
SQL 作为关系型数据库的规范语言,是 IT 从业人员必不可少的技能之一。SQL 自身并不难学,编写查问语句也很容易,然而想要编写出可能高效运行的查问语句却有肯定的难度。
查问优化是一个简单的工程,波及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行程序、索引以及统计信息的采集等,甚至应用程序和零碎的整体架构。本文介绍几个要害法令,能够帮忙咱们编写高效的 SQL 查问;尤其是对于初学者而言,这些法令至多能够防止咱们写出性能很差的查问语句。
以下法令实用于各种关系型数据库,包含但不限于:MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。如果感觉文章有用,欢送评论、点赞、转发朋友圈反对。
法令一:只返回须要的后果
肯定要为查问语句指定 WHERE 条件,过滤掉不须要的数据行。通常来说,OLTP 零碎每次只须要从大量数据中返回很少的几条记录;指定查问条件能够帮忙咱们通过索引返回后果,而不是全表扫描。绝大多数状况下应用索引时的性能更好,因为索引(B- 树、B+ 树、B* 树)执行的是二进制搜寻,具备对数工夫复杂度,而不是线性工夫复杂度。以下是 MySQL 聚簇索引的示意图:举例来说,假如每个索引分支节点能够存储 100 个记录,100 万(1003)条记录只须要 3 层 B- 树即可实现索引。通过索引查找数据时须要读取 3 次索引数据(每次磁盘 IO 读取整个分支节点),加上 1 次磁盘 IO 读取数据即可失去查问后果。纯干货!15000 字语法手册分享给你
相同,如果采纳全表扫描,须要执行的磁盘 IO 次数可能高出几个数量级。当数据量减少到 1 亿(1004)时,B- 树索引只须要再减少 1 次索引 IO 即可;而全表扫描则须要再减少几个数量级的 IO。
同理,咱们应该防止应用 SELECT * FROM,因为它示意查问表中的所有字段。这种写法通常导致数据库须要读取更多的数据,同时网络也须要传输更多的数据,从而导致性能的降落。
法令二:确保查问应用了正确的索引
如果短少适合的索引,即便指定了查问条件也不会通过索引查找数据。因而,咱们首先须要确保创立了相应的索引。一般来说,以下字段须要创立索引:
- 经常出现在 WHERE 条件中的字段建设索引能够防止全表扫描;
- 将 ORDER BY 排序的字段退出到索引中,能够防止额定的排序操作;
- 多表连贯查问的关联字段建设索引,能够进步连贯查问的性能;
- 将 GROUP BY 分组操作字段退出到索引中,能够利用索引实现分组。
即便创立了适合的索引,如果 SQL 语句写的有问题,数据库也不会应用索引。导致索引生效的常见问题包含:
- 在 WHERE 子句中对索引字段进行表达式运算或者应用函数都会导致索引生效,这种状况还包含字段的数据类型不匹配,例如字符串和整数进行比拟;
- 应用 LIKE 匹配时,如果通配符呈现在左侧无奈应用索引。对于大型文本数据的含糊匹配,应该思考数据库提供的全文检索性能,甚至专门的全文搜索引擎(Elasticsearch 等);
- 如果 WHERE 条件中的字段上创立了索引,尽量设置为 NOT NULL;不是所有数据库应用 IS [NOT] NULL 判断时都能够利用索引。
执行打算(execution plan,也叫查问打算或者解释打算)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描拜访表中的数据,连贯查问的实现形式和连贯的程序等。如果 SQL 语句性能不够现实,咱们首先应该查看它的执行打算,通过执行打算(EXPLAIN)确保查问应用了正确的索引。
法令三:尽量避免应用子查问
以 MySQL 为例,以下查问返回月薪大于部门均匀月薪的员工信息:
EXPLAIN ANALYZE
SELECT emp_id, emp_name
FROM employee e
WHERE salary > (SELECT AVG(salary)
FROM employee
WHERE dept_id = e.dept_id);
-> Filter: (e.salary > (select #2)) (cost=2.75 rows=25) (actual time=0.232..4.401 rows=6 loops=1)
-> Table scan on e (cost=2.75 rows=25) (actual time=0.099..0.190 rows=25 loops=1)
-> Select #2 (subquery in condition; dependent)
-> Aggregate: avg(employee.salary) (actual time=0.147..0.149 rows=1 loops=25)
-> Index lookup on employee using idx_emp_dept (dept_id=e.dept_id) (cost=1.12 rows=5) (actual time=0.068..0.104 rows=7 loops=25)
从执行打算能够看出,MySQL 中采纳的是相似 Nested Loop Join 实现形式;子查问循环了 25 次,而实际上能够通过一次扫描计算并缓存每个部门的均匀月薪。以下语句将该子查问替换为等价的 JOIN 语句,实现了子查问的开展(Subquery Unnest):
EXPLAIN ANALYZE
SELECT e.emp_id, e.emp_name
FROM employee e
JOIN (SELECT dept_id, AVG(salary) AS dept_average
FROM employee
GROUP BY dept_id) t
ON e.dept_id = t.dept_id
WHERE e.salary > t.dept_average;
-> Nested loop inner join (actual time=0.722..2.354 rows=6 loops=1)
-> Table scan on e (cost=2.75 rows=25) (actual time=0.096..0.205 rows=25 loops=1)
-> Filter: (e.salary > t.dept_average) (actual time=0.068..0.076 rows=0 loops=25)
-> Index lookup on t using <auto_key0> (dept_id=e.dept_id) (actual time=0.011..0.015 rows=1 loops=25)
-> Materialize (actual time=0.048..0.057 rows=1 loops=25)
-> Group aggregate: avg(employee.salary) (actual time=0.228..0.510 rows=5 loops=1)
-> Index scan on employee using idx_emp_dept (cost=2.75 rows=25) (actual time=0.181..0.348 rows=25 loops=1)
改写之后的查问利用了物化(Materialization)技术,将子查问的后果生成一个内存长期表;而后与 employee 表进行连贯。通过理论执行工夫能够看出这种形式更快。
以上示例在 Oracle 和 SQL Server 中会主动执行子查问开展,两种写法成果雷同;在 PostgreSQL 中与 MySQL 相似,第一个语句应用 Nested Loop Join,改写为 JOIN 之后应用 Hash Join 实现,性能更好。
另外,对于 IN 和 EXISTS 子查问也能够得出相似的论断。因为不同数据库的优化器能力有所差别,咱们应该尽量避免应用子查问,思考应用 JOIN 进行重写。搜寻公众号 民工哥技术之路,回复“1024”,送你一份技术资源大礼包。
法令四:不要应用 OFFSET 实现分页
分页查问的原理就是先跳过指定的行数,再返回 Top-N 记录。分页查问的示意图如下: 数据库个别反对 FETCH/LIMIT 以及 OFFSET 实现 Top-N 排行榜和分页查问。当表中的数据量很大时,这种形式的分页查问可能会导致性能问题。以 MySQL 为例:
-- MySQL
SELECT *
FROM large_table
ORDER BY id
LIMIT 10 OFFSET N;
以上查问随着 OFFSET 的减少,速度会越来越慢;因为即便咱们只须要返回 10 条记录,数据库依然须要拜访并且过滤掉 N(比方 1000000)行记录,即便通过索引也会波及不必要的扫描操作。
对于以上分页查问,更好的办法是记住上一次获取到的最大 id,而后在下一次查问中作为条件传入:
-- MySQL
SELECT *
FROM large_table
WHERE id > last_id
ORDER BY id
LIMIT 10;
如果 id 字段上存在索引,这种分页查问的形式能够根本不受数据量的影响。
法令五:理解 SQL 子句的逻辑执行程序
以下是 SQL 中各个子句的语法程序,后面括号内的数字代表了它们的逻辑执行程序:
(6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
(1) FROM t1 JOIN t2
(2) ON (join_conditions)
(3) WHERE where_conditions
(4) GROUP BY col1, col2
(5)HAVING having_condition
(7) UNION [ALL]
...
(8) ORDER BY col1 ASC,col2 DESC
(9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;
也就是说,SQL 并不是依照编写程序先执行 SELECT,而后再执行 FROM 子句。从逻辑上讲,SQL 语句的执行程序如下:
- 首先,FROM 和 JOIN 是 SQL 语句执行的第一步。它们的逻辑后果是一个笛卡尔积,决定了接下来要操作的数据集。留神逻辑执行程序并不代表物理执行程序,实际上数据库在获取表中的数据之前会应用 ON 和 WHERE 过滤条件进行优化拜访;
- 其次,利用 ON 条件对上一步的后果进行过滤并生成新的数据集;
- 而后,执行 WHERE 子句对上一步的数据集再次进行过滤。WHERE 和 ON 大多数状况下的成果雷同,然而外连贯查问有所区别,咱们将会在下文给出示例;
- 接着,基于 GROUP BY 子句指定的表达式进行分组;同时,对于每个分组计算聚合函数 agg_func 的后果。通过 GROUP BY 解决之后,数据集的构造就产生了变动,只保留了分组字段和聚合函数的后果;
- 如果存在 GROUP BY 子句,能够利用 HAVING 针对分组后的后果进一步进行过滤,通常是针对聚合函数的后果进行过滤;
- 接下来,SELECT 能够指定要返回的列;如果指定了 DISTINCT 关键字,须要对后果集进行去重操作。另外还会为指定了 AS 的字段生成别名;
- 如果还有汇合操作符(UNION、INTERSECT、EXCEPT)和其余的 SELECT 语句,执行该查问并且合并两个后果集。对于汇合操作中的多个 SELECT 语句,数据库通常能够反对并发执行;
- 而后,利用 ORDER BY 子句对后果进行排序。如果存在 GROUP BY 子句或者 DISTINCT 关键字,只能应用分组字段和聚合函数进行排序;否则,能够应用 FROM 和 JOIN 表中的任何字段排序;
- 最初,OFFSET 和 FETCH(LIMIT、TOP)限定了最终返回的行数。
理解 SQL 逻辑执行程序能够帮忙咱们进行 SQL 优化。例如 WHERE 子句在 HAVING 子句之前执行,因而咱们应该尽量应用 WHERE 进行数据过滤,防止无谓的操作;除非业务须要针对聚合函数的后果进行过滤。
除此之外,了解 SQL 的逻辑执行程序还能够帮忙咱们防止一些常见的谬误,例如以下语句:
-- 谬误示例
SELECT emp_name AS empname
FROM employee
WHERE empname ='张飞';
该语句的谬误在于 WHERE 条件中援用了列别名;从下面的逻辑程序能够看出,执行 WHERE 条件时还没有执行 SELECT 子句,也就没有生成字段的别名。
另外一个须要留神的操作就是 GROUP BY,例如:
-- GROUP BY 谬误示例
SELECT dept_id, emp_name, AVG(salary)
FROM employee
GROUP BY dept_id;
因为通过 GROUP BY 解决之后后果集只保留了分组字段和聚合函数的后果,示例中的 emp_name 字段曾经不存在;从业务逻辑上来说,依照部门分组统计之后再显示某个员工的姓名没有意义。如果须要同时显示员工信息和所在部门的汇总,能够应用窗口函数。扩大:SQL 语法速成手册
还有一些逻辑问题可能不会间接导致查问出错,然而会返回不正确的后果;例如外连贯查问中的 ON 和 WHERE 条件。以下是一个左外连贯查问的示例:
SELECT e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d ON (e.dept_id = d.dept_id)
WHERE e.emp_name ='张飞';
emp_name|dept_name|
--------|---------|
张飞 | 行政管理部 |
SELECT e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d ON (e.dept_id = d.dept_id AND e.emp_name ='张飞');
emp_name|dept_name|
--------|---------|
刘备 | [NULL]|
关羽 | [NULL]|
张飞 | 行政管理部 |
诸葛亮 | [NULL]|
...
- 第一个查问在 ON 子句中指定了连贯的条件,同时通过 WHERE 子句找出了“张飞”的信息。
- 第二个查问将所有的过滤条件都放在 ON 子句中,后果返回了所有的员工信息。这是因为左外连贯会返回左表中的全副数据,即便 ON 子句中指定了员工姓名也不会失效;而 WHERE 条件在逻辑上是对连贯操作之后的后果进行过滤。
总结
SQL 优化实质上是理解优化器的的工作原理,并且为此创立适合的索引和正确的语句;同时,当优化器不够智能的时候,手动让它智能。
起源:blog.csdn.net/horses/article/details/105695431