數(shù)據(jù)庫運(yùn)維 形考5-數(shù)據(jù)庫性能監(jiān)視與優(yōu)化實(shí)驗(yàn)

1、實(shí)驗(yàn)5數(shù)據(jù)庫性能監(jiān)視與優(yōu)化實(shí)驗(yàn)1.實(shí)驗(yàn)?zāi)康睦斫鈹?shù)據(jù)庫性能概念，練習(xí)數(shù)據(jù)庫性能監(jiān)視命令方法，能夠?qū)?shù)據(jù)庫性能進(jìn)行優(yōu)化。2.實(shí)驗(yàn)內(nèi)容【實(shí)驗(yàn)5-1】使用SHOW語句查詢設(shè)備吞吐量Questions、Com_Select、Comnsert、Com_update、Com_delete幾個(gè)指標(biāo)值。【實(shí)驗(yàn)5-2】訪問Performance_schema中的events_statements_summary_by_digest表/獲取有關(guān)的延遲、錯(cuò)誤和查詢量信息的性能指標(biāo)?！緦?shí)驗(yàn)5-3】使用SHOW語句查詢連接檢查指標(biāo)Threads_connected、Threads_running、Connection_e

2、rrorsnternal、Aborted_connects和Connection_errors_max_connections。h紹SKMSTATUSLKEe:*三VrL|b2_QeValaeThrMdieenMCted21rwiomi(0.00mSflO*STA1CSLISConnecti-gn.errors.LDterBar;Varrabl一aaaeIralu*ICcxnectioQrrrers：internal0WO0IrevLnset(0.00tec)rl9KMSTAItSLHEeAbert*deea*tr：Var:*ble.QMrValtw*Abortd.caan*ets0TOC o

3、 1-5 h zThrnd-s_dj；!rnlittatID.DOmcTtlsmJTATVSirxETHif*ds.nm;n|ValoThr*d仁cjABLnI15二二-LL一.-1rvt誑tt0CCmc3【實(shí)驗(yàn)5-4】使用SHOW語句查詢Innodb緩沖區(qū)指標(biāo)Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads?！緦?shí)驗(yàn)5-5】使用SHOW語句獲取與查詢緩沖相關(guān)的指標(biāo)：Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qc

4、ache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、Qcache_queries_in_cache、Qcache_total_blocks。、vtsqISBOVSTATICUEEQcBctw.frH.blocksFT?！癿ffTAMLIKE條Kht.”4rFr*ntbi*_oeruw*i-一一上一一一一一QWbr.tV4.BrUfYIW18731rwMtSHVSTaMLtXE皿；Varil)lviiBVtluvQuebthiis0FIroviatIQtQwcltaptqlsAlSUITSLHE”4cacfaewt；ITlw*Qeaetw.la

5、sertiI01m淖sat(0.0(1tKl祈列“SKYSUHSUK*-Qtacbr_ldwm_pnMB*)Iarlli.swt&lu*Occomr_prWH1fr4-GA-1miottt(C.DO9*cl【實(shí)驗(yàn)5-6】使用SHOW語句獲取關(guān)于臨時(shí)表的指標(biāo)Created_tmp_disk_tables、Created_tmp_files、Created_tmp_tables。errISBEX5TA7T5LTIE.75WVSTATUSLIU*Cr*Cd.TW)i1四卜！jwValw4S1rowmttt!0.00tc日峙15WSJATVSLIKE*Cr*4t*dtAblti*;Eacitvi*tI

6、D00*rIpt平“SHQVSTATVSLtlEVriBj)l*_nM*teltttCrtatedupuUitvi21minttQ.00scl【實(shí)驗(yàn)5-7】使用SHOW語句獲取訪問表的數(shù)量指標(biāo)Open_tables和Opened_tables?！玖吓e500】海在SHOWPROCESSLIST劭団在壬Rqls歸fflsa【料舉59】stBawi凹辿amysq-dumps-ow*sfGB滸#?。TihUiifljtrtqlcoluaA_pri*dbtYtnTfimcHwral_19fgiid_n*catrdh*lp_JcTPwrtb*lp_relationfaeLp_lQlEladuitatilf

7、iaodb_abtincfc_binjo_indrtplvciflprecprotijrirpTQEi+tjirir1#TT#J_rMtirrrrTslarrauttfiAfailvr_rrl*y.L*rnrfctrinfe【實(shí)驗(yàn)5-11】使用EXPLAINEXTENDED命令查看如下語句的執(zhí)行計(jì)劃：SELECT*FROMt1WHEREa110AND(EXISTS(SELECTa2FROMt2WHEREt2.a25ANDt2.b2=1)OREXISTS(SELECTa2FROMt2WHEREt2.a210)v_t2WHEREtl.alvIOANDv_t2.a2v20;【實(shí)驗(yàn)5-13】創(chuàng)建一個(gè)表，并在適當(dāng)字段上創(chuàng)建索引，對比在大數(shù)據(jù)量情形下使用索引與不使用索引的性能?！玖吓e514】海在P