In语句sql优化

(Updated: )
技术
,

待优化语句

主要耗时在in操作时拆分为多个or操作，不走索引

mapperSql

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
<select id="selectCustomerMutilInfo" resultType="com.willing.crm.entity.module.CustomerModule">
    select
        a.external_user_related_id as externalUserId,
        a.remark_mobiles mobiles,
        b.url,
        case b.`status`
            when 1  then '填写表单'
            when 2  then '付款'
            end   AS  isPay,
        c.state AS smartCodeName,
        e.`name` AS customerTags,
        f.name AS addUser,
        f.id AS addUserId,
        f.mobile AS oderMobile,
        f.create_time AS oderCreatTime,
        b.phone AS oderMobile,
        b.create_time AS oderCreatTime,
        a.create_time AS addTime
    from scrm_external_follow_user a
             left join scrm_marketing_form_order b on a.source_id = b.id and a.tenant_id =  b.tenant_id
             left join scrm_external_live_code_channel c on a.code_channel_id = c.id
             left join scrm_external_user_tag d on a.external_user_related_id = d.external_user_related_id and a.tenant_id = d.tenant_id
             left join scrm_external_tag e on e.id = d.tag_id and e.tenant_id = d.tenant_id
             left join scrm_admin_user f on a.user_id = f.id and a.tenant_id = f.tenant_id
    where a.tenant_id = #{tenantId}
    <if test="set != null and set.size > 0">
        AND a.external_user_related_id in
            <foreach collection="set" item="externalUserId" open="(" separator="," close=")">
                #{externalUserId}
            </foreach>
    </if>
</select>

实际sql

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
expla
SELECT
	a.external_user_related_id AS externalUserId,
	a.remark_mobiles mobiles,
	b.url,
CASE
		b.`status` 
		WHEN 1 THEN
		'填写表单' 
		WHEN 2 THEN
		'付款' 
	END AS isPay,
	c.state AS smartCodeName,
	e.`name` AS customerTags,
	f.NAME AS addUser,
	f.id AS addUserId,
	b.phone AS oderMobile,
	b.create_time AS oderCreatTime,
	a.create_time AS addTime 
FROM
	scrm_external_follow_user a
	LEFT JOIN scrm_marketing_form_order b ON a.source_id = b.id 
	AND a.tenant_id = b.tenant_id 
	AND b.tenant_id = 1356898121111711744
	LEFT JOIN scrm_external_live_code_channel c ON a.code_channel_id = c.id 
	AND c.tenant_id = 1356898121111711744
	LEFT JOIN scrm_external_user_tag d ON a.external_user_related_id = d.external_user_related_id 
	AND a.tenant_id = d.tenant_id 
	AND d.tenant_id = 1356898121111711744
	LEFT JOIN scrm_external_tag e ON e.id = d.tag_id 
	AND e.tenant_id = d.tenant_id 
	AND e.tenant_id = 1356898121111711744
	LEFT JOIN scrm_admin_user f ON a.user_id = f.id 
	AND a.tenant_id = f.tenant_id 
	AND f.tenant_id = 1356898121111711744 
WHERE
	a.tenant_id = 1356898121111711744 
	AND a.external_user_related_id IN (
		1414555946910363648,
		1372095997781618688,
		1371746071482609664,
		1387342888324706304,
		1358679833055473664,
		1357243275345539072,
		1381148437218734080,
		1363055974327398400,
		1385468971884621824,
		1367678600614129664,
		1367657180647534592,
		1369229584574328832,
		1419990924817227776,
		1418502480903430144,
		1422169825316331520,
		1406897826746740736,
		1357167653223018496,
		1414103085705474048,
		1405441543946514432,
		1372096409456750592,
		1410212398442360832,
		1357171129940324352,
		1413077754181726208,
		1369992208337481728,
		1393825269957144576,
		1369122211830575104,
		1372095822430351360,
		1368404710561820672,
		1367718878897123328,
		1357174467155079168,
		1364148134112014336,
		1358248387585126400,
		1388083894498242561,
		1387995419438624768,
		1387007537425035264,
		1372095907482447872,
		1363410181576339456,
		1365221194722324480,
		1357247693226913792,
		1405046113333030912,
		1381148148759670784,
		1358677923489525760,
		1405434556194107393,
		1405436207340597248,
		1372111637326213120,
		1377932235235274752,
		1408044509870305280,
		1366679916824899584,
		1413106138416820224,
		1376071016547233792,
		1398112812986937344,
		1369624913224282112,
		1365232708489457664,
		1375333241283817472,
		1369327507962212352,
		1381148472731906048,
		1423592556058533888,
		1365221332568125440,
		1357170867251064832,
		1387965628593352705,
		1357244521703944192,
		1374978257807618048,
		1407940173420965888,
		1413375150254075904,
		1390630309300678656,
		1357287730245152768,
		1390964815383638016,
		1367676017401016320,
		1364415433461084160,
		1412936903854010368,
		1407939421747163136,
		1369327086963142656,
		1380398173624872960,
		1372364718324461568,
		1387998157778071552,
		1357152137334894592,
		1357148559815880704,
		1419945920505733120,
		1413682933675470848 
	) 
	AND a.tenant_id = 1356898121111711744;

