Shulou(Shulou.com)06/01 Report--

We know that many websites have log tables about recording site searches, which are used to record members' basic login information for post-data analysis or anti-attack use. now we have a table logs, which produces about 600000 of the data every day. Environment introduction: 10.0.21-MariaDB-log our business is mainly divided into three parts: 1) query the logs; of the day 2) View the logs; of the last 2 months 3) View the logs of the latest year. Our current table is to query this table logs no matter which button is clicked. The data of this table always exists until we receive an error of 504 timeout. Then we do not deal with this table until we delete the data from a year ago. It hurts every two months, so I want to optimize it. The overall idea is: the combination of sub-table and partition table. 1) query the data of the same day and nearly two months to query logs; 2) query the data of the last year to query another table logs_oneyear, 3) then use the stored procedure + event method to regularly process logs and logs_oneyear, so that only the last 2 months and nearly 10 months of data are always retained in the two tables, thus ensuring the purpose of querying the data for one year. The specific processing process is as follows: first, deal with the table logs, which has been saved for 2 months, intend to partition by time, one partition in 7 days, use the stored procedure logs_twomonths_procedure to delete an old partition, create a new partition, and insert the deleted data from the old partition into logs_oneyear, and then do an event every 7 days to execute regularly. 1) the statement to create the table logs is as follows It is important to note that the time attribute for the timestamp type requires the help of the function unix_timestamp to create the partition table, but not for the DATETIME type. CREATE TABLE `logs` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `jsession` int (11) DEFAULT NULL, `jsession` int (11) DEFAULT NULL, `ip`bigint (20) DEFAULT NULL, `shijian` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `status` int (11) NOT NULL DEFAULT'0' COMMENT 'When the page (html) is open, this attribute will set 1bike, PRIMARY KEY (`id`, `shijian`), KEY `logs_member_ idx` (`member_ id`, `jsession`, `shijian`), KEY `logs_ip_ idx` (`ip`) KEY `logs_shijian_ idx` (`shijian`) ENGINE=MyISAM AUTO_INCREMENT=847368831411249480 DEFAULT CHARSET=utf8 PARTITION BY RANGE (unix_timestamp (shijian)) (PARTITION p20170201 VALUES LESS THAN (unix_timestamp (unix_timestamp ('2017-02-01 0000 ENGINE=MyISAM AUTO_INCREMENT=847368831411249480 DEFAULT CHARSET=utf8 PARTITION BY RANGE)) ENGINE=MyISAM, PARTITION p20170207 VALUES LESS THAN (unix_timestamp (' 2017-02-07 00 ENGINE=MyISAM AUTO_INCREMENT=847368831411249480 DEFAULT CHARSET=utf8 PARTITION BY RANGE ('2017-02-07 00 ENGINE=MyISAM AUTO_INCREMENT=847368831411249480 DEFAULT CHARSET=utf8 PARTITION BY RANGE)) ENGINE=MyISAM, PARTITION p20170214 VALUES LESS THAN (unix_timestamp (' 2017-02-14 00 PLG 0000') ENGINE=MyISAM) PARTITION p20170221 VALUES LESS THAN (unix_timestamp ('2017-02-21 0000 unix_timestamp) ENGINE = MyISAM, PARTITION p20170228 VALUES LESS THAN (unix_timestamp (' 2017-02-28 0000unix_timestamp)) ENGINE = MyISAM, PARTITION p20170307 VALUES LESS THAN (unix_timestamp ('2017-03-070000unix_timestamp (' 2017-03-0700001')) ENGINE = MyISAM, PARTITION p20170314 VALUES LESS THAN (unix_timestamp ('2017-03-14 00001)) ENGINE = MyISAM PARTITION p20170321 VALUES LESS THAN (unix_timestamp ('2017-03-21 00) ENGINE = MyISAM, PARTITION p20170328 VALUES LESS THAN (unix_timestamp (' 2017-03-28 00 unix_timestamp)) ENGINE = MyISAM, PARTITION p20170404 VALUES LESS THAN (unix_timestamp ('2017-04-04 00 unix_timestamp) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) 2) the statement to create the stored procedure logs_twomonths_procedure is as follows: MariaDB [log] > drop procedure if exists logs_twomonths_procedure; MariaDB [log] > DELIMITER $$create procedure logs_twomonths_procedure () begin / * transaction rollback * / declare exit handler for sqlexception rollback; start TRANSACTION; / * to the system table to find out the maximum partition of the table and get the date of the maximum partition. When you create a partition, the name is stored in date format for easy maintenance. P12_name defines its own variables. Note that you need the condition partitionpartitionpartitionnameplates / select REPLACE (partition_name,'p','') into @ P12_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='logs' and partitionpartitionnameplates to specify pmax' order by partition_ordinal_position DESC limit 1. / * add 7 days directly, which is the date after 7 days. + 0 is to format the dates into a format like YYYYMMDD * / set @ Max_date= date (DATE_ADD (@ P12_Name+0, INTERVAL 7 DAY)) + 0 / * modify the table by adding a partition after the maximum partition and adding a time range of 7 days. The DATE function is to change 20110101 to the date format 2011-01-01, two single quotes in the statement represent a single quote, and the repare preprocessing fixed syntax * / SET @ s1=concat ('ALTER TABLE logs_oneyear ADD PARTITION (PARTITION pairing dint Maxdatedate' VALUES LESS THAN (unix_timestamp ('', date (@ Max_date),'')')) PREPARE hezi FROM @ S1; EXECUTE hezi; DEALLOCATE PREPARE hezi; / * insert the data from the smallest partition into the logs_ oneyeartable, because the next deletion of the minimum partition will also delete the data in the partition, be careful * / select REPLACE (partition_name,'p','') into @ min_date from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='logs' order by partition_ordinal_position asc limit 1; SET @ s2=concat ('insert into logs_oneyear select * from logs where shijian

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.