现在的项目需求是测试MariaDB的单表的复杂SQL的查询性能, 所以建表, 测试性能:
新建一个springboot项目, 添加依赖:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.12</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.talkdesk</groupId>
<artifactId>mariadb-generate-date</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>mariadb-generate-date</name>
<description>mariadb-generate-date</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.4</version>
</dependency>
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.apifan.common</groupId>
<artifactId>common-random</artifactId>
<version>1.0.9</version>
</dependency>
<dependency>
<groupId>com.github.jsonzou</groupId>
<artifactId>jmockdata</artifactId>
<version>4.3.0</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2.添加CaseEvents的实体类:
package com.talkdesk.entity;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Builder;
import lombok.Data;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* @description case_events
* @author zhengkai.blog.csdn.net
* @date 2021-10-25
*/
@Builder
@Data
@TableName("case_events")
public class CaseEvents implements Serializable {
private static final long serialVersionUID = 1L;
@TableId
/**
* id
*/
private String id;
/**
* event_type
*/
private String eventType;
/**
* case_id
*/
private String caseId;
/**
* category
*/
private String category;
/**
* sub_category
*/
private String subCategory;
/**
* form_id
*/
private String formId;
/**
* old_form_id
*/
private String oldFormId;
/**
* owner_id
*/
private String ownerId;
/**
* old_owner_id
*/
private String oldOwnerId;
/**
* owner_name
*/
private String ownerName;
/**
* old_owner_name
*/
private String oldOwnerName;
/**
* channel_source
*/
private String channelSource;
/**
* tenant_id
*/
private String tenantId;
/**
* brand_id
*/
private String brandId;
/**
* group_id
*/
private String groupId;
/**
* old_group_id
*/
private String oldGroupId;
/**
* group_name
*/
private String groupName;
/**
* old_group_name
*/
private String oldGroupName;
/**
* status
*/
private String status;
/**
* old_status
*/
private String oldStatus;
/**
* priority
*/
private String priority;
/**
* old_priority
*/
private String oldPriority;
/**
* requester_id
*/
private String requesterId;
/**
* requester_name
*/
private String requesterName;
/**
* submitter_id
*/
private String submitterId;
/**
* submitter_name
*/
private String submitterName;
/**
* create_at
*/
private LocalDateTime createAt;
/**
* occurred_at
*/
private LocalDateTime occurredAt;
/**
* mask
*/
private String mask;
/**
* solve_reason
*/
private String solveReason;
/**
* close_reason
*/
private String closeReason;
/**
* assign_reason
*/
private String assignReason;
/**
* satisfaction
*/
private String satisfaction;
}
3.创建生成数据的service:
package com.talkdesk.service;
import com.apifan.common.random.source.DateTimeSource;
import com.apifan.common.random.source.NumberSource;
import com.talkdesk.config.Constant;
import com.talkdesk.entity.CaseEvents;
import com.talkdesk.mapper.CaseEventsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
/**
* @author ke.zhang
* @version 1.0
* @description: TODO
* @date 2021/10/25 10:06
*/
@Service
public class EventService {
@Autowired
private CaseEventsMapper caseEventsMapper;
public void generateEventData(int num) {
if (num < 1) {
return;
}
Constant.tenantIds().parallelStream().forEach(e -> oneTenantSaveData(num, e));
}
private void oneTenantSaveData(int num, String tenantId) {
List<CaseEvents> events = new ArrayList<>();
for (int i = 0; i < num; i++) {
CaseEvents caseEvents = getEventObject(tenantId);
events.add(caseEvents);
if (events.size() > Constant.NUM) {
caseEventsMapper.insertBatch(events);
events.clear();
}
}
if (events.size() > 0) {
caseEventsMapper.insertBatch(events);
}
}
private CaseEvents getEventObject(String tenantId) {
String id1 = UUID.randomUUID().toString().replace("-", "");
String id2 = UUID.randomUUID().toString().replace("-", "");
CaseEvents build = CaseEvents.builder()
.id(id1)
.caseId(id1.substring(0, 19))
.eventType("case_created")
.category("00" + NumberSource.getInstance().randomInt(1, 4))
.subCategory("00" + NumberSource.getInstance().randomInt(1, 4))
.formId(id1.substring(0, 19))
.oldFormId(id1.substring(0, 19))
.ownerId(id2)
.oldOwnerId(id2)
.ownerName("")
.oldOwnerName("")
.channelSource("00" + NumberSource.getInstance().randomInt(1, 3))
.tenantId(tenantId)
.brandId("0")
.groupId("management-2")
.oldGroupId("management-2")
.groupName("management-2")
.oldGroupName("management-2")
.status("00" + NumberSource.getInstance().randomInt(1, 7))
.oldStatus("00" + NumberSource.getInstance().randomInt(1, 7))
.priority("00" + NumberSource.getInstance().randomInt(1, 5))
.oldPriority("00" + NumberSource.getInstance().randomInt(1, 5))
.requesterId("001")
.requesterName("001")
.submitterId("002")
.submitterName("002")
.createAt(DateTimeSource.getInstance().randomPastTime(30))
.occurredAt(DateTimeSource.getInstance().randomPastTime(30))
.mask("001")
.solveReason("001")
.closeReason("001")
.assignReason("001")
.satisfaction("001")
.build();
return build;
}
}
4.创建Mapper
package com.talkdesk.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.talkdesk.entity.CaseEvents;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface CaseEventsMapper extends BaseMapper<CaseEvents> {
void insertBatch(@Param("events") List<CaseEvents> events);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.talkdesk.mapper.CaseEventsMapper">
<insert id="insertBatch">
INSERT INTO case_events values
<foreach collection="events" item="event" separator=",">
(#{event.id},#{event.eventType},#{event.caseId},#{event.category},#{event.subCategory},#{event.formId},#{event.oldFormId},#{event.ownerId},#{event.oldOwnerId},
#{event.ownerName},#{event.oldOwnerName},#{event.channelSource},#{event.tenantId},#{event.brandId},#{event.groupId},#{event.oldGroupId},#{event.groupName},#{event.oldGroupName},
#{event.status},#{event.oldStatus},#{event.priority},#{event.oldPriority},#{event.requesterId},#{event.requesterName},#{event.submitterId},#{event.submitterName},#{event.createAt},
#{event.occurredAt},#{event.mask},#{event.solveReason},#{event.closeReason},#{event.assignReason},#{event.satisfaction})
</foreach>
</insert>
</mapper>
5.设置常量:
package com.talkdesk.config;
import java.util.ArrayList;
import java.util.List;
/**
* @author ke.zhang
* @version 1.0
* @description: TODO
* @date 2021/10/27 16:22
*/
public class Constant {
private static List<String> tenantIds = new ArrayList<>();
public static final int NUM = 1500;
public static List<String> tenantIds() {
if (tenantIds.size() < 1) {
for (int i = 0; i < 100; i++) {
tenantIds.add("60f797a5b1a6b0e450dbe" + i);
}
}
return tenantIds;
}
}
写一个切面, 记录方法的执行时间
package com.talkdesk.config;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
/**
* @author ke.zhang
* @version 1.0
* @date 2021/10/25 16:44
*/
@Slf4j
@Aspect
@Component
public class ExecuteTimeAspect {
/**
* Pointcut 切入点
* 匹配cn.controller包下面的所有方法
*/
@Pointcut("execution(public * com.talkdesk.service.*.*(..))")
public void execute() {
}
/**
* 环绕通知
*/
@Around(value = "execute()")
public Object arround(ProceedingJoinPoint point) {
long l1 = System.currentTimeMillis();
Object o = null;
try {
o = point.proceed();
} catch (Throwable e) {
e.printStackTrace();
}
long l2 = System.currentTimeMillis();
log.info("执行{}方法,所用的时间是:{}秒", point.getSignature().toString(), (l2 - l1) / 1000);
return o;
}
}
6.配置数据库信息:
spring:
datasource:
driver-class-name: org.mariadb.jdbc.Driver
url: jdbc:mariadb://localhost:3306/test
username: root
password: 123456
mybatis-plus:
mapper-locations: classpath:mapper/*.xml
和项目启动方法:
package com.talkdesk;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.talkdesk.mapper")
public class MariadbGenerateDateApplication {
public static void main(String[] args) {
SpringApplication.run(MariadbGenerateDateApplication.class, args);
}
}
7.写一个单元测试方法
package com.talkdesk.service;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
/**
* @author ke.zhang
* @version 1.0
* @description:
* @date 2021/10/25 13:44
*/
@SpringBootTest
class EventServiceTest {
@Autowired
EventService eventService;
@Test
void generateEventData() {
eventService.generateEventData(50000);
}
}