鏈夋病鏈夐亣鍒拌繃杩欑鎯呭喌锛熸槑鏄庣粰鏁版嵁搴撳姞浜嗙储寮曪紝鏌ヨ閫熷害杩樻槸鍍忚湕鐗涚埇锛熶竴鍒颁笟鍔￠珮宄版湡锛屾暟鎹簱灏辩柉鐙傛姤璀︼紵浠婂ぉ鍜变滑灏辨潵鎺板紑鎻夌浜嗚亰鑱婏紝鎬庝箞鍍忚€佸徃鏈轰竴鏍烽伩寮€绱㈠紩鐨勫潙锛岀敤姝ｇ‘濮垮娍缁欐暟鎹簱"瑁呭姞閫熷櫒"銆?/p>

涓€銆侀€夊鎴樺満锛氬摢浜涘瓧娈靛€煎緱鍔犵储寮曪紵

鏂版墜鏈€瀹规槗鐘殑閿欏氨鏄€?strong>鈥嬭瀛楁灏卞姞绱㈠紩鈥?/strong>鈥嬶紝缁撴灉绱㈠紩姣旀暟鎹繕鍗犲湴鏂广€傝浣忚繖涓変釜榛勯噾鍘熷垯锛?/p>

1. 鈥?strong>鈥嬮珮棰戞煡璇㈠瓧娈碘€?/strong>鈥嬶細姣斿鐢ㄦ埛琛ㄧ殑鎵嬫満鍙枫€佽鍗曡〃鐨勮鍗曞彿锛岃繖浜涙瘡澶╄鏌?00閬嶇殑瀛楁涓嶅姞绱㈠紩绠€鐩村ぉ鐞嗛毦瀹?/li>
2. 鈥?strong>鈥嬮珮鍖哄垎搴﹀瓧娈碘€?/strong>鈥嬶細鍍忔€у埆瀛楁锛堝彧鏈夌敺/濂筹級鍔犵储寮曠函灞炴氮璐癸紝浣嗙敤鎴稩D杩欑鍞竴鍊煎瓧娈靛繀椤诲姞
3. 鈥?strong>鈥嬭繛鎺ュ瓧娈靛繀鍔犫€?/strong>鈥嬶細鍋氳〃鍏宠仈鏃剁敤鍒扮殑澶栭敭瀛楁锛屼笉鍔犵储寮曞氨鍍忓紑杞︿笉韪╂补闂?/li>

涓句釜鏍楀瓙馃尠锛氱敤鎴疯〃鏈?00涓囨潯鏁版嵁锛岀粰"鎵嬫満鍙?鍔犵储寮曞悗锛屾煡璇㈤€熷害鐩存帴浠?绉掗檷鍒?.01绉掋€備絾瑕佹槸缁?鎬у埆"瀛楁鍔犵储寮曪紵鏁堟灉鍙兘杩樹笉濡備笉鍔犮€?/p>

浜屻€佺粍鍚堢储寮曠殑姝ｇ‘鎵撳紑鏂瑰紡

缁勫悎绱㈠紩鐢ㄥ緱濂斤紝鏌ヨ蹇緱椋炶捣锛涚敤涓嶅ソ锛屽氨鏄粰鑷繁鎸栧潙銆傝浣忚繖涓彛璇€锛氣€?strong>鈥嬪乏浼樺厛锛屽皯鑰岀簿鈥?/strong>鈥嬨€?/p>

姣斿鏈変釜璁㈠崟鏌ヨ鍦烘櫙锛屽悓鏃剁敤鍒颁簡鐢ㄦ埛ID銆佷笅鍗曟椂闂村拰璁㈠崟鐘舵€併€傛纭殑缁勫悎绱㈠紩搴旇鏄細

sql澶嶅埗
CREATE INDEX idx_user_time_status ON orders(user_id, order_time, status)

杩欐椂鍊欙細

• 鏌ユ煇涓敤鎴锋渶杩?涓湀鐨勮鍗?鈫?绱㈠紩鐢熸晥 鉁旓笍
• 鏌ユ槰澶╂墍鏈夊凡瀹屾垚璁㈠崟 鈫?绱㈠紩澶辨晥 鉂岋紙鍥犱负娌″寘鍚玼ser_id锛?/li>

鈥?strong>鈥嬭娉暀璁€?/strong>鈥嬶細鏈変釜鐢靛晢绯荤粺鍘熸湰鏌ヨ瑕?绉掞紝璋冩暣缁勫悎绱㈠紩椤哄簭鍚庣洿鎺ラ檷鍒?.2绉掞紝TPS鎻愬崌浜?0鍊嶃€?/p>

涓夈€佷笁鏄熺储寮曪細鏁版嵁搴撳姞閫熺殑缁堟瀬濂ヤ箟

鑰佸徃鏈洪兘鍦ㄧ敤鐨勨€?strong>鈥嬩笁鏄熺储寮曞師鍒欌€?/strong>鈥嬶紝璁╀綘鐨勬煡璇㈠揩涓婂姞蹇細

1. 鈥?strong>鈥嬬涓€鏄熲€?/strong>鈥嬶細绱㈠紩琛岀浉閭诲瓨鏀撅紙鍑忓皯纾佺洏IO锛?/li>
2. 鈥?strong>鈥嬬浜屾槦鈥?/strong>鈥嬶細鎺掑簭瀛楁椤哄簭鍖归厤锛堥伩鍏嶄复鏃舵帓搴忥級
3. 鈥?strong>鈥嬬涓夋槦鈥?/strong>鈥嬶細瑕嗙洊鎵€鏈夋煡璇㈠瓧娈碉紙鏉滅粷鍥炶〃鏌ヨ锛?/li>

