Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.1.13 CREATE INDEX 構文

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

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

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

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

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

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

(col1,col2,...) という形式のカラムリストは、マルチカラムインデックスを作成します。インデックスキーの値は、特定のカラムの値を連結することによって形成されます。

col_name(length) 構文を使用してインデックスプリフィクス長を指定することにより、カラム値の先頭の部分のみを使用するインデックスを作成できます。

  • プリフィクスは、CHARVARCHARBINARY、および VARBINARY カラムに対して指定できます。

  • BLOB および TEXT カラムにもインデックスを設定できますが、プリフィクス長を指定する必要があります

  • プリフィクス長は、バイナリ以外の文字列型の場合は文字数で、バイナリ文字列型の場合はバイト単位で指定されます。つまり、インデックスエントリは、CHARVARCHAR、および TEXT カラムの場合は各カラム値の最初の length 文字、BINARYVARBINARY、および BLOB カラムの場合は各カラム値の最初の length バイトで構成されます。

  • このセクションのあとの方で説明されているように、空間カラムの場合はプリフィクス値を指定できません。

次に示すステートメントは、name カラムの最初の 10 文字を使用してインデックスを作成します。

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

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

プリフィクスのサポートやプリフィクスの長さ (サポートされている場合) は、ストレージエンジンに依存します。たとえば、InnoDB テーブルではプリフィクスの長さを最大 767 バイトに、また innodb_large_prefix オプションが有効になっている場合は 3072 バイトにすることができます。MyISAM テーブルの場合、プリフィクスの制限は 1000 バイトです。

注記

プリフィクスの制限がバイト単位で測定されるのに対して、CREATE INDEX ステートメントでのプリフィクス長は、バイナリ以外のデータ型 (CHARVARCHARTEXT) では文字数として解釈されます。複数バイトの文字セットを使用するカラムのプリフィクス長を指定する場合は、この点を考慮に入れてください。

NDBCLUSTER テーブルの可変幅カラム上のインデックスはオンラインで、つまり、テーブルコピーを行うことなく作成されます。このテーブルは、この操作の期間中、同じ API ノードに対するほかの操作に対してロックされますが、ほかの MySQL Cluster API ノードからのアクセスに対してはロックされません。これは、サーバーが実行できると判断した場合は常に、そのサーバーによって自動的に実行されます。これを実行するために、特殊な SQL 構文やサーバーオプションを使用する必要はありません。

標準の MySQL 5.6 リリースでは、サーバーがテーブルコピーなしでインデックスを作成すると決定したとき、そのサーバーをオーバーライドすることはできません。MySQL Cluster では、OFFLINE キーワードを使用してインデックスをオフラインで作成できます (これにより、そのテーブルはクラスタ内のすべての API ノードに対してロックされます)。CREATE OFFLINE INDEX および CREATE ONLINE INDEX を管理するルールや制限は、ALTER OFFLINE TABLE ... ADD INDEX および ALTER ONLINE TABLE ... ADD INDEX の場合と同じです。ONLINE キーワードを使用して、通常はオフラインで作成されるインデックスのコピーなし作成が実行されるようにすることはできません。CREATE INDEX 操作をテーブルコピーなしで実行できない場合、サーバーは ONLINE キーワードを無視します。詳細は、セクション13.1.7.2「MySQL Cluster での ALTER TABLE オンライン操作」を参照してください。

ONLINE および OFFLINE キーワードは、MySQL Cluster でのみ使用できます。これらのキーワードを標準の MySQL Server 5.6 リリースで使用しようとすると、構文エラーが発生します。ONLINE および OFFLINE キーワードは、MySQL Cluster NDB 7.3 では非推奨です。MySQL Cluster NDB 7.4 では引き続きサポートされますが、将来の MySQL Cluster リリースで削除される可能性があります。

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

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

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

(SPATIAL INDEX を使用して作成された) 空間インデックスには、次の特性があります。

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

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

  • MySQL 5.6 では、カラムプリフィクス長は禁止されています。各カラムの幅全体にインデックスが設定されます。

INDEXUNIQUE、または PRIMARY KEY で作成された非空間インデックスの特性は次のとおりです。

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

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

  • POINT カラムを除く非 SPATIAL インデックス内の空間カラムごとに、カラムプリフィクス長を指定する必要があります。(これは、インデックス付き BLOB カラムの場合と同じ要件です。)プリフィクス長は、バイト単位で指定されます。

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

MySQL 5.6 では次のとおりです。

  • NULL 値を持つことができるカラムにインデックスを追加できるのは、InnoDBMyISAM、または MEMORY ストレージエンジンを使用している場合だけです。

  • BLOB または TEXT カラムにインデックスを追加できるのは、InnoDB または MyISAM ストレージエンジンを使用している場合だけです。

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

index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

