在數(shù)據(jù)分析領(lǐng)域，數(shù)據(jù)庫是我們的好幫手。不僅可以接受我們的查詢時間，還可以在這基礎(chǔ)上做進(jìn)一步分析。所以，我們必然要在數(shù)據(jù)庫插入數(shù)據(jù)。在實際應(yīng)用中，我們經(jīng)常遇到千萬級，甚至更大的數(shù)據(jù)量。如果沒有一個快速的插入方法，則會事倍功半，花費大量的時間。
在參加阿里的天池大數(shù)據(jù)算法競賽中（流行音樂趨勢預(yù)測），我遇到了這樣的問題，在沒有優(yōu)化數(shù)據(jù)庫查詢及插入之前，我花了不少冤枉時間，沒有優(yōu)化之前，1500萬條數(shù)據(jù)，光插入操作就花費了不可思議的12個小時以上（使用最基本的逐條插入）。這也促使我思考怎樣優(yōu)化數(shù)據(jù)庫插入及查詢操作，提高效率。
在不斷優(yōu)化過程中，性能有大幅提升。在按時間序列從數(shù)據(jù)庫查詢并匯總生成2萬6000多首歌曲的下載，播放，收藏數(shù)過程中，通過查詢生成的操作速度提高從預(yù)估的40多小時降低到一小時多。在數(shù)據(jù)庫插入方面，性能得到大幅提升；在新的數(shù)據(jù)集上測試，5490萬+的數(shù)據(jù)，20分鐘完成了插入。下面分享一下我的心得。
優(yōu)化過程分為2步。第一步，實驗靜態(tài)reader從CSV文件讀取數(shù)據(jù)，達(dá)到一定量時，開始多線程插入數(shù)據(jù)庫程序；第二步，使用mysq批量插入操作。
第一步，讀取文件，開始插入多線程
在這里，達(dá)到一定量的量是個需要斟酌的問題，在我的實驗中，開始使用100w作為這個量，但是出現(xiàn)了新的問題，Java 堆內(nèi)存溢出，最終采用了10W作為量的標(biāo)準(zhǔn)。
當(dāng)然，可以有其他的量，看大家自己喜歡那個了。
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import preprocess.ImportDataBase;
public class MuiltThreadImportDB {
/**
* Java多線程讀大文件并入庫
*
* @param args
*/
private static int m_record = 99999;
private static BufferedReader br = null;
private ArrayListString> list;
private static int m_thread = 0;
static {
try {
br = new BufferedReader(
new FileReader(
"E:/tianci/IJCAI15 Data/data_format1/user_log_format1.csv"),8192);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
br.readLine(); // 去掉CSV Header
} catch (IOException e) {
e.printStackTrace();
}
}
public void start() {
String line;
int count = 0;
list = new ArrayListString>(m_record + 1);
synchronized (br) {
try {
while ((line = br.readLine()) != null) {
if (count m_record) {
list.add(line);
count++;
} else {
list.add(line);
count = 0;
Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++));
t1.start();
list = new ArrayListString>(m_record + 1);
}
}
if (list != null) {
Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++));
t1.start();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
new MuiltThreadImportDB().start();
}
}
第二步，使用多線程，批量插入數(shù)據(jù)
class MultiThread implements Runnable {
private ArrayListString> list;
public MultiThread(ArrayListString> list) {
this.list = list;
}
public void run() {
try {
ImportDataBase insert = new ImportDataBase(list);
insert.start();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
display(this.list);
}
public void display(ListString> list) {
// for (String str : list) {
// System.out.println(str);
// }
System.out.print(Thread.currentThread().getName() + " :");
System.out.println(list.size());
}
}
批量操作中，使用mysql的prepareStatement類，當(dāng)然也使用了statement類的批量操作，性能比不上前者。前者可以達(dá)到1w+每秒的插入速度，后者只有2000+；
public int insertUserBehaviour(ArrayListString> sqls) throws SQLException {
String sql = "insert into user_behaviour_log (user_id,item_id,cat_id,merchant_id,brand_id,time_stamp,action_type)"
+ " values(?,?,?,?,?,?,?)";
preStmt = conn.prepareStatement(sql);
for (int i = 0; i sqls.size(); i++) {
UserLog log =new UserLog(sqls.get(i));
preStmt.setString(1, log.getUser_id());
preStmt.setString(2, log.getItem_id());
preStmt.setString(3, log.getCat_id());
preStmt.setString(4, log.getMerchant_id());
preStmt.setString(5, log.getBrand_id());
preStmt.setString(6, log.getTimeStamp());
preStmt.setString(7, log.getActionType());
preStmt.addBatch();
if ((i + 1) % 10000 == 0) {
preStmt.executeBatch();
conn.commit();
preStmt.clearBatch();
}
}
preStmt.executeBatch();
conn.commit();
return 1;
}
當(dāng)然，也實驗了不同的mysql存儲引擎，InnoDB和MyISM,實驗結(jié)果發(fā)現(xiàn)，InnoDB更快（3倍左右），可能和mysq的新版本有關(guān)系，筆者的mysql版本是5.6。
最后總結(jié)一下，大數(shù)據(jù)量下，提高插入速度的方法。
Java代碼方面，使用多線程插入，并且使用批處理提交。
數(shù)據(jù)庫方面，表結(jié)構(gòu)建立時不要使用索引，要不然插入過程過還要維護(hù)索引B+樹；修改存儲引擎，一般默認(rèn)是InnoDB，（新版本就使用默認(rèn)就可以，老版本可能需要）。
以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
您可能感興趣的文章:- mysql千萬級數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實現(xiàn)
- MySQL循環(huán)插入千萬級數(shù)據(jù)
- MySQL 千萬級數(shù)據(jù)量如何快速分頁
- MySQL 快速刪除大量數(shù)據(jù)（千萬級別）的幾種實踐方案詳解
- MySQL千萬級大數(shù)據(jù)SQL查詢優(yōu)化知識點總結(jié)
- MySQL如何快速的創(chuàng)建千萬級測試數(shù)據(jù)
- 30個mysql千萬級大數(shù)據(jù)SQL查詢優(yōu)化技巧詳解
- mysql千萬級數(shù)據(jù)分頁查詢性能優(yōu)化
- 如何優(yōu)化Mysql千萬級快速分頁
- 詳解MySQL數(shù)據(jù)庫千萬級數(shù)據(jù)查詢和存儲
