數(shù)據(jù)庫(kù)性能監(jiān)視與優(yōu)化實(shí)驗(yàn)

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

1.實(shí)驗(yàn)?zāi)康?/p>

理解數(shù)據(jù)庫(kù)性能概念，練習(xí)數(shù)據(jù)庫(kù)性能監(jiān)視命令方法，能夠?qū)?shù)據(jù)庫(kù)性能進(jìn)

行優(yōu)化。

2.實(shí)驗(yàn)內(nèi)容

【實(shí)驗(yàn)5-1］使用SHOW語(yǔ)句查詢?cè)O(shè)備吞吐量Questions.Com_Select,

幾個(gè)指標(biāo)值。

Comjnsert,Com_updatesCom_delete

Co^wlect1

1TO9isset(0.00MC)

?。踫ao?CUBUSIATVSins~」卬口.

Variable.MBvValue

Cea.iaMrtI0

1rwisset<0.00MC)

?■?!>flDi(1MMSUBSUKE

ari?M?jw▼<!"

CtM.qHat?0

1m1Bwt(*.00Me)

9DVCUMLsonsLIKE"Co^itUte*

Variabl?_MB?Tains

CcH^Atl^te0

1rw;nset300sec)

■

【實(shí)驗(yàn)5-2］訪問(wèn)Performance_schema中的

events_statements_summary_by_digest，獲取有關(guān)的延遲、錯(cuò)誤和查詢量

信息的性能指標(biāo)。

“awl，”

?MB*?*??

?nTAW?-.FL.CA^MFE、IWM

BBMTlOBaj9r..MIIBMW；1■??

?wtwCM"MBBWrITMvrn?o

wv<*1—??

MH*MBW*"|?M?

—[GUUTSEF”M?

4fu■■

—tinnF，”ifBP

?M>

MMTWW'14^AsetT

warW4^*4MVM*w?f>9???

oanTWM?YM^T

laaafumwv^vBflMMTA9v4JMM'WBBttMVWnw<■,

BBB;"4WM▼WVWBT)-riM*?

Mvatnt'nvtawn'?—

ioBA*>'w?tBMMBa|-rwMa

V*OMM*t>■

*RTAJMM*Ii**mti-

MMTWM??AOBMfa^nUMW*?MHMX1C|VM?

?M>aWA^ijaaW.*tL

―1M?r7,1MHM，■****—*七1?”■

■■■?_一?一■■■■?-，

4HWSWM>

SBBf*im*Y■vaoB^W、B—

MVOMJFMMBTE，IS*M>

4MQtWK**?0?

【實(shí)驗(yàn)5-3】使用SHOW語(yǔ)句查詢連接檢查指標(biāo)Threads_connected、

、和

Threads_runningConnection_errors_internalxAborted_connects

Connection_errors_max_connections.

Tkrwdi_ceooect1

1reviosetCOGOMC)

“Xl90*S7A7T5LISE*ThrMds^ramiftf

VariablejwIValw

TkrMds.raalM1

:rE了10003.

B.l，SB*STATESLIKE*Coowet:M_efTcr3_iBterml*:

Vari』l..ne|hlu*|

CCOMCtion.emrs_uiierad0

Iraoini9t(000MC)

SUOSSTATVSLIXIAbcrtWmcomfcti*

Vortftble.DMi|Talw

Abortt4weeaBeet80

IrwLAwc40.00???)

■rsqlSB*STATTSLIXE'CaowetiM_?rrer?.BU.eean?ctleas

|TAIWI

CanKt10C_CTTWYjm.COMKtL(NU0

1rwiaM?*000CM)

vrtql

【實(shí)驗(yàn)5-4］使用SHOW語(yǔ)句查詢Innodb緩沖區(qū)指標(biāo)

Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、

Innodb_buffer_pool_readso

【實(shí)驗(yàn)5-5］使用SHOW語(yǔ)句獲取與查詢緩沖相關(guān)的指標(biāo)：

Qcache_free_blocksxQcache_free_memoryxQcache_hitssQcache_insertsx

Qcache_lowmem_prunesxQcache_not_cachedxQcache_queries_in_cachex

Qcache_total_blockso

