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 .

©2019-2020 Toolsou All rights reserved,
html Writing about cherry trees , Writing about cherry trees It's unexpected Python Cherry tree (turtle The gorgeous style of Library ) Browser kernel ( understand )HashMap Explain in detail java Four functional interfaces ( a key , simple )os Simple use of module Some East 14 Pay change 16 salary , Sincerity or routine ?