spring boot 整合mybatis，druid 测量 Mysql 5.0+ 数据量测试 - 励志做瘦子

1 首先在本地搭建一个虚拟机，安装mysql

2 对mysql 单表数据量测试这里使用spring boot 配合durid 和 mybatis 来搭建，下面会详细介绍

这里先贴上 pom，因为前段时间在对比 postgre，mongodb 等数据库，pom里面还有一些其他的引用，将就看吧。

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zqm</groupId>
    <artifactId>eureka</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>war</packaging>

    <name></name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>




    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.thrift/libthrift -->
<!--        <dependency>
            <groupId>org.apache.thrift</groupId>
            <artifactId>libthrift</artifactId>
            <version>0.9.3</version>
            <type>pom</type>
        </dependency>
        <!– https://mvnrepository.com/artifact/org.apache.cassandra/cassandra-thrift –>
        <dependency>
            <groupId>org.apache.cassandra</groupId>
            <artifactId>cassandra-thrift</artifactId>
            <version>1.0.12</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-cassandra</artifactId>
            <version>2.0.0.RELEASE</version>
        </dependency>-->
        <dependency>
                 <groupId>com.datastax.cassandra</groupId>
                <artifactId>cassandra-driver-core</artifactId>
                <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4.1212</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-eureka-server</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.datatype</groupId>
            <artifactId>jackson-datatype-joda</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.module</groupId>
            <artifactId>jackson-module-parameter-names</artifactId>
        </dependency>
        <!-- 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.6</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.6</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>4.3.12.RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
            <version>1.5.8.RELEASE</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.1</version>
        </dependency>

        <!--<dependency>-->
            <!--<groupId>com.suning.zhongtai</groupId>-->
            <!--<artifactId>jvmbarrier</artifactId>-->
            <!--<version>0.0.1-SNAPSHOT</version>-->
        <!--</dependency>-->
    </dependencies>
    <!-- 引入spring cloud的依赖 -->
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>Camden.SR4</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

　application.yml 的配置

#server:
#  port: 8761                    # 指定该Eureka实例的端口
#eureka:
#  client:
#    registerWithEureka: false
#    fetchRegistry: false
#    serviceUrl:
#      defaultZone: http://localhost:8761/eureka/
#
## 参考文档：http://projects.spring.io/spring-cloud/docs/1.0.3/spring-cloud.html#_standalone_mode
## 参考文档：http://my.oschina.net/buwei/blog/618756
server:
  port: 8080
  tomcat:
    max-threads: 5000
    min-spare-threads: 5000


mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.zqm.eureka.mysql.beans
#pagehelper分页插件
pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql
spring:
   data:
     cassandra:
       keyspace-name: yiibai_ks
       contact-points: 127.0.0.1
       port: 9042
#     mongodb:
#       host: 127.0.0.1
#       port: 30000
#       database: zqm
#       custom:
#         connections-per-host: 5000
#         min-connections-per-host: 500

  #     uri: mongodb://localhost:27017/test

   thymeleaf:
        prefix: classpath:/templates/
        suffix: .html
        mode: HTML5
        encoding: UTF-8
        content-type: text/html
   datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: 123456
    name: druid
    druid:
      min-idle: 1000
      max-active: 6000
      max-wait: 6000
    url: jdbc:mysql://10.24.18.111:3306/test?useSSL=false

druid 的配置

package com.zqm.eureka;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DatabaseDriver;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.io.Serializable;
import java.util.Map;


@Configuration
@ConditionalOnClass(com.alibaba.druid.pool.DruidDataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.alibaba.druid.pool.DruidDataSource", matchIfMissing = true)
public class DruidDataSourceConfiguration implements Serializable{

    @SuppressWarnings("unchecked")
    protected <T> T createDataSource(DataSourceProperties properties,
                                     Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }

   
    @Bean(name = "dataSource")
    @ConfigurationProperties("spring.datasource.druid")
    public com.alibaba.druid.pool.DruidDataSource dataSource(DataSourceProperties properties) {
        com.alibaba.druid.pool.DruidDataSource dataSource = createDataSource(
                properties, com.alibaba.druid.pool.DruidDataSource.class);

        DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());

        String validationQuery = databaseDriver.getValidationQuery();
        if (validationQuery != null) {
            dataSource.setTestOnBorrow(true);
            dataSource.setValidationQuery(validationQuery);
        }

        return dataSource;
    }

    /**
     * 注册一个StatViewServlet
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet(){
        //org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");

        //添加初始化参数：initParams
        //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
        servletRegistrationBean.addInitParameter("deny","127.0.0.1");
        //登录查看信息的账号密码.
        servletRegistrationBean.addInitParameter("loginUsername","root");
        servletRegistrationBean.addInitParameter("loginPassword","password");
        //是否能够重置数据.
        servletRegistrationBean.addInitParameter("resetEnable","false");// 禁用HTML页面上的“Reset All”功能
        return servletRegistrationBean;
    }

    /**
     * 注册一个：filterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean druidStatFilter(){

        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.setName("druidWebStatFilter");
        //添加过滤规则.
        filterRegistrationBean.addUrlPatterns("/*");
        //添加忽略的格式信息.
        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }


}

创建studengMapper.xml

<?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.zqm.eureka.mysql.mapper.StudentMapper" >

    <insert id="addStudent" parameterType="Student">

        INSERT INTO student(NAME,AGE,PHONE,ADDRESS) VALUES(#{name},#{age},#{phone},#{address});

    </insert>


</mapper>

StudentMapper.java

package com.zqm.eureka.mysql.mapper;

import com.zqm.eureka.mysql.beans.Student;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.transaction.annotation.Transactional;

@Mapper
public interface StudentMapper {

    @Transactional
    public int addStudent(Student student);

}

服务端StudentService.java 编写

package com.zqm.eureka.mysql.service;

import com.zqm.eureka.mysql.beans.Student;
import com.zqm.eureka.mysql.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class StudentService {

    @Autowired
    StudentMapper studentMapper;


    public int addStudent(Student student) {

        return studentMapper.addStudent(student);
    }

}

最后我们来验证一下

package com.zqm.eureka.mysql.web;


import com.zqm.eureka.mysql.beans.Student;
import com.zqm.eureka.mysql.mapper.StudentMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentTest {

    @Autowired
    private StudentMapper studentMapper;

    @Test
    public void test(){

        Student student = new Student();
        student.setAddress("q");
        student.setAge(11);
        student.setName("zz");
        student.setPhone("123456789");
        studentMapper.addStudent(student);

    }

}

然后我们用jmeter 来压入十万条数据看看占用多少空间，当然这里只能相对来分析。

package com.zqm.eureka.mysql.web;

import com.zqm.eureka.mysql.beans.Student;
import com.zqm.eureka.mysql.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("student")
public class StudentController {

    @Autowired
    StudentService studentService;

    @RequestMapping("add/{address}/{phone}/{age}/{name}")
    public String addStudent(@PathVariable(value ="address") String address,
                             @PathVariable(value ="phone") String phone,
                             @PathVariable(value ="age") int age,
                             @PathVariable(value ="name") String name){
        Student student = new Student();
        student.setAddress(address);
        student.setAge(age);
        student.setName(name);
        student.setPhone(phone);
        int i = 0 ;
        try {
           i = studentService.addStudent(student);
           if(i!=1){
               return "失败";
           }
            return "成功";
        }catch (Exception e){
            return "失败"+e.getMessage();
        }

    }

}

执行一下看看结果 10w 加的数据 myd 和myi 大概在 4.3M 左右

发表于 2018-03-29 15:44 励志做瘦子阅读(1890) 评论(0) 收藏举报