維護(hù)郵箱登錄系統(tǒng)贰锁，用戶表
mysql> select? f1, f2 from SUser where?email='xxx';? ? email 沒有索引巷屿，全表掃描固以。
創(chuàng)建索引的語(yǔ)句不指定前綴長(zhǎng)度，索引就會(huì)包含整個(gè)字符串。
mysql> alter? table SUser add index index1(email);? ? ? ? ? //含整個(gè)字符串
mysql> alter? table SUser add index index2(email(6));? ? ? //只取前 6 個(gè)字節(jié)
數(shù)據(jù)結(jié)構(gòu)和存儲(chǔ)區(qū)別
一憨琳、前綴索引
占用空間小诫钓，可能會(huì)增加額外的記錄掃描次數(shù)。
select? id,name,email from SUser where email='zhangssxyz@xxx.com';
index1執(zhí)行順序：
1.? 從 index1 索引樹找到滿足索引值是’zhangssxyz@xxx.com’的這條記錄篙螟，取得 ID2 的值菌湃；
2.? 查主鍵值=ID2 行，?email 值正確遍略，行記錄加入結(jié)果集惧所；
3.? 取 index1 索引樹上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)已經(jīng)不滿足email='zhangssxyz@xxx.com’的條件了绪杏，循環(huán)結(jié)束下愈。
只回主鍵索引取一次，系統(tǒng)認(rèn)為掃描一行蕾久。
index2執(zhí)行順序：
1.? 從 index2 索引樹找到滿足索引值是’zhangs’的記錄势似，找到第一個(gè) ID1；
2.? 查到主鍵=ID1 行腔彰，查出 email 的值不是’zhangssxyz@xxx.com’丟棄叫编；
3.? index2 下一條，這次值對(duì)了霹抛，加入結(jié)果集搓逾；
4. 重復(fù)上一步，idxe2 不是’zhangs’時(shí)杯拐，循環(huán)結(jié)束霞篡。
回主鍵索引取 4 次數(shù)據(jù)（index2要回索引），掃描了 4 行端逼。導(dǎo)致查詢次數(shù)多朗兵。
如果定義index2 不是 email(6) 而是 email(7），前綴’zhangss’只有一個(gè)顶滩，直接查到 ID2余掖。
前綴索引，定義好長(zhǎng)度礁鲁，節(jié)省空間盐欺，不用增加太多查詢成本。
二仅醇、多長(zhǎng)前綴好
建立索引時(shí)關(guān)注區(qū)分度冗美，區(qū)分度越高，重復(fù)鍵值越少析二。多少個(gè)不同的值粉洼，判斷用多長(zhǎng)前綴：
mysql> select? count(distinct email) as L from SUser;??
選取不同長(zhǎng)度的前綴來(lái)看這個(gè)值节预，看 4~7 個(gè)字節(jié)的前綴索引
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;
前綴索引很可能會(huì)損失區(qū)分度，設(shè)定可以接受的損失比例属韧，比如 5%安拟。 L4~L7 中，找出不小于 L * 95% 值挫剑，假設(shè)L6去扣、L7 都滿足，選擇前綴長(zhǎng)度為 6樊破。
三愉棱、前綴索引對(duì)覆蓋索引的影響
增加掃描行數(shù)，不止影響性能哲戚。
select id,email? from SUser where email='zhangssxyz@xxx.com';? 只返回 id 和 email 字段奔滑。
用 index1可用覆蓋索引，直接返回顺少，不回ID 索引朋其。index2（ email(6) ）的話，回 ID 索引脆炎，再判斷 email 字段值梅猿。系統(tǒng)并不確定是否截?cái)嗤暾畔ⅰ?/p>
前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化
郵箱效果不錯(cuò)。但公民信息系統(tǒng)秒裕，身份證號(hào)12 以上前綴索引袱蚓，滿足區(qū)分度
占用空間越大几蜻，數(shù)據(jù)頁(yè)索引值就越少喇潘，搜索效率越低。
四梭稚、占用空間小颖低，效率高
4.1 倒序存儲(chǔ)
身份證號(hào)倒過來(lái)存，每次查詢的時(shí)候：
mysql> select? field_list from t where id_card = reverse('input_id_card_string');
最后 6 位弧烤。不要忘記用count(distinct)驗(yàn)證忱屑。
4.2 hash 字段
創(chuàng)建整數(shù)字段，保存身份證校驗(yàn)碼（創(chuàng)建索引）暇昂。
mysql> alter? table t add id_card_crc int unsigned, add index(id_card_crc);
插入同時(shí)用 crc32()函數(shù)得到校驗(yàn)碼填到新字段想幻。可能存在沖突话浇，不同身份證號(hào) crc32() 結(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'
索引長(zhǎng)度4 字節(jié)幔崖，比原來(lái)小。
相同：
不支持范圍查詢。倒序存儲(chǔ)的字段上創(chuàng)建的索引是按照倒序字符串的方式排序的赏寇，已經(jīng)沒有辦法利用索引方式查出身份證號(hào)碼在 [ID_X, ID_Y]的所有市民了吉嫩。hash 只支持等值查詢。
區(qū)別：
1. 占用額外空間：倒序：存儲(chǔ)主鍵索引上嗅定，不消耗額外空間自娩， hash 增加一個(gè)字段。倒序存儲(chǔ)方式 4 字節(jié)不夠渠退，消耗差不多抵消忙迁。
2. CPU 消耗：倒序：寫和讀的時(shí)候，額外調(diào)用reverse 函數(shù)碎乃，hash ： crc32() 函數(shù)姊扔。reverse 消耗 CPU 資源小。
3. 查詢效率梅誓，hash 穩(wěn)定恰梢，沖突概率小。倒序存儲(chǔ)：前綴索引的方式梗掰，增加掃描行數(shù)嵌言。
小結(jié)
字符串字段創(chuàng)建索引場(chǎng)景，使用方式：
1.? 完整索引及穗，占用空間摧茴；
2.? 前綴索引，節(jié)省空間拥坛，增加查詢掃描次數(shù)蓬蝶，不能用覆蓋索引；
3.? 倒序存儲(chǔ)猜惋，再創(chuàng)建前綴索引丸氛，用于繞過字符串本身前綴的區(qū)分度不夠問題；
4.? hash 字段索引著摔，查詢性能穩(wěn)定缓窜，有額外存儲(chǔ)、計(jì)算消耗谍咆，不支持范圍掃描（跟第三種一樣）禾锤。
問題
維護(hù)學(xué)生信息數(shù)據(jù)庫(kù)，登錄名”學(xué)號(hào) @gmail.com",學(xué)號(hào)：15位數(shù)字摹察，城市編號(hào)(3)恩掷、學(xué)校編號(hào)(4、6)供嚎、入學(xué)年份（1~10）黄娘、順序編號(hào)（后5）峭状。
只考慮登錄驗(yàn)證，怎么設(shè)計(jì)登錄名索引逼争？
正优床、反向，重復(fù)度都高誓焦。入學(xué)年份+順序編號(hào)（9）
在此基礎(chǔ)上胆敞，數(shù)字類型存。201100001杂伟，占 4 個(gè)字節(jié)移层。就是一種 hash，字符串轉(zhuǎn)數(shù)字
評(píng)論1
學(xué)校每年預(yù)估2萬(wàn)新生稿壁，50年才100萬(wàn)記錄幽钢，直接全字段索引
hash索引適合，登錄檢驗(yàn)傅是，不需范圍查詢匪燕。倒序索引，區(qū)分度沒前綴高喧笔。
評(píng)論2
學(xué)號(hào)用bigint存儲(chǔ),4個(gè)字節(jié)帽驯，比前綴索引占用要小。比hash索引书闸，有區(qū)間查詢的優(yōu)勢(shì)
評(píng)論3
表數(shù)據(jù)量特別大：
字符串字段進(jìn)制壓縮尼变，限制每個(gè)字符范圍(如字母數(shù)字下劃線)。
每個(gè)字節(jié)并沒存8 bit信息量浆劲。單個(gè)字符取值只有n種可能性(把字符轉(zhuǎn)成0到n-1的數(shù)字)嫌术，把n進(jìn)制轉(zhuǎn)為為更高進(jìn)制存儲(chǔ)(ascii可看做是128進(jìn)制)。
減少索引長(zhǎng)度牌借，兼顧前綴匹配
