15.1.18 CREATE INDEX Statement

15.1.18 CREATE INDEX Statement

1CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
2    [index_type]
3    ON tbl_name (key_part,...)
4    [index_option]
5    [algorithm_option | lock_option] ...
6
7key_part: {col_name [(length)] | (expr)} [ASC | DESC]
8
9index_option: {
10    KEY_BLOCK_SIZE [=] value
11  | index_type
12  | WITH PARSER parser_name
13  | COMMENT 'string'
14  | {VISIBLE | INVISIBLE}
15  | ENGINE_ATTRIBUTE [=] 'string'
16  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
17}
18
19index_type:
20    USING {BTREE | HASH}
21
22algorithm_option:
23    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
24
25lock_option:
26    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

일반적으로, 테이블 자체를 CREATE TABLE로 생성할 때 해당 테이블의 모든 인덱스를 생성합니다. Section 15.1.24, “CREATE TABLE Statement”을 참조하십시오. 이 가이드는 프라이머리 키가 데이터 파일에서 행의 물리적 레이아웃을 결정하는 InnoDB 테이블의 경우 특히 중요합니다. CREATE INDEX를 사용하면 기존 테이블에 인덱스를 추가할 수 있습니다.

CREATE INDEX는 인덱스를 생성하기 위해 ALTER TABLE 스테이트먼트로 매핑됩니다. Section 15.1.11, “ALTER TABLE Statement”을 참조하십시오. CREATE INDEX는 PRIMARY KEY를 생성하는 데 사용할 수 없습니다. 대신 ALTER TABLE을 사용하십시오. 인덱스에 대한 추가 정보는 Section 10.3.1, “How MySQL Uses Indexes”를 참조하십시오.

InnoDB는 가상 컬럼에 대한 세컨더리 인덱스를 지원합니다. 자세한 내용은 Section 15.1.24.9, “Secondary Indexes and Generated Columns”를 참조하십시오.

innodb_stats_persistent 설정이 활성화된 경우, 해당 테이블에 인덱스를 생성한 후 InnoDB 테이블에 대해 ANALYZE TABLE 스테이트먼트를 실행하십시오.

key_part 지정의 _expr_는 (CAST json_expression AS type ARRAY) 형태를 취할 수도 있으며, 이를 통해 JSON 컬럼에 멀티값 인덱스를 생성합니다. Multi-Valued Indexes를 참조하십시오.

(key_part1, key_part2, ...) 형태의 인덱스 지정은 여러 키 파트를 가진 인덱스를 생성합니다. 인덱스 키 값은 지정된 키 파트 값들을 이어 붙여서 형성됩니다. 예를 들어 (col1, col2, col3)은 col1, col2, col3의 값으로 구성된 키를 갖는 멀티컬럼 인덱스를 지정합니다.

key_part 지정은 ASC 또는 DESC로 끝날 수 있으며, 이를 통해 인덱스 값이 오름차순 또는 내림차순으로 저장되는지 지정합니다. 정렬 지정자를 제공하지 않으면 기본값은 오름차순입니다.

ASC와 DESC는 HASH 인덱스, 멀티값 인덱스 또는 SPATIAL 인덱스에서는 지원되지 않습니다.

다음 섹션에서는 CREATE INDEX 스테이트먼트의 다양한 측면을 설명합니다:

• Column Prefix Key Parts

• Functional Key Parts

• Unique Indexes

• Full-Text Indexes

• Multi-Valued Indexes

• Spatial Indexes

• Index Options

• Table Copying and Locking Options

Column Prefix Key Parts

문자열 컬럼의 경우, col_name(length) 구문을 사용하여 컬럼 값의 앞부분만 사용하는 인덱스를 생성할 수 있으며, 이를 통해 인덱스 프리픽스 길이를 지정합니다:

• 프리픽스는 CHAR, VARCHAR, BINARY, VARBINARY 키 파트에 대해 지정할 수 있습니다.

• 프리픽스는 BLOB 및 TEXT 키 파트에 대해서는 반드시(must) 지정해야 합니다. 추가로, BLOB 및 TEXT 컬럼은 오직 InnoDB, MyISAM, BLACKHOLE 테이블에서만 인덱스를 생성할 수 있습니다.

• 프리픽스 _limit_는 바이트 단위로 측정됩니다. 그러나 CREATE TABLE, ALTER TABLE, CREATE INDEX 스테이트먼트의 인덱스 지정에서 프리픽스 _length_는 논바이너리 문자열 타입(CHAR, VARCHAR, TEXT)에 대해서는 문자 수로, 바이너리 문자열 타입(BINARY, VARBINARY, BLOB)에 대해서는 바이트 수로 해석됩니다. 멀티바이트 문자 집합을 사용하는 논바이너리 문자열 컬럼에 대해 프리픽스 길이를 지정할 때 이를 고려하십시오.

프리픽스 지원 여부 및 프리픽스 길이(지원되는 경우)는 스토리지 엔진에 따라 달라집니다. 예를 들어, InnoDB 테이블에서 REDUNDANT 또는 COMPACT 로우 포맷을 사용하는 경우 프리픽스는 최대 767바이트까지 가능합니다. DYNAMIC 또는 COMPRESSED 로우 포맷을 사용하는 InnoDB 테이블에서는 프리픽스 길이 제한이 3072바이트입니다. MyISAM 테이블에서는 프리픽스 길이 제한이 1000바이트입니다. NDB 스토리지 엔진은 프리픽스를 지원하지 않습니다( Section 25.2.7.6, “Unsupported or Missing Features in NDB Cluster” 참조).

지정된 인덱스 프리픽스가 컬럼 데이터 타입의 최대 크기를 초과하는 경우, CREATE INDEX는 인덱스를 다음과 같이 처리합니다:

