Spring boot 框架实现Mysql数据库分表分区的代码逻辑

一、业务：由于项目要监控硬件设备的数据，需要硬件回传数据，而每台设备一天回传数据量达到十万级级别，造成查询效率非常慢。
二、思路：将每台设备按月份分表，每张表中按天分区（由于项目只保留当年数据，不在考虑年份，而且硬件设备不多，所以不用担心见的表过多），在添加设备的时候开线程建表分区
三、实现方法：
1、设备添加逻辑不在叙述，当设备添加成功，开线程调用建表语句：

              System.out.println("===查看返回主键id:"+dInfo.getId());
                new Thread(){
                    public void run(){
                            String[] amonth = {"01","02","03","04","05","06","07","08","09","10","11","12"};
                            for(String table_month : amonth){
                                String tableName = "d_data_his_" + table_month+"_"+dInfo.getId();
                                try {
                                    mysqlCreate.createTable(tableName);
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                        }

                }.start();

工具类MysqlCreate：

package com.shallnew.dm_web.utils.db;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
@Component
public class MysqlCreate {
    @Autowired
    private ApplicationContext applicationContext ;

    public void createTable(String tableName) throws SQLException {
        DataSource dataSource =(DataSource) applicationContext.getBean("dataSource");
        Connection conn = (Connection) dataSource.getConnection();
        Statement sm =  conn.createStatement();
        sm.execute("CREATE TABLE `"+tableName+"` (\n" +
                "  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',\n" +
                "  `d_info_id` int(11) unsigned DEFAULT NULL COMMENT '设备id',\n" +
                "  `d_param_no` varchar(8) DEFAULT NULL COMMENT '参数编号',\n" +
                "  `data` varchar(20) DEFAULT NULL COMMENT '参数值',\n" +
                "  `create_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',\n" +
                "  PRIMARY KEY (`id`,`create_time`),\n" +
                "  KEY `d_data_his_1` (`d_info_id`),\n" +
                "  KEY `d_data_his_2` (`d_param_no`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='设备参数值（各台）（每月）'\n" +
                "/*!50100 PARTITION BY RANGE (DAY(create_time))\n" +
                "(PARTITION day1 VALUES LESS THAN (2) ENGINE = InnoDB,\n" +
                " PARTITION day2 VALUES LESS THAN (3) ENGINE = InnoDB,\n" +
                " PARTITION day3 VALUES LESS THAN (4) ENGINE = InnoDB,\n" +
                " PARTITION day4 VALUES LESS THAN (5) ENGINE = InnoDB,\n" +
                " PARTITION day5 VALUES LESS THAN (6) ENGINE = InnoDB,\n" +
                " PARTITION day6 VALUES LESS THAN (7) ENGINE = InnoDB,\n" +
                " PARTITION day7 VALUES LESS THAN (8) ENGINE = InnoDB,\n" +
                " PARTITION day8 VALUES LESS THAN (9) ENGINE = InnoDB,\n" +
                " PARTITION day9 VALUES LESS THAN (10) ENGINE = InnoDB,\n" +
                " PARTITION day10 VALUES LESS THAN (11) ENGINE = InnoDB,\n" +
                " PARTITION day11 VALUES LESS THAN (12) ENGINE = InnoDB,\n" +
                " PARTITION day12 VALUES LESS THAN (13) ENGINE = InnoDB,\n" +
                " PARTITION day13 VALUES LESS THAN (14) ENGINE = InnoDB,\n" +
                " PARTITION day14 VALUES LESS THAN (15) ENGINE = InnoDB,\n" +
                " PARTITION day15 VALUES LESS THAN (16) ENGINE = InnoDB,\n" +
                " PARTITION day16 VALUES LESS THAN (17) ENGINE = InnoDB,\n" +
                " PARTITION day17 VALUES LESS THAN (18) ENGINE = InnoDB,\n" +
                " PARTITION day18 VALUES LESS THAN (19) ENGINE = InnoDB,\n" +
                " PARTITION day19 VALUES LESS THAN (20) ENGINE = InnoDB,\n" +
                " PARTITION day20 VALUES LESS THAN (21) ENGINE = InnoDB,\n" +
                " PARTITION day21 VALUES LESS THAN (22) ENGINE = InnoDB,\n" +
                " PARTITION day22 VALUES LESS THAN (23) ENGINE = InnoDB,\n" +
                " PARTITION day23 VALUES LESS THAN (24) ENGINE = InnoDB,\n" +
                " PARTITION day24 VALUES LESS THAN (25) ENGINE = InnoDB,\n" +
                " PARTITION day25 VALUES LESS THAN (26) ENGINE = InnoDB,\n" +
                " PARTITION day26 VALUES LESS THAN (27) ENGINE = InnoDB,\n" +
                " PARTITION day27 VALUES LESS THAN (28) ENGINE = InnoDB,\n" +
                " PARTITION day28 VALUES LESS THAN (29) ENGINE = InnoDB,\n" +
                " PARTITION day29 VALUES LESS THAN (30) ENGINE = InnoDB,\n" +
                " PARTITION day30 VALUES LESS THAN (31) ENGINE = InnoDB,\n" +
                " PARTITION day31 VALUES LESS THAN (32) ENGINE = InnoDB,\n" +
                " PARTITION daymax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;");

        sm.close();
        conn.close();
    }


}