数据库

首页 > 数据库 > MongoDB > MySQL与MongoDB的性能测试对比

MySQL与MongoDB的性能测试对比

分享到：

【字体：大中小】

2017-01-28
来源：网络
467
50
1

导读：
摘要：MySQL版本：5.1.50，驱动版本：5.1.6（最新的5.1.13有很多杂七杂八的问题）MongoDB版本：1.6.2，驱动版本：2.1操作系统：WindowsXPSP3（这个影响应该不大）CPU：IntelCore2E65502.33G内存：2G（足够了）MySQL启动参数：bin/mys...

MySQL与MongoDB的性能测试对比

MySQL 版本： 5.1.50 ，驱动版本： 5.1.6 （最新的 5.1.13 有很多杂七杂八的问题）

MongoDB 版本： 1.6.2 ，驱动版本： 2.1

操作系统： Windows XP SP3 （这个影响应该不大）

CPU ： Intel Core2 E6550 2.33G

内存： 2G （足够了）

MySQL 启动参数： bin/mysqld --no-defaults --console --character-set-server=utf8 --max_connections=1000 --max_user_connections=1000

MongoDB 启动参数： bin/mongod --dbpath data/ --directoryperdb --rest --maxConns 1000 ?quiet

除了加大最大连接数之外，均使用默认参数

该测试主要为改进当前系统的日志的存储和查询性能提供参考，所以表的创建也以实际情况为例，下面是 MySQL 的建表语句：

CREATE TABLE `flt_evecurrent` (

`NodeID` int(11) NOT NULL DEFAULT '0',

`FltID` int(11) NOT NULL DEFAULT '0',

`ObjID` int(11) DEFAULT NULL,

`StationID` int(11) DEFAULT NULL,

`EveType` int(11) DEFAULT NULL,

`Severity` int(11) DEFAULT NULL,

`ReportTime` date DEFAULT NULL,

`CreateTime` date DEFAULT NULL,

`EveContent` varchar(1024) DEFAULT NULL,

`EveDesc` varchar(256) DEFAULT NULL,

PRIMARY KEY (`NodeID`,`FltID`)

);

MongoDB 类似，索引按照查询语句的查询字段创建，该例子中为 ObjID 和 CreateTime 两个字段创建索引。

分别插入 100 万条记录，并对其做 100 个用户并发查询操作。

MySQL 每一次都 Drop 表， MongoDB 每一次都删除 data 目录。

查询的时候，从第二次查询开始，连续记录三次。

	插入时间	查询时间
MySQL InnoDB 引擎无索引	10 分 33 秒	39.516 秒、 35.907 秒、 39.907 秒
MySQL InnoDB 引擎有索引	11 分 16 秒	非常不稳定： 22.531 秒、 13.078 秒、 23.078 秒、 26.047 秒、 21.234 秒、 28.469 秒、 20.922 秒、 13.328 秒
MySQL MyISAM 引擎无索引	3 分 21 秒	22.812 秒、 23.343 秒、 23.125 秒
MySQL MyISAM 引擎有索引	3 分 50 秒	10.312 秒、 10.359 秒、 10.296 秒
MongoDB 无索引	37 秒	59.531 秒、 60.063 秒、 59.891 秒
MongoDB 有索引	50 秒	3.484 秒、 3.453 秒、 3.453 秒

磁盘空间占用（有索引时候的占用，无索引情况差不多）：

MySQL MyISAM ： 57MB

MySQL InnoDB ： 264MB

MongoDB ： 464MB

另外测试中还发现一个有意思的现象，如果 MongoDB 查询中，如果单独查询 ObjID 字段，耗时约 1 秒，如果单独查询 CreateTime 字段，耗时约 10 秒，如果两个字段合起来查，就是上面的结果，约 3 秒，估计 MongoDB 内部对查询顺序作了优化吧。

