建站
咨詢
售后
建站咨詢
新聞中心
一、什么是前綴索引？
所謂前綴索引，說白了就是對文本的前幾個字符建立索引（具體是幾個字符在建立索引時去指定），比如以產(chǎn)品名稱的前 10 位來建索引，這樣建立起來的索引更小，查詢效率更快！
成都創(chuàng)新互聯(lián)公司為企業(yè)級客戶提高一站式互聯(lián)網(wǎng)+設(shè)計服務(wù)，主要包括成都網(wǎng)站制作、網(wǎng)站設(shè)計、手機APP定制開發(fā)、小程序定制開發(fā)、宣傳片制作、LOGO設(shè)計等，幫助客戶快速提升營銷能力和企業(yè)形象，創(chuàng)新互聯(lián)各部門都有經(jīng)驗豐富的經(jīng)驗，可以確保每一個作品的質(zhì)量和創(chuàng)作周期，同時每年都有很多新員工加入，為我們帶來大量新的創(chuàng)意。
有點類似于 Oracle 中對字段使用 Left 函數(shù)來建立函數(shù)索引，只不過 MySQL 的這個前綴索引在查詢時是內(nèi)部自動完成匹配的，并不需要使用 Left 函數(shù)。
二、為什么要用前綴索引？
可能有的同學(xué)會發(fā)出疑問，為什么不對整個字段建立索引呢？
一般來說，當(dāng)某個字段的數(shù)據(jù)量太大，而且查詢又非常的頻繁時，使用前綴索引能有效的減小索引文件的大小，讓每個索引頁可以保存更多的索引值，從而提高了索引查詢的速度。
比如，客戶店鋪名稱，有的名稱很長，有的很短，如果完全按照全覆蓋來建索引，索引的存儲空間可能會非常的大，有的表如果索引創(chuàng)建的很多，甚至?xí)霈F(xiàn)索引存儲的空間都比數(shù)據(jù)表的存儲空間大很多，因此對于這種文本很長的字段，我們可以截取前幾個字符來建索引，在一定程度上，既能滿足數(shù)據(jù)的查詢效率要求，又能節(jié)省索引存儲空間。
但是另一方面，前綴索引也有它的缺點，MySQL 中無法使用前綴索引進行 ORDER BY 和 GROUP BY，也無法用來進行覆蓋掃描，當(dāng)字符串本身可能比較長，而且前幾個字符完全相同，這個時候前綴索引的優(yōu)勢已經(jīng)不明顯了，就沒有創(chuàng)建前綴索引的必要了。
因此這又回到一個概念，那就是關(guān)于索引的選擇性！
關(guān)于數(shù)據(jù)庫表索引的選擇性，我會單獨開篇來講解，大家只需要記住一點：索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓 MySQL 在查找時過濾掉更多的行，數(shù)據(jù)查詢速度更快！
當(dāng)某個字段內(nèi)容的前幾位區(qū)分度很高的時候，這個時候采用前綴索引，可以在查詢性能和空間存儲方面達到一個很高的性價比。
那么問題來了，怎么創(chuàng)建前綴索引呢？
三、怎么創(chuàng)建前綴索引？
建立前綴索引的方式，方法很簡單，通過如下方式即可創(chuàng)建！
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
其中prefix_length這個參數(shù)，就是前綴長度的意思，通常通過如下方式進行確認，步驟如下：
第一步，先計算某字段全列的區(qū)分度。
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
第二步，然后再計算前綴長度為多少時和全列的區(qū)分度最相似
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
最后，不斷地調(diào)整prefix_length的值，直到和全列計算出區(qū)分度相近，最相近的那個值，就是我們想要的值。
下面以某個測試表為例，數(shù)據(jù)體量在 100 萬以上，表結(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;
測試一下正常的帶name條件查詢，效率如下：
select * from tb_test where name like '1805.59281427%'
我們以name?字段為例，創(chuàng)建前綴索引，找出最合適的prefix_length值。
首先，我們大致計算一下name字段全列的區(qū)分度。
可以看到，結(jié)果為 0.9945?，也就是說全局不相同的數(shù)據(jù)率在99.45%這個比例。
下面我們一起來看看，不同的prefix_length值下，對應(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
通過對比，我們發(fā)現(xiàn)當(dāng)prefix_length為11?，最接近全局區(qū)分度，因此可以為name?創(chuàng)建一個長度為11的前綴索引，創(chuàng)建索引語句如下：
alter table tb_test add key(name(11));
下面，我們再試試上面那個語句查詢！
創(chuàng)建前綴索引之后，查詢效率倍增！
四、使用前綴索引需要注意的事項？
是不是所有的字段，都適合用前綴索引呢？
答案顯然不是，在上文我們也說到了，當(dāng)某個索引的字符串列很大時，創(chuàng)建的索引也就變得很大，為了減小索引體積，提高索引的掃描速度，使用索引的前部分字符串作為索引值，這樣索引占用的空間就會大大減少，并且索引的選擇性也不會降低很多，這時前綴索引顯現(xiàn)的作用就會非常明顯，前綴索引本質(zhì)是索引查詢性能和存儲空間的一種平衡。
對于 BLOB 和 TEXT 列進行索引，或者非常長的 VARCHAR 列，就必須使用前綴索引，因為 MySQL 不允許索引它們的全部長度。
但是如果某個字段內(nèi)容，比如前綴部分相似度很高，此時的前綴索引顯現(xiàn)效果就不會很明顯，采用覆蓋索引效果會更好！
五、小結(jié)
好了，本文主要圍繞前綴索引做了一次初步的知識講解，具體數(shù)據(jù)庫表索引的選擇性，還需要結(jié)合業(yè)務(wù)實際需求來考慮！
今天就說這么多，后面的問題，我們繼續(xù)再扯！
分享名稱：面試官提問：什么是前綴索引？
文章網(wǎng)址：http://www.5511xx.com/article/dpscoej.html
