このページは機械翻訳したものです。
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 [=]
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
HASH
、BTREE
NDB
HASH
、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_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 を使用したオンライン操作」,をご覧ください。