该测试没有对MySQL和MongoDB的启动参数作任何优化，因为根据经验即便优化性能也不会有数量级的提升，另外也只是给一个大概的印象吧，第一印象总是很重要的，呵呵。
下面是代码：

MySQL插入

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Random;
public class AddData {
    public static void main(String[] args) throws Exception {
        Connection connection;
        Statement statement;
Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
        statement = connection.createStatement();
        // 清理表
statement.executeUpdate("truncate table flt_evecurrent");
        // 增加记录
        Random random = new Random(System.currentTimeMillis());
        for (int fltId = 0; fltId < 1000000; fltId++) {
if ((fltId % 10000) == 0) {
System.out.println(fltId);
}
int nodeId = 0;
int objId = random.nextInt(100);
int stationId = objId;
int eveType = 0;
int severity = 0;
String reportTime = String.format("2010-09-%d", fltId / 100000 + 1);
String createTime = reportTime;
String eveContent = "ContentContentContent";
String eveDesc = "DescDescDesc";
String sql = String.format("insert into flt_evecurrent (NodeID, FltID, ObjID, StationID, EveType, Severity, ReportTime, CreateTime, EveContent, EveDesc) "
+ "values (%d, %d, %d, %d, %d, %d, '%s', '%s', '%s', '%s')",
nodeId, fltId, objId, stationId, eveType, severity, reportTime, createTime, eveContent, eveDesc);
statement.executeUpdate(sql);
        }
statement.close();
connection.close();
    }
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Random;

public class AddData {

