당신이 알아야 할 SQL 최적화 최소 규칙

SQL 튜토리얼 관계형 데이터베이스의 표준 언어로, IT 실무자에게 필수적인 기술 중 하나입니다. SQL 자체는 배우기 어렵지 않고 쿼리문 작성도 매우 쉽지만 효율적으로 실행할 수 있는 쿼리문을 작성하는 것은 어렵습니다.

권장(무료): SQL 튜토리얼

쿼리 최적화는 하드웨어부터 매개변수 구성, 다양한 데이터베이스에 대한 파서, 옵티마이저 구현, SQL 문 실행 순서 및 인덱스까지 모든 것을 포함하는 복잡한 프로젝트입니다. 통계 정보 등의 수집은 물론 애플리케이션 및 시스템의 전체 아키텍처까지 포함합니다. 이 기사에서는 특히 초보자의 경우 효율적인 SQL 쿼리를 작성하는 데 도움이 되는 몇 가지 주요 규칙을 소개합니다. 이러한 규칙은 최소한 성능이 떨어지는 쿼리 문을 작성하는 것을 방지할 수 있습니다.

다음 규칙은 MySQL, Oracle, SQL Server, PostgreSQL, SQLite 등을 포함하되 이에 국한되지 않는 다양한 관계형 데이터베이스에 적용됩니다. 기사가 유용하다고 생각되면 댓글을 달고 좋아요를 누른 다음 지원을 위해 친구들에게 전달하세요.

규칙 1: 필요한 결과만 반환

불필요한 데이터 행을 필터링하려면 쿼리 문에 WHERE 조건을 지정해야 합니다. 일반적으로 OLTP 시스템은 대량의 데이터에서 한 번에 몇 개의 레코드만 반환하면 됩니다. 쿼리 조건을 지정하면 전체 테이블 스캔 대신 인덱스를 통해 결과를 반환하는 데 도움이 될 수 있습니다. 대부분의 경우 인덱스(B-트리, B+트리, B*트리)는 선형 시간 복잡도가 아닌 로그 시간 복잡도로 이진 검색을 수행하므로 인덱스를 사용할 때 성능이 더 좋습니다. 다음은 MySQL 클러스터형 인덱스의 개략도입니다. 예를 들어 각 인덱스 분기 노드가 100개의 레코드를 저장할 수 있다고 가정하면 1백만(1003)개의 레코드에는 인덱스를 완성하는 데 3개의 B-트리 계층만 필요합니다. 인덱스를 통해 데이터를 검색할 때 인덱스 데이터를 3번 읽어야 하며(각 디스크 IO는 전체 분기 노드를 읽음), 쿼리 결과를 얻으려면 데이터를 읽어야 하는 1번의 디스크 IO가 필요합니다. 순수한 물건! 여러분과 공유할 15,000 단어 문법 매뉴얼

반대로, 전체 테이블 스캔을 사용하면 수행해야 하는 디스크 IO 수가 몇 배 더 높아질 수 있습니다. 데이터 볼륨이 1억 개(1004)로 증가하면 B-트리 인덱스에는 인덱스 IO가 하나만 더 필요한 반면, 전체 테이블 스캔에는 몇 배 더 많은 IO가 필요합니다.

마찬가지로 SELECT * FROM은 테이블의 모든 필드를 쿼리한다는 뜻이므로 사용을 피해야 합니다. 이 쓰기 방법은 일반적으로 데이터베이스가 더 많은 데이터를 읽게 하고 네트워크도 더 많은 데이터를 전송해야 하므로 성능이 저하됩니다.

규칙 2: 쿼리가 올바른 인덱스를 사용하는지 확인하세요

해당 인덱스가 누락된 경우 쿼리 조건을 지정하더라도 해당 인덱스를 통해 데이터를 찾을 수 없습니다. 따라서 먼저 적절한 인덱스가 생성되었는지 확인해야 합니다. 일반적으로 다음 필드를 색인화해야 합니다.

• WHERE 조건에 자주 나타나는 필드에 대한 색인을 생성하면 전체 테이블 검색을 피할 수 있습니다.
• ORDER BY 정렬 필드를 색인에 추가하면 추가 정렬 작업을 피할 수 있습니다. 다중 테이블 조인 쿼리의 관련 필드에 대한 조인 쿼리 성능이 향상될 수 있습니다.
• GROUP BY 그룹화 작업 필드를 인덱스에 추가하면 인덱스를 사용하여 그룹화를 완료할 수 있습니다.
• 적합한 인덱스가 생성되더라도 SQL 문에 문제가 있으면 데이터베이스는 해당 인덱스를 사용하지 않습니다. 인덱스 오류로 이어지는 일반적인 문제는 다음과 같습니다.

WHERE 절의 인덱스 필드에 표현식 작업을 수행하거나 함수를 사용하면 인덱스가 실패하게 됩니다. 이 상황에는 비교와 같은 필드 데이터 유형의 불일치도 포함됩니다. ;

• LIKE 매칭을 사용할 때 와일드카드 문자가 왼쪽에 나타나면 인덱스를 사용할 수 없습니다. 대용량 텍스트 데이터의 퍼지 매칭을 위해서는 데이터베이스에서 제공하는 전체 텍스트 검색 기능이나 전문적인 전체 텍스트 검색 엔진(Elasticsearch 등)을 고려해야 합니다. WHERE 조건을 NOT NULL로 설정하십시오. 모든 데이터베이스가 아닌 IS [NOT] NULL로 판단할 때 인덱스를 사용할 수 있습니다.
• 실행 계획(쿼리 계획 또는 설명 계획이라고도 함)은 인덱스 또는 전체 테이블 스캔을 통해 테이블의 데이터에 액세스하고 연결 쿼리의 구현 방법 및 순서 등 데이터베이스가 SQL 문을 실행하기 위한 구체적인 단계입니다. 연결 등의 SQL 문의 성능이 이상적이지 않다면 먼저 실행 계획을 확인하고 실행 계획(EXPLAIN)을 통해 쿼리가 올바른 인덱스를 사용하는지 확인해야 합니다.

규칙 3: 하위 쿼리 사용을 피하세요

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은 하위 쿼리를 25번 반복하는 Nested Loop Join 구현 방법을 사용하지만 실제로는 한 번의 스캔으로 각 부서의 평균 월급을 계산하고 캐시할 수 있습니다. 다음 문은 하위 쿼리 확장(Subquery Unnest)을 실현하기 위해 하위 쿼리를 동등한 JOIN 문으로 대체합니다.

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 优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。

위 내용은 당신이 알아야 할 SQL 최적화 최소 규칙의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!