首页 / MYSQL / MySQL之按月拆分主表并按月分表写入数据提高数据查询速度

MySQL之按月拆分主表并按月分表写入数据提高数据查询速度

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL之按月拆分主表并按月分表写入数据提高数据查询速度，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5250字，纯文字阅读大概需要8分钟。

内容图文

使用场景：

主表数据量特别大，为了提高查询的速度，可以考虑按月进行分表，要求就是当月的数据到当月表查询，上月的数据到上月表查询，当天的数据到主表来查询。这样在一定程度上也是提高了数据的查询速度

过程演示：

1.创建总表：

CREATE TABLE `zong_biao` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `day` date NOT NULL DEFAULT ‘0000-00-00‘ COMMENT ‘日期‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

2.创建定时器：

当前时间1分钟后，每2分钟写入一条数据到zong_biao，为了演示，要保证主表里面一直有数据定时在写入

delimiter $$
create event event_1 
on schedule every 2 MINUTE  STARTS   CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
COMMENT ‘xiaowu create‘
do 
    BEGIN
           insert into test.zong_biao(username,password,create_time,day) values("李四","tomcat",now(),curdate());
    END $$
delimiter ;

3.按月对主表进行分表拆分数据

3.1建立上月表和本月表

mysql> create table biao_lastmonth like zong_biao;

Query OK, 0 rows affected

mysql>  create table biao_month like zong_biao;

Query OK, 0 rows affected

3.2.创建一个表来记录是否是1号，数据写入是否成功:

Create Table: CREATE TABLE `dr_call_time` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `c_datetime` datetime NOT NULL COMMENT ‘执行时间‘,
  `type` tinyint(4) NOT NULL DEFAULT ‘0‘ COMMENT ‘是否为1号，默认为0，即不是1号‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

mysql> select * from dr_call_time;
+----+---------------------+------+
| id | c_datetime | type |
+----+---------------------+------+
| 1 | 2018-09-24 02:00:00 | 0 |
| 2 | 2018-09-25 02:00:00 | 0 |
| 3 | 2018-09-26 02:00:00 | 0 |
| 4 | 2018-09-27 02:00:00 | 0 |
| 5 | 2018-09-28 02:00:00 | 0 |
| 6 | 2018-09-29 02:00:00 | 0 |
| 7 | 2018-09-30 02:00:00 | 0 |
| 8 | 2018-10-01 02:00:01 | 1 |
| 9 | 2018-10-02 02:00:00 | 0 |
+----+---------------------+------+
9 rows in set (0.00 sec)

3.3. 建立存储过程


/*每月1号 清空上月表，清空本月表；如果不是每月1号，则把昨天的数据插入本月数据表中*/
delimiter //
CREATE PROCEDURE p_dr_stats()
BEGIN
    declare v_day int;
  declare v_yesterday date;

set v_day = day(curdate());
set v_yesterday = date_sub(current_date(),interval 1 day);
select v_day;
/*select v_yesterday;*/

/*每月第一天 清空本月表和上月表；如果不是本月第一天，则把昨天的数据插入本月数据表中*/
if v_day = 1 then   
   truncate table biao_lastmonth;
   truncate table biao_month;
   insert into biao_lastmonth select * from zong_biao where day >= date_add(curdate()-day(curdate())+1,interval -1 month) and day < date_add(curdate(), interval - day(curdate()) + 1 day);
   insert into dr_call_time(c_datetime,type) values(now(),1);  /* 用于记录该过程是否已执行*/
else
    insert into biao_month select * from zong_biao where day >= v_yesterday and day < curdate();
    insert into dr_call_time(c_datetime) values(now());
end if;
end //
delimiter ;

3.4 建立定时任务event

/*从2018-09-24 02:00:00开始，每天凌晨2:00执行上面的3.3的存储过程
delimiter //
CREATE EVENT e_test
ON SCHEDULE EVERY 1 day STARTS TIMESTAMP ‘2018-09-24 02:00:00‘
ON COMPLETION PRESERVE
DO
BEGIN
CALL p_dr_stats();
END//
delimiter ;

查看记录是否是1号的表，了解event定时器和储存过程的执行状态

mysql> select * from dr_call_time;
+----+---------------------+------+
| id | c_datetime          | type |
+----+---------------------+------+
|  1 | 2018-09-24 02:00:00 |    0 |
|  2 | 2018-09-25 02:00:00 |    0 |
|  3 | 2018-09-26 02:00:00 |    0 |
|  4 | 2018-09-27 02:00:00 |    0 |
|  5 | 2018-09-28 02:00:00 |    0 |
|  6 | 2018-09-29 02:00:00 |    0 |
|  7 | 2018-09-30 02:00:00 |    0 |
|  8 | 2018-10-01 02:00:01 |    1 |
|  9 | 2018-10-02 02:00:00 |    0 |
+----+---------------------+------+
9 rows in set (0.00 sec)

到此处演示完毕，测试都是没问题的。

3.5查看创建存储过程的语句


mysql> show create procedure p_dr_stats\G
*************************** 1. row ***************************
           Procedure: p_dr_stats
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p_dr_stats`()
BEGIN
declare v_day int;
  declare v_yesterday date;

set v_day = day(curdate());
set v_yesterday = date_sub(current_date(),interval 1 day);
select v_day;

if v_day = 1 then   
   truncate table biao_lastmonth;
   truncate table biao_month;
   insert into biao_lastmonth select * from zong_biao where day >= date_add(curdate()-day(curdate())+1,interval -1 month) and day < date_add(curdate(), interval - day(curdate()) + 1 day);
   insert into dr_call_time(c_datetime,type) values(now(),1);  

else
    insert into biao_month select * from zong_biao where day >= v_yesterday and day < curdate();
    insert into dr_call_time(c_datetime) values(now());
end if;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql>

到此处MySQL之按月拆分主表并按月分表写入数据已经演示完毕了，欢迎伙伴们留言一起交流学习。

MySQL之按月拆分主表并按月分表写入数据提高数据查询速度

标签：tin   select   datetime   min   character   date   limit   char   sel   

本文系统来源：http://blog.51cto.com/wujianwei/2296662

内容总结

以上是互联网集市为您收集整理的MySQL之按月拆分主表并按月分表写入数据提高数据查询速度全部内容，希望文章能够帮你解决MySQL之按月拆分主表并按月分表写入数据提高数据查询速度所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/500487.html

来源：【匿名】