• 비유니크 인덱스의 경우, strict SQL 모드가 활성화되어 있으면 에러가 발생하거나, strict SQL 모드가 비활성화되어 있으면 인덱스 길이가 최대 컬럼 데이터 타입 크기 이내가 되도록 줄어들고 워닝이 발생합니다.

• 유니크 인덱스의 경우, SQL 모드와 관계없이 에러가 발생합니다. 인덱스 길이를 줄이면 지정된 유니크 요구 사항을 충족하지 않는 비유니크 엔트리의 삽입이 가능해질 수 있기 때문입니다.

다음과 같은 스테이트먼트는 name 컬럼(비바이너리 문자열 타입이라고 가정)의 처음 10문자를 사용하여 인덱스를 생성합니다:

1CREATE INDEX part_of_name ON customer (name(10));

컬럼의 name 값들이 일반적으로 처음 10문자에서 서로 다르다면, 이 인덱스를 사용하여 수행되는 룩업은 전체 name 컬럼으로 생성된 인덱스를 사용하는 것보다 크게 느리지 않을 것입니다.

또한 인덱스에서 컬럼 프리픽스를 사용하면 인덱스 파일의 크기를 훨씬 작게 만들 수 있으며, 이는 많은 디스크 공간을 절약할 수 있고 INSERT 작업을 더 빠르게 만드는 데도 도움이 될 수 있습니다.

Functional Key Parts

“일반적인” 인덱스는 컬럼 값 또는 컬럼 값의 프리픽스를 인덱싱합니다. 예를 들어, 다음 테이블에서 특정 t1 행에 대한 인덱스 엔트리에는 전체 col1 값과, col2 값의 처음 10문자로 구성된 프리픽스가 포함됩니다:

1CREATE TABLE t1 (
2  col1 VARCHAR(10),
3  col2 VARCHAR(20),
4  INDEX (col1, col2(10))
5);

식 값을 인덱싱하는 함수형 키 파트도 컬럼 또는 컬럼 프리픽스 값 대신 사용할 수 있습니다. 함수형 키 파트를 사용하면 테이블에 직접 저장되지 않은 값에 대해서도 인덱스를 생성할 수 있습니다. 예:

1CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
2CREATE INDEX idx1 ON t1 ((col1 + col2));
3CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
4ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

여러 키 파트를 가진 인덱스는 비함수형 및 함수형 키 파트를 섞어서 사용할 수 있습니다.

함수형 키 파트에 대해서는 ASC와 DESC가 지원됩니다.

함수형 키 파트는 다음 규칙을 준수해야 합니다. 키 파트 정의에 허용되지 않는 구조가 포함된 경우 에러가 발생합니다.

• 인덱스 정의에서는 식을 컬럼 또는 컬럼 프리픽스와 구분하기 위해 괄호로 감싸야 합니다. 예를 들어, 다음은 허용됩니다. 식이 괄호로 감싸져 있기 때문입니다:

1INDEX ((col1 + col2), (col3 - col4))

다음 예시는 식이 괄호로 감싸져 있지 않으므로 에러를 발생시킵니다:

1INDEX (col1 + col2, col3 - col4)

• 함수형 키 파트는 컬럼 이름만으로 구성될 수 없습니다. 예를 들어, 다음은 허용되지 않습니다:

1INDEX ((col1), (col2))

대신, 키 파트를 괄호 없이 비함수형 키 파트로 작성하십시오:

1INDEX (col1, col2)

• 함수형 키 파트 식은 컬럼 프리픽스를 참조할 수 없습니다. 우회 방법은 이 섹션 뒤에서 설명하는 SUBSTRING() 및 CAST()에 대한 설명을 참조하십시오.

• 함수형 키 파트는 외래 키 정의에서는 허용되지 않습니다.

CREATE TABLE ... LIKE의 경우, 대상 테이블은 원본 테이블의 함수형 키 파트를 그대로 유지합니다.

함수형 인덱스는 숨겨진 버추얼 생성 컬럼으로 구현되며, 이는 다음과 같은 의미를 가집니다:

• 각 함수형 키 파트는 테이블 컬럼의 총 개수 제한에 포함됩니다. Section 10.4.7, “Limits on Table Column Count and Row Size”를 참조하십시오.

• 함수형 키 파트는 생성 컬럼에 적용되는 모든 제한을 상속합니다. 예:

  • 생성 컬럼에 허용되는 함수만 함수형 키 파트에 대해서도 허용됩니다.

  • 서브쿼리, 파라미터, 변수, 스토어드 함수, 로더블 함수는 허용되지 않습니다.

적용 가능한 제한 사항에 대한 자세한 내용은 Section 15.1.24.8, “CREATE TABLE and Generated Columns” 및 Section 15.1.11.2, “ALTER TABLE and Generated Columns”를 참조하십시오.

• 버추얼 생성 컬럼 자체는 스토리지를 필요로 하지 않습니다. 인덱스 자체는 다른 인덱스와 마찬가지로 스토리지 공간을 차지합니다.

UNIQUE는 함수형 키 파트를 포함하는 인덱스에 대해 지원됩니다. 그러나 프라이머리 키는 함수형 키 파트를 포함할 수 없습니다. 프라이머리 키에는 생성 컬럼이 저장되어야 하지만, 함수형 키 파트는 스토어드 생성 컬럼이 아닌 버추얼 생성 컬럼으로 구현되기 때문입니다.

SPATIAL 및 FULLTEXT 인덱스는 함수형 키 파트를 가질 수 없습니다.

테이블에 프라이머리 키가 없는 경우, InnoDB는 첫 번째 UNIQUE NOT NULL 인덱스를 프라이머리 키로 자동 승격합니다. 함수형 키 파트를 가진 UNIQUE NOT NULL 인덱스에 대해서는 이러한 동작이 지원되지 않습니다.