涓句釜瀹炴垬妗堜緥锛氱敤鎴蜂腑蹇冭鏌?鍖椾含鍦板尯30宀佷互涓嬬敺鐢ㄦ埛锛屾寜娉ㄥ唽鏃堕棿鍊掑簭鍙栧墠100鍚?

sql澶嶅埗
-- 瀹岀編涓夋槦绱㈠紩
CREATE INDEX idx_city_gender_age ON users(city, gender, age, register_time)

杩欎釜绱㈠紩鐩存帴鎶婃煡璇㈡椂闂翠粠8绉掑共鍒?.03绉掞紝纾佺洏IO鍑忓皯90%銆?/p>

鍥涖€佷簲澶т綔姝绘搷浣滐細杩欎簺鍧戝崈涓囧埆韪?/h3>

1. 鈥?strong>鈥嬩贡鐢ㄥ嚱鏁板鑷寸储寮曞け鏁堚€?/strong>鈥嬶細WHERE YEAR(create_time)=2023 鈫?绱㈠紩鎵戣
2. 鈥?strong>鈥嬮殣寮忕被鍨嬭浆鎹⑩€?/strong>鈥嬶細瀛楃涓插瓧娈电敤鏁板瓧鏌ヨ 鈫?绱㈠紩缃㈠伐
3. 鈥?strong>鈥嬪墠瀵兼ā绯婃煡璇⑩€?/strong>鈥嬶細LIKE '%寮犱笁' 鈫?鍏ㄨ〃鎵弿璀﹀憡
4. 鈥?strong>鈥婳R鏉′欢涔辩敤鈥?/strong>鈥嬶細涓よ竟瀛楁娌＄储寮?鈫?鐩存帴鎽嗙儌
5. 鈥?strong>鈥嬭繃搴︾储寮曗€?/strong>鈥嬶細姣忓姞涓€涓储寮曪紝鍐欏叆閫熷害灏辨參涓€鎴?/li>

鏇剧粡鏈変釜绯荤粺鍔犱簡20澶氫釜绱㈠紩锛岀粨鏋滄瘡绉掑彧鑳藉啓鍏?0鏉℃暟鎹紝鍒犳帀鍐椾綑绱㈠紩鍚庣洿鎺ラ鍒?000鏉?绉掋€?/p>

---

浜斻€佺淮鎶ょ绫嶏細璁╃储寮曟案钁嗛潚鏄?/h3>

1. 鈥?strong>鈥嬪畾鏈熶綋妫€鈥?/strong>鈥嬶細姣忔湀鐢?code>EXPLAIN鍒嗘瀽鎱㈡煡璇?/li>
2. 鈥?strong>鈥嬬鐗囨暣鐞嗏€?/strong>鈥嬶細瀵逛慨鏀归绻佺殑琛ㄥ仛ALTER INDEX REBUILD
3. 鈥?strong>鈥嬬粺璁′俊鎭洿鏂扳€?/strong>鈥嬶細澶ф暟鎹噺鍙樻洿鍚庣珛鍗宠窇ANALYZE TABLE
4. 鈥?strong>鈥嬬洃鎺т娇鐢ㄧ巼鈥?/strong>鈥嬶細涓変釜鏈堟病琚敤鍒扮殑绱㈠紩鐩存帴骞叉帀

鏈変釜DBA鏈嬪弸闈犺繖濂楁柟娉曪紝鎶婃暟鎹簱鎬ц兘鎻愬崌浜?00%锛屽勾缁堝鐩存帴缈诲€嶃€?/p>

---

涓汉瑙傜偣鏃堕棿

鍋氫簡鍗佸勾鏁版嵁搴撲紭鍖栵紝鍙戠幇鏈€鍙€曠殑涓嶆槸鎶€鏈棶棰橈紝鑰屾槸浜虹殑鎯€ф€濈淮銆傚緢澶氫汉鎶婄储寮曞綋"閾跺脊"锛屽嵈蹇樹簡鈥?strong>鈥嬫病鏈夋渶濂界殑绱㈠紩锛屽彧鏈夋渶閫傚悎涓氬姟鐨勭储寮曗€?/strong>鈥嬨€備笅娆¤璁＄储寮曞墠锛屽厛闂嚜宸变笁涓棶棰橈細

1. 杩欎釜鏌ヨ姣忓ぉ璺戝灏戞锛?/li>
2. 鏁版嵁閲忓闀胯秼鍔垮浣曪紵
3. 鍐欐搷浣滃拰璇绘搷浣滅殑姣斾緥鏄灏戯紵

璁颁綇锛岀储寮曚紭鍖栨槸闂ㄥ钩琛¤壓鏈紝鏃㈣璁╂煡璇㈤璧锋潵锛屽張涓嶈兘鎷栫疮鍐欏叆閫熷害銆傚氨鍍忓仛鑿滐紝鐏€欏埌浜嗚嚜鐒堕锛屽崈涓囧埆瀛︽煇浜涗汉锛岀储寮曞姞寰楁瘮璋冩枡杩樺锛屾渶鍚庣叜鍑轰竴閿呬贡鐐栥€?/p>