mysql之對索引的操作

標籤：

1. 為什麼使用索引?

資料庫物件索引與書的目錄非常類似,主要是為了提高從表中檢索資料的速度。由於資料儲存在資料庫表中,所以索引是建立在資料庫表對象之上的,由表中的一個欄位或多個欄位產生的鍵組成,這些鍵儲存在資料結構(B-樹或雜湊表)中,通過MySQL可以快速有效尋找與索引值相關聯的欄位。根據索引的儲存類型,可以將索引分為B樹型索引(BTREE)和雜湊索引(HASH)。

InnoDB和MyISAM儲存引擎支援BTREE類型索引,MEMORY儲存引擎支援HASH類型索引,預設為BTREE索引。

資料庫索引不僅可以提高資料庫管理系統的尋找速度,而且還可以保證欄位的唯一性,從而實現資料庫表的完整性。MySQL支援6種索引,分別是普通索引,唯一索引,全文索引,單列索引,多列索引和空間索引。

索引的建立有利有弊,建立索引可以提高尋找速度,但過多的建立索引則會佔據許多的磁碟空間,因此在建立索引之前,必須權衡利弊。

適合建立索引的情況：

(1). 經常被查詢的欄位,即在where字句中出現的欄位。

(2). 在分組的欄位,即在group by字句中出現的欄位。

(3). 存在依賴關係的子表和父表之間的聯集查詢,即主鍵或外鍵欄位。

(4). 設定唯一完整性條件約束的欄位。

不適合建立索引的情況：

(1). 在查詢中很少被使用的欄位。

(2). 擁有很多重複值的欄位。

索引的操作包括建立索引,查看索引和刪除索引。

2. 建立和查看索引

2.1 建立和查看普通索引

建立表時建立普通索引:

CREATE TABLE table_name(屬性名稱 資料類型,屬性名稱 資料類型,...屬性名稱 資料類型,INDEX|KEY 【索引名】 (屬性名稱 【長度】【ASC|DESC】))

在建立索引時,可以指定索引的長度,這是因為不同儲存引擎定義了表的最大索引數和最大索引長度。MySQL所支援的儲存引擎對每個表至少支援16個索引,總索引長度至少為256個位元組。

執行個體如下:

CREATE TABLE t_dept(deptno INT,dbname VARCHAR(20),loc VARCHAR(40),INDEX index_deptno(deptno));

在已經存在的表上建立普通索引:

CREATE INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】)

執行個體如下:

CREATE INDEX index_dept ON t_dept (deptno);

通過SQL語句ALTER TABLE建立普通索引

ALTER TABLE table_name ADD INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】)

執行個體如下:

ALTER TABLE t_dept ADDINDEX index_dept ON t_dept (deptno);

2.2 建立和查看唯一索引

建立表時建立唯一索引:

CREATE TABLE table_name(屬性名稱 資料類型,屬性名稱 資料類型,...屬性名稱 資料類型,UNIQUE INDEX|KEY 【索引名】 (屬性名稱 【長度】【ASC|DESC】))

在已經存在的表上建立唯一索引:

CREATE UNIQUE INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】)

通過SQL語句ALTER TABLE建立唯一索引

ALTER TABLE table_name ADD UNIQUE  INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】)

2.3 建立和查看全文索引

建立表時建立唯一索引:

CREATE TABLE table_name(屬性名稱 資料類型,屬性名稱 資料類型,...屬性名稱 資料類型,FULLTEXT INDEX|KEY 【索引名】 (屬性名稱 【長度】【ASC|DESC】))

在已經存在的表上建立唯一索引:

CREATE FULLTEXT INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】)

通過SQL語句ALTER TABLE建立唯一索引

ALTER TABLE table_name ADD FULLTEXT  INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】)

2.4 建立和查看多列索引

所謂多列索引,是指在建立索引時,所關聯的欄位不是一個欄位,而是多個欄位,雖然可以通過所關聯的欄位進行查詢,但是只有查詢條件中使用了所關聯欄位中的第一個欄位,多列索引才會被使用。

建立表時建立多列索引:

CREATE TABLE table_name(屬性名稱 資料類型,屬性名稱 資料類型,...屬性名稱 資料類型,INDEX|KEY 【索引名】 (屬性名稱 【長度】【ASC|DESC】),
　　　　　　　　　　　　...
　　　　　　　　　　　　(屬性名稱n 【長度】【ASC|DESC】)　　　　　　)

在已經存在的表上建立多列索引:

CREATE FULLTEXT INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】),
　　　　　　　　　　　　　　　　　　　　　　...
　　　　　　　　　　　　　　　　　　　　　　(屬性名稱n 【長度】【ASC|DESC】)

通過SQL語句ALTER TABLE建立多列索引

ALTER TABLE table_name ADD FULLTEXT  INDEX 索引名 ON 表名 (屬性名稱 【長度】 【ASC|DESC】),
　　　　　　　　　　　　　　　　　　　　　　...
　　　　　　　　　　　　　　　　　　　　(屬性名稱n 【長度】【ASC|DESC】)

3. 刪除索引

DROP INDEX index_name ON table_name

mysql之對索引的操作