비함수형 인덱스는 중복 인덱스가 있는 경우 워닝을 발생시킵니다. 함수형 키 파트를 포함하는 인덱스에는 이 기능이 없습니다.

함수형 키 파트가 참조하는 컬럼을 제거하려면 먼저 인덱스를 제거해야 합니다. 그렇지 않으면 에러가 발생합니다.

비함수형 키 파트는 프리픽스 길이 지정을 지원하지만, 함수형 키 파트는 그렇지 않습니다. 해결 방법은 이 섹션 뒤에서 설명하는 것처럼 SUBSTRING()(또는 CAST())를 사용하는 것입니다. SUBSTRING() 함수가 포함된 함수형 키 파트가 쿼리에서 사용되려면, WHERE 절에 동일한 아규먼트를 가진 SUBSTRING()이 포함되어야 합니다. 다음 예에서, 인덱스 정의와 SUBSTRING()의 아규먼트가 일치하는 쿼리는 두 번째 SELECT뿐이며, 따라서 두 번째 쿼리만 인덱스를 사용할 수 있습니다:

1CREATE TABLE tbl (
2  col1 LONGTEXT,
3  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
4);
5SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
6SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';

함수형 키 파트를 사용하면 JSON 값처럼 다른 방법으로는 인덱싱할 수 없는 값에 대해 인덱스를 생성할 수 있습니다. 그러나 원하는 효과를 얻으려면 올바르게 수행해야 합니다. 예를 들어 다음 구문은 동작하지 않습니다:

1CREATE TABLE employees (
2  data JSON,
3  INDEX ((data->>'$.name'))
4);

이 구문이 실패하는 이유는 다음과 같습니다:

• ->> 연산자는 JSON_UNQUOTE(JSON_EXTRACT(...))로 변환됩니다.

• JSON_UNQUOTE()는 데이터 타입이 LONGTEXT인 값을 반환하며, 숨겨진 생성 컬럼에도 동일한 데이터 타입이 할당됩니다.

• MySQL은 키 파트에서 프리픽스 길이를 지정하지 않은 LONGTEXT 컬럼을 인덱싱할 수 없으며, 프리픽스 길이는 함수형 키 파트에서 허용되지 않습니다.

JSON 컬럼을 인덱싱하기 위해 다음과 같이 CAST() 함수를 사용해 볼 수 있습니다:

1CREATE TABLE employees (
2  data JSON,
3  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
4);

숨겨진 생성 컬럼에는 인덱싱이 가능한 VARCHAR(30) 데이터 타입이 할당됩니다. 그러나 이 접근 방식은 인덱스를 사용하려 할 때 새로운 문제를 야기합니다:

• CAST()는 서버 기본 콜레이션인 utf8mb4_0900_ai_ci 콜레이션을 사용하는 문자열을 반환합니다.

• JSON_UNQUOTE()는 하드 코딩된 콜레이션인 utf8mb4_bin을 사용하는 문자열을 반환합니다.

결과적으로, 앞의 테이블 정의에서 인덱싱된 식과 다음 쿼리의 WHERE 절 식 사이에 콜레이션 미스매치가 발생합니다:

1SELECT * FROM employees WHERE data->>'$.name' = 'James';

쿼리와 인덱스의 식이 서로 다르므로 인덱스가 사용되지 않습니다. 함수형 키 파트에 대해 이러한 종류의 시나리오를 지원하기 위해 옵티마이저는 인덱스를 찾을 때 자동으로 CAST()를 제거하지만, 오직 인덱싱된 식의 콜레이션이 쿼리 식의 콜레이션과 일치하는 경우에만 그렇게 합니다. 함수형 키 파트를 가진 인덱스가 사용되도록 하려면 다음 두 가지 해결 방법 중 하나를 사용할 수 있습니다(둘 사이에 약간의 영향 차이는 있습니다):

• 해결 방법 1. 인덱싱된 식에 JSON_UNQUOTE()와 동일한 콜레이션을 지정합니다:

1CREATE TABLE employees (
2    data JSON,
3    INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
4);
5INSERT INTO employees VALUES
6    ('{ "name": "james", "salary": 9000 }'),
7    ('{ "name": "James", "salary": 10000 }'),
8    ('{ "name": "Mary", "salary": 12000 }'),
9    ('{ "name": "Peter", "salary": 8000 }');
10SELECT * FROM employees WHERE data->>'$.name' = 'James';

->> 연산자는 JSON_UNQUOTE(JSON_EXTRACT(...))와 동일하며, JSON_UNQUOTE()는 콜레이션 utf8mb4_bin을 사용하는 문자열을 반환합니다. 따라서 비교는 대소문자를 구분하며, 하나의 행만 일치합니다:

1+------------------------------------+
2| data                               |
3+------------------------------------+
4| {"name": "James", "salary": 10000} |
5+------------------------------------+

• 해결 방법 2. 쿼리에서 전체 식을 지정합니다:

1CREATE TABLE employees (
2    data JSON,
3    INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
4);
5INSERT INTO employees VALUES
6    ('{ "name": "james", "salary": 9000 }'),
7    ('{ "name": "James", "salary": 10000 }'),
8    ('{ "name": "Mary", "salary": 12000 }'),
9    ('{ "name": "Peter", "salary": 8000 }');
10SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';

CAST()는 콜레이션 utf8mb4_0900_ai_ci를 사용하는 문자열을 반환하므로, 비교는 대소문자를 구분하지 않으며 두 개의 행이 일치합니다:

1+------------------------------------+
2| data                               |
3+------------------------------------+
4| {"name": "james", "salary": 9000}  |
5| {"name": "James", "salary": 10000} |
6+------------------------------------+

