MySQL性能优化分析——第三课

package com.xdclass.mysql.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.util.HashMap;
import java.util.Map;


@Configuration
public class DruidConfig {


    @Value("${spring.datasource.druid.username}")
    private String userName;
    @Value("${spring.datasource.druid.password}")
    private String password;


    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        Map<String, String> initParameters = new HashMap<>(3);
        // 用户名
        initParameters.put("loginUsername", userName);
        // 密码
        initParameters.put("loginPassword", password);
        // 禁用HTML页面上的“Reset All”功能
        initParameters.put("resetEnable", "false");
        servletRegistrationBean.setInitParameters(initParameters);
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

}

void batchInsert(@Param("userList") List<User> userList);

  <select id="batchInsert" parameterType="java.util.List">
    insert into user ( `name`, company
    )
    values
    <foreach collection="userList" item="item" separator=",">
      ( #{item.name,jdbcType=VARCHAR}, #{item.company,jdbcType=VARCHAR})
    </foreach>
  </select>

    @RequestMapping(value = "/batchInsert", method = RequestMethod.GET)
    public RestItemResult<Integer> batchInsert() {
        int num = 1000;
        List<com.xdclass.mysql.model.User> userList = new ArrayList<>();
        for (int i = 0; i < num; i++) {
            com.xdclass.mysql.model.User u = new com.xdclass.mysql.model.User();
            u.setName("daniel"+i);
            u.setCompany("xdclass"+i);
            userList.add(u);
        }
        List<List<com.xdclass.mysql.model.User>> partition = Lists.partition(userList, 200);
        partition.forEach(users -> customUserMapper.batchInsert(users));
        RestItemResult<Integer> integerRestItemResult = new RestItemResult<>();
        integerRestItemResult.setItem(100);
        return integerRestItemResult;
    }

将list集合按指定长度进行切分，返回新的List<List 　　List<List> lists=Lists.partition(numList,3);

import com.google.common.collect.Lists;
import org.junit.Test;
import java.util.List;

public class testList {
 
    @Test
    public void  test(){
        List<Integer> numList = Lists.newArrayList(1, 2, 3, 4, 5, 6, 7, 8);
        List<List<Integer>> lists=Lists.partition(numList,3);
        System.out.println(lists);  //[[1, 2, 3], [4, 5, 6], [7, 8]]
    }
}

Lists.Partition 在项目中遇到的坑总结：

项目中使用 Lists.Partition 批量处理数据，但是最近内存一直 OutOffMemory，GC无法回收。

后来我们对使用过的集合手动 clear，没有测试直接就上线了。尴尬的是内存回收了，但是跑出来的数据出问题了。

最后自己单元测试发现是 List<List> resultPartition = Lists.partition(list, 500) 之后再对 list 进行 clear 操作，resultPartition也会被清空。

回来谷歌才发现它最终会调用 list.subList。subList 执行结果是获取 ArrayList 的一部分，返回的是 ArrayList 的部分视图。

对子集合的操作会反映到原集合， 对原集合的操作也会影响子集合。