MySQL 8.0 リファレンスマニュアル


13.1.15 CREATE INDEX ステートメント

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

通常、テーブル上のすべてのインデックスは、そのテーブル自体が CREATE TABLE で作成された時点で作成します。 セクション13.1.20「CREATE TABLE ステートメント」を参照してください。 このガイドラインは、主キーによってデータファイル内の行の物理配列が決定される InnoDB テーブルの場合に特に重要です。 CREATE INDEX では、既存のテーブルにインデックスを追加できます。

CREATE INDEX は、インデックスを作成するために ALTER TABLE ステートメントにマップされます。 セクション13.1.9「ALTER TABLE ステートメント」を参照してください。 CREATE INDEX を使用して PRIMARY KEY を作成することはできません。代わりに ALTER TABLE を使用します。 インデックスの詳細は、セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。

InnoDB は、仮想カラムのセカンダリインデックスをサポートしています。 詳細は、セクション13.1.20.9「セカンダリインデックスと生成されたカラム」を参照してください。

innodb_stats_persistent 設定が有効になっている場合は、InnoDB テーブル上でインデックスを作成したあと、そのテーブルに対して ANALYZE TABLE ステートメントを実行します。

MySQL 8.0.17 以降、key_part 仕様の expr では、(CAST json_expression AS type ARRAY) の形式を使用して JSON カラムに複数値インデックスを作成できます。 複数値インデックスを参照してください。

(key_part1, key_part2, ...) 形式のインデックス指定では、複数のキー部分を持つインデックスが作成されます。 インデックスキー値は、指定されたキー部分の値を連結することによって形成されます。 たとえば、(col1, col2, col3) では、col1col2 および col3 の値で構成されるインデックスキーを持つ複数カラムインデックスを指定します。

key_part 仕様の末尾には、ASC または DESC を使用して、インデックス値を昇順または降順のどちらで格納するかを指定できます。 順序指定子が指定されていない場合、デフォルトは昇順です。 ASC および DESC は、HASH インデックスには使用できません。 ASC および DESC は、複数値インデックスでもサポートされていません。 MySQL 8.0.12 では、SPATIAL インデックスに対して ASC および DESC は許可されていません。

次の各セクションでは、CREATE INDEX ステートメントの様々な側面について説明します:

カラム接頭辞のキー部分

文字列カラムの場合、col_name(length) 構文を使用してインデックス接頭辞の長さを指定し、カラム値の先頭部分のみを使用するインデックスを作成できます:

  • 接頭辞は、CHAR, VARCHAR, BINARY および VARBINARY のキー部分に指定できます。

  • 接頭辞は、BLOB および TEXT のキー部分に指定する必要があります。 また、BLOB カラムおよび TEXT カラムは、InnoDBMyISAM および BLACKHOLE テーブルに対してのみインデックス付けできます。

  • 接頭辞 limits はバイト単位で測定されます。 ただし、CREATE TABLEALTER TABLE および CREATE INDEX ステートメントのインデックス指定の接頭辞 lengths は、非バイナリ文字列型 (CHAR, VARCHAR, TEXT) の場合は文字数として解釈され、バイナリ文字列型 (BINARY, VARBINARY, BLOB) の場合はバイト数として解釈されます。 マルチバイト文字セットを使用する非バイナリ文字列カラムに接頭辞の長さを指定する場合は、これを考慮してください。

    プリフィクスのサポートやプリフィクスの長さ (サポートされている場合) は、ストレージエンジンに依存します。 たとえば、REDUNDANT または COMPACT の行形式を使用する InnoDB テーブルでは、接頭辞の長さは最大 767 バイトです。 DYNAMIC または COMPRESSED の行形式を使用する InnoDB テーブルでは、接頭辞の長さの制限は 3072 バイトです。 MyISAM テーブルの場合、接頭辞の長さの制限は 1000 バイトです。 NDB ストレージエンジンは接頭辞をサポートしていません (セクション23.1.7.6「NDB Cluster でサポートされない機能または欠落している機能」 を参照)。

