mariadb增加 1000w 测试数据
in developsqlCo-De with 0 comment
mysql

mariadb增加 1000w 测试数据

in developsqlCo-De with 0 comment

mysql

造数据, 数据输出 csv 格式，1000w 数据，文件大小 1.2G

public class Generate3000 {

  public static void main(String[] args) {
    int max = 10000000;
    String userType = "USER";
    Random random = new Random();
    List<String> limit = new ArrayList<>(200);
    for (int i = 1; i <= max; i++) {
      int rInt = random.nextInt(5);
      int userId = random.nextInt(30000);
      ContactRecentRecord contactRecentRecord;
      if (rInt % 2 != 0) { 
        contactRecentRecord =
            ContactRecentRecord.builder()
                .id(i + 0L)
                .userType(userType)
                .userId(userId + "")
                .contactAccountType("IN")
                .contactAccountNo(ChineseMobileNumberGenerator.getInstance().generate())
                .contactAccountName(EnglishNameGenerator.getInstance().generate())
                .contactUserType("")
                .contactBankCode("")
                .contactBankName("")
                .serviceType("INTRANS")
                .businessSceneSource("")
                .createTime(LocalDateTime.now())
                .updateTime(LocalDateTime.now())
                .deleteMark(0)
                .build();
      } else { // AC
        contactRecentRecord =
            ContactRecentRecord.builder()
                .id(i + 0L)
                .userType(userType)
                .userId(userId + "")
                .contactAccountType("OUT")
                .contactAccountNo(
                    BankCardNumberGenerator.generate(BankNameEnum.ICBC, BankCardTypeEnum.DEBIT))
                .contactAccountName(EnglishNameGenerator.getInstance().generate())
                .contactUserType("")
                .contactBankCode(BankNameEnum.ICBC.getCode())
                .contactBankName(BankNameEnum.ICBC.getName())
                .serviceType("OUTTRANS")
                .businessSceneSource("")
                .createTime(LocalDateTime.now())
                .updateTime(LocalDateTime.now())
                .deleteMark(0)
                .build();
      }
      limit.add(contactRecentRecord.toString());
      if (i % 100 == 0) {
        try {
          Generate3000.writeCsv(limit);
          limit.clear();
        } catch (IOException e) {
          e.printStackTrace();
        }
      }
    }
    if (!limit.isEmpty()) {
      try {
        Generate3000.writeCsv(limit);
        limit.clear();
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
  }

  public static void writeCsv(List<String> cons) throws IOException {
    File csvOutputFile = new File("csvtest.csv");
    try (FileWriter fileWriter = new FileWriter(csvOutputFile, true);
        PrintWriter pw = new PrintWriter(fileWriter)) {
      cons.stream()
          .forEach(
              con -> {
                pw.println(con);
              });
    }
  }
}

建表

通过 LOAD 加载数据，耗时 2 分钟 29 秒

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.35-MariaDB-1:10.3.35+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use contacts
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [contacts]> load data infile '/opt/data/csvtest.csv' into table contact_recent_record;
ERROR 1265 (01000): Data truncated for column 'id' at row 1
MariaDB [contacts]> load data infile '/opt/data/csvtest.csv' into table contact_recent_record fields terminated by',';
Query OK, 10000000 rows affected (2 min 28.557 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [contacts]>

本文由 Ivan Dong 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: Aug 11, 2023 at 06:55 am

← → ↓ ↑
Comments are closed.