软件开发培训班 >> 编程开发 >> MySQL


MySQL面试题讲解之如何设置Hash索引

闄や簡B-Tree 绱㈠紩锛孧ySQL杩樻彁渚涗簡濡備笅绱㈠紩锛?/p>

  • Hash绱㈠紩

鍙湁Memory寮曟搸鏀寔锛屽満鏅畝鍗?/p>

  • R-Tree绱㈠紩

MyISAM鐨勪竴涓壒娈婄储寮曠被鍨嬶紝涓昏鐢ㄤ簬鍦扮悊绌洪棿鏁版嵁绫诲瀷

  • Full-text

MyISAM鐨勪竴涓壒娈婄储寮曪紝涓昏鐢ㄤ簬鍏ㄦ枃绱㈠紩锛屼粠MySQL 5.6寮�濮婭nnoDB鏀寔鍏ㄦ枃绱㈠紩

绱㈠紩 / 瀛樺偍寮曟搸MyISAMInnoDBMemoryB-Tree绱㈠紩鏀寔鏀寔鏀寔HASH绱㈠紩涓嶆敮鎸佷笉鏀寔鏀寔R-Tree绱㈠紩鏀寔鏀寔涓嶆敮鎸丗ull-text绱㈠紩鏀寔鏀寔涓嶆敮鎸?/p>

鏈�甯哥敤鐨勭储寮曚篃灏辨槸B-tree绱㈠紩鍜孒ash绱㈠紩锛屼笖鍙湁Memory锛?NDB涓ょ寮曟搸鏀寔Hash绱㈠紩銆?Hash绱㈠紩閫備簬key-value鏌ヨ锛岄�氳繃Hash绱㈠紩姣擝-tree绱㈠紩鏌ヨ鏇村姞杩呴�熴�備絾Hash绱㈠紩涓嶆敮鎸佽寖鍥存煡鎵句緥濡?lt;><==,>==绛夈�?Memory鍙湁鍦?="鐨勬潯浠朵笅鎵嶄細浣跨敤hash绱㈠紩

MySQL鍦?8.0鎵嶆敮鎸佸嚱鏁扮储寮曪紝鍦ㄦ涔嬪墠鍙兘瀵瑰垪鐨勫墠闈㈡煇涓�閮ㄥ垎杩涜绱㈠紩锛屼緥濡傛爣棰榯itle瀛楁锛屽彲浠ュ彧鍙杢itle鐨勫墠10涓瓧绗︾储寮曪紝杩欐牱鐨勭壒鎬уぇ澶х缉灏忎簡绱㈠紩鏂囦欢鐨勫ぇ灏忥紝浣嗗墠缂�绱㈠紩涔熸湁缂虹偣锛屽湪order by鍜実roup by鎿嶄綔鏃跺け鏁堛�?/p> 

create index idx_title on film(title(10));

1 鐗圭偣

鍙瓨鍦ㄦ暟缁勶紝鐢ㄤ竴涓猦ash鍑芥暟鎶妅ey杞崲鎴愪竴涓‘瀹氱殑鍐呭瓨浣嶇疆锛岀劧鍚庢妸value鏀惧湪鏁扮粍鐨勮浣嶇疆銆備娇鐢?hash 鑷劧浼氭湁鍝堝笇鍐茬獊鍙兘锛孧ySQL 閲囧彇鎷夐摼娉曡В鍐炽�?/p>

Hash绱㈠紩鍩轰簬Hash琛ㄥ疄鐜帮紝鍙湁鏌ヨ鏉′欢绮剧‘鍖归厤Hash绱㈠紩涓殑鍒楁椂锛屾墠鑳藉浣跨敤鍒癶ash绱㈠紩銆傚浜嶩ash绱㈠紩涓殑鎵�鏈夊垪锛屽瓨鍌ㄥ紩鎿庝細涓烘瘡琛岃绠椾竴涓猦ashcode锛孒ash绱㈠紩涓瓨鍌ㄧ殑灏辨槸hashcode銆?/p>

  • 渚嬪涓�涓淮鎶や簡韬唤璇佸彿鍜屽鍚嶇殑琛紝鏍规嵁韬唤璇佸彿鏌ユ壘瀵瑰簲鍚嶅瓧锛屽叾hash绱㈠紩濡備笅锛?/li>

