sql优化小技巧

本文参考：http://t.csdnimg.cn/wWksm

1.不使用select *

通常在业务中无需使用到所有的表字段，使用select *造成查询数据冗余，浪费系统性能

2.使用union all代替union

此关键字会将多条查询的结果合并，union all直接返回合并结果，union会把重复字段去重，此操作消耗额外性能

3.使用小表驱动大表

如下，先进行子查询

select * from order
where user_id in (select id from user where status=1)

或者使用连接查询

#交由数据库优化器自动决定

SELECT orders.* 
FROM orders 
INNER JOIN users ON orders.user_id = users.id 
WHERE users.status = 1;

#自定义左右连接

SELECT orders.* 
FROM orders 
RIGHT JOIN users ON orders.user_id = users.id 
WHERE users.status = 1;

4.批量操作

使用批量操作减少数据库连接操作，避免不必要的性能消耗

#使用sql批量插入，而不是for循环逐条插入
<insert id="batchInsertUsers">
    INSERT INTO users (name, email) VALUES
    <foreach collection="userList" item="user" index="index" separator=",">
        (#{user.name}, #{user.email})
    </foreach>
</insert>

5.尽量使用limit

不使用select *以限制查询列数，使用limit限制查询行数，比如我们要查询今天的第一个订单，不要把订单数据都查询出来，然后取集合的第一个，而是查询时使用order by limit 1类似查询

6.in中的值不要过多，一般1000以下

in中值过多导致接口响应时间慢，如果实在要查，建议分多次，或多线程查询合并返回

多线程查询示例代码：
 

//UserMapper.java

public interface UserMapper {
    List<User> selectUsersByIds(@Param("idList") List<Integer> idList);
}

//UserMapper.xml

<select id="selectUsersByIds" resultType="User">
    SELECT * FROM users WHERE id IN
    <foreach item="id" index="index" collection="idList" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

//UserService.java

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

public class UserService {

    private static final int BATCH_SIZE = 1000; // 每个小查询包含的数值数量
    private static final int THREAD_POOL_SIZE = 10; // 线程池大小

    private UserMapper userMapper;

    public UserService(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    public List<User> getUsersByIds(List<Integer> ids) {
        List<User> result = new ArrayList<>();
        ExecutorService executorService = Executors.newFixedThreadPool(THREAD_POOL_SIZE);
        List<Future<List<User>>> futures = new ArrayList<>();

        // 拆分大查询
        for (int i = 0; i < ids.size(); i += BATCH_SIZE) {
            List<Integer> batchList = ids.subList(i, Math.min(i + BATCH_SIZE, ids.size()));
            futures.add(executorService.submit(new UserQueryTask(userMapper, batchList)));
        }

        // 获取结果
        for (Future<List<User>> future : futures) {
            try {
                result.addAll(future.get());
            } catch (InterruptedException | ExecutionException e) {
                e.printStackTrace();
            }
        }

        executorService.shutdown();
        return result;
    }

    // Callable 任务类
    private static class UserQueryTask implements Callable<List<User>> {
        private UserMapper userMapper;
        private List<Integer> idList;

        public UserQueryTask(UserMapper userMapper, List<Integer> idList) {
            this.userMapper = userMapper;
            this.idList = idList;
        }

        @Override
        public List<User> call() throws Exception {
            return userMapper.selectUsersByIds(idList);
        }
    }
}

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import java.util.Arrays;
import java.util.List;

public class Main {
    public static void main(String[] args) {
        SqlSessionFactory sqlSessionFactory = MyBatisUtil.getSqlSessionFactory();
        try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            UserService userService = new UserService(userMapper);

            // 假设要查询的 ID 列表
            List<Integer> ids = Arrays.asList(1, 2, 3, /* ... 大量 ID ... */ 10000);
            List<User> users = userService.getUsersByIds(ids);

            // 处理查询结果
            users.forEach(user -> System.out.println(user));
        }
    }
}