mysql长字符字段做索引_MySQL 筆記整理（11） --怎么給字符串字段加索引？

筆記記錄自林曉斌(丁奇)老師的《MySQL實戰45講》

(本篇內圖片均來自丁奇老師的講解，如有侵權，請聯系我刪除)

11) --怎么給字符串字段加索引？

日常工作中的登錄系統，你很可能會使用emai這個字段。因此也很容易遇到類似這樣的語句：

mysql> select *from user where email = 'xxx';

我們知道，如果沒有索引那就只能使用全表掃描。並且MySQL是支持前綴索引的，也就是說，你可以頂一個字符串的一部分作為索引。默認地，如果你創建索引的語句不指定前綴長度，那么索引就會包含整個字符串。由於email字段通常較長，如果你直接使用email作為索引的話，索引占用的空間就會較大。所以可以采用前綴索引的優勢，比如，建立索引email(6)，每個郵箱字段只取前6個字節。但這也會帶來損失，如你可能會增加額外的記錄掃描行數。因為前6位相同的郵箱就沒辦法直接通過eamil的索引來判斷了。因此我們建議使用前綴索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。

前綴索引對覆蓋索引的影響：

我們知道，普通索引存儲的是主鍵索引的值，因此，如果你不是select* 而是select id，email就可以不進行回表，也就是覆蓋索引。但此處有一個隱藏的問題，即，如果你使用了email字段來作索引，沒問題可以利用覆蓋索引的性質來進行快速的查詢。但如果你使用的是前綴索引email(255)，(我們db中的email數據的長度均不超過它)，雖然索引覆蓋了全部長度的email的值，還是不能使用覆蓋索引的性質。因為系統並不確定前綴索引是否截斷了完整的信息，因此必須進行回表。

其他方式：

當然，你也可能會遇到字段的前綴區分度不夠高的情況。如我們國家的身份證號，對於同一個城市的人，身份證號前幾位的區分度很低。此時，你可以有別的方式來進行處理如：

使用倒序來存儲數據。

使用Hash字段存儲。

這兩種方式的相同點是都不支持范圍查詢，並且都提供了足夠多的區分度。另外，Hash計算的結果雖有沖突的可能，但概率很低，因此可以認為每次查詢的掃描行數接近1.但使用Hash的方式會額外增加計算的消耗以及額外的存儲空間的消耗。

上期問題：

如果沒有session A的配合，只是單獨執行 delete from t; call idata()； explain這三條語句，會看到explain結果中rows字段其實還是再10000左右，即使用了索引，這是為什么呢？

答：delete語句刪掉了所有的數據，然后通過call idata()插入了10萬行數據，看上去是覆蓋了原來的10萬行。但是，session A開啟了事務並沒有提交，所以之前插入的10w行數據不能刪除，這樣，之前的數據每一行都會有兩個版本，舊版本是delete之前的數據，新版本是標記為delete的語句。這樣索引a上的數據其實是有兩份的。由於Mysql是使用刪除標記來刪除記錄的，並不從索引和數據文件中真正刪除。如果delete和insert中間的間隔相對較小，purge線程還沒來得及清理記錄。如果主鍵相同的情況下，新insert會沿用delete之前的記錄空間，因此統計信息不變。

問題:

如果你要維護學生信息的數據庫，學生登錄名統一是"學號@gamail.com"，學號的規則是十五位的數字，前三位是城市編號，四到六位是學校編號，七到十位是入學年份，最后五位是順序編號，只考慮登錄驗證的話，你會怎么設計這個登錄名的索引呢？