目录
前言
一个项目的运行，总伴随着性能问题，数据库的性能问题不容忽视，并且随着业务量的与日俱增，性能配置也需要持续跟踪和调整。
数据库性能优化的目标
根据角色的不同，数据库优化分为以下几个目标：
- 业务角度（用户）：减少用户页面响应时间。
- 数据库角度（开发）：减少数据库 SQL 响应时间。
- 数据库服务器角度（运维）：充分使用数据库服务器物理资源减少数据库服务器 CPU 使用率，减少数据库服务器 IO 使用率，减少数据库服务器内存使用率。
数据库优化指标如下：
- SQL 平均响应时间变短。
- 数据库服务器 CPU 占用率变少。
- 数据库服务器 IO 使用率变低。
1.操作系统内存性能诊断
Linux 内核给每一个进程都提供了一个独立的虚拟地址空间，并且这个地址空间是连续的。这样，进程就可以很方便地访问内存，也就是虚拟内存。本文介绍常见内存性能分析命令，以及内存性能分析方法。
1.1Linux 内存性能分析常用命令
- top
top 命令经常用来监控 Linux 的系统状况，比如 CPU、内存的使用情况，如下图所示：
Copy图中第四行显示为系统内存状态
total： 物理内存总量
used： 使用中的内存总量
free： 空闲内存总量
buffers： 缓存的内存量
图中第五行显示 swap 交换分区
total： 交换区总量
used： 使用的交换区总量
free： 空闲交换区总量
cached： 缓冲的交换区总量
- vmstat
vmstat 是 Virtual Meomory Statistics（虚拟内存统计）的缩写，可对操作系统的虚拟内存、进程、IO 读写、CPU 活动等进行监视，如下图所示：
Copymemory 部分
swpd：切换到内存交换区的内存大小；
free：当前空闲的物理内存；
buff：baffers cached 内存大小；
swap：
si：内存进入内存交换区的内存大小；
so：由内存进入磁盘，也就是由内存交换区进入内存的内存大小；
1.2 Linux 内存性能诊断
这里我们使用 vmstat 来进行分析，如下所示：
Copy[root@dm8 ~]# vmstat 1 10
Copyprocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 2273472 32968 701004 0 0 458 128 165 154 0 2 98 0 0
0 0 0 2273748 32968 701004 0 0 0 36 248 253 0 1 99 0 0
0 0 0 2273812 32976 701004 0 0 0 128 673 530 0 2 98 0 0
1 0 0 2274360 32976 701020 0 0 0 4 594 439 0 2 98 0 0
0 0 0 2274728 32976 701024 0 0 0 12 279 256 0 1 99 0 0
0 0 0 2274684 32976 701024 0 0 0 4 106 191 0 0 100 0 0
0 0 0 2274684 32976 701024 0 0 0 8 156 203 0 1 100 0 0
0 0 0 2273832 32984 701016 0 0 0 80 649 531 1 2 98 0 0
0 0 0 2274052 32984 701056 0 0 0 8 491 431 0 1 98 0 0
0 0 0 2274336 32984 701060 0 0 0 4 107 192 0 0 100 0 0
-
MEMORY
- swap：切换到交换内存上的内存（默认以 KB 为单位）。如果 SWAP 的值不为 0，或者还比较大，比如超过 100 MB 了，但是 SI 和 SO 的值长期为 0，这种情况我们可以不用担心，不会影响系统性能。
- free：空闲的物理内存。
- buff：作为 buffer cache 的内存，对块设备的读写进行缓冲。
- cache：作为 page cache 的内存，文件系统的 cache 如果 cache 的值大的时候，说明 cache 处的文件数多，如果频繁访问到的文件都能被 cache，那么磁盘的读 IO bi 会非常小。
-
SWAP
- si：交换内存使用，由磁盘调入内存。
- so：交换内存使用，由内存调入磁盘。
注意内存够用的时候，这 2 个值都是 0，如果这 2 个值长期大于 0 时，系统性能会受到影响，磁盘 IO 和 CPU 资源都会被消耗。
空闲内存 (FREE) 很少的或接近于 0 时，并不能认定为内存不够用，Linux 是抢占内存的 OS，还要结合 si 和 so；如果 free 很少，si 和 so 也很少（大多时候是 0），那么不用担心，系统性能这时不会受到影响的。
2. 操作系统 CPU 性能诊断
CPU 是计算机系统的大脑，这个重要性不可置否。在性能优化的过程中，CPU 的性能指标是很重要的。本文介绍 CPU 性能分析命令，以及 CPU 性能分析方法。
2.1 CPU 性能分析命令
- top
top 命令经常用来监控 Linux 的系统状况，比如 CPU、内存的使用，如下图所示：
第三行 CPU 信息统计数据如下所示：
CopyCpu(s)：
0.0% us： 用户空间占用 CPU 百分比。
0.3% sy： 内核（系统）空间占用 CPU 百分比。
0.0% ni： 用户进程空间内改变过优先级的进程占用 CPU 百分比。
99.7% id： 空闲 CPU 百分比。
0.0% wa： 等待输入输出的 CPU 时间百分比。
0.0%hi： 硬件 CPU 中断占用百分比。
0.0%si： 软中断占用百分比。
0.0%st： 虚拟机（虚拟化技术）占用百分比。
- vmstat
vmstat 是 Virtual Meomory Statistics（虚拟内存统计）的缩写，可对操作系统的虚拟内存、进程、IO 读写、CPU 活动等进行监视，如下图所示：
CPU 以百分比显示如下所示：
Copyus(user time)：用户进程执行时间。
sy(system time)：系统进程执行时间。
id：空闲时间（包括 IO 等待时间）。
wa：等待 IO 时间，wa 的值高时，说明 IO 等待比较严重，这可能由于磁盘大量做随机访问造成的，也有可能是磁盘出现瓶颈。
st：表示被偷走的 CPU 所占百分比（一般都为 0，不用关注）。
us + sy + id + wa =100% 近似为 100%
2.2 CPU 性能诊断
使用 vmstat 查看系统的 CPU 整体运行状况，如下所示：
Copy[root@dm8 ~]# vmstat 1 10
Copyprocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 2273472 32968 701004 0 0 458 128 165 154 0 2 98 0 0
0 0 0 2273748 32968 701004 0 0 0 36 248 253 0 1 99 0 0
0 0 0 2273812 32976 701004 0 0 0 128 673 530 0 2 98 0 0
1 0 0 2274360 32976 701020 0 0 0 4 594 439 0 2 98 0 0
0 0 0 2274728 32976 701024 0 0 0 12 279 256 0 1 99 0 0
0 0 0 2274684 32976 701024 0 0 0 4 106 191 0 0 100 0 0
0 0 0 2274684 32976 701024 0 0 0 8 156 203 0 1 100 0 0
0 0 0 2273832 32984 701016 0 0 0 80 649 531 1 2 98 0 0
0 0 0 2274052 32984 701056 0 0 0 8 491 431 0 1 98 0 0
0 0 0 2274336 32984 701060 0 0 0 4 107 192 0 0 100 0 0
指标解读如下所示：
r：如果在 procs 中运行的序列 (process r) 是连续的大于在系统中的 CPU 的个数，表示 CPU 比较忙，系统现在运行比较慢，有多数的进程等待 CPU。如果 r 的输出数大于系统中可用 CPU 个数的 4 倍的话，则系统面临着 CPU 短缺的问题，或者是 CPU 的速率过低，系统中有多数的进程在等待 CPU，造成系统中进程运行过慢。
b：如果在 procs 中运行的序列 (process b)，即处于不可中断状态的进程数，连续为 CPU 的 2~3 倍就表明 CPU 排队比较严重了。
- SYSTEM
in：每秒产生的中断次数。
cs：每秒产生的上下文切换次数。
in 和 cs 这两个值越大，会看到由内核消耗的 CPU 时间会越大。
- CPU
us：用户进程消耗的 CPU 时间百分。us 的值比较高时，说明用户进程消耗的 CPU 时间多，在服务高峰期持续大于 50~60，是可以接受，但是如果长期超 50% ，那么我们就该考虑优化程序算法。
sy：内核进程消耗的 CPU 时间百分比。sy 的值高时，说明系统内核消耗的 CPU 资源多，这并不是良性表现，我们应该检查原因。
wa：IO 等待消耗的CPU时间百分比。wa 的值高时，说明 IO 等待比较严重，这可能由于磁盘大量作随机访问造成，也有可能磁盘出现瓶颈（块操作）。
id：CPU 处于空闲状态时间百分比，如果空闲时间 (cpu id) 持续为 0 并且系统时间 (cpu sy) 是用户时间的两倍 (cpu us) 系统则面临着 CPU 资源的短缺，在服务高峰期持续小于 50，可以接受。
通过以下方式可以判断 CPU 性能：
- 如果 r 连续大于 CPU 的个数，甚至几倍 CPU 个数；b 也有持续有值，甚至 CPU 的 2~3 倍，并且 id 也持续小于 50%，wa 也比较小，这就表明 CPU 负荷很严重。
- 再详细确认用：
sar -u 1 5，sar -q 1 5，可以观察 CPU 的使用率和 CPU 运行进程队列长度及负载。 - 查看具体是什么进程在消耗 CPU，就要使用命令：
top，ps –auxw | more。 - 知道了某个进程消耗大量的 CPU，想知道这个进程在做成什么，那就用命令：
strace。
3.磁盘 I/O 性能诊断
我们在 Linux 服务器排查问题时，一般会通过 top、vmstat、free、netstat、df -h 等命令排查 CPU、内存、网络和磁盘等问题。有的时候我们需要更进一步了解磁盘 I/O 的使用情况。本文介绍常用 I/O 性能分析命令和性能诊断方法。
I/O 性能分析命令
iostat
iostat 是 Linux 最常见的磁盘 I/O 监控工具。
- 基本用法
Copy$iostat -d -x -k 1 10
-d 表示：显示设备（磁盘）使用状态。
-k 表示：某些使用 block 为单位的列强制使用 Kilobytes 为单位。
1 10 表示：数据显示每隔 1 秒刷新一次，共显示 10 次。
-x 参数：我们可以获得更多统计信息。
- 参数含义
rrqm/s：每秒进行 merge（多个 IO 的合并）读操作的数量。
wrqm/s：每秒进行 merge（多个 IO 的合并）写操作的数量。
rsec/s：每秒读取的扇区数。
wsec/s：每秒写入的扇区数。
rKB/s：每秒读多少k字节，在 kernel 2.4 以上，rkB/s=2×rsec/s，因为一个扇区为 512 bytes。
wKB/s：每秒写多少k字节，在 kernel 2.4 以上，wkB/s=2×wsec/s，因为一个扇区为 512 bytes。
avgrq-sz：平均请求扇区的大小。
avgqu-sz：是平均请求队列的长度。毫无疑问，队列长度越短越好。
await：每一个 IO 请求的处理的平均时间（单位是微秒毫秒）。这里可以理解为 IO 的响应时间，一般地系统 IO 响应时间应该低于 5 ms，如果大于 10 ms 就比较大了。这个时间包括了队列时间和服务时间，也就是说，一般情况下，await 大于 svctm，它们的差值越小，则说明队列时间越短，反之差值越大，队列时间越长，说明系统出了问题。
svctm：表示平均每次设备 I/O 操作的服务时间（以毫秒为单位）。如果 svctm 的值与 await 很接近，表示几乎没有 I/O 等待，磁盘性能很好，如果 await 的值远高于 svctm 的值，则表示 I/O 队列等待太长，系统上运行的应用程序将变慢。
%util：在统计时间内所有处理 IO 时间，除以总共统计时间，该参数暗示了设备的繁忙程度，如果该参数是 100% 表示设备已经接近满负荷运行了（当然如果是多磁盘，即使 %util 是 100%，因为磁盘的并发能力，所以磁盘使用未必就到了瓶颈）。
iotop
iotop 是一个用 python 编写的类似 top 界面的磁盘 I/O 监控工具。
- 基本用法
Copyiotop
- 参数含义
DISK READ 和 DISK WRITE 字段：代表块设备在采样时间内的 I/O 带宽。
SWAPIN 和 IO 字段：表示当前进程或线程花费在页面换入和等待 I/O 的时间。
PRIO 字段：表示 I/O 优先级。
Total DISK READ 和 Total DISK WRITE 字段：表示总的 I/O 读写情况。
- 常用参数
Copy\-o --only 只显示实际具有 I/O 操作的进程或线程。可以通过快捷键 o 进行控制
\-b --batch 非交互式模式，可以用于保存输出结果
\-n 刷新次数
\-d 刷新间隔时间
\-P 只显示进程，不显示线程
\-p 监控指定进程或线程
\-k 使用 KB 表示 I/O 带宽。默认情况下，iotop 使用 B/s，K/s，M/s 表示 I/O 带宽。
\-u 监控指定用户的 I/O 操作情况
\-t 在每行输出加上时间戳
\-q 只在第一次输出时显示列名称
\-qq 不显示列名称
\-qqq 不显示总的 I/O 信息
I/O 性能诊断
用 vmstat 命令了解系统状况
Copy[root@dm8 ~]# vmstat 1 10
Copyprocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 2074352 93616 732548 0 0 41 25 77 82 0 1 99 0 0
0 0 0 2074600 93616 732548 0 0 0 8 128 194 0 0 100 0 0
0 0 0 2074432 93616 732548 0 0 0 8 193 234 0 1 99 0 0
0 0 0 2074416 93616 732548 0 0 0 8 121 194 0 0 100 0 0
0 0 0 2074424 93616 732548 0 0 0 24 136 194 0 0 100 0 0
0 0 0 2074700 93624 732540 0 0 0 80 771 721 0 2 98 0 0
0 0 0 2074796 93624 732552 0 0 0 4 100 180 0 0 100 0 0
0 0 0 2074672 93624 732552 0 0 0 8 107 179 0 0 100 0 0
0 0 0 2074860 93624 732552 0 0 0 8 164 224 0 0 100 0 0
0 0 0 2074596 93624 732552 0 0 0 8 114 187 0 0 100 0 0
如果 b 的值为 2~3 倍 CPU 数量，bi 和 bo 的值很大（有时 bi 和 bo 值很小，但 in 和 cs 很大，也会引起磁盘 IO 负载重），wa 的值持续很高，如高于 40，id 也持续高于 70，这些现象都表明系统的 IO 可能出现性能问题。可以进一步通过 iostat 命令分析，如下所示：
Copy[root@dm8 ~]# iostat -x 1 5
Linux 2.6.32-642.kb5.ky3.x86_64 (dm8) 2020年11月02日_x86_64_ (4 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.09 0.00 0.72 0.03 0.00 99.17
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 3.04 19.26 3.90 4.33 312.06 188.78 60.85 0.02 2.07 0.36 3.61 1.70 1.40
dm-0 0.00 0.00 6.67 23.60 309.97 188.77 16.48 0.08 2.64 0.54 3.24 0.46 1.40
dm-1 0.00 0.00 0.07 0.00 0.57 0.00 8.00 0.00 0.22 0.22 0.00 0.13 0.00
查看 iostat 的结果时注意事项如下：
- 首先看 %util（服务 IO 的时间占总时间的百分比），如果这个值接近 100%，表示 IO 的请求很多（表示任务服务的所有时间几乎都用在 IO 上），这种现象表明磁盘 IO 性能出现瓶颈。
- 再看 await（表示每次io设备等待时间）和 svctm（表示每次 IO 设备服务时间，一般性能越好的磁盘，这个值越小）。
如果 svctm 接近 await ，说明 IO 几乎没有等待，每个 IO 设备都得到及时的响应。
如果 svctm 远小于 await ，说明 IO 等待队列可能很长，IO 的得到服务的时间将延长（排队+服务时间）。
- avgqu-sz：表示 IO 排队的现象，如果排队过长会影响 IO 的响应时间。
- r/s+w/s：可以计算当前系统的 iops（可以结合硬盘的测试或者硬件参数来衡量是否超过磁盘的 iops 最大值）。
通过 iostat 了解到如果磁盘 I/O 出现性能瓶颈，我们可以借助 pidstat ，定位出导致瓶颈的进程，分析进程的 I/O 行为，结合应用程序的原理，分析这些 I/O 的来源。
4.数据库软件性能优化
对于数据库软件方向的性能分析，可通过动态视图+SQL 日志+JDBC 驱动日志来进行性能问题定位。
查询活动会话数，语句如下所示（eg：已执行超过 2 秒的活动 SQL）：
SELECT* FROM ( SELECT SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_RECV_TIME,SYSDATE) Y_EXETIME, SF_GET_SESSION_SQL(SESS_ID) FULLSQL,CLNT_IP FROM V$SESSIONS WHERE STATE='ACTIVE') WHERE Y_EXETIME>=2;
锁查询，语句如下所示：
SELECT O.NAME,L.* FROM V$LOCK L,SYSOBJECTS O WHERE L.TABLE_ID=O.ID AND BLOCKED=1;
阻塞查询，语句如下所示：
WITH LOCKS AS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIME FROM V$LOCK L, SYSOBJECTS O, V$SESSIONS S WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID), LOCK_TR AS (SELECT TRX_ID WT_TRXID, TID BLK_TRXID FROM LOCKS WHERE BLOCKED = 1), RES AS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID, T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP, SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL, DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS, T1.SQL_TEXT WT_SQL FROM LOCK_TR S, LOCKS T1, LOCKS T2 WHERE T1.LTYPE = 'OBJECT' AND T1.TABLE_ID <> 0 AND T2.LTYPE = 'OBJECT' AND T2.TABLE_ID <> 0 AND S.WT_TRXID = T1.TRX_ID AND S.BLK_TRXID = T2.TRX_ID) SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID FROM RES;
- SQL 日志
--设置 SQL 过滤规则，只记录必要的 SQL，生产环境不要设成 1 -- 2 只记录 DML 语句 3 只记录 DDL 语句 22 记录绑定参数的语句 -- 25 记录 SQL 语句和它的执行时间 28 记录 SQL 语句绑定的参数信息 SELECT SF_SET_SYSTEM_PARA_VALUE('SQL_TRACE_MASK','2:3:22:25:28',0,1); --同步日志会严重影响系统效率，生产环境必须设置为异步日志 SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_ASYNC_FLUSH',1,0,1); --下面这个语句设置只记录执行时间超过 200 ms 的语句 SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_MIN_EXEC_TIME',200,0,1); --下面的语句查看设置是否生效 SELECT * FROM V$DM_INI where para_name='SVR_LOG_ASYNC_FLUSH'; SELECT * FROM V$DM_INI where para_name='SQL_TRACE_MASK'; SELECT * FROM V$DM_INI where para_name='SVR_LOG_MIN_EXEC_TIME'; --开启 SQL 日志： SP_SET_PARA_VALUE(1, 'SVR_LOG', 1); --关闭 SQL 日志： SP_SET_PARA_VALUE(1, 'SVR_LOG', 0);
- AWR 日志
启用系统包和 AWR 包： CALL SP_INIT_AWR_SYS(1); CALL SP_CREATE_SYSTEM_PACKAGES(1); 查询 AWR 快照： SELECT *FROM SYS.WRM$_SNAPSHOT; 设置快照间隔，如果不设置快照间隔，手动执行快照后 SYS.WRM$_SNAPSHOT 视图中没有记录： CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(50); 在两个时间点分别手动创建快照，或者等待系统自动生成： 10:00时创建第一快照： CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); 30分钟后再创建一个，10:30， CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); 查询 AWR 快照： SELECT* FROM SYS.WRM$_SNAPSHOT; 创建AWR报告，SYS.AWR_REPORT_HTML(快照ID1,快照ID2,'AWR报告存放路径','AWR报告名称.HTLM');： SYS.AWR_REPORT_HTML(1,2,'C:\','AWR1.HTML');
- JDBC 驱动日志
4. 资源类参数配置建议和合理的进行系统架构设计
4.1 通过获取数据库服务器的配置和业务场景需求我们可以进行资源类参数配置优化。
| 参数 | 调整范围说明 |
|---|---|
| BUFFER | 内存足够的情况下，可根据数据文件的大小调整，内存不充足的情况下，可调整为可用物理内存的 60%~80% |
| BUFFER_POOLS | 高并发 OLTP 场景下，可根据客户端的并发连接数或者中间件连接池的大小进行调整 |
| MAX_BUFFER | 系统最大缓冲区大小，以兆为单位。通常设置为与 BUFFER 相同 |
| RECYCLE | 当排序缓冲区及哈希缓冲区不足的情况下，系统会优先使用 RECYCLE 缓冲区，RECYCLE 缓冲区不够，再刷临时表空间。在 OLAP 场景下，如果存在大表之间的关联查询，可以将值调大，尽可能不要使用临时表空间 |
| SORT_BUF_SIZE | 排序缓存区最大值。可以适当调大，如果在动态性能视图 vSORT_HIST0RY 和 vMTAB_USED_HISTORY 中监控到外排序，则适当调大建索引时可调大。通常不超过 20 MB |
| DICT_BUF_SIZE | 字典缓冲区大小。如果数据库中对象数量较多，或者存在大量分区表，可适当调大 |
| HJ_BUF_GL0BAL_SIZE | HASH 连接操作符的数据总缓存大小 (>= HJ_BUF_SIZE)。内存足够的情况下，可以适当调大。实际使用大小，由包含 HASH JOIN 操作符的 SQL 并发数决定 |
| HJ_BUF_SIZE | 单个 HASH 连接操作符的数据总缓存大小。在 OLTP 环境中，建议采用默认值。在 OLAP 环境下，可以根据参与 HASH JOIN 的数据量调大 |
| HJ_BLK_SIZE | 默认 1 即可，如果 HJ_BUE_SIZE 很大也可适当调大 |
| HAGR_BUF_GLOBAL_SIZE | HAGR、DIST、集合操作、SPL2、NTTS2 以及 HTAB 操作符的数据总缓存大小 (>=HAGR_BUF_SIZE)，系统级参数，以兆为单位 |
| HAGR_BUF_SIZE | 单个 HAGR、DIST、集合操作、SPL2、NTTS2 以及 HTAB 操作符的数据总缓存大小。监控 V$SORT_HISTORY，判断是否需要调整 |
| OLAP_FLAG | 用联机分析处理，0：不启用；1：启用；2：不启用，同时倾向于使用索引范围扫描。该参数会影响到计划的生成。在 OLTP 环境下，通常保持默认值 2 |
| MAX_PARALLEL_DEGREE | 设置最大并行任务个数。建议设置为 6~8 |
| PARALLEL_POLICY | 用来设置并行策略。0 表示不支持并行；1 表示自动配置并行工作线程个数（与物理 CPU 核数相同）；2 表示手动设置并行工作线程数。建议设置为 2 手动并行 |
| IO_THR_GROUPS | 表示 IO 线程组个数。建议值>=8，提升 IO 效率 |
| HIO_THR_GROUPS | HUGE 缓冲区 I0 线程组数目。使用 HUGE 表的业务场景，建议值>=8，提升 HUGE 表的 I0 效率 |
4.2 通过合理的规划、利用软硬件环境，设计更合理的系统架构
有时因为具体项目的软硬件配置各不相同，可能各个服务器的环境都不相同。这时可能就需要根据具体的业务属性来合理使用现有的资源，减少系统瓶颈。如使用读写分离集群、DSC双机冗余、MPP分布式架构等来实现更合理的业务的实现，提高系统的整体性能。
5.SQL语句性能优化
SQL 调优作为数据库性能调优中的最后一个环节，对查询性能产生着直接的影响。SQL 调优的整体目标是使用最优的执行计划，这意味着 IO 以及 CPU 代价最小。具体而言调优主要关注下列方面：
- 通过建立索引避免查询时全表扫描
如果计划中对某大表使用了全索引扫描，那么用户需要关注是否存在着该表的某个查询条件使得过滤后可以淘汰至少一半的数据量。通过添加相应的索引，全索引扫描可能被转换为范围扫描或等值查找。正常来讲全表扫描一定是最低效的查询方式。
- 合理的设置连接操作的顺序和类型
多表连接时，不同的连接顺序会影响中间结果集数量的大小，这时调优的目标就是要找到一种能使中间结果保持最小的连接顺序。
对于给定的一个连接或半连接，DM 可以用 HASH 连接、嵌套循环连接、索引连接或者是归并连接实现。通过分析表的数据量大小和索引信息，SQL 调优目标是选择最适宜的操作符。
对半连接而言，HASH 连接还可细分为左半 HASH 和右半 HASH。用户可以通过始终对数据量小的一侧建立 HASH 来进行调优。
- 合理的进行分组操作
分组操作往往要求缓存所有数据以找到属于同一组的所有数据，在大数据量情况下这会带来大量的 IO。用户应该检查 SQL 查询和表上索引信息，如果可以利用包含分组列的索引，那么执行计划就会使用排序分组从而不用缓存中间结果。
- 查询优化器介绍
除了人工对SQL的结构进行分析，得到更合理的SQL语句编写外，DM数据库还提供了查询优化器来自动实现SQL语句的优化。查询优化器采用基于代价的方法，在估计代价时，主要以统计信息或者普遍的数据分布为依据。
优化器所做的操作有：查询转换、估算代价、生成计划。
- 查询优化器介绍
执行计划是 SQL 语句的执行方式，由查询优化器为语句设计的执行方式，交给执行器去执行。在 SQL 命令行使用 EXPLAIN 可以打印出语句的执行计划。
例如：
建表和建索引语句：
Copy CREATE TABLE T1(C1 INT,C2 CHAR);
CREATE TABLE T2(D1 INT,D2 CHAR);
CREATE INDEX IDX_T1_C1 ON T1(C1);
INSERT INTO T1 VALUES(1,'A');
INSERT INTO T1 VALUES(2,'B');
INSERT INTO T1 VALUES(3,'C');
INSERT INTO T1 VALUES(4,'D');
INSERT INTO T2 VALUES(1,'A');
INSERT INTO T2 VALUES(2,'B');
INSERT INTO T2 VALUES(5,'C');
INSERT INTO T2 VALUES(6,'D');
打印执行计划：
Copy EXPLAIN SELECT A.C1+1,B.D2 FROM T1 A, T2 B WHERE A.C1 = B.D1;
执行计划如下：
1 ##NSET2: [0, 16, 9]
2 ##PRJT2: [0, 16, 9]; EXP_NUM(2), IS_ATOM(FALSE)
3 ##NEST LOOP INDEX JOIN2: [0, 16, 9]
4 ##CSCN2: [0, 4, 5]; INDEX33555535(B)
5 ##SSEK2: [0, 4, 0]; SCAN_TYPE(ASC), IDX_T1_C1 (A), SCAN_RANGE[T2.D1,T2.D1]
这个执行计划看起来就像一棵树，执行过程为：控制流从上向下传递，数据流从下向上传递。其中，类似[0, 16, 9]这样的三个数字，分别表示估算的操作符代价、处理的记录行数和每行记录的字节数。同一层次中的操作符，如本例中的 CSCN2 和 SSEK2，由父节点 NEST LOOP INDEX JOIN2 控制它们的执行顺序。
该计划的大致执行流程如下：
- CSCN2: 扫描 T2 表的聚集索引，数据传递给父节点索引连接；
- NEST LOOP INDEX JOIN2: 当左孩子有数据返回时取右侧数据；
- SSEK2: 利用 T2 表当前的 D1 值作为二级索引 IDX_T1_C1 定位查找的 KEY，返回结果给父节点；
- NEST LOOP INDEX JOIN2: 如果右孩子有数据则将结果传递给父节点 PRJT2，否则继续取左孩子的下一条记录；
- PRJT2: 进行表达式计算 C1+1, D2；
- NSET2: 输出最后结果；
- 重复过程 1) ~ 4)直至左侧 CSCN2 数据全部取完。
用户如果想了解更多关于操作符的知识，请查看动态视图 V$SQL_NODE_NAME，《DM8 系统管理员手册》的附录 3 给出了常用操作符的说明。
达梦在线服务平台：https://eco.dameng.com
708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
