0%

mysql之in和exists到底哪个快

I have a problem that has bothered me for a long time.

一、环境准备

  1. 定义表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE ie_table_in (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL DEFAULT '',
phone char(11) NOT NULL DEFAULT '',
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE ie_table_exist (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL DEFAULT '',
phone char(11) NOT NULL DEFAULT '',
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  1. 定义存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
delimiter $$

create procedure init_in_exist()

begin
declare i int;
set i=0;
while(i<10000)do
insert into ie_table_in(name,phone) value(substring(md5(rand()),1,20),concat('1',ceiling(rand()*9000000000+1000000000)));
insert into ie_table_exist(name,phone) value(substring(md5(rand()),1,20),concat('1',ceiling(rand()*9000000000+1000000000)));
set i=i+1;
end while;
end$$

delimiter ;
  1. 调用存储过程call init_in_exist();

二、in

  1. 执行查询
1
2
3
4
5
select * from ie_table_in t1
where t1.id in (
select id from ie_table_exist t2
where t1.id=t2.id
);
  1. explain select * from ie_table_in t1 where t1.id in (select id from ie_table_exist t2 where t1.id=t2.id)\G
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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 9863
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: exec.t2.id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 2 warnings (0.00 sec)
  1. 同样可开启profiling和optimizer_trace
    • show profile all for query [ID];
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
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
 *************************** 1. row ***************************
Status: starting
Duration: 0.000111
CPU_user: 0.000101
CPU_system: 0.000010
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: NULL
Source_file: NULL
Source_line: NULL
*************************** 2. row ***************************
Status: checking permissions
Duration: 0.000011
CPU_user: 0.000007
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 3. row ***************************
Status: checking permissions
Duration: 0.000011
CPU_user: 0.000009
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 4. row ***************************
Status: checking permissions
Duration: 0.000005
CPU_user: 0.000003
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 5. row ***************************
Status: checking permissions
Duration: 0.000005
CPU_user: 0.000003
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 6. row ***************************
Status: Opening tables
Duration: 0.000027
CPU_user: 0.000025
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: open_tables
Source_file: sql_base.cc
Source_line: 5714
*************************** 7. row ***************************
Status: init
Duration: 0.000168
CPU_user: 0.000113
CPU_system: 0.000056
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 1
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 121
*************************** 8. row ***************************
Status: System lock
Duration: 0.000015
CPU_user: 0.000012
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_lock_tables
Source_file: lock.cc
Source_line: 323
*************************** 9. row ***************************
Status: optimizing
Duration: 0.000036
CPU_user: 0.000033
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 151
*************************** 10. row ***************************
Status: statistics
Duration: 0.000113
CPU_user: 0.000111
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 367
*************************** 11. row ***************************
Status: preparing
Duration: 0.000034
CPU_user: 0.000031
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 475
*************************** 12. row ***************************
Status: executing
Duration: 0.000005
CPU_user: 0.000003
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 119
*************************** 13. row ***************************
Status: Sending data
Duration: 0.019846
CPU_user: 0.019599
CPU_system: 0.000246
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 75
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 195
*************************** 14. row ***************************
Status: end
Duration: 0.000011
CPU_user: 0.000004
CPU_system: 0.000007
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 199
*************************** 15. row ***************************
Status: query end
Duration: 0.000009
CPU_user: 0.000009
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 4946
*************************** 16. row ***************************
Status: closing tables
Duration: 0.000011
CPU_user: 0.000010
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 4998
*************************** 17. row ***************************
Status: freeing items
Duration: 0.000017
CPU_user: 0.000012
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 1
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_parse
Source_file: sql_parse.cc
Source_line: 5610
*************************** 18. row ***************************
Status: cleaning up
Duration: 0.000016
CPU_user: 0.000016
CPU_system: 0.000000
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: dispatch_command
Source_file: sql_parse.cc
Source_line: 1924
18 rows in set, 1 warning (0.01 sec)
  • SELECT TRACE INTO outfile '/Users/liuyulong/mysql_trace/in.log' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

三、exists

  1. 执行查询
1
2
3
4
5
select * from ie_table_in t1
where exists(
select id from ie_table_exist t2
where t1.id=t2.id
);
  1. explain select * from ie_table_in t1 where exists(select id from ie_table_exist t2 where t1.id=t2.id)\G
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
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9890
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t2
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: exec.t1.id
rows: 1
filtered: 100.00
Extra: Using index
2 rows in set, 2 warnings (0.00 sec)

三、对比

  1. show profiles\G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.01895000
Query: select * from ie_table_in t1
where t1.id in (
select id from ie_table_exist t2
where t1.id=t2.id
)
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.05620200
Query: select * from ie_table_in t1
where exists(
select id from ie_table_exist t2
where t1.id=t2.id
)

在内外表数据量相同的情况下,发现in语句反而比exists快

  1. in语句
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
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
mysql> show profile all for query 6\G
*************************** 1. row ***************************
Status: starting
Duration: 0.000118
CPU_user: 0.000108
CPU_system: 0.000010
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: NULL
Source_file: NULL
Source_line: NULL
*************************** 2. row ***************************
Status: checking permissions
Duration: 0.000008
CPU_user: 0.000003
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 3. row ***************************
Status: checking permissions
Duration: 0.000006
CPU_user: 0.000005
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 802
*************************** 4. row ***************************
Status: Opening tables
Duration: 0.000214
CPU_user: 0.000126
CPU_system: 0.000088
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 4
Swaps: 0
Source_function: open_tables
Source_file: sql_base.cc
Source_line: 5714
*************************** 5. row ***************************
Status: init
Duration: 0.000058
CPU_user: 0.000054
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 121
*************************** 6. row ***************************
Status: System lock
Duration: 0.000013
CPU_user: 0.000011
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_lock_tables
Source_file: lock.cc
Source_line: 323
*************************** 7. row ***************************
Status: optimizing
Duration: 0.000016
CPU_user: 0.000015
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 151
*************************** 8. row ***************************
Status: statistics
Duration: 0.000038
CPU_user: 0.000036
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 367
*************************** 9. row ***************************
Status: preparing
Duration: 0.000020
CPU_user: 0.000018
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 475
*************************** 10. row ***************************
Status: executing
Duration: 0.000004
CPU_user: 0.000002
CPU_system: 0.000002
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 119
*************************** 11. row ***************************
Status: Sending data
Duration: 0.018410
CPU_user: 0.016988
CPU_system: 0.001308
Context_voluntary: 0
Context_involuntary: 26
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 75
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 327
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 195
*************************** 12. row ***************************
Status: end
Duration: 0.000010
CPU_user: 0.000004
CPU_system: 0.000006
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 199
*************************** 13. row ***************************
Status: query end
Duration: 0.000007
CPU_user: 0.000006
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 4946
*************************** 14. row ***************************
Status: closing tables
Duration: 0.000007
CPU_user: 0.000006
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 4998
*************************** 15. row ***************************
Status: freeing items
Duration: 0.000012
CPU_user: 0.000008
CPU_system: 0.000003
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 1
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_parse
Source_file: sql_parse.cc
Source_line: 5610
*************************** 16. row ***************************
Status: cleaning up
Duration: 0.000009
CPU_user: 0.000009
CPU_system: 0.000001
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: dispatch_command
Source_file: sql_parse.cc
Source_line: 1924
  1. exists语句
1
大量的executing和Sending data

四、原因分析

待补充