哪些情況適合創(chuàng)建索引？ 字段的數(shù)值有唯一性的限制

索引本身可以起到約束的作用，比如唯一索引，主鍵索引都是可以起到唯一性約束的，因此在我們的數(shù)據(jù)表中如果某個(gè)字段是唯一性的，就可以直接創(chuàng)建唯一性索引，或者主鍵索引。這樣可以更快速地通過(guò)該索引來(lái)確定某條記錄。

業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。(來(lái)源:Alibaba)

說(shuō)明：不要以為唯一索引影響了 insert 速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的。

頻繁作為 WHERE 查詢條件的字段

某個(gè)字段在SELECT語(yǔ)句的 WHERE 條件中經(jīng)常被使用到，那么就需要給這個(gè)字段創(chuàng)建索引了。尤其是在數(shù)據(jù)量大的情況下，創(chuàng)建普通索引就可以大幅提升數(shù)據(jù)查詢的效率。

經(jīng)常 GROUP BY 和 ORDER BY 的列

索引就是讓數(shù)據(jù)按照某種順序進(jìn)行存儲(chǔ)或檢索，因此當(dāng)我們使用 GROUP BY 對(duì)數(shù)據(jù)進(jìn)行分組查詢，或者使用 ORDER BY 對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候，就需要對(duì)分組或者排序的字段進(jìn)行索引。如果待排序的列有多個(gè)，那么可以在這些列上建立組合索引。

如果既有GROUP BY又有ORDER BY，可以考慮聯(lián)合索引，由于GROUP BY先執(zhí)行，聯(lián)合索引中GROUP BY使用的字段排列在前面。

UPDATE、DELETE的WHERE條件列

當(dāng)我們對(duì)某條數(shù)據(jù)進(jìn)行UPDATE或者DELETE操作的時(shí)候，是否也需要對(duì)WHERE條件列創(chuàng)建索引呢？

對(duì)數(shù)據(jù)按照某個(gè)條件進(jìn)行查詢后再進(jìn)行 UPDATE 或 DELETE 的操作，如果對(duì) WHERE 字段創(chuàng)建了索引，就能大幅提升效率。原理是因?yàn)槲覀冃枰雀鶕?jù) WHERE 條件列檢索出來(lái)這條記錄，然后再對(duì)它進(jìn)行更新或刪除。如果進(jìn)行更新的時(shí)候，更新的字段是非索引字段，提升的效率會(huì)更明顯，這是因?yàn)榉撬饕侄胃虏恍枰獙?duì)索引進(jìn)行維護(hù)。

DISTINCT字段需要?jiǎng)?chuàng)建索引

有時(shí)候我們需要對(duì)某個(gè)字段進(jìn)行去重，使用 DISTINCT，那么對(duì)這個(gè)字段創(chuàng)建索引，也會(huì)提升查詢效率。索引會(huì)對(duì)數(shù)據(jù)按照某種順序進(jìn)行排序，所以在去重的時(shí)候也會(huì)快很多。

多表 JOIN 連接操作時(shí)，創(chuàng)建索引注意事項(xiàng)

首先，連接表的數(shù)量盡量不要超過(guò) 3 張，因?yàn)槊吭黾右粡埍砭拖喈?dāng)于增加了一次嵌套的循環(huán)，數(shù)量級(jí)增長(zhǎng)會(huì)非常快，嚴(yán)重影響查詢的效率。

其次，對(duì) WHERE 條件創(chuàng)建索引，因?yàn)?WHERE 才是對(duì)數(shù)據(jù)條件的過(guò)濾。如果在數(shù)據(jù)量非常大的情況下，沒(méi)有 WHERE 條件過(guò)濾是非常可怕的。

最后，對(duì)用于連接的字段創(chuàng)建索引，并且該字段在多張表中的類型必須一致。

使用列的類型小的創(chuàng)建索引

我們這里所說(shuō)的類型大小指的就是該類型表示的數(shù)據(jù)范圍的大小。

我們?cè)诙x表結(jié)構(gòu)的時(shí)候要顯式的指定列的類型，以整數(shù)類型為例，有TINYINT、MEDIUMINT、INT、BIGINT等，它們占用的存儲(chǔ)空間依次遞增，能表示的整數(shù)范圍當(dāng)然也是依次遞增。如果我們想要對(duì)某個(gè)整數(shù)列建立索引的話，在表示的整數(shù)范圍允許的情況下，盡量讓索引列使用較小的類型，比如我們能使用INT就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用 INT。這是因?yàn)椋?/p>

