Mysql insert語句的優化總結

insert是保存數據的命令在web開發中我們經常用到insert來對數據的操作了，但insert的性能並不是非常的好，如果大數據量我們需要進行一些優化處理，下面來看一篇關於Mysql insert語句的優化總結文章。

1) 如果你同時從同一客戶插入很多行，使用多個值表的INSERT語句。這比使用分開INSERT語句快(在一些情況中幾倍)。

 代碼如下 復制代碼

Insert into test values(1,2),(1,3),(1,4)…

一條SQL語句插入多條數據。

常用的插入語句如：

 代碼如下 復制代碼

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); 
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);

修改成：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES
    ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

Java實現：

 代碼如下 復制代碼

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");
// 關閉自動提交，默認情況下每執行一條sql提交一次
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement("INSERT INTO insert_table VALUES(?, ?)");
//記錄1
statement.setString(1, "2012-12-27 11:11:11");
statement.setString(2, "userid_0");
statement.setString(3, "content_0");
statement.setInt(4, 0);
statement.addBatch();
//記錄2
statement.setString(1, "2012-12-27 12:12:12");
statement.setString(2, "userid_1");
statement.setString(3, "content_1");
statement.setInt(4, 1);
statement.addBatch();
//記錄3
statement.setString(1, "2012-12-27 13:13:13");
statement.setString(2, "userid_2");
statement.setString(3, "content_2");
statement.setInt(4, 2);

statement.addBatch();
//批量執行上面3條語句.
int [] counts = statement.executeBatch();
//Commit
connection.commit();

修改後的插入操作能夠提高程序的插入效率。這裡第二種SQL執行效率高的主要原因有兩個，一是減少SQL語句解析的操作， 只需要解析一次就能進行數據的插入操作，二是SQL語句較短，可以減少網絡傳輸的IO。

2) 如果你從不同客戶插入很多行，能通過使用INSERT DELAYED語句得到更高的速度。Delayed的含義是讓insert 語句馬上執行，其實數據都被放在內存的隊列中，並沒有真正寫入磁盤；這比每條語句分別插入要快的多；LOW_PRIORITY剛好相反，在所有其他用戶對表的讀寫完後才進行插入。

3) 將索引文件和數據文件分在不同的磁盤上存放（利用建表中的選項）。

4) 如果進行批量插入，可以增加bulk_insert_buffer_size變量值的方法來提高速度，但是，這只能對myisam表使用。

5) 當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍。

6) 根據應用情況使用replace語句代替insert。

7) 根據應用情況使用ignore關鍵字忽略重復記錄。

8)鎖定表可以加速用多個語句執行的INSERT操作：

 代碼如下 復制代碼 LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;

這樣性能會提高，因為索引緩存區僅在所有INSERT語句完成後刷新到磁盤上一次。一般有多少INSERT語句即有多少索引緩存區刷新。如果能用一個語句插入所有的行，就不需要鎖定。

對於事務表，應使用BEGIN和COMMIT代替LOCK TABLES來加快插入。

1.分析
插入一行分下面幾個動作，括號後面是其大約比例額
Connecting(3)
Sendint query to server(2)
Parsing query(2)
Inserting row(1*size of row)
Inserting indexes(1*number of indexes)
Closing(1)
插入索引的速度隨表的大小減慢，LogN

