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

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

次のステートメントは、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 は、機能キー部分でサポートされています。

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

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 カラムをインデックス付けするには、次のように CAST() 関数を使用します:

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

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

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

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

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

オプティマイザではインデックス付けされた生成カラムを含む 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 ステートメントのインデックス付けされたカラムを参照できます:

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 データ型配列にキャストされます。

複数値インデックスはコンポジットインデックスの一部として定義することもできます。 次の例は、(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 句で次の関数が指定されている場合、オプティマイザは複数値インデックスを使用してレコードをフェッチします:

これを示すために、次の 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

複数値インデックスの特性

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

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

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

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

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

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

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

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

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