sql执行分析

可以看到外层的主表查询是不走索引的
range:只检索给定范围的行，使用一个索引来选择行

优化后语句

将in操作中的数据拼接起来作为一个temp，再用主表join使最外层主表的扫描走索引

mapperSql

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
<select id="selectCustomerMutilInfo" resultType="com.willing.crm.entity.module.CustomerModule">
    select
        a.external_user_related_id as externalUserId,
        a.remark_mobiles mobiles,
        b.url,
        case b.`status`
            when 1  then '填写表单'
            when 2  then '付款'
            end   AS  isPay,
        c.state AS smartCodeName,
        e.`name` AS customerTags,
        f.name AS addUser,
        f.id AS addUserId,
        b.phone AS oderMobile,
        b.create_time AS oderCreatTime,
        a.create_time AS addTime
    from scrm_external_follow_user a
             left join scrm_marketing_form_order b on a.source_id = b.id and a.tenant_id =  b.tenant_id
             left join scrm_external_live_code_channel c on a.code_channel_id = c.id
             left join scrm_external_user_tag d on a.external_user_related_id = d.external_user_related_id and a.tenant_id = d.tenant_id
             left join scrm_external_tag e on e.id = d.tag_id and e.tenant_id = d.tenant_id
             left join scrm_admin_user f on a.user_id = f.id and a.tenant_id = f.tenant_id
            <if test="set != null and set.size > 0">
                join
                <foreach collection="set" item="externalUserId" open="(" separator="UNION ALL" close=")">
                    select #{externalUserId} id
                </foreach>
                AS g on g.id = a.external_user_related_id
            </if>
    where a.tenant_id = #{tenantId}
</select>

