浣犵殑SQL鏌ヨ鏄笉鏄€诲儚铚楃墰鐖紵

"杩欑牬绯荤粺鏌ヤ釜鏁版嵁瑕?绉掞紵鐢ㄦ埛閮借窇鍏変簡锛?鈥斺€斾笂鍛ㄥ垰鍚▼搴忓憳鑰佸紶杩欎箞鍚愭Ы銆傚叾瀹炲晩锛?0%鐨勬暟鎹簱鎬ц兘闂閮藉嚭鍦⊿QL璇彞涓婏紝浣嗗緢澶氫汉鍙細鎶辨€ㄦ湇鍔″櫒涓嶈锛屽帇鏍规病鎯宠繃鏀规敼鑷繁鐨勪唬鐮併€備粖澶╁挶浠氨鍞犵偣瀹炲湪鐨勶紝5涓帴鍦版皵鐨勪紭鍖栨妧宸э紝淇濆噯璁╀綘鐨勬煡璇㈤€熷害椋炶捣鏉ャ€?/p>

涓€銆佲€?strong>鈥嬬储寮曚笉鏄秺澶氳秺濂斤紝浣嗗叧閿綅缃繀椤绘湁鈥?/strong>鈥?/h3>

"鎴戞槑鏄庡姞浜嗙储寮曪紝鎬庝箞杩樻槸鎱紵"杩欐槸鏂版墜甯歌俯鐨勫潙銆備妇涓緥瀛愶細鐢ㄦ埛琛ㄦ湁200涓囨暟鎹紝鎸夋墜鏈哄彿鏌ョ敤鎴峰緱2绉掋€傝繖鏃跺€欎綘鍔犱釜ALTER TABLE users ADD INDEX idx_phone(phone);锛岀灛闂撮檷鍒?.1绉掋€?/p>

涓嶈繃瑕佹敞鎰忓晩锛岀储寮曞氨鍍忓弻鍒冨墤锛?/p>

• 鉁旓笍 鈥?strong>鈥嬮珮棰戞煡璇㈠瓧娈靛繀鍔犫€?/strong>鈥嬶紙姣斿鐧诲綍鐢ㄧ殑鎵嬫満鍙凤級
• 鉂?鈥?strong>鈥嬪埆缁欐€у埆瀛楁鍔犵储寮曗€?/strong>鈥嬶紙灏辩敺/濂充咯鍊硷紝鍔犱簡鍙嶈€屾嫋鎱㈠啓鍏ラ€熷害锛?/li>
• 馃挕 鈥?strong>鈥嬭仈鍚堢储寮曟敞鎰忛『搴忊€?/strong>鈥嬶細WHERE city='鍖椾含' AND age>30鐨勬煡璇紝绱㈠紩搴旇寤?code>(city,age)鑰屼笉鏄弽杩囨潵

鈥?strong>鈥嬪満鏅渚嬧€?/strong>鈥嬶細鏌愮數鍟嗗钩鍙拌鍗曡〃鏌ヨ鎱紝鍘熸潵鏄敤order_time鍗曠嫭绱㈠紩锛屾敼鎴?code>(user_id, order_time)鑱斿悎绱㈠紩鍚庯紝鍝嶅簲鏃堕棿浠?.8绉掗檷鍒?.3绉掋€?/p>

---

浜屻€佲€?strong>鈥嬪埆璁╂暟鎹簱鍋氭暟瀛﹂锛屽厛绠楀ソ鍐嶆煡鈥?/strong>鈥?/h3>

瑙佽繃鏈変汉鍐?code>WHERE price*0.8 > 100鍚楋紵鏁版嵁搴撳緱閫愯璁＄畻鎶樻墸浠凤紝200涓囨暟鎹兘鍗″埌鎬€鐤戜汉鐢熴€傝繖鏃跺€欐敼鎴?code>WHERE price > 125锛堝洜涓?00梅0.8=125锛夛紝鐩存帴璁╃储寮曠敓鏁堬紝閫熷害鎻愬崌10鍊嶄笉鏄ⅵ銆?/p>

