Java SQL优化

Question

我正在尝试在Java程序中使用SQL数据库。我制作了一个7列宽、250万行的表(我需要构建的下一个表大约有2亿行)。我有两个问题:构建SQL表太慢(大约2000行/分钟)和搜索数据库太慢(如果可能，我需要在一秒钟内找到超过1亿行，目前需要一分钟以上)。我已经尝试创建csv文件并导入它，但I can't get it to work。

我在我的电脑上使用xampp和phpMyAdmin (i5 +6 6gb内存)。我正在测试三个方法: createTable()、writeSQL()和searchSQL()。

createTable：

public static void createTable() {
    String driverName = "org.gjt.mm.mysql.Driver";
    Connection connection = null;
    try {
        Class.forName(driverName);

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    String serverName = "localhost";
    String mydatabase = "PokerRanks4";
    String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
    String username = "root";
    String password = "";

    try {
        connection = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    ///////////////
    String table = "CREATE TABLE ranks(deckForm bigint(10) NOT NULL,rank0 int(2) NOT NULL,rank1 int(2) NOT NULL,rank2 int(2) NOT NULL,rank3 int(2) NOT NULL,rank4 int(2) NOT NULL,rank5 int(2) NOT NULL,PRIMARY KEY (deckForm),UNIQUE id (deckForm),KEY id_2 (deckForm))";
    try {
        Statement st = connection.createStatement();
        st.executeUpdate(table);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    ///////////////

    try {
        connection.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

writeSQL()：

public static void writeSQL() {
    String driverName = "org.gjt.mm.mysql.Driver";
    Connection connection = null;
    try {
        Class.forName(driverName);

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    String serverName = "localhost";
    String mydatabase = "PokerRanks4";
    String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
    String username = "root";
    String password = "";

    try {
        connection = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }


    /////////////// Prepared Statement with Batch   
    PreparedStatement statement = null;
    String sql = "INSERT INTO ranks VALUES (? ,0, 0, 0, 0, 0, 0)";      
    long start = System.currentTimeMillis();
    try {
        statement = connection.prepareStatement(sql);
        for (int i = 0; i < 100; i++) {
            for (int j = 0; j < 100; j++) {
                statement.setLong(1, (i*100 + j));
                statement.addBatch();
            }
            System.out.println(i);
            statement.executeBatch();
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
      if (statement != null) {
        try {
          statement.close();
        } catch (SQLException e) {
        } // nothing we can do
      }
      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
        } // nothing we can do
      }       
    }
    System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
    ///////////////

}

searchSQL()：

public static void searchSQL() {
    String driverName = "org.gjt.mm.mysql.Driver";
    Connection connection = null;
    try {
        Class.forName(driverName);

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    String serverName = "localhost";
    String mydatabase = "PokerRanks2";
    String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
    String username = "root";
    String password = "";

    try {
        connection = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }



    /////////////// Option 1, Prepared Statement
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    String query = "SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = ?";
    long start = System.currentTimeMillis();
    try {
        pstmt = connection.prepareStatement(query);             
        for (int i = 0; i < 100000; i++) {              
            pstmt.setLong(1, 1423354957);
            rs = pstmt.executeQuery();
            while (rs.next()) {             
                int[] arr = {rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)};               
            }
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }                       
    System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
    ///////////////

    /*
    /////////////// Option 2
    Statement st = null;
    long start = System.currentTimeMillis(); 
    try {
        st = connection.createStatement();
        ResultSet rs = null;
        long deckForm = 1012213456;             
        for (int i = 0; i < 100000; i++) {          
            rs = st.executeQuery("SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = " + deckForm);
            while (rs.next()) {
                int[] arr = {rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)}; 
            }
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }        
    System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
    ///////////////
    */


    try {
        connection.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

抱歉，时间太长了。我已经尝试了我能想到的所有方法来使它更快，但我不能弄清楚。有什么建议吗？

Answer 1

好的，你可以做一些改进：

1. 您正在创建连接每次要搜索、写入或创建时，都应使用池连接并通过执行解释计划来解释您的查询，并优化表关系和索引。
2. 您可以使用存储过程并调用它们。

这就是我所能帮到的了，当然还有更多的建议。

Answer 2

至于插入速度，您需要在执行插入之前禁用所有索引，并在完成后重新启用它们。有关提高大容量插入速度的详细信息，请参阅Speed of Insert Statements。

查询速度可能受到CPU和磁盘速度的限制。你可能需要投入更多的硬件来解决这个问题。

Answer 3

构建SQL表太慢(大约2,000行/分钟)

因此，从插入大量行的角度来看，一定要使用Heap table，这是它的基本表，而且它被命名为持久性页面数组，通常是由CREATE TABLE创建的，它对于搜索是无效的，因为这意味着搜索很慢，但对于插入是非常有效的，因为它将行添加到第一个空闲位置，即查找到的位置或表的末尾。但另一方面，搜索效率非常低，因为不能保证排序的项/行。

搜索数据库太慢了(如果可能，我需要在一秒内找到超过一亿行，目前需要一分钟以上)

因此，您应该在中创建表，这是搜索是有效的。在一种情况下，如果你使用Oracle，所以它提供了许多物理实现的结构，例如Index organized tables，Data clustering，Clustered tables索引/哈希/排序哈希... SQL Server我不确定，但也有聚集表和MySQL我不确切知道，我不想告诉你一些最糟糕的事情。我并不是说MySQL不好或者像Oracle那样糟糕，只是没有提供一些物理实现的技术，比如Oracle

所以，我的意思是，对于这种方法很难说出一些建议，但是你认真地思考和研究了一些关于数据库系统的物理实现的东西，看过用于优化语句的关系代数，你应该创建哪种类型的表，@duffymo的意思是，你可以让EXPLAIN PLANE FOR来解释你的查询执行计划，并基于结果进行优化。另外，如何使用indexes，它强大的数据库结构，但每个索引意味着更多的操作，任何数据库的修改，所以很好地重新考虑为哪个属性创建索引等。

通过Google，您可以找到许多关于数据建模、物理实现等方面的有用文章。

关于男人，我祝你好运