比如，這兩個(gè)在 email 字段上創(chuàng)建索引的語句：

mysql> alter table SUser add index index1(email);

或

mysql> alter table SUser add index index2(email(6));

第一個(gè)語句創(chuàng)建的 index1 索引里面，包含了每個(gè)記錄的整個(gè)字符串；而第二個(gè)語句創(chuàng)建的 index2 索引里面，對(duì)于每個(gè)記錄都是只取前 6 個(gè)字節(jié)。

那么，這兩種不同的定義在數(shù)據(jù)結(jié)構(gòu)和存儲(chǔ)上有什么區(qū)別呢？

?圖1: email索引結(jié)構(gòu)

?圖2: email(6)索引結(jié)構(gòu)

從圖中你可以看到，由于 email(6) 這個(gè)索引結(jié)構(gòu)中每個(gè)郵箱字段都只取前 6 個(gè)字節(jié)(即：zhangs)，所以占用的空間會(huì)更小，這就是使用前綴索引的優(yōu)勢(shì)。

但，這同時(shí)帶來的損失是，可能會(huì)增加額外的記錄掃描次數(shù)。

接下來，我們?cè)倏纯聪旅孢@個(gè)語句，在這兩個(gè)索引定義下分別是怎么執(zhí)行的。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是 index1(即 email 整個(gè)字符串的索引結(jié)構(gòu))，執(zhí)行順序是這樣的：

從 index1 索引樹找到滿足索引值是’zhangssxyz@xxx.com’的這條記錄，取得 ID2 的值；

到主鍵上查到主鍵值是 ID2 的行，判斷 email 的值是正確的，將這行記錄加入結(jié)果集；

取 index1 索引樹上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)已經(jīng)不滿足 email='zhangssxyz@xxx.com’的條件了，循環(huán)結(jié)束。

這個(gè)過程中，只需要回主鍵索引取一次數(shù)據(jù)，所以系統(tǒng)認(rèn)為只掃描了一行。

如果使用的是 index2(即 email(6) 索引結(jié)構(gòu))，執(zhí)行順序是這樣的：

從 index2 索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個(gè)是 ID1；

到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不是’zhangssxyz@xxx.com’，這行記錄丟棄；

取 index2 上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判斷，這次值對(duì)了，將這行記錄加入結(jié)果集；

重復(fù)上一步，直到在 idxe2 上取到的值不是’zhangs’時(shí)，循環(huán)結(jié)束。

在這個(gè)過程中，要回主鍵索引取 4 次數(shù)據(jù)，也就是掃描了 4 行。

但是，對(duì)于這個(gè)查詢語句來說，如果你定義的 index2 不是 email(6) 而是 email(7)，也就是說取 email 字段的前 7 個(gè)字節(jié)來構(gòu)建索引的話，即滿足前綴’zhangss’的記錄只有一個(gè)，也能夠直接查到 ID2，只掃描一行就結(jié)束了。

也就是說使用前綴索引，定義好長度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本。

當(dāng)要給字符串創(chuàng)建前綴索引時(shí)，有什么方法能夠確定我應(yīng)該使用多長的前綴呢？

實(shí)際上，我們?cè)诮⑺饕龝r(shí)關(guān)注的是區(qū)分度，區(qū)分度越高越好。因?yàn)閰^(qū)分度越高，意味著重復(fù)的鍵值越少。因此，我們可以通過統(tǒng)計(jì)索引上有多少個(gè)不同的值來判斷要使用多長的前綴。

你可以使用下面這個(gè)語句，算出這個(gè)列上有多少個(gè)不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次選取不同長度的前綴來看這個(gè)值，比如我們要看一下 4~7 個(gè)字節(jié)的前綴索引，可以用這個(gè)語句：

mysql> select

count(distinct left(email,4))as L4,

count(distinct left(email,5))as L5,

count(distinct left(email,6))as L6,

count(distinct left(email,7))as L7,

from SUser;

當(dāng)然，使用前綴索引很可能會(huì)損失區(qū)分度，所以你需要預(yù)先設(shè)定一個(gè)可以接受的損失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假設(shè)這里 L6、L7 都滿足，你就可以選擇前綴長度為 6。

前綴索引對(duì)覆蓋索引的影響

你先來看看這個(gè) SQL 語句：

select id,email from SUser where email='zhangssxyz@xxx.com';

相比，這個(gè)語句只要求返回 id 和 email 字段。

所以，如果使用 index1(即 email 整個(gè)字符串的索引結(jié)構(gòu))的話，可以利用覆蓋索引，從 index1 查到結(jié)果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2(即 email(6) 索引結(jié)構(gòu))的話，就不得不回到 ID 索引再去判斷 email 字段的值

即使你將 index2 的定義修改為 email(18) 的前綴索引，這時(shí)候雖然 index2 已經(jīng)包含了所有的信息，但 InnoDB 還是要回到 id 索引再查一下，因?yàn)橄到y(tǒng)并不確定前綴索引的定義是否截?cái)嗔送暾畔ⅰ?/p>

其他方式

