跳至內容

MySQL/Language/Index

維基教科書,自由的教學讀本

根據索引的原理,全NULL值不被記錄在索引上

索引類型

[編輯]
  • 主鍵索引 PRIMARY KEY: 唯一索引,不允許有空值。一般是在建表的時候同時創建主鍵索引。一個表只能有一個主鍵
  • 唯一索引 UNIQUE: 列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。可以通過ALTER TABLE table_name ADD UNIQUE (column);創建唯一索引。可以通過ALTER TABLE table_name ADD UNIQUE (column1,column2);創建唯一組合索引
  • 外鍵索引 foreign key上建立了一個index;(至少在oracle上建立外鍵,不會自動建立index)
  • 普通索引 INDEX:最基本的索引,它沒有任何限制。可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引
  • 組合索引 INDEX:即一個索引包含多個列。多用於避免回表查詢。可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創建組合索引
  • 全文索引 FULLTEXT:是目前搜尋引擎使用的一種關鍵技術。可以通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引
  • 前綴索引

刪除索引

[編輯]

索引一經創建不能修改,如果要修改索引,只能刪除重建。可以使用DROP INDEX index_name ON table_name;刪除索引。

索引設計的原則

[編輯]
  • 適合索引的列是出現在where子句中的列,或者連接子句中指定的列
  • 基數較小的類,索引效果較差,沒有必要在此列建立索引
  • 使用短索引,如果對長字符串列進行索引,應該指定一個前綴長度,這樣能夠節省大量索引空間
  • 不要過度索引。索引需要額外的磁盤空間,並降低寫操作的性能。在修改表內容的時候,索引會進行更新甚至重構,索引列越多,這個時間就會越長。所以只保持需要的索引有利於查詢即可。

重建索引命令

[編輯]
mysql> REPAIR TABLE tbl_name QUICK;

查詢數據表索引

[編輯]
mysql> SHOW INDEX FROM tbl_name;

創建鍵與索引

[編輯]

主鍵、唯一鍵、外鍵的創建方式:

  • 在字段級以key方式建立, 如 create table t (id int not null primary key);
  • 在表級以constraint方式建立,如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
  • 在表級以key方式建立,如create table t(id int, primary key (id));

在CREATE TABLE語句中的key關鍵字單用,是指普通索引。

ALTER TABLE語句創建索引(PRIMARY KEY,INDEX,UNIQUE)

mysql>ALTER TABLE tbl_name ADD INDEX index_name (column list);
mysql>ALTER TABLE tbl_name ADD UNIQUE index_name (column list);
mysql>ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column list);

刪除索引與鍵

[編輯]
mysql>ALTER TABLE tbl_name DROP INDEX index_name (column list);
mysql>ALTER TABLE tbl_name DROP UNIQUE index_name (column list);
mysql>ALTER TABLE tbl_name DROP PRIMARY KEY index_name (column list);