一、SQL語句優化?

1. ?批量插入代替單條插入?

• ?單條插入會頻繁觸發事務提交和日志寫入，效率極低。
• ?批量插入通過合并多條數據為一條SQL語句，減少網絡傳輸和SQL解析開銷。

-- 低效寫法：逐條插入
INSERT INTO table (col1, col2) VALUES (1, 'a');
INSERT INTO table (col1, col2) VALUES (2, 'b');-- 高效寫法：批量插入
INSERT INTO table (col1, col2) VALUES 
(1, 'a'), (2, 'b'), (3, 'c'), ...;

• ?建議單次插入數據量?：控制在?500~2000?行（避免超出?max_allowed_packet）。

2. ?禁用自動提交（Autocommit）??

• 默認情況下，每條插入都會自動提交事務，導致頻繁的磁盤I/O。
• ?手動控制事務，將多個插入操作合并為一個事務提交：

START TRANSACTION;
INSERT INTO table ...;
INSERT INTO table ...;
...
COMMIT;

• ?注意?：事務過大可能導致?undo log?膨脹，需根據內存調整事務批次（如每?1萬~10萬?行提交一次）。

3. ?**使用?LOAD DATA INFILE**?

• 從文件直接導入數據，比?INSERT?快 ?20倍以上，跳過了SQL解析和事務開銷。

LOAD DATA LOCAL INFILE '/path/data.csv' 
INTO TABLE table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

• ?適用場景?：從CSV或文本文件導入數據。

4. ?禁用索引和約束?

• 插入前禁用索引（尤其是唯一索引和全文索引），插入完成后重建：

-- 禁用索引
ALTER TABLE table DISABLE KEYS;
-- 插入數據...
-- 重建索引
ALTER TABLE table ENABLE KEYS;

• ?禁用外鍵檢查?：

SET FOREIGN_KEY_CHECKS = 0;
-- 插入數據...
SET FOREIGN_KEY_CHECKS = 1;

---

?二、參數配置優化?

1. ?InnoDB引擎參數調整?

• ?**innodb_flush_log_at_trx_commit**?：
  • 默認值為?1（每次事務提交都刷盤），改為?0?或?2?可減少磁盤I/O。
  • 0：每秒刷盤（可能丟失1秒數據）。
  • 2：提交時寫入OS緩存，不強制刷盤。
• ?**innodb_buffer_pool_size**?：
  • 增大緩沖池大小（通常設為物理內存的?70%~80%），提高數據緩存命中率。
• ?**innodb_autoinc_lock_mode**?：
  • 設為?2（交叉模式），減少自增鎖競爭（需MySQL 8.0+）。

2. ?調整網絡和包大小?

• ?**max_allowed_packet**?：
  • 增大允許的數據包大小（默認?4MB），避免批量插入被截斷。
• ?**bulk_insert_buffer_size**?：
  • 增大批量插入緩沖區大小（默認?8MB）。

3. ?其他參數?

• ?**back_log**?：增大連接隊列長度，應對高并發插入。
• ?**innodb_doublewrite**?：關閉雙寫機制（犧牲數據安全換取性能）。

---

?三、存儲引擎選擇?

1. ?MyISAM引擎?

• ?優點?：插入速度比InnoDB快（無事務和行級鎖開銷）。
• ?缺點?：不支持事務和崩潰恢復，適合只讀或允許數據丟失的場景。

2. ?InnoDB引擎?

• ?優點?：支持事務和行級鎖，適合高并發寫入。
• ?優化技巧?：
  • 使用?innodb_file_per_table?避免表空間碎片。
  • 主鍵使用自增整數（避免隨機寫入導致的頁分裂）。

---

?四、硬件和架構優化?

1. ?使用SSD硬盤?

• 替換機械硬盤為SSD，提升I/O吞吐量。

2. ?分庫分表?

• 將單表拆分為多個子表（如按時間或ID范圍），減少單表壓力。
• 使用中間件（如ShardingSphere）或分區表（PARTITION BY）。

3. ?讀寫分離?

• 主庫負責寫入，從庫負責查詢，降低主庫壓力。

4. ?異步寫入?

• 將數據先寫入消息隊列（如Kafka），再由消費者批量插入數據庫。

---

?五、代碼層面優化?

1. ?多線程并行插入?

• 將數據分片，通過多線程并發插入不同分片。
• ?注意?：需確保線程間無主鍵沖突。

2. ?預處理語句（Prepared Statements）??

• 復用SQL模板，減少解析開銷：

// Java示例
String sql = "INSERT INTO table (col1, col2) VALUES (?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (Data data : list) {ps.setInt(1, data.getCol1());ps.setString(2, data.getCol2());ps.addBatch();
}
ps.executeBatch();

---

?六、性能對比示例?

優化方法	插入10萬條耗時（秒）
逐條插入（默認）	120
批量插入（1000行/次）	5
LOAD DATA INFILE	1.5

---

?總結?

• ?核心思路?：減少磁盤I/O、降低鎖競爭、合并操作。
• ?推薦步驟?：
  1. 優先使用?LOAD DATA INFILE?或批量插入。
  2. 調整事務提交策略和InnoDB參數。
  3. 優化表結構（禁用非必要索引）。
  4. 根據硬件和場景選擇存儲引擎。
  5. 在架構層面分庫分表或異步寫入。

通過上述方法，可在MySQL中實現每秒數萬甚至數十萬條的高效插入。