Descending Index

1. Background

mysql 8.0 부터는 Desecending Index 가 지원이 된다.

기존 index 생성 시에 DESC 로 생성하여도 문법 상 오류가 발생하진 않지만, 실제로는 ASC 로 생성이 되었음.

8.0 부터는 DESC index 가 지원이 된다는 것.

내가 궁금한 것은 desc index 가 지원되면서 Backward Scan 의 성능 또한 향상되었는지 여부가 궁금했음.

또한 혹시나 하는 마음에.. ASC + DESC Index 를 가진 경우의 케이스도 궁금했음.

2. ASC + DESC INDEX

1) 아래와 같이 ASC + DESC 로 결합된 인덱스를 생성한다.

CREATE TABLE t4 (
  tid INT NOT NULL AUTO_INCREMENT,
  TABLE_NAME VARCHAR(64),
  COLUMN_NAME VARCHAR(64),
  ORDINAL_POSITION INT,
  KEY(tid),
  KEY ix_asc_desc (TABLE_NAME asc, COLUMN_NAME desc)
) ENGINE=InnoDB;

INSERT INTO t4 SELECT NULL, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM information_schema.COLUMNS;
INSERT INTO t4 SELECT NULL, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM t3; -- // 12번 실행

2) MySQL 5.7

실제 테이블에는 "KEY `ix_asc_desc` (`TABLE_NAME` ASC,`COLUMN_NAME` ASC)" 로 인덱스가 추가 됨.

> explain select * from t2 order by TABLE_NAME asc, COLUMN_NAME desc limit 100;
  => FULL TABLE SCANING
> explain select * from t2 order by TABLE_NAME asc, COLUMN_NAME asc limit 100;
  => INDEX SCANING

3) MySQL 8.0

실제 테이블에 "KEY `ix_asc_desc` (`TABLE_NAME` ASC, `COLUMN_NAME` DESC)" 로 인덱스가 추가 됨.

> explain select * from t2 order by TABLE_NAME asc, COLUMN_NAME desc limit 100;
  => INDEX SCANING
> explain select * from t2 order by TABLE_NAME asc, COLUMN_NAME asc limit 100;
  => FULL TABLE SCANING

4) 결론

8.0 에서 DESCENDING index 가 지원된다는 것은 ASC + DESC 같이 다른 방향을 사용하는 인덱스들의 결합 인덱스까지 지원한다.

ex. (ASC+DESC) key

 -> order by asc, desc = FULL SCAN (mysql 5.7)

 -> order by asc, desc = INDEX SCAN (mysql 8.0)

그리고 ASC+ASC 같이 이루어진 인덱스에서는 여전히 ASC, DESC 형태의 복합적인 정렬 쿼리를 이용할 수는 없음.

3. Backward Scan 성능

1. ASC
> SELECT * FROM asc_desc ORDER BY tid ASC  LIMIT 12619775,1;
1 row in set (2.85 sec)
1 row in set (2.84 sec)
1 row in set (2.83 sec)

2. DESC
> SELECT * FROM asc_desc ORDER BY tid DESC LIMIT 12619775,1;
1 row in set (3.59 sec)
1 row in set (3.60 sec)
1 row in set (3.63 sec)

-> 역시나 backward scan 이 더욱 느림.

4. 결론

8.0 의 new feature 로 제시된 Descending index 는 실제 index 를 만들 때 desc 방향의 인덱스도 만들 수 있다.

하지만 만들어진 인덱스의 backward scan 의 성능에 대한 보완은 이루어지 않았다. 

이 점은 생각하다보니 인덱스를 역방향으로 읽을 때, 인덱스 페이지의 첫부분부터 읽고 마지막 엔트리까지 찾아간 후 역방향 스캔을 하기때문에 당연한 이야기이네.. (desc index 로직과는 완전 무관한 ..)