Yyds dry inventory MySQL performance optimization (VI): common SQL optimization skills

xcbeyond 2022-01-27 06:58:00 阅读数:852

yyds dry inventory mysql performance

 Insert picture description here

author :xcbeyond
Blog :  https://xcbeyond.cn/ official account :  Program ape technology

In the face of not enough optimization 、 Or very poor performance SQL When the sentence is , Our usual idea is to refactor this SQL sentence , Keep the result set of its query the same as before , And hope SQL Improved performance . And refactoring SQL when , Generally, there are certain methods and techniques for reference , This article will show you how to refactor SQL.

One 、 decompose SQL

Sometimes for a complex SQL, The first thing we think about is whether we need Put a complex SQL Break it down into many simple SQL, To complete the same business processing results .

In the past , People always emphasize the need for a database layer to do as much work as possible , It's not hard to understand in some old products 、 There are often many super complex projects 、 Super long SQL sentence , The logic of doing so used to be that multiple interactions , In network bandwidth 、 Network communication between program and database is a very expensive thing . And now , Whether it's bandwidth or latency , The network speed is much faster than before , There's no big problem with multiple interactions . Even on a universal server , Can also run more than... Per second 10 Ten thousand inquiries , So running multiple small queries is no longer a big problem .

complex SQL Decomposition of , In the face of super complexity SQL When the sentence is , The performance improvement is particularly obvious . therefore , In the face of super complexity SQL sentence , And when there's a performance problem , It is recommended to decompose into small queries for optimization .

however , In application design , If a query is competent and does not cause performance problems , You can use a slightly more complicated SQL To complete , If again rigid force splits into many small queries is unwise .

<u> In today's many high-performance applications , It is highly recommended to use single table operation , Then the single table query results are associated in the application , To meet the query needs of complex business .</u> One SQL You can get things done , Why write separately , And it has to be executed multiple times in the application SQL Inquire about , Then we associate the result set , Why on earth should this be done ?

At first glance , It's complicated to do without saying and it's not good , Originally a query , It turns into multiple queries . in fact , This decomposition has the following advantages :

  • Make caching more efficient . In the application , It is very convenient to cache the result objects corresponding to single table query results , It is convenient to get data directly from the result object at any time in the future .
  • After decomposing the query , Executing a single query can reduce table lock contention .
  • Make connections at the application level , It's easier to split the database , It's easier to be high-performance and scalable .
  • Single table query is more efficient than multi table complex query .
  • Reduce redundant records queries . Associate... At the application level , It means that you only need to query a record once , And in the database to do associated queries , You may need to repeatedly access some data records . From this point of view , Such refactoring can also reduce network and memory consumption .

Two 、 Query segmentation

Sometimes for a big query , namely : The result set is very large , We need to adopt “ Divide and rule ” Thought , Divide big query into small query , Every query function is exactly the same , Just a small part of it , Only a small number of query results are returned at a time . Popular speaking , That's right where The filter range of the condition is segmented , Only a part of the data is queried at a time , namely : Similar to paging queries .

To do so , No matter for SQL Query itself , Or for the upper business , It's all a small expense . The most typical case is paging query , At present, all kinds of frameworks have good support , Such as :MyBatis etc. , It can be avoided by paying little attention to the actual use .

3、 ... and 、 Implementation plan

Use execution plan EXPLAIN keyword , Can let us know MySQL How to execute SQL Of the statement , This can help us analyze the performance bottleneck of our query statement or table structure .EXPLAIN The query result of will also tell us how the index primary key is used , How data tables are searched or sorted .... wait .

The syntax is :


By implementing the plan results , Will guide us further to refactor SQL sentence , Such as : Add index 、 Adjust index order 、 Avoid using certain functions and so on .

About the implementation plan , The following chapters will explain in detail .

Four 、 observe a principle

Write... At ordinary times SQL when , Form good habits , Pay more attention to , To a large extent, it will avoid some SQL Performance issues . The summary is as follows :

  • Always set one... For each table ID Primary key .

  • Avoid using SELECT *.

  • Index search fields .

  • stay Join Use columns of the corresponding type when using tables , And index it .

  • Use as much as possible NOT NULL.

  • The smaller the column, the faster .

  • Use when there is only one line of data LIMIT 1.

  • Operator optimization , Try not to use operators that are not conducive to index , The goal is to avoid full table scanning .

    1)in and not in Use with caution , As far as possible with between Instead of in, use not exists Instead of not in

    2)is null and is not null Use with caution

    3)!= or &lt;&gt; Operators can be used without , Otherwise, the engine will discard the index and scan the whole table .

  • ……

5、 ... and 、 Use query caching

When many of the same queries are executed multiple times , These query results will be put into a cache , In this way, the subsequent same query can directly access the cached results without any operation .

MySQL The query cache holds the full results returned by the query . When the query hits the cache ,MySQL Meeting like Return results , Skipped parsing 、 Optimize and perform truncation .

This is one of the most effective ways to improve query performance , And this is being MySQL The engine handles , Usually MySQL Query caching is not enabled by default , Manual opening required .

The query cache is completely transparent to the application . Applications don't need to care MySQL Is it returned by query or by actual execution . in fact , The results of the two methods are exactly the same . let me put it another way , The query cache does not need to use any syntax .

With the current general-purpose servers becoming more and more powerful , Query caching was found to be a factor in server scalability . It can become a single point of resource competition for the entire server , On a multi-core server, it can also cause the server to freeze . So most of the time you should turn off query caching by default , If the query cache is very useful , You can configure tens of megabytes of small cache space .( When choosing , A trade-off is needed )

The query cache has the following parameters to configure :

  • query_cache_type

    Whether to open query cache . You can set OFFONDEMAND,DEMAND Indicates that only the query statement explicitly writes sql_cache The statement is put into the query cache .

  • query_cache_size

    Total memory used by query cache , Unit is byte . This value must be 1024 Integer multiple , Otherwise, the actual allocated data will be different from the specified size .

  • query_cache_min_res_unit

    The minimum unit of memory allocated in the query cache .

  • query_cache_limit

    Maximum cached query results . If the query result is greater than this value , Will not be cached . Because query caching starts to try to cache data when data is generated , So only when all the results are returned ,MySQL To know if the query results exceed the limit .

About query caching , The following chapters will explain in detail .

copyright:author[xcbeyond],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/01/202201270657556653.html