インデックスカラムリストのあとに、インデックスオプションを指定できます。index_option 値には、次のいずれかを指定できます。

  • KEY_BLOCK_SIZE [=] value

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

    注記

    KEY_BLOCK_SIZE は、InnoDB に対してテーブルレベルでのみサポートされます。セクション13.1.17「CREATE TABLE 構文」を参照してください。

  • index_type

    一部のストレージエンジンでは、インデックスの作成時にインデックスタイプを指定できます。ストレージエンジンごとにサポートされている許可されるインデックスタイプ値を次の表に示します。複数のインデックスタイプが示されている場合は、最初のものが、インデックスタイプ指示子が指定されないときのデフォルトになります。

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

    例:

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

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

    注記

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

    つまり、NULL カラム上の一意のインデックスまたは主キーを使用するクエリーは常に、NDB によってテーブルのフルスキャンで処理されます。特に、NDB テーブルの一意のインデックスまたは主キーカラムに関連した IS NULL または IS NOT NULL 条件を使用する予定がある場合は、このようなインデックスをすべて USING HASH なしで作成するようにしてください。

    index_type 句を SPATIAL INDEX とともに使用することはできません。

    特定のストレージエンジンに対して有効でないインデックスタイプを指定したが、そのエンジンがクエリー結果に影響を与えることなく使用できる使用可能な別のインデックスタイプが存在する場合、エンジンはその使用可能なタイプを使用します。パーサーは RTREE をタイプ名として認識しますが、現在、これはどのストレージエンジンに対しても指定できません。

    このオプションを ON tbl_name 句の前に使用することは非推奨です。このオプションをこの位置で使用するためのサポートは、将来の MySQL リリースで削除される予定です。index_type オプションが前とあとの両方の位置で指定された場合は、最後のオプションが適用されます。

    TYPE type_name は、USING type_name のシノニムとして認識されます。ただし、推奨される形式は USING です。

  • WITH PARSER parser_name

    このオプションは、FULLTEXT インデックスとともにのみ使用できます。これは、全文インデックス設定および検索操作に特殊な処理が必要な場合に、パーサープラグインをインデックスに関連付けます。プラグインの作成の詳細は、セクション24.2「MySQL プラグイン API」を参照してください。

  • COMMENT 'string'

    インデックス定義には、最大 1024 文字のオプションのコメントを含めることができます。

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


User Comments
  Posted by Matthijs Lambooy on March 14, 2006
Only 16 fields are allowed in one fulltext index.
  Posted by Andrew Krantz on April 27, 2006
Be careful when you run this because MySQL will LOCK the table for WRITES during the index creation. Building the index can take a while on large tables even if the column is empty (all nulls).
  Posted by Nathan Moon on May 11, 2007
From my experience, adding an index to a table locks the table for reads as well as writes.
  Posted by Varun Grover on September 28, 2007
Running SELECTs on a table on which an index is being created may block because the server may need to use the index for looking-up records; and, the index is locked because it is being written to.
  Posted by David OBrien on March 26, 2009
Since there is no
CREATE INDEX IF NOT EXISTS

We made this sproc to do it...

--------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `create_index_if_not_exists`$$

CREATE DEFINER=`user`@`%` PROCEDURE `create_index_if_not_exists`(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(200))
SQL SECURITY INVOKER
BEGIN

set @Index_cnt = (
select count(1) cnt
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = table_name_vc
and index_name = index_name_vc
);

IF ifnull(@Index_cnt,0) = 0 THEN set @index_sql = concat('Alter table ',table_name_vc,' ADD INDEX ',index_name_vc,'(',field_list_vc,');');

PREPARE stmt FROM @index_sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END IF;

END$$
DELIMITER ;
------------------------

use it like...

call create_index_if_not_exists('tablename','indexname','thisfield,thatfield');
  Posted by Lincoln Rickwood on June 24, 2011
Here's an alternative stored procedure (Sorry about the lack of indentation, the comment box seems to strip leading spaces)...

delimiter //
drop procedure if exists AddTableIndex //
create procedure AddTableIndex
( in schemaName varchar(128) -- If null use name of current schema;
, in tableName varchar(128) -- If null an exception will be thrown.
, in indexName varchar(128) -- If null an exception will be thrown.
, in indexDefinition varchar(1024) -- E.g. '(expireTS_ ASC)'
, in ifPresent enum('leaveUnchanged', 'dropAndReplace') -- null=leaveUnchanged.
, out outcome tinyint(1) -- 0=unchanged, 1=replaced, 4=added.
)
begin
declare doDrop tinyint(1) default null;
declare doAdd tinyint(1) default null;
declare tmpSql varchar(4096) default '';

set schemaName = coalesce(schemaName, schema());
set ifPresent = coalesce(ifPresent, 'leaveUnchanged');
if exists
(SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = schemaName
AND table_name = tableName
AND index_name = indexName
)
then
if (ifPresent = 'leaveUnchanged')
then
set doDrop = 0;
set doAdd = 0;
set outcome = 0;
elseif (ifPresent = 'dropAndReplace')
then
set doDrop = 1;
set doAdd = 1;
set outcome = 1;
end if;
else
set doDrop = 0;
set doAdd = 1;
set outcome = 4;
end if;

if (doDrop = 1)
then
set tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` drop index `', indexName, '` ');
set @sql = tmpSql;
prepare tmp_stmt from @sql;
execute tmp_stmt;
deallocate prepare tmp_stmt;
end if;

if (doAdd = 1)
then
set tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` add index `', indexName, '` ', indexDefinition);
set @sql = tmpSql;
prepare tmp_stmt from @sql;
execute tmp_stmt;
deallocate prepare tmp_stmt;
end if;

end; //

  Posted by James Greene on May 3, 2013
OK, so I'm a bit green myself when it comes to optimizing MySQL. So, I'm giving this little bit of advice to the other newbs out there:

Indexing a LARGE amount of data, this can take hours. Thankfully, I used on a test database. Please be careful, if you plan on adding index on a production database. :-/
  Posted by Jonathan DeLanders on October 16, 2014
Specify ALGORITHM=INPLACE when adding an index to production. I know that mysql will choose INPLACE automatically when its enabled, but it doesnt warn you if it cant. IMHO the copy algorithm is never desired on a busy system ( typically dba's use percona toolkit or rebuild tables instead ). By specifying this you will at least get an error instead of locking up your server.
Sign Up Login You must be logged in to post a comment.