Optimize query detail in MySQL database

  • 2020-06-23 02:06:31
  • OfStack

Most of the time based on php+MySQL website appears in the system performance bottleneck is often on MySQL, and MySQL used the most statements is the query statement, therefore, MySQL database query statement optimization is crucial! This paper makes a detailed analysis of this issue as follows:

1. Determine whether unnecessary data is requested from THE MySQL database, as follows:

(1) Query unnecessary data. For example, you need 10 pieces of data, but you select 100 pieces of data and add limit as the limit.
(2) Return all columns in the case of multi-table association
(3) Always take out all columns. Pulling out all the columns prevents the optimizer from completing optimizations such as index coverage scans and imposes additional I/O, memory, and cpu consumption on the server
(4) Repeatedly query the same data. For example, if URL needs to query the user's avatar in the place of the user comment, the data will be cached when the user makes multiple comments and taken out of the cache when needed, so that the performance will be better.

2. Is mysql scanning additional records

The three simplest measures of query overhead are response time, number of rows scanned, and number of rows returned

Response time: service time and queuing time. Service time refers to the actual time taken by the database to process the query. Queue times are queries that the server is not actually executing because it is waiting for some resource.

Number of rows scanned and number of rows returned: Ideally, the number of rows scanned and the number of rows returned should be the same.

1 Generally MYSQL can apply where conditional records in the following three ways, from good to bad:

(1) The where condition is used in the index to filter the mismatched records, which is completed in the storage index layer.

(2) Use index overlay scan to return records, filter unwanted records directly from the index and return the hit results. This is done in the mysql server layer, but there is no need to query records in the back table.

(3) Return the data from the data table, and then filter the records that do not meet the conditions. It is completed in the mysql server layer, and the records shall be read from the data table and then filtered

If you find that a query needs to scan a large amount of data but return a small number of rows, you can usually try the following technique:

(1) Use index overlay scan to put all the required columns in the index, so that the storage engine can return the results without returning the table for corresponding rows.

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

(3) Rewrite this complex query

3. How to reconstruct the query

(1) One complex query or multiple simple queries:

Mysql internally scans millions of pieces of data per second in memory, whereas mysql responds much more slowly to the client. It is good to use as few queries as possible, all else being equal, but sometimes it is necessary to break down a large query into smaller ones.

(2) Sharding query:

Deleting old data is a good example. When a large amount of data is cleaned up on a regular basis, if done in one large statement once, many data may be locked at one time, occupying the entire transaction log. Deplete system resources and block many small but important queries.


Mysql>deletefrommessageswherecreated<DATE_SUB(NOW(),INTERVAL3MONTH);

Rewrite:


Rows_affected=0;
Do{
Rows_affected=do_query(
 " deletefrommessageswherecreated<DATE_SUB(NOW(),INTERVAL3MONTH) " ;
)
}

(3) Decompose associated query:

This makes caching more efficient and allows you to easily cache a single piece of data in your application
Once the query is decomposed, executing a single query can reduce lock contention
The correlation in the application layer makes it easier to split the database and achieve high performance and high scalability
The query itself is also more efficient.
It can reduce the redundant data query and do associated query in the application layer, which means that only one query is needed for a certain data application, while the query in the database may need to access 1 part of the data repeatedly.

Suitable scenario:

When the application can easily cache a single query result;
When the data can be distributed to different mysql servers;
When you can use IN() instead of associated queries;
When using a data table in the query.


Related articles: