在开发中，我们经常会遇到需要查询大量数据的情况，为了提高查询速度，我们可以采用以下方法：
1、使用索引
CREATE INDEX index_name ON table_name(column_name);
2、使用连接池
//使用Druid连接池public class DruidUtil {private static DataSource dataSource = null;static {try {Properties properties = new Properties();properties.load(DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {return dataSource.getConnection();}}
3、批量操作
Connection conn = DriverManager.getConnection(url, user, password);Statement stmt = conn.createStatement();for (int i = 0; i< 10000; i++) {StringBuilder sql = new StringBuilder();sql.append("INSERT INTO test (name, age) VALUES ('name").append(i).append("', ").append(i).append(")");stmt.addBatch(sql.toString());}stmt.executeBatch();conn.close();
4、使用PreparedStatement
Connection conn = DriverManager.getConnection(url, user, password);PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM test WHERE name = ?");for (int i = 0; i< 100; i++) {pstmt.setString(1, "name" + i);ResultSet rs = pstmt.executeQuery();while (rs.next()) {System.out.println(rs.getInt("id"));}}conn.close();
5、分页查询
SELECT * FROM table_name LIMIT start_index, page_size;
通过以上方法，我们可以提高jdbc查询mysql的速度，同时也可以避免一些常见的性能问题。
