8.2.1.1 WHERE Clause Optimization

This section discusses optimizations that can be made for processing WHERE clauses. The examples use SELECT statements, but the same optimizations apply for WHERE clauses in DELETE and UPDATE statements.

本篇讨论关于WHERE从句优化方面的内容，虽然例子是用SELECT 语句，但是这个优化同样适用于DELETE和UPDATE语句的WHERE从句。

You might be tempted to rewrite your queries to make arithmetic operations faster, while sacrificing readability. Because MySQL does similar optimizations automatically, you can often avoid this work, and leave the query in a more understandable and maintainable form. Some of the optimizations performed by MySQL follow:

为了SQL执行更快，你可能会重写算法操作，不惜牺牲SQL的可读性。其实MySQL已经做了类似的操作，所以尽量不要这么做，保持查询易理解和可维护的格式是比较好的选择。下面的一些优化已经被MySQL默认做了：

• Removal of unnecessary parentheses:
• 删掉不必要的括号：

   ((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)

• Constant folding:
• 常量折叠：

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

• Constant condition removal:
• 删除不必要的常量条件

   (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-> b=5 OR b=6

In MySQL 8.0.14 and later, this takes place during preparation rather than during the optimization phase, which helps in simplification of joins. See Section 8.2.1.9, “Outer Join Optimization”, for further information and examples.

在MySQL8.0.14之后，这个发生在准备阶段而不是优化阶段，这样可以帮忙简化join操作。详情请见：Section 8.2.1.9, “Outer Join Optimization”

• Constant expressions used by indexes are evaluated only once.

• 索引使用的常量表达式只计算一次

• Beginning with MySQL 8.0.16, comparisons of columns of numeric types with constant values are checked and folded or removed for invalid or out-of-rage values:

• 从MySQL8.0.16开始，数据类型与常量值的比较，会被检测和折叠、或者删掉范围外的数据

# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
  SELECT * FROM t WHERE c ≪ 256;
-≫ SELECT * FROM t WHERE 1;

See Section 8.2.1.14, “Constant-Folding Optimization”, for more information.

更多信息查看Section 8.2.1.14, “Constant-Folding Optimization

• COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.

• 对于MyISAM和MEMORY表，没有WHERE语句的COUNT(*)是直接从表信息里面查询的。同样在一个表上也适用于NOT NULL的表达式

• Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.

• 早期检测无效的常量表达式。MySQL快速侦测哪些SELECT语句是无意义的或不返回数据的。

• HAVING is merged with WHERE if you do not use GROUP BY or aggregate functions (COUNT(), MIN(), and so on).

• HAVING被合并到WHERE语句中如果没有使用GROUP BY或统计函数（COUNT(）,MIN()等等）

• For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.

• 关于每个表的关联操作，需要尽可能的构建简单的WHERE从句过滤掉更多的数据行

• All constant tables are read first before any other tables in the query. A constant table is any of the following:

• 所有的常量表都比其它类型的表被首先读取。一个常量表符合下面条件：

1.   An empty table or a table with one row.

2. 空表或者只有一行数据的表

3. A table that is used with a WHERE clause on a PRIMARY KEY or a UNIQUE index, where all index parts are compared to constant expressions and are defined as NOT NULL.

4. 一个表查询的WHERE 的从句条件是PRIMARY KEY 或UNIQUE 索引，这样所有的索引部分被定义为NOT NULL的，且是和常量表达式做比较

All of the following tables are used as constant tables:

下面所有的表被作为常量表使用：

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

• The best join combination for joining the tables is found by trying all possibilities. If all columns in ORDER BY and GROUP BY clauses come from the same table, that table is preferred first when joining.

• 通过查找所有的可能性找到最好的表链接方式。如果关联时所有的列在ORDER BY 和GROUP BY 的条件列都来自同一个表，则关联时首先选择这个表。

• If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

• 如果ORDER BY 语句和GROUP BY语句的列不同，或者ORDER BY 或GROUP BY包含的列不仅仅是第一个表，则会创建一个临时表

• If you use the SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table.

• 如果使用SQL_SMALL_RESULT 调节器，MySQL则使用内存中的临时表

• Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

• 每个表查询最好都使用索引查询除非优化器认为表扫描是更有效的。曾经，扫描取决于索引是否扫描超过表数据的的30%。但是一个固定的百分比不再决定是否是有索引或进行表扫描。优化器是更复杂的和基于预测附加因素比如表大小，数据行数，和I/O数据块大小。

• In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.

• 在一些情况下，MySQL可以直接从索引中读取数据而不是从数据文件中查询。如果所有用于索引的列是数字类型，仅仅索引树被用于查询。

• Before each row is output, those that do not match the HAVING clause are skipped.

• 在每行数据输出前，那些不符合HAVING从句的被忽略

Some examples of queries that are very fast:

下面查询列子都是非常快的：

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_part1 DESC, key_part2 DESC, ... LIMIT 10;

MySQL resolves the following queries using only the index tree, assuming that the indexed columns are numeric:

MySQL通常仅仅使用索引树解决下面查询，假定索引列都是数字类型：

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT MAX(key_part2) FROM tbl_name GROUP BY key_part1;

The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass:

下面的查询按照索引排序进行，而不需要单独的排序：

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;