mysql有索引入库速度_mysql千万级数据入库，提升插入速度

需求：将文本文件中包含的一千万int型id数据插入mysql中，并求得出现频率最高的前10条。

本文只探讨mysql插入速度。‍‍分别从‍‍数据库‍‍和‍‍代码‍‍两方面进行总结。‍‍

首先有表test：

CREATE TABLE `test` (

`id` INT NOT NULL AUTO_INCREMENT,

`testId` INT NULL,

PRIMARY KEY (`id`),

INDEX `testId` (`testId` ASC));

最基本的实现方式：

import java.io.BufferedReader;

import java.io.File;

import java.io.FileReader;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class InserterBasic {

public static void main(String[] args) throws Exception {

File file = new File("D://Downloads//BaiduYunDownload//id.txt");

BufferedReader reader = null;

reader = new BufferedReader(new FileReader(file));

String tempString = null;

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

Statement statement = con.createStatement();

while ((tempString = reader.readLine()) != null) {

statement.execute("insert into test (testId) value ("+tempString+")");

}

statement.close();

con.close();

reader.close();

}

}

这是随手即可写出的最简单实现。但它面临着几个问题：首先读文件代码和JDBC代码混淆在一起，其次无法检测插入速度不利于对比。

加入速度检测代码

使用LinkedList作为一个queue，将读出的文件中数据读入其中

再通过Timer不断的检测这个queue的长度，以此来确认入库速度。

import java.io.BufferedReader;

import java.io.File;

import java.io.FileReader;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

import java.util.LinkedList;

import java.util.Queue;

import java.util.TimerTask;

public class InserterControl {

//包含一千万数据的容器

static Queue queue = new LinkedList();

static int prevSize = 0;

public static void main(String[] args) throws Exception {

InserterControl inserter = new InserterControl();

inserter.read();

inserter.control();

inserter.insert();

}

/**

* 监控

*/

void control(){

new java.util.Timer().schedule(new TimerTask() {

@Override

public void run() {

int thisSize = queue.size();

System.out.println((prevSize-thisSize)/10+"条/秒");

prevSize = thisSize;

}

}, 0, 1000*10);//每10秒钟统计一次入库速度

}

/**

* 读文件

* @throws Exception

*/

void read() throws Exception{

File file = new File("D://Downloads//BaiduYunDownload//id.txt");

BufferedReader reader = null;

reader = new BufferedReader(new FileReader(file));

String tempString = null;

while ((tempString = reader.readLine()) != null) {

queue.offer(tempString);

}

prevSize = queue.size();

reader.close();

}

/**

* 入库

* @throws Exception

*/

void insert() throws Exception {

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

Statement statement = con.createStatement();

while(!queue.isEmpty()){

statement.execute("insert into test (testId) value ("+queue.poll()+")");

}

statement.close();

con.close();

}

}

输出前10条结果：

39条/秒

37条/秒

38条/秒

37条/秒

35条/秒

29条/秒

25条/秒

36条/秒

35条/秒

35条/秒

平均速度：34.5条/秒。粗略估计80小时候完成。结果令人沮丧。

/**对数据库参数进行调整***************************************************************************************/

调整 innodb_flush_log_at_trx_commit=0。重启。truncate test;

(其实直接将存储引擎改成MyISAM，速度可直接到达9000条/秒~10000条/秒)

再来一次：

8764条/秒

9128条/秒

7439条/秒

6129条/秒

7578条/秒

6711条/秒

7105条/秒

6754条/秒

7175条/秒

6855条/秒

平均速度：7363.8条/秒，预计20分钟即可完成。效果拔群，轻而易举的就将速度翻了210倍。

删除索引

此表在建表之初加入索引，是为了提高其后的组函数查询效率，但是在如果只考虑插入数据的速度，索引显示是个累赘。试试把索引删掉会如何。此处删除testId上索引的同时也删除了主键。使用java代码来模拟自增主键：

static int id = 0;

synchronized static int getId(){

return id++;

}

//或者是使用java.util.concurrent.atomic.AtomicInteger进行id的自增

static AtomicInteger id = new AtomicInteger(0);

truncate test;

ALTER TABLE `test`

CHANGE COLUMN `id` `id` INT(11) NULL ,

DROP INDEX `testId` ,

DROP PRIMARY KEY;

输出：

平均速度：8150.6条/秒。每秒写入增加近1000。相信这个差距会随着数据量的扩充而不断增大。

/**java代码方面*******************************************************************************************/

禁用自动提交

con.setAutoCommit(false);

//...

int i=0;

while(!queue.isEmpty()){

if(i%30000==0){

con.commit();

}

i++;

//...

输出：

平均速度：8635.1条/秒。提升近500。

多线程

使用多个线程，模拟多连接。

//

static Queue queue = new ConcurrentLinkedQueue();

void doInsert(){

for(int i=0; i<10; i++){

new Thread(){

@Override

public void run() {

try {

insert();

} catch (Exception e) {

e.printStackTrace();

}

};

}.start();

}

}

输出：

18823条/秒

17953条/秒

18485条/秒

18511条/秒

18473条/秒

16979条/秒

16091条/秒

17270条/秒

17041条/秒

17777条/秒

平均速度：15891条/秒。又有了进一倍的提升。

/************************************************************************************************/

总结：

1. 经过上述调整，插入速度提升了450倍左右。十分钟左右即可完成。喜大普奔。

2.innodb_flush_log_at_trx_commit=0对InnoDB的插入速度影响显著。需要深入研究。

3. 诸如此类统计需求，更好的选择是MyISAM。