您现在的位置:首页 >> 感人故事 > mysql优化方案_mysql澶氳〃闅忔満鏌ヨ浼樺寲鏂规

mysql优化方案_mysql澶氳〃闅忔満鏌ヨ浼樺寲鏂规

时间:2018-08-15 来源:泥巴往事网 本文已影响

相关热词搜索:澶氳mysql浼樺寲鏂规mysql澶氳〃闅忔満鏌ヨ浼樺寲鏂规mysql多表查询语句mysql多表联合查询

鎴戜互鍓嶄粙缁嶈繃鏈€澶氱殑灏辨槸鍗曡〃闅忔満鏌ヨ鏃剁殑浼樺寲浜嗭紝浠婂ぉ 鐪嬪埌涓€绔欓暱鍒嗕韩浜嗕竴涓琛ㄩ殢鏈烘煡璇紭鍖栫殑涓€浜涙柟娉曚笌鏂规锛屼笅闈㈡垜浠氨涓€璧锋潵鐪嬬湅鍚?

鏈枃涓昏璋堣濡備綍瀹炵幇Mysql鐨勯殢鏈烘煡璇紝澶氳〃闅忔満鏌ヨ銆傚湪Mysql涓殢鏈哄彇鍑轰竴鏉¤褰曠殑瀹炵幇鏂规硶銆?/p>

鎴戜滑閫氬父鐨勬煡璇㈡槸娌℃湁where鎴栬€厀here fields>2杩欐牱鐨勬柟寮忥紝杩欐牱鍙兘鍙栧嚭鍦ㄦ煇绉嶆潯浠朵笅鐨勪竴鏉℃垨澶氭潯锛屽鏋滄潯浠朵笉鍙?渚嬪2)锛岄偅涔堢粨鏋滃氨涓€鐩翠笉浼氭湁鍙樺寲銆?/p>

閭d箞濡備綍瀹炵幇闅忔満鏌ヨ鍛?鏈汉鏈変袱绉嶆柟娉曘€?/p>

鏂规硶涓€銆佹暟鎹〃璁板綍涓嶅ぇ鐨勬儏鍐典笅锛?/p>

select * from `table`

鏌ュ嚭鏉ユ墍鏈夌殑璁板綍鍒楄〃锛岀劧鍚巃rray_rand()闅忔満鍑轰竴涓粨鏋滅殑鏁扮粍鐨刱ey銆傝繛缁殑key鍙互浣跨敤mt_rand(1, count($list)); 涓轰粈涔堜笉浣跨敤rand鑰屾槸mt_rand鍛?鍥犱负mt_rand甯乺and蹇?鍊嶃€?/p>

杩欑鎯呭喌涓嬶紝鏌ュ嚭鏁翠釜鍒楄〃锛屽瓨鍏ュ埌Memcache鐨勭紦瀛樻垨鑰匯edis鐨凬oSQL涓紝涓嬫鐩存帴鍙栧嚭缁撴灉闆嗚€屼笉闇€瑕佹煡琛ㄣ€備笉杩囧綋鏁版嵁閲忎竴鏃﹁秴杩囦竾绾у埆锛屽彇鍑哄垪琛ㄥ氨寰堝洶闅句簡銆?/p>

鏂规硶浜岋細浣跨敤SQL璇彞闅忔満

MYSQL鍑芥暟RAND()锛屼骇鐢熶竴涓?-1涔嬮棿鐨勫皬鏁帮紝鐒跺悗MAX(`id`)鍙互鑾峰緱璇ヨ〃涓渶澶х殑ID銆傞偅涔圡AX(`id`) * RAND()灏卞彲浠ュ彇鍒拌〃涓墍鏈夌殑ID銆侽K锛岀湅璇彞銆?/p>