옵티마이저는 인덱싱된 생성 컬럼에서 인덱스를 찾을 때 CAST()를 자동으로 제거하는 것을 지원하지만, 다음과 같은 접근 방식은 인덱스의 유무에 따라 서로 다른 결과를 생성하기 때문에 동작하지 않습니다(Bug#27337092):

1mysql> CREATE TABLE employees (
2         data JSON,
3         generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
4       );
5Query OK, 0 rows affected, 1 warning (0.03 sec)
6
7mysql> INSERT INTO employees (data)
8       VALUES ('{"name": "james"}'), ('{"name": "James"}');
9Query OK, 2 rows affected, 1 warning (0.01 sec)
10Records: 2  Duplicates: 0  Warnings: 1
11
12mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
13+-------------------+---------------+
14| data              | generated_col |
15+-------------------+---------------+
16| {"name": "James"} | James         |
17+-------------------+---------------+
181 row in set (0.00 sec)
19
20mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
21Query OK, 0 rows affected, 1 warning (0.03 sec)
22Records: 0  Duplicates: 0  Warnings: 1
23
24mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
25+-------------------+---------------+
26| data              | generated_col |
27+-------------------+---------------+
28| {"name": "james"} | james         |
29| {"name": "James"} | James         |
30+-------------------+---------------+
312 rows in set (0.01 sec)

Unique Indexes

UNIQUE 인덱스는 인덱스 내의 모든 값이 서로 달라야 한다는 제약을 생성합니다. 기존 행과 키 값이 일치하는 새 행을 추가하려고 하면 에러가 발생합니다. UNIQUE 인덱스에서 컬럼에 대해 프리픽스 값을 지정하면, 해당 컬럼 값은 프리픽스 길이 내에서 유니크해야 합니다. UNIQUE 인덱스는 NULL 값을 허용하는 컬럼에 대해 여러 NULL 값을 허용합니다.

테이블에 하나의 정수 타입 컬럼으로 이루어진 PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스가 있는 경우, SELECT 스테이트먼트에서 _rowid를 사용하여 인덱싱된 컬럼을 참조할 수 있습니다:

• _rowid는 단일 정수 컬럼으로 구성된 PRIMARY KEY가 있는 경우 PRIMARY KEY 컬럼을 참조합니다. PRIMARY KEY가 있지만 단일 정수 컬럼으로 구성되지 않은 경우 _rowid는 사용할 수 없습니다.

• 그렇지 않으면, _rowid는 첫 번째 UNIQUE NOT NULL 인덱스의 컬럼을 참조하며, 해당 인덱스가 단일 정수 컬럼으로 구성된 경우에만 가능합니다. 첫 번째 UNIQUE NOT NULL 인덱스가 단일 정수 컬럼으로 구성되어 있지 않으면 _rowid는 사용할 수 없습니다.

Full-Text Indexes

FULLTEXT 인덱스는 InnoDB 및 MyISAM 테이블에서만 지원되며, CHAR, VARCHAR, TEXT 컬럼만 포함할 수 있습니다. 인덱스는 항상 전체 컬럼에 대해 생성되며, 컬럼 프리픽스 인덱싱은 지원되지 않으며, 지정된 프리픽스 길이는 무시됩니다. 동작에 대한 자세한 내용은 Section 14.9, “Full-Text Search Functions”을 참조하십시오.

Multi-Valued Indexes

InnoDB는 멀티값 인덱스를 지원합니다. 멀티값 인덱스는 값의 배열을 저장하는 컬럼에 대해 정의된 세컨더리 인덱스입니다. “일반적인” 인덱스는 각 데이터 레코드에 대해 하나의 인덱스 레코드(1:1)를 갖습니다. 멀티값 인덱스는 하나의 데이터 레코드에 대해 여러 인덱스 레코드(N:1)를 가질 수 있습니다. 멀티값 인덱스는 JSON 배열을 인덱싱하기 위한 것입니다. 예를 들어, 다음 JSON 도큐먼트의 우편번호 배열에 대해 정의된 멀티값 인덱스는 각 우편번호에 대해 인덱스 레코드를 생성하며, 각 인덱스 레코드는 동일한 데이터 레코드를 참조합니다.

1{
2    "user":"Bob",
3    "user_id":31,
4    "zipcode":[94477,94536]
5}

Creating multi-valued Indexes

멀티값 인덱스는 CREATE TABLE, ALTER TABLE, CREATE INDEX 스테이트먼트에서 생성할 수 있습니다. 이를 위해 인덱스 정의에서 CAST(... AS ... ARRAY)를 사용해야 하며, 이는 JSON 배열 내의 동일 타입 스칼라 값을 SQL 데이터 타입 배열로 캐스트합니다. 그런 다음 SQL 데이터 타입 배열의 값을 가진 버추얼 컬럼이 내부적으로 생성되고, 마지막으로 버추얼 컬럼에 함수형 인덱스(버추얼 인덱스라고도 함)가 생성됩니다. SQL 데이터 타입 배열의 값에서 온 버추얼 컬럼에 대해 정의된 함수형 인덱스가 멀티값 인덱스를 형성합니다.

다음 목록의 예제에서는 customers라는 테이블의 custinfo라는 JSON 컬럼에서 $.zipcode 배열에 대해 멀티값 인덱스 zips를 생성하는 세 가지 서로 다른 방법을 보여줍니다. 각 경우 JSON 배열은 UNSIGNED 정수 값의 SQL 데이터 타입 배열로 캐스트됩니다.

• CREATE TABLE만 사용하는 경우:

1CREATE TABLE customers (
2      id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3      modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4      custinfo JSON,
5      INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
6      );

• CREATE TABLE 후 ALTER TABLE을 사용하는 경우:

1CREATE TABLE customers (
2      id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3      modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4      custinfo JSON
5      );
6
7ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

• CREATE TABLE 후 CREATE INDEX를 사용하는 경우:

1CREATE TABLE customers (
2      id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3      modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4      custinfo JSON
5      );
6
7CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

멀티값 인덱스는 복합 인덱스의 일부로 정의할 수도 있습니다. 다음 예에서는 두 개의 단일값 파트(id, modified 컬럼)와 하나의 멀티값 파트(custinfo 컬럼)를 포함하는 복합 인덱스를 보여줍니다:

1CREATE TABLE customers (
2    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4    custinfo JSON
5    );
6
7ALTER TABLE customers ADD INDEX comp(id, modified,
8    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

멀티값 키 파트는 복합 인덱스에서 하나만 사용할 수 있습니다. 멀티값 키 파트는 다른 키 파트에 대해 어떤 순서로든 사용할 수 있습니다. 다시 말해, 방금 제시한 ALTER TABLE 스테이트먼트는 comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified))(또는 다른 어떤 순서)로도 유효합니다.

