mybatis-plus技巧--动态表名-多语句-拼接sql--关于mybatis的mysql分页查询总数的优化思考

动态表名

xml表名填充

<select>
select *
from ${tableName}
select>
1
2
3
4

传入tableName参数就可以了，不过只能用$不能用#

因为#会发生预编译，然后会在表名上加引号’'。

表名拦截器

新建一个表名拦截类实现TableNameHandler

@Component
public class MyTableHandler implements TableNameHandler {
    @Override
    public String dynamicTableName(String sql, String tableName) {
    	// 表名处理
        return tableName;
    }
}
1
2
3
4
5
6
7
8

mybatisPlus添加插件

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //1.添加动态表名插件
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        dynamicTableNameInnerInterceptor.setTableNameHandler(new MyTableHandler());
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }
1
2
3
4
5
6
7
8
9

实例：

每天按统计

如果表名为count则加上今天的时间

@Component
public class MyTableHandler implements TableNameHandler {
    @Override
    public String dynamicTableName(String sql, String tableName) {
    	// 表名处理
        if (StringUtils.equals(tableName,"count")){
            return tableName+ LocalDate.now();
        }
        return tableName;
    }
}
1
2
3
4
5
6
7
8
9
10
11

每次设置

@Component
public class MyTableHandler implements TableNameHandler {

    // 每个请求线程维护一个数据，避免多线程数据冲突。所以使用ThreadLocal
    private static final ThreadLocal<String> SUFFIX = new ThreadLocal<>();

    // 设置请求线程的month数据
    public static void setData(String suffix) {
        SUFFIX.set(suffix);
    }