SELECT * FROM `table` WHERE `id` > (SELECT RAND() * (SELECT MAX(`id`) FROM `table`) LIMIT 0, 1

鏃㈢劧MAX(`id`) * MAX(`id`)鍙互鍙栧埌琛ㄩ噷鎵€鏈夊€硷紝閭d箞鏈鍙ョ殑WHERE灏卞彲浠ュ彇鍒版湰琛ㄧ殑鎵€鏈夋儏鍐碉紝閭d箞杩欏氨鏄竴涓墍鏈夎褰曢兘鏈夊彲鑳借鍙栧埌鐨勯殢鏈篠QL璇彞銆?/p>

琛ュ厖鍙︿竴绡囨枃绔?/p>

1. 澶氳〃杩炴帴绫诲瀷

1. 绗涘崱灏旂Н(浜ゅ弶杩炴帴) 鍦∕ySQL涓彲浠ヤ负CROSS JOIN鎴栬€呯渷鐣ROSS鍗矹OIN锛屾垨鑰呬娇鐢?," 濡傦細

SELECT * FROM table1 CROSS JOIN table2

SELECT * FROM table1 JOIN table2

SELECT * FROM table1,table2

鐢变簬鍏惰繑鍥炵殑缁撴灉涓鸿杩炴帴鐨勪袱涓暟鎹〃鐨勪箻绉紝鍥犳褰撴湁WHERE, ON鎴朥SING鏉′欢鐨勬椂鍊欎竴鑸笉寤鸿浣跨敤锛屽洜涓哄綋鏁版嵁琛ㄩ」鐩お澶氱殑鏃跺€欙紝浼氶潪甯告參銆備竴鑸娇鐢↙EFT [OUTER] JOIN鎴栬€匯IGHT [OUTER] JOIN

2. 鍐呰繛鎺NNER JOIN 鍦∕ySQL涓妸INNER JOIN鍙仛绛夊€艰繛鎺ワ紝鍗抽渶瑕佹寚瀹氱瓑鍊艰繛鎺ユ潯浠跺湪MySQL涓瑿ROSS鍜孖NNER JOIN琚垝鍒嗗湪涓€璧枫€?join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. MySQL涓殑澶栬繛鎺ワ紝鍒嗕负宸﹀杩炴帴鍜屽彸杩炴帴锛屽嵆闄や簡杩斿洖绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滀箣澶栵紝杩樿杩斿洖宸﹁〃(宸﹁繛鎺?鎴栬€呭彸琛?鍙宠繛鎺?涓笉绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滐紝鐩稿搴旂殑浣跨敤NULL瀵瑰簲銆?/p>

渚嬪瓙锛?/p>

user琛?

id | name

———

1 | libk

2 | zyfon

3 | daodao

user_action琛?

user_id | action

—————

1 | jump

1 | kick

1 | jump

2 | run

4 | swim

sql:

select id, name, action from user as u

left join user_action a on u.id = a.user_id

result:

id | name | action

——————————–

1 | libk | jump 鈶?/p>

1 | libk | kick 鈶?/p>

1 | libk | jump 鈶?/p>

2 | zyfon | run 鈶?/p>

3 | daodao | null 鈶?/p>

鍒嗘瀽锛?/p>

娉ㄦ剰鍒皍ser_action涓繕鏈変竴涓猽ser_id=4, action=swim鐨勭邯褰曪紝浣嗘槸娌℃湁鍦ㄧ粨鏋滀腑鍑虹幇锛?/p>

鑰寀ser琛ㄤ腑鐨刬d=3, name=daodao鐨勭敤鎴峰湪user_action涓病鏈夌浉搴旂殑绾綍锛屼絾鏄嵈鍑虹幇鍦ㄤ簡缁撴灉闆嗕腑

鍥犱负鐜板湪鏄痩eft join锛屾墍鏈夌殑宸ヤ綔浠eft涓哄噯.

缁撴灉1锛?锛?锛?閮芥槸鏃㈠湪宸﹁〃鍙堝湪鍙宠〃鐨勭邯褰曪紝5鏄彧鍦ㄥ乏琛紝涓嶅湪鍙宠〃鐨勭邯褰?/p>

宸ヤ綔鍘熺悊锛?/p>

浠庡乏琛ㄨ鍑轰竴鏉★紝閫夊嚭鎵€鏈変笌on鍖归厤鐨勫彸琛ㄧ邯褰?n鏉?杩涜杩炴帴锛屽舰鎴恘鏉$邯褰?鍖呮嫭閲嶅鐨勮锛屽锛氱粨鏋?鍜岀粨鏋?)锛屽鏋滃彸杈规病鏈変笌on鏉′欢鍖归厤鐨勮〃锛岄偅杩炴帴鐨勫瓧娈甸兘鏄痭ull.鐒跺悗缁х画璇讳笅涓€鏉°€?/p>

寮曠敵锛?/p>

鎴戜滑鍙互鐢ㄥ彸琛ㄦ病鏈塷n鍖归厤鍒欐樉绀簄ull鐨勮寰? 鏉ユ壘鍑烘墍鏈夊湪宸﹁〃锛屼笉鍦ㄥ彸琛ㄧ殑绾綍锛?娉ㄦ剰鐢ㄦ潵鍒ゆ柇鐨勯偅鍒楀繀椤诲0鏄庝负not null鐨勩€?/p>

濡傦細

sql:

select id, name, action from user as u

left join user_action a on u.id = a.user_id

where a.user_id is NULL

(娉ㄦ剰:

1.鍒楀€间负null搴旇鐢╥s null 鑰屼笉鑳界敤=NULL

2.杩欓噷a.user_id 鍒楀繀椤诲0鏄庝负 NOT NULL 鐨?

)

涓婇潰sql鐨剅esult:

id | name | action

————————–

3 | daodao | NULL

——————————————————————————–

涓€鑸敤娉曪細

a. LEFT [OUTER] JOIN锛?/p>

闄や簡杩斿洖绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滀箣澶栵紝杩橀渶瑕佹樉绀哄乏琛ㄤ腑涓嶇鍚堣繛鎺ユ潯浠剁殑鏁版嵁鍒楋紝鐩稿搴斾娇鐢∟ULL瀵瑰簲

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column

b. RIGHT [OUTER] JOIN锛?/p>

RIGHT涓嶭EFT JOIN鐩镐技涓嶅悓鐨勪粎浠呮槸闄や簡鏄剧ず绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滀箣澶栵紝杩橀渶瑕佹樉绀哄彸琛ㄤ腑涓嶇鍚堣繛鎺ユ潯浠剁殑鏁版嵁鍒楋紝鐩稿簲浣跨敤NULL瀵瑰簲

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column

Tips:

1. on a.c1 = b.c1 绛夊悓浜?using(c1)

2. INNER JOIN 鍜?, (閫楀彿) 鍦ㄨ涔変笂鏄瓑鍚岀殑

3. 褰?MySQL 鍦ㄤ粠涓€涓〃涓绱俊鎭椂锛屼綘鍙互鎻愮ず瀹冮€夋嫨浜嗗摢涓€涓储寮曘€?/p>

濡傛灉 EXPLAIN 鏄剧ず MySQL 浣跨敤浜嗗彲鑳界殑绱㈠紩鍒楄〃涓敊璇殑绱㈠紩锛岃繖涓壒鎬у皢鏄緢鏈夌敤鐨勩€?/p>

閫氳繃鎸囧畾 USE INDEX (key_list)锛屼綘鍙互鍛婅瘔 MySQL 浣跨敤鍙兘鐨勭储寮曚腑鏈€鍚堥€傜殑涓€涓储寮曞湪琛ㄤ腑鏌ユ壘璁板綍琛屻€?/p>

鍙€夌殑浜岄€変竴鍙ユ硶 IGNORE INDEX (key_list) 鍙鐢ㄤ簬鍛婅瘔 MySQL 涓嶄娇鐢ㄧ壒瀹氱殑绱㈠紩銆傚锛?/p>

mysql> SELECT * FROM table1 USE INDEX (key1,key2)

-> WHERE key1=1 AND key2=2 AND key3=3;

mysql> SELECT * FROM table1 IGNORE INDEX (key3)

-> WHERE key1=1 AND key2=2 AND key3=3;

2. 琛ㄨ繛鎺ョ殑绾︽潫鏉′欢

娣诲姞鏄剧ず鏉′欢WHERE, ON, USING

1. WHERE瀛愬彞

mysql>

SELECT * FROM table1,table2 WHERE table1.id=table2.id;

2. ON

mysql>

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id

LEFT JOIN table3 ON table2.id=table3.id;

3. USING瀛愬彞锛屽鏋滆繛鎺ョ殑涓や釜琛ㄨ繛鎺ユ潯浠剁殑涓や釜鍒楀叿鏈夌浉鍚岀殑鍚嶅瓧鐨勮瘽鍙互浣跨敤USING

渚嬪锛?/p>

SELECT FROM LEFT JOIN USING ()

杩炴帴澶氫簬涓や釜琛ㄧ殑鎯呭喌涓句緥锛?/p>

mysql>

SELECT artists.Artist, cds.title, genres.genre

FROM cds

LEFT JOIN genres N cds.genreID = genres.genreID

LEFT JOIN artists ON cds.artistID = artists.artistID;

鎴栬€?mysql>

SELECT artists.Artist, cds.title, genres.genre

FROM cds

LEFT JOIN genres ON cds.genreID = genres.genreID

LEFT JOIN artists -> ON cds.artistID = artists.artistID

WHERE (genres.genre = "Pop");

--------------------------------------------

鍏堣繃婊ゆ潯浠剁劧鍚庡啀鏍规嵁琛ㄨ繛鎺?鍚屾椂鍦ㄨ〃涓缓绔嬬浉鍏虫煡璇㈠瓧娈电殑绱㈠紩杩欐牱鍦ㄥぇ鏁版嵁澶氳〃鑱斿悎鏌ヨ鐨勬儏鍐典笅閫熷害鐩稿綋蹇?/p>

SELECT M.*,SS.SensorCode,SS.SensorStatus,SS.ManufacturerId,SS.Electricity,

SS.Voltage,SS.MinElectricity,SS.MinVoltage,SS.Temperature,SS.StatusUpdteDate,SS.UpdateStatus ,tp.PricingStrategyid,tps.FreeDuration,bat.BerthTypeId

FROM

(SELECT T.* , BS.ParkStatus,BS.ChangeTime ,CA.CantonName, SE.SectionName

FROM

(SELECT A.* ,B.BerthId,B.BerthCode,B.BerthAddress,B.BerthStatus,B.LineDirection,B.CantonId,B.SectionId

FROM

(SELECT AR.AreaId,AR.AreaCode,AR.AreaName FROM SYS_Area AS AR WHERE 1=1 AND AR.AreaCode="110" ) A

LEFT JOIN SYS_Berth AS B ON B.AreaId=A.AreaId ) T

JOIN SYS_BerthStatus AS BS ON T.BerthCode=BS.BerthCode

JOIN SYS_Canton AS CA ON T.CantonId=CA.CantonId

JOIN SYS_Section AS SE ON T.SectionId=SE.SectionId )M

LEFT JOIN SYS_Sensor SS ON M.BerthCode=SS.BerthCode

LEFT JOIN TRA_PricingBerth AS tp ON tp.BerthCode=M.BerthCode

LEFT JOIN TRA_PricingStrategy AS tps ON tps.PricingStrategyId=tp.PricingStrategyId

LEFT JOIN SYS_BerthAndType AS bat ON bat.BerthCode=M.BerthCode

ORDER BY BerthCode ASC

鍙﹀闇€瑕佹敞鎰忕殑鍦版柟 鍦∕ySQL涓秹鍙婂埌澶氳〃鏌ヨ鐨勬椂鍊欙紝闇€瑕佹牴鎹煡璇㈢殑鎯呭喌锛屾兂濂戒娇鐢ㄥ摢绉嶈繛鎺ユ柟寮忔晥鐜囨洿楂樸€?/p>

1. 浜ゅ弶杩炴帴(绗涘崱灏旂Н)鎴栬€呭唴杩炴帴 [INNER | CROSS] JOIN

2. 宸﹀杩炴帴LEFT [OUTER] JOIN鎴栬€呭彸澶栬繛鎺IGHT [OUTER] JOIN 娉ㄦ剰鎸囧畾杩炴帴鏉′欢WHERE, ON锛孶SING.

3. MySQL濡備綍浼樺寲LEFT JOIN鍜孯IGHT JOIN

鍦∕ySQL涓紝A LEFT JOIN B join_condition鎵ц杩囩▼濡備笅锛?/p>

1)· 鏍规嵁琛ˋ鍜孉渚濊禆鐨勬墍鏈夎〃璁剧疆琛˙銆?/p>

