質問編集履歴
5
詳細分析
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -155,4 +155,38 @@
|
|
|
155
155
|
|
|
156
156
|
「カンマ区切りのデータを複数 IN 句に与える」ことで劣化させられるのですが、果たしてその原因は…分かる方いらっしゃいますでしょうか。
|
|
157
157
|
|
|
158
|
+
## 追記 USE INDEX 付加
|
|
159
|
+
|
|
160
|
+
`USE INDEX (PRIMARY)` を付加したところ、0.022 秒程度のクエリとなりました。実行計画は以下です。
|
|
161
|
+
|
|
162
|
+
```
|
|
163
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
164
|
+
1 SIMPLE dtb_bill range PRIMARY PRIMARY 8 NULL 2100 100.00 Using where
|
|
165
|
+
```
|
|
166
|
+
|
|
167
|
+
## 追記 SHOW PROFILE 結果
|
|
168
|
+
|
|
169
|
+
右が、USE INDEX 付加後。statistics が大きく異なる。
|
|
170
|
+
|
|
171
|
+
```
|
|
172
|
+
Status Duration
|
|
173
|
+
starting 0.005219 0.005266
|
|
174
|
+
checking permissions 0.000007 0.000005
|
|
175
|
+
Opening tables 0.000023 0.000017
|
|
176
|
+
init 0.003204 0.002664
|
|
177
|
+
System lock 0.000010 0.000007
|
|
178
|
+
optimizing 0.001004 0.000933
|
|
179
|
+
statistics 1.818584 0.002057
|
|
180
|
+
preparing 0.001847 0.001844
|
|
181
|
+
Sorting result 0.000004 0.000004
|
|
182
|
+
executing 0.000002 0.000002
|
|
183
|
+
Sending data 0.000102 0.000078
|
|
184
|
+
end 0.000004 0.000003
|
|
185
|
+
query end 0.000005 0.000004
|
|
186
|
+
closing tables 0.000009 0.000006
|
|
187
|
+
freeing items 0.000598 0.000533
|
|
188
|
+
cleaning up 0.000008 0.000008
|
|
189
|
+
|
|
190
|
+
```
|
|
191
|
+
|
|
158
192
|
よろしくお願いいたします。
|
4
調べて追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,5 +1,7 @@
|
|
|
1
1
|
どうにも分からないので困っています。AWS EC2 上の MySQL を 5.1 から 5.6 に移行しました。特定の状況下でパフォーマンスの劣化が見られたので、原因の追究を手伝って下さる方を探しています。MySQL 5.5 までは問題がないように思います。nano インスタンスですが、メモリ不足ではないと思いますし、steal もない状況で比較しています。
|
|
2
2
|
|
|
3
|
+
**※最下部に現状を追記しました。**
|
|
4
|
+
|
|
3
5
|
# テーブル
|
|
4
6
|
```SQL
|
|
5
7
|
CREATE TABLE `dtb_bill` (
|
|
@@ -17,15 +19,16 @@
|
|
|
17
19
|
|
|
18
20
|
```SQL
|
|
19
21
|
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE
|
|
20
|
-
(customer_id IN (...)) --
|
|
22
|
+
(customer_id IN (...)) -- 約 2000 通り
|
|
21
23
|
AND
|
|
22
|
-
(id IN (...)) --
|
|
24
|
+
(id IN (...)) -- 約 2200 通り
|
|
23
25
|
ORDER BY `id` desc LIMIT 20;
|
|
24
26
|
```
|
|
25
27
|
|
|
26
28
|
というクエリを発行しています。IN 句の中に意味のない数字は大量にあります。
|
|
27
29
|
どうしてそんなものを…という質問については、すみません、今回はそこは無しでお願いします。
|
|
28
30
|
※追記: サブクエリはありません。IN の中には数値の列が与えられます。
|
|
31
|
+
※追記: 数が逆でした。
|
|
29
32
|
|
|
30
33
|
# my.cnf
|
|
31
34
|
関連しそうな my.cnf の設定は
|
|
@@ -106,4 +109,50 @@
|
|
|
106
109
|
5.6 では `type` を見て、`Using where` でした。
|
|
107
110
|
こちらはこちらでまた面白いので、別途質問するかもしれません。
|
|
108
111
|
|
|
112
|
+
## 追記
|
|
113
|
+
アプリケーション側のコードを追って、SQL を突き止めました。テーブルの構成は、もともとの構成に戻してはいますが、概要は変わりません。
|
|
114
|
+
|
|
115
|
+
```SQL
|
|
116
|
+
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE
|
|
117
|
+
(customer_id IN (SELECT文 A))
|
|
118
|
+
AND
|
|
119
|
+
(id IN (SELECT文 B))
|
|
120
|
+
ORDER BY `id` desc LIMIT 20;
|
|
121
|
+
```
|
|
122
|
+
が本来行いたい処理であるようです。
|
|
123
|
+
|
|
124
|
+
これを
|
|
125
|
+
```SQL
|
|
126
|
+
SELECT `dtb_bill`.* FROM `dtb_bill` WHERE
|
|
127
|
+
(customer_id IN (SELECT文 A の「結果カンマ区切り」))
|
|
128
|
+
AND
|
|
129
|
+
(id IN (SELECT文 B の「結果カンマ区切り」))
|
|
130
|
+
ORDER BY `id` desc LIMIT 20;
|
|
131
|
+
```
|
|
132
|
+
で処理させていて(一度途中経過を取っているようです)、そうすると、性能劣化が生じます。
|
|
133
|
+
|
|
134
|
+
両方 SELECT 文なら、0.009 秒、片方 SELECT 文の時、A が SELECT のとき 0.009 秒、B が SELECT のとき 0.035 秒でした。両方カンマ区切りにすると、約 1.9 秒で、これだけやけに遅いです。
|
|
135
|
+
|
|
136
|
+
クエリの実行計画は、両方カンマ区切りが
|
|
137
|
+
```
|
|
138
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
139
|
+
1 SIMPLE dtb_bill range PRIMARY,customer_id PRIMARY 8 NULL 2100 100.00 Using where
|
|
140
|
+
```
|
|
141
|
+
で A が SELECT 文のとき
|
|
142
|
+
```
|
|
143
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
144
|
+
1 SIMPLE dtb_bill range PRIMARY,customer_id PRIMARY 8 NULL 2100 100.00 Using where
|
|
145
|
+
1 SIMPLE dtb_customer eq_ref PRIMARY,----------_by PRIMARY 8 perf_test.dtb_bill.customer_id 1 100.00 Using where
|
|
146
|
+
```
|
|
147
|
+
で B が SELECT 文のとき、
|
|
148
|
+
```
|
|
149
|
+
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
150
|
+
1 SIMPLE dtb_bill index PRIMARY,customer_id PRIMARY 8 NULL 20 37425.00 Using where
|
|
151
|
+
1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 9 perf_test.dtb_bill.id 1 100.00 NULL
|
|
152
|
+
2 MATERIALIZED dtb_----- ALL NULL NULL NULL NULL 7434 100.00 Using where
|
|
153
|
+
```
|
|
154
|
+
でした。
|
|
155
|
+
|
|
156
|
+
「カンマ区切りのデータを複数 IN 句に与える」ことで劣化させられるのですが、果たしてその原因は…分かる方いらっしゃいますでしょうか。
|
|
157
|
+
|
|
109
158
|
よろしくお願いいたします。
|
3
詳細な状況を追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -40,6 +40,8 @@
|
|
|
40
40
|
こんなところでしょうか。SWAP 発生はありません。
|
|
41
41
|
|
|
42
42
|
# 状況
|
|
43
|
+
5.5.46-1.10.amzn1 と 5.6.30-1.15.amzn1 の比較です。
|
|
44
|
+
|
|
43
45
|
示したクエリの発行で
|
|
44
46
|
* MySQL 5.5 time コマンドで 0.120 秒程度
|
|
45
47
|
* MySQL 5.6 time コマンドで 1.923 秒程度
|
|
@@ -52,6 +54,9 @@
|
|
|
52
54
|
|
|
53
55
|
クエリキャッシュは有効で、2回目以降は早いです。1回目の実行を高速化したいです。目標は、MySQL 5.6 で 1秒を切るところです。
|
|
54
56
|
|
|
57
|
+
※追記
|
|
58
|
+
クエリ自体が 160 KB なので、それが悪影響を起こしている可能性はあります。
|
|
59
|
+
|
|
55
60
|
# 備考
|
|
56
61
|
テーブルの DROP と CREATE をしても、状況は変化しません。
|
|
57
62
|
|
2
IN 句について
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -25,6 +25,7 @@
|
|
|
25
25
|
|
|
26
26
|
というクエリを発行しています。IN 句の中に意味のない数字は大量にあります。
|
|
27
27
|
どうしてそんなものを…という質問については、すみません、今回はそこは無しでお願いします。
|
|
28
|
+
※追記: サブクエリはありません。IN の中には数値の列が与えられます。
|
|
28
29
|
|
|
29
30
|
# my.cnf
|
|
30
31
|
関連しそうな my.cnf の設定は
|
1
備考その2
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -93,4 +93,11 @@
|
|
|
93
93
|
```
|
|
94
94
|
直後に速度は元に戻ります。何かのバッファメモリが足りないのでしょうか？
|
|
95
95
|
|
|
96
|
+
## 備考その2
|
|
97
|
+
実は、実際のアプリケーション上では、他にも type, status 等のカラムがあり、WHERE 句に `(status = '3' AND type <> 3) AND (type = '1')` がついていました。どちらも KEY がついています。
|
|
98
|
+
5.1 では `Using intersect(status,type); Using where; Using filesort`
|
|
99
|
+
5.5 では `Using intersect(status,PRIMARY); Using where; Using filesort`
|
|
100
|
+
5.6 では `type` を見て、`Using where` でした。
|
|
101
|
+
こちらはこちらでまた面白いので、別途質問するかもしれません。
|
|
102
|
+
|
|
96
103
|
よろしくお願いいたします。
|