MySQL optimized group by index loose scan and compact scan

The GROUP BY statement uses the B+tree index scan to be divided into two cases: loose scan, compact scan.

Loose Index Scan (loosely scanned) is just a small portion of the key value that needs to be taken out of the index scan to complete a scan of the entire SQL required data, where the range predicate or unconditional, SQL statement can be used to complete the query with the index to use the loose scan, There are several types of statements that are scanned with a loose index:

Table T1 has c1,c2,c3,c4 four fields, index is (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, C2;

As shown on the official website for the use of a loosely indexed scan, for example, I have a table with millions of of the data, the establishment of a three-field index.

Mysql>select Count (*) from history_20150425;
+--------------------+
| COUNT (*)
+--------------------+
| 7880599
+--------------------+
A total of 1 rows of records were returned, costing 1734.54 Ms.

Mysql>alter Table history_20150425 Add index (DOVALUE,PREVALUE,TASKID);

Mysql> explain select Dovalue,prevalue from history_20150425 Group by Dovalue,prevalue;

+----+-------------+----------------------------------+-------+---------------+--------------+---------+------+ -------+------------

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+----------------------------------+-------+---------------+--------------+---------+------+ -------+------------

| 1 | Simple | history_20150425 | Range | Dovalue | Dovalue | 8 | NULL | 65552 | Using index for group-by |

+----+-------------+----------------------------------+-------+---------------+--------------+---------+------+ -------+------------

1 row in Set (0.00 sec)

In the extra column appears using the index for group-by the word means that the use of the loose index scan, the other situation is not posted out, a bit of a waste of the layout, you can make their own according to the type of the official website to try.

Tight index Scan (compact index Scan), reading the loose index scan is a good understanding of this compact index scan, that is, all the data needs to be read in order to satisfy the data required by the query. We ruled out a compact scan with our hands loosely scanned, which is still an example of an official document:

For example, table T1 has a C1,C2,C3,C4 field, and Index contains a C1,C2,C3 field

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;

Like these two statements that can only scan an index fragment in order to get accurate data, you cannot use a loose index scan.

GROUP BY if it is not possible to use the index scan, the internal order by sorting is then grouped when using temporary table groupings, where an order by NULL can be used to force an internal sort operation to be canceled.

This article is from the "Yuanzhan" blog, make sure to keep this source http://xiaozhong991.blog.51cto.com/2354914/1759122

MySQL optimized group by index loose scan and compact scan