MySQL查詢最佳化之一-WHERE語句最佳化，mysql-where

MySQL查詢最佳化之一-WHERE語句最佳化，mysql-where
MySQL查詢最佳化之一-WHERE語句最佳化

如需轉載請標明出處：http://blog.csdn.net/itas109
QQ技術交流群：12951803

環境：
MySQL版本：5.5.15
作業系統：windows

本文討論WHERE語句的最佳化。 這些樣本使用SELECT語句，但是相同的最佳化適用於DELETE和UPDATE語句中的WHERE語句。

查詢速度比較快的一些樣本：

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僅使用二級索引解決以下查詢：

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 key_part2 FROM tbl_name GROUP 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, ... ;

您可能會試圖重寫您的查詢來加快算術運算，同時犧牲可讀性。 因為MySQL會自動進行類似的最佳化，所以通常可以避免這種工作，並將查詢留在更易於理解和維護的形式。 MySQL執行的一些最佳化如下：

1.刪除不必要的括弧

Removal of unnecessary parentheses

減少文法分許的or和and樹層，減少cpu消耗

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

2.常量傳遞

Constant folding

盡量不使用變數

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

3.消除無用的SQL條件

Constant condition removal (needed because of constant folding)

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

4.被索引使用的常量運算式只被計算一次

Constant expressions used by indexes are evaluated only once

5.COUNT(*)最佳化

COUNT（*）在沒有WHERE的單個表上時直接從MyISAM和MEMORY表的表資訊中檢索。 當僅與一個表使用時，這也適用於任何NOT NULL運算式。
對於諸如InnoDB之類的事務性儲存引擎，儲存確切的行數是有問題的，因為可能正在發生多個事務，每個事務都可能影響計數。

6.儘早檢測無效的常量運算式。

MySQL快速檢測到一些SELECT語句是不可能的，並且不返回任何行。

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

7.盡量將WHERE、HAVING合并

如果不使用GROUP BY或集合函數（COUNT()，MIN()等），盡量將HAVING與WHERE合并。

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

select * from t1 (select*from tab where id>10) as t2 where t1.age>10 and t2.age<25;-> select*from t1,tab as t2 where t1.age>10 and t2.age<25 and t2.id>10;

具體步驟：

1) from與from合并，修改相應參數
2) where與where合并，用and串連
3) 修改相應的謂詞（in改=）

8.對於join中的每個表，構造一個更簡單的WHERE，以獲得表的快速WHERE評估，並儘快跳過行。

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.

9.在查詢中的任何其他表之前首先讀取所有常量表。

常數表是以下任一項：
1） 一張空表或者僅有一行資料的表
2）與主鍵或唯一索引中的WHERE子句一起使用的表，其中所有索引部分都與常量運算式進行比較，並被定義為NOT NULL。
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.

以下所有表格均用作常量表格：

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

10.最佳化串連組合

儘可能找到join表的最佳串連組合。 如果ORDER BY和GROUP BY子句中的所有列都來自同一個表，則在加入時首先選擇該表。

11.ORDER BY、GROUP BY與暫存資料表

如果存在ORDER BY子句和不同的GROUP BY子句，或者ORDER BY或GROUP BY包含來自join隊列中第一個表以外的表的列，則會建立一個暫存資料表。

12.SQL_SMALL_RESULT修飾符

如果使用SQL_SMALL_RESULT修飾符，則MySQL使用記憶體中的暫存資料表

13.使用最佳索引

查詢每個表的索引，並使用最佳索引，除非最佳化器認為使用表掃描更高效。 有一次，根據最佳索引是否超過了表格的30％來使用掃描，但是固定百分比不再決定使用索引或掃描之間的選擇。 最佳化器現在更加複雜，並且根據附加因素（如表大小，行數和I/O塊大小）進行估計。

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.

14.索引數字列

在某些情況下，MySQL甚至可以從索引中讀取行，而無需詢問資料檔案。 如果索引中使用的所有列都是數字，則僅使用索引樹來解析查詢。

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.

15.在輸出每行之前，跳過與HAVING子句不匹配的行16.最佳化選擇條件的排列順序

把能過濾更多資料的條件放在前面,過濾少的條件放後面

MySQL左到右的順序處理條件,把能過濾更多資料的條件放在前面,過濾少的條件放後面

 select * from employeewhere salary >1000     --條件1,過濾的資料較少and   dept_id='01'    --條件2,過濾的資料比條件1多

上面的SQL就不符合我們的原則了,應該把過濾資料更多的條件放在前面,因此改為下面這樣更好

select * from employeewhere   dept_id='01'     --過濾更多資料的條件放在前面and   salary > 1000

Reference：
https://dev.mysql.com/doc/refman/5.5/en/where-optimization.html

覺得文章對你有協助，可以用掃描二維碼捐贈給博主，謝謝！

如需轉載請標明出處：http://blog.csdn.net/itas109
QQ技術交流群：12951803

查看評論