Index

Create Index

Grammar

CREATE [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 不能用來創(chuàng)建PRIAMARY_KEY，如果需要創(chuàng)建PRIMARY_KEY需要使用ALTER TABLE；

• 通常在建表的時候(CREATE_TABLE)中創(chuàng)建索引，在InnoDB引擎中，PRIMARY_KEY決定了行在數(shù)據(jù)文件里的物理格局（似乎更主張在建表的時候指定索引和主鍵之類的屬性）。

• 可以基于多列創(chuàng)建索引。

• 對String類型的列創(chuàng)建索引時需要注意的：

  • 可以為String類型的列（CHAR，VARCHAR， BINARY， VARBINARY）指定前綴索引。
  • Prefixes must be specified for BLOB and TEXT column indexes.
  • 由于String型的列有不同的數(shù)據(jù)類型，而且又有不同的長度，所以為String列建索引的時候需要考慮到前綴長度。
  • 空間類型的列不能建索引。（TODO）
  • 如果一個某一個string列中，它們大部分行都能以前10個字符來區(qū)分開彼此，那么這個索引的速度不會比整個列長度的索引慢很多（實際應該也存在這種情況，比如身份證號若存于19長度的字符，或許在一定數(shù)量級下，以前9位建索引跟以19為建索引的效率相差不明顯），更短的索引長度會節(jié)省更多的磁盤空間，同時會提高INSERT操作的執(zhí)行速度。

• MySQL Cluster formerly supported online CREATE INDEX operations using an alternative syntax that is no longer supported.

• UNIQUE 唯一索引：

  • 唯一索引限定了一個約束條件：被索引的列必須是唯一的（不重復的），如果插入一行與已經(jīng)存在的某一行數(shù)據(jù)的索引是相同的，這時候會報錯。
  • 所有的Mysql引擎里，唯一索引允許它索引的行為NULL值（前提是該行允許存儲NULL值）；
  • 如果為一列指定前綴唯一索引，那么必須保證這一列的值在前綴長度內(nèi)為唯一。

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

• FULLTEXT 全文索引：
  • FULLTEXT indexes are supported only for InnoDB and MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns.
  • Indexing always happens over the entire column;
  • column prefix indexing is not supported and any prefix length is ignored if specified.

For more reference: