目錄

1. SQL 語句優

1.1 避免低效查詢

?1.2?索引優化

1.3?分析執行計劃

2. 數據庫配置優化

2.1?核心參數調整

2.2?表結構與存儲引擎

2.3?存儲引擎選擇

3.?事務與鎖優化

3.1?事務控制

3.2?鎖機制優化

3.3 批量操作優化

4. 其他優化手段

4.1?監控與分析工具

4.2?讀寫分離與分庫分表

5. 總結

---

1. SQL 語句優

1.1 避免低效查詢

• 禁止?SELECT *：明確指定所需字段，減少數據傳輸和內存消耗。

• 合理使用?LIMIT：分頁查詢時通過?LIMIT offset, size?限制返回行數，避免全表掃描。

• 優化子查詢：將部分子查詢改寫為?JOIN（尤其是關聯子查詢），例如：

  -- 低效
  SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
  -- 優化后
  SELECT users.* FROM users JOIN orders ON users.id = orders.user_id;

?1.2?索引優化

• 創建有效索引：對?WHERE、JOIN、ORDER BY、GROUP BY?涉及的列建立索引。

• 避免索引失效常見索引失效原因及解決方案：

  • 不在索引列上使用函數或運算（如?WHERE YEAR(create_time) = 2023）。

  • 注意最左前綴原則，聯合索引需按順序使用。

• 覆蓋索引：通過索引直接返回數據，避免回表（如?SELECT id, name FROM users WHERE age=30，若?(age, name)?是聯合索引，則無需查表）。

1.3?分析執行計劃

• 使用?EXPLAIN查看執行計劃EXPLAIN詳解：

  • type 字段：避免?ALL（全表掃描），追求systyem、const、eq_ref、ref、index。

  • Extra 字段：警惕?Using filesort（未利用索引排序）和?Using temporary（臨時表）。

2. 數據庫配置優化

2.1?核心參數調整

• 緩沖池大小：innodb_buffer_pool_size?設置為物理內存的?70%~80%，確保熱點數據在內存中。

• 連接數配置：合理設置?max_connections（默認 151），避免連接耗盡或資源浪費。

• 日志優化：增大?innodb_log_file_size（如 1G）和?innodb_log_buffer_size（如 64M），減少磁盤 I/O。

  innodb_buffer_pool_size = 16G
  max_connections = 500
  innodb_log_file_size = 1G

2.2?表結構與存儲引擎

• 選擇合適的數據類型：如用?INT?而非?VARCHAR?存儲數字，用?DATETIME?替代?TIMESTAMP（需時區時例外）。

• 范式與反范式平衡：適度冗余減少?JOIN，如高頻查詢的用戶名可冗余到訂單表。

• 分區表：對超大數據表按時間或范圍分區，提升查詢效率。

2.3?存儲引擎選擇

• InnoDB：支持事務、行級鎖，適合高并發寫入場景（如訂單系統）。

• MyISAM：僅適合讀多寫少且無需事務的場景（如日志表），因表鎖和崩潰恢復能力差。

3.?事務與鎖優化

3.1?事務控制

• 短事務原則：盡早提交事務，避免長事務占用鎖資源。

• 隔離級別選擇：默認?REPEATABLE READ?平衡一致性與性能，若允許幻讀可降級到?READ COMMITTED。

3.2?鎖機制優化

• 行鎖升級問題：確保?WHERE?條件走索引，否則 InnoDB 退化為表鎖。

• 死鎖預防：

  • 按固定順序訪問多張表（如先 A 后 B）。

  • 批量更新時按主鍵排序。

3.3 批量操作優化

• 分批提交：如每 1000 條數據?COMMIT?一次，減少鎖持有時間。

• 高效導入：用?LOAD DATA INFILE?替代?INSERT，速度提升 10~100 倍。

4. 其他優化手段

4.1?監控與分析工具

• 慢查詢日志：開啟?slow_query_log，捕獲執行時間超過?long_query_time（如 2s）的 SQL。

  slow_query_log = 1
  long_query_time = 2

• Performance Schema：監控鎖、I/O、線程等資源使用情況。

4.2?讀寫分離與分庫分表

• 讀多寫少時，通過主從復制分散讀請求。

• 數據量極大時，使用分庫分表（如 ShardingSphere）。

5. 總結

????????MySQL 調優需結合具體場景，通過?分析慢查詢、調整配置、優化事務邏輯?逐步實施。關鍵點包括：

• SQL 是核心：低效 SQL 可能抵消所有配置優化。

• 索引是雙刃劍：過多索引影響寫入性能。

• 監控驅動優化：持續觀察數據庫狀態，針對性調整。