数据库监控与调优【一】—— 数据库调优的维度

柯柯不会Java

已于 2023-06-26 21:41:15 修改

阅读量355

点赞数

文章标签：数据库

于 2023-06-26 21:32:47 首次发布

本文链接：https://blog.csdn.net/qq_46601365/article/details/131405798

版权

文章讲述了数据库调优的多个维度，包括分析不合理业务需求、存储过程优化、SQL及索引调整、架构设计、系统参数设置以及硬件配置。通过一个具体的财务SaaS系统案例，展示了如何通过改变需求、分页和统计模式来提升性能，以及调整数据库参数和系统配置来优化数据库性能。

摘要由CSDN通过智能技术生成

数据库调优的维度

左边是千金良方的，右边是个人优化的

在这里插入图片描述

业务需求

不合理的需求，可能会造成很多问题
勇敢地对不合理的需求说不
拨乱反正，把不合理的需求变成合理的需求

例子

财务SaaS系统，财务领域有种叫做代账的概念，通俗的说就是账务外包，本公司不招聘财务，让外包公司记账。这个外包公司也叫代账公司，这个系统就是给代账会计使用。一个代账会计会给n个企业记n个账。代账会计登录系统后会展示代账会计管理的所有的账。

产品经理需求：该代账会计管理列表必须要有每条账的各种汇总信息，比如：同比增长、环比增长等。而且需要精确展示。导致需要n张表关联查询然后实时计算结果。同时该列表还不允许分页

具体页面如下：

在这里插入图片描述

针对这样的需求，初期使用java代码实现，结果对于管理帐非常多的会计需要8s左右才能返回。太慢了。后续对mysql的存储过程作了改进，优化到了5s左右

为啥存储过程能够提速？

首先存储过程只需要在创建时编译即可，而sql每次执行都需要被数据库编译。其次存储过程可以降低网络开销。像上面的业务，sql非常复杂。可能有100-200行，如果使用java代码需要将sql发送到数据库。而存储过程本身就是存储在服务器端的，调用时只需要使用CALL语句传入参数即可。因此会节省网络。但是即使这样也需要5s左右时间才能返回。依然很慢

后续做了一下sql调优，主要增加了一些索引和改写了部分sql，提高执行效率，但是也只能优化到2s左右

但是业务认为2s依然是不可以接受的

此时，其实业务上这样的需求，技术上是难保证业务功能的基础项还可以高效返回数据的。这就是业务需求的不合理性

优化方案：第一，每条账的各种汇总信息如同比增长、环比增长等不在列表页显示，改为详情中显示。列表页sql得到了大幅优化，性能得到了大幅提升。用户想要查看某条账的各种汇总信息如同比增长、环比增长等，就变成了单个账户的查询也是可控的。第二，作分页，对于某些代账会计管理的账目非常多，一次全部返回的网络开销是非常大的，作分页后每页只展示20条，可以降低网络开销。第三，提供了统计模式，如果用户确实想要在列表页查看各套账目的汇总信息，那么可以点击统计模式，统计模式会出现弹窗，提示用户这个模式是比较慢的，然后调用以前的老代码展示之前的页面视图，满足业务的需求

结果：本来的页面只需要500ms就能返回，性能提升可想而知

优化后的页面如下：

在这里插入图片描述

系统架构

做架构设计的时候，应充分考虑业务的实际情况，考虑好数据库的各种选择

根据项目读的压力，考虑需不需要做读写分离
根据项目可用性需求，考虑需不需要为数据库实现高可用
根据项目并发量，创建合理个数的数据库实例，实例太多会造成资源浪费；实例太少又满足不了业务需求
根据项目数据量，合理分库分表
用什么数据库，一些业务场景可能更适合非关系型数据库或者缓存数据库等

SQL及索引（开发人员最需要关注）

根据需求编写良好的SQL，并去创建足够高效的索引

表结构

设计良好的表结构，直接关系SQL的编写

数据库参数设置（DBA会比较关注）

设置合理的数据库性能参数

如：

join buffer：处理联表查询
sort buffer：处理排序
…

系统配置（运维和DBA会比较关注）

操作系统提供了各种资源使用策略，设置合理的配置，以便于数据库充分利用资源

如：

linux系统提供了swap交换区->提供了swappiness参数用来控制什么情况下使用swap
swappiness=0，操作系统会在几乎没有内存的情况下使用swap
swappiness=100，操作系统剩下的内存将会很快被交换
对于数据库应该把swappiness设置的尽可能小，从而保证热点数据保留到物理内存。

ps：物理内存（Physical memory）是相对于虚拟内存而言的。物理内存指通过物理内存条而获得的内存空间，而虚拟内存则是指将硬盘的一块区域划分来作为内存。内存主要作用是在计算机运行时为操作系统和各种程序提供临时储存。

swappiness：Linux内核参数，控制换出运行时内存的相对权重。swappiness参数值可设置范围在0到100之间。低参数值会让内核尽量少用交换，更高参数值会使内核更多的去使用交换空间。默认值为60（参考网络资料：当剩余物理内存低于40%（40=100-60）时，开始使用交换空间）。对于大多数操作系统，设置为100可能会影响整体性能，而设置为更低值（甚至为0）则可能减少响应延迟。