多表分区指南

我们有一个名为 tblLink 的主表，其中将有一个日期字段。tblLink 的主键在其他几个表中也将作为外键，其中一个表将是 tblMainData，随着时间的推移将有数百万行。所以我们只想保留 3 个月的数据。其余的我们想清除并保留它以生成历史报告。这个问题满谷歌都只谈单表分区，没谈多表分区。那么下面如何解决我的问题也是创建表和示例查询。

Some of the queries. 
Query 1 

SELECT DISTINCT tblLocationFrom.geoFenceName As locationFrom, tblLocationTo.geoFenceName As locationTo, 
tblLink.linkID,CAST(Date_Add(tblLink.dateTimeStartJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeStartJourney,  
CAST(Date_Add(tblLink.dateTimeEndJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney1,CAST(Date_Add(CAST(Date_Add(tblLink.dateTimeEnd, Interval '-08:00' hour_minute) AS CHAR) , Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney2, 
FROM tblLink 
JOIN tblGeoFence AS tblLocationFrom ON tblLink.locationFromID = tblLocationFrom.geoFenceID 
JOIN tblGeoFence AS tblLocationTo ON tblLink.locationToID = tblLocationTo.geoFenceID 
WHERE (dateTimeStartJourney between '".$b. "' And '".$e."' And tblLink.compID=$cID 

Query 2 

SELECT tblGeoFence.geoFenceName,     CAST(Date_Add(tblEventAlert.eventAlertDateTime , Interval '".$gmtValue."' hour_minute) AS CHAR) As eventAlertDateTime 
FROM tblEventAlert 
LEFT JOIN tblGeoFence ON tblEventAlert.geoFenceID=tblGeoFence.geoFenceID 
WHERE tblEventAlert.link=".$lID." Order By tblEventAlert.eventAlertDateTime Asc"; 

Create table statements. 

CREATE TABLE IF NOT EXISTS `tblLink` ( 
`linkID` int(5) NOT NULL, 
`compID` int(5) NOT NULL, 
`vehicleID` int(5) NOT NULL, 
`deviceID` int(5) NOT NULL, 
`locationFromID` int(5) NOT NULL, 
`locationToID` int(5) NOT NULL, 
`employeeIDInsert` int(5) NOT NULL, 
`dateTimeInsert` datetime NOT NULL, 
`dateTimeStartJourney` datetime NOT NULL, 
`dateTimeEnd` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 
`dateTimeEndJourney` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 
`linkStatus` enum('a','d','e','m') NOT NULL, 
PRIMARY KEY (`linkID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 


CREATE TABLE IF NOT EXISTS `tblEmailLog` ( 
`emailLogID` int(11) NOT NULL AUTO_INCREMENT, 
`compID` smallint(6) NOT NULL, 
`linkID` int(11) NOT NULL DEFAULT '0', 
`userID` smallint(6) NOT NULL, 
`alertCodeID` tinyint(4) NOT NULL, 
`eventAlertID` int(11) NOT NULL, 
`userEmail` varchar(100) NOT NULL, 
`alertDateTime` datetime NOT NULL, 
`alertInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (`emailLogID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 


CREATE TABLE IF NOT EXISTS `tblEventAlert` ( 
`eventAlertID` int(11) NOT NULL AUTO_INCREMENT, 
`compID` int(5) NOT NULL, 
`linkID` int(11) NOT NULL DEFAULT '0', 
`mainDataID` int(5) NOT NULL, 
`vehicleID` int(5) NOT NULL, 
`geoFenceID` int(5) NOT NULL, 
`eventAlertDateTime` datetime NOT NULL, 
`eventAlertSentEmail` varchar(50) DEFAULT NULL, 
`eventAlertMessage` varchar(255) NOT NULL, 
PRIMARY KEY (`eventAlertID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 



CREATE TABLE IF NOT EXISTS `tblMainData` ( 
`mainDataID` int(11) NOT NULL AUTO_INCREMENT, 
`linkID` int(5) NOT NULL, 
`header` varchar(3) NOT NULL, 
`deviceSerialNumber` varchar(20) NOT NULL, 
`latitude` double NOT NULL, 
`longitude` double NOT NULL, 
`speed` float NOT NULL, 
`course` int(3) NOT NULL, 
`dateTimer` datetime NOT NULL, 
`gpsDateTime` datetime NOT NULL, 
`insertDateTime` datetime NOT NULL, 
`odoMeter` float NOT NULL DEFAULT '0', 
`driverID` int(5) NOT NULL, 
`eventAlertID` int(11) NOT NULL DEFAULT '0', 
`mainDataInsertDateTime` datetime NOT NULL, 
`gpsString` varchar(450) NOT NULL, 
PRIMARY KEY (`mainDataID`), 
KEY `dateTime` (`dateTimer`), 
KEY `linkID` (`linkID`), 
KEY `eventAlertID` (`eventAlertID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 


CREATE TABLE IF NOT EXISTS `tblSubData` ( 
`subDataID` int(11) NOT NULL AUTO_INCREMENT, 
`mainDataID` int(11) NOT NULL, 
`linkID` int(11) NOT NULL, 
`eventAlertID` int(11) NOT NULL, 
`deviceSerialNumber` varchar(20) NOT NULL, 
`subdeviceSerialNumber` varchar(20) NOT NULL, 
`dateTimer` datetime NOT NULL, 
`eventType` varchar(2) NOT NULL 
PRIMARY KEY (`subDataID`), 
KEY `mainDataID` (`mainDataID`), 
KEY `linkID` (`linkID`), 
KEY `eventAlertID` (`eventAlertID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;