數(shù)據(jù)類型越小，在查詢時(shí)進(jìn)行的比較操作越快；數(shù)據(jù)類型越小，索引占用的存儲(chǔ)空間就越少，在一個(gè)數(shù)據(jù)頁(yè)內(nèi)就可以放下更多的記錄 ，從而減少磁盤 I/O 帶來(lái)的性能損耗，也就意味著可以把更多的數(shù)據(jù)頁(yè)緩存在內(nèi)存中，從而加快讀寫(xiě)效率。

這個(gè)建議對(duì)于表的主鍵來(lái)說(shuō)更加適用 ，因?yàn)椴粌H是聚簇索引中會(huì)存儲(chǔ)主鍵值，其他所有的二級(jí)索引的節(jié)點(diǎn)處都會(huì)存儲(chǔ)一份記錄的主鍵值，如果主鍵使用更小的數(shù)據(jù)類型，也就意味著節(jié)省更多的存儲(chǔ)空間和更高效的I/O。

使用字符串前綴創(chuàng)建索引

假設(shè)我們的字符串很長(zhǎng)，那存儲(chǔ)一個(gè)字符串就需要占用很大的存儲(chǔ)空間。在我們需要為這個(gè)字符串列建立索引時(shí)，那就意味著在對(duì)應(yīng)的B+樹(shù)中有這么兩個(gè)問(wèn)題：

B+樹(shù)索引中的記錄需要把該列的完整字符串存儲(chǔ)起來(lái)，更費(fèi)時(shí)，而目字符串越長(zhǎng)，在索引中占用的存儲(chǔ)空間越大。如果B+樹(shù)索引中索引列存儲(chǔ)的字符串很長(zhǎng)，那在做字符串比較時(shí)會(huì)占用更多的時(shí)間。

我們可以通過(guò)截取字段的前面一部分內(nèi)容建立索引，這個(gè)就叫前綴索引。這樣在查找記錄時(shí)雖然不能精確的定位到記錄的位置，但是能定位到相應(yīng)前綴所在的位置，然后根據(jù)前綴相同的記錄的主鍵值回表查詢完整的字符串值。既節(jié)約空間，又減少了字符串的比較時(shí)間，還大體能解決排序的問(wèn)題。

例如，TEXT和BLOG類型的字段，進(jìn)行全文檢索會(huì)很浪費(fèi)時(shí)間，如果只檢索字段前面的若干字符，這樣可以提高檢索速度。

示例：創(chuàng)建一張商戶表，因?yàn)榈刂纷侄伪容^長(zhǎng)，在地址字段上建立前綴索引

CREATE TABLE shop(address VARCHAR(120) NOT NULL);
ALTER TABLE shop ADD INDEX idx_address(address(12));

問(wèn)題是，截取多少呢？截取得多了，達(dá)不到節(jié)省索引存儲(chǔ)空間的目的；截取得少了，重復(fù)內(nèi)容太多，字段的散列度（選擇性）會(huì)降低。怎么計(jì)算不同的長(zhǎng)度的選擇性呢？

先看一下字段在全部數(shù)據(jù)中的選擇度：

SELECT COUNT(DISTINCT address) / COUNT(*) FROM shop;

通過(guò)不同長(zhǎng)度去計(jì)算，與全表的選擇性對(duì)比：

公式：

COUNT(DISTINCT LEFT(列名, 索引長(zhǎng)度)) / COUNT(*) 

例如：

SELECT COUNT(DISTINCT LEFT(address,10)) / COUNT(*) AS sub10, -- 截取前10個(gè)字符串的選擇度
COUNT(DISTINCT LEFT(address,15)) / COUNT(*) AS sub15, -- 截取前15個(gè)字符串的選擇度
COUNT(DISTINCT LEFT(address,20)) / COUNT(*) AS sub20, -- 截取前20個(gè)字符串的選擇度
COUNT(DISTINCT LEFT(address,25)) / COUNT(*) AS sub25 -- 截取前25個(gè)字符串的選擇度
FROM shop;

引申另一個(gè)問(wèn)題：索引列前綴對(duì)排序的影響

