一、索引優化
  1. 合理設計索引
  • 主鍵索引：InnoDB表必須有主鍵，建議使用自增ID（避免UUID導致的索引碎片）。
  • 聯合索引：遵循最左前綴原則，將篩選條件中高頻字段放在左側。
  • 避免冗餘索引：如已存在(a,b)，無需再創建(a)。
  • 覆蓋索引：查詢字段包含在索引中（避免回表查詢），例如：
CREATE INDEX idx_name_age ON user(name, age);
SELECT name, age FROM user WHERE name = '張三'; -- 使用覆蓋索引
  1. 索引失效場景
  • 避免在索引字段上使用函數或運算：
-- 錯誤：索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2023;
-- 正確：使用範圍查詢
SELECT * FROM user WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
  • 避免OR、LIKE '%xxx'、NULL判斷導致索引失效。
二、查詢語句優化
  1. 減少全表掃描
  • 對大表查詢必須加索引條件，避免SELECT *：
-- 錯誤：全表掃描+冗餘字段
SELECT * FROM order WHERE status = 1;
-- 正確：索引+按需字段
SELECT id, order_no FROM order WHERE status = 1; -- 假設status有索引
  1. 優化子查詢與關聯查詢
  • MySQL 8.0優化了子查詢，但複雜子查詢仍建議用JOIN替代：
-- 子查詢（效率較低）
SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount > 1000);
-- JOIN優化
SELECT u.* FROM user u 
JOIN order o ON u.id = o.user_id 
WHERE o.amount > 1000;
  1. 分頁查詢優化
  • 大偏移量分頁（如LIMIT 100000, 10）效率低，可通過主鍵或索引優化：
-- 低效
SELECT * FROM log ORDER BY id LIMIT 100000, 10;
-- 高效（利用主鍵有序性）
SELECT * FROM log WHERE id > 100000 ORDER BY id LIMIT 10;
三、數據庫配置優化
  1. 內存配置
  • 調整innodb_buffer_pool_size（建議設為物理內存的50%-70%）：
innodb_buffer_pool_size = 16G
  • 增大join_buffer_size和sort_buffer_size（按需調整，避免過大）。
  1. 併發與鎖優化
  • 開啓innodb_optimize_fulltext_only優化全文索引。
  • 避免長事務，減少行鎖競爭：
-- 優化前：長事務持有鎖
START TRANSACTION;
UPDATE user SET balance = balance - 100 WHERE id = 1;
-- 業務邏輯處理（耗時）
COMMIT;
-- 優化後：縮短事務
UPDATE user SET balance = balance - 100 WHERE id = 1;
四、項目最佳實踐
  1. SQL規範
  • 使用預處理語句（防止SQL風險+提升性能）：
// Java示例
String sql = "SELECT * FROM user WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userId);
  • 避免SELECT *，明確字段列表。
  1. 分庫分表
  • 大表（千萬級以上）採用分表，例如按時間或用户ID哈希分表：
-- 按年月分表
CREATE TABLE order_202301 (...);
CREATE TABLE order_202302 (...);
  1. 慢查詢監控
  • 開啓慢查詢日誌：
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 記錄超過1秒的查詢
  • 使用EXPLAIN ANALYZE（MySQL 8.0+）分析執行計劃：
EXPLAIN ANALYZE SELECT * FROM user WHERE name = '張三';
  1. 讀寫分離
  • 主庫寫入，從庫讀取，利用MySQL 8.0的REPLICATION機制。
五、其他優化技巧
  • 批量操作：使用INSERT ... VALUES (...), (...), (...)替代循環插入。
  • 避免NULL值：索引字段儘量設為NOT NULL，用默認值替代。
  • 定期優化表：OPTIMIZE TABLE整理碎片（適用於InnoDB）。