一、Mysql 中索引的創(chuàng)建刪除

innodb 和 myisam 引擎會(huì)自動(dòng)為主鍵或者帶有 UNIQUE 屬性的列建立索引。

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

1. 創(chuàng)建表的時(shí)候創(chuàng)建索引

CREATE TABLE 表名 (
    各個(gè)列的信息...,
    (KEY/INDEX) 索引名 (需要被索引的單個(gè)列或多個(gè)列)
)

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

比如在創(chuàng)建 index_demo 表時(shí)為c2 和 c3 列添加一個(gè)聯(lián)合索引：

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

這里創(chuàng)建的索引的名稱是idx_c2_c3，雖然索引名字可以任意起，但是建議以idx_為前綴，后面跟著要簡歷的索引的列名，多個(gè)列用下劃線隔開。

2. 修改表結(jié)構(gòu)時(shí)候添加索引

ALTER TABLE 表名 ADD (KEY/INDEX) 索引名 (需要被索引的單個(gè)列或多個(gè)列);

3. 修改表結(jié)構(gòu)時(shí)候刪除索引

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

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

ALTER TABLE index_demo DROP INDEX idx_c2_c3;

二、索引的代價(jià)

索引雖然是個(gè)好東西，但是不能肆意創(chuàng)建。

1. 空間上的代價(jià)

這個(gè)是很顯而易見的。

• 每建立一個(gè)索引，就要為它建立一顆 B+ 樹。
• 而每一顆 B+ 樹的每一個(gè)節(jié)點(diǎn)都是一個(gè)數(shù)據(jù)頁，默認(rèn)會(huì)占用16kb的存儲(chǔ)空間。
• 一顆很大的 B+ 樹又是由許多數(shù)據(jù)頁組成。

所以，這會(huì)占用很大一片存儲(chǔ)空間。

2. 時(shí)間上的代價(jià)

維護(hù)順序的耗時(shí)

每當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增刪改操作時(shí)，都需要修改各個(gè) B+樹 索引。

我們知道：

• B+ 樹中的每層節(jié)點(diǎn)都是按照索引列的值從小到大的順序組成了雙向鏈表。
• 無論是葉子節(jié)點(diǎn)還是內(nèi)節(jié)點(diǎn)，內(nèi)部中的記錄都是按照索引列的值的大小順序，形成了一個(gè)單鏈表。

所以，當(dāng)進(jìn)行增刪改操作時(shí)，可能會(huì)對(duì)節(jié)點(diǎn)和記錄的排序造成破壞，那么存儲(chǔ)引擎就需要額外的時(shí)間進(jìn)行頁面分裂、回收等操作，以維護(hù)節(jié)點(diǎn)和記錄的排序。

如果建立了很多索引，可想而知每個(gè)索引對(duì)應(yīng)的B+ 樹都要進(jìn)行相關(guān)的維護(hù)操作，這要帶來多大的消耗。

執(zhí)行計(jì)劃分析的耗時(shí)

在執(zhí)行查詢語句之前，首先會(huì)生成一個(gè)執(zhí)行計(jì)劃。

一般來說，一條查詢語句在執(zhí)行的過程中最多使用一個(gè)二級(jí)索引，在生成執(zhí)行計(jì)劃時(shí)需要計(jì)算使用不同索引執(zhí)行查詢時(shí)所需的成本，最后選擇最低的那個(gè)索引進(jìn)行查詢。

如果建立了太多的索引，可能導(dǎo)致在進(jìn)行成本分析的時(shí)候就耗時(shí)太多，從而影響查詢語句的執(zhí)行性能。

小結(jié)

綜上所述，在一個(gè)表中建立的索引越多，占用的存儲(chǔ)空間就越多，在增刪改記錄或者生成執(zhí)行計(jì)劃時(shí)性能也就越差。

所以，該怎樣建立又好又少的索引？

本文參考書籍：《mysql是怎樣運(yùn)行的》

更多關(guān)于Mysql索引創(chuàng)建刪除的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論