10.2 Optimizing SQL Statements10.2 Optimizing SQL Statements

10.2 Optimizing SQL Statements

The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines in this section help to speed up all kinds of MySQL applications. The guidelines cover SQL operations that read and write data, the behind-the-scenes overhead for SQL operations in general, and operations used in specific scenarios such as database monitoring.

数据库应用程序的核心逻辑是通过SQL语句执行的，无论是通过解释器直接发布，还是通过API在后台提交。本节中的调优指南有助于加速各种MySQL应用程序。这些指南涵盖了读取和写入数据的SQL操作、一般SQL操作的幕后开销以及在特定场景（如数据库监控）中使用的操作。

10.2.1 Optimizing SELECT Statements

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等构造。。。选择时，插入。。。DELETE语句中的SELECT和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群集数据节点，在那里它可以在它们之间分布并并行执行。有关此优化的更多信息，请参见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.

要进行慢速选择。。。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 10.3.1, “How MySQL Uses Indexes” and Section 10.8.1, “Optimizing Queries with EXPLAIN”.

索引对于使用连接和外键等功能引用不同表的查询尤为重要。您可以使用EXPLAIN语句来确定SELECT使用哪些索引。请参阅第10.3.1节“MySQL如何使用索引”和第10.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 10.5.6, “Optimizing InnoDB Queries” and Section 10.6.1, “Optimizing MyISAM Queries”.

了解每个表的存储引擎特有的调优技术、索引技术和配置参数。InnoDB和MyISAM都有一套准则来实现和维持查询的高性能。有关详细信息，请参阅第10.5.6节“优化InnoDB查询”和第10.6.1节“优化MyISAM查询”。
You can optimize single-query transactions for InnoDB tables, using the technique in Section 10.5.3, “Optimizing InnoDB Read-Only Transactions”.

您可以使用第10.5.3节“优化InnoDB只读事务”中的技术来优化InnoDA表的单查询事务。
Avoid transforming the query in ways that make it hard to understand, especially if the optimizer does some of the same transformations automatically.

避免以难以理解的方式转换查询，特别是在优化器自动执行某些相同转换的情况下。
If a performance issue is not easily solved by one of the basic guidelines, investigate the internal details of the specific query by reading the EXPLAIN plan and adjusting your indexes, WHERE clauses, join clauses, and so on. (When you reach a certain level of expertise, reading the EXPLAIN plan might be your first step for every query.)

如果性能问题不容易通过基本准则之一解决，请通过阅读EXPLAIN计划并调整索引、WHERE子句、联接子句等来调查特定查询的内部细节。（当你达到一定的专业水平时，阅读EXPLAIN计划可能是你每次查询的第一步。）
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.

处理锁定问题，因为其他会话同时访问表可能会影响查询速度。