2)· 鏍规嵁LEFT JOIN鏉′欢涓娇鐢ㄧ殑鎵€鏈夎〃(闄や簡B)璁剧疆琛ˋ銆?/p>

3)· LEFT JOIN鏉′欢鐢ㄤ簬纭畾濡備綍浠庤〃B鎼滅储琛屻€?鎹㈠彞璇濊锛屼笉浣跨敤WHERE瀛愬彞涓殑浠讳綍鏉′欢)銆?/p>

4)· 鍙互瀵规墍鏈夋爣鍑嗚仈鎺ヨ繘琛屼紭鍖栵紝鍙槸鍙湁浠庡畠鎵€渚濊禆鐨勬墍鏈夎〃璇诲彇鐨勮〃渚嬪銆傚鏋滃嚭鐜板惊鐜緷璧栧叧绯伙紝MySQL鎻愮ず鍑虹幇涓€涓敊璇€?/p>

5)· 杩涜鎵€鏈夋爣鍑哤HERE浼樺寲銆?/p>

6)· 濡傛灉A涓湁涓€琛屽尮閰峎HERE瀛愬彞锛屼絾B涓病鏈変竴琛屽尮閰峅N鏉′欢锛屽垯鐢熸垚鍙︿竴涓狟琛岋紝鍏朵腑鎵€鏈夊垪璁剧疆涓篘ULL銆?/p>

