Minimalistische Regeln für die SQL-Optimierung, die Sie nicht kennen dürfen

SQL-Tutorial Als Standardsprache relationaler Datenbanken ist es eine der wesentlichen Fähigkeiten für IT-Praktiker. SQL selbst ist nicht schwer zu erlernen und das Schreiben von Abfrageanweisungen ist ebenfalls sehr einfach, es ist jedoch schwierig, Abfrageanweisungen zu schreiben, die effizient ausgeführt werden können.

Empfohlen (kostenlos): SQL-Tutorial

Abfrageoptimierung ist ein komplexes Projekt, das alles von der Hardware bis zur Parameterkonfiguration, Parsern für verschiedene Datenbanken, Optimierungsimplementierung, Ausführungsreihenfolge von SQL-Anweisungen und Indizes umfasst sowie die Sammlung statistischer Informationen usw. und sogar die Gesamtarchitektur von Anwendungen und Systemen. In diesem Artikel werden einige wichtige Regeln vorgestellt, die uns beim Schreiben effizienter SQL-Abfragen helfen können. Diese Regeln können uns zumindest davon abhalten, Abfrageanweisungen mit schlechter Leistung zu schreiben.

Die folgenden Regeln gelten für verschiedene relationale Datenbanken, einschließlich, aber nicht beschränkt auf: MySQL, Oracle, SQL Server, PostgreSQL, SQLite usw. Wenn Sie den Artikel nützlich finden, kommentieren Sie ihn bitte, liken Sie ihn und leiten Sie ihn zur Unterstützung an Ihren Freundeskreis weiter.

Regel 1: Nur die erforderlichen Ergebnisse zurückgeben

Stellen Sie sicher, dass Sie die WHERE-Bedingung für die Abfrageanweisung angeben, um unnötige Datenzeilen herauszufiltern. Im Allgemeinen müssen OLTP-Systeme jeweils nur wenige Datensätze aus einer großen Datenmenge zurückgeben. Die Angabe von Abfragebedingungen kann uns dabei helfen, Ergebnisse über Indizes anstelle von vollständigen Tabellenscans zurückzugeben. In den allermeisten Fällen ist die Leistung bei der Verwendung von Indizes besser, da Indizes (B-Bäume, B+Bäume, B*Bäume) binäre Suchen mit logarithmischer Zeitkomplexität statt linearer Zeitkomplexität durchführen. Das Folgende ist ein schematisches Diagramm des MySQL-Cluster-Index: Unter der Annahme, dass jeder Indexzweigknoten 100 Datensätze speichern kann, sind für 1 Million (1003) Datensätze nur 3 Schichten B-Bäume erforderlich, um den Index zu vervollständigen. Wenn Sie über den Index nach Daten suchen, müssen Sie die Indexdaten dreimal lesen (Festplatten-E/A liest jedes Mal den gesamten Zweigknoten) und zusätzlich 1 Festplatten-E/A, um die Daten zu lesen und die Abfrageergebnisse zu erhalten. Reines Zeug! 15.000 Wörter umfassendes Grammatikhandbuch zum Teilen mit Ihnen

Wenn dagegen ein vollständiger Tabellenscan verwendet wird, kann die Anzahl der durchzuführenden Festplatten-IOs um mehrere Größenordnungen höher sein. Wenn das Datenvolumen auf 100 Millionen (1004) ansteigt, erfordert der B-Tree-Index nur einen weiteren Index-IO, während ein vollständiger Tabellenscan mehrere Größenordnungen mehr IO erfordert.

Ebenso sollten wir die Verwendung von SELECT * FROM vermeiden, da dies bedeutet, dass alle Felder in der Tabelle abgefragt werden. Diese Schreibmethode führt normalerweise dazu, dass die Datenbank mehr Daten liest und das Netzwerk auch mehr Daten übertragen muss, was zu einer Leistungseinbuße führt.

Regel 2: Stellen Sie sicher, dass die Abfrage den richtigen Index verwendet.

Wenn der entsprechende Index fehlt, werden die Daten nicht über den Index gefunden, selbst wenn die Abfragebedingungen angegeben sind. Daher müssen wir zunächst sicherstellen, dass der entsprechende Index erstellt wird. Im Allgemeinen müssen die folgenden Felder indiziert werden:

• Durch das Erstellen von Indizes für Felder, die häufig in WHERE-Bedingungen vorkommen, können vollständige Tabellenscans vermieden werden.
• Durch das Hinzufügen von ORDER BY-sortierten Feldern zum Index können zusätzliche Sortiervorgänge vermieden werden Zugehörige Felder von Mehrtabellen-Join-Abfragen können die Leistung von Join-Abfragen verbessern.
• Fügen Sie das Gruppierungsoperationsfeld GROUP BY zum Index hinzu, und Sie können den Index verwenden, um die Gruppierung abzuschließen.
• Auch wenn ein geeigneter Index erstellt wird und ein Problem mit der SQL-Anweisung auftritt, verwendet die Datenbank den Index nicht. Zu den häufigsten Problemen, die zu Indexfehlern führen, gehören:

Das Ausführen von Ausdrucksoperationen oder die Verwendung von Funktionen für das Indexfeld in der WHERE-Klausel führt dazu, dass der Index fehlschlägt. Zu dieser Situation gehört auch eine Nichtübereinstimmung im Datentyp des Felds, z. B. beim Vergleich Zeichenfolgen und Ganzzahlen. ;

• Wenn beim LIKE-Abgleich das Platzhalterzeichen auf der linken Seite erscheint, kann der Index nicht verwendet werden. Für den Fuzzy-Abgleich großer Textdaten sollten Sie die von der Datenbank bereitgestellte Volltextsuchfunktion oder sogar eine spezielle Volltextsuchmaschine (Elasticsearch usw.) in Betracht ziehen;
• Wenn ein Index für das Feld erstellt wird WHERE-Bedingung, versuchen Sie, sie auf NOT NULL zu setzen; nicht alle Datenbanken. Der Index kann verwendet werden, wenn er nach IS [NOT] NULL beurteilt wird.
• Ausführungsplan (auch Abfrageplan oder Erklärungsplan genannt) sind die spezifischen Schritte, mit denen die Datenbank SQL-Anweisungen ausführt, z. B. den Zugriff auf die Daten in der Tabelle über einen Index oder einen vollständigen Tabellenscan, die Implementierungsmethode der Verbindungsabfrage und die Reihenfolge der Verbindung usw. Wenn die Leistung einer SQL-Anweisung nicht optimal ist, sollten wir zunächst ihren Ausführungsplan überprüfen und sicherstellen, dass die Abfrage über den Ausführungsplan (EXPLAIN) den richtigen Index verwendet.

Regel 3: Vermeiden Sie die Verwendung von Unterabfragen

Nehmen Sie MySQL als Beispiel: Die folgende Abfrage gibt Mitarbeiterinformationen zurück, deren Monatsgehalt höher ist als das durchschnittliche Monatsgehalt der Abteilung:

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)

Wie aus der Ausführung hervorgeht Plan verwendet MySQL so etwas wie die Nested-Loop-Join-Implementierungsmethode; die Unterabfrage durchläuft 25 Mal, aber das durchschnittliche Monatsgehalt jeder Abteilung kann tatsächlich in einem Scan berechnet und zwischengespeichert werden. Die folgende Anweisung ersetzt die Unterabfrage durch eine entsprechende JOIN-Anweisung, um die Erweiterung der Unterabfrage zu realisieren (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 优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。

Das obige ist der detaillierte Inhalt vonMinimalistische Regeln für die SQL-Optimierung, die Sie nicht kennen dürfen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!