MySQL澶勭悊甯歌闇姹

濡備綍瀵硅瘎璁鸿繘琛屽垎椤靛睍绀

select customer_id,title,content from  `product_comment` where audit_status=1 and product_id=100 limit 0,5;

explain 鎵ц璁″垝鍒嗘瀽

鐩墠鍙互瀵箂elect update insert replace delete 杩涜鍒嗘瀽
鍙互浠巈xplain鐭ラ亾
– sql濡備綍浣跨敤绱㈠紩
– 鑱旀帴鏌ヨ鐨勬墽琛岄『搴
– 鏌ヨ鎵弿鐨勬暟鎹鏁
渚嬪锛
id锛1
select_type:simple
table: product_comment
partitions: null
type: all
possible_keys: null
key: null
key_len:null
ref:null
rows:9494
filtered:1.00
extra:using where

id鍒

id鍒椾腑鐨勬暟鎹负涓缁勬暟瀛楋紝琛ㄧず鎵цselect璇彞鐨勯『搴
id鍊肩浉鍚屾椂锛屾墽琛岄『搴忕敱涓婅嚦涓
id鍊艰秺澶т紭鍏堢骇瓒婇珮锛岃秺鍏堣鎵ц

select_type鍒

鍚箟
SIMPLE 涓嶅寘鍚瓙鏌ヨ鎴栨槸UNION鎿嶄綔鐨勬煡璇
primary 鏌ヨ涓鏋滃寘鍚换浣曞瓙鏌ヨ锛岄偅涔堟渶澶栧眰鐨勬煡璇㈠垯琚爣璁颁负primary
subquery select 鍒楄〃涓殑瀛愭煡璇
dependent subquery 渚濊禆澶栭儴缁撴灉鐨勫瓙鏌ヨ

table鍒

杈撳嚭鏁版嵁琛屾墍鍦ㄧ殑琛ㄧ殑鍚嶇О

partitions鍒

瀵逛簬鍒嗗尯琛紝鏄剧ず鏌ヨ鐨勫垎鍖篿d
瀵逛簬闈炲垎鍖鸿〃锛屾樉绀轰负null

type

鎬ц兘 鍚箟
system 杩欐槸const杩炴帴绫诲瀷鐨勪竴涓壒渚嬶紝褰撴煡璇㈢殑琛ㄥ彧鏈変竴琛屾椂浣跨敤
const 琛ㄤ腑鏈変笖鍙湁涓涓尮閰嶇殑琛屾椂浣跨敤锛屽瀵逛富閿垨鏄敮涓绱㈠紩鐨勬煡璇紝杩欐槸鏁堢巼鏈楂樼殑杩炴帴鏂瑰紡
eq_ref 鍞竴绱㈠紩鎴栬呬富閿储寮曟煡鎵撅紝瀵逛簬姣忎釜绱㈠紩閿紝琛ㄤ腑鍙湁涓鏉¤褰曚笌涔嬪尮閰嶃
ref 闈炲敮涓绱㈠紩鏌ユ壘锛岃繑鍥炲尮閰嶆煇涓崟鐙兼墍鏈夎銆
ref_or_null 绫诲瀷浜巖ef绫诲瀷鐨勬煡璇紝浣嗘槸闄勫姞浜嗗null鍊煎垪鐨勬煡璇
index_merge 璇ヨ繛鎺ョ被鍨嬭〃绀轰娇鐢ㄤ簡绱㈠紩鍚堝苟浼樺寲鏂规硶銆
range 绱㈠紩鑼冨洿鎵弿锛屽父瑙佷簬between锛> , <杩欐牱鐨勬煡璇㈡潯浠
index FUll index scan鍏ㄧ储寮曟壂鎻忥紝鍚宎ll鐨勫尯鍒槸锛岄亶鍘嗙殑鏄储寮曟爲
all full table scan 鍏ㄨ〃鎵弿锛 鏁堢巼鏈宸

extra 鍒

鍚箟
distanct 浼樺寲distinct鎿嶄綔锛屽湪鎵惧埌绗竴鍖归厤鐨勫厓缁勫悗鍗冲仠姝㈡壘鍚屾牱鍊肩殑鍔ㄤ綔
not exists 浣跨敤not exists鏉ヤ紭鍖栨煡璇
using filesort 浣跨敤棰濆鎿嶄綔杩涜鎺掑簭锛岄氬父浼氬嚭鐜板湪 order by 鎴杇roup by 鏌ヨ涓
using index 浣跨敤浜嗚鐩栫储寮曡繘琛屾煡璇
using temporary MySQL闇瑕佷娇鐢ㄤ复鏃惰〃鏉ュ鐞嗘煡璇紝甯歌浜庢帓搴忥紝瀛愭煡璇㈠拰鍒嗙粍鏌ヨ
using where 闇瑕佸湪MySQL鏈嶅姟鍣ㄥ眰浣跨敤where 鏉′欢鏉ヨ繃婊ゆ暟鎹
select tables optimized away 鐩存帴閫氳繃绱㈠紩鏉ヨ幏寰楁暟鎹紝涓嶇敤璁块棶琛

possible_key 鍒

鎸囧嚭MySQL鑳戒娇鐢ㄥ摢浜涚储寮曟潵浼樺寲鏌ヨ锛屼笉涓瀹氫細琚娇鐢ㄣ

key鍒

鏌ヨ浼樺寲鍣 浼樺寲鏌ヨ瀹為檯鎵浣跨敤鐨勭储寮曪紝濡傛灉娌℃湁鍙敤鐨勭储寮曪紝鍒欐樉绀轰负null

key_len鍒

琛ㄧず绱㈠紩瀛楁鐨勬渶澶у彲鑳介暱搴︼紝key_len鐨勯暱搴︾敱瀛楁瀹氫箟璁$畻鑰屾潵锛屽苟闈炴暟鎹殑瀹為檯闀垮害銆

ref 鍒

琛ㄧず閭d簺鍒楁垨甯搁噺琚敤浜庢煡鎵剧储寮曞垪涓婄殑鍊

rows鍒

琛ㄧずMySQL閫氳繃绱㈠紩缁熻淇℃伅锛屼及绠楃殑鎵闇璇诲彇鐨勮鏁帮紝rows鐨勫ぇ灏忔槸涓粺璁℃娊鏍风粨鏋滐紝骞朵笉鍗佸垎鍑嗙‘銆

Filtered鍒

琛ㄧず杩斿洖缁撴灉鐨勮鏁板崰闇璇诲彇琛屾暟鐨勭櫨鍒嗘瘮锛孎iltered鍒楃殑鍊艰秺澶ц秺濂斤紝Filtered鍒楃殑鍊间緷璧栦簬缁熻淇℃伅銆

鎵ц璁″垝鐨勯檺鍒

鏃犳硶灞曠ず瀛樺偍杩囩▼锛岃Е鍙戝櫒锛孶DF瀵规煡璇㈢殑褰卞搷銆

All posts

Other pages

鍙戣〃鍥炲

鎮ㄧ殑鐢靛瓙閭鍦板潃涓嶄細琚叕寮銆 蹇呭~椤瑰凡鐢*鏍囨敞