Original address:Blog.csdn.net/tanglei6636…

Group by:

Learn about group by optimization by using the following statement.

SELECT a.first_name,a.last_name,COUNT(*) FROM film_actor fa INNER JOIN actor a USING(actor_id) GROUP BY fa.actor_id;
Copy the code
  • 1



As shown in the figure, the SQL statement uses temporary tables and filesort. The use of temporary tables in our SQL should be reduced as much as possible. Since there is no WHERE condition in this SQL, it is inevitable to use ALL table scanning.

Optimization method:

EXPLAIN SELECT a.first_name,a.last_name,t.count FROM actor a INNER JOIN ( SELECT actor_id,COUNT(*) count FROM film_actor  fa GROUP BY actor_id ) t USING(actor_id);Copy the code
  • 1
  • 2
  • 3



Minimize the use of temporary tables and file sorting.

However, when the WHERE criteria increase, try to add the corresponding criteria to the sub-query, rather than adding the corresponding filtering criteria after all data query is completed.

Limit:

LIMIT is often used to handle paging operations and is often used with ORDER BY.

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;Copy the code
  • 1



When used in conjunction with ORDER BY, a common file ordering (filesort).

Optimization method:

1, Use indexed columns or primary keys to sort BY.

SELECT film_id,description FROM film ORDER BY film_id LIMIT 50,5;Copy the code
  • 1

This avoids the use of file sorting for ORDER BY operations and uses index searches instead of full table scans.

2, The above SQL will increase the number of rows scanned as the LIMIT changes. When the number of rows is too large, it still wastes a lot of resources to scan.



As shown, more rows are scanned.

Next, we use the primary key for filtering in this query by recording the primary key returned last time to reduce the number of scanned rows.

Suppose the last scan was: 5 lines from 50.

SELECT film_id,description FROM film WHERE film_id > 55 AND film_id <= 60 ORDER BY film_id LIMIT 1,5;Copy the code
  • 1



This can be handled by the logic of the code to reduce the number of scanned rows with each passing value, which is important when paging and avoids the resource waste of scanning too many records when there is a large amount of data.