8.2.1. Optimizing SELECT Statements

    查询，在SELECT语句的形式，在数据库中执行所有的查找操作。调整这些语句是重中之重，是否能达到亚秒级的响应时间，动态网页，或砍小时的休息时间来产生巨大的隔夜报告。

8.2.1.1. Speed of SELECT Statements（SELECT语句的速度）

    主要考虑优化查询是：

• 为了让一个缓慢的SELECT ... WHERE查询速度更快，检查的第一件事情是你是否能增加一个索引。WHERE子句中使用的列上建立索引，加快评估，过滤，，并且结果的最终检索。为了避免浪费磁盘空间，构建一个索引小集加快应用程序中使用许多相关的查询。
  
  索引是引用不同的表的查询尤为重要，使用功能如连接键和外键。可以使用EXPLAIN语句来确定哪些索引用于SELECT。详见 Section 8.3.1, “How MySQL Uses Indexes”和Section 8.8.1, “Optimizing Queries with  EXPLAIN”.

• 隔离和调整任何部分的查询，如一个函数调用，这需要过多的时间。根据查询的结构如何，一个函数可以调用一次在结果集中的每一行，甚至一次对表中的每一行，极大地放大任何低效率。
  
  
• 最大限度地减少全表扫描的数量在查询中，特别是对大表。
  
  
• 保持表的统计信息是最新通过定期的ANALYZE TABLE语句，所以优化器所需的信息构建一个高效的执行计划。
  
  
• 了解调优技术，索引技术，和配置参数特定于存储引擎的每个表。InnoDB和MyISAM有两套准则为实现和维持高性能的查询。详见，Section 8.5.5, “Optimizing InnoDB Queries”和 Section 8.6.1, “Optimizing MyISAM Queries”.
  
  
• 特别是，在MySQL 5.6.4和更高，可以优化InnoDB表单一的查询交易，使用技术 详见 Section 14.2.4.2.3, “Optimizations for Read-Only Transactions”。
  
  
• 避免转换查询方式，使人们很难理解，特别是如果优化会自动执行一些相同的转换。
  
  
• 如果性能问题是不容易通过的基本方针1解决，通过阅读Explain计划和调整指标调查特定查询的内部细节，WHERE子句，join子句，等等。（阅读EXPLAIN计划可能是优化每个查询的第一步。）
  
  
• 调整MySQL用来缓存存储区域的大小和性能。有效利用InnoDB缓冲池，MyISAM关键词缓存，和MySQL查询缓存，重复的查询运行得更快因为结果是从存储器中检索到的第2次以后。
  
  
• 即使对于运行速度快使用超高速缓冲存储器的区域的查询，你可能仍然进一步优化使得它们需要较少的高速缓冲存储器，使您的应用程序更具可扩展性。可扩展性意味着你的应用程序可以处理更多的并发用户，较大的请求，等方面而不经历一个大 的性能下降。
  
  
• 解决锁定问题，其中可能受到访问其他会话的查询速度 表在同一时间。

8.2.1.2. How MySQL Optimizes WHERE Clauses

    本节讨论可用于处理WHERE子句进行优化。该示例使用SELECT语句，但是同样的优化适用于DELETE和UPDATE语句的DELETE子句。

    你也许会重写你的查询使运算操作速度更快，而牺牲可读性。因为MySQL自动执行类似的优化，你常常可以避开这项工作，并留下查询更易懂，易维护的形式。一些由MySQL的后续执行的优化：

• 去除不必要的括号：
  

((a AND b) AND c OR (((a AND b) AND (c AND d))))

-> (a AND b AND c) OR (a AND b AND c AND d)

• 常量折叠：

(a

-> b>5 AND b=c AND a=5

• 恒定的条件下去除（因为常量折叠必要的）：
  

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)

-> B=5 OR B=6

• 使用索引常量表达式求值只有一次。
  
  
• COUNT（*）[1357]在一个表上没有一个WHERE从对MyISAM和MEMORY表的表信息直接检索。这也是做任何NOT NULL表达式只有一个表使用时。
  
  
• 早期发现无效的常量表达式。MySQL快速检测一些SELECT语句是不可能的，不返回行。
  
  
• HAVING合并用WHERE，如果你不使用GROUP BY或聚合函数（等等COUNT()[1357], MIN()[1359], ）。
  
  
• 每个表中的一个join，一个简单的WHERE构造得到一个快速的WHERE评估表并且跳过的行尽快。
  
  
• 所有的常量表查询的其他表之前首先读取。常数表任何如下：
• 一个空表或只有一行。
• 表的WHERE子句使用在PRIMARY KEY或UNIQUE上的索引，其中所有指数部分进行比较以常数表达并且被定义为NOT NULL。

以下所有的表被用作常数表：
SELECT * FROM t WHERE primary_key=1;

SELECT * FROM t1,t2

WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

• 用于连接表的最佳连接组合通过尝试所有的可能性。如果所有列在ORDER BY 和 GROUP BY子句中来自同一个表，当Join时该表是首选。
  
  
• 如果有一个ORDER BY子句和一个不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含比join队列中的第一个表，从其他表中的列，临时表被创建。
  
  
• 如果使用SQL_SMALL_RESULT选项，MySQL使用内存中的临时表。
  
  
• 每个表索引进行查询，并且最好使用索引除非优化认为它是更有效地使用表扫描。在同一时间，在扫描是基于最好的索引是否跨越表中的30％以上时，但一个固定的百分比不再确定使用索引或扫描之间的选择。优化器现在更为复杂和基于其他因素，如表大小，行数，和I / O块的大小。
  
  
• 在一些情况下，MySQL能够读取来自索引行甚至不咨询数据文件。如果从索引中使用的所有列是数字，只有索引树用于解析该查询。
  
  
• 在每一行输出前，那些确实不匹配的HAVING子句被跳过。

    查询的一些例子是非常快：

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name

    WHERE key_part1=constant;

