MySQL 索引簡介

低吟不作語發表於2022-04-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;

索引的設計原則

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

相關文章