SQL執行慢的原因分析以及調優手段

• 開發規範
  • ※謹慎使用 MySQL 分區表
  • ※經常一起使用的列放到一個表中
  • ※禁止在資料庫中存儲文件（比如圖片）這類大的二進位數據
  • 所有表必須使用 InnoDB 存儲引擎
  • 資料庫和表的字元集統一使用 UTF8
  • 儘量控制單表數據量的大小，建議控制在 500 萬以內
  • ※優先選擇符合存儲需要的最小的數據類型
  • ※儘可能把所有列定義為 NOT NULL
  • 使用 TIMESTAMP(4 個位元組) 或 DATETIME 類型 (8 個位元組) 存儲時間
• 索引規範
  • 建議單張表索引不超過 5 個
  • 禁止給表中的每一列都建立單獨的索引
  • 每個 InnoDB 表必須有個主鍵
  • 常見索引列建議
  • 創建聯合索引如何選擇索引列的順序
  • 對於頻繁的查詢優先考慮使用覆蓋索引
  • ※索引 SET 規範
• SQL語句優化
  • 如何找出需要優化的 SQL 語句
  • 禁止使用 SELECT * 必須使用 SELECT <欄位列表> 查詢
  • 充分利用表上已經存在的索引
  • 儘量避免使用子查詢
  • 用IN來替換OR
  • 讀取適當的記錄LIMIT M,N
  • 若兩個結果集沒有重覆使用UNION ALL
  • 儘可能批量Insert插入
  • WHERE 從句中禁止對列進行函數轉換和計算
  • 優化Group By語句
  • 優化Join語句

1、大多數情況下很正常，偶爾很慢，則有如下原因

(1)、資料庫在刷新臟頁，例如 redo log 寫滿了需要同步到磁碟。
(2)、執行的時候，遇到鎖，如表鎖、行鎖。
(3)、sql寫的爛

2、這條 SQL 語句一直執行的很慢，則有如下原因
(1)、沒有用上索引或索引失效：例如該欄位沒有索引；或則由於對欄位進行運算、函數操作導致無法用索引。
(2)、有索引但走了全表掃描

怎樣判斷是否走全表掃描：
索引區分度（索引的值不同越多，區分度越高），稱為基數，而數據量大時不可能全部掃描一遍得到基數，而是採樣部分數據進行預測，那有可能預測錯了，導致走全表掃描。

優化角度：索引+sql語句+資料庫結構優化+優化器優化+架構優化

對開發者來說，調優重點在開發規範、索引和線上慢查詢。

開發規範

※謹慎使用 MySQL 分區表

分區表在物理上表現為多個文件，在邏輯上表現為一個表；

謹慎選擇分區鍵，跨分區查詢效率可能更低；

建議採用物理分表的方式管理大數據。

※經常一起使用的列放到一個表中

避免更多的關聯操作。經常聯合查詢的表，可以考慮建立中間表。

※禁止在資料庫中存儲文件（比如圖片）這類大的二進位數據

在資料庫中存儲文件會嚴重影響資料庫性能，消耗過多存儲空間。

文件（比如圖片）這類大的二進位數據通常存儲於文件伺服器，資料庫只存儲文件地址信息。

所有表必須使用 InnoDB 存儲引擎

InnoDB 支持事務，支持行級鎖，更好的恢復性，高併發下性能更好。

資料庫和表的字元集統一使用 UTF8

相容性更好，統一字元集可以避免由於字元集轉換產生的亂碼，不同的字元集進行比較前需要進行轉換會造成索引失效，如果資料庫中有存儲 emoji 表情的需要，字元集需要採用 utf8mb4 字元集。

儘量控制單表數據量的大小，建議控制在 500 萬以內

可以用歷史數據歸檔（應用於日誌數據），分庫分表（應用於業務數據）等手段來控制數據量大小

※優先選擇符合存儲需要的最小的數據類型

存儲位元組越小，占用也就空間越小，性能也越好。

