1. 優化查詢語句

避免復雜的子查詢（能用連接查詢替代時）

• 性能不佳：
  • 許多子查詢在主查詢每處理一條記錄時，都可能需要重新執行一次。對于查詢結果中嵌套資產，外層查詢結果集中的每一行數據，數據庫都要再次執行內層子查詢來進行相應的條件判斷或數據計算。
  • 對索引的利用不好：子查詢的條件關聯往往使得數據庫不能像處理簡單連接查詢那樣直接按照索引順序高效檢索數據。因為每次執行子查詢時的條件都是動態變化的（依賴于外層查詢當前行對應的值），導致原本可以快速定位數據的索引不能充分發揮作用，使得數據庫不得不進行全表掃描或者更多額外的查找操作來滿足子查詢的條件，從而影響查詢的速度
• 結果可能不準確：子查詢使用 IN、NOT IN時，如果結果中包含Null 可能無法正確返回數據
• 子查詢不易于維護，讀起來相對困難

使用索引優化查詢條件：

• 為經常用于查詢條件（如 WHERE 子句中的字段）、排序（ORDER BY）以及分組（GROUP BY）的字段創建索引。
• 但要注意避免過度索引，因為索引本身也會占用存儲空間，并且在數據增刪改操作時需要更新索引，增加了額外開銷。

相同的SQL多次重復執行時，使用批量操作

mysql中：

INSERT INTO students (student_id, student_name, age)
VALUES
(1001, 'Zhang San', 20),
(1002, 'Li Si', 21),
(1003, 'Wang Wu', 19);

Java中：

1. 在mybatis中配置 BatchExecutor 來執行批量SQL
2. mapper文件中使用 for-each 標簽批量插入
3. 單次批插入數量比較大，比如超過500條。可以每500條調用一次批量插入操作。

使用limit

SELECT 1 FROM emp a LIMIT 10 , 5; limit 從第10條之后，返回5條數據
SELECT * FROM emp a order by a.id LIMIT 5 offset 10;
LIMIT 10 , 5 作用等同于 LIMIT 5 offset 10。

• 作用過程：根據排序order后的總結果集，分為中間結果集逐段查找，直到找到需要的第幾塊和多少條后返回（頁數過大時，查找會慢）
  

避免使用 SELECT *

可以減少數據傳輸量，尤其是在表字段較多時，能顯著提升查詢效率

可以使用union all的情況，不要使用union

模糊查詢時，通配符放在字符后面利用索引 like '3000%';

提升group by的效率

對被group by的字段增加索引

小表驅動大表

數據量小、索引比較完備的表作為主表 join 關聯表來查詢

少關聯表，一般是1~3張表互相關聯

2. 優化表結構

合理設計表字段類型：

根據字段實際存儲的數據選擇合適的數據類型，避免使用過大的數據類型浪費存儲空間，同時也能提升數據操作的性能。

比如，用 INT 類型存儲整數比用 BIGINT 更節省空間且運算更快，若能確定年齡字段的值范圍在合理的整數區間內，就選擇 INT 類型存儲年齡

避免過多的冗余字段：

盡量通過合理的表關聯來獲取所需數據，而非在一張表中堆積大量冗余信息。

拆分大表（垂直拆分和水平拆分）：

• 垂直拆分：當一張表中字段過多，且部分字段關聯性不強時，可以將表按照字段的業務邏輯進行垂直拆分，形成多張表，降低單張表的復雜度，便于數據管理和查詢優化。例如，將用戶表中基本信息和用戶詳細信息（如收貨地址等經常變動且查詢頻率相對低的字段）拆分成兩張表。
• 水平拆分：針對數據量非常大的表，按照一定規則（如按照時間范圍、地域等）將數據拆分到多個結構相同的表中，分散數據存儲壓力，提高查詢性能。比如電商系統中訂單表數據量巨大，可按年份將訂單數據拆分到不同的表中

3. 優化數據庫設計

• 遵循數據庫范式（一般到第三范式）：
  合理設計數據庫表之間的關系，減少數據冗余和異常情況（如插入異常、刪除異常等）。但在實際應用中，有時為了性能等因素可能會適當反范式化，需要在數據一致性和查詢性能之間做權衡。
• 使用合適的存儲引擎（針對 MySQL 等支持多種引擎的數據庫）：
  不同的存儲引擎有不同的特點，例如 MySQL 中 InnoDB 支持事務、行級鎖和外鍵等，適合對數據一致性、并發訪問要求高的應用場景；MyISAM 則查詢速度較快，適合以讀操作為主、對事務要求不高的場景。根據業務需求選擇合適的存儲引擎能優化數據庫整體性能。

