存在问题:
1.低版本mysql中in子查询导致不走索引 (测试发现MYSQL5.7之后版本是会走索引)
EXPLAIN
SELECT
*
FROM
workflow_task t
WHERE
t.task_owner_id IN (
SELECT
user_id
FROM
sys_user u
WHERE
u.org_id='027366835bcf4ddb9bf767b370f751ab'
and u. ENABLE = 1
)
执行结果如下:
explain的作用
通过explain+sql语句可以知道如下内容:
①表的读取顺序。(对应id)
②数据读取操作的操作类型。(对应select_type)
③哪些索引可以使用。(对应possible_keys)
④哪些索引被实际使用。(对应key)
⑤表直接的引用。(对应ref)
⑥每张表有多少行被优化器查询。(对应rows)
如果是使用低版本,则可通过关联查询替代,如下:
SELECT
*
FROM
workflow_task t
INNER JOIN sys_user u ON t.task_owner_id = u.user_id
WHERE
u.org_id = '027366835bcf4ddb9bf767b370f751ab'
AND u. ENABLE = 1
2.mybatis xml中使用in的sql长度超过1000
SELECT
*
FROM
sys_user
WHERE
org_id IN (
"34c9bbd6cbce11ea87d10242ac110003",
"3f8df34fca2c11ea87d10242ac110003","b7ee4e85ca4c11ea87d10242ac110003","...")
如果 org_id in 后面的值超过1000会报错
则可修改为如下(在低版本中效率也能提高):
SELECT *
FROM sys_user t,
(
SELECT "34c9bbd6cbce11ea87d10242ac110003" AS org_id UNION ALL
SELECT "3f8df34fca2c11ea87d10242ac110003" AS org_id UNION ALL
SELECT "b7ee4e85ca4c11ea87d10242ac110003" AS org_id UNION ALL
SELECT "F12EB1181DD86029D62EE5936C1DFJNW" AS org_id
) AS o
WHERE o.org_id = t.org_id;
以下再列举下其他方法
方法一.可以通过循环每次1000条,然后结果集拼接,例子如下:
public <T> List<List<T>> getSumArrayList(List<T> list){
List<List<T>> objectlist = new ArrayList<>();
int iSize = list.size()/1000;
int iCount = list.size()%1000;
for(int i=0;i<=iSize;i++){
List<T> newObjList = new ArrayList<>();
if(i==iSize){
for(int j =i*1000;j<i*1000+iCount;j++ ){
newObjList.add(list.get(j));
}
}else{
for(int j =i*1000;j<(i+1)*1000;j++ ){
newObjList.add(list.get(j));
}
}
if(newObjList.size()>0){
objectlist.add(newObjList);
}
}
return objectlist;
}
方法二.可以通过where xxx and (org_id in (1,2,3,4,5 .... ,999) or org_id in(1000,1001,....,1040)) 方式搜索出所有:
改为: 入参为List<List<String>>
SELECT
*
FROM
sys_user u
WHERE
u.enable=1
<if test="orgids!=null and orgids.size()>0" >
and
<foreach collection="orgids" item="orgid" open="(" separator="or" close=")">
org_id in
<foreach collection="orgid" item="orgId" open="(" separator="," close=")">
#{orgId}
</foreach>
</foreach>
</if>