转载请标明出处：https://blog.csdn.net/men_ma/article/details/106847165.
本文出自 不怕报错 就怕不报错的小猿猿 的博客

项目名字：碧桂园文策系统（房地产项目）

第一个坑：SQL查询速度慢

踩坑之处：

测试数据库中有40w+的数据，没查询一次这个接口要七秒的时间，即便做了mybatis_plus自动分页，还是查询速度慢，前端页面大概响应6,7秒左右才出第一页20条数据。

原SQL如下：

SELECT 
u.id, 
u.account, 
u.is_configured AS configured, 
u.is_resign AS resign, 
u.real_name AS realName, 
u.dept_code AS deptCode, 
u.bip_user_type AS bipUserType, 
u.status, 
u.role_id AS roleId, 
u.type, 
u.create_time AS createTime, 
u.update_time AS updateTime, 
GROUP_CONCAT(CASE WHEN r.name != '' THEN r.name END) AS roleName 
FROM user u 
LEFT JOIN dept d
ON u.dept_code = d.dept_code 
LEFT JOIN role r ON FIND_IN_SET(r.id, u.role_id) 
WHERE 
u.is_configured = 0 
AND u.is_resign = 0 
AND u.type = 1 
AND u.is_deleted = 0 
GROUP BY u.id 
ORDER BY u.update_time DESC, 
u.id DESC
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

