MySQL 索引簡介

2022 年 4 月 16 日
筆記
MySQL

索引的含義和特點

索引是創建在表上的，是對資料庫表中一列或多列的值進行排序的一種結構，可以提高查詢速度。使用索引類似於使用新華字典的音序表，通過提取拼音，可以快速定位該數據的位置

索引有其明顯的優勢，也有不可避免的缺點：

創建和維護索引同樣需要耗費時間，並隨著數據量的增加而增加
索引也會佔用一定的物理空間
增加、刪除和修改數據時，要動態地維護索引

索引的分類和操作

1. 普通索引

所謂普通索引，就是在創建索引時，不附加任何限制條件（唯一、非空等限制），可以創建在任何數據類型的欄位上

創建表時直接創建索引

CREATE TABLE tablename(  
	propname1 type1 [CONSTRAINT1],
    propname2 type2 [CONSTRAINT2],
    ......
	INDEX|KEY [indexName] (propname1 [(length) [ASC|DESC]])  
);

在已存在的表上通過 CREATE 語句創建索引

CREATE INDEX indexName ON tablename (propname [(length) [ASC|DESC]])

通過 ALTER TABLE 語句創建

ALTER table tablename ADD INDEX|KEY indexName(propname [(length) [ASC|DESC]])

2. 唯一索引

所謂唯一索引，就是在創建索引時，限制索引的值必須唯一，主鍵就是一種特殊的唯一索引

創建表時直接創建索引

CREATE TABLE tablename(  
	propname1 type1 [CONSTRAINT1],
    propname2 type2 [CONSTRAINT2],
    ......
	UNIQUE INDEX|KEY [indexName] (propname1 [(length) [ASC|DESC]])  
);

在已存在的表上通過 CREATE 語句創建索引

CREATE UNIQUE INDEX indexName ON tablename (propname [(length) [ASC|DESC]])

通過 ALTER TABLE 語句創建

ALTER table tablename ADD UNIQUE INDEX|KEY indexName(propname [(length) [ASC|DESC]])

3. 全文索引

全文索引主要關聯在 CHAR、VARCHAR 和 TEXT 欄位上，以便能更加快速地查詢數據量較大的字元串類型欄位

創建表時直接創建索引

CREATE TABLE tablename(  
	propname1 type1 [CONSTRAINT1],
    propname2 type2 [CONSTRAINT2],
    ......
	FULLTEXT INDEX|KEY [indexName] (propname1 [(length) [ASC|DESC]])  
);

在已存在的表上通過 CREATE 語句創建索引

CREATE FULLTEXT INDEX indexName ON tablename (propname [(length) [ASC|DESC]])

通過 ALTER TABLE 語句創建

ALTER table tablename ADD FULLTEXT INDEX|KEY indexName(propname [(length) [ASC|DESC]])

4. 多列索引

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

創建表時直接創建索引

CREATE TABLE tablename(  
	propname1 type1 [CONSTRAINT1],
    propname2 type2 [CONSTRAINT2],
    ......
	INDEX|KEY [indexName] (propname1 [(length) [ASC|DESC]],
                           propname2 [(length) [ASC|DESC]],
                           ......);
);

在已存在的表上通過 CREATE 語句創建索引

CREATE INDEX indexName ON tablename (propname1 [(length) [ASC|DESC]],
                                     propname2 [(length) [ASC|DESC]],
                                     ......);

通過 ALTER TABLE 語句創建

ALTER table tablename ADD INDEX|KEY indexName(propname1 [(length) [ASC|DESC]],
											  propname2 [(length) [ASC|DESC]],
											  ......);

5. 刪除索引

DROP INDEX indexName ON tablename;

索引的設計原則

選擇唯一性索引，可以更快速地確定某條記錄
為經常需要排序、分組和聯合操作的欄位建立索引
為經常需要作為查詢條件的欄位建立索引
限制索引的數目，索引越多，佔用的空間就越多，修改表時對索引的重構和更新也很麻煩
盡量使用數據量少的索引，索引的值越長，查詢速度也會受影響
盡量使用前綴來索引，對於 TEXT 和 BLOB 類型的欄位，進行全文索引會很浪費時間，如果只檢索欄位前面的若干欄位，可以提高速度
刪除不再使用或很少使用的索引

Tags: MySQL