MySQL架构优化实战系列3：定时计划任务与表分区

2023-08-03 08:15:22

原创 2016-07-08 汤抗 DBAplus社群

一

定时计划任务

1、概论

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

2、配置

开启计划任务

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

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

永久生效

可见已经开启

3、语法体

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

临时关闭事件

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分区

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