PostgreSQL创建百万级数据库

@PostgreSQL创建百万级数据表

从0到1创建百万级数据表

• 为学习索引相关知识，创建一个377w（21年考研人数 0.0）数据的简单表，很显然普通的SQL命令很难实现（主要是我不会），因此有了这篇文章

1. 确定需求

1. 只创建一个简单的表$⟶$一个表
2. 并无实际含义因此字段设置相对简单（字段设置并未特殊含义，只是为了包含不同数据类型）$⟶$序号，准考证号，姓名，性别，出生年月日，分数，研招网密码
3. 确定字段值的具体要求：
   1. 序号：主键，自增，因此插入数据时该项在实体类中并不体现
   2. 准考证号：准考证号=学校代码+不知名2位代码+专业代码+报考人数，每个数字在各自的范围内随机
      1. 学校代码：均为“1”开头，19年教育部公布数据显示为2956所院校（百度百科），因此取值范围为$(10001,12956)$（只是模拟，不代表实际情况）
      2. 不知名2位代码：$(11,99)$（为拼接字符串时方便）
      3. 专业代码：3位，$(101,199)$ 不代表实际情况
      4. 报考人数：5位，$(10001,19999)$ 由于情况全部随机，并且每次只生成一个数据且没有记录，因此模拟的准考证号存在相同的可能性，机器性能限制+本人性能限制（懒），因此不做处理
   3. 姓名：方便起见，姓名限制为2个字，3个字，4个字，为 $姓+名$ ，随机生成
   4. 性别：随机生成$[0,1]$，即布尔类型（只为方便使用，并无任何含义）
   5. 出生年月日：年龄无具体数据，因此随意设置为$(18,48)$，即$(1973.1.1,2003.12.30)$
   6. 分数：总分500，不考虑分布，设置为$(0,500)$，为测试浮点数，因此类型设置为浮点数，精度为$(5,2)$
   7. 研招网密码：本字段为实验字符串类型，并非真实，设置长度为$(8,24)$，长度与具体字符均为随机生成
4. 分析完毕开始干活

2. 创建数据表（随便创建一个数据库）

• 创建一个数据库（或使用已有的数据库）

create database 数据库名 with owner= 用户名;

相对插入数据而言，在明确需求的情况下创建数据表较为简单

create table postgraduate (
	id serial primary key,
	exam_num character(15),
	name varchar(4),
	sex bool,
	birthday date,
	score numeric(5,2),
	pwd varchar(24)
);

• 本应该将索引以及非空约束以及默认等情况全部理清再创建表，但因为无实际用途，因此一切从简

3. 插入数据

3.1 测试

• 根据前文中所提高的数据具体类型，使用SQL命令插入一条测试数据：

insert into postgraduate values 
(1,'123456789012345','绝世大坏',1,'2020-1-1',500.00,'abcdefghigklmnopqrstuvwx');

• 插入成功后可选择清空表

truncate table postgraduate;

3.2 插入377万条随机数据

3.2.1 新建工程

• 新建一个普通的Java工程：除说明外一路下一步即可
  
  
  

3.2.2 导入依赖

1. 项目根目录下新建lib目录存放依赖包
2. 导入依赖：Postgre数据库依赖：Maven仓库下载即可
3. 将lib文件夹add as library，直接点OK即可

3.2.2 代码部分

1. 实体类：Examinee

package com.newoag.forSqlInsert.entity;

import java.sql.Date;

public class Examinee {
    private String examNum;
    private String name;
    private boolean sex;
    private Date birthday;
    private float score;
    private String pwd;

    public Examinee() {
    }

    public String getExamNum() {
        return examNum;
    }

