When completing the query task , Queries take time in different places , Include network ,CPU calculation , Generate statistics and execution plan , Lock wait （ Mutex wait ） Etc , In particular, the call operation to retrieve data from the underlying storage engine , These calls require in memory operations ,CPU Caused by insufficient operation and memory I/O Time consuming in operation . Depending on the storage engine , There may also be a large number of context switches and system calls .
In every query case that consumes a lot of time , We can all see some unnecessary extra operations , Some operations are repeated many times in addition , Some operations are too slow, etc . The purpose of optimizing queries is to reduce and eliminate the time taken by these operations .
One more point , For the entire life cycle of a query , The above list is not complete . Here we just want to explain ： Understanding the lifecycle of queries , It is of great significance to know the time consumption of query for optimizing query . With these concepts , Let's see how to optimize the query .
In us explain When , commonly MYSQL Can be applied in the following three ways WHERE condition , From good to bad ：
* Use in index WHERE Condition to filter unmatched records . This is done at the storage engine level .
* Use index override scan （ stay Extra Column appears Using
index） To return records , Filter unnecessary records directly from the index and return hit results . This is in MySQL Server layer completed , But there is no need to return the query results .
* Return data from data table , Then filter the records that do not meet the conditions （ stay Extra Appears in column Using
Where）. This is MySQL Server tier complete ,MySQL The records need to be read out from the data table and then filtered .
There are many times , We explain When I saw it ,MySQL Scanning a lot of data , But only a few lines were returned , Then you can usually try the following techniques to optimize it ：
* Use index override scan , Use overlay index
* Change library table structure . For example, use a separate summary （ This is where we are 4 Methods discussed in Chapter ）
* Rewrite this complex query , Give Way MySQL The optimizer can execute this query in a more optimized way .
How to refactor queries
1. A complex query or multiple simple queries
One of the issues to consider when designing queries is , Whether a complex query needs to be divided into multiple simple queries . In traditional implementation , Always emphasize the need for the database layer to do as much as possible , The logic of this is that we used to think about network communication , Query parsing and optimization is a costly task . But the idea is that MySQL Not applicable ,MySQL It's designed to be lightweight to connect and disconnect , Efficient in returning a small query result . Modern network speed is much faster than before , Whether it's bandwidth or latency . Earlier versions of MySQL upper , Even on a common server , Can also run more than 10 10000 queries , Even a gigabit network card can easily meet the requirements of more than 2000 Queries of . So running multiple small queries is no longer a big problem .
however , When applying design , It is unwise to write multiple independent queries when one query is competent . for example , We see that some applications do 10 Sub independent query to return 10 Row data , One result per query , query 10 second !
Basis of query execution
When optimizing MySQL Ability to run queries with higher performance , The best way is to find out MySQL How to optimize and execute queries . Once you understand that , A lot of query work actually i Some principles should be followed to make the optimizer work in a reasonable way .
let me put it another way , It's time to look back at what we discussed earlier ：MySQL The process of executing a query . According to the figure below, we can see the direction MySQL When sending a request ,MySQL What did the truth do ：
* Client sends a query to server .
* The server checks the query cache first , If the cache is hit , Results stored in the cache are returned immediately . Otherwise, go to the next stage
* On the server side SQL analysis , Pretreatment , The optimizer then generates the corresponding execution plan .
* MySQL Execution plan generated by optimizer , Calling the storage engine's API To execute a query .
* Return the result to the client
Every step above is more complicated than you think . We'll talk about it later in our blog . We will see what state the query is in at each stage . The query optimizer is a particularly complex and difficult part . There are many exceptions , for example , When the query uses bound variables , The execution path will be different , We will discuss later .