PDF (US Ltr)
- 36.1Mb
PDF (A4)
- 36.2Mb
このページは機械翻訳したものです。
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) では、col1、col2 および 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カラムは、InnoDB、MyISAMおよびBLACKHOLEテーブルに対してのみインデックス付けできます。 -
接頭辞 limits はバイト単位で測定されます。 ただし、
CREATE TABLE、ALTER 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 の場合、宛先テーブルは元のテーブルの機能キー部分を保持します。
関数インデックスは非表示の仮想生成カラムとして実装され、次のような影響があります:
各関数キー部分は、テーブルのカラムの合計数に対する制限に対してカウントされます。セクション8.4.7「テーブルカラム数と行サイズの制限」 を参照してください。
-
機能キー部分は、生成されたカラムに適用されるすべての制限を継承します。 例:
関数キー部分には、生成されたカラムに許可された関数のみが許可されます。
サブクエリー、パラメータ、変数、ストアドファンクションおよびユーザー定義関数は使用できません。
適用可能な制限の詳細は、セクション13.1.20.8「CREATE TABLE および生成されるカラム」 および セクション13.1.9.2「ALTER TABLE および生成されるカラム」 を参照してください。
仮想生成カラム自体に記憶域は必要ありません。 インデックス自体は、他のインデックスと同様に記憶領域を占有します。
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がある場合、_rowidはPRIMARY KEYカラムを参照します。PRIMARY KEYはあるが、単一の整数カラムで構成されていない場合、_rowidは使用できません。それ以外の場合、
_rowidは最初のUNIQUE NOT NULLインデックスのカラムを参照します (そのインデックスが単一の整数カラムで構成されている場合)。 最初のUNIQUE NOT NULLインデックスが単一の整数カラムで構成されていない場合、_rowidは使用できません。
FULLTEXT インデックスは InnoDB および MyISAM テーブルでのみサポートされ、CHAR、VARCHAR、および 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 TABLE、ALTER 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 TABLEとALTER 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 TABLEとCREATE 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 つの文字セットと照合順序の組合せ以外の文字セットと照合順序は、複数値インデックスではサポートされていません:
デフォルトの
binary照合順序を持つbinary文字セットデフォルトの
utf8mb4_0900_as_cs照合順序を持つutf8mb4文字セット。
InnoDBテーブルのカラムに対する他のインデックスと同様に、USING HASHでは複数値インデックスを作成できません。作成しようとすると警告が表示されます: このストレージエンジンは HASH インデックスアルゴリズムをサポートしていません。代わりにストレージエンジンのデフォルトが使用されました。 (USING BTREEは通常どおりにサポートされます。)
MyISAM, InnoDB, NDB および ARCHIVE ストレージエンジンは、POINT、GEOMETRY などの空間カラムをサポートしています。(セクション11.4「空間データ型」 では、空間データ型について説明します。) ただし、空間カラムのインデックス設定に対するサポートはエンジンによって異なります。 空間カラムの空間インデックスおよび非空間インデックスは、次のルールに従って使用できます。
空間カラムの空間インデックスには、次の特性があります:
InnoDBおよびMyISAMテーブルでのみ使用できます。 その他のストレージエンジンに対してSPATIAL INDEXを指定すると、エラーが発生します。MySQL 8.0.12 では、空間カラムのインデックスは
SPATIALインデックスである必要があります。 したがって、SPATIALキーワードはオプションですが、空間カラムにインデックスを作成する場合は暗黙的です。単一の空間カラムにのみ使用できます。 空間インデックスは、複数の空間カラムに対して作成できません。
インデックス付きカラムは
NOT NULLである必要があります。カラム接頭辞の長さは禁止されています。 各カラムの幅全体にインデックスが設定されます。
主キーまたは一意インデックスには使用できません。
(INDEX、UNIQUE または PRIMARY KEY で作成された) 空間カラムの非空間インデックスには、次の特性があります:
ARCHIVEを除く空間カラムをサポートするすべてのストレージエンジンに対して許可されます。インデックスが主キーでないかぎり、カラムを
NULLにすることができます。非
SPATIALインデックスのインデックスタイプは、ストレージエンジンによって異なります。 現在は、B ツリーが使用されます。InnoDB、MyISAMおよびMEMORYテーブルに対してのみNULL値を持つことができるカラムに対して許可されます。
キーパートリストの後に、インデックスオプションを指定できます。 index_option 値には、次のいずれかを指定できます。
-
KEY_BLOCK_SIZE [=]valueMyISAMテーブルの場合、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 ストレージエンジンあたりのインデックスタイプ
ストレージエンジン 許可されるインデックスタイプ InnoDBBTREEMyISAMBTREEMEMORY/HEAPHASH、BTREENDBHASH、BTREE(テキストの注を参照してください)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_nameindex_typeオプションの使用は非推奨になりました。この位置でのオプションの使用のサポートは、将来の MySQL リリースで削除される予定です。index_typeオプションが前とあとの両方の位置で指定された場合は、最後のオプションが適用されます。TYPEは、type_nameUSINGのシノニムとして認識されます。 ただし、推奨される形式はtype_nameUSINGです。次のテーブルに、
index_typeオプションをサポートするストレージエンジンのインデックス特性を示します。表 13.2 InnoDB ストレージエンジンのインデックス特性
インデックスクラス インデックスタイプ NULL VALUES を格納 複数の NULL 値を許可 IS NULL スキャンタイプ IS NOT NULL スキャンタイプ 主キー BTREEいいえ いいえ N/A N/A Unique BTREEはい はい インデックス インデックス 鍵 BTREEはい はい インデックス インデックス FULLTEXTN/A はい はい Table Table SPATIALN/A いいえ いいえ N/A N/A 表 13.3 MyISAM ストレージエンジンのインデックス特性
インデックスクラス インデックスタイプ NULL VALUES を格納 複数の NULL 値を許可 IS NULL スキャンタイプ IS NOT NULL スキャンタイプ 主キー BTREEいいえ いいえ N/A N/A Unique BTREEはい はい インデックス インデックス 鍵 BTREEはい はい インデックス インデックス FULLTEXTN/A はい はい Table Table SPATIALN/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 PARSERparser_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_optionCOMMENT句を使用して個々のインデックスに対して構成できます。 例: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 を使用したオンライン操作」,をご覧ください。