实际sql

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
EXPLAIN SELECT a.external_user_related_id AS externalUserId, a.remark_mobiles mobiles, b.url, CASE b.`status` WHEN 1 THEN '填写表单' WHEN 2 THEN '付款' END AS isPay, c.state AS smartCodeName, e.`name` AS customerTags, f.name AS addUser, f.id AS addUserId, b.phone AS oderMobile, b.create_time AS oderCreatTime, a.create_time AS addTime
FROM scrm_external_follow_user a
LEFT JOIN scrm_marketing_form_order b
ON a.source_id = b.id AND a.tenant_id = b.tenant_id AND b.tenant_id = 1356898121111711744
LEFT JOIN scrm_external_live_code_channel c
ON a.code_channel_id = c.id AND c.tenant_id = 1356898121111711744
LEFT JOIN scrm_external_user_tag d
ON a.external_user_related_id = d.external_user_related_id AND a.tenant_id = d.tenant_id AND d.tenant_id = 1356898121111711744
LEFT JOIN scrm_external_tag e
ON e.id = d.tag_id AND e.tenant_id = d.tenant_id AND e.tenant_id = 1356898121111711744
LEFT JOIN scrm_admin_user f
ON a.user_id = f.id AND a.tenant_id = f.tenant_id AND f.tenant_id = 1356898121111711744 JOIN (SELECT 1414555946910363648 id
UNION ALL
SELECT 1372095997781618688 id
UNION ALL
SELECT 1371746071482609664 id
UNION ALL
SELECT 1387342888324706304 id
UNION ALL
SELECT 1358679833055473664 id
UNION ALL
SELECT 1357243275345539072 id
UNION ALL
SELECT 1381148437218734080 id
UNION ALL
SELECT 1363055974327398400 id
UNION ALL
SELECT 1385468971884621824 id
UNION ALL
SELECT 1367678600614129664 id
UNION ALL
SELECT 1367657180647534592 id
UNION ALL
SELECT 1369229584574328832 id
UNION ALL
SELECT 1419990924817227776 id
UNION ALL
SELECT 1418502480903430144 id
UNION ALL
SELECT 1422169825316331520 id
UNION ALL
SELECT 1406897826746740736 id
UNION ALL
SELECT 1357167653223018496 id
UNION ALL
SELECT 1414103085705474048 id
UNION ALL
SELECT 1405441543946514432 id
UNION ALL
SELECT 1372096409456750592 id
UNION ALL
SELECT 1410212398442360832 id
UNION ALL
SELECT 1357171129940324352 id
UNION ALL
SELECT 1413077754181726208 id
UNION ALL
SELECT 1369992208337481728 id
UNION ALL
SELECT 1393825269957144576 id
UNION ALL
SELECT 1369122211830575104 id
UNION ALL
SELECT 1372095822430351360 id
UNION ALL
SELECT 1368404710561820672 id
UNION ALL
SELECT 1367718878897123328 id
UNION ALL
SELECT 1357174467155079168 id
UNION ALL
SELECT 1364148134112014336 id
UNION ALL
SELECT 1358248387585126400 id
UNION ALL
SELECT 1388083894498242561 id
UNION ALL
SELECT 1387995419438624768 id
UNION ALL
SELECT 1387007537425035264 id
UNION ALL
SELECT 1372095907482447872 id
UNION ALL
SELECT 1363410181576339456 id
UNION ALL
SELECT 1365221194722324480 id
UNION ALL
SELECT 1357247693226913792 id
UNION ALL
SELECT 1405046113333030912 id
UNION ALL
SELECT 1381148148759670784 id
UNION ALL
SELECT 1358677923489525760 id
UNION ALL
SELECT 1405434556194107393 id
UNION ALL
SELECT 1405436207340597248 id
UNION ALL
SELECT 1372111637326213120 id
UNION ALL
SELECT 1377932235235274752 id
UNION ALL
SELECT 1408044509870305280 id
UNION ALL
SELECT 1366679916824899584 id
UNION ALL
SELECT 1413106138416820224 id
UNION ALL
SELECT 1376071016547233792 id
UNION ALL
SELECT 1398112812986937344 id
UNION ALL
SELECT 1369624913224282112 id
UNION ALL
SELECT 1365232708489457664 id
UNION ALL
SELECT 1375333241283817472 id
UNION ALL
SELECT 1369327507962212352 id
UNION ALL
SELECT 1381148472731906048 id
UNION ALL
SELECT 1423592556058533888 id
UNION ALL
SELECT 1365221332568125440 id
UNION ALL
SELECT 1357170867251064832 id
UNION ALL
SELECT 1387965628593352705 id
UNION ALL
SELECT 1357244521703944192 id
UNION ALL
SELECT 1374978257807618048 id
UNION ALL
SELECT 1407940173420965888 id
UNION ALL
SELECT 1413375150254075904 id
UNION ALL
SELECT 1390630309300678656 id
UNION ALL
SELECT 1357287730245152768 id
UNION ALL
SELECT 1390964815383638016 id
UNION ALL
SELECT 1367676017401016320 id
UNION ALL
SELECT 1364415433461084160 id
UNION ALL
SELECT 1412936903854010368 id
UNION ALL
SELECT 1407939421747163136 id
UNION ALL
SELECT 1369327086963142656 id
UNION ALL
SELECT 1380398173624872960 id
UNION ALL
SELECT 1372364718324461568 id
UNION ALL
SELECT 1387998157778071552 id
UNION ALL
SELECT 1357152137334894592 id
UNION ALL
SELECT 1357148559815880704 id
UNION ALL
SELECT 1419945920505733120 id
UNION ALL
SELECT 1413682933675470848 id) AS g
ON g.id = a.external_user_related_id
WHERE a.tenant_id = 1356898121111711744 AND a.tenant_id = 1356898121111711744;

sql执行分析

拼接IN中数据的UNION操作和DERIVED操作变成常数级别的时间
由于将数据拼接为了临时表并在ON条件后使用了索引字段，所以最外层的查询操作变成了ref
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

优化结果

原查询 ： 0.572s
优化后 ： 0.225s