MySQL slow SQL and index case

Written in the first

About the start and configuration of slow SQL before I clean up the article:

Http://www.cnblogs.com/hanxiaobei/p/5515624.html

Prerequisite Preparation:

tomcat7.x

Mysql--perfteach

Jmeter

Test interface: 192.168.1.102:8081/perfteach/slowquery?cardno=1000009

Script writing:

Single interface test JMeter is more convenient, so this article is tested with JMeter

Environment Construction and testing:

1, configure the Perfteach package to connect the local database (Perfteach library has 10,000 data)

2. Start Tomcat

./startup.sh & Tail-f. /logs/catalina.  out

3, the browser to verify whether the interface, see the following interface for the interface has been

This article only does an index test of the Cardno field

4, JMeter set to 20 threads, 1 seconds to start, cycle forever

At the same time, the server monitors

5, when the Cardno field index is not added , the result of the test is about 120 TPS. As shown

At the same time, if you turn on the slow query log, you will see the constant logs adding data. If the file size continues to increase

To see specifically which SQL execution is problematic, open the address that was written at the top of the link.

6, Add Cardno field index , the results of the test TPS is about 600. As shown

At the same time, the size of the slow query log does not change. Prove the index worked.

7. TPS to begin adding Cardno index and then remove Cardno index:

The descent was obvious.

This article is just a comparison analysis of the situation with or without indexes from the TPS perspective.

There is also the condition of CPU and memory under the condition of index, and the state of CPU and memory without index.

MySQL slow SQL and index case