如果使用了索引列前綴，比方說(shuō)前邊只把a(bǔ)ddress列的 前12個(gè)字符 放到了二級(jí)索引中，下邊這個(gè)查詢可能就有點(diǎn)兒尷尬了：

SELECT * FROM shop
ORDER BY address
LIMIT 10;

因?yàn)槎?jí)索引中不包含完整的address列信息，所以無(wú)法對(duì)前12個(gè)字符相同，后邊的字符不同的記錄進(jìn)行排序，也就是使用索引列前綴的方式，無(wú)法支持使用索引排序，只能使用文件排序。

拓展：Alibaba《 Java開(kāi)發(fā)手冊(cè)(黃山版)》

【強(qiáng)制】在 varchar 字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒(méi)必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度。

說(shuō)明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，長(zhǎng)度為20的索引，區(qū)分度會(huì)高達(dá) 90%以上，可以使用COUNT(DISTINCT LEFT(列名, 索引長(zhǎng)度)) / COUNT(*) 的區(qū)分度來(lái)確定。

區(qū)分度高(散列性高)的列適合作為索引

列的基數(shù)指的是某一列中不重復(fù)數(shù)據(jù)的個(gè)數(shù)，比方說(shuō)某個(gè)列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，雖然有9條記錄，但該列的基數(shù)卻是3。也就是說(shuō)，在記錄行數(shù)一定的情況下，列的基數(shù)越大，該列中的值越分散；列的基數(shù)越小，該列中的值越集中。這個(gè)列的基數(shù)指標(biāo)非常重要，直接影響我們是否能有效的利用索引，最好為列的基數(shù)大的列建立索引，為基數(shù)太小列的建立索引效果可能不好。

可以使用公式：

SELECT COUNT(DISTINCT 列名) / COUNT(*) FROM 表名;

計(jì)算區(qū)分度，越接近1越好，一般超過(guò)33%就算是比較高效的索引了。

拓展：聯(lián)合索引把區(qū)分度高(散列性高)的列放在前面。

使用最頻繁的列放到聯(lián)合索引的左側(cè)

這樣也可以較少的建立一些索引。同時(shí)，由于"最左前綴原則"，可以增加聯(lián)合索引的使用率。

在多個(gè)字段都要?jiǎng)?chuàng)建索引的情況下，聯(lián)合索引優(yōu)于單值索引 限制索引的數(shù)目

在實(shí)際工作中，我們也需要注意平衡，索引的數(shù)目不是越多越好。我們需要限制每張表上的索引數(shù)量，建議單張表索引數(shù)量不超過(guò)6個(gè)。原因:

每個(gè)索引都需要占用 磁盤空間，索引越多，需要的磁盤空間就越大。索引會(huì)影響 INSERT、DELETE、UPDATE等語(yǔ)句的性能，因?yàn)楸碇械臄?shù)據(jù)更改的同時(shí)，索引也會(huì)進(jìn)行調(diào)整和更新，會(huì)造成負(fù)擔(dān)。優(yōu)化器在選擇如何優(yōu)化查詢時(shí)，會(huì)根據(jù)統(tǒng)一信息，對(duì)每一個(gè)可以用到的索引來(lái)進(jìn)行評(píng)估，以生成出一個(gè)最好的執(zhí)行計(jì)劃，如果同時(shí)有很多個(gè)索引都可以用于查詢，會(huì)增加MySQL優(yōu)化器生成執(zhí)行計(jì)劃時(shí)間，降低查詢性能。 哪些情況不適合創(chuàng)建索引 在where中使用不到的字段，不要設(shè)置索引

WHERE 條件（包括 GROUP BY、ORDER BY）里用不到的字段不需要?jiǎng)?chuàng)建索引，索引的價(jià)值是快速定位，如果起不到定位的字段通常是不需要?jiǎng)?chuàng)建索引的。

數(shù)據(jù)量小的表最好不要使用索引

如果表記錄太少，比如少于 1000 個(gè)，那么是不需要?jiǎng)?chuàng)建索引的。表記錄太少，是否創(chuàng)建索引對(duì)查詢效率的影響并不大。甚至說(shuō)，查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果。

結(jié)論：在數(shù)據(jù)表中的數(shù)據(jù)行數(shù)比較少的情況下，比如不到1000行，是不需要?jiǎng)?chuàng)建索引的。

有大量重復(fù)數(shù)據(jù)的列上不要建立索引