(3MooOf5staoa【

0I

I?B-?r^w?A?

F?OU<TB10EA肛sills.iivisns

(At003a?HI

QjBMMat-?qMaO|

?mIJ?A

juMT^nae.amnms46<iWq

(IM8巾)XM?tMlt

0nwFwa^

?34?MF『3A

.W?pv^.3U1wxsMB]X.

(9M00勤3?sE3I

Clflrot**?""z"Fwao

■i?上■mr?wim

,Ajflwaw；riq3wft7iiiV$nivi$\aK

(3M00W1??53I

IBOlQ-jU-KR4

1[■▲■wFbf

:?npo;q_eaij-?pB30.nilmvisSOK?：與,"

.

(?MQQQ)IMO1B0J|l

Hsywri9or*i?jkvOTB[

-0I?mr?HF：j?A

.?p?*「[ood*.nndnstatsd

【實(shí)驗(yàn)5-6］使用SHOW語(yǔ)句獲取關(guān)于臨時(shí)表的指標(biāo)

Created_tmp_disk_tablessCreated_tmp_filessCreated_tmp_tableso

RyD9D>STATESLUKa;

Qcach*.total.blocfci1

Ireola?t(000MC)

LR1>Bmnsun?€m10_—.皿”，

CrMt“_ts|?，ielK.cabl”0

1rwvlaset傅.00MC)

Lrml>9D>STATCSLTU1CXMted.tw^fi

Variable.MMValoe

CrwetS

1rewUset(0.00MC)

LH1>9D>STARSITH?C"?tT3x”;

Eaptyt?t(0.00tee)

卜1^1>SIWSTATVSIIKCieotY.tsp.tablr

Voriable.MBv"1?|

CrwX_up.t^blrf2

IraoI.wt(000MCI

Ctt?，？'____________________________________

【實(shí)驗(yàn)5-7】使用SHOW語(yǔ)句獲取訪問(wèn)表的數(shù)量指標(biāo)Open_tables和

Opened_tableso

【實(shí)驗(yàn)5-8】使用SHOWPROCESSLIST命令查詢用戶正在運(yùn)行的線程信

息協(xié)助進(jìn)行故障診斷。

【實(shí)驗(yàn)5-9］調(diào)出慢查詢?nèi)罩静⒗胢ysqldumpslow來(lái)進(jìn)行日志分析。

【實(shí)驗(yàn)5-10］使用EXPLAINEXTENDED命令杳看帶有UNION子句的

SELECT的執(zhí)行計(jì)劃。

ISEart^l

CO1WB?_pnr

db

■■CiM.ewt

?wat

ftae

gti4_??cvtI

bvlp.cetv9BTY

twlpjwywrri

help.r?1stLoi

b?lp_topic

UMMdb_lAdM.atAtI

iMoA.tatel

ndb_bi0】o<_index

P1?KU)

pree

proctjprir

pruws.priv

?erwr.eMf

Berrvn

slarvjmster_xofo

tlevvjrwlar.I

?l>re_verkar_lua

hml>SSLCCTalFHMtlOTOKSELfiCTa2Httt2:

?rvnlaMI(&00MC>

【實(shí)驗(yàn)5-11］使用EXPLAINEXTENDED命令查看如下語(yǔ)句的執(zhí)行計(jì)劃:

SELECT*FROMtlWHEREal<10AND(

EXISTS(SELECTa2FROMt2WHEREt2.a2<5AND

t2.b2=l)OR

EXISTS(SELECTa2FROMt2WHEREt2.a2<5AND

t2.b2=2));

【實(shí)驗(yàn)5-12】使用EXPLAINEXTENDED命令查看如下語(yǔ)句的執(zhí)行計(jì)劃：

SELECT*FROMtl,(SELECT*FROMt2WHEREt2.a2>10)v_t2

WHEREtl.al<10ANDv_t2.a2<20;

【實(shí)驗(yàn)5-13］創(chuàng)建一個(gè)表，并在適當(dāng)字段上創(chuàng)建索引，對(duì)比在大數(shù)據(jù)量情

形下使用索引與不使用索引的性能。

118,?9a>jl

【實(shí)驗(yàn)5-14】使用PROCEDUREANALYSE檢查表歹山

【實(shí)驗(yàn)5-15］使用Inet_ATON將IP地址轉(zhuǎn)換為數(shù)字，再將

2130706433轉(zhuǎn)換為IP地址。

.E(T江/COS卜，211

iMtjemCIK.1M.1.r)

Iroviowt(000wc)

■TH1>siuniMt_rroi(2iX70M3J(：

UwtJfTMU13070M33?

U7.0ie.||

1mvlaa?t(0.00MC>

"'Rl'

【實(shí)驗(yàn)5-16］進(jìn)行簡(jiǎn)單的關(guān)聯(lián)查詢代替子查詢的重寫(xiě)操作，并驗(yàn)證其正確

性和執(zhí)行效率的變化。

【實(shí)驗(yàn)5-17］查詢SQL的最大連接數(shù)并修改其至合適的數(shù)值。

3.實(shí)驗(yàn)要求

(1)所有操作均在命令行或者M(jìn)ySQLWorkbench中完成。

(2)將操作過(guò)程以屏幕抓圖的方式復(fù)制，形成實(shí)驗(yàn)文檔，并對(duì)照本章內(nèi)容

寫(xiě)出分析報(bào)告。

(3)將操作所使用的命令對(duì)應(yīng)的參數(shù)、參數(shù)含義、返回的內(nèi)容、返回內(nèi)容

的含義整理到分析報(bào)告中一同給出。

數(shù)據(jù)庫(kù)中的兩個(gè)重要對(duì)象是表和索引，在6.3節(jié)的查詢性能優(yōu)化中為了提高

查詢性能，講述了很多關(guān)于索引的應(yīng)用。從本質(zhì)來(lái)講，良好的邏輯設(shè)計(jì)和物理設(shè)

計(jì)(也就是表的設(shè)計(jì))才是高性能的基石，作為數(shù)據(jù)庫(kù)中的基礎(chǔ)對(duì)象，表的設(shè)計(jì)

對(duì)性能的影響也很重要，比如反范式設(shè)計(jì)方法會(huì)提升某些查詢的速度，但同時(shí)也

可能使得另一些杳詢變得很慢，應(yīng)該根據(jù)系統(tǒng)具體執(zhí)行的任務(wù)，以及在應(yīng)用中承

擔(dān)的角色，對(duì)數(shù)據(jù)庫(kù)進(jìn)行整體的設(shè)計(jì)和優(yōu)化，這需要權(quán)衡各種因素的利弊。本節(jié)

將討論關(guān)于表的優(yōu)化。

表需要根據(jù)應(yīng)用來(lái)判斷使用何種數(shù)據(jù)類型。雖然應(yīng)用設(shè)計(jì)的時(shí)候需要考慮字

段的長(zhǎng)度留有一定的冗余，但是不推薦讓很多字段都留有大量的冗余，這樣既浪

費(fèi)存儲(chǔ)也浪費(fèi)內(nèi)存。我們可以使用PROCEDUREANALYSE。對(duì)當(dāng)前已有應(yīng)用

的表類型進(jìn)行判斷，該函數(shù)可以對(duì)數(shù)據(jù)表中的列的數(shù)據(jù)類型提出優(yōu)化建議，可以

根據(jù)應(yīng)用的實(shí)際情況酌情考慮是否實(shí)施優(yōu)化。PROCEDUREANALYSE。的語(yǔ)法

如下：

SELECT...FROM...WHERE...PROCEDUREANALYSE（[max_elements,

[max_memory]]）

max_elements（默認(rèn)值256）為analyze查找每一列不同值時(shí)所需關(guān)注的

最大不同值的數(shù)量analyze還用這個(gè)值來(lái)檢查優(yōu)化的數(shù)據(jù)類型是否為ENUM,

如果該列的不同值的數(shù)量超過(guò)了max_elements值，ENUM就不作為建議優(yōu)化

的數(shù)據(jù)類型。

max_memory（默認(rèn)值8192）為analyze查找每列所有不同值時(shí)可能分配

的最大的內(nèi)存數(shù)量。如果沒(méi)有這樣的限制，輸出信息可能很長(zhǎng)，ENUM定義通

常很難閱讀。在對(duì)字段類型進(jìn)行優(yōu)化時(shí)，可以根據(jù)統(tǒng)計(jì)信息并結(jié)合應(yīng)用的實(shí)際情

況對(duì)其進(jìn)行優(yōu)化。

例

SELECT*FROMtbl_namePROCEDUREANALYSEQ;

上述語(yǔ)句表明輸出的每列信息都會(huì)對(duì)數(shù)據(jù)表中的列的數(shù)據(jù)類型提出優(yōu)化建

議。

SELECT*FROMtbl_namePROCEDUREANALYSE(16,256);

該語(yǔ)句告訴PROCEDUREANALYSE。不要為那些包含的值多于16個(gè)或者

256字節(jié)的ENUM類型提出建議。

下面舉例說(shuō)明如何使用PROCEDUREANALYS0函數(shù)幫助我們優(yōu)化數(shù)據(jù)類

型：

例

mysql>DESCuser_account;

++++++

-+

|Field|Type|Null|Key|Default|Extra|

++++++

-+

|USERID|int(10)unsigned|NO|PRI|NULL|auto_increment|

|USERNAME|varchar(lO)|NO||NULL||

|PASSSWORD|varchar(30)|NO||NULL||

|GROUPNAME|varchar(lO)|YES||NULL||

++++++

-+

4rowsinset(0.00sec)

上面是關(guān)于user_account表結(jié)構(gòu)的查看，下面通過(guò)PROCEDURE

ANALYS0函數(shù)分析:

mysql>select*fromuser_accountPROCEDUREANALYSE(1)\G;

*****************]row*****************

Field_name:ibatis.user_account.USERID

Min_value:1

Max_value:103

Minjength:1

Maxjength:3

Empties_or_zeros:0

Nulls:0

Avg_value_or_avg_length:51.7500

Std:50.2562

Optimal_fieldtype:TINYINT(3)UNSIGNEDNOTNULL

*****************2row*****************

Field_name:ibatis.user_account.USERNAME

Min_value:dfsa

Maxvalue:LMEADORS

從第一行輸出我們可以看到analyze分析ibatis.usejaccount.USERID列

最小值為1,最大值為103,最小長(zhǎng)度為1,最大長(zhǎng)度為3,并給出了該字段的

優(yōu)化建議：將該字段的數(shù)據(jù)類型改成TINYINT（3）UNSIGNEDNOTNULLO

1.數(shù)據(jù)類型選擇的總體原則

更小的數(shù)據(jù)類型通常更好。

-更小的數(shù)據(jù)類型意味著更少的磁盤(pán)空間、內(nèi)存和CPU緩存

而且需要的CPU的周期也更少。

更簡(jiǎn)單的數(shù)據(jù)類型通常更好。

更簡(jiǎn)單的數(shù)據(jù)類型的操作比較簡(jiǎn)單，需要更少的CPU周期

例如，整型就比字符操作代價(jià)低，因?yàn)樽址团判蛞?guī)貝

使字符比較更復(fù)雜。還有使用MySQL內(nèi)建類型（女I

timestamp^data）,而不是使用字符串保存日期數(shù)據(jù),

或者是利用整數(shù)來(lái)保存ipi也址等。

盡量避免NULL值。

如果計(jì)劃對(duì)列進(jìn)行索引，盡量避免把列設(shè)置為NULL,盡石

能把字段定義為NOTNULL,可以放置一個(gè)默認(rèn)值，如''

0,特殊字符串。因?yàn)镸ySQUt以優(yōu)化NULL列，NULL列需空

更多的存儲(chǔ)空間，還需要在MYSQL內(nèi)部進(jìn)行特殊處理，%

對(duì)NULL列加索引，每條記錄都需要一個(gè)額外的字節(jié)，還《

導(dǎo)致MylSAM中固定大小的索引變成可變大小的素弓I。

「注意存儲(chǔ)范圍精度。

很多數(shù)據(jù)類型能夠保存同類型的數(shù)據(jù)，但是我們要發(fā)現(xiàn)，

其在存儲(chǔ)范圍、精度和物理空間之間的差別（磁盤(pán)或正

存空間）。例如，datetime和timestamp能保存同樣類翌

的數(shù)據(jù)日期和時(shí)間，但是timestamp僅使用datetim,

一半的空間并且能夠保存時(shí)區(qū)，同時(shí)還擁有特殊的自討

（更新能力。

2.數(shù)據(jù)類型的使用建議

3.4.1節(jié)中講述了數(shù)據(jù)庫(kù)表支持的數(shù)據(jù)類型，我們?cè)跒榱羞x擇數(shù)據(jù)類型的時(shí)

候，不僅要考慮存儲(chǔ)類型大小，還要考慮MySQL如何對(duì)它們進(jìn)行計(jì)算和比較。

例如，MySQL在內(nèi)部把ENUM和SET類型保存為整數(shù)，但是在比較的時(shí)候把

它們轉(zhuǎn)換為字符串。我們要在相關(guān)表中使用同樣的類型，類型之間要精確匹配,

包括諸如UNSIGNED這樣的屬性?；旌喜煌臄?shù)據(jù)類型會(huì)導(dǎo)致性能問(wèn)題，即使

沒(méi)有性能問(wèn)題，隱式的類型轉(zhuǎn)換也能導(dǎo)致難以察覺(jué)的錯(cuò)誤。選擇最小的數(shù)據(jù)類型

要考慮將來(lái)留出的增長(zhǎng)空間。例如，中國(guó)的省份，我們知道不會(huì)有成千上萬(wàn)個(gè),

因此不必用INT,用TINYINT就足夠了，它比INT小3個(gè)字節(jié)。整數(shù)通常是最

佳的數(shù)據(jù)類型，因?yàn)樗俣瓤?，并且能使用AUTOJNCREMENL要盡可能避

免將字符串作為列的數(shù)據(jù)類型，因?yàn)樗鼈冋加昧撕芏嗫臻g，并且通常比整數(shù)類型

