时序数据库QuestDB和TimescaleDB对比

一、springboot项目代码

       （1）QuestdbController:插入数据和查询数据

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    /**
     * @Describe 获取数据库的连接
     * @auther mofei
     * @date 2020年11月3日下午6:25:54
     */
    public static Connection getConnection() throws Exception{
        Properties properties = new Properties();
        properties.setProperty("user", "admin");
        properties.setProperty("password", "quest");
        properties.setProperty("sslmode", "disable");
        properties.setProperty("maxActive", "150");
        //设置连接最大超时时间300s
        properties.setProperty("connectTimeout", "600");
        properties.setProperty("socketTimeout", "600");
        properties.setProperty("rewriteBatchedStatements", "true");

        final Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:8812/qdb", properties);
        connection.setAutoCommit(false);
        return  connection;
    }

    //增删改资源的关闭
    public static void closeReource(Connection con, PreparedStatement ps){
        try {
            if(ps != null)
                ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if(con != null)
                con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //查询资源的关闭
    public static void closeResource(Connection con, PreparedStatement ps, ResultSet rs){
        try {
            if(con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public  static  String resultSetToJson(ResultSet rs) throws SQLException
    {
        // json数组
        JSONArray array = new JSONArray();
        // 获取列数
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        // 遍历ResultSet中的每条数据
        while (rs.next()) {
            JSONObject jsonObj = new JSONObject();

            // 遍历每一列
            for (int i = 1; i <= columnCount; i++) {
                String columnName =metaData.getColumnLabel(i);
                String value = rs.getString(columnName);
                jsonObj.put(columnName, value);
            }
            array.add(jsonObj);
        }

        return array.toString();
    }



}

@RequestMapping("/insert/{userid}")
    public void insertData(@PathVariable(name ="userid") String userid) throws Exception {
        System.out.println("开始执行:"+new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));
        long start = System.currentTimeMillis();
        Connection conn= JDBCUtils.getConnection();
        conn.setAutoCommit(false);
        String sql= "insert into tracepoint(tp_guid, pda_id,user_id,x,y,lng,lat,angle,speed,reporttime,type) " +
                "values(?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps= conn.prepareStatement(sql);
        for(int i=-480;i<0;i++){
            for(int index = 0; index < 5000; index++) {
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");//设置日期格式
                Calendar c = Calendar.getInstance();
                c.add(Calendar.DATE, i);
                Date end = c.getTime();
                String reportTime=df.format(end);
                ps.setString(1, "3");
                ps.setString(2, "C2940FE3AE644CB6A6080D6572EDAEFF");
                ps.setString(3, "S104U6693374828FA3D158B69189F0"+userid);
                ps.setDouble(4, 527768.577594348);
                ps.setDouble(5, 4629185.61306494);
                ps.setDouble(6, 123.33410580);
                ps.setDouble(7, 41.79738038);
                ps.setDouble(8, 73);
                ps.setDouble(9, 6.05581);
                ps.setString(10, reportTime);
                ps.setString(11, "GPS");
                ps.addBatch();
            }
        }
        int[] inserted1 = ps.executeBatch();
        conn.commit();
        JDBCUtils.closeReource(conn,ps);
        long end = System.currentTimeMillis();
        System.out.println("执行结束:"+new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));
        System.out.println("总耗时:" + (end - start) + " ms");
    }

    @RequestMapping("/queryquest")
    public int queryData() throws Exception {
        String res=null;
        long start = System.currentTimeMillis();
        Connection conn= JDBCUtils.getConnection();
            PreparedStatement ps = conn.prepareStatement(" SELECT * from 'tracepoint' where user_id='S104U6693374828FA3D158B69189F018' and reporttime>='2022-03-24' and  reporttime<'2022-03-25';") ;
        ResultSet rs = ps.executeQuery();
        int rowCount = 0;
        while(rs.next()) {
            rowCount++;
        }
        JDBCUtils.closeResource(conn,ps,rs);
        long end = System.currentTimeMillis();
        System.out.println("QuestDb查询结果总行数："+rowCount+",查询结果总耗时:" + (end - start) + " ms");
//        res=  JDBCUtils.resultSetToJson(rs);
//        JDBCUtils.closeResource(conn,ps,rs);
        return  rowCount;
    }

       （2）TimescaledbController:插入数据和查询数据

import lombok.var;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

@RestController
public class TimeScaleDbController {


    public  static Connection initConn(){
        Connection conn=null;
        String connUrl = "jdbc:postgresql://localhost:5435/example?user=postgres&password=jjgis123.0";
        try {
            conn = DriverManager.getConnection(connUrl);

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return  conn;
    }

    @RequestMapping("/createschema")
    private static void createSchema() throws SQLException {
        Connection conn=initConn();
        try (var stmt = conn.createStatement()) {
            stmt.execute("CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type TEXT NOT NULL, location TEXT NOT NULL)");
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER REFERENCES sensors (id), value DOUBLE PRECISION)");
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
        }
    }
    @RequestMapping("/insertdata")
    private static void insertData() throws SQLException {
        System.out.println(new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));
        for(int i=-480;i<0;i++){
            Connection conn=initConn();
            conn.setAutoCommit(false);
            String sql= "insert into tracepoint(tp_guid, pda_id,user_id,x,y,lng,lat,angle,speed,reporttime,positiontype) values(?,?,?,?,?,?,?,?,?,to_timestamp(?,'yyyy-mm-dd hh24:mi:ss.us'),?)";
            PreparedStatement ps= conn.prepareStatement(sql);
            for(int index = 0; index < 5000; index++) {
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");//设置日期格式
                Calendar c = Calendar.getInstance();
                c.add(Calendar.DATE, i);
                Date end = c.getTime();
                String reportTime=df.format(end);
                ps.setString(1, "3");
                ps.setString(2, "C2940FE3AE644CB6A6080D6572EDAEFF");
                ps.setString(3, "S174U6693374828FA3D158B69189F08E");
                ps.setDouble(4, 527768.577594348);
                ps.setDouble(5, 4629185.61306494);
                ps.setDouble(6, 123.33410580);
                ps.setDouble(7, 41.79738038);
                ps.setDouble(8, 73);
                ps.setDouble(9, 6.05581);
                ps.setString(10, reportTime);
                ps.setString(11, "GPS");
                ps.addBatch();
            }
            int[] inserted1 = ps.executeBatch();
            conn.commit();
            conn.close();
            ps.close();
            System.out.println(new SimpleDateFormat("yyyy/MM/dd-HH:mm:ss:SSS").format(new Date()));

        }
        System.out.println("执行结束");
    }

    @RequestMapping("/querytimescale")
    public int queryData() throws Exception {
        String res=null;
        long start = System.currentTimeMillis();
        Connection conn=initConn();
        PreparedStatement ps = conn.prepareStatement(" select * FROM public.tracepoint where user_id='S174U6693374828FA3D158B69189F08E' and reporttime >='2022-03-01'::timestamp and  reporttime <'2022-03-02'::timestamp;") ;
        ResultSet rs = ps.executeQuery();
        int rowCount = 0;
        while(rs.next()) {
            rowCount++;
        }
        conn.close();
        ps.close();
        rs.close();
        long end = System.currentTimeMillis();
        System.out.println("TimescaleDb查询结果总行数："+rowCount+",查询结果总耗时:" + (end - start) + " ms");
//        res=  JDBCUtils.resultSetToJson(rs);
//        JDBCUtils.closeResource(conn,ps,rs);
        return  rowCount;
    }
}

二、查询对比 测试环境 单机服务器 16g内存 4核cpu ；数据总量2100多万 

查询内容是每人每天的轨迹数（设置的是5000条数据）