mysql数据库分表

需求是把一个数据量大的隧道表数据分成日表，月表。
步骤1.
使用定时任务定时创建表
application-base.xml

<!-- 定时执行创建cisco_tunnel分表-start-->
	 <bean id="ciscoTunnelCreateJob" class="com.sdwan.task.CiscoTunnelPartition"/>  
    <bean id="ciscoTunnelCreateJobMethod" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">  
	    <property name="targetObject">
	        <ref bean="ciscoTunnelCreateJob"/>
	    </property>  
	    <property name="targetMethod">
	        <value>execute</value>  
	    </property>
	    <property name="concurrent">
        	<value>false</value>  
   		</property>
	</bean> 
	<bean id="ciscoTunnelCreateJobBean" class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">  
	    <property name="jobDetail" ref="ciscoTunnelCreateJobMethod"></property>  
	    <property name="cronExpression" value="0 8 10 * * ? * ? "></property>	    
	</bean> 

步骤二
新建CiscoTunnelPartition Task类。

public class CiscoTunnelPartition {

    private static Logger logger = LoggerFactory.getLogger(CiscoTunnelPartition.class);
	
    @Autowired
    private CiscoTunnelService ciscoTunnelService;
    
    protected void execute() throws Exception { 
    	SimpleDateFormat monthdf = new SimpleDateFormat("yyyyMM");
    	Calendar c = Calendar.getInstance();
    	//获取现在日期并格式化为年月
    	//String month= monthdf.format(c.getTime());
    	//获取当前月的下一个月
    	int addMonth = 1;
    	c.add(Calendar.MONTH, addMonth);
    	String nextMonth =monthdf.format(c.getTime());
    	
    	
    	try {	
    		//动态拼接表名
			StringBuilder sb=new StringBuilder();
			sb.append("cisco_tunnel_");
			sb.append(nextMonth);
			String tName=sb.toString();
			ciscoTunnelService.updateCreateCiscoTunnel(tName);
			logger.info("创建成功");
		} catch (Exception e) {
			logger.error("创建失败", e); 
		}
    }
   }

步骤三
插入月表数据
新建CiscoTunnelPartitionInsert task

public class CiscoTunnelPartitionInsert {

	private static Logger logger = LoggerFactory.getLogger(CiscoTunnelPartitionInsert.class);
	
    @Autowired
    private CiscoTunnelService ciscoTunnelService;
    
    protected void execute() throws Exception { 
    	SimpleDateFormat ydf=new SimpleDateFormat("yyyy-MM-01 00:00:00");
    	SimpleDateFormat ydbf=new SimpleDateFormat("yyyyMM");
    	//Date date=new Date();
    	//String pDate=" 00:00:00";
		Calendar calendar = Calendar.getInstance();
		//获取今天月份格式化为 
		String nowMonth=ydf.format(calendar.getTime());
		
		//当前日期减一天 获得昨天日期
		calendar.add(Calendar.MONTH, -1); 
		
		//拿到上一个月并格式化
		String lastMonth=ydf.format(calendar.getTime());
		String monthTName=ydbf.format(calendar.getTime());
		try {
			//动态拼接表名
			StringBuilder sb=new StringBuilder();
			sb.append("cisco_tunnel_");
			sb.append(monthTName);
			String tName=sb.toString();
			
			if(nowMonth!=null && nowMonth!="" && lastMonth!=null 
			   && lastMonth!="" && tName!=null && tName!=""){
				ciscoTunnelService.insertCiscoTunnelMonth(tName,lastMonth,nowMonth);
			}else {
			logger.info("参数值为空"); 
		  }
		} catch (Exception e) {
			logger.error("创建失败", e); 
	  }

步骤四：
创表语句

 <update id="updateCreateCiscoTunnel" statementType="STATEMENT" >
		 CREATE TABLE IF NOT EXISTS ${tName}(
        `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
        `name` varchar(100) DEFAULT NULL COMMENT '隧道名',
        `protocol` varchar(100) DEFAULT NULL COMMENT '协议',
        `count` int(10) DEFAULT '0' COMMENT '数量',
        `jitter` double DEFAULT '0' COMMENT '抖动(ms)',
        `latency` double DEFAULT '0' COMMENT '延时(ms)',
        `loss_percentage` double DEFAULT '0' COMMENT '丢包率(%)',
        `rx_octets` double DEFAULT '0' COMMENT '接收字节数',
        `tx_octets` double DEFAULT '0' COMMENT '发送字节数',
        `create_time` datetime DEFAULT NULL,
        `tenant_id` varchar(60) DEFAULT NULL COMMENT '租户ID',
         PRIMARY KEY (`id`),
         KEY `create_time` (`create_time`),
         KEY `jitter` (`jitter`),
         KEY `latency` (`latency`),
         KEY `loss_percentage` (`loss_percentage`),
         KEY `rx_octets` (`rx_octets`),
         KEY `tx_octets` (`tx_octets`),
         KEY `name` (`name`),
         KEY `rx_ctets+tx_octets` (`rx_octets`,`tx_octets`),
         KEY `index_union_name_jitter` (`name`,`jitter`),
         KEY `protocol` (`protocol`),
         KEY `index_name_jitter_time` (`name`,`jitter`,`create_time`)
         ) ENGINE=InnoDB AUTO_INCREMENT=14073103 DEFAULT CHARSET=utf8 COMMENT='隧道数据表';
	</update>

完成，分表就是这么简单。
总结：分表可以通过定时任务，定时创建表，传入表名即可。