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 的建表语句：

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

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

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

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

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

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;