質問編集履歴
5
詳細分析
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -312,4 +312,72 @@
|
|
|
312
312
|
|
|
313
313
|
|
|
314
314
|
|
|
315
|
+
## 追記 USE INDEX 付加
|
|
316
|
+
|
|
317
|
+
|
|
318
|
+
|
|
319
|
+
`USE INDEX (PRIMARY)` を付加したところ、0.022 秒程度のクエリとなりました。実行計画は以下です。
|
|
320
|
+
|
|
321
|
+
|
|
322
|
+
|
|
323
|
+
```
|
|
324
|
+
|
|
325
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
326
|
+
|
|
327
|
+
1 SIMPLE dtb_bill range PRIMARY PRIMARY 8 NULL 2100 100.00 Using where
|
|
328
|
+
|
|
329
|
+
```
|
|
330
|
+
|
|
331
|
+
|
|
332
|
+
|
|
333
|
+
## 追記 SHOW PROFILE 結果
|
|
334
|
+
|
|
335
|
+
|
|
336
|
+
|
|
337
|
+
右が、USE INDEX 付加後。statistics が大きく異なる。
|
|
338
|
+
|
|
339
|
+
|
|
340
|
+
|
|
341
|
+
```
|
|
342
|
+
|
|
343
|
+
Status Duration
|
|
344
|
+
|
|
345
|
+
starting 0.005219 0.005266
|
|
346
|
+
|
|
347
|
+
checking permissions 0.000007 0.000005
|
|
348
|
+
|
|
349
|
+
Opening tables 0.000023 0.000017
|
|
350
|
+
|
|
351
|
+
init 0.003204 0.002664
|
|
352
|
+
|
|
353
|
+
System lock 0.000010 0.000007
|
|
354
|
+
|
|
355
|
+
optimizing 0.001004 0.000933
|
|
356
|
+
|
|
357
|
+
statistics 1.818584 0.002057
|
|
358
|
+
|
|
359
|
+
preparing 0.001847 0.001844
|
|
360
|
+
|
|
361
|
+
Sorting result 0.000004 0.000004
|
|
362
|
+
|
|
363
|
+
executing 0.000002 0.000002
|
|
364
|
+
|
|
365
|
+
Sending data 0.000102 0.000078
|
|
366
|
+
|
|
367
|
+
end 0.000004 0.000003
|
|
368
|
+
|
|
369
|
+
query end 0.000005 0.000004
|
|
370
|
+
|
|
371
|
+
closing tables 0.000009 0.000006
|
|
372
|
+
|
|
373
|
+
freeing items 0.000598 0.000533
|
|
374
|
+
|
|
375
|
+
cleaning up 0.000008 0.000008
|
|
376
|
+
|
|
377
|
+
|
|
378
|
+
|
|
379
|
+
```
|
|
380
|
+
|
|
381
|
+
|
|
382
|
+
|
|
315
383
|
よろしくお願いいたします。
|
4
調べて追記
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -2,6 +2,10 @@
|
|
|
2
2
|
|
|
3
3
|
|
|
4
4
|
|
|
5
|
+
**※最下部に現状を追記しました。**
|
|
6
|
+
|
|
7
|
+
|
|
8
|
+
|
|
5
9
|
# テーブル
|
|
6
10
|
|
|
7
11
|
```SQL
|
|
@@ -36,11 +40,11 @@
|
|
|
36
40
|
|
|
37
41
|
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE
|
|
38
42
|
|
|
39
|
-
(customer_id IN (...)) -- 2
|
|
43
|
+
(customer_id IN (...)) -- 約 2000 通り
|
|
40
44
|
|
|
41
45
|
AND
|
|
42
46
|
|
|
43
|
-
(id IN (...)) --
|
|
47
|
+
(id IN (...)) -- 約 2200 通り
|
|
44
48
|
|
|
45
49
|
ORDER BY `id` desc LIMIT 20;
|
|
46
50
|
|
|
@@ -54,6 +58,8 @@
|
|
|
54
58
|
|
|
55
59
|
※追記: サブクエリはありません。IN の中には数値の列が与えられます。
|
|
56
60
|
|
|
61
|
+
※追記: 数が逆でした。
|
|
62
|
+
|
|
57
63
|
|
|
58
64
|
|
|
59
65
|
# my.cnf
|
|
@@ -214,4 +220,96 @@
|
|
|
214
220
|
|
|
215
221
|
|
|
216
222
|
|
|
223
|
+
## 追記
|
|
224
|
+
|
|
225
|
+
アプリケーション側のコードを追って、SQL を突き止めました。テーブルの構成は、もともとの構成に戻してはいますが、概要は変わりません。
|
|
226
|
+
|
|
227
|
+
|
|
228
|
+
|
|
229
|
+
```SQL
|
|
230
|
+
|
|
231
|
+
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE
|
|
232
|
+
|
|
233
|
+
(customer_id IN (SELECT文 A))
|
|
234
|
+
|
|
235
|
+
AND
|
|
236
|
+
|
|
237
|
+
(id IN (SELECT文 B))
|
|
238
|
+
|
|
239
|
+
ORDER BY `id` desc LIMIT 20;
|
|
240
|
+
|
|
241
|
+
```
|
|
242
|
+
|
|
243
|
+
が本来行いたい処理であるようです。
|
|
244
|
+
|
|
245
|
+
|
|
246
|
+
|
|
247
|
+
これを
|
|
248
|
+
|
|
249
|
+
```SQL
|
|
250
|
+
|
|
251
|
+
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE
|
|
252
|
+
|
|
253
|
+
(customer_id IN (SELECT文 A の「結果カンマ区切り」))
|
|
254
|
+
|
|
255
|
+
AND
|
|
256
|
+
|
|
257
|
+
(id IN (SELECT文 B の「結果カンマ区切り」))
|
|
258
|
+
|
|
259
|
+
ORDER BY `id` desc LIMIT 20;
|
|
260
|
+
|
|
261
|
+
```
|
|
262
|
+
|
|
263
|
+
で処理させていて(一度途中経過を取っているようです)、そうすると、性能劣化が生じます。
|
|
264
|
+
|
|
265
|
+
|
|
266
|
+
|
|
267
|
+
両方 SELECT 文なら、0.009 秒、片方 SELECT 文の時、A が SELECT のとき 0.009 秒、B が SELECT のとき 0.035 秒でした。両方カンマ区切りにすると、約 1.9 秒で、これだけやけに遅いです。
|
|
268
|
+
|
|
269
|
+
|
|
270
|
+
|
|
271
|
+
クエリの実行計画は、両方カンマ区切りが
|
|
272
|
+
|
|
273
|
+
```
|
|
274
|
+
|
|
275
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
276
|
+
|
|
277
|
+
1 SIMPLE dtb_bill range PRIMARY,customer_id PRIMARY 8 NULL 2100 100.00 Using where
|
|
278
|
+
|
|
279
|
+
```
|
|
280
|
+
|
|
281
|
+
で A が SELECT 文のとき
|
|
282
|
+
|
|
283
|
+
```
|
|
284
|
+
|
|
285
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
286
|
+
|
|
287
|
+
1 SIMPLE dtb_bill range PRIMARY,customer_id PRIMARY 8 NULL 2100 100.00 Using where
|
|
288
|
+
|
|
289
|
+
1 SIMPLE dtb_customer eq_ref PRIMARY,----------_by PRIMARY 8 perf_test.dtb_bill.customer_id 1 100.00 Using where
|
|
290
|
+
|
|
291
|
+
```
|
|
292
|
+
|
|
293
|
+
で B が SELECT 文のとき、
|
|
294
|
+
|
|
295
|
+
```
|
|
296
|
+
|
|
297
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
298
|
+
|
|
299
|
+
1 SIMPLE dtb_bill index PRIMARY,customer_id PRIMARY 8 NULL 20 37425.00 Using where
|
|
300
|
+
|
|
301
|
+
1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 9 perf_test.dtb_bill.id 1 100.00 NULL
|
|
302
|
+
|
|
303
|
+
2 MATERIALIZED dtb_----- ALL NULL NULL NULL NULL 7434 100.00 Using where
|
|
304
|
+
|
|
305
|
+
```
|
|
306
|
+
|
|
307
|
+
でした。
|
|
308
|
+
|
|
309
|
+
|
|
310
|
+
|
|
311
|
+
「カンマ区切りのデータを複数 IN 句に与える」ことで劣化させられるのですが、果たしてその原因は…分かる方いらっしゃいますでしょうか。
|
|
312
|
+
|
|
313
|
+
|
|
314
|
+
|
|
217
315
|
よろしくお願いいたします。
|
3
詳細な状況を追記
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -82,6 +82,10 @@
|
|
|
82
82
|
|
|
83
83
|
# 状況
|
|
84
84
|
|
|
85
|
+
5.5.46-1.10.amzn1 と 5.6.30-1.15.amzn1 の比較です。
|
|
86
|
+
|
|
87
|
+
|
|
88
|
+
|
|
85
89
|
示したクエリの発行で
|
|
86
90
|
|
|
87
91
|
* MySQL 5.5 time コマンドで 0.120 秒程度
|
|
@@ -106,6 +110,12 @@
|
|
|
106
110
|
|
|
107
111
|
|
|
108
112
|
|
|
113
|
+
※追記
|
|
114
|
+
|
|
115
|
+
クエリ自体が 160 KB なので、それが悪影響を起こしている可能性はあります。
|
|
116
|
+
|
|
117
|
+
|
|
118
|
+
|
|
109
119
|
# 備考
|
|
110
120
|
|
|
111
121
|
テーブルの DROP と CREATE をしても、状況は変化しません。
|
2
IN 句について
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -52,6 +52,8 @@
|
|
|
52
52
|
|
|
53
53
|
どうしてそんなものを…という質問については、すみません、今回はそこは無しでお願いします。
|
|
54
54
|
|
|
55
|
+
※追記: サブクエリはありません。IN の中には数値の列が与えられます。
|
|
56
|
+
|
|
55
57
|
|
|
56
58
|
|
|
57
59
|
# my.cnf
|
1
備考その2
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -188,4 +188,18 @@
|
|
|
188
188
|
|
|
189
189
|
|
|
190
190
|
|
|
191
|
+
## 備考その2
|
|
192
|
+
|
|
193
|
+
実は、実際のアプリケーション上では、他にも type, status 等のカラムがあり、WHERE 句に `(status = '3' AND type <> 3) AND (type = '1')` がついていました。どちらも KEY がついています。
|
|
194
|
+
|
|
195
|
+
5.1 では `Using intersect(status,type); Using where; Using filesort`
|
|
196
|
+
|
|
197
|
+
5.5 では `Using intersect(status,PRIMARY); Using where; Using filesort`
|
|
198
|
+
|
|
199
|
+
5.6 では `type` を見て、`Using where` でした。
|
|
200
|
+
|
|
201
|
+
こちらはこちらでまた面白いので、別途質問するかもしれません。
|
|
202
|
+
|
|
203
|
+
|
|
204
|
+
|
|
191
205
|
よろしくお願いいたします。
|