MySQL 8.0 Introduces Descending Index: Analysis of InnoDB Backward Scan Performance Degradation

With Descending Index actually supported starting from MySQL 8.0, indexes can be efficiently utilized in sort queries mixing ascending (ASC) and descending (DESC) orders. In previous versions, only the syntax was supported, and actual creation of reverse indexes was impossible.

The reason Backward Index Scan is slower than Forward Index Scan in the InnoDB storage engine is primarily due to two structural characteristics. First, InnoDB's page locks (Latches) are designed to be acquired only in left-to-right (Forward) order in the B-Tree to prevent deadlocks, requiring a complex lock acquisition and release process during Backward scans. Second, records within pages are connected via a Single Linked List, so during a Backward Scan, after finding a slot through the Page Directory, an average of 2 to 4 loops are required to find the previous record.

The performance impact varies depending on the query pattern. When performing an Index Range Scan with random key values, there is approximately a 10% difference in throughput, with negligible differences in CPU usage. However, when intensively reading a specific part of the index (Hotspot), lock contention on pages causes up to a 44% difference in throughput and a significant increase in CPU usage.

Therefore, for queries that rarely retrieve a small number of records, reading existing Ascending Indexes via Backward Scan is acceptable. However, for frequently executed queries or cases where lock contention on specific index areas is expected, creating a Descending Index helps improve performance and reduce contention. Note that in environments where disk I/O is the bottleneck, these structural differences are offset in Latency, resulting in minimal impact.