1. 某些字元串可以轉換成數字類型存儲比如可以將 IP 地址轉換成整型數據。

數字是連續的，性能更好，占用空間也更小。

MySQL 提供了兩個方法來處理 ip 地址

• INET_ATON() ： 把 ip 轉為無符號整型 (4-8 位)
• INET_NTOA() :把整型的 ip 轉為地址

插入數據前，先用 INET_ATON() 把 ip 地址轉為整型，顯示數據時，使用 INET_NTOA() 把整型的 ip 地址轉為地址顯示即可。

2. 對於非負型的數據 (如自增 ID,整型 IP，年齡) 來說,要優先使用無符號整型來存儲。

因為無符號相對於有符號可以多出一倍的存儲空間

3. 小數值類型（比如年齡、狀態表示如 0/1）優先使用 TINYINT 類型。

※儘可能把所有列定義為 NOT NULL

除非有特別的原因使用 NULL 值，應該總是讓欄位保持 NOT NULL。

• 索引 NULL 列需要額外的空間來保存，所以要占用更多的空間；
• 進行比較和計算時要對 NULL 值做特別的處理。

使用 TIMESTAMP(4 個位元組) 或 DATETIME 類型 (8 個位元組) 存儲時間

TIMESTAMP 存儲的時間範圍 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

TIMESTAMP 占用 4 位元組和 INT 相同，但比 INT 可讀性高

超出 TIMESTAMP 取值範圍的使用 DATETIME 類型存儲

經常會有人用字元串存儲日期型的數據（不正確的做法）

• 缺點 1：無法用日期函數進行計算和比較
• 缺點 2：用字元串存儲日期要占用更多的空間

索引規範

建議單張表索引不超過 5 個

因為 MySQL 優化器在選擇如何優化查詢時，會根據統一信息，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，就會增加 MySQL 優化器生成執行計劃的時間，同樣會降低查詢性能。

禁止給表中的每一列都建立單獨的索引

不如使用一個聯合索引

每個 InnoDB 表必須有個主鍵

InnoDB 是一種索引組織表：數據的存儲的邏輯順序和索引的順序是相同的。每個表都可以有多個索引，但是表的存儲順序只能有一種。

InnoDB 是按照主鍵索引的順序來組織表的

• 不要使用更新頻繁的列作為主鍵，不適用多列主鍵（相當於聯合索引）
• 不要使用 UUID,MD5,HASH,字元串列作為主鍵（無法保證數據的順序增長）
• 主鍵建議使用自增 ID 值

常見索引列建議

• 出現在 SELECT、UPDATE、DELETE 語句的 WHERE 從句中的列
• 包含在 ORDER BY、GROUP BY、DISTINCT 中的欄位
• 不用將符合 1 和 2 中的欄位的列都建立一個索引， 通常將 1、2 中的欄位建立聯合索引效果更好
• 多表 join 的關聯列

創建聯合索引如何選擇索引列的順序

建立索引的目的是：希望通過索引進行數據查找，減少隨機 IO，增加查詢性能 ，索引能過濾出越少的數據，則從磁碟中讀入的數據也就越少。

• 區分度最高的放在聯合索引的最左側（區分度=列中不同值的數量/列的總行數）
• 儘量把欄位長度小的列放在聯合索引的最左側（因為欄位長度越小，一頁能存儲的數據量越大，IO 性能也就越好）
• 使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）

對於頻繁的查詢優先考慮使用覆蓋索引

覆蓋索引：就是包含了所有查詢欄位 (where, select, order by, group by 包含的欄位) 的索引

※索引 SET 規範

儘量避免使用外鍵約束

• 不建議使用外鍵約束（foreign key），但一定要在表與表之間的關聯鍵上建立索引
• 外鍵可用於保證數據的參照完整性，但建議在業務端實現
• 外鍵會影響父表和子表的寫操作從而降低性能

SQL語句優化

如何找出需要優化的 SQL 語句