鈥?strong>鈥嬩笁涓绠楃鍖衡€?/strong>鈥嬶細

1. 瀛楁鍙備笌杩愮畻锛?code>year(create_time)=2023 鉂?鈫?create_time BETWEEN '2023-01-01' AND '2023-12-31' 鉁旓笍锛?/li>
2. 鍑芥暟澶勭悊瀛楁锛?code>UPPER(username)='JACK' 鉂?鈫?瀛樻暟鎹椂鐩存帴杞ぇ鍐?鉁旓笍锛?/li>
3. 绫诲瀷寮哄埗杞崲锛?code>WHERE phone=1380013800 鉂?鏁板瓧 vs 瀛楃涓?鈫?鏌ュ墠缁熶竴绫诲瀷 鉁旓笍锛?/li>

---

涓夈€佲€?strong>鈥嬪垎椤垫煡璇㈣浜哄懡锛熻瘯璇?娓告爣娉?鈥?/strong>鈥?/h3>

"鏌ョ10000椤垫暟鎹崱鎴愮嫍鎬庝箞鍔烇紵"鈥斺€旂敤LIMIT 10000,10灏辨槸鍦ㄨ鏁版嵁搴撴暟鍒扮10000鏉″啀杩斿洖锛?00涓囨暟鎹垎椤佃兘鎱㈠埌鍝€?/p>

鈥?strong>鈥嬩袱绉嶇牬瑙ｅЭ鍔库€?/strong>鈥嬶細

1. 鈥?strong>鈥嬬敤ID璺宠浆鈥?/strong>鈥嬶細WHERE id > 涓婁竴椤垫渶澶D LIMIT 10锛堥渶瑕佽繛缁璉D锛?/li>
2. 鈥?strong>鈥嬭鐩栫储寮曟硶鈥?/strong>鈥嬶細鍏堟煡绱㈠紩閲岀殑ID锛屽啀鍏宠仈涓昏〃

sql澶嶅埗
SELECT * FROM orders 
JOIN (SELECT id FROM orders WHERE status=1 LIMIT 10000,10) AS tmp 
USING(id)

鈥?strong>鈥嬬湡瀹炴暟鎹姣斺€?/strong>鈥嬶細鏌愯鍧涚殑鍒嗛〉鏌ヨ浠?2绉掗檷鍒?.7绉掞紝灏遍潬杩欐嫑銆?/p>

---

鍥涖€佲€?strong>鈥嬪叧鑱旀煡璇㈡參锛熷厛鐪嬬湅鏄笉鏄?绗涘崱灏旂Н'鍦ㄦ崳涔扁€?/strong>鈥?/h3>

澶氳〃鑱旀煡鏃舵渶瀹规槗韪╃殑闆峰氨鏄棤鎰忎腑鎼炲嚭绗涘崱灏旂Н銆傛瘮濡傛煡鐢ㄦ埛鍜岃鍗曪細

sql澶嶅埗
SELECT * FROM users,orders WHERE users.id=orders.user_id  -- 姝ｇ‘鍐欐硶

瑕佹槸鎵嬫粦鍐欐垚WHERE users.age>18锛岄偅灏辩瓑鐫€鏌?00涓囩敤鎴访?00涓囪鍗曠殑鎭愭€栫粨鏋滃惂...

鈥?strong>鈥嬭仈鏌ヤ紭鍖栦笁鏉挎枾鈥?/strong>鈥嬶細

