對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2012/11/13 8:27:58
[ MySQL ] When the subselect runs faster
When the subselect runs faster
http://www.mysqlperformanceblog.com/2010/03/18/when-the-subselect-runs-faster/
幾週之前，從我們其中一個客戶那邊接到一個協助查詢最佳化的請求。
這個查詢語法很簡單就像這樣：
這個欄位在資料表的情形像這樣：
該資料表包含 549252 筆列資料，而當然的他有一個索引是設定在 col1 欄位。
MySQL 估計該索引的基數( cardinality )為 87，雖然在這個例子當中我們知道這是不正確的索引基數，
因為他不可能超過 9，這個欄位只有 8 ( 再加上 NULL ) 個不同的內容。
這個查詢花得時間超過 5 分鐘( 由於列資料太多並且資料表不適用於快取情形 )
當妳嘗試執行的時候，MySQL 首先會透過索引試著找出每一列 col1 為 A 或 B 情形。
接著它將會使用檔案排序的方式按照 ID 排列後回傳前 20 筆資料而其餘的忽略。
在這個例子 MySQL 需要 2 個索引，一個用來找尋列資料，另外一個回傳找到的資料的正確排序方式。
MySQL 只會選擇其中一個來執行查詢 - 找到列資料的索引。
當沒有使用 LIMIT 的時候這是一個正確的決定，就算只有一個索引的時後也是如此。
因為這通常可以根據 WHERE 的查詢子句，比較快速回傳所需列數的資料。
特別是在這個例子當中，WHERE 查詢並不是非常具有選擇性。
所以我嘗試這樣：
在這個例子我們強迫 MySQL 取得已經排列過順序的資料，並且從子查詢當中檢查它是否符合我們原本的 WHERE 查詢。
如果妳透過 EXPLAIN 查看你會發現結果非常嚇人，但是事實上相依的子查詢執行只會在結果當中產生 20 筆列資料。
這樣的結果反應了一個好很多回應時間：
透過使用子查詢改寫這個查詢我們確實提昇了一百倍的效能。看來子查詢並不全都是會降低效能的。
即便我們證明了使用子查詢並不總是降低效能，但這個方法並不是最好的方法。
我們並不需要使用單獨的子查詢，去確保 MySQL 在檢查資料表是否符合 WHERE 查詢的時候使用特定的索引。
我們可以使用 FORCE INDEX 提示去複寫 MySQL 所採用的索引。
當 WHERE 查詢非常不具備選擇性的時候這個方法非常管用，
除此之外 MySQL 可能需要掃描非常大量的列資料才能找到相符的資料。妳可以使用另外一個幾年前由 Peter 所撰寫的技巧。
原文出處：[ MySQL ] When the subselect runs faster - 黑書記事 - 無名小站
When the subselect runs faster
http://www.mysqlperformanceblog.com/2010/03/18/when-the-subselect-runs-faster/
幾週之前，從我們其中一個客戶那邊接到一個協助查詢最佳化的請求。
這個查詢語法很簡單就像這樣：
SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0
這個欄位在資料表的情形像這樣：
`col1` enum('A','B','C','CD','DE','F','G','HI') default NULL該資料表包含 549252 筆列資料，而當然的他有一個索引是設定在 col1 欄位。
MySQL 估計該索引的基數( cardinality )為 87，雖然在這個例子當中我們知道這是不正確的索引基數，
因為他不可能超過 9，這個欄位只有 8 ( 再加上 NULL ) 個不同的內容。
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | table | range | col1 | col1 | 2 | NULL | 549252 | Using where; Using filesort
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
這個查詢花得時間超過 5 分鐘( 由於列資料太多並且資料表不適用於快取情形 )
當妳嘗試執行的時候，MySQL 首先會透過索引試著找出每一列 col1 為 A 或 B 情形。
接著它將會使用檔案排序的方式按照 ID 排列後回傳前 20 筆資料而其餘的忽略。
在這個例子 MySQL 需要 2 個索引，一個用來找尋列資料，另外一個回傳找到的資料的正確排序方式。
MySQL 只會選擇其中一個來執行查詢 - 找到列資料的索引。
當沒有使用 LIMIT 的時候這是一個正確的決定，就算只有一個索引的時後也是如此。
因為這通常可以根據 WHERE 的查詢子句，比較快速回傳所需列數的資料。
特別是在這個例子當中，WHERE 查詢並不是非常具有選擇性。
所以我嘗試這樣：
select * from table where id in (SELECT id FROM `table` WHERE (col1='A'||col1='B')) ORDER BY id DESC LIMIT 20 OFFSET 0;
在這個例子我們強迫 MySQL 取得已經排列過順序的資料，並且從子查詢當中檢查它是否符合我們原本的 WHERE 查詢。
如果妳透過 EXPLAIN 查看你會發現結果非常嚇人，但是事實上相依的子查詢執行只會在結果當中產生 20 筆列資料。
+----+--------------------+-------+-----------------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+--------------------+-------+-----------------+---------------+---------+---------+------+--------+-------------+
| 1 | PRIMARY | table | index | NULL | PRIMARY | 4 | NULL | 765105 | Using where
| 2 | DEPENDENT SUBQUERY | table | unique_subquery | PRIMARY,col1 | PRIMARY | 4 | func | 1 | Using where
+----+--------------------+-------+-----------------+---------------+---------+---------+------+--------+-------------+
這樣的結果反應了一個好很多回應時間：
(20 rows in set (0.01 sec))
透過使用子查詢改寫這個查詢我們確實提昇了一百倍的效能。看來子查詢並不全都是會降低效能的。
即便我們證明了使用子查詢並不總是降低效能，但這個方法並不是最好的方法。
我們並不需要使用單獨的子查詢，去確保 MySQL 在檢查資料表是否符合 WHERE 查詢的時候使用特定的索引。
我們可以使用 FORCE INDEX 提示去複寫 MySQL 所採用的索引。
mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1='A'||col1='B') order by id desc limit 20 offset 0;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | table | index | NULL | PRIMARY | 4 | NULL | 549117 | Using where
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
mysql> select * from table FORCE INDEX(PRIMARY) where (col1='A'||col1='B') order by id desc limit 20 offset 0;
...
20 rows in set (0.00 sec)
當 WHERE 查詢非常不具備選擇性的時候這個方法非常管用，
除此之外 MySQL 可能需要掃描非常大量的列資料才能找到相符的資料。妳可以使用另外一個幾年前由 Peter 所撰寫的技巧。
原文出處：[ MySQL ] When the subselect runs faster - 黑書記事 - 無名小站