對(duì)于類似于郵箱這樣的字段來說，使用前綴索引的效果可能還不錯(cuò)。但是，遇到前綴的區(qū)分度不夠好的情況時(shí)，我們要怎么辦呢？

假設(shè)你維護(hù)的數(shù)據(jù)庫是一個(gè)市的公民信息系統(tǒng)，這時(shí)候如果對(duì)身份證號(hào)做長度為 6 的前綴索引的話，這個(gè)索引的區(qū)分度就非常低了。

按照我們前面說的方法，可能你需要?jiǎng)?chuàng)建長度為 12 以上的前綴索引，才能夠滿足區(qū)分度要求。

但是，索引選取的越長，占用的磁盤空間就越大，相同的數(shù)據(jù)頁能放下的索引值就越少，搜索的效率也就會(huì)越低。

第一種方式是使用倒序存儲(chǔ)。如果你存儲(chǔ)身份證號(hào)的時(shí)候把它倒過來存，每次查詢的時(shí)候，你可以這么寫：

mysql> select field_list from t where id_card = reverse('input_id_card_string')

由于身份證號(hào)的最后 6 位沒有地址碼這樣的重復(fù)邏輯，所以最后這 6 位很可能就提供了足夠的區(qū)分度。當(dāng)然了，實(shí)踐中你不要忘記使用 count(distinct) 方法去做個(gè)驗(yàn)證。

第二種方式是使用 hash 字段。你可以在表上再創(chuàng)建一個(gè)整數(shù)字段，來保存身份證的校驗(yàn)碼，同時(shí)在這個(gè)字段上創(chuàng)建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新記錄的時(shí)候，都同時(shí)用 crc32() 這個(gè)函數(shù)得到校驗(yàn)碼填到這個(gè)新字段。由于校驗(yàn)碼可能存在沖突，也就是說兩個(gè)不同的身份證號(hào)通過 crc32() 函數(shù)得到的結(jié)果可能是相同的，所以你的查詢語句 where 部分要判斷 id_card 的值是否精確相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

這樣，索引的長度變成了 4 個(gè)字節(jié)，比原來小了很多。

接下來，我們?cè)僖黄鹂纯词褂玫剐虼鎯?chǔ)和使用 hash 字段這兩種方法的異同點(diǎn)。

首先，它們的相同點(diǎn)是，都不支持范圍查詢。倒序存儲(chǔ)的字段上創(chuàng)建的索引是按照倒序字符串的方式排序的，已經(jīng)沒有辦法利用索引方式查出身份證號(hào)碼在[ID_X, ID_Y]的所有市民了。同樣地，hash 字段的方式也只能支持等值查詢。

它們的區(qū)別，主要體現(xiàn)在以下三個(gè)方面：

從占用的額外空間來看，倒序存儲(chǔ)方式在主鍵索引上，不會(huì)消耗額外的存儲(chǔ)空間，而 hash 字段方法需要增加一個(gè)字段。當(dāng)然，倒序存儲(chǔ)方式使用 4 個(gè)字節(jié)的前綴長度應(yīng)該是不夠的，如果再長一點(diǎn)，這個(gè)消耗跟額外這個(gè) hash 字段也差不多抵消了。

在 CPU 消耗方面，倒序方式每次寫和讀的時(shí)候，都需要額外調(diào)用一次 reverse 函數(shù)，而 hash 字段的方式需要額外調(diào)用一次 crc32() 函數(shù)。如果只從這兩個(gè)函數(shù)的計(jì)算復(fù)雜度來看的話，reverse 函數(shù)額外消耗的 CPU 資源會(huì)更小些。

從查詢效率上看，使用 hash 字段方式的查詢性能相對(duì)更穩(wěn)定一些。因?yàn)?crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認(rèn)為每次查詢的平均掃描行數(shù)接近 1。而倒序存儲(chǔ)方式畢竟還是用的前綴索引的方式，也就是說還是會(huì)增加掃描行數(shù)。小結(jié)

Q&A

char 和 varchar 可以設(shè)置長度，這個(gè)長度是干什么的，對(duì)于不同字符集又有什么影響？一直看到不同的解釋，懇請(qǐng)老師專業(yè)的解釋一下

char(N)表示“最長存N，但是如果字符串小于N，用空格補(bǔ)到N”

varchar(N)表示“最長存N，如果字符串小于N，按照實(shí)際長度來存”

什么的，對(duì)于不同字符集又有什么影響？一直看到不同的解釋，懇請(qǐng)老師專業(yè)的解釋一下

char(N)表示“最長存N，但是如果字符串小于N，用空格補(bǔ)到N”

varchar(N)表示“最長存N，如果字符串小于N，按照實(shí)際長度來存”

本文地址：https://blog.csdn.net/MariaOzawa/article/details/107320595

如您對(duì)本文有疑問或者有任何想說的，請(qǐng)點(diǎn)擊進(jìn)行留言回復(fù)，萬千網(wǎng)友為您解惑！

總結(jié)

以上是生活随笔為你收集整理的mysql索引 钱缀_mysql字符串前缀索引的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。