4. 優化數據操作語句

• 批量操作代替循環操作：
  當需要插入、更新或刪除多條數據時，盡量使用批量操作語句。比如批量插入用戶信息，使用 INSERT INTO users (username, password) VALUES (...), (...), (...); 形式（一次插入多條記錄），比循環多次執行單條插入語句效率更高，可減少數據庫交互次數。
• 合理使用事務：
  對于一組相關的數據庫操作（如銀行轉賬涉及的扣款和收款操作），將它們放在一個事務中，保證數據的一致性和完整性。但要注意事務的范圍不宜過大，避免長時間占用數據庫資源，影響并發性能，并且要根據業務需求合理設置事務的隔離級別。

5. 優化數據庫配置

• 調整緩存相關參數：
  數據庫通常有各種緩存機制，如查詢緩存（部分數據庫支持）、緩沖池等，適當調整緩存相關參數，如緩存大小、緩存過期時間等，可以提高數據訪問的命中率，減少磁盤 I/O，提升性能。
• 優化數據庫連接池配置：
  合理配置連接池的參數，如最大連接數、最小連接數、連接超時時間等。確保在高并發情況下有足夠的連接可用，同時避免創建過多不必要的連接浪費資源，保證數據庫的穩定運行和高效性能。

6. 分析執行計劃

使用數據庫自帶的解釋工具（如 MySQL 的 EXPLAIN）：
通過 EXPLAIN 命令可以查看 SQL 語句的執行計劃，了解數據庫是如何查詢數據的，包括使用了哪些索引、表的連接順序、掃描的行數等關鍵信息，從而發現潛在的性能瓶頸，針對性地進行優化。例如，查看一條查詢語句的執行計劃：

EXPLAIN SELECT * FROM users WHERE username LIKE '%test%';

根據執行計劃反饋的結果，判斷是否需要添加索引或者調整查詢條件等優化操作。

7. 定期維護數據庫

• 數據清理與歸檔：
  對于不再經常使用的數據，可以進行清理或者歸檔到其他存儲介質（如歷史訂單數據轉移到數據倉庫等），減少主數據庫的數據量，提升查詢等操作的性能。
• 索引重建與優化：
  隨著數據的不斷增刪改，索引可能會出現碎片化等問題，定期對索引進行重建、分析和優化，保證索引的有效性，有助于提高查詢性能。

查看 Explain Plan 執行計劃

使用執行計劃的目的

方便分析一些復雜的語句，是否用到了索引。執行計劃是對SQL執行結果的預期，不完全等同于實際環境運行的效果。
查看表的索引：show index from emp;

Mysql 使用explain一條SQL語句，來查看執行計劃，需要關注的信息如下：

• id：SQL中被獨立處理部分，它們實際執行的優先級，越小越先執行。相同 id 的部分通常屬于同一個查詢層級或者操作單元。
• select_type：SQL類型
  • SIMPLE：一個單一的 SELECT 語句直接從表中獲取數據
  • PRIMARY：當查詢語句中包含子查詢或者 UNION 等復雜結構時，最外層的主查詢對應的 select_type 取值為 PRIMARY。用以區分主層和外層的部分。
  • SUBQUERY：用于表示在 SELECT、WHERE、HAVING 等子句中出現的子查詢，且該子查詢不依賴于外層查詢的結果，是獨立執行的
  • DERIVED：當子查詢出現在 FROM 子句中，并且這個子查詢相當于生成了一個臨時的派生表（Derived Table）時，該子查詢對應的 select_type 取值為 DERIVED
  • UNION：如果查詢語句中使用了 UNION 操作符將多個 SELECT 語句聯合起來獲取數據，除了第一個 SELECT 語句對應的 select_type 取值為 PRIMARY（因為它是整個聯合查詢的主體外層部分）之外，其余通過 UNION 連接的 SELECT 語句對應的 select_type 取值就是 UNION
  • UNION RESULT：用于表示對 UNION 操作所得到的結果集進行合并、去重等處理的操作對應的 select_type 取值。
  • MATERIALIZED：當子查詢被物化（Materialized）時，也就是子查詢的結果被提前計算出來并存儲為一個臨時的物化結果集，供外層查詢多次使用，此時該子查詢對應的 select_type 取值為 MATERIALIZED。
