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, ... ;