一、基礎優化（低成本、立竿見影）

這是優化的第一步，無需複雜操作，優先排查這些點：

1. 優化 SQL 語句本身

  • 避免全表掃描：杜絕 

SELECT *

  • ，只查詢需要的字段（減少數據傳輸和內存佔用）； 示例：




    sql 



-- 差：全表掃描+查詢所有字段
SELECT * FROM user WHERE name LIKE '%張三%';
-- 優：只查需要的字段+儘量避免左模糊（若必須模糊查詢，可考慮全文索引）
SELECT id, name, phone FROM user WHERE name LIKE '張三%';
  • 優化 WHERE 條件：避免在字段上做函數 / 運算（會導致索引失效）； 示例：




    sql 



-- 差：id字段做運算，索引失效
SELECT * FROM order WHERE id + 1 = 100;
-- 優：調整條件寫法，利用索引
SELECT * FROM order WHERE id = 99;
  • 減少子查詢，改用 JOIN：子查詢會創建臨時表，效率低，JOIN 更高效；
  • LIMIT 限制結果集：查詢大量數據時（如分頁），必須加 

LIMIT

  • ，避免一次性加載過多數據。

2. 檢查並修復慢查詢

  • 開啓 MySQL 慢查詢日誌：記錄執行時間超過閾值（如 1 秒）的 SQL，定位性能瓶頸； 開啓命令（臨時生效，重啓失效）：




    sql 



SET GLOBAL slow_query_log = ON; -- 開啓慢查詢日誌
SET GLOBAL long_query_time = 1; -- 執行時間超過1秒的SQL記錄
  • 用 

EXPLAIN

  •  分析 SQL 執行計劃：查看是否使用索引、是否全表掃描、關聯方式等； 示例：




    sql 



-- 分析這條SQL的執行計劃
EXPLAIN SELECT id, name FROM user WHERE age > 20;
  • 重點看 

type

  •  字段（越接近 

const/range

  •  越好，

ALL

  •  代表全表掃描）、

key

二、核心優化（索引優化，性能提升關鍵）

索引是提升查詢速度的核心，但不是越多越好，需精準設計：

1. 創建合適的索引

  • 優先給 WHERE/ORDER BY/GROUP BY 字段建索引：比如查詢條件中的 

id

  • 、

age

  • ，排序的 

create_time

  • ；
  • 選擇合適的索引類型：
  • 主鍵索引（PRIMARY KEY）：必須建，InnoDB 默認按主鍵聚簇存儲；
  • 普通索引（INDEX）：單字段索引，適用於簡單查詢；
  • 聯合索引（複合索引）：多字段組合索引，需遵循 “最左前綴原則”；   示例：創建 

idx_age_create_time (age, create_time)

  •  聯合索引，能匹配 

age

  • 、

age+create_time

  •  查詢，但不匹配 

create_time

  • 唯一索引（UNIQUE）：適用於唯一字段（如手機號、郵箱），既能加速查詢，又能保證數據唯一性。

2. 避免索引失效的常見場景

  • 字段為 NULL：索引不存儲 NULL 值，儘量給字段設默認值（如空字符串）；
  • 聯合索引不滿足最左前綴；
  • 使用 

OR

  • 模糊查詢用 

%xxx

  • （左模糊），索引失效（可改用全文索引）。

3. 定期維護索引

  • 刪無用索引：重複索引、未使用的索引會增加插入 / 更新的開銷；
  • 重建碎片索引：頻繁刪除 / 更新數據會導致索引碎片，可通過 

OPTIMIZE TABLE

  •  重建； 示例：




    sql 



OPTIMIZE TABLE user; -- 優化user表的索引碎片

三、進階優化（配置 / 架構層面，適配高併發）

若基礎和索引優化後仍不夠，需從配置或架構層面優化：

1. MySQL 配置優化（修改 my.cnf/my.ini）

  • 調整緩存參數：

innodb_buffer_pool_size

  • ：InnoDB 緩存池大小，建議設為物理內存的 50%-70%（核心參數，緩存表數據和索引，減少磁盤 IO）；

query_cache_size

  • ：查詢緩存（8.0 已移除，低版本可適當設置）；
  • 調整連接參數：

max_connections

  • ：最大連接數，避免因連接數不足導致請求排隊；

wait_timeout

  • ：空閒連接超時時間，釋放無用連接。

2. 架構層面優化

  • 讀寫分離：主庫寫、從庫讀，分散數據庫壓力（適用於讀多寫少場景）；
  • 分庫分表：數據量過大（如單表千萬級）時，按業務拆分（如按用户 ID 分表、按時間分表）；
  • 緩存優化：用 Redis 緩存高頻查詢結果（如熱門商品、用户信息），減少數據庫查詢次數；
  • 使用 SSD 硬盤：大幅降低磁盤 IO 延遲，提升數據讀取速度。

四、避坑提醒

  1. 不要盲目加索引：索引會降低插入 / 更新 / 刪除的速度，一張表索引建議不超過 5 個；
  2. 小表無需優化：數據量小於 1 萬行的表，全表掃描可能比索引查詢更快；
  3. 避免大事務：大事務會佔用鎖資源，導致其他查詢阻塞，拆分小事務。

總結

  1. 基礎優化：先優化 SQL 語句（避免全表掃描、用 EXPLAIN 分析），開啓慢查詢日誌定位瓶頸；
  2. 核心優化：給查詢 / 排序字段建精準索引，遵循最左前綴原則，定期維護索引；
  3. 進階優化：調整 MySQL 緩存 / 連接配置，高併發場景可做讀寫分離、分庫分表或加緩存。