MySQL与JDBC性能优化全解析

### MySQL与JDBC性能优化全解析 #### 1. Connector/J性能测试 在开发一个大部分执行时间都用于访问数据库的应用程序时，需要找到平衡以实现最佳工作环境。我们先来看看Connector/J 3.0和2.1版本的性能情况。 以下是用于性能测试的代码： ```java import java.sql.*; import java.util.Date; import java.text.DateFormat; public class Performance{ Connection connection; public Performance() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception e) { System.err.println("unable to load driver"); } try { connection = DriverManager.getConnection( "jdbc:mysql://192.168.1.25/products?user=spider&password=spider"); } catch(SQLException e) { System.out.println("SQLException: " + e.getMessage()); System.out.println("SQLState: " + e.getSQLState()); System.out.println("VendorError: " + e.getErrorCode()); } } public void run() { long startTime; try { PreparedStatement ps = connection.prepareStatement( "INSERT INTO product VALUES(null, 'title', 5.54, 'supplier', null, ?)"); startTime = new Date().getTime(); for (int i=0;i<1000;i++) { ps.setInt(1, i); ps.executeUpdate(); } System.out.println("INSERT = " + ((new Date().getTime()) - startTime)); Statement statement = connection.createStatement(); startTime = new Date().getTime(); ResultSet rs = statement.executeQuery("SELECT * FROM product"); while (rs.next()) { } rs.close(); statement.close(); System.out.println("SELECT = " + ((new Date().getTime()) - startTime)); ps = connection.prepareStatement( "UPDATE product SET inventory=10 WHERE inventory = ?"); startTime = new Date().getTime(); for (int i=0;i<1000;i++) { ps.setInt(1, i); ps.executeUpdate(); } System.out.println("UPDATE = " + ((new Date().getTime()) - startTime)); connection.close(); } catch(SQLException e) { } } public static void main(String[] args) { Performance test = new Performance(); test.run(); } } ``` 测试是针对使用以下`create table`命令定义的表进行的： ```sql mysql> create table product(id int auto_increment primary key, string varchar(32), test double, supplier varchar(128), ts timestamp, value int); ``` 在不同环境下执行上述代码，得到的结果如下： | 环境 | 插入1000行时间 | 检索1000行时间 | 更新1000行时间 | | --- | --- | --- | --- | | MySQL 3.23 - 52 - NT，1.4GHz Pentium 4，512MB RAM，Connector/J 3.01 beta | 734毫秒 | 20毫秒 | 13,063毫秒（13.063秒） | | MySQL 4.0.4，P166，256MB RAM，客户端通过100MB LAN连接，Connector/J 3.01 beta | 741毫秒 | 50毫秒 | 12,358毫秒（12.358秒） | | 1.4GHz机器，Connector/J 2.1 | 726毫秒 | 22毫秒 | 12,645毫秒（12.645秒） | | P166，Connector/J 2.1 | 761毫秒 | 40毫秒 | 12,448毫秒（12.448秒） | 从这些数据可以看出，切换到Connector/J 2.1版本，性能数字变化不大。 #### 2. 数据库调优 如果系统运行缓慢，需要检查数据库性能，确保其在运行的机器上达到最佳水平。以下是一些数据库调优的方法： ##### 2.1 服务器选项 在Windows机器上首次安装MySQL时，可以参考安装基目录中提供的几个配置文件示例，将其中一个配置文件复制到根目录。Unix版本虽不提供示例配置文件，但查看这些文件中设置的服务器参数，有助于了解小型系统和大型系统的配置差异。 以下是不同服务器规模对应的参数设置表格： | 服务器规模 | key_buffer_size | max_allowed_packet | thread_stack | table_cache | sort_buffer | net_buffer_length | thread_cache_size | | --- | --- | --- | --- | --- | --- | --- | --- | | 小型 | 16K | 1MB | 64K | 4 | 64K | 2K | NA | | 中型 | 16MB | 1MB | NA | 64 | 512K | 8MB | | | 大型 | 256MB | 1MB | 256 | NA | 1MB | 64MB | 8 | | 巨型 | 384MB | 1MB | 512 | NA | 2MB | 128MB | 8 | 各参数解释如下： - `key_buffer_size`：用于索引块的总内存，应约为系统总内存的四分之一。 - `max_all