看看下面這條sql
select * from user where email ='jiepi@qq.com'如果我們不添加索引，肯定是要進行全表掃描的，那麼我們如何添加呢有兩種方式alter table user add index index1(email)alter table user add index index2(email(6))上面兩種方式都是在添加索引，不同點就是第二種添加的僅僅是郵箱的前綴索引，那麼他的結構表現如下圖
index2每個索引中僅僅存儲了欄位的前6個字符，而index1存儲了整個字符串,我們分別在不同索引下執行下面的語句select id,name,email from user where email='zhangsan@qq.com'在index1中找到符合條件的記錄，獲取id=5
然後使用id=1,在主鍵索引上獲取整行記錄
在index1尋找下一條記錄，直到發現不滿足位置，循環結束
在index2中找到符合條件的值，獲取到id=1
然後在主鍵索引中找到id=1的數據，發現不符合
在index2繼續尋找，id=2,然後在主鍵上找到行記錄，發現不符合
循環上面步驟，直到符合的記錄
我們發現使用index2雖然佔用的空間小，但是他對搜索的性能沒有更好的提高，而是要多掃描幾行記錄。
是不是發現前綴索引不是很好呢，然而並不是的，我們如果把索引的長度修改成8，然後在index2中僅僅找到一條符合記錄，因此只需要掃描一行就夠了。於是，我們發現選擇合適的前綴長度，既可以節省空間，也可以不用增加更多的查詢成本，
那麼如何選擇合適的前綴長度呢
建立索引之前，我們要關注欄位的區分度，區分度越大，性能越高，意味著重複的值就越少。查看某列的值不同的值
select count(distinct email) as L from user然後，我們分別取不同長度的前綴，比如我們看一下4-7個字節的前綴索引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 user當前使用前綴索引會丟失區分度，索引我們要預定一個可以接受的損失比例，比如5%，然後，計算出L4-L7中，找到不小於L*95%。假設L6,L7,滿足，你就可以選擇長度為6.前綴索引對覆蓋索引的影響
上面我們分析了，前綴索引影響掃描的行數，其實，他也是會影響覆蓋索引的，正如下面例子
select id,email from user where email='zhangsan@qq.com'select id,name,email from user where email='zhangsan@qq.com'如果我們只要返回id,email,因此使用index1索引，找到符合記錄利用覆蓋索引,直接返回索引的值，就可以滿足需求，但是如果我們使用index2,即使我們使用email(8)可以找到唯一一行數據，但是我們還是要進行那id的值去主鍵索引判斷是不是email的值，再不濟，我們使用email(18),完全包含欄位的長度，但是我們依然要回表查找主鍵索引，因為系統並確定前綴索引是否有截斷完成信息，其他方式
對於郵箱類型的欄位，使用前綴索引是一個不錯的選擇，但是我們要是遇到前綴區分度不大的欄位應該怎麼辦呢，
第一種，我們可以使用倒序存儲，如果存儲身份證，一般身份中前6位，一個省的值都是一樣，而後幾位區分度就還可以，索引我們可以使用下面語句查詢
select name from user where id_card=reverse('id_card_string')第二種，我們使用hash欄位，我們創建一個欄位，直接把身份證的值進行hash,存儲在這個欄位中，同時建立索引,由於可能存在多個身份證可能有一樣的hash值，因此還要判斷id_card的值是否準確
select name from user where id_card_crc=crc32('id_card_string') and id_card='id_card_string'上面兩種共同點就是不支持範圍查詢，他們的主要區別如下從佔用的空間方面說，倒序存儲方式在主鍵上不會消耗額外的空間，而hash索引要所創建一個欄位，但是如果倒排存儲長度過長的話，消耗的空間和hash差不多
兩種方式都要使用額外函數，如果從兩個函數的複雜度說的話，reverse函數額外消耗的CPU小點
查詢效率上，hash欄位的方式更穩定一些，雖然可能有衝突，但是這種概率很小，且認為平均掃描一行，而倒序存儲畢竟還要使用前綴索引方式，也就是會增加掃描的行數
如果文章對您有一絲絲幫助，麻煩點個關注，也歡迎轉發點讚,謝謝