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

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

1.實驗?zāi)康?/p>

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

行優(yōu)化。

2.實驗內(nèi)容

【實驗5-1］使用SHOW語句查詢設(shè)備吞吐量Questions.Com_Select,

幾個指標(biāo)值。

Comjnsert,Com_updatesCom_delete

Co^wlect1

1TO9isset(0.00MC)

丁［sao?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)

■

【實驗5-2］訪問Performance_schema中的

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

信息的性能指標(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?

【實驗5-3】使用SHOW語句查詢連接檢查指標(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

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

Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、

Innodb_buffer_pool_readso

【實驗5-5］使用SHOW語句獲取與查詢緩沖相關(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

【實驗5-6］使用SHOW語句獲取關(guān)于臨時表的指標(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?，？'____________________________________

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

Opened_tableso

【實驗5-8】使用SHOWPROCESSLIST命令查詢用戶正在運行的線程信

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

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

【實驗5-10］使用EXPLAINEXTENDED命令杳看帶有UNION子句的

SELECT的執(zhí)行計劃。

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>

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

SELECT*FROMtlWHEREal<10AND(

EXISTS(SELECTa2FROMt2WHEREt2.a2<5AND

t2.b2=l)OR

EXISTS(SELECTa2FROMt2WHEREt2.a2<5AND

t2.b2=2));

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

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

WHEREtl.al<10ANDv_t2.a2<20;

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

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

118,?9a>jl

【實驗5-14】使用PROCEDUREANALYSE檢查表歹山

【實驗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'

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

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

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

3.實驗要求

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

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

寫出分析報告。

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

的含義整理到分析報告中一同給出。

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

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

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

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

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

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

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

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

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

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

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

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

如下：

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

[max_memory]]）

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

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

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

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

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

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

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

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

例

SELECT*FROMtbl_namePROCEDUREANALYSEQ;

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

議。

SELECT*FROMtbl_namePROCEDUREANALYSE(16,256);

該語句告訴PROCEDUREANALYSE。不要為那些包含的值多于16個或者

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

下面舉例說明如何使用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)的查看，下面通過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,最小長度為1,最大長度為3,并給出了該字段的

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

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

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

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

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

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

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

例如，整型就比字符操作代價低，因為字符集和排序規(guī)貝

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

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

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

盡量避免NULL值。

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

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

0,特殊字符串。因為MySQUt以優(yōu)化NULL列，NULL列需空

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

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

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

「注意存儲范圍精度。

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

其在存儲范圍、精度和物理空間之間的差別（磁盤或正

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

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

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

（更新能力。

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

3.4.1節(jié)中講述了數(shù)據(jù)庫表支持的數(shù)據(jù)類型，我們在為列選擇數(shù)據(jù)類型的時

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

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

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

包括諸如UNSIGNED這樣的屬性。混合不同的數(shù)據(jù)類型會導(dǎo)致性能問題，即使

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

要考慮將來留出的增長空間。例如，中國的省份，我們知道不會有成千上萬個,

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

佳的數(shù)據(jù)類型，因為它速度快，并且能使用AUTOJNCREMENL要盡可能避

免將字符串作為列的數(shù)據(jù)類型，因為它們占用了很多空間，并且通常比整數(shù)類型

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

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

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

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

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

TINYINT/INT/BIGINT的選擇，因為三者所占用的存儲空間也有很大的差別，能

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

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

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

了其性能要低于CHAR類型或者是VARCHAR類型的處理。對于定長字段，建

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

的最大長度，而不是非常隨意地給一個最大長度的限定，因為不同的長度范圍，

MySQL也會有不一樣的存儲處理。

(3)關(guān)于時間類型，盡量使用TIMESTAMP類型，因為其存儲空間只需要

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

類型，因為其存儲空間只需要3個字節(jié)，比TIMESTAMP還少。不建議通過INT

類型存儲一個unixtimestamp的值，因為這太不直觀，會給維護(hù)帶來不必要

的麻煩，同時還不會帶來任何好處。

(4)對于狀態(tài)字段，可以嘗試使用ENUM來存放，因為可以極大地減小

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

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

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

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

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

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

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

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

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

隨機寫入索引的不同位置，所以插入速度慢，還有可能會導(dǎo)致頁分裂和磁盤隨機

訪問，在查詢時也會因為邏輯上相鄰的行分布在磁盤和同存的不同的位置而變得

很慢。隨機值會弱化查詢語句的緩存作用，因為它使得緩存賴以工作的訪問局部

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

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

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

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

點的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ū)是一種簡單的水平拆分，用戶需要在建表的時

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

對用戶來說，分區(qū)表是一個獨立的邏輯表，但是底層由多個物理子表組成,

實現(xiàn)分區(qū)的代碼實際上是通過對一組底層表的對象封裝，但對SQL層來說是一

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

的子表定義，沒有全局索引。

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

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

能很容易地批量刪除。

CREATETABLEmembers(

firstnameVARCHAR(25)NOTNULL,

lastn