mysql 索引的基礎操作彙總

1.為什麼使用索引： 
    資料庫物件中的索引其實和書的目錄類似，主要是為了提高從表中檢索資料的速度。由於資料儲存在資料庫表中，所以索引是建立在資料庫表物件上，由表中的一個欄位或者多個欄位生成的鍵組成，這些鍵儲存在資料結構(B-樹或者雜湊表)中，透過MySQL可以快速有效查詢與鍵相關聯的欄位。根據索引的儲存型別，可以將索引分為B型樹索引(BTREE)和雜湊索引(HASH)。注意：InnoDB和MyISAM儲存引擎支援BTREE型別索引，MEMORY儲存引擎支援HASH型別的索引，預設為前者索引。
    MySQL支援6種索引，分別是普通索引、唯一索引、全文索引、單列索引、多列索引、空間索引。
    以下情況適合建立索引：
    1. 經常被查詢的欄位。即在WHERE子句中出現的欄位。
    2. 在分組的欄位，即在GROUP BY 子句中出現的欄位。
    3. 存在依賴關係的子表和父表之間的聯合查詢，即主鍵或外來鍵欄位。
    4. 設定唯一完整約束的欄位。

2.建立和檢視索引： 
    索引的操作包括建立索引、檢視索引、 刪除索引。所謂建立索引，就是在表的一個欄位或多個欄位上建立索引，在MySQL中，可以通常三種方式來建立索引，建立表時建立索引、在已經存在的表上建立索引、透過SQL語句ALTER TABLE建立索引。 
2.1 建立和檢視普通索引： 
    所謂普通索引就是在建立索引時，不附加任何限制條件(唯一、非空等限制)。該型別的索引可以建立在任何型別的欄位上。 
2.1.1 建立表時建立普通索引：

語法格式如下：

?

1234567

CREATE TABLE table_name( 屬性名 資料型別, 屬性名 資料型別, ...... 屬性名 資料型別, INDEX|KEY [索引名] (屬性名1 [(長度)] [ASC|DESC]) );

   說明：INDEX或者KEY引數用來指定欄位為索引，“索引名”引數用來指定所建立索引的名稱，“屬性名1”引數用來指定索引所關聯的欄位的名稱，“長度”引數用來指定索引的長度，“ASC|DESC”引數用來指定升序還是降序。
   注意：在建立索引時，可以指定索引的長度。這是因為不同儲存引擎定義了表的最大索引數和最大索引長度。MySQL所支援的儲存引擎對每個表至少支援16個索引，總索引長度至少256位元組。

示例：

?

CREATE TABLE t_dept( deptno INT, dname VARCHAR(30), loc VARCHAR(40), INDEX index_deptno (deptno));

說明：
   可以透過 SHOW CREATE TABLE t_dept G 來檢視是否成功建立了索引；
   可以透過 EXPLAIN SELECT * FROM t_dept WHERE deptno=1G;來校驗資料庫表中索引是否被使用。如果執行結果中的possible_keys和key欄位處的值都為所建立的索引名index_deptno,則說明該索引已經存在，而且已經開始啟用。

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

透過SQL語句CREATE INDEX實現，其語法形式為：
CREATE INDEX 索引名
    ON 表名 (屬性名  [(長度)]  [ADC|DESC])

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

語法形式為：
ALTER TABLE table_name ADD INDEX|KEY 索引名 (屬性名 [(長度)] [ASC|DESC]);

2.2 建立和檢視唯一索引： 
    所謂唯一索引，就是在建立索引時，限制索引的值必須是唯一的。透過該型別的索引可以更快的查詢某條記錄。在MySQL中，根據建立索引的方式，可以分為自動索引和手動索引兩種。
    所謂自動索引，是指在資料庫表裡設定完整性約束時，該表會被系統自動建立索引。所謂手動索引，是指手動在表上建立索引。當設定表中的某個欄位為主鍵或唯一完整性約束時，系統就會自動建立關聯該欄位的唯一索引。 

2.2.1 建立表時建立唯一索引：

語法形式為：

?

CREATE TABLE table_name( 屬性名 資料型別, 屬性名 資料型別, ...... 屬性名 資料型別, UNIQUE INDEX|KEY [索引名] (屬性名1 [(長度)] [ASC | DESC]));

說明：UNIQUE INDEX或者UNIQUE KEY表示建立唯一索引。

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

透過SQL語句CREATE UNIQUE INDEX來實現，語法形式為：
CREATE UNIQUE INDEX 索引名
    ON 表名 (屬性名 [(長度)] [ASC|DESC]);

2.2.3 透過SQL語句ALTER TABLE建立唯一索引：

語法形式為：
ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名 (屬性名 [(長度)] [ASC|DESC]);

2.3 建立和檢視全文索引： 
    全文索引主要關聯在資料型別為CHAR、VARCHAR和TEXT的欄位上，以便能更加快速的查詢資料量較大的字串型別的欄位。MySQL只能在儲存引擎為MyISAM的資料庫表上建立全文引擎。預設情況下，全文引擎的搜尋執行方式為不區分大小寫，如果全文引擎所關聯的欄位為二進位制資料型別，則以區分大小寫的方式執行。 

2.3.1 建立表時建立全文索引：

語法形式為：

?

CREATE TABLE table_name ( 屬性名 資料型別, 屬性名 資料型別, ...... 屬性名 資料型別, FULLTEXT INDEX|KEY [索引名] (屬性名1 [(長度)] [ASC|DESC]));

2.3.2 在已經存在的表上建立全文索引：

語法形式為：
CREATE FULLTEXT INDEX 索引名
    ON 表名 (屬性名 [(長度)] [ASC|DESC]);

2.3.3 透過SQL語句ALTER TABLE 建立全文索引：

語法形式為：
ALTER TABLE table_name
    ADD FULLTEXT INDEX|KEY 索引名 (屬性名 [(長度)] [ASC|DESC]);

2.4 建立和檢視多列索引： 
    所謂多列索引，是指在建立索引時，所關聯的欄位不是一個欄位，而是多個欄位。雖然可以透過所關聯的欄位進行查詢，但是隻有查詢條件中使用了所關聯欄位中的第一個欄位，多列索引才會被使用。 
2.4.1 建立表時建立多列索引：

語法形式如下：

?

CREATE TABLE table_name( 屬性名 資料型別, 屬性名 資料型別, ...... 屬性名 資料型別, INDEX|KEY [索引名]  (屬性名1 [(長度)] [ASC|DESC]), ...... (屬性名1 [(長度)] [ASC|DESC]));

上述語句建立索引時，所關聯的欄位至少大於一個欄位。

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

語法形式為：

?

12345

CREATE INDEX 索引名 ON 表名 ( 屬性名 [(長度)] [ASC|DESC], ...... 屬性名n [(長度)] [ASC|DESC]);

2.4.3 透過SQL語句ALTRE TABLE 建立多列索引：

語法形式為：
ALTER TABLE table_name ADD INDEX|KEY 索引名(屬性名 [(長度)] [ASC|DESC],屬性名n [(長度)] [ASC|DESC]);

3. 刪除索引：

刪除索引的語法形式：
DROP INDEX index_name ON table_name

4. 檢視索引：

檢視索引的語法形式：
SHOW INDEX FROM table_name

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支援指令碼之家。