实例讲解数据库优化

小瑶姑娘_4303

小瑶姑娘_4303

2019-11-27

3004人浏览

转载

从网上去搜数据库优化基本都是从sql层次进行优化的,很少有提及到数据库本身的实例优化。就算有也都是基于某个特定数据库的实例优化,本文涵盖目前市面上所有主流数据库的实例优化(oralce、mysql、postgres、达梦),按照文章的配置能够将你数据库性能用到80%或以上。

数据库优化方法论

这部分为理论知识,不感兴趣的同学可以直接跳到后面参数配置部分。

数据库优化目标

推荐 《mysql视频教程》  

根据角色的不同,数据库优化分为以下几个目标:

业务角度(关键用户):减少用户页面响应时间数据库角度(开发):减少数据库SQL响应时间数据库服务器角度(运维):充分使用数据库服务器物理资源减少数据库服务器CPU使用率减少数据库服务器IO使用率减少数据库服务器内存使用率

指标

1. SQL平均响应时间变短

a. 优化前:数据库平均响应时间500ms

b. 优化目标:数据库平均响应时间200ms

2. 数据库服务器CPU占用率变少

a. 优化前:数据库高峰期CPU使用率70%

b. 优化目标:数据库高峰期CPU使用率50%

3. 数据库服务器IO使用率变低

a. 优化前:数据库IO WAIT为30%

b. 优化目标:数据库IO WAIT低于10%

数据库优化误区

在进行数据库优化的时候可能会有以下几个误区:

1. 优化之前一定要深入了解数据库内部原理

优化是有“套路”的,照着这些“套路”你也可以很好的完成数据库优化

2. 不断调整数据库参数就可以最终实现优化

有时候设计不合理怎么调整参数都不行

3. 不断调整操作系统参数就可以最终实现优化

同上

4. 数据库性能由应用、数据库架构决定,与应用开发关系不大

恰恰相反,应用开发的关系很大

5. 必须要做读写分离,必须要弄分库分表

数据量级只有达到一定的比例才有必要做读写分离,分表分库,否则徒增复杂度。一般来说Oracle的单表量级可以达到1亿,MySQL到1000万~2000万

数据库优化流程

Drizzle ORM TypeScript SQL 数据库工具包
Drizzle ORM TypeScript SQL 数据库工具包

Drizzle ORM 是一个轻量级的 TypeScript ORM,提供类型安全的 SQL 模式声明、关系型和类 SQL 查询构建器,以及自动生成迁移。其压缩后仅 7.4kb,且无依赖项,支持 PostgreSQL、MySQL 和 SQLite,包括 Neon、Turso 和 Cloudflare D1 等无服务器数据库。

下载

完整的数据库优化流程如下:

file

首先需要尽可能的了解优化问题,收集问题期间系统信息并做好存档。根据当前系统问题表现制定优化目标并与客户沟通目标达成一致;通过一系列工具分析系统问题,制定优化方案,方案评审完成后由各负责人员进行实施。若达到优化目标则编写优化报告,否则需要重新制定优化方案。

数据库实例优化

数据库实例优化遵循三句口诀:日志不能小、缓存足够大、连接要够用。

数据库事务提交后需要将事务对数据页的修改刷( fsync)到磁盘上,才能保证数据的持久性。这个刷盘,是一个随机写,性能较低,如果每次事务提交都要刷盘,会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法:

a. 先将事务写到日志文件RedoLog(WAL),将随机写优化成顺序写

b. 加一层缓存结构Buffer,将每次写优化成顺序写

所以日志跟缓存对数据库实例尤其重要。而连接如果不够用,数据库会直接抛出异常,系统无法访问。

数据库参数优化

主流数据库架构都有如下的共同点:

数据缓存

SQL解析区

排序内存

REDO及UNDO

锁、LATCH、MUTEX

监听及连接

文件读写性能

接下来我们根据不同的数据库调整参数以使数据库达到最佳性能。

ORACLE

参数分类 参数名 参数值 备注
数据缓存 SGA_TAGET、MEMORY_TARGET 物理内存70-80% 越大越好
数据缓存 DB_CACHE_SIZE 物理内存70-80% 越大越好
SQL解析 SHARED_POOL_SIZE 4-16G 不建议设置过大
监听及连接 PROCESSES、SESSIONS、OPEN_CURSORS 根据业务需求设置 一般为业务预估连接数的120%
其他 SESSION_CACHED_CURSORS 大于200 软软解析

MYSQL(INNODB)

参数分类 参数名 参数值 备注
数据缓存 INNODB_BUFFER_POOL_SIZE 物理内存50-80% 一般来说越大性能越好
日志相关 Innodb_log_buffer_size 16-32M 根据运行情况调整
日志相关 sync_binlog 1、100、0 1安全性最好
监听及连接 max_connections 根据业务情况调整 可以预留一部分值
文件读写性能 innodb_flush_log_at_trx_commit 2 安全和性能的折中考虑
其他 wait_timeout,interactive_timeout 28800 避免应用连接定时中断

POSTGRES

参数分类 参数名 参数值 备注
数据缓存 SHARED_BUFFERS 物理内存10-25%
数据缓存 CACHE_BUFFER_SIZE 物理内存50-60%
日志相关 wal_buffer 8-64M 不建议设置过大过小
监听及连接 max_connections 根据业务情况调整 一般为业务预估连接数的120%
其他 maintenance_work_mem 512M或更大
其他 work_mem 8-16M 原始配置1M过小
其他 checkpoint_segments 32或者更大

达梦数据库

参数分类 参数名 参数值 备注
数据缓存 MEMROY_TARGET、MEMROY_POOL 物理内存90%
数据缓存 BUFFER 物理内存60% 数据缓存
数据缓存 MAX_BUFFER 物理内存70% 最大数据缓存
监听及连接 max_sessions 根据业务需求设置 一般为业务预估连接数的120%

总结

数据库的优化手法太多太多,有换磁盘阵列升级硬件,有改写SQL脚本添加索引,还有数据库参数调整优化性能,甚至还可以调整数据库架构。本文从数据库本身参数进行调优,大家根据上面几张表中的参数进行调整基本能达到数据库最佳性能的80%。

本文来自php中文网,mysql教程栏目,欢迎学习!  

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

数据库

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程