数据库性能监视与优化实验

实验5数据库性能监视与优化实验

1.实验目的

理解数据库性能概念，练习数据库性能监视命令方法，能够对数据库性能进

行优化。

2.实验内容

【实验5-1］使用SHOW语句查询设备吞吐量Questions.Com_Select,

几个指标值。

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，获取有关的延迟、错误和查询量

信息的性能指标。

“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语句查询连接检查指标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缓冲区指标

Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、

Innodb_buffer_pool_readso

【实验5-5］使用SHOW语句获取与查询缓冲相关的指标：

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语句获取关于临时表的指标

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语句获取访问表的数量指标Open_tables和

Opened_tableso

【实验5-8】使用SHOWPROCESSLIST命令查询用户正在运行的线程信

息协助进行故障诊断。

【实验5-9］调出慢查询日志并利用mysqldumpslow来进行日志分析。

【实验5-10］使用EXPLAINEXTENDED命令杳看带有UNION子句的

SELECT的执行计划。

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命令查看如下语句的执行计划:

SELECT*FROMtlWHEREal<10AND(

EXISTS(SELECTa2FROMt2WHEREt2.a2<5AND

t2.b2=l)OR

EXISTS(SELECTa2FROMt2WHEREt2.a2<5AND

t2.b2=2));

【实验5-12】使用EXPLAINEXTENDED命令查看如下语句的执行计划：

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

WHEREtl.al<10ANDv_t2.a2<20;

【实验5-13］创建一个表，并在适当字段上创建索引，对比在大数据量情

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

118,«9a>jl

【实验5-14】使用PROCEDUREANALYSE检查表歹山

【实验5-15］使用Inet_ATON将IP地址转换为数字，再将

2130706433转换为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］进行简单的关联查询代替子查询的重写操作，并验证其正确

性和执行效率的变化。

【实验5-17］查询SQL的最大连接数并修改其至合适的数值。

3.实验要求

(1)所有操作均在命令行或者MySQLWorkbench中完成。

(2)将操作过程以屏幕抓图的方式复制，形成实验文档，并对照本章内容

写出分析报告。

(3)将操作所使用的命令对应的参数、参数含义、返回的内容、返回内容

的含义整理到分析报告中一同给出。

数据库中的两个重要对象是表和索引，在6.3节的查询性能优化中为了提高

查询性能，讲述了很多关于索引的应用。从本质来讲，良好的逻辑设计和物理设

计(也就是表的设计)才是高性能的基石，作为数据库中的基础对象，表的设计

对性能的影响也很重要，比如反范式设计方法会提升某些查询的速度，但同时也

可能使得另一些杳询变得很慢，应该根据系统具体执行的任务，以及在应用中承

担的角色，对数据库进行整体的设计和优化，这需要权衡各种因素的利弊。本节

将讨论关于表的优化。

表需要根据应用来判断使用何种数据类型。虽然应用设计的时候需要考虑字

段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪

费存储也浪费内存。我们可以使用PROCEDUREANALYSE。对当前已有应用

的表类型进行判断，该函数可以对数据表中的列的数据类型提出优化建议，可以

根据应用的实际情况酌情考虑是否实施优化。PROCEDUREANALYSE。的语法

如下：

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

[max_memory]]）

max_elements（默认值256）为analyze查找每一列不同值时所需关注的

最大不同值的数量analyze还用这个值来检查优化的数据类型是否为ENUM,

如果该列的不同值的数量超过了max_elements值，ENUM就不作为建议优化

的数据类型。

max_memory（默认值8192）为analyze查找每列所有不同值时可能分配

的最大的内存数量。如果没有这样的限制，输出信息可能很长，ENUM定义通

常很难阅读。在对字段类型进行优化时，可以根据统计信息并结合应用的实际情

况对其进行优化。

例

SELECT*FROMtbl_namePROCEDUREANALYSEQ;

上述语句表明输出的每列信息都会对数据表中的列的数据类型提出优化建

议。

SELECT*FROMtbl_namePROCEDUREANALYSE(16,256);

该语句告诉PROCEDUREANALYSE。不要为那些包含的值多于16个或者

256字节的ENUM类型提出建议。

下面举例说明如何使用PROCEDUREANALYS0函数帮助我们优化数据类

型：

例

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)

上面是关于user_account表结构的查看，下面通过PROCEDURE

ANALYS0函数分析:

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,并给出了该字段的

优化建议：将该字段的数据类型改成TINYINT（3）UNSIGNEDNOTNULLO

1.数据类型选择的总体原则

更小的数据类型通常更好。

-更小的数据类型意味着更少的磁盘空间、内存和CPU缓存

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

更简单的数据类型通常更好。

更简单的数据类型的操作比较简单，需要更少的CPU周期

例如，整型就比字符操作代价低，因为字符集和排序规贝

使字符比较更复杂。还有使用MySQL内建类型（女I

timestamp^data）,而不是使用字符串保存日期数据,

或者是利用整数来保存ipi也址等。

