Streamline statements to make MySQL more effective

BKJIA exclusive Translation: Simple teachingFast scalability Improvement

Through several simple rules, we can easily improve the scalability of the MySQL server. One of the following is an instance.

"The most effective way to improve SQL statements is to streamline them"

 

The method for streamlining SQL statements is a summary, but before listing the typical examples observed by the customer, please allow meBasic Prerequisites for improving scalabilityMake explanations.

The core of MySQL can only run a certain number of SQL statements in a given period of time, for example, per second.No matter how powerful your computer is, there is always an upper limit on computing this physical process. If you can streamline the SQL statements that are not critical or necessary, important SQL statements are automatically prioritized. Of course, this will also bring about other chain reactions, but it is only a small problem in the field of simple mathematics. In short,To run more SQL commands, first streamline your commands..

In this example, we use the mk-query-digest tool to analyze TCP/IP data packets and output the results.

 
 

  
  
# Rank Query ID           Response time Calls R/Call Apdx V/M   Item  
  
  
# ==== ================== ============= ===== ====== ==== ===== ==========  
  
  
#    1 0xD631CB919867DB50  0.0436 47.3%    92 0.0005 1.00  0.00 SELECT TTDOD  
  
  
#    2 0x04FE01C5B31FD305  0.0258 27.9%   329 0.0001 1.00  0.00 ADMIN PING  
  
  
#    3 0x93321857BCD8E771  0.0229 24.8%    36 0.0006 1.00  0.00 SELECT TTD 
 
 

There are many problems, including the one-row (RAT) feature of SQL, but we will not discuss the issue of ping too much here. First, let's look at the first statement.

 
 

  
  
SELECT `Date` FROM TTDOD WHERE ID = 9999; 
 
 

On the surface, this query command is concise enough, but let's look at the list again.

 
 

  
  
mysql> select count(*) from TTDOD;  
  
  
+----------+  
  
  
| count(*) |  
  
  
+----------+  
  
  
|        0 |  
  
  
+----------+ 
 
 

In this case, the query command does not return any content because the current list is empty. Of course, this may change in the future, but for the moment, this is more of an exception handling status in simple data management, because this list rarely contains content. The establishment of a targeted solution to notify the application can completely avoid such unnecessary query behavior.

The above is only a reference instance with less than 2 seconds of running time, and clearing the first query command has reduced the overall query time by 20%. Whether this is a typical loading process or a parallel loading process in batch processing, the principles are common. Moreover, there is no doubt that our streamlining work will be very promising in the following query commands.

Address: http://ronaldbradford.com/blog/simple-lessons-in-improving-scalability-2011-02-16/