歡迎關注我的頭條號：Wooola，10年Java軟件開發及架構設計經驗，專注於Java、Golang、微服務架構，致力於每天分享原創文章、快樂編碼和開源技術。

作者 | 陳虹君 DBA專家 授權頭條原創首發

一、SQL優化整體思路

性能優化層次

SQL響應時間分析

T=響應時間，C=開銷，V=吞吐量

性能優化的目標：

1. 縮短響應時間
2. 減少資源開銷
3. 提高系統吞吐量

Sql 優化基本原則

1. 減少磁盤I/O：減少數據庫訪問
2. 減少網路傳輸：減少交互次數
3. 減少複雜計算：減少CPU開銷
4. 增加資源：利用多線程併發處理

SQL調優思路

SQL語句執行過程

綁定變量

二、表設計在SQL優化中的應用

命名規範

表相關對象的命名建議項目組統一用業務名稱命名，做到見名知意

1. ORACLE不區分大小寫

2. MYSQL在LINUX環境下區分大小寫,在Windows環境下不分區大小寫

最大長度限制 表名：30 其它：18不要使用數據庫保留關鍵字，不要使用空格以及特殊符號，特別注意不要使用數據庫保留關鍵字，不要使用空格以及特殊符號，特別注意編寫腳本時，不要帶雙引號

合理進行範式化設計

TIPS：並不是範式化程度越高的設計就越好，適當反範式化設計，可以達到以空間換時間的目的。

反範式化設計：增加字段-冗餘列，派生列

不同表類型特點

表設計規範

表設計你應該考慮：預期的數據量，數據保留週期，數據清理策略，字段查詢頻率

1. 選擇合適的表類型
2. 必須設計主鍵，可以不用設計外鍵（除非是關係非常緊密---特別是MySql）
3. 字段必須設置合理的約束（例如：業務上非空的必須設置NOT NULL ，業務上唯一的必須設置UNIQUE）
4. 選擇合適的字段類型
5. 儘量避免寬表（控制少而精，提升IO效率，緩存更多有效數據）
6. 大表考慮分表，分區（針對MYSQL 甚至分庫）
7. 慎用觸發器（儘量使用程序來實現）

正確匹配數據類型

1. integer,char,varchar …… 請暫時忘了它,常用的記住上面幾種就行
2. oracle的隱式轉換功能 …… 請忘掉它（當然也包括MySQL）

請別再讓字符、數字、時間混用了

1. 不允許用字符類型存放時間或日期類數據。
2. 不允許用字符類型存放數值類型的數據。
3. 不允許表中字段數值類型直接使用INT型，應明確寫明字段的取值範圍，如number(8)。int 型在數據庫中表示為number(38),造成存儲空間浪費。
4. 不允許表中長度不固定的字段字符類型直接使用CHAR、VARCHAR，應用VARCHAR2定義；
5. 如無特別需要，避免使用大字段(blob，clob，long，text，image等)，假設要使用可以使用SecureFiles 新一代LOB。
6. 同一個字段名在一個數據庫中只能代表一個意思，不同的表用於相同內容的字段應該採用同樣的名稱，字段類型定義。

主外鍵與約束設計

一般有一個設計原則，兩個表有主外建關係時，外鍵的列上要創建索引，這樣有兩大好處，1.關聯查詢更高效

2.外鍵所在的表更新不容易產生死鎖

Sharding 之 分庫分表分區

表分區技術基本原理

分區技術的效益和目標

分區類型選擇

三、索引設計加速數據檢索

索引與數據檢索

創建索引以進行有效率的數據查詢

Ø 數據量少時，即便沒有索引，全表搜索也不成問題

Ø 數據量大時，搜索性能問題（特別是全表搜索）才會產生

B+樹索引結構

索引設計規範

1. 合理選擇單列索引與組合索引
2. 應用模糊查詢時嚴禁左模糊匹配與全模糊匹配
3. 多表關聯查詢時確保被關聯字段存在索引，且確保需要JOIN的字段數據類型一致
4. 利用索引覆蓋進行查詢避免回表
5. 分區表儘量使用本地（LOCAL）索引，有利於數據維護，是否使用（ Global ）索引請遵循分區索引創建基本原則
6. 在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據 實際文本區分度決定索引長度即可【該規範只適用於MYSQL】
7. 位圖索引與函數索引應該儘量規避
8. 外鍵必須創建索引

