Mysql性能优化三(分表、增量备份、还原)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

接上篇Mysql性能优化二

对表进行水平划分           

如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了。如果我拆成100个表,那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了。如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了。所以一个好的拆分依据是 最重要的。关键字:UNION 
例:

  • 订单表根据订单产生时间来分表(一年一张)
  • 学生情况表
  • 查询电话费,近三个月的数据放入一张表,一年内的放入到另一张表

对表进行垂直划分  

有些表记录数并不多,可能也就2、3万条,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。 (JOIN)        

【试题内容】、【答案信息】两个表,最初是作为几个字段添加到【试题信息】里的,可以看到试题内容和答案这两个字段很长,在表里有3万记录时,表已经占 了1G的空间,在列试题列表时非常慢。经过分析,发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件,分页显示试题详细内容。而每 次检索都是这几个表做join,每次要扫描一遍1G的表。我们完全可以把内容和答案拆分成另一个表,只有显示详细内容的时候才读这个大表,由此 就产生了【试题内容】、【答案信息】两个表。

选择适当的字段类型,特别是主键      

选择字段的一般原则是保小不保大,能用占用字节小的字段就不用大字段。比如主键, 建议使用自增类型,这样省空间,空间就是效率!按4个字节和按32个字节定位一条记录,谁快谁慢太明显了。涉及到几个表做join时,效果就更明显了。
建议使用一个不含业务逻辑的id做主角如s1001。例:

复制代码
int 4  bigint 8 mediumint smallint 2 tinyint 1
md5  char(32)
id :整数 tinyint samllint int bigint
student表
id stuno     stuname     adress
1  s1001    小民            深圳
复制代码

文件、图片等大文件用文件系统存储

数据库只存储路径。图片和文件存放在文件系统,甚至单独放在一台服务器(图床 / 视频服务器 ).

数据库参数配置

最重要的参数就是内存,我们主要用的innodb引擎,所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size
=1G

对于myisam,需要调整key_buffer_size,当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数
在my.ini修改端口3306,默认存储引擎和最大连接数

在my.ini中.
port=3306 [有两个地方修改]
default-storage-engine=INNODB 
max_connections=100

合理的硬件资源和操作系统

如果你的机器内存超过4G,那么毋庸置疑应当采用64位操作系统和64位mysql 5.5.19 or mysql5.6
读写分离
    如果数据库压力很大,一台机器支撑不了,那么可以用mysql复制实现多台机器同步,将数据库的压力分散。 

Master
Slave1
Slave2
Slave3
    主库master用来写入,slave1—slave3都用来做select,每个数据库分担的压力小了很多。
要实现这种方式,需要程序特别设计,写都操作master,读都操作slave,给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理,对程 序来读写哪些数据库是透明的。官方有个mysql-proxy,但是还是alpha版本的。新浪有个amobe for mysql,也可达到这个目的,结构如下 

定时完成数据库的备份

项目实际需求,请完成定时备份某个数据库,或者定时备份数据库的某些表的操作
windows 下每隔1小时,备份一次数据newsdb
windows 每天晚上2:00   备份 newsdb 下 某一张表 
cmd> mysqldump –u root –p密码  数据库名 > 把数据库放入到某个目录
案例,备份 mydb 库的所有表
进入mysqldump所在的目录
cmd> mysqldump –u root –phsp shop> d:/shop.log   [把shop数据库的所有表全部导出]
cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop数据库的 temusers和emp导出]
如何恢复数据的表
进入的mysql操作界面
mysql>source  备份文件的全路径
定时备份:(把命令写入到my.bat 问中)
windows 如何定时备份 (每天凌晨2:00)
使用windows自带的计划任务,定时执行批处理命令。

增量备份和还原

定义:mysql数据库会以二进制的形式,自动把用户对mysql数据库的操作,记录到文件,当用户希望恢复的时候,可以使用备份文件进行恢复。

