MySQL performance test (index)

First, use Talend to randomly generate 10 million data:

There are now 10 million + data in the database table:

Mysql> Select Count(*) fromZhangchao;+----------+| Count(*)|+----------+| 10040005 |+----------+1Rowinch Set(2.50Sec

The time to query a record is 3.02s without indexing

Mysql> Select *  fromZhangchaowhereX="bq2i2t";+--------+--------+|X|Y|+--------+--------+|bq2i2t|Drt5mx|+--------+--------+1Rowinch Set(3.02sec) MySQL>ShowCreate TableZhangchao;+-----------+------------------------------------------------------------------------------------------------ ------------------------------------+| Table     | Create Table                                                                                                                       |+-----------+------------------------------------------------------------------------------------------------- -----------------------------------+|Zhangchao| CREATE TABLE' Zhangchao ' (' x 'varchar( -)DEFAULT NULL, ' y 'varchar( -)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=Latin1|+-----------+------------------------------------------------------------------------------------------------- -----------------------------------+1Rowinch Set(0.00Sec

Creating an index for the X column of table Zhangchao takes 45.43s:

Mysql> Alter TableZhangchaoAdd Index(x); Query OK,0Rows Affected (45.43sec) Records:0Duplicates:0Warnings:0MySQL>ShowCreate TableZhangchao;+-----------+------------------------------------------------------------------------------------------------- ----------------------------------------------------+| Table     | Create Table                                                                                                                                        |+-----------+------------------------------------------------------------------------------------------------- ----------------------------------------------------+|Zhangchao| CREATE TABLE' Zhangchao ' (' x 'varchar( -)DEFAULT NULL, ' y 'varchar( -)DEFAULT NULL,  KEY' x ' (' X ')) ENGINE=InnoDBDEFAULTCHARSET=Latin1|+-----------+------------------------------------------------------------------------------------------------- ----------------------------------------------------+1Rowinch Set(0.00Sec

In executing the same query, the time is 0.09s:

Mysql> Select *  fromZhangchaowhereX="bq2i2t";+--------+--------+|X|Y|+--------+--------+|bq2i2t|Drt5mx|+--------+--------+1Rowinch Set(0.09Sec

and non-indexed performance differences: 3.02/0.09 = 33.555 times times

MySQL performance test (index)