要慢。MylSAM默認(rèn)情況下為字符串使用了壓縮索引，這使得查找更為緩慢。

(1)關(guān)于數(shù)字類型，非萬(wàn)不得已不要使用DOUBLE,這不僅只是存儲(chǔ)長(zhǎng)度

的問(wèn)題，同時(shí)還會(huì)存在精確性的問(wèn)題。同樣，固定精度的小數(shù)也不建議使用

DECIMAL,建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲(chǔ)，可以大大節(jié)省存儲(chǔ)空間，且不

會(huì)帶來(lái)任何附加維護(hù)成本。對(duì)于整數(shù)的存儲(chǔ)，在數(shù)據(jù)量較大的情況下，建議區(qū)分

TINYINT/INT/BIGINT的選擇，因?yàn)槿咚加玫拇鎯?chǔ)空間也有很大的差別，能

確定不會(huì)使用負(fù)數(shù)的字段，建議添加unsigned定義。當(dāng)然，如果是數(shù)據(jù)量較小

的數(shù)據(jù)庫(kù)，也可以不用嚴(yán)格區(qū)分三種整數(shù)類型。

(2)關(guān)于字符型，非萬(wàn)不得已不要使用TEXT數(shù)據(jù)類型，其處理方式?jīng)Q定

了其性能要低于CHAR類型或者是VARCHAR類型的處理。對(duì)于定長(zhǎng)字段，建

