mysql Loose index scan of Loose index scan

  • 2021-01-25 08:01:25
  • OfStack

The most efficient way to optimize Group is when you can use the index directly to fully retrieve the fields that require group. When using this access method, MySQL uses the type of the index sorted by the keyword (such as the BTREE index). This prevents the fields used for group in the index from having to fully cover the key corresponding to the index in the WHERE condition. Because only 1 part of the key in the index is included, it is called a loose index scan.

Historically, MySQL has not been able to do a loose index scan. This way it can scan non-contiguous parts of an index. In the following example, suppose there is 1 index on the column (a,b) and run the following query:

mysql > SELECT... FROM tbl WHERE b BETWEEN 2 AND 3;

Because the index starts at column a, but WHERE does not specify column a, MySQL will do a full table scan to eliminate mismatched rows.
You really want to have a fast way to execute the query. Some index structures (not ES32en) let you locate to the beginning of each range, scan to the end of the range, and then jump to the beginning of the next range.
This is of course a relatively simple example, but it could easily be done by adding a different index. In fact, a lot of times adding another index doesn't solve the problem. One example is some query that has a range condition on the first column of the index and an equivalence condition on the second column.
Starting with MySQL 5.0, loose index scans are possible under certain conditions, such as finding the maximum and minimum values in a grouped query:

mysql > EXPLAIN SELECT actor_id, MAX(film_id)
- > FROM sakila.film_actor
- > GROUP BY actor_idG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: range
possible_keys: NULL
key_len: 2
ref: NULL
rows: 396
Extra: Using index for group-by

Loose index scans can be used in the following situations:
The & # 8226; The query is for a single table.
The & # 8226; GROUP BY includes the first consecutive part of the index (if for GROUP BY, the query has one DISTINCT clause, then all DISTINCT attributes point to the beginning of the index).
The & # 8226; Only the cumulative functions (if any)MIN() and MAX() are used, and they all point to the same column.
The & # 8226; Any other parts of the index (except those GROUP BY from the query references) must be constant (that is, they must be referenced by a constant number), with the exception of arguments to the MIN() or MAX() functions.

Related articles: