mysql 關(guān)于大數(shù)據(jù)量日志表的優(yōu)化過程
我們知道很多網(wǎng)站都會有關(guān)于記錄網(wǎng)站搜索的日志表，用于記錄會員的基本登錄信息，用于后期數(shù)據(jù)分析或者防攻擊使用，現(xiàn)在我們有一張表logs，每天產(chǎn)生大概60萬的數(shù)據(jù)。
環(huán)境介紹：10.0.21-MariaDB-log
我們的業(yè)務(wù)主要分成三塊：
1）查詢當天的logs；
2）查看最近2個月的logs；
3）查看最近一年的logs.
我們當前的表是不管點那個按鈕查詢的都是這個表logs,這個表的數(shù)據(jù)一直存在，直到收到反饋報錯504 timeout，然后才去處理這個表，就是刪除一年之前的數(shù)據(jù)，倆月就得處理一次，很蛋疼，于是想著優(yōu)化下。
整體的思路就是：分表和分區(qū)表的結(jié)合。
1）查詢當天和近兩個月的數(shù)據(jù)的業(yè)務(wù)去查詢logs;
2）查看最近一年的數(shù)據(jù)的業(yè)務(wù)去查詢另一個表logs_oneyear，
3）然后利用存儲過程+event的方式定時去處理logs和logs_oneyear，使這兩個表里始終只保留最近2個月以及近10個月的數(shù)據(jù)，這樣保證了可以查詢到一年數(shù)據(jù)的目的。
具體處理過程如下：
一：首先處理保存2個月的表logs，打算按時間分區(qū)，7天一個分區(qū)，利用存儲過程logs_twomonths_procedure刪除一個老分區(qū)，創(chuàng)建一個新分區(qū)，并且把刪除的老分區(qū)的數(shù)據(jù)，插入到logs_oneyear，然后做一個event每7天定時執(zhí)行，
1）創(chuàng)建表logs的語句如下，這里需要注意的是針對timestamp類型的時間屬性需要借助函數(shù)unix_timestamp才能創(chuàng)建分區(qū)表，但是DATETIME 類型不需要。
CREATE TABLE `logs` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`member_id` 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 1',
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('2017-02-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20170207 VALUES LESS THAN (unix_timestamp('2017-02-07 00:00:00')) ENGINE = MyISAM,
PARTITION p20170214 VALUES LESS THAN (unix_timestamp('2017-02-14 00:00:00')) ENGINE = MyISAM,
PARTITION p20170221 VALUES LESS THAN (unix_timestamp('2017-02-21 00:00:00')) ENGINE = MyISAM,
PARTITION p20170228 VALUES LESS THAN (unix_timestamp('2017-02-28 00:00:00')) ENGINE = MyISAM,
PARTITION p20170307 VALUES LESS THAN (unix_timestamp('2017-03-07 00:00:00')) ENGINE = MyISAM,
PARTITION p20170314 VALUES LESS THAN (unix_timestamp('2017-03-14 00:00:00')) ENGINE = MyISAM,
PARTITION p20170321 VALUES LESS THAN (unix_timestamp('2017-03-21 00:00:00')) ENGINE = MyISAM,
PARTITION p20170328 VALUES LESS THAN (unix_timestamp('2017-03-28 00:00:00')) ENGINE = MyISAM,
PARTITION p20170404 VALUES LESS THAN (unix_timestamp('2017-04-04 00:00:00')) ENGINE = MyISAM,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM);
2）如下創(chuàng)建存儲過程logs_twomonths_procedure的語句：
MariaDB [log]> drop procedure if exists logs_twomonths_procedure;
MariaDB [log]>
DELIMITER $$
create procedure logs_twomonths_procedure()
begin
/* 事務(wù)回滾*/
declare exit handler for sqlexception rollback;
start TRANSACTION;
/* 到系統(tǒng)表查出這個表的最大分區(qū)，得到最大分區(qū)的日期。在創(chuàng)建分區(qū)的時候，名稱就以日期格式存放，方便后面維護，p12_name是自己定義變量，注意需要條件partition_name!='pmax'*/
select REPLACE(partition_name,'p','') into @P12_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='logs' and partition_name!='pmax' order by partition_ordinal_position DESC limit 1;
/*直接加7天，就是得到7天之后的日期。 +0 是為了把日期都格式化成YYYYMMDD這樣的格式*/
set @Max_date= date(DATE_ADD(@P12_Name+0, INTERVAL 7 DAY))+0;
/* 修改表，在最大分區(qū)的后面增加一個分區(qū)，時間范圍添加7天 ,DATE函數(shù)是把20110101改成日期格式2011-01-01，語句中兩個單引號代表一個單引號 ,repare預(yù)處理固定語法 */
SET @s1=concat('ALTER TABLE logs_oneyear ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (unix_timestamp(''',date(@Max_date),''')))');
PREPARE hezi FROM @s1;
EXECUTE hezi ;
DEALLOCATE PREPARE hezi ;
/* 將最小分區(qū)中的數(shù)據(jù)插入到logs_oneyear表中，因為接下來要刪除這個最小分區(qū)會同時刪除分區(qū)內(nèi)的數(shù)據(jù)，慎重 */
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<''',date(@min_date),'''');
PREPARE hezi1 FROM @s2;
EXECUTE hezi1 ;
DEALLOCATE PREPARE hezi1 ;
/* 取出最小的分區(qū)的名稱，并刪除掉 */
select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='logs' order by partition_ordinal_position limit 1;
SET @s3=concat('ALTER TABLE logs DROP PARTITION ',@P0_Name);
PREPARE hezi3 FROM @s3;
EXECUTE hezi3 ;
DEALLOCATE PREPARE hezi3;
/* 提交 */
COMMIT ;
end;
$$
3）創(chuàng)建event,7天執(zhí)行一次，應(yīng)該在log庫下創(chuàng)建，
mysql的庫相當于oracle的schema,
MariaDB [log]> CREATE EVENT logs_Partition7
ON SCHEDULE
EVERY 7 day STARTS '2017-03-31 08:00:00'
DO
call logs_twomonths_procedure();
二：處理報錯10個月數(shù)據(jù)的那個表logs_oneyear.
1)創(chuàng)建logs_oneyear表的語句，半月一個分區(qū)
MariaDB [log]>drop table if exists logs_oneyear;
MariaDB [log]>CREATE TABLE `logs_oneyear` (
`disc` int(11) NOT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`member_id` int(11) DEFAULT NULL,
`jsession` int(11) DEFAULT NULL,
`ip` bigint(20) DEFAULT NULL,
`keyword` varchar(255) DEFAULT NULL,
`page` int(11) DEFAULT NULL,
`rp` int(11) DEFAULT NULL,
`area` varchar(255) DEFAULT NULL,
`category` varchar(255) DEFAULT NULL,
`fenlei` int(11) DEFAULT NULL,
`shijianduan` int(11) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`agency_company` varchar(55) DEFAULT NULL,
`biaoxun_type` int(11) DEFAULT NULL,
`caigou_type` int(11) DEFAULT NULL,
`shijian` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`total` int(11) DEFAULT NULL,
`xmxx_jieduan` int(11) DEFAULT NULL,
`zhanguan` varchar(255) DEFAULT NULL,
`main_product` varchar(255) DEFAULT NULL,
`fazx_fenlei1` int(11) DEFAULT NULL,
`fazx_fenlei2` int(11) DEFAULT NULL,
`jiaoyileixing` int(11) DEFAULT NULL,
`mailid` bigint(20) DEFAULT NULL,
`referer` int(11) DEFAULT NULL,
`search_id` bigint(20) DEFAULT NULL,
`info_id` bigint(20) DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '0' COMMENT 'When the page(html) is open ,this attribute will set 1',
PRIMARY KEY (`id`,`shijian`),
KEY `logs_disc_idx` (`disc`),
KEY `logs_member_idx` (`member_id`,`jsession`,`shijian`),
KEY `logs_ip_idx` (`ip`),
KEY `logs_shijian_idx` (`shijian`),
KEY `logs_mail_idx` (`mailid`)
) ENGINE=MyISAM AUTO_INCREMENT=847368831411249480 DEFAULT CHARSET=utf8
PARTITION BY RANGE(unix_timestamp(shijian))
(
PARTITION p20160501 VALUES LESS THAN (unix_timestamp('2016-05-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20160515 VALUES LESS THAN (unix_timestamp('2016-05-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20160601 VALUES LESS THAN (unix_timestamp('2016-06-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20160615 VALUES LESS THAN (unix_timestamp('2016-06-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20160701 VALUES LESS THAN (unix_timestamp('2016-07-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20160715 VALUES LESS THAN (unix_timestamp('2016-07-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20160801 VALUES LESS THAN (unix_timestamp('2016-08-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20160815 VALUES LESS THAN (unix_timestamp('2016-08-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20160901 VALUES LESS THAN (unix_timestamp('2016-09-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20160915 VALUES LESS THAN (unix_timestamp('2016-09-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20161001 VALUES LESS THAN (unix_timestamp('2016-10-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20161015 VALUES LESS THAN (unix_timestamp('2016-10-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20161101 VALUES LESS THAN (unix_timestamp('2016-11-01 00:00:00')) ENGINE = MyISAM ,
PARTITION p20161115 VALUES LESS THAN (unix_timestamp('2016-11-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20161201 VALUES LESS THAN (unix_timestamp('2016-12-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20161215 VALUES LESS THAN (unix_timestamp('2016-12-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20170101 VALUES LESS THAN (unix_timestamp('2017-01-01 00:00:00')) ENGINE = MyISAM,
PARTITION p20170115 VALUES LESS THAN (unix_timestamp('2017-01-15 00:00:00')) ENGINE = MyISAM,
PARTITION p20170201 VALUES LESS THAN (unix_timestamp('2017-02-01 00:00:00'))
ENGINE = MyISAM,
PARTITION p20170215 VALUES LESS THAN (unix_timestamp('2017-02-15 00:00:00'))
ENGINE = MyISAM,
PARTITION pmax1 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM));
);
2）創(chuàng)建存儲過程logs_oneyear_procedure，
MariaDB [log]>drop procedure if exists logs_oneyear_procedure;
DELIMITER $$
MariaDB [log]>create procedure logs_oneyear_procedure()
begin
declare exit handler for sqlexception rollback;
start TRANSACTION;
select REPLACE(partition_name,'p','') into @liu_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='logs_oneyear' and partition_name!='pmax1' order by partition_ordinal_position DESC limit 1;
/* 判斷最大分區(qū)的時間段，如果是15號，那么根據(jù)情況需要加,14,15,16，17天,，如果是1號就直接加14即可，這樣的目的就是保證產(chǎn)生的分區(qū)間隔是每個月的15號和下個月的的1號，說白了 就是為了規(guī)范*/
IF (DAY(@P12_Name)=15) THEN
CASE day(LAST_DAY(@P12_name))
WHEN 31 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 17 DAY))+0 ;
WHEN 30 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 16 DAY))+0 ;
WHEN 29 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 15 DAY))+0 ;
WHEN 28 THEN set @Max_date= date(DATE_ADD(@P12_Name+0,INTERVAL 14 DAY))+0 ;
END CASE;
ELSE
set @Max_date= date(DATE_ADD(@P12_Name+0, INTERVAL 14 DAY))+0;
END IF;
SET @s1=concat('ALTER TABLE logs_oneyear ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (unix_timestamp(''',date(@Max_date),''')))');
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
/* 將最小分區(qū)中的數(shù)據(jù)插入到歷史庫下的備份表logs_back_start20160501中，因為接下來要刪除這個最小分區(qū)會同時刪除分區(qū)內(nèi)的數(shù)據(jù)，慎重 */
select REPLACE(partition_name,'p','') into @min_date from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='logs_oneyear' order by partition_ordinal_position asc limit 1;
SET @s2=concat('insert into history.logs_back_start20160501 select * from logs_oneyear where shijian<''',date(@min_date),'''');
PREPARE stmt3 FROM @s2;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='logs_oneyear' order by partition_ordinal_position limit 1;
SET @s=concat('ALTER TABLE logs_oneyear DROP PARTITION ',@P0_Name);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
COMMIT ;
end;
$$
3)創(chuàng)建event，每隔15天執(zhí)行一次。
計劃任務(wù)代碼：
CREATE EVENT logs_Partition_oneyear
ON SCHEDULE
EVERY 15 day STARTS '2017-04-01 22:30:00'
DO
call logs_oneyear_procedure();
最后把相應(yīng)的數(shù)據(jù)insert到logs和logs_oneyear即可，至此處理結(jié)束，觀察一段時間，性能果然有所提升，需要注意的是，如果查詢的語句中where條件中沒有使用shijian字段，那么表做分區(qū)性能提升不大，還影響了insert的效率，所以具體該不該分區(qū)，或者應(yīng)該使用什么字段分區(qū)，需要自己去探究。
引申內(nèi)容：
1）關(guān)于時間字段datetime和timestamp的異同點，如果可以盡量使用TIMESTAMP：
相同
顯示
TIMESTAMP列的顯示格式與DATETIME列相同。換句話說，顯示寬度固定在19字符，并且格式為YYYY-MM-DD HH:MM:SS。
不同
范圍
datetime 以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支持的范圍為'1000-01-01 00:00:00'到'9999-12-31 23:59:59'TIMESTAMP值不能早于1970或晚于2037
儲存
TIMESTAMP
1.4個字節(jié)儲存（Time stamp value is stored in 4 bytes）
2.值以UTC格式保存（ it stores the number of milliseconds）
3.時區(qū)轉(zhuǎn)化 ，存儲時對當前的時區(qū)進行轉(zhuǎn)換，檢索時再轉(zhuǎn)換回當前的時區(qū)。
datetime
1.8個字節(jié)儲存（8 bytes storage）
2.實際格式儲存（Just stores what you have stored and retrieves the same thing which you have stored.）
3.與時區(qū)無關(guān)（It has nothing to deal with the TIMEZONE and Conversion.）
2）關(guān)于mysql預(yù)處理語句
其用法十分簡單，其中stmt_name 是隨便起的名字。
PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name
[USING @var_name [, @var_name] ...] -
{DEALLOCATE | DROP} PREPARE stmt_name
使用PAREPARE STATEMENT可以減少每次執(zhí)行SQL的語法分析，比如用于執(zhí)行帶有WHERE條件的SELECT和DELETE，或者UPDATE，或者INSERT，只需要每次修改變量值即可。同樣可以防止SQL注入，參數(shù)值可以包含轉(zhuǎn)義符和定界符。適用在應(yīng)用程序中，或者SQL腳本中均可
