數據庫優化(底層基礎優化)

數據庫層面的優化是性能“基礎"， 主要包含架構設計、存儲引擎、表結構、索引策略、配置參數等方面考慮。目標是減少資源(CPU、IO和內存)消耗。

架構設計

• 讀寫分離：將"讀操作"和"寫操作"分離到不同的數據庫節點。
  • 主庫(Master)：負責寫操作(INSERT/UPDATE/DELETE)，保證數據一致性。
  • 從庫(Slave)：負責讀操作(SELECT)，通過主從復制(基于binlog)同步主庫數據，
  • 適用場景：讀多寫少的業務(如電商商品詳情頁，新聞網站)，可通過增加從庫數量分攤讀壓力。
• 分庫分表：當單表數據量過大時(超千萬行)，或單庫壓力過高時，需拆分數據。
  • 水平分表(按行拆分)：將表數據按照不同行拆分到多表。(結構相同)。如按照時間拆分。
  • 垂直分表(按列拆分)：將大表中不常用的字段 拆分到子表中(減少單表寬度)
  • 分庫：將多個表拆分到不同數據庫(如按照業務模塊分庫：用戶表、訂單庫)
• 使用緩存緩解數據庫壓力
  • 對高頻訪問且不常變化的數據（如商品分類、熱門文章）通過Redis。Memcached等緩存中間件緩存，減少數據庫的查詢次數。
  • 注意：需要處理緩存一致性(如更新數據庫后同步更新緩存)和緩存穿透/擊穿/雪崩問題。
• 存儲引擎優化：選擇合適的存儲引擎時性能優化的關鍵，需要根據業務場景匹配特性：
  • 優先選擇InnoDB(MySQL 5.5默認)：
    適合需要外鍵、事務、行級鎖、崩潰恢復的場景
    • 優化點：調整innodb_Buffer_pool_size(建議設為物理內存的50%~70%)，減少IO。
    • 開啟innoDB_Flush_log_at_trx_commit(默認)保證事務持久性，若允許少量數據丟失可設置為2.提升性能。
  • MYISAM：適用于 讀多寫少，無需事務(如日志、靜態數據)，優勢索引緩存效率高，但不支持事務和行鎖。崩潰后回復困難。
• 表結構設計優化：合理設計表結構 能夠減少存儲空間，提升查詢效率。核心原則：精簡、合適、平衡范式于反范式。
  • 數據類型選擇：最尋最小夠用，避免大類型存儲小數據
  • 平衡范式和反范式：
    • 范式(1NF~3NF)：減少冗余(如避免同一字段再多表中重復)。但會導致多表聯查增多。
    • 反范式：適量增加冗余，減少JOIN操作，提高性能。
  • 避免過度設計：
    • 不過多適用外鍵(外鍵會增加寫操作開銷)
    • 合理設置表中字段(建議不超過20個)，過多會導致IO和內存消耗。
• 核心索引設計原則：
  • 為WHERE、JOIN、GROUP BY的字段建立索引。
  • 遵循聯合索引"最左匹配原則”
  • 優先分區分度大的字段建立索引。
• 避免索引失效
  • 索引字段適用函數/運算。
  • OR連接無索引字段。
  • 字符串不加引號，導致類型轉換
  • 范圍查詢左邊以%開始
  • NOT IN，!=，<>
• 索引維護
  • 定期刪除冗余索引(如主鍵已索引，舊無需再建立二級索引)。
• 相關配置設置，通過調整MYSQL配置文件(my.config/my.ini)提升性能，
  • 內存相關：
  • innodb_buffer_pool_size：Innodb緩沖池大小(一般為物理內存的50%~70%).
  • key_buffer_size：MyISAM 索引緩存大小（僅用于 MyISAM 表）。
• IO相關
  • innodb_flush_log_at_trx_commit：控制 redo log 刷新策略（1 = 每次提交刷盤，最安全；2 = 每秒刷盤，性能更好）。
  • sync_binlog：控制 binlog 刷新策略（1 = 每次提交刷盤，主從同步更可靠；0 = 由 OS 決定，性能高但有丟失風險）。