議使用CHAR類型，而不定長(zhǎng)字段盡量使用VARCHAR類型，且僅僅設(shè)定適當(dāng)

的最大長(zhǎng)度，而不是非常隨意地給一個(gè)最大長(zhǎng)度的限定，因?yàn)椴煌拈L(zhǎng)度范圍，

MySQL也會(huì)有不一樣的存儲(chǔ)處理。

(3)關(guān)于時(shí)間類型，盡量使用TIMESTAMP類型，因?yàn)槠浯鎯?chǔ)空間只需要

DATETIME類型的一半。對(duì)于只需要精確到某一天的數(shù)據(jù)類型，建議使用DATE

類型，因?yàn)槠浯鎯?chǔ)空間只需要3個(gè)字節(jié)，比TIMESTAMP還少。不建議通過(guò)INT

類型存儲(chǔ)一個(gè)unixtimestamp的值，因?yàn)檫@太不直觀，會(huì)給維護(hù)帶來(lái)不必要

的麻煩，同時(shí)還不會(huì)帶來(lái)任何好處。

(4)對(duì)于狀態(tài)字段，可以嘗試使用ENUM來(lái)存放，因?yàn)榭梢詷O大地減小

存儲(chǔ)空間，而且即使需要增加新的類型，只要增加于末尾，修改結(jié)構(gòu)也不需要重