1. 鈥?strong>鈥嬫案杩滃厛杩囨护鍐嶅叧鑱斺€?/strong>鈥?/li>
2. 鈥?strong>鈥嬪皬琛ㄩ┍鍔ㄥぇ琛ㄢ€?/strong>鈥嬶紙鐢ㄦ埛琛?涓囨潯锛岃鍗曡〃500涓囨潯锛屽氨鐢ㄧ敤鎴疯〃鍋氫富琛級
3. 鈥?strong>鈥嬪繀瑕佹椂涓婂啑浣欏瓧娈碘€?/strong>鈥嬶紙姣斿鍦ㄨ鍗曡〃鐩存帴瀛樼敤鎴峰鍚嶏紝鐪佸緱鍏宠仈鐢ㄦ埛琛級

---

浜斻€佲€?strong>鈥嬫墽琛岃鍒掓槸浣犵殑X鍏夌墖锛屼笉鐪嬪氨寮€鍒€鍑嗗嚭浜嬧€?/strong>鈥?/h3>

鑰佷腑鍖绘妸鑴夎繕寰楃湅鑸岃嫈鍛紝璋僑QL涓嶇瀰鎵ц璁″垝灏辨槸鐬庢姌鑵俱€傚湪MySQL閲屾暡涓?code>EXPLAIN SELECT ...锛岄噸鐐圭湅杩欎笁鍒楋細

• 鈥?strong>鈥媡ype鍒椻€?/strong>鈥嬶細鍑虹幇ALL灏辨槸鍏ㄨ〃鎵弿锛岃刀绱у姞绱㈠紩
• 鈥?strong>鈥媟ows鍒椻€?/strong>鈥嬶細浼扮畻鎵弿琛屾暟锛岃秴杩?涓囧氨瑕佽鎯?/li>
• 鈥?strong>鈥婨xtra鍒椻€?/strong>鈥嬶細鐪嬭Using temporary鎴?code>Using filesort璇存槑鏈変复鏃惰〃鎴栨枃浠舵帓搴?/li>

鈥?strong>鈥嬪疄鎴樻紨绀衡€?/strong>鈥嬶細鏌愮墿娴佺郴缁熸煡杩愬崟锛?code>EXPLAIN鏄剧ず鎵弿浜?0涓囪锛屽姞涓?code>(region_code,status)绱㈠紩鍚庯紝鎵弿琛屾暟鐩存帴闄嶅埌150琛屻€?/p>

---

涓汉瑙傜偣锛氫紭鍖栦笉鏄偒鎶€锛岃€屾槸鎵惧钩琛?/h3>

鎼炰簡杩欎箞澶氬勾鏁版嵁搴擄紝鎴戝彂鐜板緢澶氫汉闄峰叆涓や釜鏋佺锛氳涔堟棤鑴戝姞绱㈠紩锛岃涔堜负浜?鏋佽嚧浼樺寲"鎶奡QL鍐欏緱鍍忓ぉ涔︺€傚叾瀹炲惂锛岃浣忎笁涓師鍒欏氨琛岋細

1. 鈥?strong>鈥嬩簩鍏畾寰嬧€?/strong>鈥嬶細鍏堣В鍐冲悆鎺?0%鎬ц兘鐨勯偅20%鐨凷QL
2. 鈥?strong>鈥嬮€傚彲鑰屾鈥?/strong>鈥嬶細鎶?绉掔殑鏌ヨ浼樺寲鍒?.5绉掑氨鍙互鏀舵墜锛岄潪瑕佸帇鍒?.01绉掑彲鑳藉緱涓嶅伩澶?/li>
3. 鈥?strong>鈥嬬溂瑙佷负瀹炩€?/strong>鈥嬶細鍒俊鎰熻锛岀敤EXPLAIN鍜?code>SHOW PROFILES鐪嬬湡瀹炴暟鎹?/li>

灏卞儚鍋氳彍鏀剧洂锛屽捀娣″悎閫傛渶閲嶈銆備笅娆￠亣鍒版參鏌ヨ锛屽厛鍒€ョ潃鐢╅攨缁欒繍缁达紝鎸変粖澶╄鐨勪簲鎷涜瘯璇曪紝淇濆噯鐢叉柟澶镐綘"绐佺劧寮€绐嶄簡"銆?/p>