增量备份会记录dml语句、创建表的语句,不会记录select。记录的东西包括:sql语句本身、操作时间,位置

进行增量备份的步骤和恢复

 注意:mysql5.0及之前的版本是不支持增量备份的
1、配置my.ini文件或者my.conf,启用二进制备份。
打开my.ini文件,查找log-bin,进行配置:log-bin=G:\Database\mysqlbinlog\mylog
在G:\Database目录下面新建目录mysqlbinlog
2、重启mysql服务
这个时候会在mysqlbinlog目录下面看到以下两个文件:
mylog.000001:日志备份文件。如果要查看这个日志文件里面的信息,我们可以使用mysqlbinlog程序查看,mysqlbinlog程序存放在mysql的bin目录下面(“C:\Program Files\MySQL\MySQL Server 5.6\bin”)。

执行sql语句

UPDATE emp set ename='zouqj' where empno=100003;

开始——运行——cmd,mysqlbinlog 备份文件路径

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlbinlog G:\Database\mysqlbinlog\mylog.000001


mylog.index:日志索引文件,里面记录了所以的日志文件。(G:\Database\mysqlbinlog\mylog.000001)
3、假设现在问题来了,我这条update是误操作,如何进行恢复
在mysql日志中会记录每一次操作的时间和位置,所以我们既可以根据时间来恢复,也可以根据位置来恢复。
那么,我们现在马上可以从上图看出,这条语句产生的时间是"2016-04-17 12:01:36",位置是614

按时间来恢复

我们可以选择在语句产生时间的前一秒

执行cmd命令:mysqlbinlog --stop-datetime="2016-04-17 12:01:35" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p

这个时候我再执行SQL语句查看

SELECT * from emp where empno=100003;

结果变成了

按位置来恢复

执行cmd命令:mysqlbinlog --stop-position="614" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p

这个时候再执行SQL来查看结果,又变回来了。


本文转自邹琼俊博客园博客,原文链接:http://www.cnblogs.com/jiekzou/p/5399160.html,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
26天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之从MySQL同步数据到Doris时,历史数据时间字段显示为null,而增量数据部分的时间类型字段正常显示的原因是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
关系型数据库 MySQL OLAP
实时计算 Flink版产品使用合集之可以支持 MySQL 数据源的增量同步到 Hudi 吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2天前
|
canal 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在进行整库同步MySQL数据到StarRocks时,遇到全量数据可以同步,但增量数据无法同步,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3天前
|
SQL 监控 关系型数据库
实时计算 Flink版产品使用问题之使用mysql cdc配置StartupOptions.initial()全量之后就不增量了,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3天前
|
SQL 存储 关系型数据库
深入理解MySQL:数据库管理与性能优化
第一章:MySQL基础 MySQL概述:简要介绍MySQL的历史、特点和应用领域
|
3天前
|
存储 关系型数据库 MySQL
精通MySQL:从基础到高级应用及性能优化
第一章:MySQL入门 1.1 MySQL简介 介绍MySQL的历史、版本和开源特性
|
3天前
|
SQL 关系型数据库 MySQL
精通MySQL:从数据库管理到性能优化
第一章:MySQL入门 MySQL简介:了解MySQL的起源、发展历程以及在Web开发中的重要性
|
3天前
|
关系型数据库 MySQL 数据库
精通MySQL:数据库管理、性能优化与最佳实践
h3> 一、引言 MySQL是一个功能强大的开源关系型数据库管理系统,广泛应用于各种Web应用、企业级应用和数据分析等领域
|
3天前
|
SQL 安全 关系型数据库
精通MySQL:核心功能、性能优化与安全管理
h3> 第一章:MySQL入门 1.1 MySQL概述 介绍MySQL的历史、版本发展及其在当前数据库领域的重要地位
|
18天前
|
SQL 关系型数据库 MySQL
mysql日志管理 、备份与恢复
mysql日志管理 、备份与恢复