mysql中什麼是前綴索引

在mysql中，前綴索引是一種特殊索引類型，是對文字的前幾個字元建立索引；這種索引類型可以在一定程度上減少索引的大小，可以更有效率地處理一些特定的查詢操作。通常情況下，索引長度應該越短越好，因為短索引可以減少索引的大小，但是如果索引長度太短，可能會出現索引失效的情況，導致查詢效率反而變慢；因此，在使用前綴索引時，需要根據具體情況選擇適當的索引長度。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

一、什麼是前綴索引

MySQL是一款廣泛使用的關聯式資料庫管理系統，它在資料儲存和檢索方面具有很高的效率和靈活性。在MySQL中，索引是一種用於加速查詢操作的資料結構，它可以大幅提高查詢的速度和效率。而前綴索引則是MySQL中的一種特殊索引類型，它可以更有效率地處理一些特定的查詢操作。

所謂前綴索引，說白了就是對文字的前幾個字元建立索引（具體是幾個字元在建立索引時去指定），例如以產品名稱的前10 位來建立索引，這樣建立起來的索引更小，查詢效率更快！

前綴索引是指只索引字串的前綴部分，而不是整個字串。這種索引類型可以在一定程度上減少索引的大小，例如，如果有一個包含一千萬個字串的表，每個字串的長度為100個字符，如果使用完整字串作為索引，那麼索引大小將會非常大，查詢效率也會很低。但如果只索引字串的前10個字符，那麼索引大小就會大大減小，而且查詢效率也會明顯提高。

有點類似 Oracle 中對欄位使用 Left 函數來建立函數索引，只不過 MySQL 的這個前綴索引在查詢時是內部自動完成匹配的，並不需要使用 Left 函數。

在MySQL中，使用前綴索引需要指定索引的長度。通常情況下，索引長度應該越短越好，因為短索引可以減少索引的大小，但是如果索引長度太短，可能會出現索引失效的情況，導致查詢效率反而變慢。因此，在使用前綴索引時，需要根據具體情況選擇適當的索引長度。

二、為什麼要用前綴索引

可能有的同學會發出疑問，為什麼不對整個欄位建立索引呢？

一般來說，當某個欄位的資料量太大，而且查詢又非常的頻繁時，使用前綴索引能有效的減少索引檔案的大小，讓每個索引頁可以儲存更多的索引值，從而提高了索引查詢的速度。

例如，客戶店鋪名稱，有的名稱很長，有的很短，如果完全按照全覆蓋來建索引，索引的存儲空間可能會非常的大，有的表如果索引創建的很多，甚至會出現索引存儲的空間都比數據表的存儲空間大很多，因此對於這種文本很長的字段，我們可以截取前幾個字符來建索引，在一定程度上，既能滿足資料的查詢效率需求，又能節省索引儲存空間。

但另一方面，前綴索引也有它的缺點，MySQL 中無法使用前綴索引進行ORDER BY 和GROUP BY，也無法用來進行覆蓋掃描，當字串本身可能比較長，而且前幾個字元完全相同，這個時候前綴索引的優勢已經不明顯了，就沒有創建前綴索引的必要了。

因此這又回到一個概念，那就是關於索引的選擇性！

關於資料庫表索引的選擇性，我會單獨開篇來講解，大家只需要記住一點：索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL 在尋找時過濾掉更多的行，資料查詢速度更快！

當某個欄位內容的前幾位區分度很高的時候，這個時候採用前綴索引，可以在查詢效能和空間儲存方面達到一個很高的性價比。

那麼問題來了，要怎麼創建前綴索引呢？

三、怎麼建立前綴索引

建立前綴索引的方式，方法很簡單，可以透過以下方式建立！

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

其中prefix_length這個參數，就是前綴長度的意思，通常透過以下方式確認，步驟如下：

##第一步，先計算某欄位全列的區分度。

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

第二步，然後再計算前綴長度為多少時和全列的區分度最相似

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

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

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字段为例，创建前缀索引，找出最合适的prefix_length值。

首先，我们大致计算一下name字段全列的区分度。

可以看到，结果为 0.9945，也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看，不同的prefix_length值下，对应的数据不重复比例。

当prefix_length为5，区分度为0.2237

当prefix_length为10，区分度为0.9944

当prefix_length为11，区分度为0.9945

通过对比，我们发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

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

下面，我们再试试上面那个语句查询！

创建前缀索引之后，查询效率倍增！

四、使用前缀索引需要注意的事项

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

答案显然不是，在上文我们也说到了，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好！

【相关推荐：mysql视频教程】

以上是mysql中什麼是前綴索引的詳細內容。更多資訊請關注PHP中文網其他相關文章！