指定したインデックス接頭辞がカラムの最大データ型サイズを超える場合、CREATE INDEX は次のようにインデックスを処理します:

  • 一意でないインデックスの場合は、エラーが発生するか (厳密な SQL モードが有効な場合)、インデックスの長さが最大カラムデータ型サイズ内になるように縮小され、警告が生成されます (厳密な SQL モードが有効でない場合)。

  • 一意インデックスの場合、インデックスの長さを短くすると、指定した一意性要件を満たさない一意でないエントリの挿入が可能になるため、SQL モードに関係なくエラーが発生します。

次のステートメントは、name カラムの最初の 10 文字を使用してインデックスを作成します (name にバイナリ以外の文字列型があると想定しています):

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

通常、カラムの名前が最初の 10 文字と異なる場合、このインデックスを使用して実行されるルックアップは、name カラム全体から作成されたインデックスを使用する場合よりも遅くなることはありません。 また、インデックスにカラムプリフィクスを使用するとインデックスファイルをはるかに小さくできるため、多くのディスク領域が節約されるだけでなく、INSERT 操作も高速化される可能性があります。

機能キー部品

normal インデックスは、カラム値またはカラム値の接頭辞をインデックス付けします。 たとえば、次のテーブルでは、特定の t1 行のインデックスエントリに、最初の 10 文字で構成される完全な col1 値と col2 値の接頭辞が含まれています:

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

MySQL 8.0.13 以上では、カラムまたはカラムの接頭辞値ではなく式の値をインデックス付けする関数キー部分がサポートされています。 関数キーパーツを使用すると、テーブルに直接格納されない値のインデックス付けが可能になります。 例:

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

複数のキーパートを持つインデックスでは、非機能キーパートと機能キーパートを混在させることができます。

ASC および DESC は、機能キー部分でサポートされています。

機能キー部分は、次のルールに従う必要があります。 キーパート定義に許可されていない構成が含まれている場合は、エラーが発生します。

  • インデックス定義では、式をカッコで囲み、カラムまたはカラムの接頭辞と区別します。 たとえば、これは許可されており、式はカッコで囲まれています:

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

    これによりエラーが発生します。式はカッコで囲まれません:

    INDEX (col1 + col2, col3 - col4)
  • 関数キー部分は、カラム名のみで構成できません。 たとえば、これは許可されていません:

    INDEX ((col1), (col2))

    かわりに、キー部分を機能しないキー部分としてカッコなしで記述します:

    INDEX (col1, col2)
  • 関数キー部分式はカラム接頭辞を参照できません。 回避策については、このセクションで後述する SUBSTRING() および CAST() の説明を参照してください。

  • 外部キー仕様では、機能キー部分は許可されません。

CREATE TABLE ... LIKE の場合、宛先テーブルは元のテーブルの機能キー部分を保持します。

関数インデックスは非表示の仮想生成カラムとして実装され、次のような影響があります:

UNIQUE は、関数キー部分を含むインデックスに対してサポートされています。 ただし、主キーに機能キー部分を含めることはできません。 主キーでは、生成されたカラムを格納する必要がありますが、機能キー部分は、格納された生成カラムではなく、仮想生成カラムとして実装されます。

SPATIAL および FULLTEXT インデックスには、関数キー部分を含めることはできません。

テーブルに主キーが含まれていない場合、InnoDB は最初の UNIQUE NOT NULL インデックスを主キーに自動的に昇格します。 これは、関数キー部分を持つ UNIQUE NOT NULL インデックスではサポートされません。

インデックスが重複している場合は、非関数インデックスで警告が発生します。 関数キー部分を含むインデックスには、この機能はありません。

関数キー部分によって参照されるカラムを削除するには、最初にインデックスを削除する必要があります。 それ以外の場合は、エラーが発生します。

