10.3.13 Descending Indexes

10.3.13 Descending Indexes

MySQL은 descending index를 지원합니다. index 정의에서의 DESC는 더 이상 무시되지 않고 key 값을 내림차순으로 저장하게 합니다. 이전에는 index를 역순으로 스캔할 수 있었지만 성능상 불이익이 있었습니다. descending index는 더 효율적인 정방향 스캔이 가능하게 합니다.

또한 descending index를 사용하면, 가장 효율적인 스캔 순서가 일부 column에는 오름차순, 다른 column에는 내림차순이 섞여 있는 경우에도 옵티마이저가 멀티컬럼 index를 사용할 수 있습니다.

다음과 같이 두 개의 column과, 이 column에 대해 오름차순과 내림차순 index의 여러 조합을 포함하는 네 개의 two-column index 정의가 있는 table 정의를 살펴보겠습니다:

1CREATE TABLE t (
2  c1 INT, c2 INT,
3  INDEX idx1 (c1 ASC, c2 ASC),
4  INDEX idx2 (c1 ASC, c2 DESC),
5  INDEX idx3 (c1 DESC, c2 ASC),
6  INDEX idx4 (c1 DESC, c2 DESC)
7);

이 table 정의는 네 개의 서로 다른 index를 생성합니다. 옵티마이저는 각 ORDER BY 절에 대해 forward index 스캔을 수행할 수 있으며, filesort 작업을 사용할 필요가 없습니다:

1ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
2ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
3ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
4ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

descending index 사용에는 다음 조건이 적용됩니다:

• descending index는 InnoDB 스토리지 엔진에서만 지원되며, 다음과 같은 제한이 있습니다:

  • index에 descending index key column이 포함되어 있거나 primary key에 descending index column이 포함되어 있는 secondary index에 대해서는 change buffering이 지원되지 않습니다.

  • InnoDB SQL 파서는 descending index를 사용하지 않습니다. InnoDB 전체 텍스트 검색의 경우, index된 table의 FTS_DOC_ID column에 필요한 index는 descending index로 정의될 수 없음을 의미합니다. 자세한 내용은 Section 17.6.2.4, “InnoDB Full-Text Indexes”를 참조하십시오.

• ascending index가 가능한 모든 데이터 타입에 대해 descending index가 지원됩니다.

• descending index는 일반 column(nongenerated)과 generated column 둘 다(VIRTUAL과 STORED 모두)에 대해 지원됩니다.

• DISTINCT는 descending key part를 포함하는 index를 포함하여, 일치하는 column을 포함하는 어떤 index든 사용할 수 있습니다.

• descending key part를 가진 index는 집계 함수(aggregate function)를 호출하지만 GROUP BY 절이 없는 query의 MIN()/MAX() 최적화에는 사용되지 않습니다.

• descending index는 BTREE에 대해서는 지원되지만 HASH index에 대해서는 지원되지 않습니다. descending index는 FULLTEXT 또는 SPATIAL index에 대해서는 지원되지 않습니다.

HASH, FULLTEXT, SPATIAL index에 대해 ASC와 DESC를 명시적으로 지정하면 error가 발생합니다.

EXPLAIN 출력의 Extra column에서 옵티마이저가 descending index를 사용할 수 있음을 다음과 같이 확인할 수 있습니다:

1mysql> CREATE TABLE t1 (
2    -> a INT,
3    -> b INT,
4    -> INDEX a_desc_b_asc (a DESC, b ASC)
5    -> );
6
7mysql> EXPLAIN SELECT * FROM t1 ORDER BY a ASC\G
8*************************** 1. row ***************************
9           id: 1
10  select_type: SIMPLE
11        table: t1
12   partitions: NULL
13         type: index
14possible_keys: NULL
15          key: a_desc_b_asc
16      key_len: 10
17          ref: NULL
18         rows: 1
19     filtered: 100.00
20        Extra: Backward index scan; Using index

EXPLAIN FORMAT=TREE 출력에서는 index 이름 뒤에 (reverse)가 추가되어 descending index 사용이 다음과 같이 표시됩니다:

1mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 ORDER BY a ASC\G
2*************************** 1. row ***************************
3EXPLAIN: -> Index scan on t1 using a_desc_b_asc (reverse)  (cost=0.35 rows=1)

EXPLAIN Extra Information도 참조하십시오.