 |
|
茫茫網海中的冷日
發生過的事，不可能遺忘，只是想不起來而已！ |
|
|
恭喜您是本站第 1746961
位訪客!
登入 | 註冊
|
|
|
|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2012/11/13 8:27 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15773
|
- [轉貼][ MySQL ] When the subselect runs faster
- [ 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 - 黑書記事 - 無名小站
|
|
|
討論串
|
[轉貼]只談MySQL (第15天) Subquery (冷日