單列索引

創建單列索引的要素：

• 頻繁出現在where條件中的列
• 用來與其它表進行連接的列
• 有高的選擇性和過濾性的列（高選擇性的字段：如果很少的字段擁有相同值,即有很多唯一值,則選擇性很好）
• Oracle與MySql在主鍵字段和UNIQUE字段上會自動建立唯一索引

一般在查詢數據量佔總表數據量比例較小時才考慮使用索引定位數據，否則大量數據掃描還不如全表

複合索引

創建複合索引的要素：最左前綴原則、覆蓋索引原則

• 適用於單獨查詢返回記錄較多，而組合查詢後返回記錄較少的情況

例如where 學歷=本科 以上會返回不少的記錄，where 工作=廚師 同樣會返回不少的記錄，那麼這2個條件任意一個查詢做索引，都會不合適，但學歷既是本科且工作又是廚師的，返回的記錄就少之又少了，這時候創建複合索引就非常合適

• 組合查詢的組合順序，要考慮單獨的前綴查詢情況（否則單獨前綴查詢的索引不能生效或者只能用到跳躍索引），我們應該讓選擇性好的做為前導列

例如我們創建object_id,object_type的聯合索引時，要看考慮是單獨where object_id=xxx查詢的多，還是單獨where object_type=xxx查詢的多

• 僅等值無範圍查詢時，複合索引順序不影響查詢性能（比如where col1=xxx and col2=xxx，無論COL1+COL2組合還是COL2+COL1組合 都一樣的效果）
• 複合索引最佳順序一般是將列等值查詢的列置前

分區索引

• 可以將索引存儲在不同的分區中
• 與分區有關的索引有三種類型：

1. 局部分區索引 － 在分區表上創建的索引，在每個表分區上創建獨立的索引，索引的分區範圍與表一致
2. 全局分區索引 － 在分區表或非分區表上創建的索引，索引單獨指定分區的範圍，與表的分區範圍或是否分區無關
3. 全局非分區索引 － 在分區表上創建的全局普通索引，索引沒有被分區

▲需注意Oracle與MySQL的異同

索引失效情況分析

• 物理失效

索引真正意義上的失效，DML操作列時會報錯，比如人為設置了unusable操作，或者分區表相關操作導致的索引失效，以及其它操作如 move表等

• 邏輯失效

索引本身並沒有真正失效，而只是因為SQL的寫法等一些原因導致索引利用不上那麼哪些情況會導致索引無法使用？

索引去哪兒了

1. 字段類型不匹配，發生了隱式或顯示轉換
2. Like 與 %前置模糊查詢
3. 反向鍵索引不支持範圍查詢
4. 索引列進行了函數轉換或者表達式運算
5. 不等於<>, != 操作
6. 查詢謂詞條件是 is null
7. 複合索引前置列設置錯誤

四、經典表連接方式應用

經典的3種表連接方式解讀

表連接方式特點與優化建議

五、SQL審核與編碼規範

SQL審核防範於未然

我們應該建立長效的機制，為應用的健康穩定運行保駕護航

1. 通過規範與培訓提高開發人員能力
2. 通過SQL審核進行SQL質量控制
3. 通過SQL捕獲優化解決現有程序問題

書寫規則

SQL開發審核規範

六、項目案例解析

SQL拆解分析，整個SQL其實就由3部分組成

糾結的寫法

目前BPM系統存在的問題基本全在SQL開發審核規範覆蓋範圍之內

1. 大量查詢使用*號
2. 沒有使用綁定變量
3. 大量查詢使用全模糊搜索查詢”%”
4. 大量全表掃描（未創建索引or索引設計不合理）
5. 分頁查詢框架使用錯誤
6. 大表沒有數據清理策略（採取分表OR分區OR歷史數據歸檔）
7. 部分存在主外鍵關係約束的表，外鍵沒有創建索引
8. 字段類型設計不合理

案例1：表設計問題 - - - 問題描述

存在問題：

1. 表沒有設計主鍵
2. sendresult字段類型設計不合理
3. Sendtime 字段類型設計不合理
4. 表索引設計不合理
5. sendresult字段類型設計不合理
6. Sendtime 字段類型設計不合理
7. 表索引設計不合理