2.優化方法
a. 一個客戶端在一個時候要插多條數據，那麼用多個values
insert into t1 values(...),(...),(...)
如果是往一個非空的表裡插數據，可調節bulk_insert_buffer_size（缺省為8388608字節=8M）
b. 如果多個客戶端在同時插許多條數據，那麼用insert delayed語句
利：客戶端馬上返回，數據排成一隊；數據整齊的寫到一個塊裡，而不是分散。
弊：如果這個表被查獲刪數據，那麼插入會變慢，另外，為這個表起一個handler線程來處理這些數據也要耗費一些額外資源
待插的數據放在內存裡，一旦數據庫被意外終止（如kill -9），那麼數據會丟失。
這個方法只適用於myisam,memory,archive,blackhole引擎類表。
可調節delayed_insert_limit（缺省為一次100條）
delayed_insert_timeout（缺省為300）秒內，若無新的insert delayed語句，則handler線程退出。
delayed_queue_size（缺省為1000條）一旦滿了，客戶端的insert delayed會阻塞。
比第一個方法要慢。
而且對Myisam來說，在可以使用方法c時，不需用此方法b。
c. 對Myisam表來說，如果一個表中間沒有刪除過數據，那麼，在Select語句執行時，可以同時執行insert語句將數據插在文件最後。
concurrent_insert必須為1（缺省就是1）
d. 從文本文件執行load data infile一般要比用insert語句快20倍。
如果表有索引，可以先去掉索引，load完後，再加上索引。可以提高速度（相比load同時建索引，可以減少disk seek）。
這個事後建索引的方法在msisam表為空時自動執行。
e. 如果插入多條語句，可以先lock tables t write，插入後再unlock tables(索引會只flush一次）; 但如果當中只有1條insert，那麼不需要。
f. 要提高Myisam表的load data和insert速度，可提高key_buffer_size（缺省為8M）
如果機器有256M以上內存，那麼可以設key_buffer_size為64M，table_open_cache可以調高為256（缺省為64）
如果有128M以上內存，可以設key_buffer_size為16M

3.測試情況：
表t(id int auto_increment primary key,content1 varchar(30),content2 int);
create index ind_of_t on t(content1);

a. 單條插入空表：
1千條，耗時:24秒
5千條，耗時:160秒
1萬條，耗時:277秒

b. 一次1000個values list插空表
1千條，耗時:2秒
5千條，耗時:6秒
1萬條，耗時:11秒
5萬條，耗時:51秒
10萬條，耗時:99秒

c. 10個線程同時插空表，一次1000個values list
4,Begin ...
8,Begin ...
6,Begin ...
0,Begin ...
7,Begin ...
9,Begin ...
3,Begin ...
5,Begin ...
2,Begin ...
6,1千條，耗時:6秒
3,1千條，耗時:5秒
9,1千條，耗時:6秒
8,1千條，耗時:7秒
2,1千條，耗時:7秒
5,1千條，耗時:7秒
0,1千條，耗時:10秒
1,Begin ...
4,1千條，耗時:12秒
1,1千條，耗時:2秒
7,1千條，耗時:17秒
6,5千條，耗時:27秒
2,5千條，耗時:28秒
5,5千條，耗時:30秒
4,5千條，耗時:31秒
0,5千條，耗時:34秒
8,5千條，耗時:35秒
7,5千條，耗時:36秒
1,5千條，耗時:30秒
9,5千條，耗時:46秒
3,5千條，耗時:49秒
2,1萬條，耗時:49秒
8,1萬條，耗時:60秒
7,1萬條，耗時:61秒
0,1萬條，耗時:63秒
5,1萬條，耗時:65秒
6,1萬條，耗時:67秒
1,1萬條，耗時:61秒
4,1萬條，耗時:79秒
3,1萬條，耗時:78秒
9,1萬條，耗時:84秒
6,5萬條，耗時:275秒
1,5萬條，耗時:285秒
0,5萬條，耗時:306秒
8,5萬條，耗時:314秒
2,5萬條，耗時:316秒
4,5萬條，耗時:330秒
5,5萬條，耗時:351秒
3,5萬條，耗時:364秒
9,5萬條，耗時:377秒
7,5萬條，耗時:403秒
6,10萬條，耗時:552秒
6,End
0,10萬條，耗時:558秒
0,End
1,10萬條，耗時:573秒
1,End
4,10萬條，耗時:615秒
4,End
3,10萬條，耗時:615秒
3,End
5,10萬條，耗時:623秒
5,End
8,10萬條，耗時:625秒
8,End
7,10萬條，耗時:643秒
7,End
9,10萬條，耗時:648秒
9,End
2,10萬條，耗時:654秒
2,End

d. 10個線程同時插表（已有100萬條記錄），一次1000個values list，再插900萬條記錄
5,Begin ...on 1236937010秒
1,Begin ...on 1236937010秒
8,Begin ...on 1236937010秒
4,Begin ...on 1236937010秒
7,Begin ...on 1236937010秒
2,Begin ...on 1236937010秒
3,Begin ...on 1236937010秒
9,Begin ...on 1236937010秒
0,Begin ...on 1236937011秒
6,Begin ...on 1236937011秒
8,10萬條，耗時:499秒
0,10萬條，耗時:518秒
3,10萬條，耗時:519秒
7,10萬條，耗時:556秒
9,10萬條，耗時:565秒
2,10萬條，耗時:578秒
5,10萬條，耗時:654秒
1,10萬條，耗時:709秒
0,20萬條，耗時:1006秒
9,20萬條，耗時:1070秒
3,20萬條，耗時:1091秒
8,20萬條，耗時:1141秒
5,20萬條，耗時:1146秒
2,20萬條，耗時:1157秒
7,20萬條，耗時:1185秒
1,20萬條，耗時:1291秒
0,30萬條，耗時:1510秒
3,30萬條，耗時:1616秒
9,30萬條，耗時:1649秒
7,30萬條，耗時:1690秒
8,30萬條，耗時:1701秒
5,30萬條，耗時:1767秒
1,30萬條，耗時:1778秒
2,30萬條，耗時:1898秒
0,40萬條，耗時:2066秒
3,40萬條，耗時:2109秒
8,40萬條，耗時:2197秒
9,40萬條，耗時:2213秒
1,40萬條，耗時:2235秒
5,40萬條，耗時:2266秒
0,50萬條，耗時:2461秒
3,50萬條，耗時:2502秒
9,50萬條，耗時:2607秒
1,50萬條，耗時:2655秒
8,50萬條，耗時:2663秒
5,50萬條，耗時:2739秒
3,60萬條，耗時:2876秒
0,60萬條，耗時:2921秒
1,60萬條，耗時:3055秒
8,60萬條，耗時:3101秒
5,60萬條，耗時:3178秒
9,60萬條，耗時:3201秒
3,70萬條，耗時:3312秒
0,70萬條，耗時:3358秒
1,70萬條，耗時:3437秒
8,70萬條，耗時:3523秒
9,70萬條，耗時:3645秒
5,70萬條，耗時:3694秒
3,80萬條，耗時:3731秒
0,80萬條，耗時:3799秒
8,80萬條，耗時:3906秒
1,80萬條，耗時:3915秒
5,80萬條，耗時:4062秒
3,90萬條，耗時:4101秒
3,End
0,90萬條，耗時:4209秒
0,End
8,90萬條，耗時:4227秒
8,End
1,90萬條，耗時:4241秒
1,End
5,90萬條，耗時:4288秒
5,End