10.3.5 Column Indexes

10.3.5 Column Indexes

가장 일반적인 형태의 인덱스는 단일 컬럼을 대상으로 하며, 그 컬럼의 값들을 데이터 구조 안에 복사해 저장하여 해당 컬럼 값에 대응하는 로우를 빠르게 룩업할 수 있게 합니다. B-tree 데이터 구조는 =, >, ≤, BETWEEN, IN 등의 연산자와 함께 WHERE 절에서 사용되는 특정 값, 값의 집합, 값의 범위를 인덱스가 빠르게 찾을 수 있도록 해 줍니다.

테이블당 인덱스의 최대 개수와 인덱스 길이의 최대값은 스토리지 엔진별로 정의됩니다. Chapter 17, The InnoDB Storage Engine 및 Chapter 18, Alternative Storage Engines를 참조하십시오. 모든 스토리지 엔진은 테이블당 최소 16개의 인덱스와 최소 256 bytes의 총 인덱스 길이를 지원합니다. 대부분의 스토리지 엔진은 이보다 더 높은 제한을 가집니다.

컬럼 인덱스에 대한 추가 정보는 Section 15.1.18, “CREATE INDEX Statement”를 참조하십시오.

• Index Prefixes

• FULLTEXT Indexes

• Spatial Indexes

• Indexes in the MEMORY Storage Engine

Index Prefixes

string 컬럼에 대한 인덱스 정의에서 col_name(N) 시맨틱을 사용하면 컬럼의 처음 N 개 문자만 사용하는 인덱스를 생성할 수 있습니다. 이런 방식으로 컬럼 값의 프리픽스만을 인덱스하면 인덱스 파일을 훨씬 더 작게 만들 수 있습니다. BLOB 또는 TEXT 컬럼을 인덱스할 때는 인덱스에 대해 프리픽스 길이를 반드시 지정해야 합니다. 예를 들면 다음과 같습니다:

1CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

InnoDB 테이블 중 REDUNDANT 또는 COMPACT 로우 포맷을 사용하는 테이블의 경우 프리픽스는 최대 767 bytes까지 가능하며, DYNAMIC 또는 COMPRESSED 로우 포맷을 사용하는 InnoDB 테이블의 경우 프리픽스 길이 제한은 3072 bytes입니다. MyISAM 테이블의 프리픽스 길이 제한은 1000 bytes입니다.

참고

프리픽스 제한은 bytes 단위로 측정되지만, CREATE TABLE, ALTER TABLE, CREATE INDEX 스테이트먼트에서의 프리픽스 길이는 논바이너리 string 타입(CHAR, VARCHAR, TEXT)에 대해서는 문자 수로, 바이너리 string 타입(BINARY, VARBINARY, BLOB)에 대해서는 byte 수로 해석됩니다. 멀티바이트 문자 집합을 사용하는 논바이너리 string 컬럼에 대해 프리픽스 길이를 지정할 때는 이를 감안해야 합니다.

search term이 인덱스 프리픽스 길이를 초과하면, 인덱스는 일치하지 않는 로우를 제외하는 데 사용되고, 나머지 로우는 일치 가능성을 확인하기 위해 검사됩니다.

인덱스 프리픽스에 대한 추가 정보는 Section 15.1.18, “CREATE INDEX Statement”를 참조하십시오.

FULLTEXT Indexes

FULLTEXT 인덱스는 전체 텍스트 검색에 사용됩니다. InnoDB 및 MyISAM 스토리지 엔진만이 FULLTEXT 인덱스를 지원하며, 이때 컬럼은 오직 CHAR, VARCHAR, TEXT 타입이어야 합니다. 인덱스는 항상 컬럼 전체에 대해 수행되며, 컬럼 프리픽스 인덱싱은 지원되지 않습니다. 자세한 내용은 Section 14.9, “Full-Text Search Functions”를 참조하십시오.

특정 종류의 FULLTEXT 쿼리가 단일 InnoDB 테이블에 대해 수행될 때는 옵티마이제이션이 적용됩니다. 다음과 같은 특징을 가지는 쿼리는 특히 효율적입니다:

• document ID만, 또는 document ID와 search rank만 반환하는 FULLTEXT 쿼리.

• 일치하는 로우를 score의 내림차순으로 정렬하고, LIMIT 절을 적용하여 상위 N개의 일치 로우만 가져오는 FULLTEXT 쿼리. 이 옵티마이제이션이 적용되려면, WHERE 절이 없어야 하며 오직 하나의 ORDER BY 절만 존재해야 하고, 이 ORDER BY는 내림차순이어야 합니다.

• search term과 일치하는 로우의 COUNT(*) 값만 가져오고 추가적인 WHERE 절이 없는 FULLTEXT 쿼리. 이때 WHERE 절은 WHERE MATCH(text) AGAINST ('other_text')와 같이, > 0 비교 연산자 없이 작성해야 합니다.

full-text expression을 포함하는 쿼리에 대해서 MySQL은 쿼리 실행의 옵티마이제이션 단계 동안 이러한 expression을 평가합니다. 옵티마이저는 full-text expression을 보고 단순히 추정만 하는 것이 아니라, 실행 계획을 만드는 과정에서 실제로 이를 평가합니다.

이 동작이 의미하는 바는, 옵티마이제이션 단계 동안 expression 평가가 발생하지 않는 non-full-text 쿼리에 비해 full-text 쿼리에 대한 EXPLAIN이 일반적으로 더 느리다는 점입니다.

full-text 쿼리에 대한 EXPLAIN은 옵티마이제이션 과정에서 매칭이 발생하기 때문에 Extra 컬럼에 Select tables optimized away를 표시할 수 있으며, 이 경우 이후 실행 단계에서는 테이블 액세스가 전혀 필요하지 않습니다.

Spatial Indexes

spatial 데이터 타입에 대해 인덱스를 생성할 수 있습니다. MyISAM 및 InnoDB는 spatial 타입에 대해 R-tree 인덱스를 지원합니다. 다른 스토리지 엔진은(사실상 spatial 타입 인덱싱을 지원하지 않는 ARCHIVE를 제외하고) spatial 타입 인덱싱에 B-tree를 사용합니다.

Indexes in the MEMORY Storage Engine

MEMORY 스토리지 엔진은 기본적으로 HASH 인덱스를 사용하지만, BTREE 인덱스도 지원합니다.