数据库运维 数据库性能监视与优化实验
发布时间：2023-07-20 11:07:35浏览次数：44实验 5 数据库性能监视与优化实验1.实验目的理解数据库性能概念，练习数据库性能监视命令方法，能够对数据库性能进行优化。2.实验内容【 实 验 】 使 用 语 句 查 询 设 备 吞 吐 量、、、、 几个指标值。【 实 验 】 访 问 中 的 表，获取有关的延迟、错误和查询量信息的性能指标。【 实 验 ! 】 使 用 语 句 查 询 连 接 检 查 指 标"、" 、、# 和 $。【 实 验 % 】 使 用 语 句 查 询 & 缓 冲 区 指 标&' 、 &'( 、 &'。【 实 验 】 使 用 语 句 获 取 与 查 询 缓 冲 相 关 的 指 标 ：)、、、、*、、(、)。、【 实 验 + 】 使 用 语 句 获 取 关 于 临 时 表 的 指 标)、,、。
【 实 验 - 】 使 用 语 句 获 取 访 问 表 的 数 量 指 标 和。【实验 .】使用 /012&" 命令查询用户正在运行的线程信息协助进行故障诊断。【实验 3】调出慢查询日志并利用 (* 来进行日志分析。【实验 4】使用 152#&6/15"16717 命令查看带有 86&6 子句的 121" 的执行计划。【实验 】使用 152#&6/15"16717 命令查看如下语句的执行计划：121"/9/:0;//101/<4/#67/=////////15&"/=121"//:0;//101/></#67/>?@/0/////////15&"/=121"//:0;//101/></#67/>?@@A【实验 】使用 152#&6/15"16717 命令查看如下语句的执行计划：121"/9/:0;/B/=121"/9/:0;//101/>/C4@// 101/><4/#67/><4A【实验 !】创建一个表，并在适当字段上创建索引，对比在大数据量情形下使用索引与不使用索引的性能。【实验 %】使用 017801/#6#2D1 检查表列。【 实 验 】 使 用 &#"6 将 & 地 址 3>.>> 转 换 为 数 字 ， 再 将!4-4+%!! 转换为 & 地址。【实验 +】进行简单的关联查询代替子查询的重写操作，并验证其正确性和执行效率的变化。【实验 -】查询 2 的最大连接数并修改其至合适的数值。
3.实验要求（）所有操作均在命令行或者 ;2/) 中完成。（）将操作过程以屏幕抓图的方式复制，形成实验文档，并对照本章内容写出分析报告。（!）将操作所使用的命令对应的参数、参数含义、返回的内容、返回内容的含义整理到分析报告中一同给出。数据库中的两个重要对象是表和索引，在 +>! 节的查询性能优化中为了提高查询性能，讲述了很多关于索引的应用。从本质来讲，良好的逻辑设计和物理设计（也就是表的设计）才是高性能的基石，作为数据库中的基础对象，表的设计对性能的影响也很重要，比如反范式设计方法会提升某些查询的速度，但同时也可能使得另一些查询变得很慢，应该根据系统具体执行的任务，以及在应用中承担的角色，对数据库进行整体的设计和优化，这需要权衡各种因素的利弊。本节将讨论关于表的优化。表需要根据应用来判断使用何种数据类型。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪费存储也浪费内存。我们可以使用 PROCEDURE ANALYSE()对当前已有应用的表类型进行判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化 。017801/#6#2D1=@的语法如下：121">>>:0;>>>101>>>017801 / #6#2D1=E$ ，E$FF@$（默认值 +）为 G 查找每一列不同值时所需关注的最大不同值的数量，G 还用这个值来检查优化的数据类型是否为 168;，如果该列的不同值的数量超过了 $ 值，168; 就不作为建议优化的数据类型。
$（默认值 .3）为 G 查找每列所有不同值时可能分配的最大的内存数量。如果没有这样的限制，输出信息可能很长，168;/定义通常很难阅读。在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。例121"/9/:0;//017801/#6#2D1=@A上述语句表明输出的每列信息都会对数据表中的列的数据类型提出优化建议。121"/9/:0;//017801/#6#2D1=+B+@A该语句告诉 017801/#6#2D1=@不要为那些包含的值多于 + 个或者 + 字节的168; 类型提出建议。下面举例说明如何使用 017801/#6#2D=@函数帮助我们优化数据类型：例(C/71/AHIIIIIIJHIIIIHIIHIIIIJHIIIHIIIIIHK/:/K/"/K/6/K/L/K/7/K/1$/KHIIIIIIJHIIIIHIIHIIIIJHIIIHIIIIIHK/810&7/K/=4@/ /K/6/K/0&/K/6822/K//KK/8106#;1/K/=4@/K/6/K/K/6822/K/KK/#07/K/=!4@/K/6/K/K/6822/K/KK/M086#;1/K/=4@/K/D1/K/K/6822/K/KHIIIIIIJHIIIIHIIHIIIIJHIIIHIIIIIH%/*///=4>44/@上面是关于 表结构的查看，下面通过 017801/#6#2D=@函数分析：(C//9///017801/#6#2D1=@NMA99999999999999999/>/*/99999999999999999:O/>>810&7;O/;$O/4!; O/;$ O/!1GO/4
6O/4# O/>-44O/4>+,O/"&6D&6"=!@/86&M617/6"/682299999999999999999/>/*/99999999999999999:O/>>8106#;1;O/;$O/2;1#70从第一行输出我们可以看到 G 分析 >>810&7 列最小值为，最大值为 4!，最小长度为 ，最大长度为 !，并给出了该字段的优化建议：将该字段的数据类型改成 "&6D&6"=!@/86&M617/6"/6822。1.数据类型选择的总体原则2.数据类型的使用建议
!>%> 节中讲述了数据库表支持的数据类型，我们在为列选择数据类型的时候，不仅要考虑存储类型大小，还要考虑 ;2 如何对它们进行计算和比较。例如，;2 在内部把 168; 和 1" 类型保存为整数，但是在比较的时候把它们转换为字符串。我们要在相关表中使用同样的类型，类型之间要精确匹配，包括诸如 86&M617 这样的属性。混合不同的数据类型会导致性能问题，即使没有性能问题，隐式的类型转换也能导致难以察觉的错误 。选择最小的数据类型要考虑将来留出的增长空间。例如，中国的省份，我们知道不会有成千上万个，因此不必用 &6"，用 "&6D&6" 就足够了，它比 &6" 小 ! 个字节。整数通常是最佳的数据类型，因为它速度快，并且能使用 #8"&601;16"。要尽可能避免将字符串作为列的数据类型，因为它们占用了很多空间，并且通常比整数类型要慢。;&#; 默认情况下为字符串使用了压缩索引，这使得查找更为缓慢。（）关于数字类型，非万不得已不要使用 78P21，这不仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数也不建议使用 71&;#2，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分 "&6D&6"Q&6"QP&M&6" 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加 定义。当然，如果是数据量较小的数据库，也可以不用严格区分三种整数类型。（）关于字符型，非万不得已不要使用 "15" 数据类型，其处理方式决定了其性能要低于 #0 类型或者是 R#0#0 类型的处理。对于定长字段，建议使用 #0 类型，而不定长字段尽量使用 R#0#0 类型，且仅仅设定适当的最大长度，而不是非常随意地给一个最大长度的限定，因为不同的长度范围，;2 也会有不一样的存储处理。（!）关于时间类型，尽量使用 "&;1"#; 类型，因为其存储空间只需要 7#"1"&;1类型的一半。对于只需要精确到某一天的数据类型，建议使用 7#"1 类型，因为其存储空间只需要 ! 个字节，比 "&;1"#; 还少。不建议通过 &6" 类型存储一个 $/的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。
（%）对于状态字段，可以尝试使用 168; 来存放，因为可以极大地减小存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用 1" 类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。（）关于大对象类型，强烈反对在数据库中存放 P2P 类型数据，虽然数据库提供了这样的功能，但这不是其所擅长的。（+）关联查询时，两个表中关联的字段最好是同一个数据类型。如果没有负数，最好是设置 867&M617，这样既避免出现负数的 P8M，又使得存储的数据扩大一倍。168;和 1" 类型适合存储固定信息，如有序的状态、产品类型、性别。对于完全随机的字符串【如 ;7=@、#=@、88&7=@】，在插入值时会随机写入索引的不同位置，所以插入速度慢，还有可能会导致页分裂和磁盘随机访问，在查询时也会因为逻辑上相邻的行分布在磁盘和同存的不同的位置而变得很慢。随机值会弱化查询语句的缓存作用，因为它使得缓存赖以工作的访问局部性原理失效。在存十六进制的 88&7 值时，最好移除“S号。最好的做法是用 $=@函数将其转为 + 字节的数字，并存在一个 =+@列中，在检索时可通过$=@函数转为十六进制格式。& 地址时实际是 ! 位的无符号整数，所以存储的最好方式是用 无符号整 数 ， 而 不 是 字 符 串 类 型 。 &#"6=@函数将带 点的 & 转为数字，而&6"#=@函数可将数字转为 &。例121"/&#"6=T->4>4>U@A/IIC!4-4+%!!121"/&#"6=T->U@A/IIC!4-4+%!!121"/&6"#=!44+%.4@A/IIC43>4->%>%4;2 在 > 版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的，无须修改代码。
对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对 2 层来说是一个完全封装底层的黑盒子。;2 实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。分区最适合的场景是数据的时间序列性比较强，则可以按时间来分区，如下面的例子，查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容易地批量删除。01#"1/"#P21//=////,/R#0#0=@/6"/6822B/////R#0#0=@/6"/6822B/////R#0#0=+@/6"/6822B/////R#0#0=!@B////V/7#"1/6"/6822@////#0"&"&6/PD/0#6M1=/D1#0=V@/@/////=#0"&"&6/4/R#281/21/"#6/=3-4@B////#0"&"&6//R#281/21/"#6/=3.4@B////#0"&"&6//R#281/21/"#6/=334@B////#0"&"&6/!/R#281/21/"#6/=444@B////#0"&"&6/%/R#281/21/"#6/;#5R#281@A如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问，那么可以将热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，就能够有效使用索引和缓存；分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据，此外，还可以对一个独立分区进行优化、检查、修复等操作；部分查询能够从查询条件确定只落在少数分区上，速度会很快；可以使用分区表来避免某些特殊瓶颈，如 &7P 单个索引的互斥访问；可以备份和恢复单个分区;2 有一种早期的简单的分区实现合并表（; /"），其限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代。
用户的 2 语句是需要针对分区表做优化，2 条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过 152#&6/#0"&"&6 来查看某条 2 语句会落在哪些分区上，从而进行 2 优化，下面例子中可以看出，有 条记录落在两个分区上。(C/$///=@///*///=BB!B%B@AHHHHHHHHHHHHK//K//K//K//K//K/)/K/)/K/)K//K/*/K/1$/KHHHHHHHHHHHHKK&;21KKB%K K0&;#0DK0&;#0DK.K6822KK8 *A/$KHHHHHHHHHHHH/*///=4>44/@（）垂直拆分，即把主码和一些列放入一个表，然后把主码和另外的列放到另一个表中。垂直拆分只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，每个表中的数据记录数在一般情况下是相同的，只是字段不一样，其使用主键关联，经常访问的字段尽量是定长的，这样可以有效地提高表的查询和更新的效率。