原mybatis的mapper：

   <select id="listUserConfigManage" resultType="com.bgy.entity.result.ListUserConfigManageResult">
        select
        u.id,
        u.account,
        u.is_configured AS configured,
        u.is_resign AS resign,
        u.real_name AS realName,
        u.dept_code AS deptCode,
        u.bip_user_type AS bipUserType,
        u.status,
        u.role_id AS roleId,
        u.type ,
        u.create_time AS createTime,
        u.update_time AS updateTime,
        GROUP_CONCAT(case when r.name!='' then r.name end) AS roleName
        from user u
        left join dept d
        on u.dept_code=d.dept_code
        left join role r
        on FIND_IN_SET(r.id,u.role_id)
        <where>
            <if test="vo.configured==0">
                and u.is_configured=0 and u.is_resign=0
            </if>
            <if test="vo.configured==1">
                and u.is_configured=1 and u.is_resign=0
            </if>
            <if test="vo.configured==2">
                and u.is_resign=1
            </if>
            <if test="vo.deptCode!=null">
                and u.dept_code=#{vo.deptCode}
            </if>
            <if test="vo.userName!=null">
                and (u.account=#{vo.userName} or u.real_name=#{vo.userName})
            </if>
            <if test="vo.type!=null">
                and u.type=#{vo.type}
            </if>
            and u.is_deleted=0
        </where>
        GROUP BY u.id
        order by u.update_time desc,u.id desc
    </select>
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

解决方案：

修改SQL为手动分页+先按条件查询后再进行分组最后排序（利用子查询的方法）。

优化了SQL后，查询第一页前20条数据只需要2秒时间就可查出结果了，由自动分页改成手动分页，需注意limit的参数问题：LIMIT $(vo.page-1)\ast vo.limit,${vo.limit}（下面会继续记录分页踩的坑）

修改后的SQL：

 select 
 temp.*, 
 GROUP_CONCAT(case when r.name!='' then r.name end) AS roleName 
 from ( 
	select 
	u.id, 
	u.account,
	u.is_configured AS configured,
	u.is_resign AS resign,
	u.real_name AS realName, 
	u.dept_code AS deptCode,
	u.bip_user_type AS bipUserType, 
	u.status,
	u.role_id AS roleId, 
	u.type , 
	u.create_time AS createTime, 
	u.update_time AS updateTime 
	from user u 
	left join dept d 
	on u.dept_code=d.dept_code 
	WHERE u.type=1 and u.is_deleted=0  limit 0,20
	) temp 
left join role r 
on FIND_IN_SET(r.id,temp.roleId) 
GROUP BY temp.id 
order by temp.updateTime desc,temp.id desc
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

修改过后的mybatis的mapper：

<select id="list" resultType="com.bgy.entity.result.ListUserConfigManageResult">
        select
            temp.*,
            GROUP_CONCAT(case when r.name!='' then r.name end) AS roleName
        from (
                select
                u.id,
                u.account,
                u.is_configured AS configured,
                u.is_resign AS resign,
                u.real_name AS realName,
                u.dept_code AS deptCode,
                u.bip_user_type AS bipUserType,
                u.status,
                u.role_id AS roleId,
                u.type ,
                u.create_time AS createTime,
                u.update_time AS updateTime
                from user u
                left join dept d
                on u.dept_code=d.dept_code
                <where>
                    1 = 1
                        <if test="vo.configured==0">
                           and u.is_configured=0 and u.is_resign=0
                        </if>
                        <if test="vo.configured==1">
                            and u.is_configured=1 and u.is_resign=0
                        </if>
                        <if test="vo.configured==2">
                            and u.is_resign=1
                        </if>
                        <if test="vo.deptCode!=null">
                            and u.dept_code=#{vo.deptCode}
                        </if>
                        <if test="vo.userName!=null">
                            and u.account=#{vo.userName} or u.real_name=#{vo.userName}
                        </if>
                        <if test="vo.type!=null">
                            and u.type=#{vo.type}
                        </if>
                        and u.is_deleted=0
                    </where>
                        LIMIT ${(vo.page-1)*vo.limit},${vo.limit}
            ) temp
        left join role r
        on FIND_IN_SET(r.id,temp.roleId)
        GROUP BY temp.id
        order by temp.updateTime desc,temp.id desc
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select
        count(*)
        from user u
        <where>
            1 = 1
            <if test="vo.configured==0">
               and u.is_configured=0 and u.is_resign=0
            </if>
            <if test="vo.configured==1">
                and u.is_configured=1 and u.is_resign=0
            </if>
            <if test="vo.configured==2">
                and u.is_resign=1
            </if>
            <if test="vo.deptCode!=null">
                and u.dept_code=#{vo.deptCode}
            </if>
            <if test="vo.userName!=null">
                and (u.account=#{vo.userName} or u.real_name=#{vo.userName})
            </if>
            <if test="vo.type!=null">
                and u.type=#{vo.type}
            </if>
            and u.is_deleted=0
        </where>
    </select>
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

第二个坑：mybatis中LIMIT之后的语法问题

LIMIT语法分析：

客户端通过传递page(页码)，limit(每页显示的条数)两个参数去分页查询数据库表中的数据，我们知道MySql数据库提供了分页的函数LIMIT m，n。

LIMIT后面，可以跟两个参数

第一个参数，OFFSET，可选参数，表示偏移量，如果不指定默认值为0

如果，偏移量为0，则从查询结果的第一条记录开始，偏移量为1，则从查询结果中的第二条记录开始，以此类推

第二个参数，记录数，表示返回查询结果的条数

LIMIT案例分析

参考博客：https://blog.csdn.net/u013182595/article/details/109293991
例如：

     查询第1条到第10条的数据的sql是：select * from table limit 0,10;   ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

     查询第11条到第20条的数据的sql是：select * from table limit 10,10;  ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

    通过上面的分析，可以归纳得出符合需求的分页SQL伪代码，如下所示。总之，我们只需要告诉数据库要从第几行开始拿多少条数据就行了。
1
2
3
4
5

mysql分页：select * from 表 limit (page-1)*limit,limit;

oracle分页：select a.* (select 表.*,rowum rn from 表) a where rn>(page-1)limit and rn <=pagelimit;

踩坑之处：

原LIMIT语句：mybatis中使用LIMIT #{pageSize} // 错误（不能用#）

在上述SQL中由mybatis_plus自动分页改成了mybatis手动分页
最开始用的是： LIMIT  #{vo.page}-1*#{vo.limit},#{vo.limit};//错误的
无法计算
1
2
3

解决方案：

修改后的LIMIT语句：把#{pageSize} 改 ${pageSize} //正确

把SQL中的LIMIT改成这样： LIMIT ${(vo.page-1)*vo.limit},${vo.limit}
1