Use limit to optimize queries and prevent SQL from being optimized

Table of Contents

  • Query optimization
    • 1.1 Optimization of maximum and minimum values
    • 1.2 Optimize limit paging
      • 1.2.1 Optimization using Associated Query
      • 1.2.2 Use range Query
      • 1.2.3 Query with unique increment sequence
  • Prevent being optimized
  • reference

Query optimization

1.1 Optimization of maximum and minimum values

MySQL is not optimized very well for MIN() and MAX() queries, for example

select MIN(id) FROM film where name = Journey to the West;Copy the code

Assume that the film data in the table is as follows:

id name price
1 hero 12
2 Which zha legend 14
3 Journey to the west 34
4 Water margin 23
5 A dream of red mansions 34
6 The red 2
7 The red 4
8 The little mermaid 23
9 Dad back 23
10 Who am I 12
11 Pleasant goat 56
12 Journey to the west 67

Where id is the primary key and is incremented, name is vARCHar and has no index

Because name has no index, because MySQL will perform a full table scan. Select * from ‘id’ where id =’ name ‘; select * from ‘id’;

select id FROM film where name = Journey to the West limit 1;Copy the code

When the first record is found, the query stops, achieving higher performance.

1.2 Optimize limit paging

When the system performs paging operations, when the offset is large, such as limit 1000020 and 20, MySQL needs to query 10020 records and only return 20 records, and all the previous records are discarded. This is very expensive

SELECT id, name, price FROM file LIMIT 10000 OFFSET 20
Copy the code

The SQL above I think is written in a regular way for pagination, which is nothing wrong and, as mentioned above, wastes a lot of performance.

1.2.1 Optimization using Associated Query

A simple way to optimize such queries is to use indexes to cover the scan as much as possible, rather than querying all columns, then doing an association as needed and returning the required columns. For large offsets, the efficiency gains are huge.

SELECT
    id, name, price
FROM film
INNER JOIN (
    SELECT id
    FROM film
    LIMIT 10000 OFFSET 20
    ) AS LIM USING(id)
Copy the code

1.2.2 Use range Query

LIMIT can sometimes be converted to a query for a known location, allowing MySQL to obtain the corresponding result through a range scan. For example, if there is an index on a positional column and the boundary values are precomputed, rewrite the query to:

SELECT id, name, price
FROM film
WHERE position BETWEEN 10000 AND 10020
ORDER BY position
Copy the code

1.2.3 Query with unique increment sequence

The only increment sequence here can be the increment ID primary key or any other number that has a unique and ascending order

In the previous thought, we are concerned with the number of pages and the number of pages per page. In some operations, we can pass in the increment sequence of the last query and then query the corresponding number of pages per page.

For example, the number of pages (pageIndex) and the number of pages (pageSize) are passed to the front end

select * from film 
limit (pageIndex - 1) * pageSize OFFSET pageIndex * pageSize
Copy the code

Instead, let the front end pass in the increment sequence (SID) and the number of pages per query (pageSize) for the last query.

For example, if sid is the id of film, then SQL can be rewritten to

select * from film 
where id > sid
limit pageSize
Copy the code

When the first page is checked, sid passes in 0, and when the second page is checked, sid gets the first page and finally gets the ID

Prevent being optimized

In addition to optimizing your SQL to reduce execution time, sometimes you need to prevent your SQL from being optimized for you by MySQL itself, resulting in execution results that are not what you expected.

When the group by statement is used in MySQL query, the first data is displayed when multiple values are met. For example:

Assume that the film data in the table is as follows:

id name price
1 hero 12
2 Which zha legend 14
3 Journey to the west 34
4 Water margin 23
5 Tang march 80
6 Tang march 50

SQL select * from film group by name

id name price
1 hero 12
2 Which zha legend 14
3 Journey to the west 34
4 Water margin 23
5 Tang march 80

But when some data is repeated, we tend to want the last item of data, which is what we would think through the form of sub-query first sorted after group by as follows:

select *
from ( select * from film order by id desc) as film_ordered
group by name;
Copy the code

The Derived table does not contain any of the following conditions:

  • UNION clause
  • GROUP BY
  • DISTINCT
  • Aggregation
  • LIMIT or OFFSET

According to the above description, we can use limit to prevent subquery optimization and rewrite the SQL

select *
from ( select * from film order by id desc limit 10000000) as film_ordered
group by name;
Copy the code

So that’s what we want

reference

Blog.csdn.net/lglaljj123/…