建表數(shù)據(jù)。如果是存放可預(yù)先定義的屬性數(shù)據(jù)呢？可以嘗試使用SET類型，即

使存在多種屬性，同樣可以游刃有余，同時(shí)還可以節(jié)省不小的存儲(chǔ)空間。

(5)關(guān)于大對(duì)象類型，強(qiáng)烈反對(duì)在數(shù)據(jù)庫(kù)中存放BLOB類型數(shù)據(jù)，雖然數(shù)

據(jù)庫(kù)提供了這樣的功能，但這不是其所擅長(zhǎng)的。

(6)關(guān)聯(lián)查詢時(shí)，兩個(gè)表中關(guān)聯(lián)的字段最好是同一個(gè)數(shù)據(jù)類型。如果沒(méi)有

負(fù)數(shù)，最好是設(shè)置UNDIGNED,這樣既避免出現(xiàn)負(fù)數(shù)的BUG,又使得存儲(chǔ)的數(shù)

據(jù)擴(kuò)大一倍。ENUM和SET類型適合存儲(chǔ)固定信息，如有序的狀態(tài)、產(chǎn)品類型、

性別。對(duì)于完全隨機(jī)的字符串【如MD5()、SHA1()、UUID()],在插入值時(shí)會(huì)

隨機(jī)寫(xiě)入索引的不同位置，所以插入速度慢，還有可能會(huì)導(dǎo)致頁(yè)分裂和磁盤(pán)隨機(jī)