    public static void main(String[] args) throws Exception {
        Connection connection;
        Statement statement;

        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
        statement = connection.createStatement();

// 清理表
statement.executeUpdate("truncate table flt_evecurrent");

        // 增加记录
        Random random = new Random(System.currentTimeMillis());
        for (int fltId = 0; fltId < 1000000; fltId++) {
if ((fltId % 10000) == 0) {
System.out.println(fltId);
}
int nodeId = 0;
int objId = random.nextInt(100);
int stationId = objId;
int eveType = 0;
int severity = 0;
String reportTime = String.format("2010-09-%d", fltId / 100000 + 1);
String createTime = reportTime;
String eveContent = "ContentContentContent";
String eveDesc = "DescDescDesc";
String sql = String.format("insert into flt_evecurrent (NodeID, FltID, ObjID, StationID, EveType, Severity, ReportTime, CreateTime, EveContent, EveDesc) "
+ "values (%d, %d, %d, %d, %d, %d, '%s', '%s', '%s', '%s')",
nodeId, fltId, objId, stationId, eveType, severity, reportTime, createTime, eveContent, eveDesc);
statement.executeUpdate(sql);
        }

        statement.close();
        connection.close();
    }
}

MySQL查询

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class PerfTest {
    public PerfTest() {
    }
    @BeforeClass
    public static void setUpClass() throws Exception {
    }
    @AfterClass
    public static void tearDownClass() throws Exception {
    }
    @Test
    public void test() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
//        final Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
        List threads = new ArrayList();
        for (int i = 0; i < 100; i++) {
Thread thread = new Thread(new Runnable() {
public void run() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
statement = connection.createStatement();
System.out.println(String.format("线程%d查询开始", Thread.currentThread().getId()));
resultSet = statement.executeQuery("select count(*) from flt_evecurrent where objid in (30,50,70) and createtime between '2010-09-03' and '2010-09-07'");
resultSet.first();
System.out.println(String.format("数量：%d", resultSet.getLong("count(*)")));
System.out.println(String.format("线程%d查询结束", Thread.currentThread().getId()));
} catch (Exception ex) {
Logger.getLogger(PerfTest.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
connection.close();
} catch (SQLException ex) {
Logger.getLogger(PerfTest.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
});
thread.start();
threads.add(thread);
        }
        for (Thread thread : threads) {
thread.join();
        }
//        connection.close();
    }
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class PerfTest {

public PerfTest() {
}

    @BeforeClass
    public static void setUpClass() throws Exception {
    }

    @AfterClass
    public static void tearDownClass() throws Exception {
    }

    @Test
    public void test() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
//        final Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");

List threads = new ArrayList();
for (int i = 0; i < 100; i++) {
Thread thread = new Thread(new Runnable() {

public void run() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
statement = connection.createStatement();
System.out.println(String.format("线程%d查询开始", Thread.currentThread().getId()));
resultSet = statement.executeQuery("select count(*) from flt_evecurrent where objid in (30,50,70) and createtime between '2010-09-03' and '2010-09-07'");
resultSet.first();
System.out.println(String.format("数量：%d", resultSet.getLong("count(*)")));
System.out.println(String.format("线程%d查询结束", Thread.currentThread().getId()));
} catch (Exception ex) {
Logger.getLogger(PerfTest.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
connection.close();
} catch (SQLException ex) {
Logger.getLogger(PerfTest.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
});
thread.start();
threads.add(thread);
}

for (Thread thread : threads) {
thread.join();
}

// connection.close();
}
}

MongoDB插入

import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.Mongo;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;
public class AddData {
    public static void main(String[] args) throws Exception {
        Mongo mongo = new Mongo("localhost", 27017);
        DB db = mongo.getDB("test");
        DBCollection coll = db.getCollection("flt_evecurrent");
        // 删除表
        coll.drop();
        // 增加索引
        coll.createIndex(new BasicDBObject("ObjID", 1));
        coll.createIndex(new BasicDBObject("CreateTime", 1));
        // 增加记录
        Random random = new Random(System.currentTimeMillis());
        Calendar calendar = Calendar.getInstance();
        for (int fltId = 0; fltId < 1000000; fltId++) {
if ((fltId % 10000) == 0) {
System.out.println(fltId);
}
int nodeId = 0;
int objId = random.nextInt(100);
int stationId = objId;
int eveType = 0;
int severity = 0;
calendar.set(2010, 9, fltId / 100000 + 1);
Date reportTime = calendar.getTime();
Date createTime = reportTime;
String eveContent = "ContentContentContent";
String eveDesc = "DescDescDesc";
BasicDBObject obj = new BasicDBObject();
obj.put("NodeID", nodeId);
obj.put("FltID", fltId);
obj.put("ObjID", objId);
obj.put("StationID", stationId);
obj.put("EveType", eveType);
obj.put("Severity", severity);
obj.put("ReportTime", reportTime);
obj.put("CreateTime", createTime);
obj.put("EveContent", eveContent);
obj.put("EveDesc", eveDesc);
coll.insert(obj);
        }
    }
}
import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.Mongo;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;

public class AddData {

    public static void main(String[] args) throws Exception {
        Mongo mongo = new Mongo("localhost", 27017);
        DB db = mongo.getDB("test");
        DBCollection coll = db.getCollection("flt_evecurrent");

// 删除表
coll.drop();

        // 增加索引
        coll.createIndex(new BasicDBObject("ObjID", 1));
        coll.createIndex(new BasicDBObject("CreateTime", 1));

        // 增加记录
        Random random = new Random(System.currentTimeMillis());
        Calendar calendar = Calendar.getInstance();
        for (int fltId = 0; fltId < 1000000; fltId++) {
if ((fltId % 10000) == 0) {
System.out.println(fltId);
}
int nodeId = 0;
int objId = random.nextInt(100);
int stationId = objId;
int eveType = 0;
int severity = 0;
calendar.set(2010, 9, fltId / 100000 + 1);
Date reportTime = calendar.getTime();
Date createTime = reportTime;
String eveContent = "ContentContentContent";
String eveDesc = "DescDescDesc";
BasicDBObject obj = new BasicDBObject();
obj.put("NodeID", nodeId);
obj.put("FltID", fltId);
obj.put("ObjID", objId);
obj.put("StationID", stationId);
obj.put("EveType", eveType);
obj.put("Severity", severity);
obj.put("ReportTime", reportTime);
obj.put("CreateTime", createTime);
obj.put("EveContent", eveContent);
obj.put("EveDesc", eveDesc);
coll.insert(obj);
        }
    }
}

MongoDB查询

import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.Mongo;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class PerfTest {
    public PerfTest() {
    }
    @BeforeClass
    public static void setUpClass() throws Exception {
    }
    @AfterClass
    public static void tearDownClass() throws Exception {
    }
    @Test
    public void test() throws Exception {
        // 增大Mongo驱动的并发连接数量
System.setProperty("MONGO.POOLSIZE", "1000");
        Mongo mongo = new Mongo("localhost", 27017);
        DB db = mongo.getDB("test");
        final DBCollection coll = db.getCollection("flt_evecurrent");
        Calendar calendar = Calendar.getInstance();
        calendar.set(2010, 9, 3);
        Date beginTime = calendar.getTime();
        calendar.set(2010, 9, 7);
        Date endTime = calendar.getTime();
        List objIds = new ArrayList();
objIds.add(30);
objIds.add(50);
objIds.add(70);
        final BasicDBObject query = new BasicDBObject();
        query.put("CreateTime", new BasicDBObject("$gte", beginTime).append("$lte", endTime));
        query.put("ObjID", new BasicDBObject("$in", objIds));
        List threads = new ArrayList();
        for (int i = 0; i < 100; i++) {
Thread thread = new Thread(new Runnable() {
public void run() {
System.out.println(String.format("线程%d查询开始", Thread.currentThread().getId()));
long count = coll.getCount(query);
System.out.println(String.format("数量：%d", count));
System.out.println(String.format("线程%d查询结束", Thread.currentThread().getId()));
}
});
thread.start();
threads.add(thread);
        }
        for (Thread thread : threads) {
thread.join();
        }
    }
}
import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.Mongo;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class PerfTest {

public PerfTest() {
}

    @BeforeClass
    public static void setUpClass() throws Exception {
    }

    @AfterClass
    public static void tearDownClass() throws Exception {
    }

    @Test
    public void test() throws Exception {
        // 增大Mongo驱动的并发连接数量
System.setProperty("MONGO.POOLSIZE", "1000");

        Mongo mongo = new Mongo("localhost", 27017);
        DB db = mongo.getDB("test");
        final DBCollection coll = db.getCollection("flt_evecurrent");

        Calendar calendar = Calendar.getInstance();
        calendar.set(2010, 9, 3);
        Date beginTime = calendar.getTime();
        calendar.set(2010, 9, 7);
        Date endTime = calendar.getTime();
        List objIds = new ArrayList();
        objIds.add(30);
        objIds.add(50);
        objIds.add(70);
        final BasicDBObject query = new BasicDBObject();
        query.put("CreateTime", new BasicDBObject("$gte", beginTime).append("$lte", endTime));
        query.put("ObjID", new BasicDBObject("$in", objIds));

List threads = new ArrayList();
for (int i = 0; i < 100; i++) {
Thread thread = new Thread(new Runnable() {

public void run() {
System.out.println(String.format("线程%d查询开始", Thread.currentThread().getId()));
long count = coll.getCount(query);
System.out.println(String.format("数量：%d", count));
System.out.println(String.format("线程%d查询结束", Thread.currentThread().getId()));
}
});
thread.start();
threads.add(thread);
}

        for (Thread thread : threads) {
thread.join();
        }
    }
}

MySQL的建表语句

-- MyISAM无索引
DROP TABLE `flt_evecurrent`;
CREATE TABLE `flt_evecurrent` (
`NodeID` int(11) NOT NULL DEFAULT '0',
`FltID` int(11) NOT NULL DEFAULT '0',
`ObjID` int(11) DEFAULT NULL,
`StationID` int(11) DEFAULT NULL,
`EveType` int(11) DEFAULT NULL,
`Severity` int(11) DEFAULT NULL,
`ReportTime` date DEFAULT NULL,
`CreateTime` date DEFAULT NULL,
`EveContent` varchar(1024) DEFAULT NULL,
`EveDesc` varchar(256) DEFAULT NULL,
PRIMARY KEY (`NodeID`,`FltID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- MyISAM有索引
DROP TABLE `flt_evecurrent`;
CREATE TABLE `flt_evecurrent` (
`NodeID` int(11) NOT NULL DEFAULT '0',
`FltID` int(11) NOT NULL DEFAULT '0',
`ObjID` int(11) DEFAULT NULL,
`StationID` int(11) DEFAULT NULL,
`EveType` int(11) DEFAULT NULL,
`Severity` int(11) DEFAULT NULL,
`ReportTime` date DEFAULT NULL,
`CreateTime` date DEFAULT NULL,
`EveContent` varchar(1024) DEFAULT NULL,
`EveDesc` varchar(256) DEFAULT NULL,
PRIMARY KEY (`NodeID`,`FltID`),
KEY `ObjID` (`ObjID`),
KEY `CreateTime` (`CreateTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- InnoDB无索引
DROP TABLE `flt_evecurrent`;
CREATE TABLE `flt_evecurrent` (
`NodeID` int(11) NOT NULL DEFAULT '0',
`FltID` int(11) NOT NULL DEFAULT '0',
`ObjID` int(11) DEFAULT NULL,
`StationID` int(11) DEFAULT NULL,
`EveType` int(11) DEFAULT NULL,
`Severity` int(11) DEFAULT NULL,
`ReportTime` date DEFAULT NULL,
`CreateTime` date DEFAULT NULL,
`EveContent` varchar(1024) DEFAULT NULL,
`EveDesc` varchar(256) DEFAULT NULL,
PRIMARY KEY (`NodeID`,`FltID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- InnoDB有索引
DROP TABLE `flt_evecurrent`;
CREATE TABLE `flt_evecurrent` (
`NodeID` int(11) NOT NULL DEFAULT '0',
`FltID` int(11) NOT NULL DEFAULT '0',
`ObjID` int(11) DEFAULT NULL,
`StationID` int(11) DEFAULT NULL,
`EveType` int(11) DEFAULT NULL,
`Severity` int(11) DEFAULT NULL,
`ReportTime` date DEFAULT NULL,
`CreateTime` date DEFAULT NULL,
`EveContent` varchar(1024) DEFAULT NULL,
`EveDesc` varchar(256) DEFAULT NULL,
PRIMARY KEY (`NodeID`,`FltID`),
KEY `ObjID` (`ObjID`),
KEY `CreateTime` (`CreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL与MongoDB的性能测试对比

分享到：

在Ubuntu 10.04系统下安装MongoDB数据库
在Ubuntu 10.04系统下安装MongoDB数据库　　一、什么是MongoDB? 　　NOSQL指的是非关系型的数据库。随着互联网web2.0网站的兴起，传统的关系数据库在应付web2.0网站，特别是超大规模和高并发的SNS类型的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题，而非关系型的数据库则由于其本身的特点得到了非常...

MongoDB集群部署过程详解
MongoDB集群部署过程详解 Mongodb是时下流行的NoSql数据库，它的存储方式是文档式存储，并不是Key-Value形式。关于Mongodb的特点，这里就不多介绍了，大家可以去看看官方说明：http://docs.mongodb.org/manual/ 今天主要来说说Mongodb的三种集群方式的搭建：Replica Set / Sharding / Master...

php迷，一个php技术的分享社区，专属您自己的技术摘抄本、收藏夹。
在这里……

About

特别鸣谢使用协议

意见反馈网站投稿

数据库

MySQL与MongoDB的性能测试对比

Tags

About