MySQL 插入/更新/刪除操作優化實戰指南

MySQL 插入/更新/刪除操作優化實戰指南

一、高效插入策略

1. 批量插入語法

INSERT INTO target_table (col1, col2)
SELECT col1, col2 FROM source_table WHERE condition;

適用於表間數據遷移、歷史數據歸檔等場景。

2. 實戰演示：表間數據遷移

-- 建表
CREATE TABLE person (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(40) NOT NULL DEFAULT '',
  age INT NOT NULL DEFAULT 0,
  info CHAR(50) NULL,
  PRIMARY KEY (id)
);

CREATE TABLE person_old LIKE person;

-- 源表插入測試數據
INSERT INTO person_old VALUES (11,'Harry',20,'student'),(12,'Beckham',31,'police');

-- 遷移數據 (顯式指定列，避免自增主鍵衝突)
INSERT INTO person(id,name,age,info) SELECT id,name,age,info FROM person_old;

3. 處理重複鍵衝突的三種方案 (MySQL 特有)

-- IGNORE 示例：第二次執行不報錯，不插入重複數據
INSERT IGNORE INTO person(id,name,age,info) SELECT id,name,age,info FROM person_old;

4. MySQL vs SQL Server 插入差異對比

• 自增列處理：MySQL 允許顯式插入 NULL 或指定值觸發自增，無需 SET IDENTITY_INSERT ON
• 唯一索引 NULL 語義：MySQL 允許唯一索引列有多個 NULL，SQL Server 僅允許一個

二、更新操作優化

UPDATE person SET info='police' WHERE id BETWEEN 14 AND 17;

核心建議：

• 優先使用主鍵/唯一索引定位行
• 避免全表掃描更新，必要時分批提交大事務
• 關注 rows_affected 確認影響範圍

三、刪除操作優化

1. 範圍刪除

DELETE FROM person WHERE id BETWEEN 14 AND 17;

2. 全表清空：TRUNCATE vs DELETE

-- MyISAM 測試 30 行數據
DELETE FROM person;      -- 較慢
TRUNCATE TABLE person;   -- 瞬間完成

3. TRUNCATE 後自增值驗證

SHOW TABLE STATUS LIKE 'person';

確認 Auto_increment 重置為 1。

四、最佳實踐總結

1. 批量操作優於單條：多行 INSERT、LOAD DATA INFILE 大幅降低網絡往返
2. 選擇正確的重複鍵策略：IGNORE 適合幂等導入，ON DUPLICATE KEY UPDATE 適合計數聚合
3. 大量刪除用 TRUNCATE：全表清空首選，範圍刪除考慮分批 DELETE 避免長事務鎖表
4. 監控影響行數：應用層校驗 affected_rows 防止誤操作