5.绱㈠紩瀹為獙
5.1绱㈠紩鐨勫畾涔?/h3> 鈥?绱㈠紩鏄竴绉嶇壒娈婄殑鏁版嵁搴撶粨鏋勶紝鐢辨暟鎹〃涓殑涓€鍒楁垨澶氬垪缁勫悎鑰屾垚锛屽彲浠ョ敤鏉ュ揩閫熸煡璇㈡暟鎹〃涓湁鏌愪竴鐗瑰畾鍊肩殑璁闆綍銆?
鈥?閫氳繃绱㈠紩锛屾煡璇㈡暟鎹椂涓嶇敤璇誨畬璁闆綍鐨勬墍鏈変俊鎭紝鑰屽彧鏄煡璇㈢儲寮曞垪銆傚惁鍒欙紝鏁版嵁搴撶郴缁熷皢璇誨彇姣忔潯璁闆綍鐨勬墍鏈変俊鎭繘琛屽尮閰嶃€?
鈥?鍙互鎶婄儲寮曟瘮浣滄柊鍗庡瓧鍏哥殑闊沖簭琛ㄣ€備緥濡傦紝瑕佹煡鈥滃簱鈥濆瓧锛屽鏋滀笉浣跨敤闊沖簭锛屽氨闇€瑕佷粠瀛楀吀鐨?400 椤典腑閫愰〉鏉ユ壘銆備絾鏄紝濡傛灉鎻愬彇鎷奸煶鍑烘潵锛屾瀯鎴愰煶搴忚〃锛屽氨鍙渶瑕佷粠 10 澶氶〉鐨勯煶搴忚〃涓洿鎺ユ煡鎵俱€傝繖鏍峰氨鍙互澶уぇ鑺傜渷鏃堕棿銆?鍥犳锛屼嬌鐢ㄧ儲寮曞彲浠ュ緢澶х▼搴︿笂鎻愰珮鏁版嵁搴撶殑鏌ヨ閫熷害锛岃繕鏈夋晥鐨勬彁楂樹簡鏁版嵁搴撶郴缁熺殑鎬ц兘銆?
5.2浣跨敤绱㈠紩鐨勪紭鐐?/h3>
鈶犻€氳繃鍒涘緩鍞竴绱㈠紩鍙互淇濊瘉鏁版嵁搴撹〃涓瘡涓€琛屾暟鎹殑鍞竴鎬с€?
鈶″彲浠ョ粰鎵€鏈夌殑 MySQL 鍒楃被鍨嬭缃儲寮曘€?
鈶㈠彲浠ュぇ澶у姞蹇暟鎹殑鏌ヨ閫熷害锛岃繖鏄嬌鐢ㄧ儲寮曟渶涓昏鐨勫師鍥犮€?
鈶ｅ湪瀹炵幇鏁版嵁鐨勫弬鑰冨畬鏁存€ф柟闈㈠彲浠ュ姞閫熻〃涓庤〃涔嬮棿鐨勮繛鎺ャ€?
鈶ゅ湪浣跨敤鍒嗙粍鍜屾帓搴忓瓙鍙ヨ繘琛屾暟鎹煡璇㈡椂涔熷彲浠ユ樉钁楀噺灏戞煡璇腑鍒嗙粍鍜屾帓搴忕殑鏃堕棿
5.3浣跨敤绱㈠紩鐨勭己鐐?/h3>
鈶犲垱寤哄拰缁存姢绱㈠紩缁勮鑰楄垂鏃堕棿锛屽苟涓旈殢鐫€鏁版嵁閲忕殑澧炲姞鎵€鑰楄垂鐨勬椂闂翠篃浼氬鍔犮€?
鈶＄儲寮曢渶瑕佸崰纾佺洏绌洪棿锛岄櫎浜嗘暟鎹〃鍗犳暟鎹┖闂翠互澶栵紝姣忎竴涓儲寮曡繕瑕佸崰涓€瀹氱殑鐗╃悊绌洪棿銆傚鏋滄湁澶ч噺鐨勭儲寮曪紝绱㈠紩鏂囦歡鍙兘姣旀暟鎹枃浠舵洿蹇揪鍒版渶澶ф枃浠跺昂瀵搞€?
鈶㈠綋瀵矽〃涓殑鏁版嵁杩涜澧炲姞銆佸垹闄ゅ拰淇敼鐨勬椂鍊欙紝绱㈠紩涔熻鍔ㄦ€佺淮鎶わ紝杩欐牱灏遍檷浣庝簡鏁版嵁鐨勭淮鎶ら€熷害銆?
5.4 MySQL 涓紝閫氬父鏈変互涓嬩袱绉嶆柟寮忚闂暟鎹簱琛ㄧ殑琛屾暟鎹?/h3> 5.4.1 椤哄簭璁塊棶
鈥?椤哄簭璁塊棶鏄湪琛ㄤ腑瀹炶鍏ㄨ〃鎵弿锛屼粠澶村埌灏鵑€愯閬嶅巻锛岀洿鍒闆湪鏃犲簭鐨勮鏁版嵁涓壘鍒扮鍚堟潯浠剁殑鐩爣鏁版嵁銆傞『搴忚闂疄鐜版瘮杈冪畝鍗曪紝浣嗘槸褰撹〃涓湁澶ч噺鏁版嵁鐨勬椂鍊欙紝鏁堢巼闈炲父浣庝笅銆備緥濡傦紝鍦ㄥ嚑鍗冧竾鏉℃暟鎹腑鏌ユ壘灏戦噺鐨勬暟鎹椂锛屼嬌鐢ㄩ『搴忚闂柟寮忓皢浼氶亶鍘嗘墍鏈夌殑鏁版嵁锛岃姳璐瑰ぇ閲忕殑鏃堕棿锛屾樉鐒朵細褰卞搷鏁版嵁搴撶殑澶勭悊鎬ц兘銆? 5.4.2绱㈠紩璁塊棶
鈥?绱㈠紩璁塊棶鏄€氳繃閬嶅巻绱㈠紩鏉ョ洿鎺ヨ闂〃涓褰曡鐨勬柟寮忋€備嬌鐢ㄨ繖绉嶆柟寮忕殑鍓嶆彁鏄琛ㄥ緩绔嬩竴涓儲寮曪紝鍦ㄥ垪涓婂垱寤轟簡绱㈠紩涔嬪悗锛屾煡鎵炬暟鎹椂鍙互鐩存帴鏍規嵁璇ュ垪涓婄殑绱㈠紩鎵懼埌瀵瑰簲璁闆綍琛岀殑浣嶇疆锛屼粠鑰屽揩鎹峰湴鏌ユ壘鍒版暟鎹€傜儲寮曞瓨鍌ㄤ簡鎸囧畾鍒楁暟鎹€肩殑鎸囬拡锛屾牴鎹寚瀹氱殑鎺掑簭椤哄簭瀵矽繖浜涙寚閽堟帓搴忋€?
鈥?渚嬪锛屽湪瀛︾敓鍩烘湰淇℃伅琛?tb_students 涓紝濡傛灉鍩轟簬 student_id 寤虹珛浜嗙儲寮曪紝绯葷粺灏卞緩绔嬩簡涓€寮犵儲寮曞垪鍒闆疄闄呰褰曠殑鏄犲皠琛ㄣ€傚綋鐢ㄦ埛闇€瑕佹煡鎵?student_id 涓?12022 鐨勬暟鎹殑鏃跺€欙紝绯葷粺鍏堝湪 student_id 绱㈠紩涓婃壘鍒拌璁闆綍锛岀劧鍚庨€氳繃鏄犲皠琛ㄧ洿鎺ユ壘鍒版暟鎹锛屽苟涓旇繑鍥炶琛屾暟鎹€傚洜涓烘壂鎻忕儲寮曠殑閫熷害涓€鑸繙杩滃ぇ浜庢壂鎻忓疄闄呮暟鎹鐨勯€熷害锛屾墍浠ラ噰鐢ㄧ儲寮曠殑鏂瑰紡鍙互澶уぇ鎻愰珮鏁版嵁搴撶殑宸ヤ綔鏁堢巼銆?
鈥?绠€鑰岃█涔嬶紝涓嶄嬌鐢ㄧ儲寮曪紝MySQL 灏卞繀椤諱粠绗竴鏉¤褰曞紑濮嬭瀹屾暣涓〃锛岀洿鍒版壘鍑虹浉鍏崇殑琛屻€傝〃瓒婂ぇ锛屾煡璇㈡暟鎹墍鑺辮垂鐨勬椂闂村氨瓒婂銆傚鏋滆〃涓煡璇㈢殑鍒楁湁涓€涓儲寮曪紝MySQL 灏辮兘蹇€熷埌杈句竴涓綅缃幓鎼滅儲鏁版嵁鏂囦歡锛岃€屼笉蹇呮煡鐪嬫墍鏈夋暟鎹紝杩欐牱灏嗕細鑺傜渷寰堝ぇ涓€閮ㄥ垎鏃堕棿銆?
5.5绱㈠紩绫誨瀷
5.5.1B-TREE绱㈠紩
鈶犳櫘閫氱儲寮?INDEX)锛氭渶鍩烘湰绱㈠紩锛屾病鏈夊敮涓€鎬ч檺鍒? 鈶″敮涓€鎬х儲寮?UNIQUE)锛氱儲寮曞垪鐨勫€間笉鑳介噸澶? 鈶富閿?PRIMARY KEY)锛氬垱寤鴻〃鐨勬椂鍊欐寚瀹氾紝姣忎釜琛ㄥ彧鑳芥湁涓€涓富閿紝涓婚敭鐨勫€間笉鍙噸澶嶏紝涔熶笉鍙負绌猴紙NULL锛?5.5.2HASH绱㈠紩锛堝綋琛ㄧ被鍨嬩負MEMORY鎴朒EAP鏃跺彲鐢級
鍒涘緩 HASH绱㈠紩
鍒涘緩涓€涓寘鍚?(employeeID, name, education) 绛夊瓧娈電殑涓存椂鍛樺伐琛?tmpEmployee)锛屽苟鍦ㄨ琛ㄧ殑鍛樺伐缂栧彿瀛楁涓婂垱寤轟竴涓狧ASH绱㈠紩
棣栧厛鍒涘緩琛細
create table if not exists tmpEmployee(employeeID varchar(6) primary key
not null,name varchar(10) not null,education varhcar(10));
create index in_hash using hash on tmpEmployee(employeeId);
5.5.3R-TREE绱㈠紩
鈥?MySQL鏀寔瀵圭┖闂存暟鎹簱杩涜R-TREE绱㈠紩 5.6MySQL 鎻愪緵浜嗕笁绉嶅垱寤虹儲寮曠殑鏂規硶锛?/h3> 5.6.1 浣跨敤 CREATE INDEX 璇彞
鈥?鍙互浣跨敤涓撻棬鐢ㄤ簬鍒涘緩绱㈠紩鐨?CREATE INDEX 璇彞鍦ㄤ竴涓凡鏈夌殑琛ㄤ笂鍒涘緩绱㈠紩锛屼絾璇ヨ鍙ヤ笉鑳藉垱寤轟富閿€?
璇硶鏍煎紡锛?
璇硶璇存槑濡備笅锛?
渚嬪锛屽湪employee琛ㄧ殑name鍒楀緩绔嬩竴涓儲寮昳n_employee鈶?lt;绱㈠紩鍚?gt;`锛氭寚瀹氱儲寮曞悕銆備竴涓〃鍙互鍒涘緩澶氫釜绱㈠紩锛屼絾姣忎釜绱㈠紩鍦ㄨ琛ㄤ腑鐨勫悕绉版槸鍞竴鐨勩€?
鈶?lt;琛ㄥ悕>`锛氭寚瀹氳鍒涘緩绱㈠紩鐨勮〃鍚嶃€?
鈶?lt;鍒楀悕>`锛氭寚瀹氳鍒涘緩绱㈠紩鐨勫垪鍚嶃€傞€氬父鍙互鑰冭檻灏嗘煡璇㈣鍙ヤ腑鍦?JOIN 瀛愬彞鍜?WHERE 瀛愬彞閲岀粡甯稿嚭鐜扮殑鍒椾綔涓虹儲寮曞垪銆?
鈶?lt;闀垮害>`锛氬彲閫夐」銆傛寚瀹氫嬌鐢ㄥ垪鍓嶇殑 length 涓瓧绗︽潵鍒涘緩绱㈠紩銆備嬌鐢ㄥ垪鐨勪竴閮ㄥ垎鍒涘緩绱㈠紩鏈夊埄浜庡噺灏忕儲寮曟枃浠剁殑澶у皬锛岃妭鐪佺儲寮曞垪鎵€鍗犵殑绌洪棿銆傚湪鏌愪簺鎯呭喌涓嬶紝鍙兘瀵瑰垪鐨勫墠缂€杩涜绱㈠紩銆傜儲寮曞垪鐨勯暱搴︽湁涓€涓渶澶т笂闄?255 涓瓧鑺傦紙MyISAM 鍜?InnoDB 琛ㄧ殑鏈€澶т笂闄愪負 1000 涓瓧鑺傦級锛屽鏋滅儲寮曞垪鐨勯暱搴﹁秴杩囦簡杩欎釜涓婇檺锛屽氨鍙兘鐢ㄥ垪鐨勫墠缂€杩涜绱㈠紩銆傚彟澶栵紝BLOB 鎴?TEXT 绫誨瀷鐨勫垪涔熷繀椤諱嬌鐢ㄥ墠缂€绱㈠紩銆?
鈶SC|DESC
锛氬彲閫夐」銆?/code>ASC
鎸囧畾绱㈠紩鎸夌収鍗囧簭鏉ユ帓鍒楋紝
DESC
鎸囧畾绱㈠紩鎸夌収闄嶅簭鏉ユ帓鍒楋紝榛樿涓?/code>ASC`銆?
CREATE INDEX in_employee ON employee(name=);
CREATE INDEX in_employee ON student( name asc); #鍗囧簭绱㈠紩
娉細鍙互鍦ㄥ垱寤鴻〃鐨勫悓鏃跺垱寤虹儲寮?
鍙互鐢?
SHOW INDEX FROM 琛ㄥ悕
show index from employee;
5.6.2浣跨敤 CREATE TABLE 璇彞
绱㈠紩涔熷彲浠ュ湪鍒涘緩琛紙CREATE TABLE锛夌殑鍚屾椂鍒涘緩銆傚湪 CREATE TABLE 璇彞涓坊鍔犱互涓嬭鍙ャ€傝娉曟牸寮忥細
鍦?CREATE TABLE 璇彞涓坊鍔犳璇彞锛岃〃绀哄湪鍒涘緩鏂拌〃鐨勫悓鏃跺垱寤鴻琛ㄧ殑涓婚敭銆?
璇硶鏍煎紡锛?
KEY | INDEX [<绱㈠紩鍚?gt;] [<绱㈠紩绫誨瀷>] (<鍒楀悕>,鈥?
鍦?CREATE TABLE 璇彞涓坊鍔犳璇彞锛岃〃绀哄湪鍒涘緩鏂拌〃鐨勫悓鏃跺垱寤鴻琛ㄧ殑绱㈠紩銆?
璇硶鏍煎紡锛?
UNIQUE [ INDEX | KEY] [<绱㈠紩鍚?gt;] [<绱㈠紩绫誨瀷>] (<鍒楀悕>,鈥?
鍦?CREATE TABLE 璇彞涓坊鍔犳璇彞锛岃〃绀哄湪鍒涘緩鏂拌〃鐨勫悓鏃跺垱寤鴻琛ㄧ殑鍞竴鎬х儲寮曘€?
璇硶鏍煎紡锛?
FOREIGN KEY <绱㈠紩鍚?gt; <鍒楀悕>
鍦?CREATE TABLE 璇彞涓坊鍔犳璇彞锛岃〃绀哄湪鍒涘緩鏂拌〃鐨勫悓鏃跺垱寤鴻琛ㄧ殑澶栭敭銆?
鈥?鍦ㄤ嬌鐢?CREATE TABLE 璇彞瀹氫箟鍒楅€夐」鐨勬椂鍊欙紝鍙互閫氳繃鐩存帴鍦ㄦ煇涓垪瀹氫箟鍚庨潰娣誨姞 PRIMARY KEY 鐨勬柟寮忓垱寤轟富閿€傝€屽綋涓婚敭鏄敱澶氫釜鍒楃粍鎴愮殑澶氬垪绱㈠紩鏃訛紝鍒欎笉鑳戒嬌鐢ㄨ繖绉嶆柟娉曪紝鍙兘鐢ㄥ湪璇彞鐨勬渶鍚庡姞涓婁竴涓?PRIMARY KRY(<鍒楀悕>锛屸€? 瀛愬彞鐨勬柟寮忔潵瀹炵幇銆?
5.6.2.1鍞竴绱㈠紩锛坲nique锛?
create unique index in_name on employee (name);
5.6.2.2鍒涘緩澶嶅悎涓婚敭
create index in_employeeId_name on employee(employeID,name);
5.6.2.3鍒涘緩琛ㄧ殑鏃跺€欏垱寤虹儲寮?/h5>
鍒涘緩涓巇epartment琛ㄧ浉鍚岀粨鏋勭殑琛╠epartment1锛屽苟灏哾epartName璁句負涓婚敭锛宒epartmentID涓婂緩绔嬩竴涓儲寮?銆?
棣栧厛寤虹珛涓€涓〃department1琛細
CREATE TABLE `department1` (
`departmentID` char(3 ) not null,
`departName` char(20) NOT NULL,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`departName`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk ;
create index in_departmentId on department1(departmentID);
5.6.3 浣跨敤 ALTER TABLE 璇彞
CREATE INDEX 璇彞鍙互鍦ㄤ竴涓凡鏈夌殑琛ㄤ笂鍒涘緩绱㈠紩锛孉LTER TABLE 璇彞涔熷彲浠ュ湪涓€涓凡鏈夌殑琛ㄤ笂鍒涘緩绱㈠紩銆傚湪浣跨敤 ALTER TABLE 璇彞淇敼琛ㄧ殑鍚屾椂锛屽彲浠ュ悜宸叉湁鐨勮〃娣誨姞绱㈠紩銆傚叿浣撶殑鍋氭硶鏄湪 ALTER TABLE 璇彞涓坊鍔犱互涓嬭娉曟垚鍒嗙殑鏌愪竴椤規垨鍑犻」銆?
璇硶鏍煎紡锛?
ADD INDEX [<绱㈠紩鍚?gt;] [<绱㈠紩绫誨瀷>] (<鍒楀悕>,鈥?
鍦?ALTER TABLE 璇彞涓坊鍔犳璇硶鎴愬垎锛岃〃绀哄湪淇敼琛ㄧ殑鍚屾椂涓鴻琛ㄦ坊鍔犵儲寮曘€?
璇硶鏍煎紡锛?
ADD PRIMARY KEY [<绱㈠紩绫誨瀷>] (<鍒楀悕>,鈥?
鍦?ALTER TABLE 璇彞涓坊鍔犳璇硶鎴愬垎锛岃〃绀哄湪淇敼琛ㄧ殑鍚屾椂涓鴻琛ㄦ坊鍔犱富閿€?
璇硶鏍煎紡锛?
ADD UNIQUE [ INDEX | KEY] [<绱㈠紩鍚?gt;] [<绱㈠紩绫誨瀷>] (<鍒楀悕>,鈥?
鍦?ALTER TABLE 璇彞涓坊鍔犳璇硶鎴愬垎锛岃〃绀哄湪淇敼琛ㄧ殑鍚屾椂涓鴻琛ㄦ坊鍔犲敮涓€鎬х儲寮曘€?
璇硶鏍煎紡锛?
ADD FOREIGN KEY [<绱㈠紩鍚?gt;] (<鍒楀悕>,鈥?
5.7鏌ョ湅绱㈠紩
SHOW INDEX FROM <琛ㄥ悕> [ FROM <鏁版嵁搴撳悕>]
璇硶璇存槑濡備笅锛?
鈶?lt;琛ㄥ悕>锛氭寚瀹氶渶瑕佹煡鐪嬬儲寮曠殑鏁版嵁琛ㄥ悕銆?
鈶?lt;鏁版嵁搴撳悕>锛氭寚瀹氶渶瑕佹煡鐪嬬儲寮曠殑鏁版嵁琛ㄦ墍鍦ㄧ殑鏁版嵁搴擄紝鍙渷鐣ャ€傛瘮濡傦紝SHOW INDEX FROM student FROM test; 璇彞琛ㄧず鏌ョ湅 test 鏁版嵁搴撲腑 student 鏁版嵁琛ㄧ殑绱㈠紩銆?
5.8鍒犻櫎绱㈠紩
褰撲笉鍐嶉渶瑕佺儲寮曟椂锛屽彲浠ヤ嬌鐢?DROP INDEX 璇彞鎴?ALTER TABLE 璇彞鏉ュ绱㈠紩杩涜鍒犻櫎銆?5.8.1 浣跨敤 DROP INDEX 璇彞
璇硶鏍煎紡锛?DROP INDEX <绱㈠紩鍚?gt; ON <琛ㄥ悕>
鈶?lt;绱㈠紩鍚?gt;`锛氳鍒犻櫎鐨勭儲寮曞悕銆?
鈶?lt;琛ㄥ悕>`锛氭寚瀹氳绱㈠紩鎵€鍦ㄧ殑琛ㄥ悕銆?
5.8.2浣跨敤 ALTER TABLE 璇彞
鈥?鏍規嵁 ALTER TABLE 璇彞鐨勮娉曞彲鐭ワ紝璇ヨ鍙ヤ篃鍙互鐢ㄤ簬鍒犻櫎绱㈠紩銆傚叿浣撲嬌鐢ㄦ柟娉曟槸灏?ALTER TABLE 璇彞鐨勮娉曚腑閮ㄥ垎鎸囧畾涓轟互涓嬪瓙鍙ヤ腑鐨勬煇涓€椤廣€?鈶燚ROP PRIMARY KEY锛氳〃绀哄垹闄よ〃涓殑涓婚敭銆備竴涓〃鍙湁涓€涓富閿紝涓婚敭涔熸槸涓€涓儲寮曘€?
鈶ROP INDEX index_name锛氳〃绀哄垹闄ゅ悕绉頒負 index_name 鐨勭儲寮曘€?
鈶ROP FOREIGN KEY fk_symbol锛氳〃绀哄垹闄ゅ閿€?
娉ㄦ剰锛氬鏋滃垹闄ょ殑鍒楁槸绱㈠紩鐨勭粍鎴愰儴鍒嗭紝閭ｄ箞鍦ㄥ垹闄よ鍒楁椂锛屼篃浼氬皢璇ュ垪浠庣儲寮曚腑鍒犻櫎锛涘鏋滅粍鎴愮儲寮曠殑鎵€鏈夊垪閮借鍒犻櫎锛岄偅涔堟暣涓儲寮曞皢琚垹闄ゃ€?
浣跨敤DROP INDEX璇彞鍒犻櫎绱㈠紩锛?
drop index in_employee on employee;
show create table employee;
娉細涔熷彲浠ョ敤 ALTER TABLE 鏉ュ垹闄ょ儲寮曪細
alter table employee drop index in_phone ;