非機能キー部分は接頭辞の長さの指定をサポートしていますが、これは機能キー部分では不可能です。 解決策は、SUBSTRING()(または、このセクションの後半で説明する CAST()) を使用することです。 クエリーで使用される SUBSTRING() 関数を含む関数キー部分の場合、WHERE 句には同じ引数を持つ SUBSTRING() が含まれている必要があります。 次の例では、SUBSTRING() への引数がインデックス指定と一致する唯一のクエリーであるため、インデックスを使用できるのは 2 つ目の SELECT のみです:

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

関数キーパーツを使用すると、JSON 値など、インデックス化できない値のインデックス化が可能です。 ただし、目的の効果を得るには、これを正しく行う必要があります。 たとえば、次の構文は機能しません:

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

構文は、次の理由で失敗します:

  • ->> 演算子は JSON_UNQUOTE(JSON_EXTRACT(...)) に変換されます。

  • JSON_UNQUOTE() は、データ型が LONGTEXT の値を戻し、非表示の生成されたカラムには同じデータ型が割り当てられます。

  • MySQL では、キー部分に接頭辞の長さを指定せずに LONGTEXT カラムをインデックス付けすることはできず、機能キー部分では接頭辞の長さを使用できません。

JSON カラムをインデックス付けするには、次のように CAST() 関数を使用します:

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

非表示の生成されたカラムには、インデックス付け可能な VARCHAR(30) データ型が割り当てられます。 ただし、この方法では、インデックスを使用しようとすると新しい問題が発生します:

  • CAST() は、照合 utf8mb4_0900_ai_ci (サーバーのデフォルトの照合) を含む文字列を返します。

  • JSON_UNQUOTE() は、照合順序が utf8mb4_bin (ハードコード) の文字列を返します。

その結果、前述のテーブル定義のインデックス付き式と次のクエリーの WHERE 句式の間に照合の不一致があります:

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

クエリーとインデックスの式が異なるため、インデックスは使用されません。 関数キー部分でこのようなシナリオをサポートするために、オプティマイザは使用するインデックスを検索するときに CAST() を自動的に削除しますが、インデックス付き式の照合がクエリー式の照合と一致する場合はのみを削除します。 関数キー部分が使用されるインデックスの場合、次の 2 つのソリューションのいずれかが機能します (ただし、ある程度異なります):

  • 解決策 1。 インデックス付き式に JSON_UNQUOTE() と同じ照合を割り当てます:

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

    ->>演算子は JSON_UNQUOTE(JSON_EXTRACT(...)) と同じで、JSON_UNQUOTE() は照合順序 utf8mb4_bin を持つ文字列を返します。 したがって、比較では大文字と小文字が区別され、一致する行は 1 つのみです:

    +------------------------------------+
    | data                               |
    +------------------------------------+
    | {"name": "James", "salary": 10000} |
    +------------------------------------+
  • ソリューション 2. クエリーに完全な式を指定します:

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

    CAST() は照合 utf8mb4_0900_ai_ci を含む文字列を返すため、比較では大文字と小文字が区別されず、次の 2 つの行が一致します:

    +------------------------------------+
    | data                               |
    +------------------------------------+
    | {"name": "james", "salary": 9000}  |
    | {"name": "James", "salary": 10000} |
    +------------------------------------+

オプティマイザではインデックス付けされた生成カラムを含む CAST() の自動削除がサポートされていますが、次の方法ではインデックスの有無にかかわらず異なる結果が生成されるため、機能しないことに注意してください (Bug#27337092):

mysql> CREATE TABLE employees (
         data JSON,
         generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
       );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> INSERT INTO employees (data)
       VALUES ('{"name": "james"}'), ('{"name": "James"}');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "James"} | James         |
+-------------------+---------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "james"} | james         |
| {"name": "James"} | James         |
+-------------------+---------------+
2 rows in set (0.01 sec)

一意インデックス

