一、介绍

通用导入方法:数据量大应当使用load data方式导入,而不是使用source sql文件的方式

此处为了验证PXC集群性能,需要创造一千万条数据进行SQL语句性能测试

二、数据生成

若对数据内容无要求,则可使用通用工具生成随机数据,此处面向具体业务场景,故使用Java应用程序生成数据

1. 创建SpringBoot工程

使用SpringBoot快速创建数据导入工程,引入jdbc和jpa依赖

...

    org.springframework.boot
    spring-boot-starter-parent
    2.2.2.RELEASE
     

...

    
        org.springframework.boot
        spring-boot-starter-data-jpa
    

    
        mysql
        mysql-connector-java
        runtime
    
    
        org.projectlombok
        lombok
        true
    
    
        org.springframework.boot
        spring-boot-starter-test
        test
        
            
                org.junit.vintage
                junit-vintage-engine
            
        
    

2. 编写实体类

package top.fjy8018.jdbcbench;

/**
 * @author F嘉阳
 * @date 2019-12-28 13:08
 */
@Entity(name = "tb_test")
@Data
public class DBTest {

    @Id
    private Integer id;

    private String name;
}

实现JPA接口

package top.fjy8018.jdbcbench;

/**
 * @author F嘉阳
 * @date 2019-12-28 13:09
 */
public interface DBTestRepository extends JpaRepository {}

3. 配置JDBC连接

此处有个重要配置,就是rewriteBatchedStatements=true,加上该配置,批量写入数据的性能能提升数百倍。

参考资料:jdbcTemplate.batchUpdate在批量执行的时候,性能差没有效果,看看怎么解决的。

1577623991663

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: admin
    password: password
    url: jdbc:mysql://192.168.1.9:13306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 10000
        order_inserts: true
        generate_statistics: true

4.准备数据库

  • 每个PXC分片只开启一个节点,导入一个节点中则不会产生限流,导入完成后拷贝数据文件到其他节点再启动即可

  • 修改PXC节点文件,然后重启PXC服务

    ## 不等待事务提交先写入硬盘
    innodb_flush_log_at_trx_commit = 0
    ## 日志数据直接写入磁盘,不进入日志缓冲区
    innodb_flush_method = O_DIRECT
    ## 缓存越大越好
    innodb_buffer_pool_size = 200M
    
  • 创建t_test数据表

    CREATE TABLE tb_test(
    	id INT UNSIGNED PRIMARY KEY,
        name VARCHAR(200) NOT NULL
    );
    
  • 配置MyCat

    
    
    
            
            
                    
    select user() select user()

5.编写单元测试

package top.fjy8018.jdbcbench;

@Slf4j
@Component
class JdbcBenchTest extends JdbcBenchApplicationTests{

    @Autowired
    private DBTestRepository repository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void insertTest(){
        DBTest t = new DBTest();
        t.setId(1);
        t.setName(1+",测试实体");
        DBTest save = repository.save(t);
        Assert.notNull(save,"保存测试失败");
    }

    @Test
    public void insertData() throws Exception {
        for (int i = 1; i <= 10000000;) {
            List tests = new LinkedList<>();
            for (int j = 1; j <= 50000; j++) {
                DBTest t = new DBTest();
                t.setId(j+i);
                t.setName(j+i+",测试实体");
                tests.add(t);
            }
            i = i+50000;
            log.info("插入{}条数据",i);
            batchInsert(tests);
            tests.clear();
        }
    }

    @Transactional
    public void batchInsert(List dbTestList) {
        String sql = "insert into tb_test(id,name) values(?,?)";
        log.info("批次大小:{}",dbTestList.size());
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String name = dbTestList.get(i).getName();
                int id = dbTestList.get(i).getId();
                ps.setInt(1, id);
                ps.setString(2, name);
            }
            public int getBatchSize() {
                return dbTestList.size();
            }
        });
    }
}

执行单元测试,大约1小时写入完成,不同服务器配置写入性能差距明显,应当以实际为准

三、数据导入后的操作

  1. 关闭PXC节点(还原配置文件)
  2. 拷贝数据文件到其他PXC节点
  3. 关闭 MyCat(还原配置文件)
  4. 启动PC和 MyCat

四、大数据性能测试——limit关键字

SELECT id, name FROM tb_test LIMIT 100, 100;
SELECT id, name FROM tb_test LIMIT 10000, 100;
SELECT id, name FROM tb_test LIMIT 1000000, 100;
SELECT id, name From tb_test LIMIT 5000000, 100;

现象:执行最后一条sql语句时速度慢,且cpu和内存占用率极大,主要对mycat节点内存占用一直居高不下

分析索引使用情况,rows字段显示其操作了4746302条数据,走全表扫描

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tb_test ALL 4746302 100

原因:

  • 全表扫描,速度极慢
  • limit语句的查询时间与起始记录的位置成正比
  • mysql limit的语句是很方便,但是对记录很多的表并不适合直接使用
最后修改:2019 年 12 月 30 日
如果觉得我的文章对你有用,请随意赞赏