考试首页 | 考试用书 | 培训课程 | 模拟考场 | 考试论坛  
  当前位置:编程开发 > MSSQL > 文章内容
  

MsSql教程:Mysql数据库性能优化三(分表、增量备份、还原)

 [ 2017年7月5日 ] 【

  对表进行水平划分

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

  例:

  订单表根据订单产生时间来分表(一年一张)

  学生情况表

  查询电话费,近三个月的数据放入一张表,一年内的放入到另一张表

  对表进行垂直划分

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

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

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

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

  建议使用一个不含业务逻辑的id做主角如s1001。例:

  ?

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

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

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

  数据库参数配置

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

  ?

1
2
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

  对于myisam,需要调整key_buffer_size,当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数

  在my.ini修改端口3306,默认存储引擎和最大连接数

  ?

1
2
3
4
在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语句本身、操作时间,位置

首页 1 2 尾页
本文纠错】【告诉好友】【打印此文】【返回顶部
将考试网添加到收藏夹 | 每次上网自动访问考试网 | 复制本页地址,传给QQ/MSN上的好友 | 申请链接 | 意见留言 TOP
关于本站  网站声明  广告服务  联系方式  站内导航  考试论坛
Copyright © 2007-2013 中华考试网(Examw.com) All Rights Reserved