Efficiency improvement caused by using indexes in Mysql for instance testing _ MySQL

Instance testing improves the efficiency of using indexes in Mysql bitsCN.com

Improved efficiency of using indexes for Mysql instances

Create a database:

[SQL]

Create database 'SQL _ learn_db ';

Create a table:

[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

Table structure:

[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 |

+ ----------- + -------------- + ------ + ----- + --------- + ---------------- +

Use JDBC to insert 1000000 data records.

DBIndexTest. java:

(Please manually modify the user name and password and import the driver package)

[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.exe cuteUpdate ();

System. out. println (affect = 1? "Inserted successfully! ":" Insertion failed! ");

} Catch (SQLException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

}

}

Public static void main (String [] args ){

Long start = System. currentTimeMillis ();

For (int I = 0; I <1000000; I ++ ){

InsertRecord ();

}

Long end = System. currentTimeMillis ();

System. out. println ("Total time:" + (end-start)/1000.0 + "s ");

}

}

Running result:

[Plain]

Inserted successfully!

Inserted successfully!

Inserted successfully!

...

...

...

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Inserted successfully!

Total time: 2167.19 s

Number of inserted records in the table:

[SQL]

Mysql> select count (*) from persons;

+ ---------- +

| Count (*) |

+ ---------- +

| 1, 1000000 |

+ ---------- +

When no index is created for the LastName column of the table, try to query:

[SQL]

Mysql> select count (*) from persons where lastname = 'abc ';

+ ---------- +

| Count (*) |

+ ---------- +

| 7 |

+ ---------- +

1 row in set (6.33 sec)

Create an index for the table column LastName:

[SQL]

Mysql> create index my_index on persons (lastname );

Query OK, 0 rows affected (12.44 sec)

Records: 0 Duplicates: 0 Warnings: 0

Execute the same query again:

[SQL]

Mysql> select count (*) from persons where lastname = 'abc ';

+ ---------- +

| Count (*) |

+ ---------- +

| 7 |

+ ---------- +

1 row in set (0.00 sec)

Of course, 0.00 sec is not equal to 0, because the speed is too fast and the unit is too large (in seconds). This result is returned after rounding (in milliseconds, it may not be 0 ).

The results of each execution are not necessarily the same. However, from this small experiment, we can see that the index-based table is a little faster than the non-index-based table, but it is not absolute. Whether or not to use the index technology depends on the specific problem. If a table has a large number of queries, you should increase the index. However, indexes will decrease the table update speed accordingly (because indexes must be updated at the same time ).

BitsCN.com