Règles minimalistes d'optimisation SQL que vous ne devez pas connaître

Tutoriel SQL Langage standard des bases de données relationnelles, il fait partie des compétences essentielles des informaticiens. SQL lui-même n'est pas difficile à apprendre, et l'écriture d'instructions de requête est également très simple, mais il est difficile d'écrire des instructions de requête qui peuvent s'exécuter efficacement.

Recommandé (gratuit) : Tutoriel SQL

L'optimisation des requêtes est un projet complexe qui implique du matériel à la configuration des paramètres, aux analyseurs pour différentes bases de données, à la mise en œuvre de l'optimiseur, à l'ordre d'exécution des instructions SQL, aux index, à la collecte d'informations statistiques, etc., et même à l'architecture globale de l'application et du système. Cet article présente plusieurs règles clés qui peuvent nous aider à écrire des requêtes SQL efficaces, en particulier pour les débutants, ces règles peuvent au moins nous empêcher d'écrire des instructions de requête avec des performances médiocres.

Les règles suivantes s'appliquent à diverses bases de données relationnelles, notamment : MySQL, Oracle, SQL Server, PostgreSQL, SQLite, etc. Si vous trouvez l'article utile, veuillez commenter, aimer et transmettre-le à votre cercle d'amis pour obtenir de l'aide.

Règle 1 : Renvoyez uniquement les résultats requis

Assurez-vous de spécifier la condition WHERE pour l'instruction de requête afin de filtrer les lignes de données inutiles. De manière générale, les systèmes OLTP n'ont besoin de renvoyer que quelques enregistrements d'une grande quantité de données à la fois ; la spécification des conditions de requête peut nous aider à renvoyer les résultats via des index au lieu d'analyses de tables complètes. Dans la grande majorité des cas, les performances sont meilleures lors de l'utilisation d'index, car les index (B-trees, B+trees, B*trees) effectuent des recherches binaires avec une complexité temporelle logarithmique, plutôt qu'une complexité temporelle linéaire. Ce qui suit est un diagramme schématique de l'index clusterisé MySQL : Par exemple, en supposant que chaque nœud de branche d'index peut stocker 100 enregistrements, 1 million (1003) enregistrements ne nécessitent que 3 couches d'arbres B pour compléter l'index. Lors de la recherche de données via l'index, vous devez lire les données d'index 3 fois (chaque IO de disque lit l'intégralité du nœud de branche), plus 1 IO de disque pour lire les données afin d'obtenir les résultats de la requête. Du pur truc ! Manuel de grammaire de 15 000 mots partagé avec vous

Au contraire, si une analyse de table complète est utilisée, le nombre d'E/S disque à effectuer peut être plusieurs ordres de grandeur plus élevé. Lorsque le volume de données augmente jusqu'à 100 millions (1 004), l'index B-tree ne nécessite qu'une seule E/S d'index supplémentaire, tandis qu'une analyse complète de la table nécessite plusieurs ordres de grandeur supplémentaires.

De même, nous devrions éviter d'utiliser SELECT * FROM car il représente tous les champs de la table de requête. Cette méthode d'écriture amène généralement la base de données à lire plus de données et le réseau doit également transmettre plus de données, ce qui entraîne une diminution des performances.

Règle 2 : Assurez-vous que la requête utilise le bon index

Si un index approprié est manquant, les données ne seront pas trouvées via l'index même si les conditions de requête sont spécifié. Par conséquent, nous devons d’abord nous assurer que les index appropriés sont créés. De manière générale, les champs suivants doivent être indexés :

• Créer des index sur des champs qui apparaissent souvent dans les conditions WHERE peut éviter des analyses de table complètes
• Ajouter des champs triés ORDER BY à l'index ; , vous pouvez éviter des opérations de tri supplémentaires ;
• L'indexation des champs associés aux requêtes de jointure multi-tables peut améliorer les performances des requêtes de jointure
• L'ajout du champ d'opération de regroupement GROUP BY à l'index peut être utilisé ; index pour compléter le regroupement.

Même si un index approprié est créé, s'il y a un problème avec l'instruction SQL, la base de données n'utilisera pas l'index. Les problèmes courants qui provoquent un échec de l'index incluent :

• L'exécution d'opérations d'expression ou l'utilisation de fonctions sur les champs d'index dans la clause WHERE entraînera un échec de l'index. Cette situation inclut également une incompatibilité de type de données de champ, comme les chaînes et les entiers. comparé ;
• Lors de l'utilisation de la correspondance LIKE, si le caractère générique apparaît sur le côté gauche, l'index ne peut pas être utilisé. Pour une correspondance floue de données textuelles volumineuses, vous devriez considérer la fonction de recherche en texte intégral fournie par la base de données, ou même un moteur de recherche en texte intégral spécialisé (Elasticsearch, etc.) ;
• Si un index est créé sur ; le champ dans la condition WHERE, essayez de le définir sur NOT NULL ; Toutes les bases de données ne peuvent pas utiliser d'index lors de l'utilisation du jugement IS [NOT] NULL.

Le plan d'exécution (également appelé plan de requête ou plan d'explication) correspond aux étapes spécifiques permettant à la base de données d'exécuter des instructions SQL, telles que l'accès aux données de la table via une analyse d'index ou de table complète, et la mise en œuvre de requête de connexion et ordre des connexions, etc. Si les performances d'une instruction SQL ne sont pas idéales, nous devons d'abord vérifier son plan d'exécution et nous assurer que la requête utilise le bon index via le plan d'exécution (EXPLAIN).

Règle 3 : essayez d'éviter d'utiliser des sous-requêtes

En prenant MySQL comme exemple, la requête suivante renvoie les informations sur les employés dont le salaire mensuel est supérieur au salaire mensuel moyen du département :

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)

Comme le montre le plan d'exécution, MySQL utilise une implémentation similaire de Nested Loop Join ; la sous-requête boucle 25 fois, mais le salaire mensuel moyen de chaque département peut en fait être calculé et mis en cache en une seule analyse. . L'instruction suivante remplace la sous-requête par une instruction JOIN équivalente pour réaliser l'expansion de la sous-requête (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 优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!