Tips for optimizing MySQL SQL statements

When faced with a SQL statement that is not optimized enough or has extremely poor performance, we usually want to refactor the SQL statement so that the query result set remains the same as the original one, and hope that the SQL performance can be improved. When refactoring SQL, there are generally certain methods and techniques available for reference. This article will introduce how to refactor SQL using these techniques and methods.

1. Decomposing SQL

Sometimes, for a complex SQL, the first thing we think of is whether we need to decompose the complex SQL into multiple simple SQLs to achieve the same business processing results.

In the past, people always emphasized the need for the database layer to complete as much work as possible. This is why it is not difficult to understand why we often see many super-complex and super-long SQL statements in some old products and projects. The logic of doing so was previously considered to require multiple interactions, which was a very costly thing in terms of network bandwidth, network communication between programs and databases, etc. Now, whether in terms of bandwidth or latency, the network speed is much faster than before, and there are no major problems with multiple interactions. Even on a general-purpose server, it is possible to run more than 100,000 queries per second, so running multiple small queries is not a big problem now.

The decomposition of complex SQL statements can significantly improve performance when dealing with extremely complex SQL statements. Therefore, when faced with super complex SQL statements and there are performance issues, it is recommended to break them down into small queries for optimization .

However, when designing an application, if a query is sufficient and does not cause performance issues, it can be completed with a slightly more complex SQL. It is unwise to rigidly split it into multiple small queries.

In many high-performance application systems today, it is strongly recommended to use single-table operations and then associate the single-table query results in the application to meet the query requirements of complex businesses. Why write separate SQL statements when one can do the job? And why execute SQL queries multiple times in the application and then associate the result sets? Why do we need to do this?

At first glance, this seems complicated and has no benefit. Instead of a single query, it turns into multiple queries. In fact, this decomposition has the following advantages:

• Make caching more efficient. In an application, you can easily cache the result object corresponding to a single-table query result so that you can directly obtain data from the result object at any time later.
• After breaking down the query, executing a single query can reduce contention for table locks.
• By making associations at the application layer, it is easier to split the database and achieve high performance and scalability.
• Single-table query efficiency is higher than multi-table complex query.
• Reduce the query of redundant records. Association at the application layer means that the application only needs to query a certain record once, while doing an associated query in the database may require repeated access to some data records. From this point of view, such reconstruction may also reduce network and memory consumption.
  

2. Query segmentation

Sometimes, for a large query, that is, a query with a large result set, we need to adopt the idea of ​​"divide and conquer" and split the large query into small queries. Each query has exactly the same function, but only completes a small part and returns only a small part of the query results each time. In layman's terms, it is to split the filtering range of the where condition and query only part of the data each time, which is similar to paging query.

Doing so will only incur very little overhead, both for the SQL query itself and for upper-level services. The most typical case is paging query, which is well supported by various frameworks, such as MyBatis, etc. It can be avoided by paying a little attention in actual use.

3. Execution Plan

Using the EXPLAIN keyword in the execution plan allows us to know how MySQL executes SQL statements, which can help us analyze the performance bottlenecks of our query statements or table structures. The query results of EXPLAIN will also tell us how the index primary key is used, how the data table is searched or sorted, and so on.

The syntax format is:

EXPLAIN SELECT statement;

The execution plan results will guide us to further reconstruct SQL statements, such as adding indexes, adjusting index order, avoiding the use of certain functions, and so on.

Regarding the execution plan, the subsequent chapters will explain it in detail.

IV. Compliance with Principles

When writing SQL in daily life, if you develop good habits and pay more attention, you can avoid some SQL performance problems to a large extent. The summary is as follows:

• Always set an ID primary key for each table.
• Avoid using SELECT *.
• Index the search fields.
• When joining tables, use columns of corresponding types and index them.
• Use NOT NULL whenever possible.
• Smaller columns will be faster.
• Use LIMIT 1 when only one row of data is needed.
• Operator optimization tries not to use operators that are not conducive to indexing, in order to avoid full table scans.
  

1) Use in and not in with caution. Try to use between instead of in, and use not exists instead of not in.
2) Use is null and is not null with caution
3) Avoid using the != or <> operator if possible; otherwise the engine will abandon the use of the index and perform a full table scan.

5. Use query cache

When many identical queries are executed multiple times, the query results are put into a cache so that subsequent identical queries can access the cached results directly without any operations.

The MySQL query cache stores the complete results returned by queries. When a query hits the cache, MySQL returns the result like, skipping parsing, optimization, and execution truncation.

This is one of the most effective ways to improve query performance, and it is handled by the MySQL engine. Usually, MySQL does not enable query cache by default and needs to be enabled manually.

The query cache is completely transparent to the application. The application does not need to be concerned with whether MySQL returns the results through queries or actual execution. In fact, the results of these two methods are exactly the same. In other words, there is no syntax required to query the cache.

As current general-purpose servers become more powerful, query cache is found to be a factor affecting server scalability. It may become a single point of resource competition for the entire server and may even cause server deadlock on a multi-core server. Therefore, query cache should be turned off by default most of the time. If query cache is very useful, you can configure a small cache space of tens of megabytes. (When choosing, you need to make a trade-off)

The following parameters are available for query cache configuration:

• query_cache_type

Whether to enable query cache. You can set it to OFF, ON, or DEMAND. DEMAND means that only statements that are explicitly written to sql_cache in the query statement are put into the query cache.

• query_cache_size
  

The total memory space used by the query cache, in bytes. This value must be an integer multiple of 1024, 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 query results to cache. If the query result is larger than this value, it will not be cached. Because the query cache starts trying to cache data as it is generated, MySQL does not know whether the query results exceed the limit until all the results are returned.

Regarding query cache, the following chapters will explain it in detail separately.

The above is the details of MySQL optimization SQL statement techniques. For more information about MySQL optimization SQL statements, please pay attention to other related articles on 123WORDPRESS.COM!