如何改進：

1. wf_orunid 設計主鍵
2. sendresult字段類型應該設計為整形
3. Sendtime 字段類型應該設計為時間類型
4. wf_orunid 應該設計為唯一索引
5. 分表或者按時間字段分區
6. 業務上非空的字段應該顯示設計not null 非空約束
7. 注意CLOB字段

案例2：沒有使用綁定變量 - - - 問題討論

案例3：分頁框架使用錯誤 - - - 問題討論

解決方案

案例3：分頁框架使用拓展 - - - 解決方案 續1

分頁SQL語句的優化思路：避免排序，減少掃描

1. 在分頁的需求中存在排序的時候，如何避免，我們可以利用索引已經排序的特性，將order by 的列包含在索引中
2. .利用rownnum 的COUNT STOPKEY 特性，減少掃描直至N行停止

拓展：多表關聯分頁如何優化

優化思路：多表關聯分頁語句如果存在排序，只能對其中一個表進行排序，且讓參與排序的表作為嵌套循環的驅動表，並且驅動表返回數據的順序要與排序的順序一致，表的連接列必須創建好索引，如果有外連接，排序的字段只能是主表的字段，SQL語句中不要有distinct,group by ,union,union all,avg,max,min 操作

案例3：分頁框架使用拓展 - - - 解決方案 續2

拓展：MySQL分頁框架

開發常用框架：

select * from tab where tab_id=:B1 limit M,N

這是通常普通的分頁寫法,越往後翻（M越大）性能越差

建議分頁框架：

select t1.* from tab t1,

(select priv_id from tab where tab_id=:B1 limit M,N) t2

where t1. priv_id =t2. priv_id

Notes：priv_id為tab表的主鍵，tab_id字段需創建索引

案例4：索引設計問題---續

案例5：索引列發生計算引發失效問題

案例6：索引列NULL值引發失效問題

案例6：索引列NULL值引發失效問題 ---續

案例7：select 查詢 *號問題

案例8：LIKE 與%模糊查詢問題

案例9：外鍵與索引引發的性能問題

"

歡迎關注我的頭條號：Wooola，10年Java軟件開發及架構設計經驗，專注於Java、Golang、微服務架構，致力於每天分享原創文章、快樂編碼和開源技術。

作者 | 陳虹君 DBA專家 授權頭條原創首發

一、SQL優化整體思路

性能優化層次

SQL響應時間分析

T=響應時間，C=開銷，V=吞吐量

性能優化的目標：

1. 縮短響應時間
2. 減少資源開銷
3. 提高系統吞吐量

Sql 優化基本原則

1. 減少磁盤I/O：減少數據庫訪問
2. 減少網路傳輸：減少交互次數
3. 減少複雜計算：減少CPU開銷
4. 增加資源：利用多線程併發處理

SQL調優思路

SQL語句執行過程

綁定變量

二、表設計在SQL優化中的應用

命名規範

表相關對象的命名建議項目組統一用業務名稱命名，做到見名知意

1. ORACLE不區分大小寫

2. MYSQL在LINUX環境下區分大小寫,在Windows環境下不分區大小寫

最大長度限制 表名：30 其它：18不要使用數據庫保留關鍵字，不要使用空格以及特殊符號，特別注意不要使用數據庫保留關鍵字，不要使用空格以及特殊符號，特別注意編寫腳本時，不要帶雙引號

合理進行範式化設計

TIPS：並不是範式化程度越高的設計就越好，適當反範式化設計，可以達到以空間換時間的目的。

反範式化設計：增加字段-冗餘列，派生列

不同表類型特點

表設計規範

表設計你應該考慮：預期的數據量，數據保留週期，數據清理策略，字段查詢頻率

1. 選擇合適的表類型
2. 必須設計主鍵，可以不用設計外鍵（除非是關係非常緊密---特別是MySql）
3. 字段必須設置合理的約束（例如：業務上非空的必須設置NOT NULL ，業務上唯一的必須設置UNIQUE）
4. 選擇合適的字段類型
5. 儘量避免寬表（控制少而精，提升IO效率，緩存更多有效數據）
6. 大表考慮分表，分區（針對MYSQL 甚至分庫）
7. 慎用觸發器（儘量使用程序來實現）