訪問(wèn)，在查詢時(shí)也會(huì)因?yàn)檫壿嬌舷噜彽男蟹植荚诖疟P(pán)和同存的不同的位置而變得

很慢。隨機(jī)值會(huì)弱化查詢語(yǔ)句的緩存作用，因?yàn)樗沟镁彺尜囈怨ぷ鞯脑L問(wèn)局部

性原理失效。在存十六進(jìn)制的UUID值時(shí)，最好移除號(hào)。最好的做法是用

unhex()函數(shù)將其轉(zhuǎn)為16字節(jié)的數(shù)字，并存在一個(gè)binary(16)列中，在檢索時(shí)

可通過(guò)hex()函數(shù)轉(zhuǎn)為十六進(jìn)制格式。IP地址時(shí)實(shí)際是32位的無(wú)符號(hào)整數(shù)，所

以存儲(chǔ)的最好方式是用無(wú)符號(hào)整數(shù)，而不是字符串類型。Inet_ATON()函數(shù)將帶

點(diǎn)的IP轉(zhuǎn)為數(shù)字，而Inet_NTOA()函數(shù)可將數(shù)字轉(zhuǎn)為IP。

例

SELECTInet_ATON('');——>2130706433

SELECTInet_ATON('127.1');——>2130706433

SELECTInet_NTOA(3520061480);——>0

MySQL在5.1版引入的分區(qū)是一種簡(jiǎn)單的水平拆分，用戶需要在建表的時(shí)

候加上分區(qū)參數(shù)，對(duì)應(yīng)用是透明的，無(wú)須修改代碼。

對(duì)用戶來(lái)說(shuō)，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表組成,

實(shí)現(xiàn)分區(qū)的代碼實(shí)際上是通過(guò)對(duì)一組底層表的對(duì)象封裝，但對(duì)SQL層來(lái)說(shuō)是一

個(gè)完全封裝底層的黑盒子。MySQL實(shí)現(xiàn)分區(qū)的方式也意味著索引也是按照分區(qū)

的子表定義，沒(méi)有全局索引。

分區(qū)最適合的場(chǎng)景是數(shù)據(jù)的時(shí)間序列性比較強(qiáng)，則可以按時(shí)間來(lái)分區(qū)，如下

面的例子，查詢時(shí)加上時(shí)間范圍條件效率會(huì)非常高，同時(shí)對(duì)于不需要的歷史數(shù)據(jù)

能很容易地批量刪除。

CREATETABLEmembers(

firstnameVARCHAR(25)NOTNULL,

lastn