Shulou(Shulou.com)06/01 Report--

This article brings you the content is about MySQL optimization of specific types of query methods to explain, there is a certain reference value, there are friends in need can refer to, I hope to help you.

Optimize associative queries

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

Make sure there is an index on the column in the ON or USING clause. When creating an index, consider the order of associations. When table A and table B are associated by column c, if the optimizer association order is B, A, then there is no need to index the corresponding column of table B. Unless there is another reason, you only need to create an index on the corresponding column of the second table in the associative order, and an unused index just adds extra burden.

Ensure that any GROUP BY and ORDER BY expressions refer only to columns in a table, so that MySQL can optimize the process using indexes.

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

Optimize GROUP BY and DISTINCT

In many cases MySQL optimizes both types of queries in the same way, and in fact the MySQL optimizer transforms both types of queries internally. They can all be optimized using indexes, which is also the most efficient optimization method.

When indexes are not available, GROUP BY uses two strategies to accomplish this: use temporary tables or file sorting for grouping. For any query statement, there is room for improvement in the performance of both strategies. We can prompt SQL_BIG_RESULT and SQL_SMALL_RESULT to get the optimizer to behave the way you want.

Optimize LIMIT Pagination

When paging operations are needed in the system, we usually use LIMIT plus offset method to implement it, and add the appropriate ORDER BY clause. If there is a corresponding index, it is usually efficient; otherwise, MySQL needs to do a lot of file sorting operations.

But there is a very common and headache problem is that when the offset is very large, such as LIMIT 10000,20 query, MySQL needs to query 10020 records and return only the last 20, which is very expensive.

If all pages are accessed equally often, then such a query requires, on average, access to half a table's data. To optimize this query, either limit the number of pages in the page or optimize performance for large offsets.

One of the easiest ways to optimize this type of paging query is to use index coverage scans whenever possible, rather than querying all columns. Then do an association operation according to the needs and finally return the required columns. For large offsets, this is much more efficient. Consider the following query:

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

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

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

The "deferred association" here will greatly improve query efficiency, it allows MySQL to scan as few pages as possible, get the records to be accessed, and then return all the columns required for the original table query according to the association.

Another optimization is to use the application to record the location of the last data and scan directly from that location on the next query, thus avoiding the use of OFFSET.

Using this approach turns it into a range query that performs well no matter how far you turn.

About MySQL optimization of specific types of queries will be shared here, of course, not only the above and everyone's analysis methods, but Xiaobian can ensure that its accuracy is absolutely no problem. Hope that the above content can have a certain reference value for everyone, can learn to apply. If you like this post, share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.