UNIQUE インデックスは、そのインデックス内のすべての値が異なっている必要があるという制約を作成します。 既存の行に一致するキー値を持つ新しい行を追加しようとすると、エラーが発生します。 UNIQUE インデックスのカラムに接頭辞値を指定する場合、カラム値は接頭辞の長さ内で一意である必要があります。 UNIQUE インデックスでは、NULL を含むことができるカラムに対して複数の NULL 値が許可されます。

テーブルに整数型の単一カラムで構成される PRIMARY KEY または UNIQUE NOT NULL インデックスがある場合は、次のように_rowid を使用して SELECT ステートメントのインデックス付けされたカラムを参照できます:

  • 単一の整数カラムで構成される PRIMARY KEY がある場合、_rowidPRIMARY KEY カラムを参照します。 PRIMARY KEY はあるが、単一の整数カラムで構成されていない場合、_rowid は使用できません。

  • それ以外の場合、_rowid は最初の UNIQUE NOT NULL インデックスのカラムを参照します (そのインデックスが単一の整数カラムで構成されている場合)。 最初の UNIQUE NOT NULL インデックスが単一の整数カラムで構成されていない場合、_rowid は使用できません。

全文インデックス

FULLTEXT インデックスは InnoDB および MyISAM テーブルでのみサポートされ、CHARVARCHAR、および TEXT カラムのみを含めることができます。 インデックス設定は常に、カラム全体に対して実行されます。カラムプリフィクスのインデックス設定はサポートされていないため、プリフィクス長が指定されてもすべて無視されます。 操作の詳細は、セクション12.10「全文検索関数」を参照してください。

複数値インデックス

MySQL 8.0.17 では、InnoDB は複数値インデックスをサポートしています。 複数値インデックスは、値の配カラムを格納するカラムに定義されたセカンダリインデックスです。 normal インデックスには、データレコードごとに 1 つのインデックスレコードがあります (1:1)。 複数値インデックスは、単一のデータレコードに対して複数のインデックスレコードを持つことができます (N:1)。 複数値インデックスは JSON 配列のインデックス付けを目的としています。 たとえば、次の JSON ドキュメントの郵便番号の配列に定義された複数値インデックスでは、各インデックスレコードが同じデータレコードを参照するように、郵便番号ごとにインデックスレコードが作成されます。

{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}
複数値インデックスの作成

CREATE TABLEALTER TABLE または CREATE INDEX ステートメントで複数値インデックスを作成できます。 これには、JSON 配列内の同じ型のスカラー値を SQL データ型配列にキャストするインデックス定義で CAST(... AS ... ARRAY) を使用する必要があります。 仮想カラムは、SQL データ型配列の値を使用して透過的に生成されます。最後に、仮想カラムに関数インデックス (仮想インデックスとも呼ばれます) が作成されます。 これは、複数値インデックスを形成する SQL データ型配カラムの値の仮想カラムに定義された関数インデックスです。

次のリストの例は、customers という名前のテーブルの JSON カラム custinfo の配カラム $.zipcode に複数値インデックス zips を作成する方法を示しています。 いずれの場合も、JSON 配列は UNSIGNED 整数値の SQL データ型配列にキャストされます。

  • CREATE TABLE のみ:

    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON,
        INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) )
        );
  • CREATE TABLEALTER TABLE:

    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON
        );
    
    ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );

  • CREATE TABLECREATE INDEX:

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