闃块噷闈㈣瘯瀹橈細璁捐涓狹ySQL鐨凥ash绱㈠紩鍚э紵

姣斿鎴戜滑鎯虫煡ID_card_n4瀵瑰簲username锛?/p>

  • 灏咺D_card_n4閫氳繃hash鍑芥暟绠楀嚭A
  • 鎸夐『搴忛亶鍘嗭紝鎵惧埌User4

鍥涗釜ID_card_n鍊煎苟涓嶄竴瀹氶�掑锛岃繖鏍峰嵆浣垮鍔犳柊鐨刄ser锛岄�熷害涔熷揩锛屽彧闇�鍦ㄥ悗杩藉姞銆?褰撶劧缂虹偣涔熷緢鏄庢樉锛屼笉鏄湁搴忥紝鎵�浠ash绱㈠紩鍋氬尯闂存煡璇㈤�熷害寰堟參銆傛瘮濡傝鎵捐韩浠借瘉鍙峰湪[ID_card_X, ID_card_Y]鍖洪棿鐨勬墍鏈夌敤鎴凤紝灏遍』鍏ㄨ〃鎵弿銆?/p>

2 Hash绱㈠紩鐨勭己闄?/h2>
  • 蹇呴』浜屾鏌ユ壘
  • 涓嶆敮鎸侀儴鍒嗙储寮曟煡鎵俱�佽寖鍥存煡鎵?/li>
  • 鍝堝笇鐮佸彲鑳藉瓨鍦ㄥ搱甯屽啿绐侊紝濡傛灉hash 绠楁硶璁捐涓嶅ソ锛岀鎾炶繃澶氾紝鎬ц兘涔熶細鍙樺樊
  • 绱㈠紩瀛樻斁鐨勬槸hash鍊?鎵�浠ヤ粎鏀寔 < = > 浠ュ強 IN
  • 鏃犳硶閫氳繃鎿嶄綔绱㈠紩鏉ユ帓搴忥紝鍥犱负瀛樻斁鐨勬椂鍊欎細缁忚繃hash璁＄畻锛屼絾鏄绠楃殑hash鍊煎拰瀛樻斁鐨勪笉涓�瀹氱浉绛夛紝鎵�浠ユ棤娉曟帓搴?/li>
  • 涓嶈兘閬垮厤鍏ㄨ〃鎵弿锛屽彧鏄敱浜庡湪memory琛ㄩ噷鏀寔闈炲敮涓�鍊糷ash绱㈠紩锛屽嵆涓嶅悓鐨勭储寮曢敭锛屽彲鑳藉瓨鍦ㄧ浉鍚宧ash鍊?/li>
  • 鍥犱负鍝堝笇琛ㄦ槸涓�绉嶆牴鎹叧閿瓧鐩存帴璁块棶鍐呭瓨瀛樺偍浣嶇疆鐨勬暟鎹粨鏋?锛屾墍浠ュ埄鐢ㄥ叾鍘熺悊鐨刪ash 绱㈠紩锛屼篃灏遍渶瑕佸皢鎵�鏈夋暟鎹枃浠舵坊鍔犲埌鍐呭瓨锛岃繖灏卞緢鑰楀唴瀛?/li>
  • 濡傛灉鎵�鏈夌殑鏌ヨ閮芥槸绛夊�兼煡璇紝閭ｄ箞hash纭疄蹇紝浣嗗疄闄呬笂鑼冨洿鏌ユ壘鏁版嵁鏇村
  • 鏅鸿兘澶勭悊閿�煎緱鍏ㄥ�煎尮閰?/li>
  • 鏌ヨHash鍑芥暟鍐冲畾鐫�绱㈠紩閿殑澶у皬

瑕佷娇InnoDB鎴朚yISAM鏀寔鍝堝笇绱㈠紩锛屽彲浠ラ�氳繃浼搱甯岀储寮曟潵瀹炵幇锛屽彨鑷�傚簲鍝堝笇绱㈠紩銆?/p>