在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引，但字段中如果有大量重復(fù)數(shù)據(jù)，也不用創(chuàng)建索引。

比如在學(xué)生表的"性別"字段上只有“男”與“女”兩個(gè)不同值，因此無(wú)須建立索引。如果建立索引，不但不會(huì)提高查詢效率，反而會(huì)嚴(yán)重降低數(shù)據(jù)更新速度。

示例：要在 100 萬(wàn)行數(shù)據(jù)中查找其中的 50 萬(wàn)行（比如性別為男的數(shù)據(jù)），一旦創(chuàng)建了索引，你需要先訪問(wèn) 50萬(wàn)次索引，然后再訪問(wèn) 50 萬(wàn)次數(shù)據(jù)表，這樣加起來(lái)的開(kāi)銷比不使用索引可能還要大。

索引的價(jià)值是幫你快速定位。如果想要定位的數(shù)據(jù)有很多，那么索引就失去了它的使用價(jià)值，比如通常情況下的性別字段。

結(jié)論：當(dāng)數(shù)據(jù)重復(fù)度大，比如 高于10% 的時(shí)候，也不需要對(duì)這個(gè)字段使用索引。

避免對(duì)經(jīng)常更新的表創(chuàng)建過(guò)多的索引

第一層含義：頻繁更新的字段不一定要?jiǎng)?chuàng)建索引。因?yàn)楦聰?shù)據(jù)的時(shí)候，也需要更新索引，如果索引太多，在更新索引的時(shí)候也會(huì)造成負(fù)擔(dān)，從而影響效率。

第二層含義：避免對(duì)經(jīng)常更新的表創(chuàng)建過(guò)多的索引，并且索引中的列盡可能少。此時(shí)，雖然提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度。

不建議用無(wú)序的值作為索引

例如身份證、UUID(在索引比較時(shí)需要轉(zhuǎn)為ASCII，并且插入時(shí)可能造成頁(yè)分裂)、MD5、HASH、無(wú)序長(zhǎng)字符串等。

刪除不再使用或者很少使用的索引

表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)定期找出這些索引，將它們刪除，從而減少索引對(duì)更新操作的影響。

不要定義冗余或重復(fù)的索引 冗余索引

有時(shí)候有意或者無(wú)意的就對(duì)同一個(gè)列創(chuàng)建了多個(gè)索引，比如：index(a,b,c)相當(dāng)于index(a)、index(a,b)、index(a,b,c)。

示例：

CREATE TABLE person_info (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR (100) NOT NULL,
	birthday DATE NOT NULL,
	phone_number CHAR (11) NOT NULL,
	country VARCHAR (100) NOT NULL,
	PRIMARY KEY (id),
	KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
	KEY idx_name (name(10)) 
);

我們知道，通過(guò)idx_name_birthday_phone_number 索引就可以對(duì) name 列進(jìn)行快速搜索，再創(chuàng)建一個(gè)專門針對(duì) name 列的索引就算是一個(gè)冗余索引，維護(hù)這個(gè)索引只會(huì)增加維護(hù)的成本，并不會(huì)對(duì)搜索有什么好處。

重復(fù)索引

另一種情況，我們可能會(huì)對(duì)某個(gè)列重復(fù)建立索引：

CREATE TABLE repeat_index_demo (
	col1 INT PRIMARY KEY,
	co12 INT,
	UNIQUE uk_idx_c1 (col1),
	INDEX idx_c1 (col1)
);

我們看到，col1既是主鍵、又給它定義為一個(gè)唯一索引，還給它定義了一個(gè)普通索引?？墒侵麈I本身就會(huì)生成聚簇索引，所以定義的唯一索引和普通索引是重復(fù)的，這種情況要避免。

小結(jié)

索引是一把雙刃劍，可提高查詢效率，但也會(huì)降低插入和更新的速度并占用磁盤空間。

選擇索引的最終目的是為了使查詢的速度變快，上面給出的原則是最基本的準(zhǔn)則，但不能拘泥于上面的準(zhǔn)則，大家要在以后的學(xué)習(xí)和工作中進(jìn)行不斷的實(shí)踐，根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷，選擇最合適的索引方式。

到此這篇關(guān)于MySQL索引設(shè)計(jì)原則深入分析講解的文章就介紹到這了,更多相關(guān)MySQL索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論