SELECT ... FROM tbl_name

    ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name

    ORDER BY key_part1DESC, key_part2DESC, ... LIMIT 10;

    MySQL解析下面的查询只使用索引树，假设索引列是数字：

SELECT key_part1,key_part2FROM tbl_nameWHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name

    WHERE key_part1=val1AND key_part2=val2;

SELECT key_part2FROM tbl_nameGROUP BY key_part1;

    下面的查询使用索引来检索排序顺序的行没有一个单独的排序通过：

SELECT ... FROM tbl_name

    ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name

    ORDER BY key_part1 DESC, key_part2 DESC, ... ;

8.2.1.3. Optimizing LIMIT Queries

    如果从结果集需要的行只有一个指定的号码，在查询中使用LIMIT子句，而不是获取整个结果集并丢弃了额外的数据。MySQL有时优化一个查询有一个LIMIT row_count 子句并且没有HAVING子句：

• 如果你只选择少数行用LIMIT，MySQL某些情况下使用在索引，当通常它会更愿意做一个全表扫描时。
  
  
• 如果你使用LIMIT ROW_COUNT用ORDER BY，MySQL结束，尽快排序，因为它已经找到的第一个ROW_COUNT行的排序结果，而不是排序整个结果。如果排序是通过使用一个索引进行，这是非常快的。如果一个文件排序必须做的，所有行匹配该查询没有LIMIT子句选择，大部分或全部进行排序，在第一个ROW_COUNT被发现之前。在最初的行已经发现，MySQL不排序任何结果集的剩余部分。
  
  
• 当使用DISTINCT结合LIMIT ROW_COUNT，MySQL停止立刻它发现row_count唯一行。
  
  
• 在一些情况下，一个GROUP BY可以通过读取关键为了（或做在键排序），然后计算摘要直到密钥值改变来解决。在这种情况下，LIMIT  ROW_COUNT不计算任何不必要的GROUP BY值。
  
  
• 只要MySQL已经发送的行的所需数量到客户端，它终止查询除非你使用SQL_CALC_FOUND_ROWS。
  
  
• LIMIT 0迅速返回一个空集。这可以是用于检查查询的有效性是有用的。当使用MySQL API之一，它也可以用于获得该结果列的类型。（这一招并不在MySQL监视器（在mysqlprogram）工作），这只是显示空集在这种情况下; 使用SHOW COLUMNS 或 DESCRIBE。
  
  
• 当服务器使用临时表来解决查询，它使用LIMIT ROW_COUNT子句来计算多少空间是必需的。

    在MySQL5.6.2，优化更有效地处理以下形式的查询（和子查询）：

SELECT ... FROM single_table... ORDER BY non_index_column[DESC] LIMIT [M,]N;

    这种类型的查询是在显示从一个较大的结果集只有几行的Web应用普遍。例如：

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

    排序缓冲区有sort_buffer_size的值[574]的大小。如果排序的元素为N行足够小以适应在排序缓冲器（M + N行如果指定M），服务器可以避免在使用合并文件并执行 排序完全在内存通过处理排序缓冲区作为优先级队列：

• 扫描表，在队列中的排序顺序插入每个选定行的选择列表列。如果队列已满，磕碰出的排序顺序的最后一行。
  
  
• 从队列中返回前N行。（如果指定了M，跳过第一个M行并返回下一个N行）。

    此前，该服务器通过使用用于排序合并文件上执行此操作：

• 扫描表，重复步骤经过该表的结束：
• 选择行，直到排序缓冲区填满。
• 写在缓冲器中的前N行（M + N行如果指定M）来合并文件。
  
  
• 排序合并文件并返回前N行。（如果指定了M，跳过第一个M行返回下一个N行）

    表扫描的成本是相同的队列中并合并文件的方法，因此基于其他成本方法的优化选择：

• 队列方法涉及更多的CPU插入行插入到队列中在排序中。
  
  
• 合并文件的方法有I / O成本，写入和读取文件并CPU成本排序

    优化器会考虑这些因素对N和特定值的行大小之间的平衡。

8.2.1.4. How to Avoid Full Table Scans（如何避免全表扫描）

    从EXPLAIN的输出显示在所有类型的列当MySQL使用全表扫描来解决查询时。这通常是在以下条件下发生：

• 该表是那么小这是更快的性能比费心一个键查找表扫描。这是很常见的少于10行和短行长度表。
  
  
• 这是没有使用限制在ON或WHERE子句索引列。
  
  
• 您比较索引列与常量和MySQL已经计算（基于索引树）常数覆盖了表和表扫描会更快太大的一部分。详见 Section 8.2.1.2, “How MySQL Optimizes WHERE Clauses”。
  
  
• 您使用的主键是低基数（许多行匹配键值）通过另一列。在这种情况下，MySQL假设通过使用键它可能会做很多键查找并表扫描会更快。

    对于小表，表扫描常常是适当的并且对性能的影响可以忽略不计。对于大表，试试下面的方法来避免优化器正确选择表扫描：

• 使用ANALYZE TABLE tbl_name更新为扫描表的关键分布。详见 Section 13.7.2.1, “ANALYZE TABLE Syntax”。
  
  
• 使用FORCE INDEX为扫描表告诉MySQL相比使用给定的索引表扫描是非常昂贵的：

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

WHERE t1.col_name=t2.col_name;

详见 Section 13.2.9.3, “Index Hint Syntax”.

• 使用 --max-seeks-for-key=1000[536]选项启动MySQL或使用SET  max_seeks_for_key=1000告诉优化器假设没有按键扫描导致1000键寻求。详见 Section 5.1.4, “Server System Variables”。