MemSQL、SQLFire、Oracle XE性能对比测试

首页 > 代码库 > MemSQL、SQLFire、Oracle XE性能对比测试

MemSQL、SQLFire、Oracle XE性能对比测试

2024-07-22 06:11:52 220人阅读

1.软件版本

MemSQL 3.1

Pivotal SQLFire 1.1.2

Oracle 11g XE

2.测试环境

宿主机

OS：Windows 7 64位专业版

CPU：i7-3770 3.4GHz

内存：16G

硬盘：大于800G

VMware虚拟机

OS：CentOS 6.3 64位

内存：16G

其中MemSQL运行于虚拟机，SQLFire和Oracle直接运行于宿主机

SQLFire只开启一个server member

3.DDL

MemSQL

DROP TABLE t_main;
DROP TABLE t_rel1;
DROP TABLE t_rel2;
DROP TABLE t_rel3;

create table t_main(
	rec_id BIGINT not null  PRIMARY KEY,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) ;

create table t_rel1(
	rec_id BIGINT not null  PRIMARY KEY,
	main_id BIGINT,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) ;

create table t_rel2(
	rec_id BIGINT not null  PRIMARY KEY,
	main_id BIGINT,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) ;

create table t_rel3(
	rec_id BIGINT not null  PRIMARY KEY,
	main_id BIGINT,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) ;

create index rel1main on t_rel1(main_id);
create index rel2main on t_rel2(main_id);
create index rel3main on t_rel3(main_id);

SQLFire

DROP TABLE t_main;
DROP TABLE t_rel1;
DROP TABLE t_rel2;
DROP TABLE t_rel3;

create table t_main(
	rec_id BIGINT not null  PRIMARY KEY,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) PERSISTENT;

create table t_rel1(
	rec_id BIGINT not null  PRIMARY KEY,
	main_id BIGINT,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) PERSISTENT;

create table t_rel2(
	rec_id BIGINT not null  PRIMARY KEY,
	main_id BIGINT,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) PERSISTENT;

create table t_rel3(
	rec_id BIGINT not null  PRIMARY KEY,
	main_id BIGINT,
	c1 DOUBLE PRECISION,
	c2 BIGINT,
	c3 VARCHAR(100),
	c4 VARCHAR(100),
	c5 VARCHAR(100),
	c6 VARCHAR(100)
	
) PERSISTENT;

create index rel1main on t_rel1(main_id);
create index rel2main on t_rel2(main_id);
create index rel3main on t_rel3(main_id);

Oracle

DROP TABLE t_main;
DROP TABLE t_rel1;
DROP TABLE t_rel2;
DROP TABLE t_rel3;

create table t_main(
	rec_id NUMBER(19,0) not null  PRIMARY KEY,
	c1 DOUBLE PRECISION,
	c2 NUMBER(19,0),
	c3 VARCHAR2(100),
	c4 VARCHAR2(100),
	c5 VARCHAR2(100),
	c6 VARCHAR2(100)
	
) ;

create table t_rel1(
	rec_id NUMBER(19,0) not null  PRIMARY KEY,
	main_id NUMBER(19,0),
	c1 DOUBLE PRECISION,
	c2 NUMBER(19,0),
	c3 VARCHAR2(100),
	c4 VARCHAR2(100),
	c5 VARCHAR2(100),
	c6 VARCHAR2(100)
	
) ;

create table t_rel2(
	rec_id NUMBER(19,0) not null  PRIMARY KEY,
	main_id NUMBER(19,0),
	c1 DOUBLE PRECISION,
	c2 NUMBER(19,0),
	c3 VARCHAR2(100),
	c4 VARCHAR2(100),
	c5 VARCHAR2(100),
	c6 VARCHAR2(100)
	
) ;

create table t_rel3(
	rec_id NUMBER(19,0) not null  PRIMARY KEY,
	main_id NUMBER(19,0),
	c1 DOUBLE PRECISION,
	c2 NUMBER(19,0),
	c3 VARCHAR2(100),
	c4 VARCHAR2(100),
	c5 VARCHAR2(100),
	c6 VARCHAR2(100)
	
) ;

