mysql存储过程实现大数据迁移 mysql存储大量数据

转载

mob64ca14154457 2024-03-04 16:33:08

文章标签 mysql存储过程实现大数据迁移 java 缓存 sql 文章分类 MySQL 数据库

一、mysql数据库中存储大量数据的问题

1.创建一个连接数据库的工具类

package com.shujia.util;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * 这是连接数据库的工具类
 */
public class JDBCUtil {
    private static Connection conn;
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://master:3306/user?useUnicode=true&characterEncoding=utf-8", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取jdbc
     */
    public static Connection getConnection(){
        return conn;
    }
}

注意：这个写一个工具类，是为了可以重复使用。将连接数据库的操作放在静态代码块里，是使连接只是用一次，不用每次访问时都连接数据库，使服务器访问更加的快速。

2.造大量数据

package com.shujia;

import com.shujia.util.JDBCUtil;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class MakeStudentData {
    public static void main(String[] args) throws Exception {
        /**
         * 生产大量的数据
         */
        //获取连接
        Connection con = JDBCUtil.getConnection();
        BufferedReader br = new BufferedReader(new FileReader("spring/data/students.txt"));

        String line;
        while ((line=br.readLine())!=null){
            String[] split = line.split(",");
            String id = split[0];
            String name = split[1];
            Integer age = Integer.parseInt(split[2]);
            String gender = split[3];
            String clazz = split[4];

            String sql="insert into student (id,name,age,gender,clazz) values(?,?,?,?,?)";
            //将数据保存到mysql
            PreparedStatement stat = con.prepareStatement(sql);

            for (int i = 0; i < 1000; i++) {
                String newid = id+i;
                stat.setString(1,newid);
                stat.setString(2,name);
                stat.setInt(3,age);
                stat.setString(4,gender);
                stat.setString(5,clazz);

                //一次处理一个批次
                stat.addBatch();
            }
            stat.executeBatch();
            System.out.println("插入1000条成功");
        }
    }
}

3.查询数据

package com.shujia.controller;

import com.shujia.bean.Student;
import com.shujia.util.JDBCUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

@RestController
public class StudentController {
    /**
     * 通过HashMap来模拟一个数据缓存量越来（使用内存来缓存）
     *
     * 将已经查询的过的数据保存到cache中
     *
     * 使用HashMap做缓存存在的问题
     * 1、当数据量越来越多时，HashMap效率会降低
     * 2、没办法清除没有用的缓存
     */
   HashMap<String,Student> cache = new HashMap<String,Student>();


    /**
     * 通过学号查询学生信息的接口
     * http://192.168.0.197:8080/queryStudentBuId?id=1500100127&key=123456(key是为了使数据接口更加的安全)
     */
    @GetMapping("/queryStudentById")
    public Student queryStudentById(String id,String key){
        //这里是访问该数据接口提供了一个钥匙，使数据更加的安全
      if(!"123456".equals(key)){
          return null;
      }

        /**
         * 1、先查询缓存
         *
         * 如果缓存中有数据，直接返回，如果没有在查询数据库
         *
         */

        Student student1 = cache.get(id);
        if(student1!=null){
            System.out.println("缓存中有数据，直接返回");
            return student1;
        }
        System.out.println("缓存中没有数据查询数据库");

        /**
         * 连接数据库查询学生的信息
         */
        Connection conn = JDBCUtil.getConnection();//创建了一个工具类，来连接数据库
        try {
            PreparedStatement stat = conn.prepareStatement("select * from student where id=?");
            stat.setString(1,id);
            ResultSet resultSet = stat.executeQuery();

            if(resultSet.next()){
                String id1 = resultSet.getString("id");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                String clazz = resultSet.getString("clazz");


                Student student = new Student(id1, name, age, gender, clazz);
                /**
                 * 2.将查询回来的结果保存到缓存中
                 */
                cache.put(id1,student);

                //Spring boot会自动将自定义类的对象转换成json格式的字符串返回
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

二、数据库中已经存储了大量数据，但是查询起来特别慢，可以添加主键和索引

例如

select  clazz,count(*) from student  group by clazz

没有使用主键之前---->需要两秒---->100万条数据

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) NOT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `clazz` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clazz_num` (`clazz`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

KEY clazz_num (clazz) USING HASH
增加索引之后查询时间变成200毫秒
主键默认有索引：PRIMARY KEY (id),
单例索引： KEY clazz_num (clazz) USING HASH

多个字段分组时候可以加多列索引

select clazz,gender, count(*) from student  group by clazz ,gender

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) NOT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `clazz` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clazz_gender` (`clazz`,`gender`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

多列索引： KEY clazz_gender (clazz,gender) USING HASH

三、Mysql数据库中有大量数据，但是服务器访问特别慢，我们可以通过缓存数据来查询

package com.shujia.controller;

import com.shujia.bean.Student;
import com.shujia.util.JDBCUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

@RestController
public class StudentController {
    /**
     * 通过HashMap来模拟一个数据缓存量越来（使用内存来缓存）
     *
     * 将已经查询的过的数据保存到cache中
     *
     * 使用HashMap做缓存存在的问题
     * 1、当数据量越来越多时，HashMap效率会降低
     * 2、没办法清除没有用的缓存
     */
   HashMap<String,Student> cache = new HashMap<String,Student>();


    /**
     * 通过学号查询学生信息的接口
     * http://192.168.0.197:8080/queryStudentBuId?id=1500100127&key=123456(key是为了使数据接口更加的安全)
     */
    @GetMapping("/queryStudentById")
    public Student queryStudentById(String id,String key){
        //这里是访问该数据接口提供了一个钥匙，使数据更加的安全
      if(!"123456".equals(key)){
          return null;
      }

        /**
         * 1、先查询缓存
         *
         * 如果缓存中有数据，直接返回，如果没有在查询数据库
         *
         */

        Student student1 = cache.get(id);
        if(student1!=null){
            System.out.println("缓存中有数据，直接返回");
            return student1;
        }
        System.out.println("缓存中没有数据查询数据库");

        /**
         * 连接数据库查询学生的信息
         */
        Connection conn = JDBCUtil.getConnection();//创建了一个工具类，来连接数据库
        try {
            PreparedStatement stat = conn.prepareStatement("select * from student where id=?");
            stat.setString(1,id);
            ResultSet resultSet = stat.executeQuery();

            if(resultSet.next()){
                String id1 = resultSet.getString("id");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                String clazz = resultSet.getString("clazz");


                Student student = new Student(id1, name, age, gender, clazz);
                /**
                 * 2.将查询回来的结果保存到缓存中
                 */
                cache.put(id1,student);

                //Spring boot会自动将自定义类的对象转换成json格式的字符串返回
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

这个部分使用了HashMap来模拟缓存数据，访问的过程是先访问缓存数据，如果没有，就去连接数据库去查询。可以把查询到的数据存到HashMap中，下次在来查询时，访问缓存数据时，这里面有了数据，就不用去访问数据库了，大大节省的访问时间。

解释图：

mysql存储过程实现大数据迁移 mysql存储大量数据_java