NoSql数据库mongoDB和MySQL的比较

发布时间：2020-12-13 13:49:35 所属栏目：百科来源：网络整理

导读：下面是代码： 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; State

下面是代码：

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,'%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","");

        List<Thread> threads = new ArrayList<Thread>();
        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","");
                        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,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);
        }
    }
}

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,3);
        Date beginTime = calendar.getTime();
        calendar.set(2010,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<Thread> threads = new ArrayList<Thread>();
        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`),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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- InnoDB有索引
DROP TABLE `flt_evecurrent`;
CREATE TABLE `flt_evecurrent` (
  `NodeID` int(11) NOT NULL DEFAULT '0',KEY `CreateTime` (`CreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!