• 連接相關：
  • max_connections：最大連接數（默認 151，需根據并發量調整，避免連接數不足）。
  • wait_timeout：空閑連接超時時間（釋放長期閑置的連接，默認 8 小時）。

語句優化

針對單條語句的執行效率，盡可能讓SQL走索引。核心通過EXPLAIN分析執行計劃，優化語法

• 避免全表掃描(type:ALL)，全表掃描(遍歷表中所有行)，

  • 明確查詢條件：WHERE子句必須包含索引字段（或能觸發索引的條件）。
  • 反例：SELECT * FROM user（無WHERE，必全表掃描，除非表極小）。

• 優化查詢字段：

  • 避免SELECT＊：只需要查詢需要的字段，減少數據傳輸和IO，且盡可能避免回表。
  • 減少SELECT DISTINCT：DISTINCT會觸發排序去重，開銷大，可通過索引或業務邏輯避免重復數據。

• 優化JOIN操作：JOIN是多表聯合查詢的核心，低效的JOIN會降低效率。

  • 小表驅動大表：JOIN時，用小數據量作為驅動*(左表)*，減少外層循環。
  • 關聯字段加索引，JOIN的關聯字段(如s.id = b.sid中id和sid)必須建索引，否則會導致全表掃描+嵌套循環。
  • 減少Join表的數量：盡量控制表數量在3張以內。

• 優化子查詢：子查詢(SELECT 中嵌套SELECT)可能產生臨時表，效率較低，建議使用JOIN替代。

• 優化排序和分組

  • 利用索引排序：若排序字段是索引的一部分，可避免額外排序（索引本身有序）。
    例：索引(age, name)，查詢SELECT * FROM user WHERE age > 18 ORDER BY age, name（直接用索引順序，無需排序）。
  • 限制排序數據量：排序前通過WHERE過濾掉無關數據，減少排序行數。
    例：SELECT * FROM user WHERE age > 18 ORDER BY age LIMIT 10（僅排序符合條件的行，且只取前 10）。
  • GROUP BY優化：GROUP BY會先排序再分組，可通過ORDER BY NULL禁用排序（若無需分組后排序）：
    SELECT age, COUNT(*) FROM user GROUP BY age ORDER BY NULL 。

• 分頁查詢優化：大分頁(如limit 100000,10)會掃描大量無用數據，

  • 基于主鍵分頁：利用主鍵有序性，通過WHERE定位起始位置：
    優化前：SELECT * FROM order LIMIT 100000, 10（掃描 100010 行）
    優化后：SELECT * FROM order WHERE id > 100000 LIMIT 10（僅掃描 10 行，需id是主鍵）
  • 延遲關聯：先查主鍵，再關聯獲取其他字段（減少掃描字段）：
    SELECT o.* FROM order o JOIN (SELECT id FROM order LIMIT 100000, 10) t ON o.id = t.id

• 避免頻繁創建臨時表
  以下操作可能觸發臨時表（內存或磁盤臨時表，開銷大）：

  • GROUP BY、DISTINCT、UNION
  • 子查詢結果作為臨時表
    優化：盡量用JOIN替代子查詢，避免不必要的GROUP BY，或通過tmp_table_size和max_heap_table_size限制內存臨時表大小（超過則轉磁盤）。

• 用EXPLAIN分析執行計劃
  EXPLAIN是 SQL 優化的 “利器”，通過它可查看 SQL 的執行方式（是否走索引、掃描行數等），重點關注：

  • type：訪問類型（從差到好：ALL（全表掃描）→ index（索引掃描）→ range（范圍掃描）→ ref（非唯一索引匹配）→ const（主鍵匹配））。
  • key：實際使用的索引（NULL表示未走索引）。
  • rows：預估掃描的行數（越小越好）。
  • Extra：額外信息（如Using filesort（需排序）、Using temporary（用臨時表）、Using index（覆蓋索引，無需回表））。