Let's talk about MySQL's loose index scan,

Let's talk about MySQL's loose index scan,

As we all know, InnoDB uses the index organization table (IOT), which is called the index organization table, and the leaf node stores all the data, which means that the data is always stored in a certain order. So the question is, what should be the execution of such a statement? The statement is as follows:

select count(distinct a) from table1;

If column a has an index, how can we scan it based on simple ideas? It is very easy to scan one by one. In this way, a full index scan is actually performed, which is very inefficient. This scan method will scan many duplicate indexes. In this case, the optimization method is also easy to think of: Skip duplicate indexes. So we can find such an optimization method on the Internet:

select count(*) from (select distinct a from table1) t;

From the information that has been searched, the extra in the execution plan changes from using index to using index for group-.

However, but, fortunately we have not used version 5.1, and most of them are Version 5.5 or above. These modern versions have implemented loose index scan:

It's good. You don't need to use this kind of tricks to optimize SQL.

The group by statement in this document is a bit interesting. The most popular method is to scan the entire table and create a temporary table, so that the execution plan will be ugly, there must be ALL and using temporary tables.

After 5.0, group by may use loose index scan under certain conditions,

CREATE TABLE log_table (id INT NOT NULL PRIMARY KEY,log_machine VARCHAR(20) NOT NULL,log_time DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);

1

SELECT MAX(log_time) FROM log_table；SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 1')；

These two sqls can be returned with only one index seek, because of the ordered sorting of the index. The optimizer realizes that min/max is located in the leftmost/rightmost block, thus avoiding range scanning;
Extra display Select tables optimized away;
2
Copy codeThe Code is as follows: select max (log_time) FROM log_table WHERE log_machine IN ('machine 1', 'machine 2', 'machine 3', 'machine 4 ');

The execution plan type is range (extra displays using where; using index), that is, the index range scan is executed. First, all records meeting the log_machine constraint are read, and then the max value is obtained through traversal;
Improvement
Copy codeThe Code is as follows: select max (log_time) FROM log_table WHERE log_machine IN ('machine 1', 'machine 2', 'machine 3', 'machine 4 ') group by log_machine order by 1 desc limit 1;
This satisfies the requirements for selecting loose index scan for group by. The execution plan extra displays using index for group-by, and the execution effect is equivalent

SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1')UnionSELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 2')…..

That is, execute loose index scan for each log_machine. rows is reduced from 82636 to 16 (a total of 1,000,000 records in this table ).

When does Group by use loose index scan?

Applicable Conditions:

1. Single Table operations
2 Group by use of the leftmost prefix column of the Index
3. Only clustering functions min ()/max () are supported ()
4. The column that appears in the Where condition must be = constant, and the index column that does not appear in group by must use constant.
5 prefix indexes are not supported, that is, some column indexes, such as index (c1 (10 ))
The execution plan's extra should display using index for group-
Assume that table t1 has an index idx (c1, c2, c3)

SELECT c1, c2 FROM t1 group by c1, c2; select distinct c1, c2 FROM t1; SELECT c1, MIN (c2) FROM t1 group by c1; SELECT c1, c2 FROM t1 WHERE c1 <const group by c1, c2; select max (c3), MIN (c3), c1, c2 FROM t1 WHERE c2> const group by c1, c2; SELECT c2 FROM t1 WHERE c1 <const group by c1, c2; SELECT c1, c2 FROM t1 WHERE c3 = const group by c1, c2SELECT c1, c3 FROM t1 group by c1, c2; -- loose index cannot be used

SELECT c1, c3 FROM t1 where c3 = const group by c1, c2; then you can

Compact index scan tight index scan
Group by cannot use loose index scan and can also select tight. If neither of them is available, only temporary tables can be used;
When scanning an index, you must read all the index keys that meet the conditions, either full index scan or range index scan;
The index column of Group by is not consecutive; or it does not start with the leftmost prefix, but the leftmost column appears in the where condition;

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

Improvement of 5.6
In fact, the 5.6 index condition push down can compensate for the performance loss caused by the missing loose index scan.
KEY (age, zip)

mysql> explain SELECT name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346, 12347);+----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra    |+----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+| 1 | SIMPLE   | people | range | age      | age | 4    | NULL | 90556 | Using where |+----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+1 row in set (0.01 sec)

According to key_len = 4, we can infer that SQL only uses the first column of the index, that is, the row records that meet age () are first identified through the index, then, filter the rows that meet the zip constraints from the server layer;
Pre-5.6, for composite indexes, the chance to use subsequent index columns during index scanning is only available when the bootstrap column uses "=.

mysql> explain SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347);+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra    |+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE   | people | range | age      | age | 8    | NULL |  3 | Using where |+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)

Compare query efficiency

mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345,12346, 12347);+----------------------------------+| name               |+----------------------------------+| 888ba838661aff00bbbce114a2a22423 |+----------------------------------+1 row in set (0.06 sec)mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN (12345,12346, 12347);+----------------------------------+| name               |+----------------------------------+| ed4481336eb9adca222fd404fa15658e || 888ba838661aff00bbbce114a2a22423 |+----------------------------------+2 rows in set (1 min 56.09 sec)

You can use union to rewrite the second SQL statement,

mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347)  -> UNION ALL  -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347)  -> UNION ALL  -> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347)  -> UNION ALL  -> SELECT name FROM people WHERE age=21 AND zip IN (12345,12346, 12347)  -> UNION ALL-> SELECT name FROM people WHERE age=22 AND zip IN (12345,12346, 12347);

In mysql5.6, index condition pushdown is introduced to solve this problem from the optimizer level.

Articles you may be interested in:

• Mysql creates constraints and indexes in Bitmap_Join_Indexes.
• Can the Create Index in MySQL Create a Primary Key?
• MySQL Create Index method and syntax structure and example
• MySQL startup error: File./mysql-bin.index not found (Errcode: 13)
• Usage of the string function substring_index intercepted by mysql
• How does MySQL export all indexes and constraints?