UDN-企业互联网技术人气社区

板块导航

浏览  : 753
回复  : 1

[资源分享] MySQL架构优化实战系列3:定时计划任务与表分区

[复制链接]
瞌睡虫的头像 楼主
  一  定时计划任务

  1、概论

  mysql计划任务可以定时更新数据库表或者做大文件的汇总表。

  2、配置

  开启计划任务

  SHOWVARIABLESLIKE'event_scheduler'查看是否开启off表示未开启

  setglobalevent_scheduler=1此次重启之后的mysql器件生效

  
1.webp.jpg


  永久生效

  
2.png


  可见已经开启

  3、语法体

  
3.webp.jpg


  4、周期或者时间点语法

  每1秒执行

  onscheduleevery1second

  10天后执行

  onscheduleatcurrent_timestamp+interval10day

  指定日期时间执行

  onscheduleattimestamp'2016-08-1600:00:00'

  每天凌晨3点执行

  onscheduleevery1day

  starts'2016-05-1803:00:00'(设定从第二天凌晨3点开始)

  每天定时执行,5天后停止执行

  on schedule every 1 day

  end scurrent_time stamp+interval 5 day

  5天后开启每天定时清空test表,一个月后停止执行

  on schedule every 1 day

  starts current_time stamp+interval 5 day

  endscurrent_timestamp+interval1month

  5、高级用法

  执行多条sql

  
4.webp.jpg


  临时关闭事件

  alter events mudge_insertdisable;

  临时开启事件

  altereventsmudge_insertenable;

  删除计划任务

  dropeventsmudge_insert;

  二

  表分区

  单张表超过1000W行已经算作是大数据存储场景。

  常规海量数据优化:大表拆小表、sql语句优化,下面我们重点介绍大表拆小表的优化。

  1、拆表方式

  水平拆表

  将表user中的1000w行数据拆成user1表和user2表,每张表500w行数据

  但是这样做法就是导致sql语句需要更改为selectuser1,user2...

  拆的越多,sql语句越长,所以不推荐此法拆表

  垂直分表

  如将user表100个字段拆成表user_base(30字段)、表user_extend(20字段)

  sql语句优化成...user_baseleftjoinuser_extendonuser_base.id=user_extend.id..

  表分区

  对行水平进行分表,物理存储上分区存储,每个分表有独立的文件,应用程序上还是一张表。

  Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

  Hash(哈希)–这中模式允许通过对表的一个或多个列的HashKey进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

  Key(键值)-上面Hash模式的一种延伸,这里的HashKey是MySQL系统产生的。

  List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。

  2、基于range分区

  基于给定连续的区间的值对行进行分区。

  新建表user基于salary区间进行表分区

  以字段salary为准按照区间[0,1000][1000,3000][3000,..]将表分三个区

  
5.webp.jpg


  对已有的表创建分区

  
6.webp.jpg


  可能遇到的错误

  
7.webp.jpg


  这里的提示已经很明确了,分区的列必须是个主键列。所以我们给salary添加主键。

  查看现在数据库文件

  [root@localhostsmudge]#cd/usr/local/mysql/var

  
8.webp.jpg


  插入数据测试

  
9.webp.jpg


  
10.webp.jpg


  可以插入更多的数据,观察分区文件的大小。

  使用watch-n1ls-lh每秒监测文件大小的变化:

  
11.webp.jpg


  3、基于list分区

  分区语句

  以area_id列为准,按照华南和华北将表分成两个区

  
12.webp.jpg


  表文件

  
13.webp.jpg


  4、基于hash分区

  常用于对主键的快速分区

  分区语句

  以主键id为准,hash算法将表平均分成4个区

  
14.webp.jpg


  表文件

  
15.webp.jpg


  5、基于key分区

  建表分区

  和hash分区类似将表分成4个区

  
16.webp.jpg


  添加分区

  
17.webp.jpg


  表文件

  
18.webp.jpg


  6、对于Innodb引擎表的分区

  Innodb表默认是共享存储空间

  默认my.cnf文件

  
19.webp.jpg


  默认情况下Innodb是使用的共享表空间

  当在库smudge中,新建一张Innodb引擎的shop表

  cdusr/local/mysql/var/smudge

  
20.webp.jpg


  只有一个文件shop.frm

  
21.webp.jpg


  shop表的索引和数据都存在ibdata1文件中,共享存储空间的Innodb不可以分区!

  所以我们要将Innodb表设置成独立表空间,索引和数据都存放在ibd文件中。

  设置成独立表空间

  添加一行innodb_file_per_table

  
22.webp.jpg


  servicemysqlrestart重启mysql服务

  添加表分区

  
23.webp.jpg


  查看表文件

  
24.webp.jpg


  可见分区成功了。

文章来源:DBAplus社群

文章作者:汤抗

相关帖子

发表于 2016-7-9 13:17:39 | 显示全部楼层
赞一个
使用道具 举报

回复

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部