Mysql索引建立刪除及使用的代價是什麼

一、Mysql 中索引的建立刪除

innodb 和 myisam 引擎會自動為主鍵或是帶有 UNIQUE 屬性的欄位建立索引。

如果要為其他欄位建立索引，就需要顯示的指明。

1. 建立表格的時候建立索引

CREATE TABLE 表名 (
    各个列的信息...,
    (KEY/INDEX) 索引名 (需要被索引的单个列或多个列)
)

注意，KEY 和 INDEX 任用其一即可，是同義詞。

例如在建立index_demo 表時為c2 和c3 欄位新增一個聯合索引：

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);

這裡建立的索引的名稱是idx_c2_c3，雖然索引名字可以任意起，但建議以idx_為前綴，後面跟著要簡歷的索引的列名，多個列用下劃線隔開。

2. 修改表結構時候加入索引

ALTER TABLE 表名 ADD (KEY/INDEX) 索引名 (需要被索引的单个列或多个列);

3. 修改表結構時候刪除索引

ALTER TABLE 表名 DROP (KEY/INDEX) 索引名;

現在刪除掉最開始建的索引idx_c2_c3，可以這樣寫：

ALTER TABLE index_demo DROP INDEX idx_c2_c3;

二、索引的代價

索引雖然是個好東西，但是不能肆意創建。

1. 空間上的代價

這個是很顯而易見的。

• 每建立一個索引，就要為它建立一顆 B 樹。

• 而每一顆 B 樹的每一個節點都是資料頁，預設會佔用16kb的儲存空間。

• 一顆很大的 B 樹又由許多資料頁組成。

所以，這會佔用很大一片儲存空間。

2. 時間上的代價

維護順序的耗時

每當對表中的資料進行增刪改操作時，都需要修改各個 B 樹 索引。

我們知道：

• B 樹中的每層節點都是依照索引列的值從小到大的順序組成了雙向鍊錶。

• 無論是葉子節點或內節點，內部中的記錄都是依照索引列的值的大小順序，形成了一個單鍊錶。

所以，當進行增刪改操作時，可能會對節點和記錄的排序造成破壞，那麼儲存引擎就需要額外的時間進行頁面分割、回收等操作，以維護節點和記錄的排序。

如果建立了許多索引，可想而知每個索引對應的B 樹都要進行相關的維護操作，這要帶來多大的消耗。

執行計劃分析的耗時

在執行查詢語句之前，首先會產生一個執行計劃。

一般來說，一條查詢語句在執行的過程中最多使用一個二級索引，在產生執行計劃時需要計算使用不同索引執行查詢時所需的成本，最後選擇最低的那個索引進行查詢。

如果建立了太多的索引，可能會導致在進行成本分析的時候就耗時太多，從而影響查詢語句的執行效能。

以上是Mysql索引建立刪除及使用的代價是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！