Mysql和單晒SQL議匯違化帶
<貧匯准
和匯准>
宥狛show status才哘喘蒙泣阻盾光嶽SQL議峇佩撞楕
宥狛SHOW STATUS辛參戻工捲暦匂彜蓑佚連?匆辛參聞喘mysqladmin extended-status凋綜資誼。SHOW STATUS辛參功象俶勣?塋?session雫艶議由柴潤惚才global雫艶議由柴潤惚。
參和叱倖歌方斤Myisam才Innodb贋刈哈陪脅柴方?- Com_select 峇佩select荷恬議肝方?匯肝臥儂峪拙紗1?
- Com_insert 峇佩insert荷恬議肝方?斤噐答楚峨秘議insert荷恬?峪拙紗匯肝?
- Com_update 峇佩update荷恬議肝方?
- Com_delete 峇佩delete荷恬議肝方。
參和叱倖歌方頁寞斤Innodb贋刈哈陪柴方議?拙紗議麻隈匆待嗤音揖?
- Innodb_rows_read select臥儂卦指議佩方?
- Innodb_rows_inserted峇佩Insert荷恬峨秘議佩方?
- Innodb_rows_updated 峇佩update荷恬厚仟議佩方?
- Innodb_rows_deleted 峇佩delete荷恬評茅議佩方。
宥狛參貧叱倖歌方?辛參載否叟議阻盾輝念方象垂議哘喘頁參峨秘厚仟葎麼珊頁參臥儂荷恬葎麼?參式光嶽窃侏議SQL寄崑議峇佩曳箭頁謹富。斤噐厚仟荷恬議柴方?頁斤峇佩肝方議柴方?音胎戻住珊頁指獄脅氏拙紗。
斤噐並暦侏議哘喘?宥狛Com_commit才Com_rollback辛參阻盾並暦戻住才指獄議秤趨?斤噐指獄荷恬掲械撞訓議方象垂?辛嬬吭龍彭哘喘園亟贋壓諒籾。
緩翌?參和叱倖歌方宴噐厘断阻盾方象垂議児云秤趨?
- Connections 編夕銭俊Mysql捲暦匂議肝方
- Uptime 捲暦匂垢恬扮寂
- Slow_queries 蛸臥儂議肝方
協了峇佩丼楕熟詰議SQL囂鞘
辛參宥狛參和曾嶽圭塀協了峇佩丼楕熟詰議SQL囂鞘?- 辛參宥狛蛸臥儂晩崗協了椎乂峇佩丼楕熟詰議sql囂鞘?喘--log-slow-queries[=file_name]僉?尼強扮?mysqld亟匯倖淫根侭嗤峇佩扮寂階狛long_query_time昼議SQL囂鞘議晩崗猟周。辛參全俊欺砿尖略擦嶄議屢購嫗准。
- 蛸臥儂晩崗壓臥儂潤崩參朔嘉射村?侭參壓哘喘郡啌峇佩丼楕竃孖諒籾議扮昨臥儂蛸臥儂晩崗旺音嬬協了諒籾?辛參聞喘show processlist凋綜臥心輝念MySQL壓序佩議?殻?淫凄?殻議彜蓑?頁倦迄燕吉吉?辛參糞扮議臥心SQL峇佩秤趨?揖扮斤匯乂迄燕荷恬序佩單晒。
宥狛EXPLAIN蛍裂詰丼SQL議峇佩柴皿
宥狛參貧化帶臥儂欺丼楕詰議SQL朔?厘断辛參宥狛explain賜宀desc 資函MySQL泌採峇佩SELECT囂鞘議佚連?淫凄select囂鞘峇佩狛殻燕泌採銭俊才銭俊議肝會。explain辛參岑祇焚担扮昨駅倬葎燕紗秘沫哈參誼欺匯倖聞喘沫哈栖儖孀芝村議厚酔議SELECT。
mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006;
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| select_type | table | type | possible_keys| key | key_len | rows | Extra |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| SIMPLE | b | index | PRIMARY | PRIMARY | 4 | 1 | Using index |
| SIMPLE | a | ALL | NULL | NULL | NULL | 12 | Using where |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
2 rows in set (0.02 sec)
傍苧?
- select_type?select 窃侏
- table?補竃潤惚鹿議燕
-
type?燕幣燕議銭俊窃侏
- ?輝燕嶄叙嗤匯佩頁type議峙葎system頁恷煮議銭俊窃侏?
- ?輝select荷恬嶄聞喘沫哈序佩燕銭俊扮type議峙葎ref?
- ?輝select議燕銭俊短嗤聞喘沫哈扮?将械氏心欺type議峙葎ALL?燕幣斤乎燕序佩阻畠燕膝宙?宸扮俶勣深打宥狛幹秀沫哈栖戻互燕銭俊議丼楕。
- possible_keys?燕幣臥儂扮,辛參聞喘議沫哈双.
- key?燕幣聞喘議沫哈
- key_len?沫哈海業
- rows?膝宙袈律
- Extra?峇佩秤趨議傍苧才宙峰
鳩協諒籾?旺寡函屢哘議單晒企仏
将狛參貧化帶?児云辛參鳩範諒籾竃孖議圻咀?辛參功象秤趨寡函屢哘議企仏?序佩單晒戻互峇佩議丼楕。箭泌貧中議箭徨?厘断鳩範頁斤a燕議畠燕膝宙擬崑丼楕議音尖??厘断斤a燕議year忖粁幹秀阻沫哈?臥儂俶勣膝宙議佩方苧?埆鷲戞?
mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006;
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| select_type | table | type | possible_keys| key | key_len | rows | Extra |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| SIMPLE | b | index | PRIMARY | PRIMARY | 4 | 1 | Using index |
| SIMPLE | a | ref | year | year | 4 | 3 | Using where |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
2 rows in set (0.02 sec)
<貧匯准
和匯准>