MySQL 8.4 Release Notes
- 10.2.1.1 WHERE 子句优化10.2.1.1 WHERE Clause Optimization
- 10.2.1.2 范围优化10.2.1.2 Range Optimization
- 10.2.1.3 索引合并优化10.2.1.3 Index Merge Optimization
- 10.2.1.4 哈希连接优化10.2.1.4 Hash Join Optimization
- 10.2.1.5 引擎条件下推优化10.2.1.5 Engine Condition Pushdown Optimization
- 10.2.1.6 索引条件下推优化10.2.1.6 Index Condition Pushdown Optimization
- 10.2.1.7 嵌套循环连接算法10.2.1.7 Nested-Loop Join Algorithms
- 10.2.1.8 嵌套连接优化10.2.1.8 Nested Join Optimization
- 10.2.1.9 外连接优化10.2.1.9 Outer Join Optimization
- 10.2.1.10 外连接简化10.2.1.10 Outer Join Simplification
- 10.2.1.11 多范围读取优化10.2.1.11 Multi-Range Read Optimization
- 10.2.1.12 块嵌套循环和批量键访问连接10.2.1.12 Block Nested-Loop and Batched Key Access Joins
- 10.2.1.13 条件过滤10.2.1.13 Condition Filtering
- 10.2.1.14 常量折叠优化10.2.1.14 Constant-Folding Optimization
- 10.2.1.15 IS NULL 优化10.2.1.15 IS NULL Optimization
- 10.2.1.16 ORDER BY 优化10.2.1.16 ORDER BY Optimization
- 10.2.1.17 GROUP BY 优化10.2.1.17 GROUP BY Optimization
- 10.2.1.18 DISTINCT 优化10.2.1.18 DISTINCT Optimization
- 10.2.1.19 LIMIT 查询优化10.2.1.19 LIMIT Query Optimization
- 10.2.1.20 函数调用优化10.2.1.20 Function Call Optimization
- 10.2.1.21 窗口函数优化10.2.1.21 Window Function Optimization
- 10.2.1.22 行构造表达式优化10.2.1.22 Row Constructor Expression Optimization
- 10.2.1.23 避免全表扫描10.2.1.23 Avoiding Full Table Scans
查询，以SELECT语句的形式，执行数据库中的所有查找操作。优化这些语句是首要任务，无论是为了实现动态网页的亚秒级响应时间，还是为了将生成大量夜间报表的时间缩短数小时。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 语句之外，查询的调优技术也适用于诸如 CREATE
TABLE...AS SELECT、INSERT
INTO...SELECT 以及 DELETE 语句中的 WHERE 子句。这些语句有额外的性能考虑，因为它们将写操作与面向读取的查询操作结合在一起。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.
NDB Cluster 支持一种连接下推优化，即将符合条件的连接整体发送到 NDB Cluster 数据节点，在这些节点上可以分发并并行执行。有关此优化的更多信息，请参见 NDB 下推连接的条件。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.
优化查询的主要考虑因素是：The main considerations for optimizing queries are:
要使一个慢的
SELECT ... WHERE查询更快，首先要检查是否可以添加 索引。在WHERE子句中使用的列上设置索引，以加快评估、筛选和最终结果的检索。为了避免浪费磁盘空间，构建一小组索引，以加快应用程序中使用的多个相关查询。To make a slowSELECT ... WHEREquery faster, the first thing to check is whether you can add an index. Set up indexes on columns used in theWHEREclause, 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.索引对于引用不同表的查询尤其重要，使用了例如连接和外键等功能。您可以使用
EXPLAIN语句来确定某个SELECT使用了哪些索引。请参见第 10.3.1 节，“MySQL 如何使用索引”和第 10.8.1 节，“使用 EXPLAIN 优化查询”。Indexes are especially important for queries that reference different tables, using features such as joins and foreign keys. You can use theEXPLAINstatement to determine which indexes are used for aSELECT. See Section 10.3.1, “How MySQL Uses Indexes” and Section 10.8.1, “Optimizing Queries with 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.
通过定期使用
ANALYZE TABLE语句保持表统计信息的最新状态，这样优化器就有必要的信息来构建高效的执行计划。Keep table statistics up to date by using theANALYZE TABLEstatement periodically, so the optimizer has the information needed to construct an efficient execution plan.学习针对每个表的存储引擎的调优技术、索引技术和配置参数。
InnoDB和MyISAM都有一套用于启用和维持查询高性能的指南。详情请参见 第 10.5.6 节，“优化 InnoDB 查询” 和 第 10.6.1 节，“优化 MyISAM 查询”。Learn the tuning techniques, indexing techniques, and configuration parameters that are specific to the storage engine for each table. BothInnoDBandMyISAMhave sets of guidelines for enabling and sustaining high performance in queries. For details, see Section 10.5.6, “Optimizing InnoDB Queries” and Section 10.6.1, “Optimizing MyISAM Queries”.您可以使用 第 10.5.3 节，“优化 InnoDB 只读事务” 中的技术来优化
InnoDB表的单查询事务。You can optimize single-query transactions forInnoDBtables, using the technique in Section 10.5.3, “Optimizing InnoDB Read-Only Transactions”.避免以使查询难以理解的方式进行转换，尤其是当优化器也会自动执行某些相同的转换时。Avoid transforming the query in ways that make it hard to understand, especially if the optimizer does some of the same transformations automatically.
如果性能问题不能通过基本指南轻松解决，请通过阅读
EXPLAIN执行计划来调查特定查询的内部细节，并调整索引、WHERE子句、连接子句等。（当你达到一定的专业水平时，阅读EXPLAIN执行计划可能会成为你每个查询的第一步。）If a performance issue is not easily solved by one of the basic guidelines, investigate the internal details of the specific query by reading theEXPLAINplan and adjusting your indexes,WHEREclauses, join clauses, and so on. (When you reach a certain level of expertise, reading theEXPLAINplan might be your first step for every query.)调整 MySQL 用于缓存的内存区域的大小和属性。通过高效使用
InnoDB缓冲池、MyISAM键缓存以及 MySQL 查询缓存，相同查询在第二次及之后运行时会更快，因为结果是从内存中获取的。Adjust the size and properties of the memory areas that MySQL uses for caching. With efficient use of theInnoDBbuffer pool,MyISAMkey cache, and the MySQL query cache, repeated queries run faster because the results are retrieved from memory the second and subsequent times.即使对于使用缓存内存区域运行得很快的查询，你仍然可以进一步优化，使它们需要更少的缓存内存，从而使你的应用程序更具可扩展性。可扩展性意味着你的应用程序能够处理更多的同时用户、更大的请求等，而不会出现性能大幅下降的情况。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.