摘要

某些場景會有這樣的需求：無記錄則插入，有記錄則更新。例如：新增用戶，以身份證號碼作為唯一身份標(biāo)識，插入時若先查詢是否存在記錄再決定插入還是更新，在高并發(fā)情況下必然存在問題。本文提供三種解決方案。

方案一 加鎖

無論通過synchronized鎖、ReentranLock鎖還是分布式鎖，都可以解決該問題。缺點是，加鎖會影響性能。方法二和三都是數(shù)據(jù)庫層面解決方案，個人感覺比方法一好一些。

方案二 Unique和Replace Into … SELECT …

首先對唯一性的字段添加唯一索引ALTER TABLE tb_name ADD UNIQUE (col1、col2...)，，通過唯一索引即可保證數(shù)據(jù)的唯一性。

加入唯一索引后，通過INSERT INTO插入相同數(shù)據(jù)就會報錯，此時需要使用REPLACE INTO插入數(shù)據(jù)，用法是一樣的。通過REPLACE INTO插入數(shù)據(jù)時，若存在相同數(shù)據(jù)，會將之前的記錄刪除，再重新插入數(shù)據(jù)。缺點是，存在先刪除再插入的過程，sql需要考慮全部數(shù)據(jù)列，不然會丟失部分列的數(shù)據(jù)。缺點是，建立唯一索引會影響插入效率。下面是具體的例子。

# 建立索引
ALTER TABLE user ADD UNIQUE (id_card);
# 假設(shè)user表只有id,name,id_card三個字段，且id字段自增。
# 現(xiàn)在需要插入name=ly，id_card=142733的記錄。
# 但是，若之前存在id_card=142733的記錄，修改name=ly即可。
REPLACE INTO user (id,name,id_card) 
SELECT id,'ly',142733 FROM user RIGHT JOIN (SELECT 1) AS tab 
ON user.id_card = 142733;

通過RIGHT JOIN (SELECT 1)，若存在id_card=142733的記錄，執(zhí)行sql后會將原始id保存在臨時的結(jié)果集中，隨name和id_card一同插入。若不存在該記錄，則將null作為id隨name和id_card一同插入。最終實現(xiàn)

方案三 通過預(yù)插入語句判斷是否存在記錄

通過預(yù)插入語句，嘗試插入，判斷修改的記錄是否大于0，若大于0表示插入成功，若為0則表示記錄已存在，需要執(zhí)行更新操作。

# 預(yù)插入
INSERT INTO user (name,id_card)
SELECT 'ly',142733 FROM DUAL 
WHERE NOT EXISTE (SELECT id_card FROM user WHERE id_card = 142733) ;
# 若預(yù)插入語句插入成功（修改記錄數(shù)=1），則無需后續(xù)操作。否則執(zhí)行更新操作。
UPDATE user SET name = 'ly' WHERE id_card = 142733;

通過NOT EXISTE條件，若存在id_card=142733的記錄則偽表DUAL記錄為空，預(yù)插入語句修改記錄為0，此時需要執(zhí)行更新操作。

若不存在id_card=142733的記錄，則偽表DUAL記錄為一行且內(nèi)容是'ly',142733，預(yù)插入語句修改記錄為1，此時不必執(zhí)行更新語句。

Mysql批量插入更新性能優(yōu)化

對于數(shù)據(jù)量較大的插入和更新，因io/cpu等性能瓶頸，會產(chǎn)生大量的時間消耗，目前主流的優(yōu)化主要包括預(yù)編譯、單條sql插入多條數(shù)據(jù)、事務(wù)插入等，下面詳細(xì)介紹一下：

單條插入（Mybatis）

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(${cityCode}, ${cityName}, ${provinceName}, ${alias}, ${abbrePy})

單條預(yù)編譯插入（Mybatis）

采用預(yù)編譯可以節(jié)約mysql服務(wù)的解析時間，mytatis中就是采用#變量

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(#{cityCode}, #{cityName}, #{provinceName}, #{alias}, #{abbrePy})

單條sql插入多條數(shù)據(jù)

即是拼接sql，在一個sql中插入多條或更新多條數(shù)據(jù)。

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
("cityCode1", "cityName1", "provinceName1" "alias1", "abbrePy1"),("cityCode2", "cityName2", "provinceName2" "alias2", "abbrePy2")

快的原因

1、合并后日志量（MySQL的binlog和innodb的事務(wù)讓日志）減少了，降低日志刷盤的數(shù)據(jù)量和頻率，從而提高效率；

2、通過合并SQL語句，減少網(wǎng)絡(luò)傳輸?shù)腎O；

3、通過合并SQL語句，減少SQL語句解析的次數(shù)；

注意事項

1、數(shù)據(jù)庫sql長度是有限制，sql長度別溢出，會報錯；

2、亂序插入時候速度超過innodb_buffer的容量，每次定位索引涉及較多的磁盤讀寫操作，性能下降較快；

事務(wù)插入

事務(wù)插入即在插入前開啟事務(wù)，插入結(jié)束關(guān)閉事務(wù)進行提交即可。

快的原因

1、進行一個INSERT操作時，MySQL內(nèi)部會建立一個事務(wù)，在事務(wù)內(nèi)才進行真正插入處理操作。通過使用事務(wù)可以減少創(chuàng)建事務(wù)的消耗；

注意事項

1、事務(wù)不能過大，MySQL有innodb_log_buffer_size配置項，事務(wù)超出這個時，會刷磁盤，導(dǎo)致性能下降；

2、亂序插入時候速度超過innodb_buffer的容量，每次定位索引涉及較多的磁盤讀寫操作，性能下降較快；

測試結(jié)果

環(huán)境：i5-4200U 1.6GHZ，12G內(nèi)存，固態(tài)硬盤

總結(jié)

采用合并sql+事務(wù)插入組合，效率最高，亂序插入時候速度超過innodb_buffer的容量，每次定位索引涉及較多的磁盤讀寫操作，性能下降較快；盡量采用非亂序方式即可。以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持本站。

國外服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。