7)· 濡傛灉浣跨敤LEFT JOIN鎵惧嚭鍦ㄦ煇浜涜〃涓笉瀛樺湪鐨勮锛屽苟涓旇繘琛屼簡涓嬮潰鐨勬祴璇曪細WHERE閮ㄥ垎鐨刢ol_name IS NULL锛屽叾涓璫ol_name鏄竴涓0鏄庝负 NOT NULL鐨勫垪锛孧ySQL鎵惧埌鍖归厤LEFT JOIN鏉′欢鐨勪竴涓鍚庡仠姝?涓哄叿浣撶殑鍏抽敭瀛楃粍鍚?鎼滅储鍏跺畠琛屻€?/p>

RIGHT JOIN鐨勬墽琛岀被浼糒EFT JOIN锛屽彧鏄〃鐨勮鑹插弽杩囨潵銆?/p>

鑱旀帴浼樺寲鍣ㄨ绠楄〃搴旇仈鎺ョ殑椤哄簭銆侺EFT JOIN鍜孲TRAIGHT_JOIN寮哄埗鐨勮〃璇婚『搴忓彲浠ュ府鍔╄仈鎺ヤ紭鍖栧櫒鏇村揩鍦板伐浣滐紝鍥犱负妫€鏌ョ殑琛ㄤ氦鎹㈡洿灏戙€傝娉ㄦ剰杩欒鏄庡鏋滄墽琛屼笅闈㈢被鍨嬬殑鏌ヨ锛孧ySQL杩涜鍏ㄦ壂鎻廱锛屽洜涓篖EFT JOIN寮哄埗瀹冨湪d涔嬪墠璇诲彇锛?/p>