    @Override
    public String dynamicTableName(String sql, String tableName) {
        String suffix = SUFFIX.get();
        if(StringUtils.isNotBlank(suffix)){
            SUFFIX.remove();
            return suffix;
        }
        return tableName;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

直接设置名字，然后就会改变的。

   MyTableHandler.setData(tableName);
   list();
1
2

多语句操作

需要在配置文件中的url中新增条件允许多查询allowMultiQueries=true

在xml中多条sql用；隔开，就可以多条sql同时发送执行。

forEach动态拼接

传递List的参数

<update id="updateBatchById">
    <foreach collection="list" item="s" separator=";">
        update
            `user`
        set
            `name` = #{name},
            `salary` = #{salary}
        where
            id = #{id}
    </foreach>
</update>
1
2
3
4
5
6
7
8
9
10
11

参数构建

java进行拼接sql

缺点，没有ide提示，容易敲错

mapper参数传递 @Param(“ew”) LambdaQueryWrapper
param中只能是ew不能是其他的。

在xml中使用：

    <select id="getAllInfo" resultType="com.yu.model.domain.Company">
        select *
        from table
        ${ew.customSqlSegment}
    select>
1
2
3
4
5

ew有多个属性，${ew.sqlSegment}，${ew.sqlSelect}，${ew.customSqlSegment}

mysql分页查询总数

count

不要使用count（常数），count（列名）代替count(*)

由于count不会统计null值，count（列名）可能会导致总数出错。
又因为COUNT(*)是SQL92定义的标准统计行数的语法，因为他是标准语法，所以MySQL数据库对他进行过很多优化。

自己计数

自己维护计数，如存入redis。比较麻烦但是高效。

SQL_CALC_FOUND_ROWS

我觉得如果有大量用这个更好。

SQL_CALC_FOUND_ROWS是MySQL的一个特殊修饰符，用于在执行SELECT查询时同时计算满足条件的总行数。

每次分页都需要count一次，就像mybatis-plus也是在查询之前先进行count一次，如果total不为0在进行下一轮查询。
SQL_CALC_FOUND_ROWS 语句会统计出符合筛选条件的记录总数，保存在mysql 端；

后面使用 SELECT FOUND_ROWS() 语句可以得到这个数字，不用再次计算。当然，如果你再次运行了SQL_CALC_FOUND_ROWS的话，这个数字就会被覆盖成最新的。

不过呢，这个东西说好的和不好的都有。也没有个说法，但是我在实际中可以知道，在某些情况下肯定是有比count快的。
我在sql也不是特别懂,我只能通过实践来进行了，哪个快用哪个。这个呢不太好说。

它的原理是在执行SELECT查询时，MySQL会先执行一次不带LIMIT子句的查询来计算满足条件的总行数，然后再执行带LIMIT子句的查询来返回实际的结果集。这样可以避免在查询结果集之前进行一次COUNT(*)查询，从而提高查询的性能。

使用SQL_CALC_FOUND_ROWS修饰符的查询语句的执行速率取决于满足条件的总行数和实际返回的结果集的大小。如果总行数很大，而实际返回的结果集较小，那么执行速率可能会比较慢。反之，如果总行数和实际返回的结果集大小相差不大，执行速率可能会比较快。

需要注意的是，使用SQL_CALC_FOUND_ROWS修饰符会增加查询的开销，因为MySQL需要执行两次查询。因此，在实际应用中，需要根据具体情况权衡使用SQL_CALC_FOUND_ROWS的优势和开销。

测试中SQL_CALC_FOUND_ROWS 确实会导致第一次查询变慢，但是得到总数快。2条sql进行查询，有时候会快点有时候慢点。

xml单条接口实现

<select id="selectList" resultMap="User,count">
SELECT SQL_CALC_FOUND_ROWS user_id,user_name
FROM user
LIMIT 10;
SELECT FOUND_ROWS() AS total;
select>
1
2
3
4
5
6

mapper

List selectList();
1

servies

List<Object> list= baseMapper.selectList();
List<User> user = list.get(0);
Integer total = list.get(1);
1
2
3

这样，每次都需要list接收，一个数据一个是总数。

mybatis拦截器

我们来看一下对比图，这是mybatis-plus自带的分页插件，吞吐量11


下面是自定义的分页插件，吞吐量17，很明显快一些，当然小数据量/单表的时候速度和count差不多，主要在于多表数据量大的时候提升效果显著。


至于准确率方便，设置有响应断言，都没有异常，可以保证准确率

实现：
也可以当mybatis插件的一个练习吧，借鉴了mybatis-plus的分页插件。
依据是否存在page作为参数来进行是否进行分页查询，这个条件也可以依据自己的更换。

@Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object target = invocation.getTarget();
        try {
            if (target instanceof Executor executor) {
                Object[] args = invocation.getArgs();
                Object parameter = args[1];
                // 处理参数为 IPage 的情况
                IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);

                if (null == page) {
                    return invocation.proceed();
                }
                boolean isUpdate = args.length == 2;
                MappedStatement ms = (MappedStatement) args[0];

                if (!isUpdate && ms.getSqlCommandType() == SqlCommandType.SELECT) {
                    RowBounds rowBounds = (RowBounds) args[2];
                    BoundSql boundSql = ms.getBoundSql(parameter);

                    // 处理 page中 orderBy 拼接
                    boolean addOrdered = false;
                    String buildSql = boundSql.getSql();
                    List<OrderItem> orders = page.orders();
                    if (CollectionUtils.isNotEmpty(orders)) {
                        addOrdered = true;
                        buildSql = this.concatOrderBy(buildSql, orders);
                    }

                    // size 小于 0 且不限制返回值则不构造分页sql
                    Long _limit = page.maxLimit() != null ? page.maxLimit() : 1000;
                    if (addOrdered) {
                        PluginUtils.mpBoundSql(boundSql).sql(buildSql);
                    }

                    handlerLimit(page, _limit);
                    IDialect dialect = DialectFactory.getDialect(DbType.MYSQL);


                    final Configuration configuration = ms.getConfiguration();
                    DialectModel model = dialect.buildPaginationSql(buildSql, page.offset(), page.getSize());
                    PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
                    List<ParameterMapping> mappings = mpBoundSql.parameterMappings();
                    Map<String, Object> additionalParameter = mpBoundSql.additionalParameters();
                    model.consumers(mappings, configuration, additionalParameter);
                    // 加入SQL_CALC_FOUND_ROWS
                    String selectSqlCalcFoundRows = model.getDialectSql()
                            .replaceFirst("(?i)SELECT", "SELECT SQL_CALC_FOUND_ROWS ");
                    mpBoundSql.sql(selectSqlCalcFoundRows);
                    mpBoundSql.parameterMappings(mappings);
              
                    CacheKey cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
                    Connection connection = executor.getTransaction().getConnection();
                    Statement statement = connection.createStatement();
                    // 查询数据 95%
                    List<Object> query = executor.query(ms, parameter, rowBounds, (ResultHandler) args[3], cacheKey, boundSql);
                    // 查询总数 占速率5%
                    ResultSet resultSet = statement.executeQuery("SELECT FOUND_ROWS() AS total;");
                    while (resultSet.next()) {
                        String column = resultSet.getString(1);
                        page.setTotal(Long.parseLong(column));
                    }
                    return query;
                }
            }
        }catch (Exception e){
            log.error("分页失败优化失败，原因：{}",e.getMessage());
            // 打印本次调用的方法和参数
            log.error("本次调用的方法为：{}",invocation.getMethod());
            log.error("本次调用的参数为：{}",invocation.getArgs());
        }
        return invocation.proceed();
    }
    protected List<OrderByElement> addOrderByElements(List<OrderItem> orderList, List<OrderByElement> orderByElements) {
        List<OrderByElement> additionalOrderBy = orderList.stream()
                .filter(item -> StringUtils.isNotBlank(item.getColumn()))
                .map(item -> {
                    OrderByElement element = new OrderByElement();
                    element.setExpression(new Column(item.getColumn()));
                    element.setAsc(item.isAsc());
                    element.setAscDescPresent(true);
                    return element;
                }).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(orderByElements)) {
            return additionalOrderBy;
        }
        // github pull/3550 优化排序，比如：默认 order by id 前端传了name排序，设置为 order by name,id
        additionalOrderBy.addAll(orderByElements);
        return additionalOrderBy;
    }

    /**
     * 处理超出分页条数限制,默认归为限制数
     *
     * @param page IPage
     */
    protected void handlerLimit(IPage<?> page, Long limit) {
        final long size = page.getSize();
        if (limit != null && limit > 0 && (size > limit || size < 0)) {
            page.setSize(limit);
        }
    }
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