Optimizing the query statement in Mysql database _mysql

Many times the site based on the php+mysql of the system can appear in the bottleneck is often out of MySQL, and MySQL in the most of the statements are query statements, therefore, for MySQL database query statement optimization is very important! This article makes a detailed analysis of this issue as follows:

1, to determine whether the MySQL database request for unwanted data, such as the following:

(1), the query does not need data, for example, you need 10 data, but you selected 100 data added limit to do the limit.
(2), multiple table associations return all columns
(3), always take out all the columns select* ... Removing all columns will allow the optimizer to not complete the optimizations such as index overlay scans, but also bring additional I/O, memory, and CPU consumption to the server
(4), repeated query the same data, for example, in the user comments where the user needs to query the head of the URL, then the user comments on the time to cache the data, when needed from the cache out, so performance will be better.

2. Whether MySQL is scanning for additional records

The simplest measure of the query cost is the following three metrics: response time , number of rows scanned , number of rows returned

Response time : Service time and queue time. Service time refers to the time that the database is actually spent processing the query. Queue time is a query that the server does not actually execute because it waits for some resources.

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

In general, MySQL can use the Where condition record in the following three ways, from good to bad in turn:

(1), use the Where condition in the index to filter unmatched records, complete at the storage index layer .

(2), Use index overlay scan to return records, filter unwanted records directly from the index and return the result of the hit, complete at the MySQL server layer, but do not need to query records in the back table .

(3), from the data table to return the data, and then filter the records that do not meet the conditions, the MySQL server layer to complete, you need to read out the data table and then filter

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

(1) use index overlay scans to put all required columns into the index so that the storage engine can return results without having to return a table to fetch the corresponding row .

(2), change the structure of the library table, using a separate summary table .

(3) rewrite this complex query

3, the way to reconstruct the query

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

MySQL internal to scan in memory per second millions data, in contrast, the MySQL response data to the client is much slower, in other conditions are the same, using as few queries as possible is certainly good, but sometimes it is necessary to decompose a large query into multiple small queries.

(2), segmentation query :

Deleting old data is a good example of a time when a large amount of data is cleared, and if you complete it once with a big statement, you can lock up a lot of data and fill up the whole thing log. Drain system resources and block a number of 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), Decomposition Association query:

Allows caching to be more efficient, allowing easy caching of individual data in an application
Execution of a single query can reduce the contention of the lock after the query is decomposed
It makes it easier to split the database at the application level, making it easier to perform high performance and scalability
The query itself will be more efficient.
Queries that reduce redundant data, and associate queries at the application level mean that only one query is needed for a single data application, and that querying in a database may require repeated access to some data.

Fit Scene:

① when an application can easily cache the results of a single query;
② when the data can be distributed to a different MySQL server;
③ when an in () way can be used in place of the associated query;
④ When a data table is used in a query.