一、什么是前綴索引？

所謂前綴索引，說(shuō)白了就是對(duì)文本的前幾個(gè)字符建立索引（具體是幾個(gè)字符在建立索引時(shí)去指定），比如以產(chǎn)品名稱(chēng)的前 10 位來(lái)建索引，這樣建立起來(lái)的索引更小，查詢(xún)效率更快！

成都創(chuàng)新互聯(lián)公司為企業(yè)級(jí)客戶(hù)提高一站式互聯(lián)網(wǎng)+設(shè)計(jì)服務(wù)，主要包括成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、手機(jī)APP定制開(kāi)發(fā)、小程序定制開(kāi)發(fā)、宣傳片制作、LOGO設(shè)計(jì)等，幫助客戶(hù)快速提升營(yíng)銷(xiāo)能力和企業(yè)形象，創(chuàng)新互聯(lián)各部門(mén)都有經(jīng)驗(yàn)豐富的經(jīng)驗(yàn)，可以確保每一個(gè)作品的質(zhì)量和創(chuàng)作周期，同時(shí)每年都有很多新員工加入，為我們帶來(lái)大量新的創(chuàng)意。 

有點(diǎn)類(lèi)似于 Oracle 中對(duì)字段使用 Left 函數(shù)來(lái)建立函數(shù)索引，只不過(guò) MySQL 的這個(gè)前綴索引在查詢(xún)時(shí)是內(nèi)部自動(dòng)完成匹配的，并不需要使用 Left 函數(shù)。

二、為什么要用前綴索引？

可能有的同學(xué)會(huì)發(fā)出疑問(wèn)，為什么不對(duì)整個(gè)字段建立索引呢？

一般來(lái)說(shuō)，當(dāng)某個(gè)字段的數(shù)據(jù)量太大，而且查詢(xún)又非常的頻繁時(shí)，使用前綴索引能有效的減小索引文件的大小，讓每個(gè)索引頁(yè)可以保存更多的索引值，從而提高了索引查詢(xún)的速度。

比如，客戶(hù)店鋪名稱(chēng)，有的名稱(chēng)很長(zhǎng)，有的很短，如果完全按照全覆蓋來(lái)建索引，索引的存儲(chǔ)空間可能會(huì)非常的大，有的表如果索引創(chuàng)建的很多，甚至?xí)霈F(xiàn)索引存儲(chǔ)的空間都比數(shù)據(jù)表的存儲(chǔ)空間大很多，因此對(duì)于這種文本很長(zhǎng)的字段，我們可以截取前幾個(gè)字符來(lái)建索引，在一定程度上，既能滿(mǎn)足數(shù)據(jù)的查詢(xún)效率要求，又能節(jié)省索引存儲(chǔ)空間。

但是另一方面，前綴索引也有它的缺點(diǎn)，MySQL 中無(wú)法使用前綴索引進(jìn)行 ORDER BY 和 GROUP BY，也無(wú)法用來(lái)進(jìn)行覆蓋掃描，當(dāng)字符串本身可能比較長(zhǎng)，而且前幾個(gè)字符完全相同，這個(gè)時(shí)候前綴索引的優(yōu)勢(shì)已經(jīng)不明顯了，就沒(méi)有創(chuàng)建前綴索引的必要了。

因此這又回到一個(gè)概念，那就是關(guān)于索引的選擇性！

關(guān)于數(shù)據(jù)庫(kù)表索引的選擇性，我會(huì)單獨(dú)開(kāi)篇來(lái)講解，大家只需要記住一點(diǎn)：索引的選擇性越高則查詢(xún)效率越高，因?yàn)檫x擇性高的索引可以讓 MySQL 在查找時(shí)過(guò)濾掉更多的行，數(shù)據(jù)查詢(xún)速度更快！

當(dāng)某個(gè)字段內(nèi)容的前幾位區(qū)分度很高的時(shí)候，這個(gè)時(shí)候采用前綴索引，可以在查詢(xún)性能和空間存儲(chǔ)方面達(dá)到一個(gè)很高的性?xún)r(jià)比。

