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,
@Repository The role of annotations Three methods of value transfer between non parent and child components SpringBoot JpaRepository Database addition, deletion, modification and query python Dynamic programming for single source shortest path vue-countTo Complete operation use PyMC3 Bayesian statistical analysis was performed ( code + example )( Essence )2020 year 6 month 26 day C# Class library GUID Help class SQL Server Database Glossary Programmer and architect Hua Shan Science fiction comes true !“ Trisomy ” Found out