MySQL optimizes specific types of queries (code example)

The content this article brings to you is about MySQL optimizing specific types of queries (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Optimize associated queries

If you want to optimize queries that use associations, we need to pay special attention to the following points:

1. Ensure that ON or USING There are indexes on the columns in the clause. The order of associations needs to be taken into consideration when creating indexes. When table A and table B are related using column c, if the optimizer's association order is B, A, then there is no need to build an index on the corresponding column of table B. Unless there are other reasons, you only need to create an index on the corresponding column of the second table in the association sequence. An unused index will only bring additional burden.

2. Make sure that any expressions in GROUP BY and ORDER BY only involve columns in one table, so that MySQL can use indexes to optimize this process.

3. When upgrading MySQL, you need to pay attention to: association syntax, operator precedence and other places that may change.

Optimizing GROUP BY and DISTINCT

In many scenarios, MySQL uses the same method to optimize these two queries. In fact, the MySQL optimizer will handle it internally. time to convert these two types of queries to each other. They can all be optimized using indexes, which is also the most effective optimization method.

When the index cannot be used, GROUP BY is accomplished using two strategies: using temporary tables or file sorting for grouping. For any query statement, the performance of both strategies can be improved. We can prompt SQL_BIG_RESULT and SQL_SMALL_RESULT to make the optimizer behave the way you want.

Optimize LIMIT paging

When paging operations need to be performed in the system, we usually use LIMIT plus offset method to implement it, and add appropriate ORDER BY clause. If there is a corresponding index, the efficiency will usually be good. Otherwise, MySQL needs to do a lot of file sorting operations.

But a very common and troublesome problem is that when the offset is very large, for example, it may be a query like LIMIT 10000,20. At this time, MySQL needs to query 10020 records and only return the last one. 20 items, the price is very high.

If all pages are accessed with the same frequency, then such a query needs to access half of the table's data on average. To optimize such queries, either limit the number of pages in the page or optimize performance for large offsets.

One of the simplest ways to optimize this type of paging query is to use index coverage scans as much as possible instead of querying all columns. Then perform a correlation operation as needed and finally return the required columns. When the offset is large, the efficiency of this will be much improved. Consider the following query:

SELECT file_id, description FROM sakila.film
ORDER BY title LIMTI 50, 5;

If this table is large, it is best to modify this query to look like this:

SELECT file.file_id, file.description
FROM sakila.film
    INNER JOIN(
        SELECT film_id FROM sakila.film
        ORDER BY title LIMIT 50, 5
    )

The "delayed association" here will greatly improve the query efficiency. Let MySQL scan as few pages as possible, obtain the records that need to be accessed, and then return all the columns needed to query the original table according to the associated columns.

There is another optimization method, which is to use the application to record the location of the last data. In the next query, you can start scanning directly from the recorded location, thus avoiding the use of OFFSET.

Using this method can turn it into a range query, and its performance will be very good no matter how far back the page is turned.

The above is the detailed content of MySQL optimizes specific types of queries (code example). For more information, please follow other related articles on the PHP Chinese website!