Shulou(Shulou.com)06/01 Report--

MySQL index performance test blog Document structure diagram:

I haven't studied MySQL for a long time, and I have been working on oracle research. Recently, I have to pick up MySQL again to have a look. I remember two MySQL articles published last year:

MySQL 5.6.21 download installation download article (1): http://blog.itpub.net/26736162/viewspace-1349705/

MySQL 5.6.21 Download Installation (II): http://blog.itpub.net/26736162/viewspace-1349787/

Today we will look at the performance test of indexes in mysql: 1 Prepare the environment 1.1 Create a test table in the database test1

[root@rhel6_lhr ~]# mysql -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 14

Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| db1 |

| db4 |

| lhr_test |

| mysql |

| opensource |

| opesource |

| performance_schema |

| test |

| wyzc |

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

10 rows in set (0.00 sec)

mysql> use lhr_test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table test1(

-> id int,

-> num int,

-> pass varchar(50)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> desc test1;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | YES | | NULL | |

| num | int(11) | YES | | NULL | |

| pass | varchar(50) | YES | | NULL | |

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

3 rows in set (0.00 sec)

mysql> create table test2(

-> id int,

-> num int,

-> pass varchar(50),

-> index idIdx (id)

-> );

Query OK, 0 rows affected (0.24 sec)

mysql> create table test3(

-> id int,

-> num int,

-> pass varchar(50)

-> );

reset query cache;Query OK, 0 rows affected (0.09 sec)

1.2 Execute the following statement at the system prompt to create 1 million lines of data. Execute the command on the operating system to generate 100W pieces of data, where lhr in-plhr is the password of mysql, lhr_test is the database name, and the command is a whole command: for ((i=1; i/tmp/mysql.txt 2>&1

After a long wait to check the data, I can't stand it anymore. Let's do the test directly at 20W:

2 Simple test index performance 2.1 Executing queries with and without indexes

mysql> select * from test1 limit 10;

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

| id | num | pass |

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

| 1 | 1 | c4ca4238a0b923820dcc509a6f75849b |

| 2 | 3 | c81e728d9d4c2f636f067f89cc14862c |

| 3 | 4 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |

| 4 | 5 | a87ff679a2f3e71d9181a67b7542122c |

| 5 | 5 | e4da3b7fbbce2345d7772b0674a318d5 |

| 6 | 6 | 1679091c5a880faf6fb5e6087eb1b2dc |

| 7 | 10 | 8f14e45fceea167a5a36dedd4bea2543 |

| 8 | 12 | c9f0f895fb98ab9159f51fd0297e236d |

| 9 | 12 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |

| 10 | 12 | d3d9446802a44259755d38e6d163e820 |

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

10 rows in set (0.00 sec)

mysql> reset query cache; ---Empty cache

Query OK, 0 rows affected (0.18 sec)

mysql> select num,pass from test1 where id>=50000 and id insert into test2 select * from test1;

Query OK, 225494 rows affected (2.53 sec)

Records: 225494 Duplicates: 0 Warnings: 0

mysql> reset query cache;

Query OK, 0 rows affected (0.00 sec)

mysql> explain select num,pass from test2 where id>=50000 and id explain select num,pass from test1 where id>=50000 and id reset query cache;

Query OK, 0 rows affected (0.00 sec)

mysql> select num,pass from test2 where id>=50000 and id reset query cache;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 select * from test1;

Query OK, 225494 rows affected (1.67 sec)

Records: 225494 Duplicates: 0 Warnings: 0

mysql>

3 Summary table name

table properties

Query (in seconds)

Insertion (in seconds)

test1

indexless

0.15

test2

indexed

0.00

2.53

test3

indexless

1.67

Conclusion: Under normal circumstances, there is an index in the case of query faster, insert slower, so in a large number of data import operations should first delete the index, after the completion of the data import index, due to in-depth study of oracle, feel that these seem to be nonsense here, but seriously doing experiments is an attitude, there are always some you do not know in this blog, o($>_$>)o haha.

.............................................................................................................................

The author of this article: wheat seedlings, only focus on database technology, pay more attention to the application of technology

ITPUB BLOG：http://blog.itpub.net/26736162

Address: http://blog.itpub.net/26736162/viewspace-1466094/

QQ: 642808185 Note: ITPUB Article Title

.............................................................................................................................

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.