那么問(wèn)題來(lái)了，怎么創(chuàng)建前綴索引呢？

三、怎么創(chuàng)建前綴索引？

建立前綴索引的方式，方法很簡(jiǎn)單，通過(guò)如下方式即可創(chuàng)建！

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

其中prefix_length這個(gè)參數(shù)，就是前綴長(zhǎng)度的意思，通常通過(guò)如下方式進(jìn)行確認(rèn)，步驟如下：

第一步，先計(jì)算某字段全列的區(qū)分度。

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

第二步，然后再計(jì)算前綴長(zhǎng)度為多少時(shí)和全列的區(qū)分度最相似

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最后，不斷地調(diào)整prefix_length的值，直到和全列計(jì)算出區(qū)分度相近，最相近的那個(gè)值，就是我們想要的值。

下面以某個(gè)測(cè)試表為例，數(shù)據(jù)體量在 100 萬(wàn)以上，表結(jié)構(gòu)如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

測(cè)試一下正常的帶name條件查詢(xún)，效率如下：

select * from tb_test where name like '1805.59281427%'

我們以name?字段為例，創(chuàng)建前綴索引，找出最合適的prefix_length值。

首先，我們大致計(jì)算一下name字段全列的區(qū)分度。

可以看到，結(jié)果為 0.9945?，也就是說(shuō)全局不相同的數(shù)據(jù)率在99.45%這個(gè)比例。

下面我們一起來(lái)看看，不同的prefix_length值下，對(duì)應(yīng)的數(shù)據(jù)不重復(fù)比例。

當(dāng)prefix_length為5?，區(qū)分度為0.2237

當(dāng)prefix_length為10?，區(qū)分度為0.9944

當(dāng)prefix_length為11?，區(qū)分度為0.9945

通過(guò)對(duì)比，我們發(fā)現(xiàn)當(dāng)prefix_length為11?，最接近全局區(qū)分度，因此可以為name?創(chuàng)建一個(gè)長(zhǎng)度為11的前綴索引，創(chuàng)建索引語(yǔ)句如下：

alter table tb_test add key(name(11));

下面，我們?cè)僭囋嚿厦婺莻€(gè)語(yǔ)句查詢(xún)！

創(chuàng)建前綴索引之后，查詢(xún)效率倍增！

四、使用前綴索引需要注意的事項(xiàng)？

是不是所有的字段，都適合用前綴索引呢？

答案顯然不是，在上文我們也說(shuō)到了，當(dāng)某個(gè)索引的字符串列很大時(shí)，創(chuàng)建的索引也就變得很大，為了減小索引體積，提高索引的掃描速度，使用索引的前部分字符串作為索引值，這樣索引占用的空間就會(huì)大大減少，并且索引的選擇性也不會(huì)降低很多，這時(shí)前綴索引顯現(xiàn)的作用就會(huì)非常明顯，前綴索引本質(zhì)是索引查詢(xún)性能和存儲(chǔ)空間的一種平衡。

對(duì)于 BLOB 和 TEXT 列進(jìn)行索引，或者非常長(zhǎng)的 VARCHAR 列，就必須使用前綴索引，因?yàn)?MySQL 不允許索引它們的全部長(zhǎng)度。

但是如果某個(gè)字段內(nèi)容，比如前綴部分相似度很高，此時(shí)的前綴索引顯現(xiàn)效果就不會(huì)很明顯，采用覆蓋索引效果會(huì)更好！

五、小結(jié)

好了，本文主要圍繞前綴索引做了一次初步的知識(shí)講解，具體數(shù)據(jù)庫(kù)表索引的選擇性，還需要結(jié)合業(yè)務(wù)實(shí)際需求來(lái)考慮！

今天就說(shuō)這么多，后面的問(wèn)題，我們繼續(xù)再扯！

名稱(chēng)欄目：面試官提問(wèn)：什么是前綴索引？
分享網(wǎng)址：http://uogjgqi.cn/article/dpscoej.html