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）等技術，當視圖的定義變得復雜時，可以考慮使用一下

標籤：MySQL

上一篇：資料操作（基礎）

下一篇：返回列表