1. 查詢速度慢并不只是因為SQL陳述句本身，還可能是因為記憶體分配不佳、檔案結構不合理等其他原因
1.1. 都是為了減少對硬碟的訪問
2. 不同代碼能夠得出相同結果
2.1. 從理論上來說，得到相同結果的不同代碼應該有相同的性能
2.2. 遺憾的是，查詢優化器生成的執行計劃很大程度上要受到代碼外部結構的影響
2.3. 如果想優化查詢性能，必須知道如何寫代碼才能使優化器的執行效率更高
3. 使用高效的查詢
3.1. 引數是子查詢時，使用EXISTS代替IN
3.1.1. IN謂詞卻有成為性能優化的瓶頸的危險
3.1.1.1. 當IN的引數是子查詢時，資料庫首先會執行子查詢，然后將結果存盤在一張臨時的作業表里（行內視圖），然后掃描整個視圖
3.1.1.2. 從代碼的可讀性上來看，IN要比EXISTS好
3.1.2. 示例
3.1.2.1.
--慢
SELECT ＊
FROM Class_A
WHERE id IN (SELECT id
FROM Class_B);
--快
SELECT ＊
FROM Class_A A
WHERE EXISTS
(SELECT ＊
FROM Class_B B
WHERE A.id = B.id);
3.1.2.1.1. 如果連接列（id）上建立了索引，那么查詢Class_B時不用查實際的表，只需查索引就可以了
3.1.2.1.2. 如果使用EXISTS，那么只要查到一行資料滿足條件就會終止查詢，不用像使用IN時一樣掃描全表
3.1.2.1.2.1. 在這一點上NOT EXISTS也一樣
3.1.2.1.3. 使用EXISTS的話，資料庫不會生成臨時的作業表
3.2. 引數是子查詢時，使用連接代替IN
3.2.1. 示例
3.2.1.1. --使用連接代替IN
SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B
ON A.id = B.id;
3.2.1.1.1. 至少能用到一張表的“id”列上的索引
3.2.1.1.2. 沒有了子查詢，所以資料庫也不會生成中間表
3.2.1.1.3. 如果沒有索引，那么與連接相比，可能EXISTS會略勝一籌
4. 避免排序
4.1. 在SQL語言中，用戶不能顯式地命令資料庫進行排序操作
4.2. 對用戶隱藏這樣的操作正是SQL的設計思想
4.3. 在資料庫內部頻繁地進行著暗中的排序
4.3.1. 會進行排序的代表性的運算
4.3.1.1. GROUP BY子句
4.3.1.2. ORDER BY子句
4.3.1.3. 聚合函式（SUM、COUNT、AVG、MAX、MIN）
4.3.1.4. DISTINCT
4.3.1.5. 集合運算子（UNION、INTERSECT、EXCEPT）
4.3.1.6. 視窗函式（RANK、ROW_NUMBER等）
4.4. 靈活使用集合運算子的ALL可選項
4.4.1. 如果不在乎結果中是否有重復資料，或者事先知道不會有重復資料，請使用UNION ALL代替UNION
4.4.2. 加上ALL可選項是優化性能的一個非常有效的手段
4.4.3. 對于INTERSECT和EXCEPT也是一樣的，加上ALL可選項后就不會進行排序了
4.5. 使用EXISTS代替DISTINCT
4.5.1. 為了排除重復資料，DISTINCT也會進行排序
4.5.1.1.
SELECT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;
4.5.1.2.
SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;
4.5.1.3.
SELECT item_no
FROM Items I
WHERE EXISTS
(SELECT ＊
FROM SalesHistory SH
WHERE I.item_no = SH.item_no);
4.6. 在極值函式中使用索引（MAX/MIN）
4.6.1. 使用這兩個函式時都會進行排序
4.6.1.1. --這樣寫需要掃描全表
SELECT MAX(item)
FROM Items;
4.6.2. 如果引數欄位上建有索引，則只需要掃描索引，不需要掃描整張表
4.6.2.1. --這樣寫能用到索引
SELECT MAX(item_no)
FROM Items;
4.6.3. 對于聯合索引，只要查詢條件是聯合索引的第一個欄位，索引就是有效的
4.6.4. 這種方法并不是去掉了排序這一程序，而是優化了排序前的查找速度，從而減弱排序對整體性能的影響
4.7. 能寫在WHERE子句里的條件不要寫在HAVING子句里
4.7.1. --聚合后使用HAVING子句過濾
SELECT sale_date, SUM(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';
4.7.2. --聚合前使用WHERE子句過濾
SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;
4.7.2.1. 在使用GROUP BY子句聚合時會進行排序，如果事先通過WHERE子句篩選出一部分行，就能夠減輕排序的負擔
4.7.2.2. 第二個是在WHERE子句的條件里可以使用索引，HAVING子句是針對聚合后生成的視圖進行篩選的，但是很多時候聚合后的視圖都沒有繼承原表的索引結構
4.8. 在GROUP BY子句和ORDER BY子句中使用索引
4.8.1. 通過指定帶索引的列作為GROUP BY和ORDER BY的列，可以實作高速查詢
4.8.2. 在一些資料庫中，如果操作物件的列上建立的是唯一索引，那么排序程序本身都會被省略掉
5. 真正用到索引！
5.1. 在索引欄位上進行運算
5.1.1.
SELECT ＊
FROM SomeTable
WHERE col_1 ＊ 1.1 ＞ 100;
5.2. 把運算的運算式放到查詢條件的右側，就能用到索引了
5.2.1. WHERE col_1 ＞ 100 / 1.1
5.3. 在查詢條件的左側使用函式時，也不能用到索引
5.3.1.
SELECT ＊
FROM SomeTable
WHERE SUBSTR(col_1, 1, 1) = 'a';
5.4. 如果無法避免在左側進行運算，那么使用函式索引也是一種辦法
5.5. 使用索引時，條件運算式的左側應該是原始欄位
5.6. 使用IS NULL謂詞
5.6.1. 索引欄位是不存在NULL的，所以指定IS NULL和IS NOT NULL的話會使得索引無法使用，進而導致查詢性能低下
5.6.1.1.
SELECT ＊
FROM SomeTable
WHERE col_1 IS NULL;
5.6.1.2. --IS NOT NULL的代替方案
SELECT ＊
FROM SomeTable
WHERE col_1 ＞ 0;
5.6.1.2.1. 如果要選擇“非NULL的行”，正確的做法還是使用IS NOT NULL
5.7. 使用否定形式
5.7.1. 否定形式不能用到索引
5.7.1.1. ＜＞
5.7.1.2. ! =
5.7.1.3. NOT IN
5.8. 使用OR
5.8.1. 在col_1和col_2上分別建立了不同的索引，或者建立了（col_1, col_2）這樣的聯合索引時，如果使用OR連接條件，那么要么用不到索引，要么用到了但是效率比AND要差很多
5.8.2. 如果無論如何都要使用OR，那么有一種辦法是位圖索引，但是這種索引的話更新資料時的性能開銷會增大
5.9. 使用聯合索引時，列的順序錯誤
5.9.1. 假設存在這樣順序的一個聯合索引“col_1, col_2, col_3”
5.9.2.
○ SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
○ SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
× SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
× SELECT ＊ FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
× SELECT ＊ FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;
5.9.3. 聯合索引中的第一列（col_1）必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒
5.9.4. 有些資料庫里順序顛倒后也能使用索引，但是性能還是比順序正確時差一些
5.9.5. 如果無法保證查詢條件里列的順序與索引一致，可以考慮將聯合索引拆分為多個索引
5.10. 使用LIKE謂詞進行后方一致或中間一致的匹配
5.10.1. 只有前方一致的匹配才能用到索引
5.10.2.
× SELECT ＊ FROM SomeTable WHERE col_1 LIKE '%a';
× SELECT ＊ FROM SomeTable WHERE col_1 LIKE '%a%';
○ SELECT ＊ FROM SomeTable WHERE col_1 LIKE 'a%';
5.11. 進行默認的型別轉換
5.11.1. 默認的型別轉換不僅會增加額外的性能開銷，還會導致索引不可用
5.11.2. 在需要型別轉換時顯式地進行型別轉換
6. 減少中間表
6.1. 子查詢的結果會被看成一張新表，這張新表與原始表一樣，可以通過代碼進行操作
6.2. 靈活使用HAVING子句
6.2.1. 對聚合結果指定篩選條件時不需要專門生成中間表
6.2.2.
SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) ＞= 10;
6.2.3. HAVING子句和聚合操作是同時執行的，所以比起生成中間表后再執行的WHERE子句，效率會更高一些，而且代碼看起來也更簡潔
6.3. 需要對多個欄位使用IN謂詞時，將它們匯總到一處
6.3.1.
SELECT id, state, city
FROM Addresses1 A1
WHERE state IN (SELECT state
FROM Addresses2 A2
WHERE A1.id = A2.id)
AND city IN (SELECT city
FROM Addresses2 A2
WHERE A1.id = A2.id);
6.3.2.
SELECT ＊
FROM Addresses1 A1
WHERE id || state || city
IN (SELECT id || state|| city
FROM Addresses2 A2);
6.3.2.1. 子查詢不用考慮關聯性，而且只執行一次就可以
6.3.3.
SELECT ＊
FROM Addresses1 A1
WHERE (id, state, city)
IN (SELECT id, state, city
FROM Addresses2 A2);
6.3.3.1. 不用擔心連接欄位時出現的型別轉換問題
6.3.3.2. 不會對欄位進行加工，因此可以使用索引
6.4. 先進行連接再進行聚合
6.4.1. 連接和聚合同時使用時，先進行連接操作可以避免產生中間表
6.4.1.1. 連接做的是“乘法運算”
6.4.1.2. 連接表雙方是一對一、一對多的關系時，連接運算后資料的行數不會增加
6.5. 合理地使用視圖
6.5.1. 特別是視圖的定義陳述句中包含以下運算的時候，SQL會非常低效，執行速度也會變得非常慢
6.5.1.1. 聚合函式（AVG、COUNT、SUM、MIN、MAX）
6.5.1.2. 集合運算子（UNION、INTERSECT、EXCEPT等）
6.5.2. 物化視圖（materialized view）等技術，當視圖的定義變得復雜時，可以考慮使用一下
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/552453.html
標籤：其他
下一篇：返回列表