1. 通過查詢 MySQL 的慢查詢日誌來發現需要進行優化的 SQL 語句；

2. explain查看SQL的執行計劃，這樣就知道是否命中索引了。

   當explain與SQL一起使用時，MySQL將顯示來自優化器的有關語句執行計劃的信息。

   

   重點關註type、rows、filtered、extra、key

   1. type

      type表示連接類型，查看索引執行情況的一個重要指標。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

      • system：這種類型要求資料庫表中只有一條數據，是const類型的一個特例，一般情況下是不會出現的。
      • const：通過一次索引就能找到數據，一般用於主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。
      • eq_ref：常用於主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
      • ref : 常用於非主鍵和唯一索引掃描。
      • ref_or_null：這種連接類型類似於ref，區別在於MySQL會額外搜索包含NULL值的行
      • index_merge：使用了索引合併優化方法，查詢使用了兩個以上的索引。
      • unique_subquery：類似於eq_ref，條件用了in子查詢
      • index_subquery：區別於unique_subquery，用於非唯一索引，可以返回重覆值。
      • range：常用於範圍查詢，比如：between ... and 或 In 等操作
      • index：全索引掃描
      • ALL：全表掃描

   2. rows

      該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數。對於InnoDB表，此數字是估計值，並非一定是個準確值。

   3. filtered

      該列是一個百分比的值，表裡符合條件的記錄數的百分比。簡單點說，這個欄位表示存儲引擎返回的數據在經過過濾後，剩下滿足條件的記錄數量的比例。

   4. extra

      該欄位包含有關MySQL如何解析查詢的其他信息，它一般會出現這幾個值：

      • Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會出現。一般見於order by語句
      • Using index ：表示是否用了覆蓋索引。
      • Using temporary: 表示是否使用了臨時表,性能特別差，需要重點優化。一般多見於group by語句，或者union語句。
      • Using where : 表示使用了where條件過濾.
      • Using index condition：MySQL5.6之後新增的索引下推。在存儲引擎層進行數據過濾，而不是在服務層過濾，利用索引現有的數據減少回表的數據。

   5. key

      該列表示實際用到的索引。一般配合possible_keys列一起看。

3. show profile 分析

   瞭解SQL執行的線程的狀態及消耗的時間。

   預設是關閉的，開啟語句“set profiling = 1;”

   SHOW PROFILES ;
   SHOW PROFILE FOR QUERY  #{id};
   

4. trace

   trace分析優化器如何選擇執行計劃，通過trace文件能夠進一步瞭解為什麼選擇A執行計劃而不選擇B執行計劃。

禁止使用 SELECT * 必須使用 SELECT <欄位列表> 查詢

• SELECT * 消耗更多的 CPU 和 IO 以網路帶寬資源
• SELECT * 無法使用覆蓋索引
• SELECT <欄位列表> 可減少表結構變更帶來的影響

充分利用表上已經存在的索引

一個 SQL 只能利用到複合索引中的一列進行範圍查詢。如：有 a,b,c 列的聯合索引，在查詢條件中有 a 列的範圍查詢，則在 b,c 列上的索引將不會被用到。

在定義聯合索引時，如果 a 列要用到範圍查找的話，就要把 a 列放到聯合索引的右側，使用 left join 或 not exists 來優化 not in 操作，因為 not in 也通常會使用索引失效。

儘量避免使用子查詢

用join連接

用IN來替換OR

# 優化前
SELECT * FROM t WHERE id = 10 OR id = 30;
# 優化後
SELECT * FROM t WHERE id IN (10, 30);

對於連續的數值，能用between就不要用in了；再或者使用join連接來替換。

讀取適當的記錄LIMIT M,N

# 優化前
SELECT id.name FROM t LIMIT 866613, 20

對於limit m,n 的分頁查詢，越往後面翻頁（即m越大的情況下）SQL的耗時會越來越長，對於這種應該先取出主鍵id，然後通過主鍵id跟原表進行Join關聯查詢。因為MySQL並不是跳過offset行，而是取offset+N行，然後放棄前offset行，返回N行，那當offset特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行SQL改寫。