鍙�氳繃澧炲姞涓�涓瓧娈碉紝瀛樺偍hash鍊硷紝灏唄ash鍊煎缓绔嬬储寮曪紝鍦ㄦ彃鍏ュ拰鏇存柊鐨勬椂鍊欙紝寤虹珛瑙﹀彂鍣紝鑷姩娣诲姞璁＄畻鍚庣殑hash鍒拌〃閲屻�?/p>

鍝堝笇琛ㄨ繖绉嶇粨鏋勯�傜敤浜庡彧鏈夌瓑鍊兼煡璇㈢殑鍦烘櫙锛屾瘮濡侻emcached銆?/p>

3 妗堜緥搴旂敤

鍋囧鏈変竴涓潪甯搁潪甯稿ぇ鐨勮〃锛屾瘮濡傜敤鎴风櫥褰曟椂闇�瑕侀�氳繃email妫�绱㈠嚭鐢ㄦ埛锛屽鏋滅洿鎺ュ湪email鍒楀缓绱㈠紩锛岄櫎浜嗙储寮曞尯闂村尮閰嶏紝杩樿杩涜瀛楃涓插尮閰嶆瘮瀵癸紝email鐭繕濂斤紝濡傛灉闀跨殑璇濊繖涓煡璇唬浠峰氨姣旇緝澶с�?鑻ユ鏃讹紝鍦╡mail寤虹珛鍝堝笇绱㈠紩锛屾煡璇互int鏌ヨ锛屾�ц兘灏辨瘮瀛楃涓叉瘮瀵规煡璇㈠揩澶氫簡銆?/p>

Hash 绠楁硶

寤虹珛鍝堝笇绱㈠紩锛岄鍏堝氨瑕侀�夊畾鍝堝笇绠楁硶锛屻�婇珮鎬ц兘MySQL銆嬭鍒扮殑CRC32绠楁硶銆?/p>

INSERT UPDATE SELECT 鎿嶄綔

鍦ㄨ〃涓坊鍔爃ash鍊肩殑瀛楁锛?/p> 

ALTER TABLE `User` ADD COLUMN email_hash int unsigned NOT NULL DEFAULT 0;

鎺ヤ笅鏉ュ氨鏄湪UPDATE鍜孖NSERT鏃讹紝鑷姩鏇存柊 email_hash 瀛楁锛岄�氳繃瑙﹀彂鍣ㄥ疄鐜帮細

DELIMITER |
CREATE TRIGGER user_hash_insert BEFORE INSERT ON `User` FOR EACH ROW BEGIN
SET NEW.email_hash=crc32(NEW.email);
END;
|
CREATE TRIGGER user_hash_update BEFORE UPDATE ON `User` FOR EACH ROW BEGIN
SET NEW.email_hash=crc32(NEW.email);
END;
|
DELIMITER ;

杩欐牱SELECT璇锋眰灏变細鍙樻垚锛?/p> 

SELECT `email`, `email_hash` FROM `User` WHERE 
	email_hash = CRC32(鈥渪xoo@gmail.com鈥? 
			AND `email`= 鈥渪xoo@gmail.com鈥?

+----------------------------+------------+
| email                    |  email_hash  |
+----------------------------+------------+
| xxoo@gmail.com | 2765311122 |
+----------------------------+------------+

AND email = "xxoo@gmail.com" 鏄负浜嗛槻姝㈠搱甯岀鎾炴椂鏁版嵁涓嶅噯纭�?/p>

鍒版杩欑瘒鍏充簬MySQL闈㈣瘯棰樿瑙ｄ箣濡備綍璁剧疆Hash绱㈠紩鐨勬枃绔犲氨浠嬬粛鍒拌繖浜?鏇村鐩稿叧MySQL 璁剧疆Hash绱㈠紩鍐呭璇锋悳绱㈣剼鏈箣瀹朵互鍓嶇殑鏂囩珷鎴栫户缁祻瑙堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у浠ュ悗澶氬鏀寔鑴氭湰涔嬪锛?/p>