mybatis中的foreach条件参数过多时，#和$效率比较
·
背景
最近对mybatis的in查询做优化时，看到一个有趣的方法，使用外部拼接好查询条件，然后用$符合，直接替代了mybatis内部foreach，特地在本地上做个评测
看到一篇文章，当foreach条件参数过多的时候，采用外部拼接的方式能提升mybatis，特地在本地做了个实验
条件
in 查询条件为1000个，去驱动数据表查询
环境准备
mysql语句
CREATE TABLE `workexperience` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`companyname` varchar(255) DEFAULT NULL COMMENT '项目名称',
`duty` text COMMENT '你的职责',
`startyear` int(11) DEFAULT NULL COMMENT '开始工作时间',
`endyear` int(11) DEFAULT NULL COMMENT '结束年份',
`startmonth` int(11) DEFAULT NULL,
`endmonth` int(11) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL COMMENT '工作类型',
`nature` varchar(255) DEFAULT NULL COMMENT '公司发展类型',
`position` varchar(255) DEFAULT NULL COMMENT '职位',
`department` varchar(255) DEFAULT NULL COMMENT '部门',
`writetime` datetime DEFAULT NULL COMMENT '填写时间',
`state` varchar(255) DEFAULT NULL COMMENT '备用字段',
`uid` int(11) DEFAULT NULL COMMENT '该工作经验属于谁所有',
`weight` int(255) DEFAULT NULL COMMENT '展示的权重',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32233 DEFAULT CHARSET=utf8;
逆向工程生成POJO、dao、mapper、xml
package com.xiaozheng.recruitment.pojo;
import java.util.Date;
public class Workexperience {
private Integer id;
private String companyname;
private String duty;
private Integer startyear;
private Integer endyear;
private Integer startmonth;
private Integer endmonth;
private String type;
private String nature;
private String position;
private String department;
private Date writetime;
private String state;
private Integer uid;
private Integer weight;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCompanyname() {
return companyname;
}
public void setCompanyname(String companyname) {
this.companyname = companyname == null ? null : companyname.trim();
}
public String getDuty() {
return duty;
}
public void setDuty(String duty) {
this.duty = duty == null ? null : duty.trim();
}
public Integer getStartyear() {
return startyear;
}
public void setStartyear(Integer startyear) {
this.startyear = startyear;
}
public Integer getEndyear() {
return endyear;
}
public void setEndyear(Integer endyear) {
this.endyear = endyear;
}
public Integer getStartmonth() {
return startmonth;
}
public void setStartmonth(Integer startmonth) {
this.startmonth = startmonth;
}
public Integer getEndmonth() {
return endmonth;
}
public void setEndmonth(Integer endmonth) {
this.endmonth = endmonth;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type == null ? null : type.trim();
}
public String getNature() {
return nature;
}
public void setNature(String nature) {
this.nature = nature == null ? null : nature.trim();
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position == null ? null : position.trim();
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department == null ? null : department.trim();
}
public Date getWritetime() {
return writetime;
}
public void setWritetime(Date writetime) {
this.writetime = writetime;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state == null ? null : state.trim();
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Integer getWeight() {
return weight;
}
public void setWeight(Integer weight) {
this.weight = weight;
}
@Override
public String toString() {
return "Workexperience{" +
"id=" + id +
", companyname='" + companyname + '\'' +
", duty='" + duty + '\'' +
", startyear=" + startyear +
", endyear=" + endyear +
", startmonth=" + startmonth +
", endmonth=" + endmonth +
", type='" + type + '\'' +
", nature='" + nature + '\'' +
", position='" + position + '\'' +
", department='" + department + '\'' +
", writetime=" + writetime +
", state='" + state + '\'' +
", uid=" + uid +
", weight=" + weight +
'}';
}
}
package com.xiaozheng.recruitment.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.xiaozheng.recruitment.pojo.Workexperience;
public interface WorkexperienceMapper {
int deleteByPrimaryKey(Integer id);
int insert(Workexperience record);
int insertSelective(Workexperience record);
Workexperience selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Workexperience record);
int updateByPrimaryKey(Workexperience record);
@Select("select * from workexperience w where w.uid = #{0} order by w.weight DESC,w.writetime DESC")
List<Workexperience> selectByUid(int userId);
List<Workexperience> listByUidList(List<Integer> list);
List<Workexperience> listByUidList2(List<Integer> list);
List<Workexperience> listByUidList3(@Param(value="uidStr") String uidStr);
}
<?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.xiaozheng.recruitment.dao.WorkexperienceMapper" >
<resultMap id="BaseResultMap" type="com.xiaozheng.recruitment.pojo.Workexperience" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="companyname" property="companyname" jdbcType="VARCHAR" />
<result column="duty" property="duty" jdbcType="VARCHAR" />
<result column="startyear" property="startyear" jdbcType="INTEGER" />
<result column="endyear" property="endyear" jdbcType="INTEGER" />
<result column="startmonth" property="startmonth" jdbcType="INTEGER" />
<result column="endmonth" property="endmonth" jdbcType="INTEGER" />
<result column="type" property="type" jdbcType="VARCHAR" />
<result column="nature" property="nature" jdbcType="VARCHAR" />
<result column="position" property="position" jdbcType="VARCHAR" />
<result column="department" property="department" jdbcType="VARCHAR" />
<result column="writetime" property="writetime" jdbcType="TIMESTAMP" />
<result column="state" property="state" jdbcType="VARCHAR" />
<result column="uid" property="uid" jdbcType="INTEGER" />
<result column="weight" property="weight" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
id, companyname, duty, startyear, endyear, startmonth, endmonth, type, nature, position,
department, writetime, state, uid, weight
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from workexperience
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from workexperience
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.xiaozheng.recruitment.pojo.Workexperience" >
insert into workexperience (id, companyname, duty,
startyear, endyear, startmonth,
endmonth, type, nature,
position, department, writetime,
state, uid, weight)
values (#{id,jdbcType=INTEGER}, #{companyname,jdbcType=VARCHAR}, #{duty,jdbcType=VARCHAR},
#{startyear,jdbcType=INTEGER}, #{endyear,jdbcType=INTEGER}, #{startmonth,jdbcType=INTEGER},
#{endmonth,jdbcType=INTEGER}, #{type,jdbcType=VARCHAR}, #{nature,jdbcType=VARCHAR},
#{position,jdbcType=VARCHAR}, #{department,jdbcType=VARCHAR}, #{writetime,jdbcType=TIMESTAMP},
#{state,jdbcType=VARCHAR}, #{uid,jdbcType=INTEGER}, #{weight,jdbcType=INTEGER})
</insert>
<insert id="insertSelective" parameterType="com.xiaozheng.recruitment.pojo.Workexperience" >
insert into workexperience
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="companyname != null" >
companyname,
</if>
<if test="duty != null" >
duty,
</if>
<if test="startyear != null" >
startyear,
</if>
<if test="endyear != null" >
endyear,
</if>
<if test="startmonth != null" >
startmonth,
</if>
<if test="endmonth != null" >
endmonth,
</if>
<if test="type != null" >
type,
</if>
<if test="nature != null" >
nature,
</if>
<if test="position != null" >
position,
</if>
<if test="department != null" >
department,
</if>
<if test="writetime != null" >
writetime,
</if>
<if test="state != null" >
state,
</if>
<if test="uid != null" >
uid,
</if>
<if test="weight != null" >
weight,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="companyname != null" >
#{companyname,jdbcType=VARCHAR},
</if>
<if test="duty != null" >
#{duty,jdbcType=VARCHAR},
</if>
<if test="startyear != null" >
#{startyear,jdbcType=INTEGER},
</if>
<if test="endyear != null" >
#{endyear,jdbcType=INTEGER},
</if>
<if test="startmonth != null" >
#{startmonth,jdbcType=INTEGER},
</if>
<if test="endmonth != null" >
#{endmonth,jdbcType=INTEGER},
</if>
<if test="type != null" >
#{type,jdbcType=VARCHAR},
</if>
<if test="nature != null" >
#{nature,jdbcType=VARCHAR},
</if>
<if test="position != null" >
#{position,jdbcType=VARCHAR},
</if>
<if test="department != null" >
#{department,jdbcType=VARCHAR},
</if>
<if test="writetime != null" >
#{writetime,jdbcType=TIMESTAMP},
</if>
<if test="state != null" >
#{state,jdbcType=VARCHAR},
</if>
<if test="uid != null" >
#{uid,jdbcType=INTEGER},
</if>
<if test="weight != null" >
#{weight,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.xiaozheng.recruitment.pojo.Workexperience" >
update workexperience
<set >
<if test="companyname != null" >
companyname = #{companyname,jdbcType=VARCHAR},
</if>
<if test="duty != null" >
duty = #{duty,jdbcType=VARCHAR},
</if>
<if test="startyear != null" >
startyear = #{startyear,jdbcType=INTEGER},
</if>
<if test="endyear != null" >
endyear = #{endyear,jdbcType=INTEGER},
</if>
<if test="startmonth != null" >
startmonth = #{startmonth,jdbcType=INTEGER},
</if>
<if test="endmonth != null" >
endmonth = #{endmonth,jdbcType=INTEGER},
</if>
<if test="type != null" >
type = #{type,jdbcType=VARCHAR},
</if>
<if test="nature != null" >
nature = #{nature,jdbcType=VARCHAR},
</if>
<if test="position != null" >
position = #{position,jdbcType=VARCHAR},
</if>
<if test="department != null" >
department = #{department,jdbcType=VARCHAR},
</if>
<if test="writetime != null" >
writetime = #{writetime,jdbcType=TIMESTAMP},
</if>
<if test="state != null" >
state = #{state,jdbcType=VARCHAR},
</if>
<if test="uid != null" >
uid = #{uid,jdbcType=INTEGER},
</if>
<if test="weight != null" >
weight = #{weight,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.xiaozheng.recruitment.pojo.Workexperience" >
update workexperience
set companyname = #{companyname,jdbcType=VARCHAR},
duty = #{duty,jdbcType=VARCHAR},
startyear = #{startyear,jdbcType=INTEGER},
endyear = #{endyear,jdbcType=INTEGER},
startmonth = #{startmonth,jdbcType=INTEGER},
endmonth = #{endmonth,jdbcType=INTEGER},
type = #{type,jdbcType=VARCHAR},
nature = #{nature,jdbcType=VARCHAR},
position = #{position,jdbcType=VARCHAR},
department = #{department,jdbcType=VARCHAR},
writetime = #{writetime,jdbcType=TIMESTAMP},
state = #{state,jdbcType=VARCHAR},
uid = #{uid,jdbcType=INTEGER},
weight = #{weight,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
<!-- 根据#查询 -->
<select id="listByUidList" parameterType="java.util.List" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
workexperience
WHERE uid IN
<foreach collection="list" item="item" open="(" separator="," close=")">#{item,jdbcType=INTEGER}</foreach>
</select>
<!-- 根据$查询 -->
<select id="listByUidList2" parameterType="java.util.List" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
workexperience
WHERE uid IN
<foreach collection="list" item="item" open="(" separator="," close=")">${item}</foreach>
</select>
<!-- 根据外部拼接好数据 + $查询 -->
<select id="listByUidList3" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
workexperience
WHERE uid IN (${uidStr})
</select>
</mapper>
测试代码：
package recruitmentWebsite.util.test;
import com.xiaozheng.recruitment.dao.WorkexperienceMapper;
import com.xiaozheng.recruitment.pojo.Workexperience;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @author xiaozheng
* @version 1.0
* @date 2021/1/22 15:00
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:applicationContext.xml", "classpath:sqlMapConfig.xml" })
@Transactional
public class workexperience {
private Integer[] uId = new Integer[]{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999};
@Autowired
private WorkexperienceMapper workexperienceMapper;
@Test
public void testFindById(){
Integer id = 1;
long sTime = System.currentTimeMillis();
Workexperience workexperience = workexperienceMapper.selectByPrimaryKey(id);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println(workexperience);
System.out.println("程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
@Test
public void test1to100(){
StringBuilder sb = new StringBuilder();
for(int i = 0; i < 1000; i++) {
sb.append(i + ",");
}
System.out.println(sb.toString());
}
public Workexperience getWorkexperience(int i){
int sub = (int) (Math.random() * 1000);
Workexperience workexperience = new Workexperience();
workexperience.setCompanyname("测试公司名称" + i);
workexperience.setDepartment("测试部们名称" + i);
workexperience.setDuty("测试职责" + i);
workexperience.setStartmonth(12);
workexperience.setStartyear(2018);
workexperience.setEndyear(2020);
workexperience.setEndmonth(12);
workexperience.setType("测试类型" + i);
workexperience.setState("测试状态100");
workexperience.setPosition("测试位置" + i);
workexperience.setNature("测试职位" + i);
workexperience.setWritetime(new Date());
System.out.println(sub + " :" + uId[sub]);
workexperience.setUid(uId[sub]);
return workexperience;
}
@Test
@Rollback(false)
public void createData(){
for(int i = 0; i < 2400000; i++) {
workexperienceMapper.insert(getWorkexperience(i));
System.out.println(i);
}
}
@Test
public void listByUid(){
List<Integer> ints = Arrays.asList(uId);
long sTime = System.currentTimeMillis();
List<Workexperience> workexperiences = workexperienceMapper.listByUidList(ints);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println("查询到数据条目数:" + workexperiences.size() +"程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
@Test
public void listByUid2(){
List<Integer> ints = Arrays.asList(uId);
long sTime = System.currentTimeMillis();
List<Workexperience> workexperiences = workexperienceMapper.listByUidList2(ints);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println("查询到数据条目数:" + workexperiences.size() +"程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
@Test
public void listByUid3(){
List<Integer> ints = Arrays.asList(uId);
long sTime = System.currentTimeMillis();
StringBuilder sb = new StringBuilder();
for(Integer integer : ints) {
sb.append(integer + ",");
}
String uIdStr = sb.toString().substring(0, sb.toString().length() - 1);
List<Workexperience> workexperiences = workexperienceMapper.listByUidList3(uIdStr);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println("查询到数据条目数:" + workexperiences.size() +"程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
}
实验
- 创建数据
private Integer[] uId = new Integer[]{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999};
@Autowired
private WorkexperienceMapper workexperienceMapper;
@Test
public void testFindById(){
Integer id = 1;
long sTime = System.currentTimeMillis();
Workexperience workexperience = workexperienceMapper.selectByPrimaryKey(id);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println(workexperience);
System.out.println("程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
@Test
public void test1to100(){
StringBuilder sb = new StringBuilder();
for(int i = 0; i < 1000; i++) {
sb.append(i + ",");
}
System.out.println(sb.toString());
}
public Workexperience getWorkexperience(int i){
int sub = (int) (Math.random() * 1000);
Workexperience workexperience = new Workexperience();
workexperience.setCompanyname("测试公司名称" + i);
workexperience.setDepartment("测试部们名称" + i);
workexperience.setDuty("测试职责" + i);
workexperience.setStartmonth(12);
workexperience.setStartyear(2018);
workexperience.setEndyear(2020);
workexperience.setEndmonth(12);
workexperience.setType("测试类型" + i);
workexperience.setState("测试状态100");
workexperience.setPosition("测试位置" + i);
workexperience.setNature("测试职位" + i);
workexperience.setWritetime(new Date());
System.out.println(sub + " :" + uId[sub]);
workexperience.setUid(uId[sub]);
return workexperience;
}
@Test
@Rollback(false)
public void createData(){
for(int i = 0; i < 2400000; i++) {
workexperienceMapper.insert(getWorkexperience(i));
System.out.println(i);
}
}
- 编写# 、$、外部拼接数据库
<!-- 根据#查询 -->
<select id="listByUidList" parameterType="java.util.List" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
workexperience
WHERE uid IN
<foreach collection="list" item="item" open="(" separator="," close=")">#{item,jdbcType=INTEGER}</foreach>
</select>
<!-- 根据$查询 -->
<select id="listByUidList2" parameterType="java.util.List" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
workexperience
WHERE uid IN
<foreach collection="list" item="item" open="(" separator="," close=")">${item}</foreach>
</select>
<!-- 根据外部拼接好数据 + $查询 -->
<select id="listByUidList3" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM
workexperience
WHERE uid IN (${uidStr})
</select>
- Mapper
List<Workexperience> listByUidList(List<Integer> list);
List<Workexperience> listByUidList2(List<Integer> list);
List<Workexperience> listByUidList3(@Param(value="uidStr") String uidStr);
- 测试方法
@Test
public void listByUid(){
List<Integer> ints = Arrays.asList(uId);
long sTime = System.currentTimeMillis();
List<Workexperience> workexperiences = workexperienceMapper.listByUidList(ints);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println("查询到数据条目数:" + workexperiences.size() +"程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
@Test
public void listByUid2(){
List<Integer> ints = Arrays.asList(uId);
long sTime = System.currentTimeMillis();
List<Workexperience> workexperiences = workexperienceMapper.listByUidList2(ints);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println("查询到数据条目数:" + workexperiences.size() +"程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
@Test
public void listByUid3(){
List<Integer> ints = Arrays.asList(uId);
long sTime = System.currentTimeMillis();
StringBuilder sb = new StringBuilder();
for(Integer integer : ints) {
sb.append(integer + ",");
}
String uIdStr = sb.toString().substring(0, sb.toString().length() - 1);
List<Workexperience> workexperiences = workexperienceMapper.listByUidList3(uIdStr);
long eTime = System.currentTimeMillis();
System.out.println("============================");
System.out.println("查询到数据条目数:" + workexperiences.size() +"程序执行时间: " + (eTime - sTime) + "毫秒");
System.out.println("============================");
}
测试
1w条数据
#： 程序执行时间: 492毫秒
$: 程序执行时间: 539毫秒
拼接 + $ ： 程序执行时间: 436毫秒
总表格
以1万个条件驱动200万表数据查询
总结
记住：1000个查询条件 驱动表查询
总体来看的话，性能提升不高
以前看过一句话，现在mybatis内部优化非常不错，foreach跟java程序里面的循环性能差不多， 因此这样做没多大效率
确实当条件增加时，在一定程度上能优化in效率，不过还是推荐用exist
以上仅仅代表个人看法，有不一样的测试数据可以分享看看
授人以鱼不如授人以渔，知道的越多，不知道的越多，希望对你有帮助！\color{red}授人以鱼不如授人以渔，知道的越多，不知道的越多，希望对你有帮助！授人以鱼不如授人以渔，知道的越多，不知道的越多，希望对你有帮助！
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
1
0- 0
已为社区贡献2条内容
所有评论(0)