|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2012/11/13 8:22 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]只談MySQL (第15天) Subquery
- 只談MySQL (第15天) Subquery
標籤：linux mysql
昨天談到Subquery...今天我就針對這個主題做點分享...
Subquery的運用很泛的, 大致有下列:
1. Subquery做為數值運算
例如:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2 WHERE column2='20091027');
由資料表t2的column2為'20091027'的資料錄中的column1取得值再去和t1的column1欄位值做比較
或是另一個例子:
SELECT column1, (SELECT column1 FROM t2 WHERE column2='20091027') FROM t1 WHERE column2 = '20091027';
傳回t1的column1資料, t2的column1資料, 而t1及t2的column2值都必需為'20091027'
2. 運用Subqueries作比較運算
例:
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);
傳回t1中column1等於t2中的column2最大值的資料錄
3. 和ANY, IN, SOME合用的Subqueries
例如:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); ..t1中的s1等於t2中s1欄位的任一值
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2); .. 同上 = ANY
SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2); ..t1中的s1不等於t2中s1欄位的任一值
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2); ..同上<>ANY
4. 和ALL合用的Subqueries
例如:
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
..t1中s1值大於t2中的s2欄位的所有值
5. Row Subqueries
舉例來說最明白:
SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2); 或
SELECT column1,column2,column3
FROM t1
WHERE ROW(column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);
兩條指令都是一樣的, 看出ROW的用法了嗎?
6. EXISTS和NOT EXISTS
例如:
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);
判斷Subquery是否有資料錄....有的話, 第一條WHERE成立, 第二條不成立
7. 在FROM中的Subqueries
舉例來說:
SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;
用了一個Subquery當成資料來源t1...可以把Subquery當成暫存資料表或檢視表
關於Subquery, 我們先介紹到這裏, 以後我們會再來討論Subquery的優化問題(Optimization)
原文出處：只談MySQL (第15天) Subquery - iT邦幫忙::IT知識分享社群
|
|
|
冷日
(冷日) |
發表時間：2012/11/13 8:24 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]善用 sub query 優化 order by limit
- 善用 sub query 優化 order by limit
2009年9月22日 pigo
假設 , 我們有個 Table 叫 test , 有 20 萬筆記錄 , test 有 20 個欄位好了 , 其中有一個欄位 post_time 有建立索引可以排序用的 , 而有個主鍵 id
下一道指令
SQLSELECT * FROM test ORDER BY post_time DESC LIMIT 150000,10
這道指令看似簡單 , 理論上應該跑很快 ~ 實際上卻不然 , 也許跑上 20 秒 , 因為這牽扯到 MySQL Server 的 sort buffer 大小 , 因為這道指令可能會因為 buffer 不足而改用 disk 作 sort , 這樣就很慢了
這時候就要搬出 subquery 了 , 怎麼作 ? 看看下面
SQLSELECT * FROM test AS t
INNER JOIN ( SELECT id FROM test ORDER BY post_time DESC LIMIT 15000,10 ) AS s
ON t.id=s.id
我相信 , 這道指令應該 1 秒都不用
為何會如此呢 ?
因為我們在 subquery 中只有抓 id , 這樣 mysql 就只需要少量記憶體就可以存放資料列進行排序 , 原始的作法就是每一列有多少資料 , mysql 就必須分配多少記憶體 , 如果資料數太大 , 記憶體不夠分配 , 自然就是用 disk 了
但如果我們要抓的資料只是前面幾筆 , 例如 LIMIT 100,10 , 那麼原本的寫法會比較快 , 因此這可以在程式中判斷而交互使用兩種 QUERY 的方式
原文出處：壞蛋的部落格 » 善用 sub query 優化 order by limit
|
|
|
冷日
(冷日) |
發表時間：2012/11/13 8:25 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]MySQL 子查詢(sub query) limit 的用法
- MySQL 子查詢(sub query) limit 的用法
select * from (select * from table order by a limit 100 ) as newtable order by newtable.b
newtable 是一個別名，取什麼都可以
原因是：
error(#1248 Every derived table must have its own alias)
原文出處：MySQL 子查詢(sub query) limit 的用法 @ Arrack筆記 :: 痞客邦 PIXNET ::
|
|
|
|
|
冷日
(冷日) |
發表時間：2012/11/13 8:27 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼][ 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 - 黑書記事 - 無名小站
|
|
|
|
