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