SQL 优化

Optimizing SELECT Statements（优化 SELECT 语句）

本文翻译自 MySQL 官方文档 8.2.1 Optimizing SELECT Statements

1 WHERE Clause Optimization （WHERE 子句优化）
2 Range Optimization
3 Index Merge Optimization
4 Hash Join Optimization
5 Engine Condition Pushdown Optimization
6 Index Condition Pushdown Optimization
7 Nested-Loop Join Algorithms
8 Nested Join Optimization
9 Outer Join Optimization
10 Outer Join Simplification
11 Multi-Range Read Optimization
12 Block Nested-Loop and Batched Key Access Joins
13 Condition Filtering
14 Constant-Folding Optimization
15 IS NULL Optimization
16 ORDER BY Optimization
17 GROUP BY Optimization
18 DISTINCT Optimization
19 LIMIT Query Optimization
20 Function Call Optimization
21 Window Function Optimization
22 Row Constructor Expression Optimization
23 Avoiding Full Table Scans

Queries, in the form of SELECT statements, perform all the lookup operations in the database. Tuning these statements is a top priority, whether to achieve sub-second response times for dynamic web pages, or to chop hours off the time to generate huge overnight reports.

SELECT 语句用于执行数据库中的查找操作，优化这些语句是最重要的，无论是实现动态网页的亚秒级时间响应，还是缩短生成隔夜报告的时间。

Besides SELECT statements, the tuning techniques for queries also apply to constructs such as CREATE TABLE...AS SELECT, INSERT INTO...SELECT, and WHERE clauses in DELETE statements. Those statements have additional performance considerations because they combine write operations with the read-oriented query operations.

除了 SELECT 语句之外，查询调优技术还适用于构造诸如 CREATE TABLE...AS SELECT、INSERT INTO...SELECT 和 DELETE 语句中的 WHERE 条件等语句。这些语句有额外的影响性能的因素，因为它们将写入操作与读取操作结合在了一起。

NDB Cluster supports a join pushdown optimization whereby a qualifying join is sent in its entirety to NDB Cluster data nodes, where it can be distributed among them and executed in parallel. For more information about this optimization, see Conditions for NDB pushdown joins.

NDB Cluster 支持连接下推优化，其中合格的连接被完整发送到 NDB Cluster 数据节点，可以在它们之间分发并并行执行。有关此优化的更多信息，请参阅 NDB 下推联接的条件。

The main considerations for optimizing queries are:

优化查询的主要因素有：

• To make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. Set up indexes on columns used in the WHERE clause, to speed up evaluation, filtering, and the final retrieval of results. To avoid wasted disk space, construct a small set of indexes that speed up many related queries used in your application.

  为了使慢 SELECT ... WHERE 查询更快，首先要检查是否可以添加索引。在 WHERE 子句中使用的列上设置索引，以加快结果的评估、过滤和最终检索。为了避免浪费磁盘空间，请构建少量的索引来加速应用程序中多数的相关查询。

  Indexes are especially important for queries that reference different tables, using features such as joins and foreign keys. You can use the EXPLAIN statement to determine which indexes are used for a SELECT. See Section 8.3.1, “How MySQL Uses Indexes” and Section 8.8.1, “Optimizing Queries with EXPLAIN”.

  索引对于多表间的联接和外键查询尤其重要。您可以使用 EXPLAIN 语句来确定 SELECT 语句使用了哪些索引。请参阅第 8.3.1 节“MySQL 如何使用索引”和第 8.8.1 节“使用 EXPLAIN 优化查询”。

• Isolate and tune any part of the query, such as a function call, that takes excessive time. Depending on how the query is structured, a function could be called once for every row in the result set, or even once for every row in the table, greatly magnifying any inefficiency.

  隔离并调整查询语句的任一部分，例如需要过多时间的函数调用。根据查询的结构，可以是结果集中的每一行都要调用一次函数，也可以是整张表只调用一次函数，从而大大提高效率。

• Minimize the number of full table scans in your queries, particularly for big tables.

  最大限度地减少查询中全表扫描的次数，尤其是对于大表。

• Keep table statistics up to date by using the ANALYZE TABLE statement periodically, so the optimizer has the information needed to construct an efficient execution plan.

  通过定期使用 ANALYZE TABLE 语句使表统计信息保持最新，以便优化器拥有构建高效执行计划所需的信息。

• Learn the tuning techniques, indexing techniques, and configuration parameters that are specific to the storage engine for each table. Both InnoDB and MyISAM have sets of guidelines for enabling and sustaining high performance in queries. For details, see Section 8.5.6, “Optimizing InnoDB Queries” and Section 8.6.1, “Optimizing MyISAM Queries”.

  了解每个表的存储引擎的调优技术、索引技术和参数配置。 InnoDB 和 MyISAM 都有一套用于高性能查询的准则。有关详细信息，请参阅第 8.5.6 节“优化 InnoDB 查询”和第 8.6.1 节“优化 MyISAM 查询”。

• Adjust the size and properties of the memory areas that MySQL uses for caching. With efficient use of the InnoDB buffer pool, MyISAM key cache, and the MySQL query cache, repeated queries run faster because the results are retrieved from memory the second and subsequent times.

  调整 MySQL 用于缓存的内存区域的大小和属性。通过高效使用 InnoDB 缓冲池、MyISAM 键缓存和 MySQL 查询缓存，重复查询的运行速度会更快，因为第二次及后续查询都会从内存中检索结果。

• Even for a query that runs fast using the cache memory areas, you might still optimize further so that they require less cache memory, making your application more scalable. Scalability means that your application can handle more simultaneous users, larger requests, and so on without experiencing a big drop in performance.

  即使对于使用缓存快速运行的查询，仍可以进一步优化，以使它们使用更少的缓存，从而使您的应用程序更具可扩展性。可扩展性意味着您的应用程序可以处理更多的并发用户、更大的请求等，而不会出现性能大幅下降。

• Deal with locking issues, where the speed of your query might be affected by other sessions accessing the tables at the same time.

  处理锁问题，查询速度可能会受到其他会话在同一时间访问表的影响。