Java SQL优化
我正在尝试使用SQL数据库和Java程序。我创建了一个7列宽、250万行的表（我需要构建的下一个表大约有2亿行）。我有两个问题：构建SQL表太慢（大约2000行/分钟），搜索数据库太慢（如果可能，我需要在一秒钟内找到超过1亿行，目前需要一分钟）。我尝试创建一个csv文件并导入它，但是I can't get it to work
我在我的计算机上使用xampp和phpMyAdmin（i5+6gb ram）。我正在测试三种方法：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();
}
}
对不起，这么久了。我已经想尽一切办法来加快速度，但我想不出来。有什么建议吗
# 1 楼答案
嗯，您可以做一些改进：
这就是我能帮到的，当然还有更多的建议
# 2 楼答案
至于插入速度，您需要在插入之前禁用所有索引，并在插入之后重新启用它们。有关提高批量插入速度的详细信息，请参见Speed of Insert Statements
查询速度可能受到CPU和磁盘速度的限制。你可能需要投入更多的硬件来解决这个问题
# 3 楼答案
因此，插入大量行的观点肯定是使用
Heap table，它是基本表，也被命名为持久页面数组，通常由CREATE TABLE创建，它对搜索无效，因为您的意思是搜索很慢，但插入非常有效，因为它将行添加到第一个空闲位置，即在表末尾查找或插入的行。但另一方面，搜索效率非常低，因为无法保证项目/行的种类所以，对于这一点，您应该创建一个表，以确保搜索是有效的。在这种情况下，如果您使用}我不太清楚，我不想告诉你最糟糕的事情。我并没有说MySQL和Oracle一样糟糕，只是没有提供一些物理实现技术，比如
Oracle，那么它提供了许多物理实现的构造，例如Index organized tables、Data clustering、Clustered tables-Index/Hash/sortedhash。。。SQL Server我不确定，但也有集群表和{Oracle所以，我的意思是，很难对这种方法提出一些建议，但是您认真思考和研究了数据库系统的物理实现，研究了关系代数以优化您的语句，您应该创建哪些类型的表，@duffymo的意思是，您可以通过
EXPLAIN PLANE FOR并基于结果来优化查询执行计划。还有如何使用indexes，这是一个强大的数据库构造，但每个索引都意味着对数据库的任何修改都需要更多的操作，因此需要重新考虑为哪个属性创建索引等通过Google，您可以找到许多关于数据建模、物理实现等的有用文章
问候你，祝你好运