Forward Index Scan vs. Backward Index Scan

MySQL Ascending index vs Descending index

MySQL 8.0 이전

• 문법은 지원했지만, Ascending Index로만 구현됨
• ORDER BY index_column DESC 역순 정렬이 필요한 경우, Ascending Index를 Backward 스캔

인덱스 정순 스캔 vs 인덱스 역순 스캔

• Forward Index Scan : 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
• Backward Index Scan : 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔
• 인덱스 페이지 블록 간의 Doubly Linked List를 통해 전진할 것인지 후진할 것인지의 차이 → 근데 왜 성능 차이?

Backward Index Scan이 왜 느릴까?

mysql> SELECT * FROM t1 ORDER BY tid ASC LIMIT 12619775,1;
1 row in set (4.14 sec)
1 row in set (4.15 sec)
1 row in set (4.15 sec)
1 row in set (4.14 sec)
1 row in set (4.15 sec)

mysql> SELECT * FROM t1 ORDER BY tid DESC LIMIT 12619775,1;
1 row in set (5.35 sec)
1 row in set (5.35 sec)
1 row in set (5.35 sec)
1 row in set (5.36 sec)
1 row in set (5.35 sec)

1) 페이지 잠금이 Forward Index Scan에 적합한 구조

void
btr_pcur_move_to_next_page(
/*=======================*/
    btr_pcur_t* cursor, /*!< in: persistent cursor; must be on the
                last record of the current page */
    mtr_t*      mtr)    /*!< in: mtr */
{
    // ... skip ...

    page = btr_pcur_get_page(cursor);
    next_page_no = btr_page_get_next(page, mtr);

    // ... skip ...

    buf_block_t*    block = btr_pcur_get_block(cursor);

    // 다음 페이지(next page)를 찾아서, 잠금 획득
    next_block = btr_block_get(
        page_id_t(block->page.id.space(), next_page_no),
        block->page.size, mode,
        btr_pcur_get_btr_cur(cursor)->index, mtr);

    next_page = buf_block_get_frame(next_block);

    // ... skip ...

    // 다음 페이지(next page) 잠금 획득후, 현재 페이지(이전 페이지)의 잠금을 해제
    btr_leaf_page_release(btr_pcur_get_block(cursor), mode, mtr);

    // ... skip ...
}

Forward Index Scan으로 페이지 잠금을 획득할 땐, 현재 페이지의 잠금을 획득하고 읽는다. 그리고 다음 페이지의 잠금을 획득한 후 현재 페이지의 잠금을 해제하는 방식이다.

mtr (mini-transaction) DML 작업 도중 내부 데이터 구조를 물리적인 수준에서 변경하는 InnoDB 작업의 내부 페이즈. 롤백 개념이 없다. 단일 트랜잭션 내에서 다수의 mtr이 발생할 수 있고, Crash Revovery에 사용되는 리두 로그에 정보를 기록한다. 백그라운드 스레드에 의한 purge 작업(일종의 가비지 컬렉션)과 같이, 일반적인 트랜잭션의 외부 컨텍스트에서 발생할 수도 있다.