正確匹配數據類型

1. integer,char,varchar …… 請暫時忘了它,常用的記住上面幾種就行
2. oracle的隱式轉換功能 …… 請忘掉它（當然也包括MySQL）

請別再讓字符、數字、時間混用了

1. 不允許用字符類型存放時間或日期類數據。
2. 不允許用字符類型存放數值類型的數據。
3. 不允許表中字段數值類型直接使用INT型，應明確寫明字段的取值範圍，如number(8)。int 型在數據庫中表示為number(38),造成存儲空間浪費。
4. 不允許表中長度不固定的字段字符類型直接使用CHAR、VARCHAR，應用VARCHAR2定義；
5. 如無特別需要，避免使用大字段(blob，clob，long，text，image等)，假設要使用可以使用SecureFiles 新一代LOB。
6. 同一個字段名在一個數據庫中只能代表一個意思，不同的表用於相同內容的字段應該採用同樣的名稱，字段類型定義。

主外鍵與約束設計

一般有一個設計原則，兩個表有主外建關係時，外鍵的列上要創建索引，這樣有兩大好處，1.關聯查詢更高效

2.外鍵所在的表更新不容易產生死鎖

Sharding 之 分庫分表分區

表分區技術基本原理

分區技術的效益和目標

分區類型選擇

三、索引設計加速數據檢索

索引與數據檢索

創建索引以進行有效率的數據查詢

Ø 數據量少時，即便沒有索引，全表搜索也不成問題

Ø 數據量大時，搜索性能問題（特別是全表搜索）才會產生

B+樹索引結構

索引設計規範

1. 合理選擇單列索引與組合索引
2. 應用模糊查詢時嚴禁左模糊匹配與全模糊匹配
3. 多表關聯查詢時確保被關聯字段存在索引，且確保需要JOIN的字段數據類型一致
4. 利用索引覆蓋進行查詢避免回表
5. 分區表儘量使用本地（LOCAL）索引，有利於數據維護，是否使用（ Global ）索引請遵循分區索引創建基本原則
6. 在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據 實際文本區分度決定索引長度即可【該規範只適用於MYSQL】
7. 位圖索引與函數索引應該儘量規避
8. 外鍵必須創建索引

單列索引

創建單列索引的要素：

• 頻繁出現在where條件中的列
• 用來與其它表進行連接的列
• 有高的選擇性和過濾性的列（高選擇性的字段：如果很少的字段擁有相同值,即有很多唯一值,則選擇性很好）
• Oracle與MySql在主鍵字段和UNIQUE字段上會自動建立唯一索引

一般在查詢數據量佔總表數據量比例較小時才考慮使用索引定位數據，否則大量數據掃描還不如全表

複合索引

創建複合索引的要素：最左前綴原則、覆蓋索引原則

• 適用於單獨查詢返回記錄較多，而組合查詢後返回記錄較少的情況

例如where 學歷=本科 以上會返回不少的記錄，where 工作=廚師 同樣會返回不少的記錄，那麼這2個條件任意一個查詢做索引，都會不合適，但學歷既是本科且工作又是廚師的，返回的記錄就少之又少了，這時候創建複合索引就非常合適

• 組合查詢的組合順序，要考慮單獨的前綴查詢情況（否則單獨前綴查詢的索引不能生效或者只能用到跳躍索引），我們應該讓選擇性好的做為前導列

例如我們創建object_id,object_type的聯合索引時，要看考慮是單獨where object_id=xxx查詢的多，還是單獨where object_type=xxx查詢的多

• 僅等值無範圍查詢時，複合索引順序不影響查詢性能（比如where col1=xxx and col2=xxx，無論COL1+COL2組合還是COL2+COL1組合 都一樣的效果）
• 複合索引最佳順序一般是將列等值查詢的列置前

分區索引

• 可以將索引存儲在不同的分區中
• 與分區有關的索引有三種類型：

1. 局部分區索引 － 在分區表上創建的索引，在每個表分區上創建獨立的索引，索引的分區範圍與表一致
2. 全局分區索引 － 在分區表或非分區表上創建的索引，索引單獨指定分區的範圍，與表的分區範圍或是否分區無關
3. 全局非分區索引 － 在分區表上創建的全局普通索引，索引沒有被分區