優化的方法如下：可以取前一頁的最大行數的id（將上一次遍歷到的最末尾的數據ID傳給資料庫，然後直接定位到該ID處，再往後面遍曆數據），然後根據這個最大的id來限制下一頁的起點。比如此列中，上一頁最大的id是866612。sql可以採用如下的寫法：

# 優化後
SELECT id.name FROM table_name WHERE id> 866612 LIMIT 20

若兩個結果集沒有重覆使用UNION ALL

union 和 union all 的差異主要是前者需要將結果集合併後再進行唯一性過濾操作，這就會涉及到排序，增加大量的CPU運算，加大資源消耗及延遲。

當然，union all 的前提條件是兩個結果集沒有重覆數據。所以一般是我們明確知道不會出現重覆數據的時候才建議使用 union all 提高速度。

儘可能批量Insert插入

INSERT INTO t(id, name) VALUES(1, 'aaa');
INSERT INTO t(id, name) VALUES(2, 'bbb');
INSERT INTO t(id, name) VALUES(3, 'ccc');
 
-->
INSERT INTO t(id, name) VALUES(1, 'aaa'),(2, 'bbb'),(3, 'ccc');

WHERE 從句中禁止對列進行函數轉換和計算

對列進行函數轉換或計算時會導致無法使用索引不推薦：

where date(create_time)='20190101'

推薦：

where create_time >= '20190101' and create_time < '20190102'

優化Group By語句

如果對group by 語句的結果沒有排序要求，要在語句後面加上order by null (group預設會排序)；

儘量讓group by 過程用上表的索引，確認方法是explain結果里沒有Using temporary和Using filesort;

如果group by 需要統計的數據量不大，儘量只使用記憶體臨時表；也可以通過適當調大tmp_table_size參數，來避免用到磁碟臨時表；

如果數據量實在太大，使用SQL_BIG_RESULT這個提示，來告訴優化器直接使用排序演算法（直接用磁碟臨時表）得到group by 的結果。

使用where字句替換having子句：避免使用having子句，having只會在檢索出所有記錄之後才會對結果集進行過濾，這個處理需要排序分組，如果能通過where子句提前過濾查詢的數目，就可以減少這方面的開銷。

# 優化前
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'
# 優化後
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

優化Join語句

當我們執行兩個表的Join的時候，就會有一個比較的過程，逐條比較兩個表的語句是比較慢的，因此可以把兩個表中數據一次讀進一個記憶體塊中，在MySQL中執行：

show variables like 'join_buffer_size'

可以看到Join在記憶體中的緩存池大小，其大小將會影響Join語句的性能。在執行Join的時候，資料庫會選擇一個表把它要返回以及需要進行和其他表進行比較的數據放進join_buffer。

1. 當連接查詢沒有where條件時

• left join前面的表是驅動表，後面的表是被驅動表
• right join後面的表是驅動表，前面的表是被驅動表
• inner join / join會自動選擇表數據比較少的作為驅動表
• traight_join(≈join)直接選擇左邊的表作為驅動表（語義上與join類似，但去除了join自動選擇小表作為驅動表的特性）

2. 當連接查詢有where條件時，帶where條件的表是驅動表，否則是被驅動表

假如有表如右邊：t1與t2表完全一樣，a欄位有索引，b無索引，t1有100條數據，t2有1000條數據

1. 對被驅動表的join欄位上建立索引;
2. 當被驅動表的join欄位上無法建立索引時，設置足夠的 Join Buffer Size;
3. 儘量用 inner join(因為其會自動選擇小表去驅動大表)，避免 left join（一般我們使用left join 的場景是大表驅動小表）和 NULL
4. left join是由左邊決定的，左邊一定都有，所以右邊是我們的關鍵點，建立索引要建在右邊。當然如果索引是在左邊的，我們可以考慮使用右連接