mysql索引簡介，mysql索引

mysql索引簡介，mysql索引

索引從本質上來說也是一種表，這種表格儲存體被列為索引的列項值和指向真正完整記錄的指標。索引對使用者透明，只被資料庫引擎用來加速檢索真實記錄。有索引的表，insert和update操作會耗費更多時間而select則會變快，因為insert和update操作同時也要insert和update索引值，但這些操作對使用者也透明。索引通常運用在where、join、order by語句中[1]。

在mysql中，index和key是同義字，兩者可以互換[2]。一張表最多能有16個索引，每個索引最多由15個列組成。mysql中有4種類型索引。索引依據表類型（也既是儲存引擎）有不同儲存方式[3]。

• primary key 可以指定一列或多列組合成索引，用來唯一確定一條記錄，每個指定為primary key的項必須唯一，且不可為空。一張表至多有一個primary key。設定了 primary key 的表自動為 primary key
  建立索引。
• unique key 與 primary key 類似，可以指定一列或多列組合成索引，用來唯一確定一條記錄。每個指定為 unique key 的項必須唯一，允許有NULL值。一張表可以有多個 unique key。
• fulltext類型索引只能用於MyISAM表，也只能用於char，varchar，text列項。
• 普通 index，沒有唯一性限制，也沒有非空限制。

儲存引擎	可能的索引類型
MyISAM	btree
InnoDB	btree
Memory/Heap	hash，btree
NDB	btree，hash

索引可以在建立表的時候建立：

create table table_name (create_column_definition [, ...] );

其中 create_column_definition 可以替換成：

• column_name column_definetion
• [constraint [symbol]] primary key (column_name, ...) [index_type]
• [constraint [symbol]] unique [index|key] (column_name, ...) [index_type]
• {index|key} [index_name] (column_name, ...) [index_type]
• {fulltext} [index | key] (column_name, ...) [index_type]
  其中 column_definetion 可以替換成：
• data_type [not null | null] [default default_value]
• [auto_increment] [unique [key] | [primary] key]
• [comment 'string'] [reference_definition]

例如：

create table test(`id` int unsigned not null auto_increment,`data0` varchar(20),`data1` varchar(20),primary key (`id`),);

create table test(id int unsigned not null auto_increment primary key,`data0` varchar(20),`data1` varchar(20));

表建立之後也可以添加索引：
1）使用alter命令：

alter table table_name[alter_specification [, alter_specification] ... ];

其中 alter_sepcification 可以替換成任意一種：

• add [constraint [symbol]] primary key (index_cloumn_name, ... ) [index_type]
• add [constraint [symbol]] unique [index|key] [index_name] (index_cloumn_name, ... ) [index_type]
• add {index | key} [index_name] (index_cloumn_name, ... ) [index_type]
• add [fulltext] [index|key] [index_name] (index_cloumn_name, ... ) [index_type]

其中 index_cloumn_name 可以替換成：column_name [(length) [asc|desc]]
其中 index_type 可以替換成：using {btree|hash}
例如：

alter table test add unique key `index_data0` (`data0` (10));

2）使用create命令：

create [unique|fulltext|spatial] index index_nameon table_name (index_cloumn_name, ... ) [index_type];

其中 index_cloumn_name 可以替換成：column_name [(length) [asc|desc]]
其中 index_type 可以替換成：using {btree|hash}
需要注意的幾點：

• create命令不能用於建立primary key
• 能夠為char，varchar，binary，varbinary設定索引前置長度，這意味著可以只索引這些欄位的前面某部分。
• blob和text若為索引項目類型，必須指定索引前置長度[5]。

例如：

create index `index_data1` on test (`data1` (10));

刪除索引：

alter table table_name drop primary key;alter table table_name drop {index | key} index_name;

當建立多列索引之後，查詢全部索引或索引的前n列（與定義索引順序一致），能夠使用該索引[6]。例如：

create table test(id int not null auto_increment,last_name char(30) not null,first_name char(30) not null,primary key (id),index name (last_name, first_name));

以下這些查詢能用到索引 name：

select * from test where last='xyb';select * from test where last='xyb' and first_name='love';select * from test where last='xyb' and (first_name='love' or first_name='Charlotte');select * from test where last='xyb' and first_name >= 'l' and first_name <= 'n';

以下這些chauncey不能用到索引 name：

select * from test where first_name='Charlotte';select * from test where last='xyb' or first_name='Charlotte';

綜合講解索引可以參見連結[7]

參考連結：
[1]http://www.tutorialspoint.com/mysql/mysql-indexes.htm
[2]http://stackoverflow.com/questions/3844899/difference-between-key-primary-key-unique-key-and-index-in-mysql
[3]https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
[4]https://dev.mysql.com/doc/refman/5.0/en/alter-table.html
[5]https://dev.mysql.com/doc/refman/5.0/en/create-table.html
[6]https://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
[7]http://blog.csdn.net/tianmohust/article/details/7930482