测试java程序插入单表2000万条数据（MySQL,PostgreSQL,达梦）

 

1、创建表

1.1、MySQL

CREATE TABLE `tb_person` (
  `id` bigint NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` varchar(4) NOT NULL,
  `age` int NOT NULL,
  `email` varchar(30) DEFAULT NULL,
  `tel` varchar(20) DEFAULT NULL,
  `road` varchar(100) DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

1.2、PostgreSQL

CREATE TABLE tb_person (
  id serial PRIMARY KEY,
  name varchar(100) NOT NULL,
  gender varchar(4) NOT NULL,
  age int NOT NULL,
  email varchar(30) DEFAULT NULL,
  tel varchar(20) DEFAULT NULL,
  road varchar(100) DEFAULT NULL,
  create_time TIMESTAMP  NOT NULL,
  update_time TIMESTAMP  DEFAULT NULL
);

 

1.3、达梦

 

2、java批量插入程序

参考：mysql-mock: 生成千万条以上mysql模拟数据，适用于性能测试的场景；测试数据贴近业务场景，看着较真实

pom.xml中配置驱动

<!-- 达梦数据库JDBC驱动 -->
<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>DmJdbcDriver18</artifactId>
    <version>8.1.3.140</version>
</dependency>

<!-- PostgreSQL JDBC驱动 -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- MySQL JDBC驱动 -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

application.yaml配置

spring:
  autoconfigure:
    exclude: org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration
  datasource:
#    driver-class-name: dm.jdbc.driver.DmDriver
#    url: jdbc:dm://localhost:5236
#    username: SYSDBA
#    password: xxx
#    driver-class-name: org.postgresql.Driver
#    url: jdbc:postgresql://localhost:5432/sku?currentSchema=public&useUnicode=true&characterEncoding=UTF-8
#    username: postgres
#    password: xxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/sku?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: xxx

 

3、单表批量插入2000万条数据

3.1、MySQL

3.2、PostgreSQL

3.3、达梦

 

4、查询测试

测试表 tb_person，数量为2000万

select * from tb_person where name like '%hom1e%' limit 10;

select id from tb_person where name like '%hom1e%' limit 10;

select p.* from tb_person p 
join(select id from tb_person where name like '%hom1e%' limit 10) a
on a.id=p.id;

 

4.1、MySQL

create index idx_name on tb_person(name);

 

4.2、PostgreSQL

create index idx_name on tb_person(name);

 

4.3、达梦

create index idx_name on test.tb_person(name);