对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。
MySQL实现分区的方式也意味着索引也是按照分区的子表定义， 没有全局索引 。
分区的意思是指将同一表中不同行的记录分配到不同的物理文件中 ，几个分区就有几个.idb文件。MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。
1、可以让单表 存储更多的数据 。
2、 分区表的数据更容易维护 ，可以通过删除与那些数据有关的分区，更容易删除数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等 *** 作。
3、部分查询能够从查询条件确定只落在少数分区上， 查询速度会很快 。
4、通过跨多个磁盘来分散数据查询，来 获得更大的查询吞吐量 。
要使定时事件起作用，MySQL的常量GLOBAL event_scheduler必须为on或者是1。
1、查看scheduler的当前状态：
2、修改scheduler状态为打开(0:off , 1:on)：
3、临时打开定时器(四种方法)：
4、永久生效的方法，修改配置文件my.cnf
5、临时开启某个事件
6、临时关闭某个事件
1、UNION2、若是innodb分表，则可以用merge处理。
直接搞一张专门针对统计数据用的汇总表
如果可能的话，不要采用分表的设计，采用表分区，这样就对于查询就不需要特殊处理了。规划好索引，性能应该不会有问题。
我正好有楼主类似的需求，每个季度为几个表增加一个分区，表的基本名称是在一个叫设备类型的表里，每天计划执行一个过程，在过程里从系统表中判断是否已经创建了相关的分区，如果没创建就创建它
楼主可以参考一下，记得在my.ini 文件里配置event_scheduler=on
/**定时每天检查各个设备类型的历史数据表，如果历史数据表的所在分区已经
接近当前日期，则为此设备类型追加分区
*/
-- 得到按月分区的日期值
delimiter
drop function if exists fnGetPartitionDateForMonth
delimiter
create function fnGetPartitionDateForMonth() returns INT
begin
declare v_today datetime default date_add(now(), INTERVAL 2 month)
return year(v_today) * 100 + month(v_today)
end
-- 得到按季度分区的日期值
delimiter
drop function if exists fnGetPartitionDateForQuarter
delimiter
create function fnGetPartitionDateForQuarter() returns int
begin
declare v_today datetime default date_add(now(), interval 3 month)
declare v_month int
set v_month = month(v_today)
if v_month = 1 or v_month = 2 or v_month = 3 then
set v_today = DATE_ADD(v_today, INTERVAL (4 - v_month) month)
elseif v_month = 4 or v_month = 5 or v_month = 6 THEN
set v_today = DATE_ADD(v_today, INTERVAL (7 - v_month) month)
elseif v_month = 7 or v_month = 8 or v_month = 9 THEN
set v_today = date_add(v_today, INTERVAL (10 - v_month) month)
ELSE
set v_today = date_add(v_today, INTERVAL (13 - v_month) month)
end if
return year(v_today) * 100 + month(v_today)
end
-- 得到按半年分区的日期值
delimiter
drop function if exists fnGetPartitionDateForHalfYear
delimiter
create function fnGetPartitionDateForHalfYear() returns int
begin
declare v_today datetime default date_add(now(), interval 6 month)
declare v_month int
set v_month = month(v_today)
if v_month <= 6 THEN
set v_today = date_add(v_today, INTERVAL (7 - v_month) month)
else
set v_today = DATE_ADD(v_today, INTERVAL (13 - v_month) month)
end if
return year(v_today) * 100 + month(v_today)
end
-- 维护按年分区
delimiter
drop function if exists fnGetPartitionDateForYear
delimiter
create function fnGetPartitionDateForYear() returns int
begin
declare v_today datetime default date_add(now(), INTERVAL 2 year)
return year(v_today) * 100
end
delimiter
drop procedure if exists spMaintainPartitions
delimiter
create procedure spMaintainPartitions()
BEGIN
declare v_sql varchar(2000)
declare v_cnt int
declare v_deviceTypeId int
declare v_tablename varchar(50)
declare v_tablename_analog varchar(50)
declare v_tablename_digital varchar(50)
declare v_partitionType int
declare v_fileDir varchar(1000)
declare v_tablenames varchar(1000) default ''
declare v_intDate int
declare v_partitionName varchar(100)
declare done int default 0
declare c_deviceType cursor
for select Id, TableName, PartitionType, DataFileDir
from tbDeviceType
where Generated = 1
declare continue handler for not found set done = 1
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), 'spMaintainPartitions start......')
open c_deviceType
deviceType_loop: LOOP
fetch c_deviceType into v_deviceTypeId, v_tablename, v_partitionType, v_fileDir
set v_fileDir = replace(v_fileDir, '\\', '/')
if locate(':', v_fileDir) > 0 and locate(':/', v_fileDir) = 0 then
set v_fileDir = replace(v_fileDir, ':', ':/')
end if
if done = 1 then
leave deviceType_loop
end if
set v_intDate = null
if v_partitionType = 1 then
set v_intDate = fnGetPartitionDateForMonth()
ELSEIF v_partitionType = 2 THEN
set v_intDate = fnGetPartitionDateForQuarter()
ELSEIF v_partitionType = 3 then
set v_intDate = fnGetPartitionDateForHalfYear()
elseif v_partitionType = 4 then
set v_intDate = fnGetPartitionDateForYear()
end if
if v_intDate is null then
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), Concat('DeviceTypeId = ', cast(v_deviceTypeId As char(10)), ' did not define paritition schedule'))
else
set v_partitionName = concat('p', cast(v_intDate as char(6)))
-- 模拟量表
set v_tablename_analog = concat(v_tablename, '_Analog')
select count(*) into v_cnt
from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_analog
if v_cnt > 0 then
select count(*) into v_cnt
from
information_schema.`PARTITIONS`
where
TABLE_SCHEMA = database() and table_name = v_tablename_analog and partition_name = v_partitionName
if v_cnt = 0 then
set v_sql = CONCAT('alter table ', v_tablename_analog, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')')
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), concat('sql = ''', v_sql))
set @sql = v_sql
prepare cmd from @sql
execute cmd
deallocate prepare cmd
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), concat('execute complete: ', v_sql))
end if
end if
-- 数字量表
set v_tablename_digital = concat(v_tablename, '_Digital')
select count(*) into v_cnt
from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_digital
if v_cnt > 0 then
select count(*) into v_cnt
from
information_schema.`PARTITIONS`
where
TABLE_SCHEMA = database() and table_name = v_tablename_digital and partition_name = v_partitionName
if v_cnt = 0 then
set v_sql = CONCAT('alter table ', v_tablename_digital, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')')
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), concat('sql = ''', v_sql))
set @sql = v_sql
prepare cmd from @sql
execute cmd
deallocate prepare cmd
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), concat('execute complete: ', v_sql))
end if
end if
end if
end loop deviceType_loop
close c_deviceType
END
delimiter
drop event if exists e_DataPartitionMaintain
create event e_DataPartitionMaintain
on SCHEDULE every 60 Second
on completion PRESERVE
do call spMaintainPartitions()
set global event_scheduler = on
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）