实例测试Mysql使用索引带来的效率提升
[sql]
CREATE DATABASE `sql_learn_db`;
www.2cto.com
创建一个表：
[sql]
Create Table: CREATE TABLE `persons` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`LastName` varchar(255) DEFAULT NULL,
`FirstName` varchar(255) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表的结构： www.2cto.com
[plain]
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| LastName | varchar(255) | YES | | NULL | |
| FirstName | varchar(255) | YES | | NULL | |
| Address | varchar(255) | YES | | NULL | |
| City | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
利用JDBC插入 1000000条数据。
DBIndexTest.java:
(请手动修改用户名和密码，并导入驱动包)
[java]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Random;
public class DBIndexTest {
private static final String MYSQL_DRIVER = "com.
mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost:3306/sql_learn_db";
private static final String USER_NAME = "root";
private static final String PASSWORD = "";
private static final String sql = "insert into persons values (null, , ,'zjut','hangzhou')";
private static Connection conn = null;
private static PreparedStatement pstmt = null;
private static Random random = new Random();
private DBIndexTest(){};
public static String getRandomName() {
int fornum = 1 + random.nextInt(10); /* 1~10 */
StringBuilder sb = new StringBuilder(); /* 97~122 */
for(int i=0; i
sb.append((char)(97 + random.nextInt(26)));
}
return sb.toString();
}
private static void createConnection() {
try {
Class.forName(MYSQL_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER_NAME,PASSWORD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
if(conn == null) {
createConnection();
}
return conn;
}
public static void insertRecord() {
conn = getConnection();
try {
if(pstmt == null)
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, getRandomName());
pstmt.setString(2, getRandomName());
int affect = pstmt.executeUpdate();
System.out.println(affect == 1 "插入成功!" : "插入失败!");
} catch (SQLException e) {
// TODO Auto-generated c