mysql关于大数据量日志表的优化过程 - 行业资讯 - 肥雀云

　　我们知道很多网站都会有关于记录网站搜索的日志表,用于记录会员的基本登录信息,用于后期数据分析或者防攻击使用,现在我们有一张表日志,每天产生大概60多万的数据。　　　　　　环境介绍:10.0.21-MariaDB-log 　　　　　　我们的业务主要分成三块: 　　　　　　1)查询当天的日志; 　　　　　　2)查看最近2个月的日志; 　　　　　　3)查看最近一年的日志。　　　　　　我们当前的表是不管点那个按钮查询的都是这个表日志,这个表的数据一直存在,直到收到反馈报错504超时,然后才去处理这个表,就是删除一年之前的数据,俩月就得处理一次,很蛋疼,于是想着优化下。　　　　　　整体的思路就是:分表和分区表的结合。　　　　　　1)查询当天和近两个月的数据的业务去查询日志; 　　　　　　2)查看最近一年的数据的业务去查询另一个表logs_oneyear, 　　　　　　3)然后利用存储过程+事件的方式定时去处理日志和logs_oneyear,使这两个表里始终只保留最近2个月以及近10个月的数据,这样保证了可以查询到一年数据的目的。　　　　　　具体处理过程如下: 　　　　　　　　　　　　DATETIME 　　　　　　创建表“日志”( 　　　　　　“id”bigint (20) NOT NULL AUTO_INCREMENT, 　　　　　　“member_id”int(11)默认为空, 　　　　　　“jsession”int(11)默认为空, 　　　　　　“知识产权”的bigint(20)默认为空, 　　　　　　“shijian”零违约CURRENT_TIMESTAMP CURRENT_TIMESTAMP更新时间戳, 　　　　　　“状态”int (11) NOT NULL默认“0”评论“打开网页(html)时,该属性将1 ', 　　　　　　主键(“id”,“shijian”), 　　　　　　关键“logs_member_idx”(‘member_id’,‘jsession’,‘shijian’), 　　　　　　关键“logs_ip_idx”(“知识产权”), 　　　　　　关键“logs_shijian_idx”(“shijian”) 　　　　　　)引擎=MyISAM AUTO_INCREMENT=847368831411249480默认字符集=utf8 　　　　　　范围分区((shijian)) 　　　　　　（　　　　　　分区p20170201值小于((' 2017-02-01 '))=MyISAM引擎, 　　　　　　分区p20170207值小于((' 2017-02-07 '))=MyISAM引擎, 　　　　　　分区p20170214值小于((' 2017-02-14 '))=MyISAM引擎, 　　　　　　分区p20170221值小于((' 2017-02-21 '))=MyISAM引擎, 　　　　　　分区p20170228值小于((' 2017-02-28 '))=MyISAM引擎, 　　　　　　分区p20170307值小于((' 2017-03-07 '))=MyISAM引擎, 　　　　　　分区p20170314值小于((' 2017-03-14 '))=MyISAM引擎, 　　　　　　分区p20170321值小于((' 2017-03-21 '))=MyISAM引擎, 　　　　　　分区p20170328值小于((' 2017-03-28 '))=MyISAM引擎, 　　　　　　分区p20170404值小于((' 2017-04-04 '))=MyISAM引擎, 　　　　　　分区pmax值小于(MAXVALUE)=MyISAM引擎); 　　　　　　　　　　　　MariaDB[记录]比;下降过程中如果存在logs_twomonths_procedure; 　　　　　　MariaDB[记录]比; 　　　　　　分隔符$ $ 　　　　　　创建过程logs_twomonths_procedure () 　　　　　　开始/*事务回滚*/sqlexception宣布退出处理程序回滚; 　　　　　　开始事务;/*到系统表查出这个表的最大分区,得到最大分区的日期。在创建分区的时候,名称就以日期格式存放,方便后面维护,p12_name是自己定义变量、注意需要条件*/选择替换(partition_name,“p”)从INFORMATION_SCHEMA @P12_Name。分区,TABLE_SCHEMA=叭罩尽焙蛅able_name=叭罩尽焙蚾rder by partition_ordinal_position DESC限制1;/*直接加7天,就是得到7天之后的日期。+ 0是为了把日期都格式化成名称这样的格式*/设置@Max_date=日期(DATE_ADD (@P12_Name + 0,间隔7天)+ 0;/*修改表,在最大分区的后面增加一个分区,时间范围添加7天,日期函数是把20110101改成日期格式2011-01-01,语句中两个单引号代表一个单引号,返修预处理固定语法*/设置@s1=concat (“ALTER TABLE logs_oneyear添加分区(分区p、@Max_date的值小于((“”、日期(@Max_date))))); 　　　　　　准备从@s1 hezi; 　　　　　　执行总裁hezi; 　　　　　　释放准备hezi;/*将最小分区中的数据插入到logs_oneyear表中,因为接下来要删除这个最小分区会同时删除分区内的数据,慎重*/选择替换(partition_name,“p”)从INFORMATION_SCHEMA @min_date。分区,TABLE_SCHEMA=叭罩尽焙蛅able_name=叭罩尽泵頿artition_ordinal_position asc限制1; 　　　　　　设置@s2=concat(“插入logs_oneyear select *从日志shijian<薄⑷掌?@min_date) " "); 　　　　　　准备从@s2 hezi1; 　　　　　　执行hezi1; 　　　　　　释放准备hezi1;/*取出最小的分区的名称,并删除掉*/从INFORMATION_SCHEMA选择partition_name成@P0_Name。分区,TABLE_SCHEMA=叭罩尽焙蛅able_name=叭罩尽泵頿artition_ordinal_position限制1; 　　　　　　设置@s3=concat (ALTER TABLE日志删除分区,@P0_Name); 　　　　　　准备从@s3 hezi3; 　　　　　　执行hezi3; 　　　　　　释放准备hezi3;/*提交*/提交; 　　　　　　结束; 　　　　　　$ $ 　　　　　　3)创建事件,7天执行一次,应该在日志库下创建,mysql的库相当于甲骨文的模式, 　　　　　　MariaDB[记录]比;创建事件logs_Partition7 　　　　　　按计划　　　　　　每7天开始“2017-03-31 08:00:00” 　　　　　　做　　　　　　调用logs_twomonths_procedure (); 　　　　　　二:处理报错10个月数据的那个表logs_oneyear。　　　　　　1)创建logs_oneyear表的语句,半月一个分区　　　　　　MariaDB[登录]在删除表如果存在logs_oneyear; 　　　　　　MariaDB(日志)在创建表“logs_oneyear”( 　　　　　　“盘”int(11)不是零, 　　　　　　“id”bigint (20) NOT NULL AUTO_INCREMENT, 　　　　　　“member_id”int(11)默认为空, 　　　　　　“jsession”int(11)默认为空, 　　　　　　“知识产权”的bigint(20)默认为空, 　　　　　　“关键字”varchar(255)默认为空, 　　　　　　“页面”int(11)默认为空, 　　　　　　rp的int(11)默认为空, 　　　　　　“区域”的varchar(255)默认为空, 　　　　　　“类别”varchar(255)默认为空, 　　　　　　“fenlei”int(11)默认为空, 　　　　　　“shijianduan”int(11)默认为空, 　　　　　　“类型”int(11)默认为空, 　　　　　　“agency_company”varchar(55)默认为空, 　　　　　　“biaoxun_type”int(11)默认为空, 　　　　　　“caigou_type”int(11)默认为空, 　　　　　　“shijian”零违约CURRENT_TIMESTAMP CURRENT_TIMESTAMP更新时间戳, 　　　　　　“总”int(11)默认为空, 　　　　　　“xmxx_jieduan”int(11)默认为空, 　　　　　　“zhanguan”varchar(255)默认为空, 　　　　　　“main_product”varchar(255)默认为空, 　　　　　　“fazx_fenlei1”int(11)默认为空, 　　　　　　“fazx_fenlei2”int(11)默认为空, 　　　　　　“jiaoyileixing”int(11)默认为空, 　　　　　　mailid的bigint(20)默认为空, 　　　　　　“推荐人”int(11)默认为空, 　　　　　　search_id的bigint(20)默认为空, 　　　　　　info_id的bigint(20)默认为空, 　　　　　　“状态”int (11) NOT NULL默认“0”评论“打开网页(html)时,该属性将1 ', 　　　　　　主键(“id”,“shijian”), 　　　　　　关键“logs_disc_idx”(“盘”), 　　　　　　关键“logs_member_idx”(‘member_id’,‘jsession’,‘shijian’), 　　　　　　关键“logs_ip_idx”(“知识产权”), 　　　　　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null