MySQL架构优化:定时计划任务与表分区

转自:

MySQL架构优化实战系列3:定时计划任务与表分区 - 今日头条(TouTiao.com)
http://toutiao.com/a6304736482361049345/?tt_from=mobile_qq&utm_campaign=client_share&app=news_article&utm_source=mobile_qq&iid=4710631352&utm_medium=toutiao_ios

定时计划任务

1、概论

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

2、配置

  • 开启计划任务

SHOW VARIABLES LIKE 'event_scheduler' 查看是否开启 off 表示未开启

set global event_scheduler =1 此次重启之后的mysql器件生效

MySQL架构优化:定时计划任务与表分区

永久生效

MySQL架构优化:定时计划任务与表分区

可见已经开启

3、语法体

MySQL架构优化:定时计划任务与表分区

4、周期或者时间点语法

  • 每1秒执行

on schedule every 1 second

  • 10天后执行

on schedule at current_timestamp + interval 10 day

  • 指定日期时间执行

on schedule at timestamp '2016-08-16 00:00:00'

  • 每天凌晨3点执行

on schedule every 1 day starts '2016-05-18 03:00:00' (设定从第二天凌晨3点开始)

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

on schedule every 1 day ends current_timestamp + interval 5 day

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

on schedule every 1 day

starts current_timestamp + interval 5 day

ends current_timestamp + interval 1 month

5、高级用法

  • 执行多条sql

MySQL架构优化:定时计划任务与表分区

  • 临时关闭事件

alter event smudge_insert disable;

  • 临时开启事件

alter event smudge_insert enable;

  • 删除计划任务

drop event smudge_insert;

表分区

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

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

1、拆表方式

  • 水平拆表

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

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

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

  • 垂直分表

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

sql语句优化成 ... user_base left join user_extend on user_base.id = user_extend.id..

  • 表分区

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

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

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

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

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

2、基于range分区

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

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

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

MySQL架构优化:定时计划任务与表分区

对已有的表创建分区

MySQL架构优化:定时计划任务与表分区

  • 可能遇到的错误

MySQL架构优化:定时计划任务与表分区

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

  • 查看现在数据库文件

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

MySQL架构优化:定时计划任务与表分区

  • 插入数据测试

MySQL架构优化:定时计划任务与表分区

MySQL架构优化:定时计划任务与表分区

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

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

MySQL架构优化:定时计划任务与表分区

3、基于list分区

  • 分区语句

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

MySQL架构优化:定时计划任务与表分区

  • 表文件

MySQL架构优化:定时计划任务与表分区

4、基于hash分区

常用于对主键的快速分区

  • 分区语句

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

MySQL架构优化:定时计划任务与表分区

  • 表文件

MySQL架构优化:定时计划任务与表分区

5、基于key分区

  • 建表分区

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

MySQL架构优化:定时计划任务与表分区

  • 添加分区

MySQL架构优化:定时计划任务与表分区

  • 表文件

MySQL架构优化:定时计划任务与表分区

6、对于Innodb引擎表的分区

  • Innodb表默认是共享存储空间

默认my.cnf文件

MySQL架构优化:定时计划任务与表分区

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

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

cd usr/local/mysql/var/smudge

MySQL架构优化:定时计划任务与表分区

只有一个文件 shop.frm

MySQL架构优化:定时计划任务与表分区

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

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

  • 设置成独立表空间

添加一行 innodb_file_per_table

MySQL架构优化:定时计划任务与表分区

service mysql restart 重启mysql服务

  • 添加表分区

MySQL架构优化:定时计划任务与表分区

  • 查看表文件

MySQL架构优化:定时计划任务与表分区

可见分区成功了

上一篇:非负矩阵分解NMF


下一篇:利用JQuery实现全选和反选的几种方法