知っておくべき SQL 最適化の最小限のルール-SQL-php.cn

SQL チュートリアル リレーショナルデータベースの標準言語である SQL は、IT 実務者にとって必須のスキルの 1 つです。 SQL 自体は学習するのが難しくなく、クエリステートメントを記述することも非常に簡単ですが、効率的に実行できるクエリステートメントを記述するのは困難です。

知っておくべき SQL 最適化の最小限のルール

推奨 (無料): SQL チュートリアル

クエリの最適化は、ハードウェアからの情報を含む複雑なプロジェクトです。パラメータ設定、さまざまなデータベースのパーサー、オプティマイザの実装、SQL ステートメントの実行順序、インデックス、統計情報の収集など、さらにはアプリケーションとシステムの全体的なアーキテクチャにまで及びます。この記事では、効率的な SQL クエリを作成するのに役立ついくつかの重要なルールを紹介します。特に初心者にとって、これらのルールは、少なくともパフォーマンスの悪いクエリステートメントを作成することを防ぐことができます。

次のルールは、MySQL、Oracle、SQL Server、PostgreSQL、SQLite などを含む (ただしこれらに限定されない) さまざまなリレーショナルデータベースに適用されます。この記事が役立つと思われた場合は、コメントしたり、「いいね！」をしたり、サポートを求めて友人の輪に転送してください。

ルール 1: 必要な結果のみを返す

クエリステートメントに WHERE 条件を指定して、不要なデータ行を除外するようにしてください。一般に、OLTP システムは一度に大量のデータから少数のレコードを返すだけで済みます。クエリ条件を指定すると、テーブル全体のスキャンではなくインデックスを通じて結果を返すことができます。インデックス (B ツリー、B ツリー、B* ツリー) は線形時間計算量ではなく対数時間計算量でバイナリ検索を実行するため、ほとんどの場合、インデックスを使用するとパフォーマンスが向上します。以下は、MySQL クラスタードインデックスの概略図です。たとえば、各インデックスブランチノードが 100 レコードを保存できると仮定すると、100 万 (1003) レコードのインデックスを完成するには 3 層の B ツリーのみが必要です。インデックスを通じてデータを検索する場合、インデックスデータを 3 回読み取る必要があります (各ディスク IO はブランチノード全体を読み取ります)。さらに、クエリ結果を取得するためにデータを読み取るための 1 回のディスク IO が必要です。純粋なもの！ 15,000 語の文法マニュアルを共有します

逆に、フルテーブルスキャンを使用すると、実行する必要があるディスク IO の数が数桁多くなる可能性があります。データ量が 1 億 (1004) に増加すると、B ツリーインデックスに必要なインデックス IO は 1 つ増えるだけですが、テーブル全体のスキャンには数桁多くの 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 回ループしますが、各部門の平均月給は実際に 1 回の計算で計算してキャッシュできます。スキャン。次のステートメントは、サブクエリを同等の 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 优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。

以上が知っておくべき SQL 最適化の最小限のルールの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。