SELECT *

FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

鍦ㄨ繖绉嶆儏鍐典笅淇鏃剁敤a鐨勭浉鍙嶉『搴忥紝b鍒椾簬FROM瀛愬彞涓細

SELECT *

FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

MySQL鍙互杩涜涓嬮潰鐨凩EFT JOIN浼樺寲锛氬鏋滃浜庝骇鐢熺殑NULL琛岋紝WHERE鏉′欢鎬讳负鍋囷紝LEFT JOIN鍙樹负鏅€氳仈鎺ャ€?/p>

渚嬪锛屽湪涓嬮潰鐨勬煡璇腑濡傛灉t2.column1涓篘ULL锛學HERE 瀛愬彞灏嗕负false锛?/p>

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

鍥犳锛屽彲浠ュ畨鍏ㄥ湴灏嗘煡璇㈣浆鎹负鏅€氳仈鎺ワ細

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

杩欐牱鍙互鏇村揩锛屽洜涓哄鏋滃彲浠ヤ娇鏌ヨ鏇翠匠锛孧ySQL鍙互鍦ㄨ〃t1涔嬪墠浣跨敤琛╰2銆備负浜嗗己鍒朵娇鐢ㄨ〃椤哄簭锛屼娇鐢⊿TRAIGHT_JOIN銆?/p>

 
  • 美文摘抄
  • 实用文档
  • 优秀作文
  • 语文知识
  • 说说
  • 教案
  • 好句子
  • 泥巴往事网 © 2018 版权所有 All Rights Reserved. 湘ICP备14009743号-32