複数値インデックスはコンポジットインデックスの一部として定義することもできます。 次の例は、(id および modified カラムの) 2 つの単一値部分と (custinfo カラムの) 1 つの複数値部分を含むコンポジットインデックスを示しています:

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX comp(id, modified,
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

コンポジットインデックスで使用できる複数値キー部分は 1 つのみです。 複数値キー部分は、キーの他の部分に対して任意の順序で使用できます。 つまり、示されている ALTER TABLE ステートメントは、comp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (またはその他の順序付け) を使用している可能性があり、引き続き有効です。

複数値インデックスの使用

WHERE 句で次の関数が指定されている場合、オプティマイザは複数値インデックスを使用してレコードをフェッチします:

  • MEMBER OF()

  • JSON_CONTAINS()

  • JSON_OVERLAPS()

これを示すために、次の CREATE TABLE および INSERT ステートメントを使用して customers テーブルを作成および移入します:

mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON
    ->     );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO customers VALUES
    ->     (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    ->     (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    ->     (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    ->     (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    ->     (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

まず、customers テーブルに対して 3 つのクエリーを実行します。それぞれが MEMBER OF()JSON_CONTAINS() および JSON_OVERLAPS() を使用し、次に示す各クエリーの結果が表示されます:

mysql> SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

次に、前述の 3 つのクエリーごとに EXPLAIN を実行します:

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

上記の 3 つのクエリーでは、どのキーも使用できません。 この問題を解決するには、次のように、zipcode 配カラムの JSON カラム (custinfo) に複数値インデックスを追加します:

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

前の EXPLAIN ステートメントを再度実行すると、作成したばかりのインデックス zips をクエリーで使用できる (および使用できる) ことがわかります:

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

複数値インデックスは一意キーとして定義できます。 一意キーとして定義されている場合、複数値インデックスにすでに存在する値を挿入しようとすると、重複キーエラーが返されます。 重複する値がすでに存在する場合、次に示すように、一意の複数値インデックスを追加しようとすると失敗します:

mysql> ALTER TABLE customers DROP INDEX zips;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customers
    ->     ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'
mysql> ALTER TABLE customers
    ->     ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0
複数値インデックスの特性

複数値インデックスには、次に示す追加の特性があります:

  • 複数値インデックスに影響する DML 操作は、通常のインデックスに影響する DML 操作と同じ方法で処理されますが、唯一の違いは、単一のクラスタインデックスレコードに対して複数の挿入または更新が存在する可能性があることです。

  • NULL 値可能性および複数値インデックス:

    • 複数値キー部分に空の配列がある場合、インデックスにエントリは追加されず、データレコードにはインデックススキャンでアクセスできません。

    • 複数値キー部分の生成で NULL 値が返された場合、NULL を含む単一のエントリが複数値インデックスに追加されます。 キー部分が NOT NULL として定義されている場合は、エラーが報告されます。

    • 型付き配列カラムが NULL に設定されている場合、ストレージエンジンは、データレコードを指す NULL を含む単一のレコードを格納します。

    • インデックス付き配列では、JSON の NULL 値は許可されません。 戻り値が NULL の場合、JSON null として扱われ、「JSON 値が無効です」エラーが報告されます。

  • 複数値インデックスは仮想カラムの仮想インデックスであるため、仮想生成カラムのセカンダリインデックスと同じルールに従う必要があります。

  • 空の配列のインデックスレコードは追加されません。

複数値インデックスの制限事項

複数値インデックスには、次の制限事項があります:

  • 複数値インデックスごとに許可される複数値キー部分は 1 つのみです。 ただし、次に示すように、CAST(... AS ... ARRAY) 式は JSON 文書内の複数の配列を参照できます:

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

    この場合、JSON 式に一致するすべての値が単一のフラット配列としてインデックスに格納されます。

  • 複数値キー部分を持つインデックスは順序付けをサポートしていないため、主キーとして使用できません。 同じ理由で、ASC または DESC キーワードを使用して複数値インデックスを定義することはできません。

  • 複数値インデックスをカバーインデックスにすることはできません。

  • 複数値インデックスのレコード当たりの最大値は、単一の undo ログページに格納できるデータ量によって決まります。つまり、65221 バイト (オーバーヘッドの場合は 64K から 315 バイトを引いた値) で、キー値の最大合計長も 65221 バイトです。 キーの最大数は様々な要因に依存するため、特定の制限を定義できません。 たとえば、テストでは、レコードごとに 1604 個までの整数キーを許可する複数値インデックスが示されています。 制限に達すると、次のようなエラーが報告されます: ERROR 3905 (HY000): 複数値インデックス'idx'のレコード当たりの値の最大数を 1 値超えました

  • 複数値キー部分で許可されている式のタイプは、JSON 式のみです。 式は、インデックス付けされたカラムに挿入された JSON ドキュメント内の既存の要素を参照する必要はありませんが、構文的に有効である必要があります。

  • 同じクラスタインデックスレコードのインデックスレコードは複数値インデックス全体に分散されるため、複数値インデックスではレンジスキャンまたはインデックスのみのスキャンはサポートされません。

  • 複数値インデックスは、外部キー指定では使用できません。

  • 複数値インデックスにはインデックス接頭辞を定義できません。

  • 複数値インデックスは、データキャストで BINARY として定義できません (CAST() 関数の説明を参照)。

  • 複数値インデックスのオンライン作成はサポートされていません。つまり、操作で ALGORITHM=COPY が使用されます。 パフォーマンスおよび領域要件を参照してください。

  • 次の 2 つの文字セットと照合順序の組合せ以外の文字セットと照合順序は、複数値インデックスではサポートされていません:

    1. デフォルトの binary 照合順序を持つ binary 文字セット

    2. デフォルトの utf8mb4_0900_as_cs 照合順序を持つ utf8mb4 文字セット。

  • InnoDB テーブルのカラムに対する他のインデックスと同様に、USING HASH では複数値インデックスを作成できません。作成しようとすると警告が表示されます: このストレージエンジンは HASH インデックスアルゴリズムをサポートしていません。代わりにストレージエンジンのデフォルトが使用されました。 (USING BTREE は通常どおりにサポートされます。)

空間インデックス

MyISAM, InnoDB, NDB および ARCHIVE ストレージエンジンは、POINTGEOMETRY などの空間カラムをサポートしています。(セクション11.4「空間データ型」 では、空間データ型について説明します。) ただし、空間カラムのインデックス設定に対するサポートはエンジンによって異なります。 空間カラムの空間インデックスおよび非空間インデックスは、次のルールに従って使用できます。

空間カラムの空間インデックスには、次の特性があります:

  • InnoDB および MyISAM テーブルでのみ使用できます。 その他のストレージエンジンに対して SPATIAL INDEX を指定すると、エラーが発生します。

  • MySQL 8.0.12 では、空間カラムのインデックスは SPATIAL インデックスである必要があります。 したがって、SPATIAL キーワードはオプションですが、空間カラムにインデックスを作成する場合は暗黙的です。

  • 単一の空間カラムにのみ使用できます。 空間インデックスは、複数の空間カラムに対して作成できません。

  • インデックス付きカラムは NOT NULL である必要があります。

  • カラム接頭辞の長さは禁止されています。 各カラムの幅全体にインデックスが設定されます。

  • 主キーまたは一意インデックスには使用できません。

(INDEXUNIQUE または PRIMARY KEY で作成された) 空間カラムの非空間インデックスには、次の特性があります:

  • ARCHIVE を除く空間カラムをサポートするすべてのストレージエンジンに対して許可されます。

  • インデックスが主キーでないかぎり、カラムを NULL にすることができます。

  • SPATIAL インデックスのインデックスタイプは、ストレージエンジンによって異なります。 現在は、B ツリーが使用されます。

  • InnoDBMyISAM および MEMORY テーブルに対してのみ NULL 値を持つことができるカラムに対して許可されます。

インデックスオプション

キーパートリストの後に、インデックスオプションを指定できます。 index_option 値には、次のいずれかを指定できます。

  • KEY_BLOCK_SIZE [=] value

    MyISAM テーブルの場合、KEY_BLOCK_SIZE はオプションで、インデックスキーブロックに使用するサイズをバイト単位で指定します。 この値はヒントとして扱われます。必要に応じて、異なるサイズが使用される可能性があります。 個々のインデックス定義に指定された KEY_BLOCK_SIZE 値は、テーブルレベルの KEY_BLOCK_SIZE 値をオーバーライドします。

    KEY_BLOCK_SIZE は、InnoDB テーブルのインデックスレベルではサポートされていません。 セクション13.1.20「CREATE TABLE ステートメント」を参照してください。

  • index_type

    一部のストレージエンジンでは、インデックスの作成時にインデックスタイプを指定できます。 例:

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

    表13.1「ストレージエンジンあたりのインデックスタイプ」 には、様々なストレージエンジンでサポートされている許容インデックスタイプ値が表示されます。 複数のインデックスタイプが示されている場合は、最初のものが、インデックスタイプ指示子が指定されないときのデフォルトになります。 テーブルに示されていないストレージエンジンは、インデックス定義で index_type 句をサポートしていません。

    表 13.1 ストレージエンジンあたりのインデックスタイプ

    ストレージエンジン 許可されるインデックスタイプ
    InnoDB BTREE
    MyISAM BTREE
    MEMORY/HEAP HASHBTREE
    NDB HASHBTREE (テキストの注を参照してください)

    index_type 句は、FULLTEXT INDEX または (MySQL 8.0.12 より前の) SPATIAL INDEX 仕様には使用できません。 フルテキストインデックスの実装は、ストレージエンジンに依存します。 空間インデックスは R ツリーインデックスとして実装されます。

    特定のストレージエンジンに対して無効なインデックスタイプを指定しても、エンジンがクエリー結果に影響を与えずに使用できる別のインデックスタイプが使用可能な場合、エンジンは使用可能なタイプを使用します。 パーサーは、RTREE を型名として認識します。 MySQL 8.0.12 では、これは SPATIAL インデックスに対してのみ許可されます。 8.0.12 より前では、どのストレージエンジンにも RTREE を指定できません。

    BTREE インデックスは、NDB ストレージエンジンによって T ツリーインデックスとして実装されます。

    注記

    NDB テーブルカラム上のインデックスの場合、USING オプションは、一意のインデックスまたは主キーに対してのみ指定できます。 USING HASH では、順序付けされたインデックスは作成されません。それ以外の場合、NDB テーブルに一意インデックスまたは主キーを作成すると、順序付けられたインデックスとハッシュインデックスの両方が自動的に作成され、それぞれが同じカラムセットをインデックス付けします。

    NDB テーブルの 1 つ以上の 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 オプションをサポートするストレージエンジンのインデックス特性を示します。

    表 13.2 InnoDB ストレージエンジンのインデックス特性

    インデックスクラス インデックスタイプ NULL VALUES を格納 複数の NULL 値を許可 IS NULL スキャンタイプ IS NOT NULL スキャンタイプ
    主キー BTREE いいえ いいえ N/A N/A
    Unique BTREE はい はい インデックス インデックス
    BTREE はい はい インデックス インデックス
    FULLTEXT N/A はい はい Table Table
    SPATIAL N/A いいえ いいえ N/A N/A

    表 13.3 MyISAM ストレージエンジンのインデックス特性

    インデックスクラス インデックスタイプ NULL VALUES を格納 複数の NULL 値を許可 IS NULL スキャンタイプ IS NOT NULL スキャンタイプ
    主キー BTREE いいえ いいえ N/A N/A
    Unique BTREE はい はい インデックス インデックス
    BTREE はい はい インデックス インデックス
    FULLTEXT N/A はい はい Table Table
    SPATIAL N/A いいえ いいえ N/A N/A

    表 13.4 MEMORY ストレージエンジンのインデックス特性

    インデックスクラス インデックスタイプ NULL VALUES を格納 複数の NULL 値を許可 IS NULL スキャンタイプ IS NOT NULL スキャンタイプ
    主キー BTREE いいえ いいえ N/A N/A
    Unique BTREE はい はい インデックス インデックス
    BTREE はい はい インデックス インデックス
    主キー HASH いいえ いいえ N/A N/A
    Unique HASH はい はい インデックス インデックス
    HASH はい はい インデックス インデックス

    表 13.5 NDB ストレージエンジンのインデックス特性

    インデックスクラス インデックスタイプ NULL VALUES を格納 複数の NULL 値を許可 IS NULL スキャンタイプ IS NOT NULL スキャンタイプ
    主キー BTREE いいえ いいえ インデックス インデックス
    Unique BTREE はい はい インデックス インデックス
    BTREE はい はい インデックス インデックス
    主キー HASH いいえ いいえ テーブル (ノート 1 を参照) テーブル (ノート 1 を参照)
    Unique HASH はい はい テーブル (ノート 1 を参照) テーブル (ノート 1 を参照)
    HASH はい はい テーブル (ノート 1 を参照) テーブル (ノート 1 を参照)

    テーブルノート:

    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 文字のオプションのコメントを含めることができます。

    インデックスページ用の MERGE_THRESHOLD は、CREATE INDEX ステートメントの index_option COMMENT 句を使用して個々のインデックスに対して構成できます。 例:

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

    行が削除されたとき、または更新操作によって行が短縮されたときに、インデックスページのページフル率が MERGE_THRESHOLD 値を下回った場合、InnoDB はインデックスページを隣接するインデックスページとマージしようとします。 デフォルトの MERGE_THRESHOLD 値は 50 で、これは以前にハードコードされた値です。

    MERGE_THRESHOLD は、CREATE TABLE および ALTER TABLE ステートメントを使用して、インデックスレベルおよびテーブルレベルで定義することもできます。 詳細は、セクション15.8.11「インデックスページのマージしきい値の構成」を参照してください。

  • VISIBLE, INVISIBLE

    インデックスの可視性を指定します。 インデックスはデフォルトで可視化されます。 不可視インデックスはオプティマイザでは使用されません。 インデックスの可視性の指定は、主キー以外のインデックス (明示的または暗黙的) に適用されます。 詳細は、セクション8.3.12「不可視のインデックス」を参照してください。

  • ENGINE_ATTRIBUTE および SECONDARY_ENGINE_ATTRIBUTE オプション (MySQL 8.0.21 の時点で使用可能) は、プライマリストレージエンジンおよびセカンダリストレージエンジンのインデックス属性を指定するために使用されます。 オプションは、将来の使用のために予約されています。

    許可される値は、有効な JSON ドキュメントまたは空の文字列 ('') を含む文字列リテラルです。 無効な JSON が拒否されました。

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

    ENGINE_ATTRIBUTE および SECONDARY_ENGINE_ATTRIBUTE の値は、エラーなしで繰り返すことができます。 この場合、最後に指定した値が使用されます。

    ENGINE_ATTRIBUTE および SECONDARY_ENGINE_ATTRIBUTE の値は、サーバーによってチェックされず、テーブルストレージエンジンが変更されたときにもクリアされません。

テーブルのコピーおよびロックのオプション

ALGORITHM 句および LOCK 句を指定して、インデックスの変更中にテーブルの読取りおよび書込みを行うためのテーブルのコピー方法および同時実行性のレベルに影響を与えることができます。 これらには、ALTER TABLE ステートメントの場合と同じ意味があります。 詳細は、セクション13.1.9「ALTER TABLE ステートメント」を参照してください。

NDB Cluster は、標準の MySQL Server で使用されるものと同じ ALGORITHM=INPLACE 構文を使用したオンライン操作をサポートします。 詳しくはセクション23.5.11「NDB Cluster での ALTER TABLE を使用したオンライン操作」,をご覧ください。


関連キーワード:  インデックス, ステートメント, カラム, CREATE, TABLE, 複数, キー, テーブル, col, 部分