    public void setExamNum(String examNum) {
        this.examNum = examNum;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public boolean isSex() {
        return sex;
    }

    public void setSex(boolean sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public float getScore() {
        return score;
    }

    public void setScore(float score) {
        this.score = score;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "Examinee{" +
                "examNum='" + examNum + '\'' +
                ", name='" + name + '\'' +
                ", sex=" + sex +
                ", birthday=" + birthday +
                ", score=" + score +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

2. 工具类：生成随机数据

package com.newoag.forSqlInsert.utils;

import com.newoag.forSqlInsert.entity.Examinee;
import java.text.DecimalFormat;
import java.sql.Date;
import java.util.Random;


/**
 * @Author New_Oag
 * @Date 2021-7-14
 */
public class ExamineeUtils {
    static Random random = new Random(100);
    final static String[] firsts = {"赵","钱","孙","李","周","吴","郑","王","冯","陈","褚","卫","蒋","沈","韩","杨","朱","秦","尤","许","何","吕","施","张","孔","曹","严","华","金","魏","陶","姜","戚","谢","邹","喻","柏","水","窦","章","云","苏","潘","葛","奚","范","彭","郎","鲁","韦","昌","马","苗","凤","花","方","俞","任","袁","柳","酆","鲍","史","唐","费","廉","岑","薛","雷","贺","倪","汤","滕","殷","罗","毕","郝","邬","安","常","乐","于","时","傅","皮","卞","齐","康","伍","余","元","卜","顾","孟","平","黄","和","穆","萧","尹","姚","邵","湛","汪","祁","毛","禹","狄","米","贝","明","臧","计","伏","成","戴","谈","宋","茅","庞","熊","纪","舒","屈","项","祝","董","梁","杜","阮","蓝","闵","席","季","麻","强","贾","路","娄","危","江","童","颜","郭","梅","盛","林","刁","钟","徐","邱","骆","高","夏","蔡","田","樊","胡","凌","霍","虞","万","支","柯","昝","管","卢","莫","经","房","裘","缪","干","解","应","宗","丁","宣","贲","邓","郁","单","杭","洪","包","诸","左","石","崔","吉","钮","龚","程","嵇","邢","滑","裴","陆","荣","翁","荀","羊","于","惠","甄","曲","家","封","芮","羿","储","靳","汲","邴","糜","松","井","段","富","巫","乌","焦","巴","弓","牧","隗","山","谷","车","侯","宓","蓬","全","郗","班","仰","秋","仲","伊","宫","宁","仇","栾","暴","甘","钭","厉","戎","祖","武","符","刘","景","詹","束","龙","叶","幸","司","韶","郜","黎","蓟","溥","印","宿","白","怀","蒲","邰","从","鄂","索","咸","籍","赖","卓","蔺","屠","蒙","池","乔","阴","郁","胥","能","苍","双","闻","莘","党","翟","谭","贡","劳","逄","姬","申","扶","堵","冉","宰","郦","雍","却","璩","桑","桂","濮","牛","寿","通","边","扈","燕","冀","浦","尚","农","温","别","庄","晏","柴","瞿","阎","充","慕","连","茹","习","宦","艾","鱼","容","向","古","易","慎","戈","廖","庾","终","暨","居","衡","步","都","耿","满","弘","匡","国","文","寇","广","禄","阙","东","欧","殳","沃","利","蔚","越","夔","隆","师","巩","厍","聂","晁","勾","敖","融","冷","訾","辛","阚","那","简","饶","空","曾","毋","沙","乜","养","鞠","须","丰","巢","关","蒯","相","查","后","荆","红","游","郏","竺","权","逯","盖","益","桓","公","仉","督","岳","帅","缑","亢","况","郈","有","琴","归","海","晋","楚","闫","法","汝","鄢","涂","钦","商","牟","佘","佴","伯","赏","墨","哈","谯","篁","年","爱","阳","佟","言","福","南","火","铁","迟","漆","官","冼","真","展","繁","檀","祭","密","敬","揭","舜","楼","疏","冒","浑","挚","胶","随","高","皋","原","种","练","弥","仓","眭","蹇","覃","阿","门","恽","来","綦","召","仪","风","介","巨","木","京","狐","郇","虎","枚","抗","达","杞","苌","折","麦","庆","过","竹","端","鲜","皇","亓","老","是","秘","畅","邝","还","宾","闾","辜","纵","侴","万俟","司马","上官","欧阳","夏侯","诸葛","闻人","东方","赫连","皇甫","羊舌","尉迟","公羊","澹台","公冶","宗正","濮阳","淳于","单于","太叔","申屠","公孙","仲孙","轩辕","令狐","钟离","宇文","长孙","慕容","鲜于","闾丘","司徒","司空","兀官","司寇","南门","呼延","子车","颛孙","端木","巫马","公西","漆雕","车正","壤驷","公良","拓跋","夹谷","宰父","谷梁","段干","百里","东郭","微生","梁丘","左丘","东门","西门","南宫","第五","公仪","公乘","太史","仲长","叔孙","屈突","尔朱","东乡","相里","胡母","司城","张廖","雍门","毋丘","贺兰","綦毋","屋庐","独孤","南郭","北宫","王孙"};

    static DecimalFormat decimalFormat = new DecimalFormat("#.##");
    static ExamineeUtils examineeUtils = new ExamineeUtils();
    public static void updateExaminee(Examinee examinee){
        examinee.setExamNum(examineeUtils.generateExamNum());
        examinee.setName(examineeUtils.generateName());
        examinee.setSex(examineeUtils.generateSex());
        examinee.setBirthday(examineeUtils.generateBirthday());
        examinee.setScore(examineeUtils.generateScore());
        examinee.setPwd(examineeUtils.generatePwd());
    }
    /**
     * 生成准考证号  5+2+3+5
     * @return 准考证号
     */
    public String generateExamNum(){
        String school = String.valueOf(random.nextInt(2956)+10001);
        String second = String.valueOf(random.nextInt(89)+11);
        String major = String.valueOf(random.nextInt(98)+101);
        String number = String.valueOf(random.nextInt(9998)+10001);
        return "".concat(school).concat(second).concat(major).concat(number);
    }

    /**
     * 生成姓名  姓为百家姓，名随意
     * @return 姓名
     */
    public String generateName(){
        int first = random.nextInt(firsts.length);
        int second =random.nextInt(2)+1;

        StringBuilder name = new StringBuilder(firsts[first]);
        for (int i = 0; i < second; i++) {
            char ch = (char) (0x4e00 + (int) (Math.random() * (0x9fa5 - 0x4e00 + 1)));
            name.append(ch);
        }
        return name.toString();
    }

    public boolean generateSex(){
        return random.nextBoolean();
    }

    /**
     * 完成于：2021-07-14  Date(1626220800000)
     * @return
     */
    public Date generateBirthday(){
        return new Date((long)(random.nextFloat()*1626220800000L));
    }

    public float generateScore(){
        return Float.parseFloat(decimalFormat.format(random.nextFloat()*500));
    }

    public String generatePwd(){
        int length = random.nextInt(17)+8;
        StringBuilder pwd = new StringBuilder();

        while (length>0){
            int ch = random.nextInt(128);
            if (Character.isLetterOrDigit(ch)){
                pwd.append((char)ch);
                length--;
            }
        }
        return pwd.toString();
    }

}

3. 插入数据开始

package com.newoag.forSqlInsert;

import com.newoag.forSqlInsert.entity.Examinee;
import com.newoag.forSqlInsert.utils.ExamineeUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @Author New_Oag
 * @Date 2021-7-14
 */
public class Main {
    public static void main(String[] args) throws SQLException {

        Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "root");
        Statement statement = connection.createStatement();


        //此处插入数据337万条
        Examinee examinee = new Examinee();
        int num = 0;
        for (int i = 0; i < 3370000; i++) {
            ExamineeUtils.updateExaminee(examinee);
            String insertSql = "insert into postgraduate (exam_num,name,sex,birthday,score,pwd) values('" + examinee.getExamNum() + "','" + examinee.getName() + "'," + examinee.isSex() + ",'" + examinee.getBirthday().toString() + "'," + examinee.getScore() + ",'" + examinee.getPwd() + "');";
            num+=statement.executeUpdate(insertSql);
            System.out.printf("%d/3370000\n",num);
        }
        System.out.printf("总计%d条数据被插入",num);
        
    }
}

3.3.4 强迫症

在运行主程序插入全部数据之前，测试了一些插入操作，从而导致数据的id不再从1开始递增，这是因为系统生成的序列的值被这些插入操作用掉了一部分，所以会导致这种情况，因此只需要将这一部分还原即可，两种办法

1. alter sequence 查看系统为你创建的序列的名字 restart with 1或者另一个你喜欢的数字
2. 很笨的办法：创建一个新的序列，去覆盖原本的id的序列

转载请注明出处 😀