MySQL slow query and query reconstruction method record

Preface

What is a slow query and how to optimize a slow query? The following introduces the relevant knowledge of these two knowledge points.

Slow Query Basics: Optimizing Data Access

Is unnecessary data requested from the database?

Query unnecessary records: Solution: Add Limit after the query

Return all columns when multiple tables are associated: Solution: Only take the required columns

Always retrieve all columns: select * Solution: It is better to retrieve partial columns unless the application caches the column data

Repeated query of the same data: Solution: Get it from the cache when needed

Is MySQL scanning additional records?

The query cost can be measured in terms of response time, number of rows scanned, and number of rows returned.

To reduce the number of scanned rows, you can use indexes to cover the required records. However, adding indexes does not mean that the number of scanned rows can be equal to the number of returned rows, such as aggregate functions such as sum and count.

How to restructure a query

One complex query or multiple simple queries

In the past, there was always an emphasis on the database layer doing as much work as possible because query parsing and optimization over network communications was an expensive endeavor.

But this idea does not apply to MySQL, which is designed to be lightweight in connecting and disconnecting, and efficient in returning small query results. Modern networks are much faster than before, both in terms of bandwidth and latency. On some versions of MySQL, even on a general-purpose server, it is possible to run over 100,000 queries per second, and even a Gigabit network card can easily satisfy over 2,000 queries per second. So running multiple small queries is not a big problem now.

Of course, compared to internal scanning, responding to data to the client is more time-consuming, so under the same conditions, it is better to use as few queries as possible.

Split query

When using SQL to query, you need to pay attention to splitting large queries into small queries to reduce the impact on the database. If a large statement is completed at one time, it may lock a lot of data at once, fill up the entire transaction log, exhaust system resources, and block other queries.

For example, when we do data statistics, we need to query the information of a large number of order numbers. Should we use IN query at one time, or cut the parameter list and query multiple times? The answer is to cut the parameter list and query multiple times. Because when there are too many parameters in IN, MySQL will think that it is no longer possible to use the index, and may perform a full table query. If the amount of data in the data table is too large at this time, it may cause the query to timeout.

// chops a list into non-view sublists of length L
static <T> List<List<T>> chopped(List<T> list, final int L) {
    List<List<T>> parts = new ArrayList<List<T>>();
    final int N = list.size();
    for (int i = 0; i < N; i += L) {
        parts.add(new ArrayList<T>(
            list.subList(i, Math.min(N, i + L)))
        );
    }
    return parts;
}
List<Integer> numbers = Collections.unmodifiableList(
    Arrays.asList(5,3,1,2,9,5,0,7)
);
List<List<Integer>> parts = chopped(numbers, 3);
System.out.println(parts); // prints "[[5, 3, 1], [2, 9, 5], [0, 7]]"
parts.get(0).add(-1);
System.out.println(parts); // prints "[[5, 3, 1, -1], [2, 9, 5], [0, 7]]"
System.out.println(numbers); // prints "[5, 3, 1, 2, 9, 5, 0, 7]" (unmodified!)

Truncate the list programmatically so that the query can use the index instead of a full table scan.

The Alibaba Development Manual recommends carefully evaluating the number of collection elements after in and keeping it within 1,000.

Decomposing a relational query

Decompose the inner join of multiple tables into small queries. If the join exceeds three tables, it needs to be prohibited.

Advantages include:

1. The cache will be more efficient.
2. Decomposed queries can reduce lock contention
3. The application layer can cache query data to reduce the pressure on the database.
4. It can improve query efficiency because in queries using primary keys and the like may be more efficient than queries based on conditional ranges, especially for large tables.
5. Can reduce the query of redundant records
6. Furthermore, this is equivalent to implementing a hash join in the application, rather than using MySQL's nested loop join. In some scenarios, hash association is much more efficient.

Summarize

This is the end of this article about MySQL slow query and query reconstruction. For more relevant MySQL slow query and query reconstruction content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!