create index rel1main on t_rel1(main_id);
create index rel2main on t_rel2(main_id);
create index rel3main on t_rel3(main_id);

4.测试数据

4张表，每张100万条数据

生成数据的代码片段为

String sql = "insert into t_main(rec_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?)";
		PreparedStatement st1 = conn.prepareStatement(sql);
		sql = "insert into t_rel1(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";
		PreparedStatement st2 = conn.prepareStatement(sql);
		sql = "insert into t_rel2(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";
		PreparedStatement st3 = conn.prepareStatement(sql);
		sql = "insert into t_rel3(rec_id,main_id,c1,c2,c3,c4,c5,c6) values(?,?,?,?,?,?,?,?)";
		PreparedStatement st4 = conn.prepareStatement(sql);

		long a = System.currentTimeMillis();

		for (int i = 1; i <= count; i++) {
			st1.setLong(1, i);
			st1.setDouble(2, i * 0.1);
			st1.setLong(3, i * 2);
			st1.setString(4, "c3_" + i);
			st1.setString(5, "c4_" + i);
			st1.setString(6, "c5_" + i);
			st1.setString(7, "c6_" + i);
			st1.executeUpdate();

			st2.setLong(1, i);
			st2.setLong(2, i);
			st2.setDouble(3, i * 0.2);
			st2.setLong(4, i * 3);
			st2.setString(5, "c3_" + i);
			st2.setString(6, "c4_" + i);
			st2.setString(7, "c5_" + i);
			st2.setString(8, "c6_" + i);
			st2.executeUpdate();

			st3.setLong(1, i);
			st3.setLong(2, i);
			st3.setDouble(3, i * 0.3);
			st3.setLong(4, i * 4);
			st3.setString(5, "c3_" + i);
			st3.setString(6, "c4_" + i);
			st3.setString(7, "c5_" + i);
			st3.setString(8, "c6_" + i);
			st3.executeUpdate();

			st4.setLong(1, i);
			st4.setLong(2, i);
			st4.setDouble(3, i * 0.4);
			st4.setLong(4, i * 5);
			st4.setString(5, "c3_" + i);
			st4.setString(6, "c4_" + i);
			st4.setString(7, "c5_" + i);
			st4.setString(8, "c6_" + i);
			st4.executeUpdate();

		}

5.测试结果

4张表，每张插入100万数据，消耗时间对比

单位：毫秒

MemSQL	SQLFire	Oracle
624765	196140	1289811

以下为查询测试，均执行10次求得平均消耗时间（不包含首次执行）

单位：毫秒

查询测试一：单表整型字段比较

select count(*) from t_main where c2>1000;

MemSQL	SQLFire	Oracle
21	675	58

查询测试二：单表like

select count(*) from t_main where c4 like '%c%';

MemSQL	SQLFire	Oracle
41	875	133

查询测试三：多表关联浮点数sum

select sum(m.c1+r1.c1+r2.c1+r3.c1) "rt" from t_main m,t_rel1 r1,t_rel2 r2,t_rel3 r3 where m.rec_id=r1.main_id and m.rec_id=r2.main_id and m.rec_id=r3.main_id;

MemSQL	SQLFire	Oracle
1365	14640	2077

查询测试四：多表关联整型sum

select sum(m.c2+r1.c2+r2.c2+r3.c2) "rt" from t_main m,t_rel1 r1,t_rel2 r2,t_rel3 r3 where m.rec_id=r1.main_id and m.rec_id=r2.main_id and m.rec_id=r3.main_id;

MemSQL	SQLFire	Oracle
1360	10257	2084

6.总结

测试过程中CPU、内存使用均未超过50%

插入性能SQLFire最高，MemSQL其次，Oracle最慢，MemSQL效率约是Oracle的两倍

查询性能MemSQL最高，Oracle其次，SQLFire最慢（慢的出奇。。。），MemSQL效率约是Oracle的两倍

不知道怎样的环境能测出30倍性能的提升。。。

MemSQL、SQLFire、Oracle XE性能对比测试

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > MemSQL、SQLFire、Oracle XE性能对比测试

MemSQL、SQLFire、Oracle XE性能对比测试

看完仍有疑问？有类似问题直接问程序猿