Using multi-valued Indexes

옵티마이저는 WHERE 절에서 다음 함수가 지정된 경우 멀티값 인덱스를 사용하여 레코드를 가져옵니다:

• MEMBER OF()

• JSON_CONTAINS()

• JSON_OVERLAPS()

이를 보여주기 위해 다음 CREATE TABLE 및 INSERT 스테이트먼트를 사용하여 customers 테이블을 생성 및 채웁니다:

1mysql> CREATE TABLE customers (
2    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4    ->     custinfo JSON
5    ->     );
6Query OK, 0 rows affected (0.51 sec)
7
8mysql> INSERT INTO customers VALUES
9    ->     (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
10    ->     (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
11    ->     (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
12    ->     (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
13    ->     (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
14Query OK, 5 rows affected (0.07 sec)
15Records: 5  Duplicates: 0  Warnings: 0

먼저 MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS()를 각각 사용하는 세 개의 쿼리를 customers 테이블에 대해 실행하며, 각 쿼리의 결과는 다음과 같습니다:

1mysql> SELECT * FROM customers
2    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
3+----+---------------------+-------------------------------------------------------------------+
4| id | modified            | custinfo                                                          |
5+----+---------------------+-------------------------------------------------------------------+
6|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
7|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
8|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
9+----+---------------------+-------------------------------------------------------------------+
103 rows in set (0.00 sec)
11
12mysql> SELECT * FROM customers
13    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
14+----+---------------------+-------------------------------------------------------------------+
15| id | modified            | custinfo                                                          |
16+----+---------------------+-------------------------------------------------------------------+
17|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
18|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
19+----+---------------------+-------------------------------------------------------------------+
202 rows in set (0.00 sec)
21
22mysql> SELECT * FROM customers
23    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
24+----+---------------------+-------------------------------------------------------------------+
25| id | modified            | custinfo                                                          |
26+----+---------------------+-------------------------------------------------------------------+
27|  1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
28|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
29|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
30|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
31+----+---------------------+-------------------------------------------------------------------+
324 rows in set (0.00 sec)

다음으로, 앞의 세 쿼리 각각에 대해 EXPLAIN을 실행합니다:

1mysql> EXPLAIN SELECT * FROM customers
2    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
3+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
4| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
5+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
6|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
7+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
81 row in set, 1 warning (0.00 sec)
9
10mysql> EXPLAIN SELECT * FROM customers
11    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
12+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
13| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
14+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
15|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
16+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
171 row in set, 1 warning (0.00 sec)
18
19mysql> EXPLAIN SELECT * FROM customers
20    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
21+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
22| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
23+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
24|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
25+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
261 row in set, 1 warning (0.01 sec)

위에서 보여준 세 쿼리는 어느 것도 키를 사용할 수 없습니다. 이 문제를 해결하기 위해, 다음과 같이 JSON 컬럼(custinfo) 내의 zipcode 배열에 멀티값 인덱스를 추가할 수 있습니다:

1mysql> ALTER TABLE customers
2    ->     ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
3Query OK, 0 rows affected (0.47 sec)
4Records: 0  Duplicates: 0  Warnings: 0

이전에 사용한 EXPLAIN 스테이트먼트를 다시 실행하면, 이제 쿼리가 방금 생성한 인덱스 zips를 사용할 수 있고 실제로 사용하고 있음을 확인할 수 있습니다:

1mysql> EXPLAIN SELECT * FROM customers
2    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
3+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
4| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
5+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
6|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
7+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
81 row in set, 1 warning (0.00 sec)
9
10mysql> EXPLAIN SELECT * FROM customers
11    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
12+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
13| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
14+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
15|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
16+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
171 row in set, 1 warning (0.00 sec)
18
19mysql> EXPLAIN SELECT * FROM customers
20    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
21+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
22| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
23+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
24|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
25+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
261 row in set, 1 warning (0.01 sec)

멀티값 인덱스는 유니크 키로 정의할 수 있습니다. 유니크 키로 정의된 경우, 멀티값 인덱스에 이미 존재하는 값을 삽입하려 하면 중복 키 에러가 반환됩니다. 이미 중복 값이 존재하는 경우, 유니크 멀티값 인덱스를 추가하려고 하면 다음과 같이 실패합니다:

1mysql> ALTER TABLE customers DROP INDEX zips;
2Query OK, 0 rows affected (0.55 sec)
3Records: 0  Duplicates: 0  Warnings: 0
4
5mysql> ALTER TABLE customers
6    ->     ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
7ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'
8mysql> ALTER TABLE customers
9    ->     ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
10Query OK, 0 rows affected (0.36 sec)
11Records: 0  Duplicates: 0  Warnings: 0

Characteristics of Multi-Valued Indexes

멀티값 인덱스는 다음에 나열된 추가 특성을 가집니다:

• 멀티값 인덱스에 영향을 미치는 DML 작업은 일반 인덱스에 영향을 미치는 DML 작업과 동일한 방식으로 처리되며, 유일한 차이점은 하나의 클러스터드 인덱스 레코드에 대해 삽입 또는 업데이트가 여러 번 발생할 수 있다는 점입니다.

• 널 허용 여부와 멀티값 인덱스:

  • 멀티값 키 파트가 빈 배열을 가지는 경우, 인덱스에 아무 엔트리도 추가되지 않으며, 데이터 레코드는 인덱스 스캔으로 접근할 수 없습니다.

  • 멀티값 키 파트 생성이 NULL 값을 반환하면, NULL을 포함하는 단일 엔트리가 멀티값 인덱스에 추가됩니다. 키 파트가 NOT NULL로 정의된 경우 에러가 보고됩니다.

  • 타입이 지정된 배열 컬럼이 NULL로 설정되면, 스토리지 엔진은 데이터 레코드를 가리키는 NULL을 포함하는 단일 레코드를 저장합니다.

  • 인덱싱된 배열에는 JSON null 값이 허용되지 않습니다. 반환된 값이 NULL인 경우, JSON null로 처리되며 Invalid JSON value 에러가 보고됩니다.

• 멀티값 인덱스는 버추얼 생성 컬럼에 대한 세컨더리 인덱스와 동일한 규칙을 따라야 하는 버추얼 인덱스이므로, 이에 대한 동일한 규칙을 준수해야 합니다.

• 빈 배열에 대해서는 인덱스 레코드가 추가되지 않습니다.

Limitations and Restrictions on Multi-valued Indexes

멀티값 인덱스에는 다음에 나열된 제한 및 제약이 적용됩니다:

• 멀티값 인덱스당 멀티값 키 파트는 하나만 허용됩니다. 그러나 CAST(... AS ... ARRAY) 식은 다음과 같이 JSON 도큐먼트 내의 여러 배열을 참조할 수 있습니다:

1CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)

이 경우, JSON 식과 일치하는 모든 값은 단일 평탄한 배열로 인덱스에 저장됩니다.

• 멀티값 키 파트를 가진 인덱스는 정렬을 지원하지 않으므로 프라이머리 키로 사용할 수 없습니다. 같은 이유로, 멀티값 인덱스는 ASC 또는 DESC 키워드를 사용하여 정의할 수 없습니다.

• 멀티값 인덱스는 커버링 인덱스가 될 수 없습니다.

• 멀티값 인덱스에서 레코드당 최대 값 개수는 단일 언두 로그 페이지에 저장할 수 있는 데이터 양(65221바이트, 즉 64K에서 오버헤드 315바이트를 뺀 값)에 의해 결정되며, 이는 키 값의 총 길이 최대값도 65221바이트임을 의미합니다. 키의 최대 개수는 여러 요인에 따라 달라지므로 구체적인 한계를 정의할 수는 없습니다. 예를 들어, 테스트 결과 멀티값 인덱스가 레코드당 최대 1604개의 정수 키를 허용하는 것으로 나타났습니다. 한계에 도달하면 다음과 유사한 에러가 보고됩니다: ERROR 3905 (HY000): Exceeded max number of values per record for multi-valued index 'idx' by 1 value(s).

• 멀티값 키 파트에서 허용되는 식 타입은 JSON 식뿐입니다. 이 식은 인덱싱된 컬럼에 삽입된 JSON 도큐먼트 내의 기존 요소를 참조할 필요는 없지만, 자체적으로 문법상 유효해야 합니다.

• 동일한 클러스터드 인덱스 레코드에 대한 인덱스 레코드가 멀티값 인덱스 전체에 분산되어 있기 때문에, 멀티값 인덱스는 레인지 스캔 또는 인덱스 온리 스캔을 지원하지 않습니다.

• 멀티값 인덱스는 외래 키 정의에서 허용되지 않습니다.

• 멀티값 인덱스에 대해 인덱스 프리픽스를 정의할 수 없습니다.

• 멀티값 인덱스는 BINARY로 캐스트된 데이터에 대해 정의할 수 없습니다(CAST() 함수 설명을 참조하십시오).

• 멀티값 인덱스의 온라인 생성은 지원되지 않으며, 이는 해당 작업이 ALGORITHM=COPY를 사용함을 의미합니다. Performance and Space Requirements를 참조하십시오.

• 다음 두 가지 캐릭터 집합과 콜레이션 조합 이외의 캐릭터 집합 및 콜레이션은 멀티값 인덱스에서 지원되지 않습니다:

  1. 기본 콜레이션 binary를 사용하는 binary 캐릭터 집합

  2. 기본 콜레이션 utf8mb4_0900_as_cs를 사용하는 utf8mb4 캐릭터 집합

• InnoDB 테이블의 다른 컬럼에 대한 인덱스와 마찬가지로, 멀티값 인덱스는 USING HASH로 생성할 수 없습니다. 그렇게 시도하면 다음과 같은 워닝이 발생합니다: This storage engine does not support the HASH index algorithm, storage engine default was used instead. (USING BTREE는 일반적으로 지원됩니다.)

Spatial Indexes

MyISAM, InnoDB, NDB, ARCHIVE 스토리지 엔진은 POINT, GEOMETRY와 같은 공간 컬럼을 지원합니다. (공간 데이터 타입에 대한 설명은 Section 13.4, “Spatial Data Types”을 참조하십시오.) 그러나 공간 컬럼 인덱싱 지원은 엔진마다 다릅니다. 공간 컬럼에 대한 공간 및 비공간 인덱스는 다음 규칙에 따라 사용할 수 있습니다.

공간 컬럼에 대한 공간 인덱스는 다음 특성을 가집니다:

• InnoDB 및 MyISAM 테이블에서만 사용할 수 있습니다. 다른 스토리지 엔진에 대해 SPATIAL INDEX를 지정하면 에러가 발생합니다.

• 공간 컬럼에 대한 인덱스는 반드시(must) SPATIAL 인덱스여야 합니다. 따라서 공간 컬럼에 대한 인덱스를 생성할 때 SPATIAL 키워드는 선택 사항이지만 묵시적으로 적용됩니다.

• 단일 공간 컬럼에 대해서만 사용할 수 있습니다. 여러 공간 컬럼에 걸쳐 공간 인덱스를 생성할 수 없습니다.

• 인덱싱된 컬럼은 NOT NULL이어야 합니다.

• 컬럼 프리픽스 길이는 사용할 수 없습니다. 각 컬럼의 전체 폭이 인덱싱됩니다.

• 프라이머리 키나 유니크 인덱스로는 사용할 수 없습니다.

공간 컬럼에 대한 비공간 인덱스(INDEX, UNIQUE, PRIMARY KEY로 생성됨)는 다음 특성을 가집니다:

• ARCHIVE를 제외한 공간 컬럼을 지원하는 모든 스토리지 엔진에서 허용됩니다.

• 인덱스가 프라이머리 키가 아닌 경우 컬럼은 NULL일 수 있습니다.

• 비 SPATIAL 인덱스의 인덱스 타입은 스토리지 엔진에 따라 다릅니다. 현재는 B-tree가 사용됩니다.

• NULL 값을 가질 수 있는 컬럼에 대한 인덱스는 InnoDB, MyISAM, MEMORY 테이블에서만 허용됩니다.

Index Options

키 파트 목록 뒤에는 인덱스 옵션을 지정할 수 있습니다. index_option 값은 다음 중 하나가 될 수 있습니다:

• KEY_BLOCK_SIZE [=] value

MyISAM 테이블의 경우, KEY_BLOCK_SIZE는 인덱스 키 블록에 사용할 바이트 단위의 크기를 선택 사항으로 지정합니다. 이 값은 힌트로 처리되며, 필요하다면 다른 크기가 사용될 수 있습니다. 개별 인덱스 정의에 지정된 KEY_BLOCK_SIZE 값은 테이블 수준의 KEY_BLOCK_SIZE 값을 오버라이드합니다.

KEY_BLOCK_SIZE는 InnoDB 테이블의 인덱스 수준에서는 지원되지 않습니다. Section 15.1.24, “CREATE TABLE Statement”를 참조하십시오.

• index_type

일부 스토리지 엔진은 인덱스를 생성할 때 인덱스 타입을 지정할 수 있도록 허용합니다. 예를 들어:

1CREATE TABLE lookup (id INT) ENGINE = MEMORY;
2CREATE INDEX id_index ON lookup (id) USING BTREE;

Table 15.1, “Index Types Per Storage Engine”은 서로 다른 스토리지 엔진이 지원하는 허용 가능한 인덱스 타입 값을 보여줍니다. 여러 인덱스 타입이 나열된 경우, 인덱스 타입 지정자가 제공되지 않으면 첫 번째 타입이 기본값으로 사용됩니다. 표에 나열되지 않은 스토리지 엔진은 인덱스 정의에서 index_type 절을 지원하지 않습니다.

Table 15.1 Index Types Per Storage Engine

Storage Engine	Permissible Index Types
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH, BTREE
NDB	HASH, BTREE (see note in text)

index_type 절은 FULLTEXT INDEX 지정에는 사용할 수 없습니다. 풀텍스트 인덱스 구현은 스토리지 엔진에 따라 다릅니다. 공간 인덱스는 R-tree 인덱스로 구현됩니다.

주어진 스토리지 엔진에 대해 유효하지 않은 인덱스 타입을 지정했지만, 쿼리 결과에 영향을 주지 않고 엔진이 사용할 수 있는 다른 인덱스 타입이 있는 경우, 엔진은 사용 가능한 타입을 사용합니다. 파서는 RTREE를 타입 이름으로 인식합니다. 이는 SPATIAL 인덱스에 대해서만 허용됩니다.

BTREE 인덱스는 NDB 스토리지 엔진에서 T-tree 인덱스로 구현됩니다.

참고

NDB 테이블 컬럼에 대한 인덱스의 경우, USING 옵션은 유니크 인덱스 또는 프라이머리 키에 대해서만 지정할 수 있습니다. USING HASH는 순서가 있는 인덱스의 생성을 방지합니다. 그렇지 않으면, NDB 테이블에서 유니크 인덱스 또는 프라이머리 키를 생성하면 동일한 컬럼 집합을 인덱싱하는 순서가 있는 인덱스와 해시 인덱스가 모두 자동으로 생성됩니다.

NDB 테이블의 하나 이상의 NULL 컬럼을 포함하는 유니크 인덱스의 경우, 해시 인덱스는 리터럴 값을 룩업하는 데만 사용할 수 있으며, 이는 IS [NOT] NULL 조건에 대해 테이블 전체 스캔이 필요함을 의미합니다. 한 가지 해결 방법은 그러한 테이블에서 하나 이상의 NULL 컬럼을 사용하는 유니크 인덱스를 항상 순서가 있는 인덱스를 포함하도록 생성하는 것입니다. 즉, 인덱스를 생성할 때 USING HASH 사용을 피하십시오.

주어진 스토리지 엔진에 대해 유효하지 않은 인덱스 타입을 지정했지만, 쿼리 결과에 영향을 주지 않고 엔진이 사용할 수 있는 다른 인덱스 타입이 있는 경우, 엔진은 사용 가능한 타입을 사용합니다. 파서는 RTREE를 타입 이름으로 인식하지만, 현재 어떤 스토리지 엔진에도 이를 지정할 수는 없습니다.

참고

ON tbl_name 절 앞에서 index_type 옵션을 사용하는 것은 더 이상 권장되지 않으며, 향후 MySQL 릴리스에서 이 위치에서의 옵션 지원이 제거될 것으로 예상됩니다. index_type 옵션이 앞과 뒤 위치에 모두 지정된 경우, 마지막에 지정된 옵션이 적용됩니다.

TYPE type_name은 USING type_name의 동의어로 인식됩니다. 그러나 USING이 선호되는 형태입니다.

다음 표는 index_type 옵션을 지원하는 스토리지 엔진의 인덱스 특성을 보여줍니다.

Table 15.2 InnoDB Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table
SPATIAL	N/A	No	No	N/A	N/A

Table 15.3 MyISAM Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table
SPATIAL	N/A	No	No	N/A	N/A

Table 15.4 MEMORY Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
Primary key	HASH	No	No	N/A	N/A
Unique	HASH	Yes	Yes	Index	Index
Key	HASH	Yes	Yes	Index	Index

Table 15.5 NDB Storage Engine Index Characteristics

Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
Primary key	BTREE	No	No	Index	Index
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
Primary key	HASH	No	No	Table (see note 1)	Table (see note 1)
Unique	HASH	Yes	Yes	Table (see note 1)	Table (see note 1)
Key	HASH	Yes	Yes	Table (see note 1)	Table (see note 1)

Table note:

1. USING HASH는 묵시적인 순서가 있는 인덱스의 생성을 방지합니다.

• WITH PARSER parser_name

이 옵션은 FULLTEXT 인덱스에서만 사용할 수 있습니다. 풀텍스트 인덱싱 및 검색 작업에 특수한 처리가 필요한 경우 파서 플러그인을 인덱스에 연결합니다. InnoDB와 MyISAM은 풀텍스트 파서 플러그인을 지원합니다. 풀텍스트 파서 플러그인이 연결된 MyISAM 테이블이 있는 경우, ALTER TABLE을 사용하여 테이블을 InnoDB로 변환할 수 있습니다. 자세한 내용은 Full-Text Parser Plugins 및 Writing Full-Text Parser Plugins를 참조하십시오.

• COMMENT 'string'

인덱스 정의에는 최대 1024문자의 선택적 코멘트를 포함할 수 있습니다.

CREATE INDEX 스테이트먼트의 index_option COMMENT 절을 사용하여 인덱스 페이지에 대한 MERGE_THRESHOLD를 개별 인덱스에 대해 설정할 수 있습니다. 예:

1CREATE TABLE t1 (id INT);
2CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

행이 삭제되거나 업데이트 작업으로 인해 행이 짧아져서 인덱스 페이지의 페이지-풀 비율이 MERGE_THRESHOLD 값 아래로 떨어지면, InnoDB는 인덱스 페이지를 인접한 인덱스 페이지와 병합하려고 시도합니다. 기본 MERGE_THRESHOLD 값은 50이며, 이는 이전에 하드 코딩된 값입니다.

MERGE_THRESHOLD는 CREATE TABLE 및 ALTER TABLE 스테이트먼트를 사용하여 인덱스 수준 및 테이블 수준에서도 정의할 수 있습니다. 자세한 내용은 Section 17.8.11, “Configuring the Merge Threshold for Index Pages”를 참조하십시오.

• VISIBLE, INVISIBLE

인덱스 가시성을 지정합니다. 인덱스는 기본적으로 visible입니다. invisible 인덱스는 옵티마이저에서 사용되지 않습니다. 인덱스 가시성 지정은 프라이머리 키(명시적 또는 암시적)를 제외한 인덱스에 적용됩니다. 자세한 내용은 Section 10.3.12, “Invisible Indexes”를 참조하십시오.

• ENGINE_ATTRIBUTE 및 SECONDARY_ENGINE_ATTRIBUTE는 프라이머리 및 세컨더리 스토리지 엔진에 대한 인덱스 속성을 지정하는 데 사용됩니다. 이 옵션은 향후 사용을 위해 예약되어 있습니다.

이 옵션에 할당되는 값은 유효한 JSON 도큐먼트 또는 빈 문자열('')을 포함하는 문자열 리터럴입니다. 잘못된 JSON은 거부됩니다.

1CREATE INDEX i1 ON t1 (c1) ENGINE_ATTRIBUTE='{"key":"value"}';

ENGINE_ATTRIBUTE 및 SECONDARY_ENGINE_ATTRIBUTE 값은 에러 없이 반복 지정할 수 있습니다. 이 경우 마지막에 지정된 값이 사용됩니다.

ENGINE_ATTRIBUTE 및 SECONDARY_ENGINE_ATTRIBUTE 값은 서버에서 검사되지 않으며, 테이블의 스토리지 엔진이 변경되더라도 제거되지 않습니다.

Table Copying and Locking Options

ALGORITHM 및 LOCK 절은 인덱스가 수정되는 동안 테이블 복사 방법과 테이블의 읽기 및 쓰기 동시성 수준에 영향을 주도록 지정할 수 있습니다. 이 절의 의미는 ALTER TABLE 스테이트먼트에서와 동일합니다. 자세한 내용은 Section 15.1.11, “ALTER TABLE Statement”를 참조하십시오.

NDB Cluster는 표준 MySQL Server에서 사용되는 것과 동일한 ALGORITHM=INPLACE 구문을 사용하여 온라인 작업을 지원합니다. 자세한 내용은 Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”를 참조하십시오.