怎么在千万级数据的表中加索引 insert into select 千万级数据量

转载

mob64ca13f63f2c 2024-05-15 13:08:23

文章标签 怎么在千万级数据的表中加索引数据库 java sql mysql 文章分类 数据仓库大数据

首先有表test：

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `testId` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `testId` (`testId` ASC));
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();
	}
}
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<String> queue = new LinkedList<String>();
	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();
	}
}
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<String> queue = new LinkedList<String>();
	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条/秒
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条/秒
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);
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;
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++;
	//...
con.setAutoCommit(false);

//...

int i=0;
while(!queue.isEmpty()){
	if(i%30000==0){
		con.commit();
	}
	i++;
	//...

输出：

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

多线程

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

//
static Queue<String> queue = new ConcurrentLinkedQueue<String>();

void doInsert(){
	for(int i=0; i<10; i++){
		new Thread(){
			@Override
			public void run() {
				try {
					insert();
				} catch (Exception e) {
					e.printStackTrace();
				}
			};
		}.start();
	}
}
//
static Queue<String> queue = new ConcurrentLinkedQueue<String>();

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条/秒
18823条/秒
17953条/秒
18485条/秒
18511条/秒
18473条/秒
16979条/秒
16091条/秒
17270条/秒
17041条/秒
17777条/秒

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

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

总结：

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

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

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。