Optimize Query statements in the MySQL database

Most of the time, the system performance bottleneck for websites built based on php + MySQL is often found in MySQL, and the most frequently used statements in MySQL are query statements. Therefore, optimization of MySQL database query statements is crucial! This article makes a detailed analysis of this problem as follows: 1. Determine whether unnecessary data is requested from the MySQL database, as shown in the following

Most of the time, the system performance bottleneck for websites built based on php + MySQL is often found in MySQL, and the most frequently used statements in MySQL are query statements. Therefore, optimization of MySQL database query statements is crucial! This article makes a detailed analysis of this problem as follows: 1. Determine whether unnecessary data is requested from the MySQL database, as shown in the following

Most of the time, the system performance bottleneck for websites built based on php + MySQL is often found in MySQL, and the most frequently used statements in MySQL are query statements. Therefore, optimization of MySQL database query statements is crucial! This article makes a detailed analysis of this problem as follows:

1. Determine whether unnecessary data is requested from the MySQL database, in the following cases:

(1) query unnecessary data. For example, if you need 10 data records, you have selected 100 data records and added the limit by limit.
(2) When multiple tables are joined, all columns are returned.
(3) always retrieve all columns select *...... retrieving all columns will make it impossible for the optimizer to complete index overwrite scanning. This will also cause additional I/O, memory, and cpu consumption on the server.
(4) Repeat the query of the same data. For example, if you need to query the URL of the user's profile picture in the user's comments, you can cache the data when making multiple comments, retrieve from the cache when necessary, so the performance will be better.

2. Is mysql scanning additional records?

The following three indicators are used to measure query Overhead: response time, number of scanned rows, and number of returned rows.

Response time: Service time and queue time. Service time refers to the actual time spent by the database to process this query. The queue time refers to the query that the server does not actually execute because it waits for some resources.

Number of scanned rowsAndNumber of returned rows: Ideally, the number of scanned rows is the same as the number of returned rows.

In general, MYSQL can use the following three methods to apply the where condition record, from good to bad:

(1) Use the where condition in the index to filter unmatched records, which is completed at the storage index layer.

(2) Use index overwrite scanning to return records, filter unwanted records directly from the index, and return hit results, which are completed at the mysql server layer, however, you do not need to query records in the returned table.

(3) return data from the data table, and then filter records that do not meet the conditions. At the mysql server layer, read the records from the data table and then filter them.

If you find that a query needs to scan a large amount of data but returns a few rows, you can usually try the following techniques:

(1) Use index overwrite scanning to put all required columns into the index, so that the storage engine can return results without returning the table to obtain the corresponding row.

(2) Change the database table structure and use a separate summary table.

(3) rewrite this complex Query

3. Rebuild the query method

(1 ),One complex query or multiple simple queries:

Mysql can scan millions of data records in the memory per second. In contrast, it is much slower for mysql to respond to data from the client. When other conditions are the same, it is better to use as few queries as possible, however, it is necessary to break a large Query into multiple small queries.

(2 ),Split Query:

The deletion of old data is a good example. When a large number of data is regularly cleared, if a large statement is used once, a lot of data may be locked at a time to occupy the entire transaction log. System resources are exhausted and many small but important queries are blocked.

Mysql> deletefrommessageswherecreated
 
  

  
Rewrite:
  

  
Rows_affected = 0; Do {Rows_affected = do_query ("deletefrommessageswherecreated
   
    

    
(3 ),Decomposition association query:
    
This improves the cache efficiency and allows you to conveniently cache a single piece of data in applications.
After the query is decomposed, executing a single query can reduce the lock competition.
Association at the application layer makes it easier to split databases and achieve high performance and high scalability.
The query itself is more efficient.
This can reduce the query of redundant data and perform associated queries at the application layer. This means that only one query is required for a data application, but a query in the database may require repeated access to some data.
    
Applicable scenarios:
    
① When the application can conveniently cache a single query result;
② When data can be distributed to different mysql servers;
③ When the IN () method can be used instead of the associated query;
④ When a data table is used in the query.