How does MySQL skip ranges (nodes) in a loose index scan?
0
votes
2
answers
459
views
I've read the MySQL loose index scan, and it makes sense as optimization, however, when using an example B+tree, I don't understand how nodes can be efficiently skipped, due to potential matches causing many traverses to be performed.
Take this B+-tree:
+-------------+
|A |
+-----------------------------------------+ (5,2) (7,0) +----------------------------------------+
| | | |
| +------+------+ |
| | |
| | |
+------v------+ +------v------+ +------v------+
|B +---------------------------------->C +--------------------------------->D |
+--------+ (2,0) (2,2) +----------+ +---------+ (5,2) (5,4) +---------+ +--------+ (7,0) (8,1) +---------+
| | | | | | | | | | | |
| +------+------+ | | +------+------+ | | +------+------+ |
| | | | | | | | |
| | | | | | | | |
+------v------+ +------v------+ +-------v-----+ +-----v-------+ +------v------+ +-------v-----+ +------v------+ +------v------+ +-------v-----+
|E | |F | |G | |H | |I | |L | |M | |N | |O |
| (0,0) (1,0) +-> (2,0) (2,1) +--> (2,2) (3,0) +-> (4,0) (5,1) +-> (5,2) (5,3) +-> (5,4) (5,5) +-> (6,0) (6,1) +-> (7,0) (8,0) +-> (8,1) (8,2) |
| | | | | | | | | | | | | | | | | |
+-------------+ +-------------+ +-------------+ +-------------+ +-------------+ +-------------+ +-------------+ +-------------+ +-------------+
and the query SELECT (c1), MIN(c2) ... GROUP BY c1
.
Now, based on my understanding, a loose index scan will skip nodes when it is certain that a subtree doesn't include values that won't affect the (current) aggregate result.
With the tree above, I reckon the traversal will be:
- A
- B
- E
- find (0,0) (1,0)
- backtrack
- find (2,0)
- skip F
- G
- find (3,0)
- backtrack
- H
- find (4,0) (5,1)
- backtrack
- skip I
- L
- backtrack
- D
- M
- find (6,0)
- backtrack
- find (7,0)
- N
- find (8,0)
- O
Assuming that a backtrace has no cost, isn't in this example less expensive to perform a tight index scan (that is, navigate all the leaves directly)?
Is there any mistake in the traversal logic above? Or is this an excessively pessimistic (therefore, non representative) example?
Asked by Marcus
(390 rep)
Jan 21, 2020, 08:21 PM
Last activity: Jan 27, 2020, 05:06 PM
Last activity: Jan 27, 2020, 05:06 PM