設想

最近研發(fā)側在做數據導入的改造，改造的方式是將原來LOAD DATA的方式修改為INSERT插入的方式，主要基于如下兩方面考慮：

1、修改成INSERT后能更方便地在程序側控制寫入的線程數，更好地保護后端DB，防止后端DB雪崩

2、減少大批量導入帶來DB的延遲（單個LOAD拆分成多個INSERT，多線程同步）

另外監(jiān)控DB大部分的load數據量都比較小，改成INSERT對導入延遲影響不會太大。

下面是對INSERT和LOAD DATA的方式做的簡單的測試結果：

從上面的測試結果，批量INSERT 30000條數據只比LOAD DATA的方式慢了2倍多一點，如果將30000的數據拆分成1000一個批量INSERT的SQL，并發(fā)寫入，耗時比單線程LOAD要有提升。

上線遇到的問題

上線后，平均耗時增長了很多，如下圖所示：

并且深入去看，耗時比較高的都是跨IDC的機器（上海寫深圳），而且高得離譜。

從上圖中，可以看到，耗時比較大的都是上海的機器。

并且監(jiān)控DB的主從延遲也有比較大的飆升，如下圖：

奇怪，按照之前的計算評估，不應該會有那么大的延遲，而且同步延遲應該有緩解才對，不可能越來越厲害。

分析和解決問題

查看監(jiān)控發(fā)現INSERT出現了暴增，如下圖：

針對insert，改寫成1000條一次的并發(fā)插入，正常也不應該有那么大的增長，懷疑是INSERT INTO的方式不是采用批量的方式，而是一條一條insert的方式。分析binlog發(fā)現執(zhí)行方式如下：

BEGIN；

INSERT INTO TABLENAME VALUES('X','X','X');

INSERT INTO TABLENAME VALUES('X','X','X');

INSERT INTO TABLENAME VALUES('X','X','X');

.......

COMMIT;

這種方式和單個INSERT的方式性能差別其實并不算大，下面是簡單的測試結果：

測試場景：?在跨機房場景下，延遲相對比較大，30ms左右，測試INSERT本身的性能

1、INSERT 單行

2、INSERT 單行多values

3、INSERT 多行（27行）

4、INSERT 多行（27行）在一個事務中

測試接入過如下圖：

可以看出：

采用INSERT 多values的形式和插入單行差別不算大（主要是行數不多）；

采用INSERT多行和把多個INSERT放到一個大事務中性能相差不大。

解決辦法就很簡單了，直接把大事務修改為INSERT多values的方式。

修改后，INSERT的量大幅度下降：

平均耗時也大幅下降：

同步延遲也逐漸恢復：

總結

在寫入效率上，對比關系如下：

1、LOAD DATA效率最高

2、INSERT 多values次之

3、INSERT 多行大事務再次之

4、INSERT 單行最差