▲需注意Oracle與MySQL的異同

索引失效情況分析

• 物理失效

索引真正意義上的失效，DML操作列時會報錯，比如人為設置了unusable操作，或者分區表相關操作導致的索引失效，以及其它操作如 move表等

• 邏輯失效

索引本身並沒有真正失效，而只是因為SQL的寫法等一些原因導致索引利用不上那麼哪些情況會導致索引無法使用？

索引去哪兒了

1. 字段類型不匹配，發生了隱式或顯示轉換
2. Like 與 %前置模糊查詢
3. 反向鍵索引不支持範圍查詢
4. 索引列進行了函數轉換或者表達式運算
5. 不等於<>, != 操作
6. 查詢謂詞條件是 is null
7. 複合索引前置列設置錯誤

四、經典表連接方式應用

經典的3種表連接方式解讀

表連接方式特點與優化建議

五、SQL審核與編碼規範

SQL審核防範於未然

我們應該建立長效的機制，為應用的健康穩定運行保駕護航

1. 通過規範與培訓提高開發人員能力
2. 通過SQL審核進行SQL質量控制
3. 通過SQL捕獲優化解決現有程序問題

書寫規則

SQL開發審核規範

六、項目案例解析

SQL拆解分析，整個SQL其實就由3部分組成

糾結的寫法

目前BPM系統存在的問題基本全在SQL開發審核規範覆蓋範圍之內

1. 大量查詢使用*號
2. 沒有使用綁定變量
3. 大量查詢使用全模糊搜索查詢”%”
4. 大量全表掃描（未創建索引or索引設計不合理）
5. 分頁查詢框架使用錯誤
6. 大表沒有數據清理策略（採取分表OR分區OR歷史數據歸檔）
7. 部分存在主外鍵關係約束的表，外鍵沒有創建索引
8. 字段類型設計不合理

案例1：表設計問題 - - - 問題描述

存在問題：

1. 表沒有設計主鍵
2. sendresult字段類型設計不合理
3. Sendtime 字段類型設計不合理
4. 表索引設計不合理
5. sendresult字段類型設計不合理
6. Sendtime 字段類型設計不合理
7. 表索引設計不合理

如何改進：

1. wf_orunid 設計主鍵
2. sendresult字段類型應該設計為整形
3. Sendtime 字段類型應該設計為時間類型
4. wf_orunid 應該設計為唯一索引
5. 分表或者按時間字段分區
6. 業務上非空的字段應該顯示設計not null 非空約束
7. 注意CLOB字段

案例2：沒有使用綁定變量 - - - 問題討論

案例3：分頁框架使用錯誤 - - - 問題討論

解決方案

案例3：分頁框架使用拓展 - - - 解決方案 續1

分頁SQL語句的優化思路：避免排序，減少掃描

1. 在分頁的需求中存在排序的時候，如何避免，我們可以利用索引已經排序的特性，將order by 的列包含在索引中
2. .利用rownnum 的COUNT STOPKEY 特性，減少掃描直至N行停止

拓展：多表關聯分頁如何優化

優化思路：多表關聯分頁語句如果存在排序，只能對其中一個表進行排序，且讓參與排序的表作為嵌套循環的驅動表，並且驅動表返回數據的順序要與排序的順序一致，表的連接列必須創建好索引，如果有外連接，排序的字段只能是主表的字段，SQL語句中不要有distinct,group by ,union,union all,avg,max,min 操作

案例3：分頁框架使用拓展 - - - 解決方案 續2

拓展：MySQL分頁框架

開發常用框架：

select * from tab where tab_id=:B1 limit M,N

這是通常普通的分頁寫法,越往後翻（M越大）性能越差

建議分頁框架：

select t1.* from tab t1,

(select priv_id from tab where tab_id=:B1 limit M,N) t2

where t1. priv_id =t2. priv_id

Notes：priv_id為tab表的主鍵，tab_id字段需創建索引

案例4：索引設計問題---續

案例5：索引列發生計算引發失效問題

案例6：索引列NULL值引發失效問題

案例6：索引列NULL值引發失效問題 ---續

案例7：select 查詢 *號問題

案例8：LIKE 與%模糊查詢問題

案例9：外鍵與索引引發的性能問題

"