化與踩坑實(shí)錄)
INSERT大概是所有 SQL 語句里長得最老實(shí)的一條看起來就是“往表里塞數(shù)據(jù)”很多同學(xué)寫了兩三年 SQL用到 90% 的場景也就一句話INSERT INTO 表名 VALUES (...)?？烧娴搅司€上環(huán)境你會發(fā)現(xiàn)這條“簡單”語句背后全是坑主鍵沖突、字符集報錯、批量插入慢到懷疑人生、自增 ID 莫名其妙瘋狂跳號、并發(fā)一上來就鎖等待超時。這篇文章把我這幾年在 MySQL 插入數(shù)據(jù)這件事上踩過的坑和總結(jié)出來的經(jīng)驗(yàn)完整過一遍從最基礎(chǔ)的語法拆到類型轉(zhuǎn)換、約束處理再到ON DUPLICATE KEY UPDATE、批量插入優(yōu)化和報錯排查。適合剛接觸 MySQL 的初學(xué)者也適合寫了兩三年代碼但沒認(rèn)真摳過細(xì)節(jié)的開發(fā)者對照著查能幫你少走很多彎路。1. 先看最基礎(chǔ)的 INSERT 語法兩種核心寫法怎么選1.1 完整語法與兩種基礎(chǔ)寫法MySQL 的 INSERT 語句標(biāo)準(zhǔn)語法可以簡單拆成這么幾塊INSERT [IGNORE] INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);這是最常見也最推薦的一種寫法顯式列出目標(biāo)列再給對應(yīng)的值。除了這種還有兩種變體也經(jīng)常遇到-- 省略列名按表結(jié)構(gòu)的列順序給值 INSERT INTO 表名 VALUES (值1, 值2, ...); -- 使用 SET 方式更像 UPDATE 的寫法 INSERT INTO 表名 SET 列名1 值1, 列名2 值2;SET寫法其實(shí)在很多 ORM 框架底層會用到比如某些早期版本的框架在生成插入語句時就會轉(zhuǎn)成這種形式。它的好處是列名和值一一對應(yīng)寫起來直觀缺點(diǎn)是同一張表插入多條數(shù)據(jù)時要寫多條語句沒法合并成一條多值插入性能上不如VALUES列表方式。多值插入可以寫成這樣INSERT INTO 表名 (列名1, 列名2) VALUES (值1, 值2), (值3, 值4), (值5, 值6);這種一次性插多行的寫法也是后面講批量插入性能優(yōu)化的基礎(chǔ)先把語法記住。這里補(bǔ)充一個容易看花眼的細(xì)節(jié)官方文檔里VALUES也可以寫作VALUE兩者等價。VALUE (1, 2)這種寫法在某些自動生成的腳本里能看到是合法的不用覺得奇怪。1.2 為什么業(yè)務(wù)代碼里建議顯式寫清列名很多初學(xué)者圖省事喜歡寫不帶列名的INSERT INTO 表名 VALUES (...)。這種寫法的前提是值的數(shù)量、順序必須和表結(jié)構(gòu)完全一致包括自增主鍵、默認(rèn)值字段、時間字段一個都不能少順序也不能亂。問題是表結(jié)構(gòu)是會變的。我見過一次線上事故起因就是某個開發(fā)同學(xué)圖省事寫了不帶列名的 INSERT后來 DBA 給表加了一個業(yè)務(wù)字段這條 SQL 直接在線上報錯“Column count doesnt match value count at row 1”接口瞬間 500。排查的時候發(fā)現(xiàn)代碼里幾十處插入語句全是這種寫法一次性全炸。顯式寫出列名至少有三個好處表結(jié)構(gòu)增加字段時老的 INSERT 不受影響新字段有默認(rèn)值就能正常插入。列的物理順序隨便調(diào)整SQL 都不會錯?？勺x性強(qiáng)別人看 SQL 一眼就知道每個值對應(yīng)的含義排查問題省很多時間。所以我個人的習(xí)慣是只要是寫進(jìn)業(yè)務(wù)代碼或腳本里的 INSERT一律顯式列名這一條從來沒有例外。2. 類型轉(zhuǎn)換、約束與自增主鍵插入前的隱藏邏輯2.1 嚴(yán)格模式才是你想要的模式MySQL 在插入數(shù)據(jù)時會對值做隱式類型轉(zhuǎn)換。最經(jīng)典的例子往INT類型的列里插入字符串a(chǎn)bc非嚴(yán)格模式下 MySQL 不會報錯而是把字符串轉(zhuǎn)成0存進(jìn)去插入12abc會轉(zhuǎn)成12。這種自動容錯看起來“方便”實(shí)際是在往數(shù)據(jù)表里埋雷。等到你按數(shù)字查數(shù)據(jù)時發(fā)現(xiàn)一堆臟數(shù)據(jù)根本不知道是哪條語句寫入的。MySQL 5.7 開始默認(rèn)開啟了嚴(yán)格模式核心參數(shù)是STRICT_TRANS_TALLS。在嚴(yán)格模式下插入abc到整數(shù)列會直接報錯Incorrect integer value: abc for column id at row 1。這樣反而好問題在寫入那一刻就暴露出來了。查看當(dāng)前嚴(yán)格模式的命令SELECT sql_mode;比較常見的sql_mode配置會包含這些值ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION。其中NO_ZERO_DATE和NO_ZERO_IN_DATE跟插入關(guān)系很大它們禁止插入0000-00-00這類零日期值。如果你是從老系統(tǒng)遷移數(shù)據(jù)可能會遇到原本存在的零日期在插入新庫時全部報錯的情況這個后面講時間字段時會再提到。2.2 自增主鍵與 last_insert_id() 的正確用法自增主鍵是 InnoDB 表最常見的做法。插入一條數(shù)據(jù)后要拿到這條數(shù)據(jù)生成的主鍵 ID標(biāo)準(zhǔn)做法是調(diào)用LAST_INSERT_ID()INSERT INTO 用戶表 (姓名) VALUES (張三); SELECT LAST_INSERT_ID();兩個容易被忽略的細(xì)節(jié)第一LAST_INSERT_ID()是會話級別的不是全局的也不是表級別的。A 連接插入后拿到 IDB 連接再調(diào)用拿到的是自己會話的值互不影響。如果在一個連接里發(fā)起了大量別的 SQL 之后再調(diào)用拿到的可能就不是你想的那個 ID 了所以要在插入語句之后立刻調(diào)用。第二如果用的是一條多值 INSERT 插入了多行LAST_INSERT_ID()只返回批量插入中產(chǎn)生的第一個 ID不是最后一個。比如一次插入了 5 行自增 ID 從 100 到 104調(diào)用返回的是 100。如果你需要拿到一整批的 ID方案有這么幾種插入后用業(yè)務(wù)唯一鍵回查這批記錄。插入前在應(yīng)用層生成 ID比如雪花算法然后把 ID 顯式插入。逐條插入逐條獲取但性能較差不推薦大批量場景用。另外注意如果你在 INSERT 時顯式指定了自增列的值比如INSERT INTO 表 (id, name) VALUES (200, a)那么LAST_INSERT_ID()返回的仍然是上一條自動生成的值不會變成 200除非你再插入一條真正由 MySQL 自增生成的記錄。2.3 約束在插入時怎么發(fā)力INSERT 不光是往表里寫數(shù)據(jù)它還要過一遍表上所有約束任何一環(huán)過不去整條語句就報錯。最常見的幾類NOT NULL約束字段沒有默認(rèn)值且沒傳值報Field xxx doesnt have a default value。DEFAULT約束字段有默認(rèn)值不傳時自動用默認(rèn)值。UNIQUE唯一約束插入重復(fù)值報Duplicate entry xxx for key yyy。CHECK約束MySQL 8.0.16 之后 CHECK 約束才真正生效插入不符合條件的值會報錯。FOREIGN KEY外鍵約束插入子表記錄時外鍵列的值在主表不存在報Cannot add or update a child row: a foreign key constraint fails。理解約束的檢查時機(jī)很重要。一條 INSERT 語句要插入多行時如果第 3 行違反了唯一約束整條語句會報錯而在默認(rèn)的自動提交模式下已經(jīng)插入的前兩行不會自動回滾表里會留下部分?jǐn)?shù)據(jù)。如果你希望“要么全成功要么全失敗”必須手動開啟事務(wù)把多條 INSERT 包在BEGIN和COMMIT之間出錯就ROLLBACK。3. 進(jìn)階寫法怎么選INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO3.1 INSERT IGNORE一次性導(dǎo)入去重很方便但要看清它會吞掉什么語法就是在 INSERT 后面加一個IGNOREINSERT IGNORE INTO 用戶表 (id, 郵箱) VALUES (1, testexample.com);IGNORE的本意是插入時遇到錯誤就跳過這條不報錯只給一個 warning。最常見的用途是做數(shù)據(jù)初始化或外部數(shù)據(jù)導(dǎo)入時遇到重復(fù)唯一鍵自動跳過保證后面數(shù)據(jù)繼續(xù)插入。但這里有個容易被忽略的坑IGNORE吞掉的不只是唯一鍵沖突還包括NOT NULL約束、Data too long、無效日期等很多錯誤。比如某個字段是NOT NULL你插入NULL普通 INSERT 會報錯INSERT IGNORE會靜默跳過然后字段被寫成默認(rèn)值或空字符串。數(shù)據(jù)缺失了程序也不知道后面查數(shù)據(jù)時才發(fā)現(xiàn)少了一堆記錄。我的建議是INSERT IGNORE只用在兩種場景一是外部導(dǎo)入時明確知道要按唯一鍵去重二是初始化字典表這類基礎(chǔ)數(shù)據(jù)。寫進(jìn)業(yè)務(wù)代碼前一定要想清楚如果這條插入因?yàn)閿?shù)據(jù)質(zhì)量被吞掉后續(xù)邏輯會不會出問題。3.2 ON DUPLICATE KEY UPDATE存在就更新但別忽視自增消耗這個語法是最常用的“存在即更新不存在即插入”方案俗稱 upsertINSERT INTO 計數(shù)器表 (id, 計數(shù)) VALUES (1, 1) ON DUPLICATE KEY UPDATE 計數(shù) 計數(shù) 1;在 MySQL 8.0.20 之前的版本里想在更新時引用本次準(zhǔn)備插入的值要借助VALUES()函數(shù)INSERT INTO 計數(shù)器表 (id, 計數(shù)) VALUES (1, 1) ON DUPLICATE KEY UPDATE 計數(shù) VALUES(計數(shù));8.0.20 開始官方把VALUES()這種寫法標(biāo)記為廢棄推薦使用行別名方式INSERT INTO 計數(shù)器表 (id, 計數(shù)) VALUES (1, 1) AS new ON DUPLICATE KEY UPDATE 計數(shù) new.計數(shù);用ON DUPLICATE KEY UPDATE時影響行數(shù)有三種含義插入成功返回 1更新成功返回 2數(shù)據(jù)沒任何變化返回 0。如果你的應(yīng)用依賴影響行數(shù)來判斷是插入還是更新這個細(xì)節(jié)要特別小心。還有個大坑是自增 ID 的消耗。ON DUPLICATE KEY UPDATE觸發(fā)更新時MySQL 在嘗試插入的那一刻就已經(jīng)從自增計數(shù)器里取走了一個 ID即使最終沒有真正插入新記錄ID 也會被“浪費(fèi)”。長期大量執(zhí)行 upsert 的表自增 ID 會漲得飛快甚至出現(xiàn)跳號幾百上千的情況。如果業(yè)務(wù)對主鍵連續(xù)性有要求這種方案就不太合適了。3.3 REPLACE INTO先刪再插代價比你想的大REPLACE INTO的語法和 INSERT 很像REPLACE INTO 用戶表 (id, 郵箱) VALUES (1, newexample.com);遇到主鍵或唯一鍵沖突時REPLACE不是更新而是先把舊記錄 DELETE 掉再插入一條新記錄。聽上去“無非是更新”實(shí)際代價很大刪除再插入意味著每次都要分配一個新的自增 ID自增消耗比ON DUPLICATE KEY UPDATE更嚴(yán)重。如果舊行被其他表的外鍵引用DELETE 可能會觸發(fā)外鍵失敗或級聯(lián)刪除。只想要更新某一個字段時其他字段也必須全量給出來否則會被重置為默認(rèn)值。如果表上有觸發(fā)器REPLACE會觸發(fā) DELETE 和 INSERT 兩類觸發(fā)器行為比預(yù)期復(fù)雜很多。所以我在工作里幾乎不用REPLACE INTO只有一種場景會考慮明確要重建整行數(shù)據(jù)且能接受主鍵 ID 變化和觸發(fā)器副作用。絕大多數(shù)“存在則更新”的需求ON DUPLICATE KEY UPDATE都更可控。4. 大批量插入時性能為什么差以及怎么改4.1 多行 VALUES 比單條插入快在哪插入性能最直觀的優(yōu)化辦法把多條單行 INSERT 合并成一條多行 INSERT。-- 不推薦循環(huán) 1000 次 INSERT INTO 表 (a, b) VALUES (1, 2); INSERT INTO 表 (a, b) VALUES (3, 4); -- 推薦一條語句插多行 INSERT INTO 表 (a, b) VALUES (1, 2), (3, 4);快的原因有幾點(diǎn)SQL 解析只做一次網(wǎng)絡(luò)往返從 N 次變成 1 次InnoDB 寫 binlog 和 redo log 的 IO 次數(shù)也大幅減少。尤其是網(wǎng)絡(luò)開銷以前每次 INSERT 都有一次客戶端和 MySQL 服務(wù)器之間的往返合并后直接省掉了絕大部分 RTT。所以在代碼里寫循環(huán)插入等于把最容易省掉的成本全扛在自己身上。需要注意單條 INSERT 語句也不是越大越好。MySQL 有max_allowed_packet參數(shù)限制單次包大小默認(rèn)值常見 64MB 或 128MB。如果一次插入的數(shù)據(jù)量太大會直接報錯Packet too large。建議單條多行 INSERT 控制在幾百到一兩千行之間既能享受合并帶來的性能收益又不會讓單條語句太大太笨重。4.2 JDBC 批量插入與 rewriteBatchedStatements 參數(shù)Java 后端最常見的插入場景是 MyBatis / JDBC 批量寫入。很多人寫代碼時用的是foreach拼接多行 VALUES或者是 JDBC 的addBatch()/executeBatch()。如果你的應(yīng)用用的是 JDBC 批量接口一定要確認(rèn)連接串里有沒有加上這么個參數(shù)jdbc:mysql://host:port/db?rewriteBatchedStatementstrue沒有這個參數(shù)MySQL 驅(qū)動會把executeBatch()里的每一條 SQL 都單獨(dú)發(fā)給服務(wù)器執(zhí)行所謂的“批量插入”實(shí)際上還是逐條插入性能提升很有限。加上rewriteBatchedStatementstrue之后驅(qū)動會自動把一批 INSERT 重寫成一條多行 VALUES 語句再發(fā)給服務(wù)器這時才是真正的批量插入。我遇到過很多次“一樣用 batch為什么性能天差地別”的排查最后 90% 都是這個參數(shù)沒開。還有一點(diǎn)批量插入最好確保每批次的數(shù)據(jù)量是可控的不要一次性addBatch幾百萬條還在繼續(xù)往內(nèi)存里塞建議每 1000 到 2000 條就executeBatch一次并清空 batch避免內(nèi)存撐爆和單條語句過大。4.3 幾個影響插入性能的數(shù)據(jù)庫側(cè)參數(shù)除了 SQL 寫法數(shù)據(jù)庫側(cè)有幾個參數(shù)會直接影響插入速度排查性能問題時可以按順序看innodb_flush_log_at_trx_commit默認(rèn)值 1表示每次事務(wù)提交都要把 redo log flush 到磁盤數(shù)據(jù)安全最好但最慢。如果業(yè)務(wù)能接受斷電或崩潰時丟失最近一小段數(shù)據(jù)可以臨時調(diào)到 2會快很多。生產(chǎn)環(huán)境調(diào)整這個參數(shù)必須經(jīng)過充分評估別把數(shù)據(jù)安全當(dāng)兒戲。innodb_autoinc_lock_mode自增鎖模式。默認(rèn)在 MySQL 5.7 里是 1連續(xù)模式會導(dǎo)致批量插入時其他插入操作被阻塞改成 2交錯模式后并發(fā)插入性能更好但要求 binlog 格式必須是ROW否則主從復(fù)制數(shù)據(jù)會不一致。max_allowed_packet前面提過控制單次最大包大小大批量插入時如果報Packet too large就調(diào)它。這些參數(shù)的名字我建議你記下來因?yàn)楹芏?MySQL 性能排查資料都會提到面試時也經(jīng)常被問。具體調(diào)參之前先看清楚自己用的 MySQL 版本不同版本默認(rèn)行為和參數(shù)取值不完全一樣。5. 插入報錯的常見場景與排查實(shí)錄5.1 先把最常見的報錯整理成一張表我根據(jù)平時排查線上問題的經(jīng)驗(yàn)把 INSERT 最常見的報錯整理成一張速查表按錯誤碼分類遇到問題直接對照著看很方便。錯誤碼常見報錯信息含義解決思路1062Duplicate entry xxx for key yyy唯一鍵或主鍵沖突檢查是否重復(fù)提交或改用 upsert 寫法1366Incorrect string value: \x...字符集不支持當(dāng)前字符字符串字符集改utf8mb4檢查連接字符集1364Field xxx doesnt have a default value非空字段沒有值給字段補(bǔ)值或?yàn)樽侄卧O(shè)置 DEFAULT1406Data too long for column xxx數(shù)據(jù)超過列長度限制檢查字段長度定義或截斷超長內(nèi)容1136Column count doesnt match value count列數(shù)量與值數(shù)量不一致檢查 SQL 中列名和值的數(shù)量1054Unknown column xxx in field list指定的列不存在核對表結(jié)構(gòu)確認(rèn)列名拼寫1146Table xxx doesnt exist表不存在檢查庫名表名是否存在大小寫是否有問題1452Cannot add or update a child row外鍵約束失敗檢查父表是否有對應(yīng)記錄1205Lock wait timeout exceeded鎖等待超時查事務(wù)、鎖持有情況縮短事務(wù)時間1213Deadlock found死鎖檢查事務(wù)加鎖順序必要時增加重試機(jī)制這張表只覆蓋高頻場景實(shí)際工作中還會遇到一些低頻但更隱蔽的問題下面挑幾個詳細(xì)展開。5.2 字符集引發(fā)的插入失敗emoji 插不進(jìn)去這是非常經(jīng)典的坑。往表里插入一個 emoji 或者生僻字?jǐn)?shù)據(jù)庫直接報錯INSERT INTO 用戶表 (昵稱) VALUES (張三); -- ERROR 1366 (HY000): Incorrect string value: \xF0\x9F\x98\x80 for column 昵稱 at row 1原因在于 MySQL 里的utf8其實(shí)最多只能存 3 個字節(jié)而 emoji 是 4 字節(jié)編碼必須用utf8mb4才能存下。很多老項目建表時寫的是utf8字符集本身就不支持 emoji。排查步驟一般是先看表的字符集SHOW CREATE TABLE 用戶表;如果字段是varchar(20) CHARACTER SET utf8就要修改字段字符集ALTER TABLE 用戶表 MODIFY 昵稱 VARCHAR(20) CHARACTER SET utf8mb4;還要確保 MySQL 連接的字符集也是utf8mb4否則改了表也白搭。連接后執(zhí)行SET NAMES utf8mb4;生產(chǎn)環(huán)境更徹底的做法建庫時就統(tǒng)一用utf8mb4連接串上也加上characterEncodingutf8mb4Java 側(cè)寫法是characterEncodingutf8配合服務(wù)端utf8mb4也能正常工作具體看驅(qū)動版本。如果只在某個表字段上用utf8mb4其他表和索引還是utf8兩表關(guān)聯(lián)時會出現(xiàn)字符集不一致導(dǎo)致索引失效的問題這個也要注意。5.3 時間字段的三個隱藏坑時間字段插入報錯沒有那么頻繁但一旦出問題排查起來很繞。第一個坑零日期。0000-00-00這種值在包含NO_ZERO_DATE的嚴(yán)格模式下插入會直接報錯。老系統(tǒng)導(dǎo)出數(shù)據(jù)時經(jīng)常有這種值導(dǎo)入新庫前要么先清洗成合法日期要么臨時調(diào)整sql_mode。第二個坑TIMESTAMP的范圍。TIMESTAMP類型取值范圍是 1970 年到 2038 年如果你插入一個2040-01-01會直接報錯或變成0000-00-00取決于模式。如果業(yè)務(wù)需要存更早或更晚的日期用DATETIME它能存從 1000 年到 9999 年。第三個坑時區(qū)。TIMESTAMP在存儲時會按照當(dāng)前會話時區(qū)轉(zhuǎn)換成 UTC讀取時再轉(zhuǎn)回來。如果應(yīng)用連接串上的時區(qū)和數(shù)據(jù)庫服務(wù)器的時區(qū)不一致插入的時間會比預(yù)期差幾個小時。排查時先看會話時區(qū)SELECT global.time_zone, session.time_zone;遇到時區(qū)問題建議應(yīng)用層統(tǒng)一用同一個時區(qū)寫入一般是東八區(qū)數(shù)據(jù)庫側(cè)也明確設(shè)置time_zone避免“明明插入的是一個時間點(diǎn)查出來卻是另一個時間”這種詭異現(xiàn)象。5.4 并發(fā)插入時的鎖等待與死鎖并發(fā)插入同一張表尤其是同一個唯一鍵附近很容易觸發(fā)鎖等待超時和死鎖。最典型的場景是多個事務(wù)同時準(zhǔn)備插入同一條唯一鍵第一個沒提交第二個插入相同鍵時在等待它的鎖超出時間就報Lock wait timeout exceeded; try restarting transaction。遇到這種情況先看當(dāng)前有哪些事務(wù)沒有提交SELECT * FROM information_schema.innodb_trx\G;重點(diǎn)看trx_started字段找出運(yùn)行時間特別長的事務(wù)。很多鎖問題都是因?yàn)閼?yīng)用開了事務(wù)插了一條數(shù)據(jù)卻遲遲不 COMMIT把鎖一直攥著其他線程全部卡死。死鎖的解決思路通常是縮短事務(wù)范圍插入后及時提交。多個事務(wù)插入多行時盡量按相同順序操作減少互相等待。應(yīng)用層處理死鎖的常規(guī)做法是捕獲 1213 錯誤后做有限次數(shù)的重試。還有一個容易被忽略的并發(fā)問題大量并發(fā)執(zhí)行INSERT ... ON DUPLICATE KEY UPDATE會在重復(fù)鍵上產(chǎn)生鎖競爭嚴(yán)重時也會引發(fā)死鎖。如果業(yè)務(wù)并發(fā)量很高建議從架構(gòu)上考慮比如把計數(shù)類的更新獨(dú)立到消息隊列中串行處理而不是所有線程同時懟一張表。6. 寫在最后幾次實(shí)戰(zhàn)排查后的個人體會干了這么多年我越來越覺得“插入數(shù)據(jù)”這件事看著簡單真正寫穩(wěn)一點(diǎn)都不容易。每次線上出問題最后定位到的原因往往都是那些平時不太注意的參數(shù)和細(xì)節(jié)。比如有一次排查接口偶發(fā)超時查到最后是一條 INSERT 語句在等另一個未提交事務(wù)的鎖而那個事務(wù)是前一個開發(fā)同學(xué)調(diào)試時手動開啟忘了提交的這種問題靠排錯表都很難一次定位必須熟悉事務(wù)和鎖的底層機(jī)制。我自己現(xiàn)在寫插入相關(guān)代碼時有幾個固定習(xí)慣寫完 SQL 先執(zhí)行SHOW CREATE TABLE核對字段和字符集批量插入強(qiáng)制分批能用顯式列名就絕不用省略寫法涉及“存在則更新”時先想清楚自增 ID 的消耗會不會影響業(yè)務(wù)。這些習(xí)慣不能幫你寫出更花哨的 SQL但能讓你少在半夜被線上告警叫醒。最后再分享一個小技巧如果你發(fā)現(xiàn)某條 INSERT 慢得異常別只盯著 SQL 本身先看表的數(shù)據(jù)量和索引情況。插入操作需要維護(hù)所有二級索引索引越多插入越慢。表里有大量在線業(yè)務(wù)還需要頻繁寫入時索引設(shè)計一定要謹(jǐn)慎這也是從源頭減少插入性能問題的最好方式。 MySQL INSERT 核心指南：語法、批量優(yōu)化與踩坑實(shí)錄
INSERT大概是所有 SQL 語句里長得最老實(shí)的一條看起來就是“往表里塞數(shù)據(jù)”很多同學(xué)寫了兩三年 SQL用到 90% 的場景也就一句話INSERT INTO 表名 VALUES (...)?？烧娴搅司€上環(huán)境你會發(fā)現(xiàn)這條“簡單”語句背后全是坑主鍵沖突、字符集報錯、批量插入慢到懷疑人生、自增 ID 莫名其妙瘋狂跳號、并發(fā)一上來就鎖等待超時。這篇文章把我這幾年在 MySQL 插入數(shù)據(jù)這件事上踩過的坑和總結(jié)出來的經(jīng)驗(yàn)完整過一遍從最基礎(chǔ)的語法拆到類型轉(zhuǎn)換、約束處理再到ON DUPLICATE KEY UPDATE、批量插入優(yōu)化和報錯排查。適合剛接觸 MySQL 的初學(xué)者也適合寫了兩三年代碼但沒認(rèn)真摳過細(xì)節(jié)的開發(fā)者對照著查能幫你少走很多彎路。1. 先看最基礎(chǔ)的 INSERT 語法兩種核心寫法怎么選1.1 完整語法與兩種基礎(chǔ)寫法MySQL 的 INSERT 語句標(biāo)準(zhǔn)語法可以簡單拆成這么幾塊INSERT [IGNORE] INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);這是最常見也最推薦的一種寫法顯式列出目標(biāo)列再給對應(yīng)的值。除了這種還有兩種變體也經(jīng)常遇到-- 省略列名按表結(jié)構(gòu)的列順序給值 INSERT INTO 表名 VALUES (值1, 值2, ...); -- 使用 SET 方式更像 UPDATE 的寫法 INSERT INTO 表名 SET 列名1 值1, 列名2 值2;SET寫法其實(shí)在很多 ORM 框架底層會用到比如某些早期版本的框架在生成插入語句時就會轉(zhuǎn)成這種形式。它的好處是列名和值一一對應(yīng)寫起來直觀缺點(diǎn)是同一張表插入多條數(shù)據(jù)時要寫多條語句沒法合并成一條多值插入性能上不如VALUES列表方式。多值插入可以寫成這樣INSERT INTO 表名 (列名1, 列名2) VALUES (值1, 值2), (值3, 值4), (值5, 值6);這種一次性插多行的寫法也是后面講批量插入性能優(yōu)化的基礎(chǔ)先把語法記住。這里補(bǔ)充一個容易看花眼的細(xì)節(jié)官方文檔里VALUES也可以寫作VALUE兩者等價。VALUE (1, 2)這種寫法在某些自動生成的腳本里能看到是合法的不用覺得奇怪。1.2 為什么業(yè)務(wù)代碼里建議顯式寫清列名很多初學(xué)者圖省事喜歡寫不帶列名的INSERT INTO 表名 VALUES (...)。這種寫法的前提是值的數(shù)量、順序必須和表結(jié)構(gòu)完全一致包括自增主鍵、默認(rèn)值字段、時間字段一個都不能少順序也不能亂。問題是表結(jié)構(gòu)是會變的。我見過一次線上事故起因就是某個開發(fā)同學(xué)圖省事寫了不帶列名的 INSERT后來 DBA 給表加了一個業(yè)務(wù)字段這條 SQL 直接在線上報錯“Column count doesnt match value count at row 1”接口瞬間 500。排查的時候發(fā)現(xiàn)代碼里幾十處插入語句全是這種寫法一次性全炸。顯式寫出列名至少有三個好處表結(jié)構(gòu)增加字段時老的 INSERT 不受影響新字段有默認(rèn)值就能正常插入。列的物理順序隨便調(diào)整SQL 都不會錯?？勺x性強(qiáng)別人看 SQL 一眼就知道每個值對應(yīng)的含義排查問題省很多時間。所以我個人的習(xí)慣是只要是寫進(jìn)業(yè)務(wù)代碼或腳本里的 INSERT一律顯式列名這一條從來沒有例外。2. 類型轉(zhuǎn)換、約束與自增主鍵插入前的隱藏邏輯2.1 嚴(yán)格模式才是你想要的模式MySQL 在插入數(shù)據(jù)時會對值做隱式類型轉(zhuǎn)換。最經(jīng)典的例子往INT類型的列里插入字符串a(chǎn)bc非嚴(yán)格模式下 MySQL 不會報錯而是把字符串轉(zhuǎn)成0存進(jìn)去插入12abc會轉(zhuǎn)成12。這種自動容錯看起來“方便”實(shí)際是在往數(shù)據(jù)表里埋雷。等到你按數(shù)字查數(shù)據(jù)時發(fā)現(xiàn)一堆臟數(shù)據(jù)根本不知道是哪條語句寫入的。MySQL 5.7 開始默認(rèn)開啟了嚴(yán)格模式核心參數(shù)是STRICT_TRANS_TALLS。在嚴(yán)格模式下插入abc到整數(shù)列會直接報錯Incorrect integer value: abc for column id at row 1。這樣反而好問題在寫入那一刻就暴露出來了。查看當(dāng)前嚴(yán)格模式的命令SELECT sql_mode;比較常見的sql_mode配置會包含這些值ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION。其中NO_ZERO_DATE和NO_ZERO_IN_DATE跟插入關(guān)系很大它們禁止插入0000-00-00這類零日期值。如果你是從老系統(tǒng)遷移數(shù)據(jù)可能會遇到原本存在的零日期在插入新庫時全部報錯的情況這個后面講時間字段時會再提到。2.2 自增主鍵與 last_insert_id() 的正確用法自增主鍵是 InnoDB 表最常見的做法。插入一條數(shù)據(jù)后要拿到這條數(shù)據(jù)生成的主鍵 ID標(biāo)準(zhǔn)做法是調(diào)用LAST_INSERT_ID()INSERT INTO 用戶表 (姓名) VALUES (張三); SELECT LAST_INSERT_ID();兩個容易被忽略的細(xì)節(jié)第一LAST_INSERT_ID()是會話級別的不是全局的也不是表級別的。A 連接插入后拿到 IDB 連接再調(diào)用拿到的是自己會話的值互不影響。如果在一個連接里發(fā)起了大量別的 SQL 之后再調(diào)用拿到的可能就不是你想的那個 ID 了所以要在插入語句之后立刻調(diào)用。第二如果用的是一條多值 INSERT 插入了多行LAST_INSERT_ID()只返回批量插入中產(chǎn)生的第一個 ID不是最后一個。比如一次插入了 5 行自增 ID 從 100 到 104調(diào)用返回的是 100。如果你需要拿到一整批的 ID方案有這么幾種插入后用業(yè)務(wù)唯一鍵回查這批記錄。插入前在應(yīng)用層生成 ID比如雪花算法然后把 ID 顯式插入。逐條插入逐條獲取但性能較差不推薦大批量場景用。另外注意如果你在 INSERT 時顯式指定了自增列的值比如INSERT INTO 表 (id, name) VALUES (200, a)那么LAST_INSERT_ID()返回的仍然是上一條自動生成的值不會變成 200除非你再插入一條真正由 MySQL 自增生成的記錄。2.3 約束在插入時怎么發(fā)力INSERT 不光是往表里寫數(shù)據(jù)它還要過一遍表上所有約束任何一環(huán)過不去整條語句就報錯。最常見的幾類NOT NULL約束字段沒有默認(rèn)值且沒傳值報Field xxx doesnt have a default value。DEFAULT約束字段有默認(rèn)值不傳時自動用默認(rèn)值。UNIQUE唯一約束插入重復(fù)值報Duplicate entry xxx for key yyy。CHECK約束MySQL 8.0.16 之后 CHECK 約束才真正生效插入不符合條件的值會報錯。FOREIGN KEY外鍵約束插入子表記錄時外鍵列的值在主表不存在報Cannot add or update a child row: a foreign key constraint fails。理解約束的檢查時機(jī)很重要。一條 INSERT 語句要插入多行時如果第 3 行違反了唯一約束整條語句會報錯而在默認(rèn)的自動提交模式下已經(jīng)插入的前兩行不會自動回滾表里會留下部分?jǐn)?shù)據(jù)。如果你希望“要么全成功要么全失敗”必須手動開啟事務(wù)把多條 INSERT 包在BEGIN和COMMIT之間出錯就ROLLBACK。3. 進(jìn)階寫法怎么選INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO3.1 INSERT IGNORE一次性導(dǎo)入去重很方便但要看清它會吞掉什么語法就是在 INSERT 后面加一個IGNOREINSERT IGNORE INTO 用戶表 (id, 郵箱) VALUES (1, testexample.com);IGNORE的本意是插入時遇到錯誤就跳過這條不報錯只給一個 warning。最常見的用途是做數(shù)據(jù)初始化或外部數(shù)據(jù)導(dǎo)入時遇到重復(fù)唯一鍵自動跳過保證后面數(shù)據(jù)繼續(xù)插入。但這里有個容易被忽略的坑IGNORE吞掉的不只是唯一鍵沖突還包括NOT NULL約束、Data too long、無效日期等很多錯誤。比如某個字段是NOT NULL你插入NULL普通 INSERT 會報錯INSERT IGNORE會靜默跳過然后字段被寫成默認(rèn)值或空字符串。數(shù)據(jù)缺失了程序也不知道后面查數(shù)據(jù)時才發(fā)現(xiàn)少了一堆記錄。我的建議是INSERT IGNORE只用在兩種場景一是外部導(dǎo)入時明確知道要按唯一鍵去重二是初始化字典表這類基礎(chǔ)數(shù)據(jù)。寫進(jìn)業(yè)務(wù)代碼前一定要想清楚如果這條插入因?yàn)閿?shù)據(jù)質(zhì)量被吞掉后續(xù)邏輯會不會出問題。3.2 ON DUPLICATE KEY UPDATE存在就更新但別忽視自增消耗這個語法是最常用的“存在即更新不存在即插入”方案俗稱 upsertINSERT INTO 計數(shù)器表 (id, 計數(shù)) VALUES (1, 1) ON DUPLICATE KEY UPDATE 計數(shù) 計數(shù) 1;在 MySQL 8.0.20 之前的版本里想在更新時引用本次準(zhǔn)備插入的值要借助VALUES()函數(shù)INSERT INTO 計數(shù)器表 (id, 計數(shù)) VALUES (1, 1) ON DUPLICATE KEY UPDATE 計數(shù) VALUES(計數(shù));8.0.20 開始官方把VALUES()這種寫法標(biāo)記為廢棄推薦使用行別名方式INSERT INTO 計數(shù)器表 (id, 計數(shù)) VALUES (1, 1) AS new ON DUPLICATE KEY UPDATE 計數(shù) new.計數(shù);用ON DUPLICATE KEY UPDATE時影響行數(shù)有三種含義插入成功返回 1更新成功返回 2數(shù)據(jù)沒任何變化返回 0。如果你的應(yīng)用依賴影響行數(shù)來判斷是插入還是更新這個細(xì)節(jié)要特別小心。還有個大坑是自增 ID 的消耗。ON DUPLICATE KEY UPDATE觸發(fā)更新時MySQL 在嘗試插入的那一刻就已經(jīng)從自增計數(shù)器里取走了一個 ID即使最終沒有真正插入新記錄ID 也會被“浪費(fèi)”。長期大量執(zhí)行 upsert 的表自增 ID 會漲得飛快甚至出現(xiàn)跳號幾百上千的情況。如果業(yè)務(wù)對主鍵連續(xù)性有要求這種方案就不太合適了。3.3 REPLACE INTO先刪再插代價比你想的大REPLACE INTO的語法和 INSERT 很像REPLACE INTO 用戶表 (id, 郵箱) VALUES (1, newexample.com);遇到主鍵或唯一鍵沖突時REPLACE不是更新而是先把舊記錄 DELETE 掉再插入一條新記錄。聽上去“無非是更新”實(shí)際代價很大刪除再插入意味著每次都要分配一個新的自增 ID自增消耗比ON DUPLICATE KEY UPDATE更嚴(yán)重。如果舊行被其他表的外鍵引用DELETE 可能會觸發(fā)外鍵失敗或級聯(lián)刪除。只想要更新某一個字段時其他字段也必須全量給出來否則會被重置為默認(rèn)值。如果表上有觸發(fā)器REPLACE會觸發(fā) DELETE 和 INSERT 兩類觸發(fā)器行為比預(yù)期復(fù)雜很多。所以我在工作里幾乎不用REPLACE INTO只有一種場景會考慮明確要重建整行數(shù)據(jù)且能接受主鍵 ID 變化和觸發(fā)器副作用。絕大多數(shù)“存在則更新”的需求ON DUPLICATE KEY UPDATE都更可控。4. 大批量插入時性能為什么差以及怎么改4.1 多行 VALUES 比單條插入快在哪插入性能最直觀的優(yōu)化辦法把多條單行 INSERT 合并成一條多行 INSERT。-- 不推薦循環(huán) 1000 次 INSERT INTO 表 (a, b) VALUES (1, 2); INSERT INTO 表 (a, b) VALUES (3, 4); -- 推薦一條語句插多行 INSERT INTO 表 (a, b) VALUES (1, 2), (3, 4);快的原因有幾點(diǎn)SQL 解析只做一次網(wǎng)絡(luò)往返從 N 次變成 1 次InnoDB 寫 binlog 和 redo log 的 IO 次數(shù)也大幅減少。尤其是網(wǎng)絡(luò)開銷以前每次 INSERT 都有一次客戶端和 MySQL 服務(wù)器之間的往返合并后直接省掉了絕大部分 RTT。所以在代碼里寫循環(huán)插入等于把最容易省掉的成本全扛在自己身上。需要注意單條 INSERT 語句也不是越大越好。MySQL 有max_allowed_packet參數(shù)限制單次包大小默認(rèn)值常見 64MB 或 128MB。如果一次插入的數(shù)據(jù)量太大會直接報錯Packet too large。建議單條多行 INSERT 控制在幾百到一兩千行之間既能享受合并帶來的性能收益又不會讓單條語句太大太笨重。4.2 JDBC 批量插入與 rewriteBatchedStatements 參數(shù)Java 后端最常見的插入場景是 MyBatis / JDBC 批量寫入。很多人寫代碼時用的是foreach拼接多行 VALUES或者是 JDBC 的addBatch()/executeBatch()。如果你的應(yīng)用用的是 JDBC 批量接口一定要確認(rèn)連接串里有沒有加上這么個參數(shù)jdbc:mysql://host:port/db?rewriteBatchedStatementstrue沒有這個參數(shù)MySQL 驅(qū)動會把executeBatch()里的每一條 SQL 都單獨(dú)發(fā)給服務(wù)器執(zhí)行所謂的“批量插入”實(shí)際上還是逐條插入性能提升很有限。加上rewriteBatchedStatementstrue之后驅(qū)動會自動把一批 INSERT 重寫成一條多行 VALUES 語句再發(fā)給服務(wù)器這時才是真正的批量插入。我遇到過很多次“一樣用 batch為什么性能天差地別”的排查最后 90% 都是這個參數(shù)沒開。還有一點(diǎn)批量插入最好確保每批次的數(shù)據(jù)量是可控的不要一次性addBatch幾百萬條還在繼續(xù)往內(nèi)存里塞建議每 1000 到 2000 條就executeBatch一次并清空 batch避免內(nèi)存撐爆和單條語句過大。4.3 幾個影響插入性能的數(shù)據(jù)庫側(cè)參數(shù)除了 SQL 寫法數(shù)據(jù)庫側(cè)有幾個參數(shù)會直接影響插入速度排查性能問題時可以按順序看innodb_flush_log_at_trx_commit默認(rèn)值 1表示每次事務(wù)提交都要把 redo log flush 到磁盤數(shù)據(jù)安全最好但最慢。如果業(yè)務(wù)能接受斷電或崩潰時丟失最近一小段數(shù)據(jù)可以臨時調(diào)到 2會快很多。生產(chǎn)環(huán)境調(diào)整這個參數(shù)必須經(jīng)過充分評估別把數(shù)據(jù)安全當(dāng)兒戲。innodb_autoinc_lock_mode自增鎖模式。默認(rèn)在 MySQL 5.7 里是 1連續(xù)模式會導(dǎo)致批量插入時其他插入操作被阻塞改成 2交錯模式后并發(fā)插入性能更好但要求 binlog 格式必須是ROW否則主從復(fù)制數(shù)據(jù)會不一致。max_allowed_packet前面提過控制單次最大包大小大批量插入時如果報Packet too large就調(diào)它。這些參數(shù)的名字我建議你記下來因?yàn)楹芏?MySQL 性能排查資料都會提到面試時也經(jīng)常被問。具體調(diào)參之前先看清楚自己用的 MySQL 版本不同版本默認(rèn)行為和參數(shù)取值不完全一樣。5. 插入報錯的常見場景與排查實(shí)錄5.1 先把最常見的報錯整理成一張表我根據(jù)平時排查線上問題的經(jīng)驗(yàn)把 INSERT 最常見的報錯整理成一張速查表按錯誤碼分類遇到問題直接對照著看很方便。錯誤碼常見報錯信息含義解決思路1062Duplicate entry xxx for key yyy唯一鍵或主鍵沖突檢查是否重復(fù)提交或改用 upsert 寫法1366Incorrect string value: \x...字符集不支持當(dāng)前字符字符串字符集改utf8mb4檢查連接字符集1364Field xxx doesnt have a default value非空字段沒有值給字段補(bǔ)值或?yàn)樽侄卧O(shè)置 DEFAULT1406Data too long for column xxx數(shù)據(jù)超過列長度限制檢查字段長度定義或截斷超長內(nèi)容1136Column count doesnt match value count列數(shù)量與值數(shù)量不一致檢查 SQL 中列名和值的數(shù)量1054Unknown column xxx in field list指定的列不存在核對表結(jié)構(gòu)確認(rèn)列名拼寫1146Table xxx doesnt exist表不存在檢查庫名表名是否存在大小寫是否有問題1452Cannot add or update a child row外鍵約束失敗檢查父表是否有對應(yīng)記錄1205Lock wait timeout exceeded鎖等待超時查事務(wù)、鎖持有情況縮短事務(wù)時間1213Deadlock found死鎖檢查事務(wù)加鎖順序必要時增加重試機(jī)制這張表只覆蓋高頻場景實(shí)際工作中還會遇到一些低頻但更隱蔽的問題下面挑幾個詳細(xì)展開。5.2 字符集引發(fā)的插入失敗emoji 插不進(jìn)去這是非常經(jīng)典的坑。往表里插入一個 emoji 或者生僻字?jǐn)?shù)據(jù)庫直接報錯INSERT INTO 用戶表 (昵稱) VALUES (張三); -- ERROR 1366 (HY000): Incorrect string value: \xF0\x9F\x98\x80 for column 昵稱 at row 1原因在于 MySQL 里的utf8其實(shí)最多只能存 3 個字節(jié)而 emoji 是 4 字節(jié)編碼必須用utf8mb4才能存下。很多老項目建表時寫的是utf8字符集本身就不支持 emoji。排查步驟一般是先看表的字符集SHOW CREATE TABLE 用戶表;如果字段是varchar(20) CHARACTER SET utf8就要修改字段字符集ALTER TABLE 用戶表 MODIFY 昵稱 VARCHAR(20) CHARACTER SET utf8mb4;還要確保 MySQL 連接的字符集也是utf8mb4否則改了表也白搭。連接后執(zhí)行SET NAMES utf8mb4;生產(chǎn)環(huán)境更徹底的做法建庫時就統(tǒng)一用utf8mb4連接串上也加上characterEncodingutf8mb4Java 側(cè)寫法是characterEncodingutf8配合服務(wù)端utf8mb4也能正常工作具體看驅(qū)動版本。如果只在某個表字段上用utf8mb4其他表和索引還是utf8兩表關(guān)聯(lián)時會出現(xiàn)字符集不一致導(dǎo)致索引失效的問題這個也要注意。5.3 時間字段的三個隱藏坑時間字段插入報錯沒有那么頻繁但一旦出問題排查起來很繞。第一個坑零日期。0000-00-00這種值在包含NO_ZERO_DATE的嚴(yán)格模式下插入會直接報錯。老系統(tǒng)導(dǎo)出數(shù)據(jù)時經(jīng)常有這種值導(dǎo)入新庫前要么先清洗成合法日期要么臨時調(diào)整sql_mode。第二個坑TIMESTAMP的范圍。TIMESTAMP類型取值范圍是 1970 年到 2038 年如果你插入一個2040-01-01會直接報錯或變成0000-00-00取決于模式。如果業(yè)務(wù)需要存更早或更晚的日期用DATETIME它能存從 1000 年到 9999 年。第三個坑時區(qū)。TIMESTAMP在存儲時會按照當(dāng)前會話時區(qū)轉(zhuǎn)換成 UTC讀取時再轉(zhuǎn)回來。如果應(yīng)用連接串上的時區(qū)和數(shù)據(jù)庫服務(wù)器的時區(qū)不一致插入的時間會比預(yù)期差幾個小時。排查時先看會話時區(qū)SELECT global.time_zone, session.time_zone;遇到時區(qū)問題建議應(yīng)用層統(tǒng)一用同一個時區(qū)寫入一般是東八區(qū)數(shù)據(jù)庫側(cè)也明確設(shè)置time_zone避免“明明插入的是一個時間點(diǎn)查出來卻是另一個時間”這種詭異現(xiàn)象。5.4 并發(fā)插入時的鎖等待與死鎖并發(fā)插入同一張表尤其是同一個唯一鍵附近很容易觸發(fā)鎖等待超時和死鎖。最典型的場景是多個事務(wù)同時準(zhǔn)備插入同一條唯一鍵第一個沒提交第二個插入相同鍵時在等待它的鎖超出時間就報Lock wait timeout exceeded; try restarting transaction。遇到這種情況先看當(dāng)前有哪些事務(wù)沒有提交SELECT * FROM information_schema.innodb_trx\G;重點(diǎn)看trx_started字段找出運(yùn)行時間特別長的事務(wù)。很多鎖問題都是因?yàn)閼?yīng)用開了事務(wù)插了一條數(shù)據(jù)卻遲遲不 COMMIT把鎖一直攥著其他線程全部卡死。死鎖的解決思路通常是縮短事務(wù)范圍插入后及時提交。多個事務(wù)插入多行時盡量按相同順序操作減少互相等待。應(yīng)用層處理死鎖的常規(guī)做法是捕獲 1213 錯誤后做有限次數(shù)的重試。還有一個容易被忽略的并發(fā)問題大量并發(fā)執(zhí)行INSERT ... ON DUPLICATE KEY UPDATE會在重復(fù)鍵上產(chǎn)生鎖競爭嚴(yán)重時也會引發(fā)死鎖。如果業(yè)務(wù)并發(fā)量很高建議從架構(gòu)上考慮比如把計數(shù)類的更新獨(dú)立到消息隊列中串行處理而不是所有線程同時懟一張表。6. 寫在最后幾次實(shí)戰(zhàn)排查后的個人體會干了這么多年我越來越覺得“插入數(shù)據(jù)”這件事看著簡單真正寫穩(wěn)一點(diǎn)都不容易。每次線上出問題最后定位到的原因往往都是那些平時不太注意的參數(shù)和細(xì)節(jié)。比如有一次排查接口偶發(fā)超時查到最后是一條 INSERT 語句在等另一個未提交事務(wù)的鎖而那個事務(wù)是前一個開發(fā)同學(xué)調(diào)試時手動開啟忘了提交的這種問題靠排錯表都很難一次定位必須熟悉事務(wù)和鎖的底層機(jī)制。我自己現(xiàn)在寫插入相關(guān)代碼時有幾個固定習(xí)慣寫完 SQL 先執(zhí)行SHOW CREATE TABLE核對字段和字符集批量插入強(qiáng)制分批能用顯式列名就絕不用省略寫法涉及“存在則更新”時先想清楚自增 ID 的消耗會不會影響業(yè)務(wù)。這些習(xí)慣不能幫你寫出更花哨的 SQL但能讓你少在半夜被線上告警叫醒。最后再分享一個小技巧如果你發(fā)現(xiàn)某條 INSERT 慢得異常別只盯著 SQL 本身先看表的數(shù)據(jù)量和索引情況。插入操作需要維護(hù)所有二級索引索引越多插入越慢。表里有大量在線業(yè)務(wù)還需要頻繁寫入時索引設(shè)計一定要謹(jǐn)慎這也是從源頭減少插入性能問題的最好方式。