尽量避免NULL值。

如果计划对列进行索引，尽量避免把列设置为NULL,尽石

能把字段定义为NOTNULL,可以放置一个默认值，如''

0,特殊字符串。因为MySQUt以优化NULL列，NULL列需空

更多的存储空间，还需要在MYSQL内部进行特殊处理，%

对NULL列加索引，每条记录都需要一个额外的字节，还《

导致MylSAM中固定大小的索引变成可变大小的素弓I。

「注意存储范围精度。

很多数据类型能够保存同类型的数据，但是我们要发现，

其在存储范围、精度和物理空间之间的差别（磁盘或正

存空间）。例如，datetime和timestamp能保存同样类翌

的数据日期和时间，但是timestamp仅使用datetim,

一半的空间并且能够保存时区，同时还拥有特殊的自讨

（更新能力。

2.数据类型的使用建议

3.4.1节中讲述了数据库表支持的数据类型，我们在为列选择数据类型的时

候，不仅要考虑存储类型大小，还要考虑MySQL如何对它们进行计算和比较。

例如，MySQL在内部把ENUM和SET类型保存为整数，但是在比较的时候把

它们转换为字符串。我们要在相关表中使用同样的类型，类型之间要精确匹配,

包括诸如UNSIGNED这样的属性。混合不同的数据类型会导致性能问题，即使

没有性能问题，隐式的类型转换也能导致难以察觉的错误。选择最小的数据类型

要考虑将来留出的增长空间。例如，中国的省份，我们知道不会有成千上万个,

因此不必用INT,用TINYINT就足够了，它比INT小3个字节。整数通常是最

佳的数据类型，因为它速度快，并且能使用AUTOJNCREMENL要尽可能避

免将字符串作为列的数据类型，因为它们占用了很多空间，并且通常比整数类型

要慢。MylSAM默认情况下为字符串使用了压缩索引，这使得查找更为缓慢。

(1)关于数字类型，非万不得已不要使用DOUBLE,这不仅只是存储长度

的问题，同时还会存在精确性的问题。同样，固定精度的小数也不建议使用

DECIMAL,建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不

会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分

TINYINT/INT/BIGINT的选择，因为三者所占用的存储空间也有很大的差别，能

确定不会使用负数的字段，建议添加unsigned定义。当然，如果是数据量较小

的数据库，也可以不用严格区分三种整数类型。

(2)关于字符型，非万不得已不要使用TEXT数据类型，其处理方式决定

了其性能要低于CHAR类型或者是VARCHAR类型的处理。对于定长字段，建

议使用CHAR类型，而不定长字段尽量使用VARCHAR类型，且仅仅设定适当

的最大长度，而不是非常随意地给一个最大长度的限定，因为不同的长度范围，

MySQL也会有不一样的存储处理。

(3)关于时间类型，尽量使用TIMESTAMP类型，因为其存储空间只需要

DATETIME类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE

类型，因为其存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT

类型存储一个unixtimestamp的值，因为这太不直观，会给维护带来不必要

的麻烦，同时还不会带来任何好处。

(4)对于状态字段，可以尝试使用ENUM来存放，因为可以极大地减小

存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重

建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即

使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。

(5)关于大对象类型，强烈反对在数据库中存放BLOB类型数据，虽然数

据库提供了这样的功能，但这不是其所擅长的。

(6)关联查询时，两个表中关联的字段最好是同一个数据类型。如果没有

负数，最好是设置UNDIGNED,这样既避免出现负数的BUG,又使得存储的数

据扩大一倍。ENUM和SET类型适合存储固定信息，如有序的状态、产品类型、

性别。对于完全随机的字符串【如MD5()、SHA1()、UUID()],在插入值时会

随机写入索引的不同位置，所以插入速度慢，还有可能会导致页分裂和磁盘随机

访问，在查询时也会因为逻辑上相邻的行分布在磁盘和同存的不同的位置而变得

很慢。随机值会弱化查询语句的缓存作用，因为它使得缓存赖以工作的访问局部

性原理失效。在存十六进制的UUID值时，最好移除号。最好的做法是用

unhex()函数将其转为16字节的数字，并存在一个binary(16)列中，在检索时

可通过hex()函数转为十六进制格式。IP地址时实际是32位的无符号整数，所

以存储的最好方式是用无符号整数，而不是字符串类型。Inet_ATON()函数将带

点的IP转为数字，而Inet_NTOA()函数可将数字转为IP。

例

SELECTInet_ATON('');——>2130706433

SELECTInet_ATON('127.1');——>2130706433

SELECTInet_NTOA(3520061480);——>0

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时

候加上分区参数，对应用是透明的，无须修改代码。

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成,

实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一

个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区

的子表定义，没有全局索引。

分区最适合的场景是数据的时间序列性比较强，则可以按时间来分区，如下

面的例子，查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据

能很容易地批量删除。

CREATETABLEmembers(

firstnameVARCHAR(25)NOTNULL,

lastn