The knowledge points of this article are as follows:
1. How to write dozens of lines of code can be simulated test high concurrent access to the Tens database table
2. Compare high concurrency (200 times/Second, 2000 times/second, 10,000 times/sec) database performance
3. Compare the Tens database with the large difference in index and non-index (in fact, I am surprised by the result of this test.)
For the 10 million data inserted in the previous article into the database, we conducted high concurrency under test (simulation teacher input name and password in 1 seconds to log into the database), the thread class code is as follows
Package insert; Import Java.sql.connection;import java.sql.drivermanager;import java.sql.resultset;import java.sql.SQLException; Import java.sql.Statement; public class Threadtomysql extends Thread {public String teachername; public String password; Public Threadtomysql (String teachername, string password) {//constructor passed in to query the teacher name and password for login This.teachername=teachern Ame This.password=password; public void Run () {String URL = "Jdbc:mysql://127.0.0.1/teacher"; String name = "Com.mysql.jdbc.Driver"; String user = "root"; String password = "123456"; Connection conn = null; try {class.forname (name); conn = drivermanager.getconnection (URL, user, password);//Get Connection Conn.setautocommit (FALSE);//Turn off autocommit, otherwise Conn.commi T () Run to this sentence will error} catch (ClassNotFoundException E1) {e1.printstacktrace (); } catch (SQLException e) {e.printstacktrace (); } if (conn!=null) {long starttime=system.currenttimemillis ();//Start time String sql= "SELECT ID from T_ Teacher where t_name= ' "+teachername+" ' and t_password= ' "+password+" ' ";//sql statement String Id=null; try {Statement stmt=conn.createstatement (); ResultSet rs=stmt.executequery (SQL);//Get result set if (Rs.next ()) {id=rs.getstring ("id"); } conn.commit (); Stmt.close (); Conn.close (); } catch (SQLException e) {e.printstacktrace (); } Long End=system.currenttimemillis (); System.out.println (CurrentThread (). GetName () + "Query results:" +id+ "Start Time:" +starttime+ "End Time:" +end+ "spents:" + (End-starttime) + " MS "); } else {System.out.println (CurrentThread (). GetName () + "database connection failed:"); } } }
The test class code is as follows:
Package insert; public class Testthreadtomysql {public static void Main (string[] args) {for (int i = 1; I <=2000; i++) { String teachername=string.valueof (i); New Threadtomysql (TeacherName, "123456"). Start (); }
I. Test without index:
Set the maximum number of connections for the database to 250:
Test code:
Package insert; public class Testthreadtomysql {public static void Main (string[] args) {for (int i = 1; I <=200; i++) { S Tring teachername=string.valueof (i); New Threadtomysql (TeacherName, "123456"). Start (); } } }
Test results:
More than 100 seconds ah ... My God, this user experience is also out O (∩_∩) o haha ~
Two. Add the index again after the high concurrency test:
The database index SQL statement is as follows: Here I have a question, last week I added index for half an hour I did not add the index I stopped, this afternoon only took 551 seconds to add up the index ... I don't understand.
Clean down the project code and run again (try to remove the cache as often as possible, otherwise the results will vary):
Have you been shocked to see this result? Haha add the index from more than 100 seconds to a millisecond, query speed hint 10,000 times, query performance has been greatly improved by the abnormal level ~ ~ ~
I have 2 seconds to query a single record without indexing.
Use explain to view the statement to be able to scan the whole table, the performance of course greatly decreased
Let's challenge 2000 threads to concurrently access the query database. Look at the results:
Set the maximum number of database connections to 2500
Test code changed to 2000
Package insert; public class Testthreadtomysql {public static void Main (string[] args) {for (int i = 1; I <=2000; i++) { String teachername=string.valueof (i); New Threadtomysql (TeacherName, "123456"). Start (); } } }
Results:
Performance is no problem, average dozens of milliseconds, very satisfied
Let's challenge 10,000 threads concurrently high concurrent access, you can first think about the results, haha
Setting the maximum number of database connections 12000
Change the test code to 10000 (prompt again.) Clean up the project to get rid of the cache, so the results are more accurate)
Package insert; public class Testthreadtomysql {public static void Main (string[] args) {for (int i = 1; I <=10000; i++) {
string teachername=string.valueof (i); New Threadtomysql (TeacherName, "123456"). Start (); } } }
The results are as follows (after running the computer found a little card):
The result is two kinds of error, 1. Connection request denied 2. Connection failed 3. But there's also a part of it that's successfully connected and running correctly
Then I see the maximum number of connection responses in the database:
You can see that even if your database is set to a higher level, your database server will not respond .... Response at most 5,758
More than 100 seconds ah ... My God, this user experience is also out O (∩_∩) o haha ~
Two. Add the index again after the high concurrency test:
The database index SQL statement is as follows: Here I have a question, last week I added index for half an hour I did not add the index I stopped, this afternoon only took 551 seconds to add up the index ... I don't understand.
Clean down the project code and run again (try to remove the cache as often as possible, otherwise the results will vary):
Have you been shocked to see this result? Haha add the index from more than 100 seconds to a millisecond, query speed hint 10,000 times, query performance has been greatly improved by the abnormal level ~ ~ ~
I have 2 seconds to query a single record without indexing.
Use explain to view the statement to be able to scan the whole table, the performance of course greatly decreased
Let's challenge 2000 threads to concurrently access the query database. Look at the results:
Set the maximum number of database connections to 2500
Test code changed to 2000
Send a wave of dry goods, test 2000 threads concurrently query 10 million database tables and index optimization