• table：別名
• type：表示查詢的方式。
  • ALL：遍歷整張表的每一條記錄來查找滿足條件的數據（這是一種效率相對較低的訪問類型，意味著數據庫需要對表進行全表掃描）
  • const：表示查詢索引字段，并且表中最多只有一行匹配
  • eq_ref | ref| ref_or_null：表示被關聯表的 where 關聯字段的類型：1對1 | 1對多 | 1對多并允許為null
  • index_merge（常見于 MySQL 等數據庫）：根據多個索引查詢后合并得到的結果
  • unique_subquery | index_subquery：常出現在子查詢中，并且子查詢返回的結果是基于（唯一索引 | 普通索引）或者主鍵的單一值，作為外層查詢的條件使用
  • range：用于表示通過索引進行范圍查找的情況，常見的如使用 BETWEEN、>、<、>=、<= 等運算符限定了一個范圍條件來訪問表中的數據
  • fulltext|system（僅 MySQL有）：根據articles 全文檢索數據（mysql 需要開啟全文檢索功能）| 對數據庫系統表查詢
• possible_Keys：顯示在當前查詢中，數據庫認為可以使用的所有潛在的索引列表。（可以了解是否存在被數據庫忽略但可能有助于優化查詢的索引）
• key：指出當前查詢實際使用的索引名稱，如果沒有使用任何索引，則顯示為 NULL。
• rows：預估的在執行當前操作時需要掃描或者處理的數據行數
• key_len：表示當前查詢使用的索引的長度，單位通常是字節。它可以反映出索引被使用的具體情況，比如對于復合索引（包含多個列的索引），通過 key_len 的值可以大致判斷出是索引中的哪些列參與了查詢操作，以及每個列使用了多少字節來構建索引等信息，輔助分析索引利用是否充分、合理。
• index_type：索引類型
• filtered：表示基于某個條件對數據進行篩選過濾的比例估計值，以百分比的形式呈現。它反映了經過當前查詢涉及的各種條件篩選后，預計符合要求的記錄在表所有可能記錄中所占的大致比例，通過這個值可以評估查詢條件的篩選效果，判斷是否需要優化查詢條件、添加索引等來提高篩選效率，減少不必要的數據處理量。
• Extra：表示額外含義，常見取值：
  • Using index：表示查詢只需要遍歷索引就能獲取到全部所需的數據，無需訪問表中的實際行記錄，這種情況通常出現在查詢的所有列都包含在索引中（即全索引掃描），是一種比較高效的查詢方式。
  • Using where：說明在獲取數據后，還需要通過 WHERE 子句進一步對數據進行篩選，也就是存在額外的篩選條件在數據檢索之后起作用。
  • Using temporary：意味著查詢過程中生成了臨時表來輔助完成查詢操作，臨時表的生成和使用會占用一定的系統資源，并且可能影響查詢性能，若頻繁出現，需要考慮優化查詢以避免使用臨時表。
  • Using filesort：表示在查詢過程中需要對結果進行排序操作，且不能利用索引來完成排序，而是要在內存或磁盤等介質上進行文件排序，這是一種相對耗時的操作，盡量要通過優化查詢邏輯或者創建合適的索引來避免這種情況出現。
  • Range checked for each record（常見于 MySQL）：通常在使用索引進行范圍查找時，對每條記錄都要重新檢查范圍條件是否滿足，這種情況可能會導致一定的性能損耗，需要關注是否可以優化查詢條件或者索引來改善

Oracle 查看執行計劃需要關注的信息（PL\SQL 中 F5）：

查看執行計劃語句：
EXPLAIN PLAN FOR SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
主要需要關注的字段：

• Id：用于標識執行計劃中的各個操作步驟，數字越小一般越先執行
• Operation：指明了具體的操作類型，如 TABLE ACCESS FULL 表示全表掃描，INDEX RANGE SCAN 表示索引范圍掃描，NESTED LOOPS 表示采用嵌套循環的方式進行表連接等
• Name：顯示了操作涉及的表名或者索引名
• Bytes：預估每個操作步驟涉及的數據字節數，可輔助了解數據量大小對性能的影響等情況
• cost：操作的成本估計值，是 Oracle 中綜合考慮多種因素（如 CPU 使用率、I/O 操作等）來衡量操作復雜度和性能開銷的一個指標
• Time：預估的操作執行所需的時間范圍
• Rows：預估的每個操作步驟涉及的數據行數