teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問mysqlでSELECTが遅すぎる。（チューニング）

編集履歴

質問編集履歴

3

エラー修正

2020/03/16 07:33

投稿

hukutoro
hukutoro

スコア4

test CHANGED
File without changes
test CHANGED
@@ -58,7 +58,7 @@
58
58
 
59
59
  ```sql
60
60
 
61
- SELECT *,
61
+ SELECT spot.*, spot_review.score,
62
62
 
63
63
  (6371 * acos(
64
64
 
@@ -224,7 +224,7 @@
224
224
 
225
225
  FROM(
226
226
 
227
- SELECT *,
227
+ SELECT spot.*, spot_review.score,
228
228
 
229
229
  (6371 * acos(
230
230
 

2

取得件数指定

2020/03/16 07:33

投稿

hukutoro
hukutoro

スコア4

test CHANGED
File without changes
test CHANGED
@@ -80,7 +80,9 @@
80
80
 
81
81
  WHERE latitude IS NOT NULL AND longitude IS NOT NULL
82
82
 
83
- ORDER BY distance ASC, create_at DESC;
83
+ ORDER BY distance ASC, create_at DESC
84
+
85
+ LIMIT 0, 10;
84
86
 
85
87
  ```
86
88
 
@@ -246,7 +248,9 @@
246
248
 
247
249
  ) as tmp
248
250
 
249
- ORDER BY distance ASC, create_at DESC;
251
+ ORDER BY distance ASC, create_at DESC
252
+
253
+ LIMIT 0, 10;
250
254
 
251
255
  ```
252
256
 

1

サンプルファイル添付、タイポ修正

2020/03/16 07:23

投稿

hukutoro
hukutoro

スコア4

test CHANGED
File without changes
test CHANGED
@@ -18,35 +18,39 @@
18
18
 
19
19
  ```
20
20
 
21
- DESC spot;
21
+ CREATE TABLE `spot` (
22
-
23
- +-----------------+---------------+------+-----+---------+----------------+
22
+
24
-
25
- | Field | Type | Null | Key | Default | Extra |
26
-
27
- +-----------------+---------------+------+-----+---------+----------------+
28
-
29
- | SpotId | int(11) | NO | PRI | NULL | auto_increment |
23
+ `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'スポットID',
30
-
24
+
31
- | latitude | decimal(9,6) | YES | | NULL | |
25
+ `latitude` decimal(9,6) DEFAULT NULL COMMENT '緯度',
32
-
26
+
33
- | longitude | decimal(9,6) | YES | | NULL | |
27
+ `longitude` decimal(9,6) DEFAULT NULL COMMENT '経度',
34
-
35
- | UpdateDate | datetime | YES | | NULL | |
28
+
36
-
37
- | CreateDate | datetime | YES | | NULL | |
29
+ `create_at` datetime NOT NULL COMMENT '作成日',
30
+
38
-
31
+ PRIMARY KEY (`id`)
32
+
33
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
34
+
35
+
36
+
37
+ CREATE TABLE `spot_review` (
38
+
39
- | UpdateBy | varchar(20) | YES | | NULL | |
39
+ `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'レビューID',
40
-
40
+
41
- | CreateBy | varchar(20) | YES | | NULL | |
41
+ `spot_id` int(11) NOT NULL COMMENT 'スポットID',
42
-
42
+
43
- | DeleteFlg | varchar(1) | YES | | 0 | |
43
+ `score` int(11) NOT NULL COMMENT '点数',
44
+
44
-
45
+ PRIMARY KEY (`id`)
46
+
45
- +-----------------+---------------+------+-----+---------+----------------+
47
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
46
-
48
+
47
- ```
49
+ ```
48
-
50
+
49
- Engine: InnoDB
51
+ [サンプルファイル](https://xfs.jp/udQ9GZ) ※teratailでのファイル添付方法がわからず外部ストレージです
52
+
53
+
50
54
 
51
55
 
52
56
 
@@ -54,8 +58,172 @@
54
58
 
55
59
  ```sql
56
60
 
61
+ SELECT *,
62
+
63
+ (6371 * acos(
64
+
65
+ cos(radians('35.439440'))
66
+
67
+ * cos(radians(latitude))
68
+
69
+ * cos(radians(longitude) - radians('139.634000'))
70
+
71
+ + sin(radians('35.439440'))
72
+
73
+ * sin(radians(latitude))
74
+
75
+ )) AS distance
76
+
77
+ FROM spot
78
+
79
+ LEFT JOIN spot_review ON spot.id = spot_review.spot_id
80
+
81
+ WHERE latitude IS NOT NULL AND longitude IS NOT NULL
82
+
83
+ ORDER BY distance ASC, create_at DESC;
84
+
85
+ ```
86
+
87
+ ※実際にはもっと複雑なためテーブル結合など一部省略し、単純化しています。
88
+
89
+ 登録数は15000件ほど
90
+
91
+ ### 発生している問題・エラーメッセージ
92
+
93
+ 取得が遅い、DBサーバが停止してしまう(too many connections)
94
+
95
+
96
+
97
+ ##### ステージング環境のログ
98
+
99
+ ```sql
100
+
101
+ show processlist;
102
+
103
+ +---------+-------+------------------------------+------------------------------------------+
104
+
105
+ | Command | Time | State | Info |
106
+
107
+ +---------+-------+------------------------------+------------------------------------------+
108
+
109
+ | Execute | 2068 | Copying to tmp table on disk | SELECT * (6371 * acos( |
110
+
111
+ | Execute | 2067 | Copying to tmp table on disk | SELECT * (6371 * acos( |
112
+
113
+ | Execute | 2066 | Copying to tmp table on disk | SELECT * (6371 * acos( |
114
+
115
+ | Execute | 2066 | Copying to tmp table on disk | SELECT * (6371 * acos( |
116
+
117
+ | Execute | 2065 | Copying to tmp table on disk | SELECT * (6371 * acos( |
118
+
119
+ | Execute | 2064 | Copying to tmp table on disk | SELECT * (6371 * acos( |
120
+
121
+ | Execute | 2064 | Copying to tmp table on disk | SELECT * (6371 * acos( |
122
+
123
+ | Execute | 2063 | Copying to tmp table on disk | SELECT * (6371 * acos( |
124
+
125
+ | Execute | 2062 | Copying to tmp table on disk | SELECT * (6371 * acos( |
126
+
127
+ | Execute | 2061 | Copying to tmp table on disk | SELECT * (6371 * acos( |
128
+
129
+ | Execute | 2060 | Copying to tmp table on disk | SELECT * (6371 * acos( |
130
+
131
+ | Execute | 2060 | Copying to tmp table on disk | SELECT * (6371 * acos( |
132
+
133
+ | Execute | 2059 | Copying to tmp table on disk | SELECT * (6371 * acos( |
134
+
135
+ +---------+-------+------------------------------+------------------------------------------+
136
+
137
+ ```
138
+
139
+
140
+
141
+ ##### ローカル環境のログ
142
+
143
+ 負荷実験のためcurlで同時に100アクセスした場合
144
+
145
+ ※MySQL5.6（急ぎ環境が準備できなかったため）
146
+
147
+ ※「Copying to tmp table on disk」は再現できず
148
+
149
+ ※ログは一部抜粋
150
+
151
+ ```sql
152
+
153
+ show processlist;
154
+
155
+ +---------+-------+------------------------+--------------------------+
156
+
157
+ | Command | Time | State | Info |
158
+
159
+ +---------+-------+------------------------+--------------------------+
160
+
161
+ | Execute | 48 | Sending data | SELECT * (6371 * acos( |
162
+
163
+ | Execute | 47 | Sending data | SELECT * (6371 * acos( |
164
+
165
+ | Execute | 46 | Sending data | SELECT * (6371 * acos( |
166
+
167
+ | Execute | 46 | Creating sort index | SELECT * (6371 * acos( |
168
+
169
+ | Execute | 45 | Sending data | SELECT * (6371 * acos( |
170
+
171
+ | Execute | 44 | Sending data | SELECT * (6371 * acos( |
172
+
173
+ | Execute | 44 | Sending data | SELECT * (6371 * acos( |
174
+
175
+ | Execute | 43 | Sending data | SELECT * (6371 * acos( |
176
+
177
+ | Execute | 42 | Sending data | SELECT * (6371 * acos( |
178
+
179
+ | Execute | 41 | Creating sort index | SELECT * (6371 * acos( |
180
+
181
+ | Execute | 40 | Sending data | SELECT * (6371 * acos( |
182
+
183
+ | Execute | 40 | Sending data | SELECT * (6371 * acos( |
184
+
185
+ | Execute | 39 | Sending data | SELECT * (6371 * acos( |
186
+
187
+ +---------+-------+------------------------+--------------------------+
188
+
189
+ ```
190
+
191
+
192
+
193
+ ##### EXPLAIN
194
+
195
+ ```sql
196
+
197
+ +----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+
198
+
199
+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
200
+
201
+ +----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+
202
+
203
+ | 1 | SIMPLE | spot | ALL | IDX01_Spot | NULL | NULL | NULL | 17307 | Using where; Using temporary; Using filesort |
204
+
205
+ +----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+
206
+
207
+ ```
208
+
209
+
210
+
211
+ ### 試したこと
212
+
213
+ ローカル環境では起きないが本番ではログに「Copying to tmp table on disk」とあるので、
214
+
215
+ EXLAINの「Using temporary」を疑い、ORDER BY原因があることがわかりました。
216
+
217
+ 空間インデックスなどが使えないためサブクエリにして、メインクエリにORDER BYをつけたところ「Using temporary」はなくなり以前よりは早くなりました。
218
+
219
+ ```sql
220
+
57
221
  SELECT *
58
222
 
223
+ FROM(
224
+
225
+ SELECT *,
226
+
59
227
  (6371 * acos(
60
228
 
61
229
  cos(radians('35.439440'))
@@ -70,115 +238,15 @@
70
238
 
71
239
  )) AS distance
72
240
 
73
- FROM Spot
241
+ FROM spot
242
+
74
-
243
+ LEFT JOIN spot_review ON spot.id = spot_review.spot_id
244
+
75
- WHERE latitude IS NOT NULL AND longitude IS NOT NULL
245
+ WHERE latitude IS NOT NULL AND longitude IS NOT NULL
246
+
76
-
247
+ ) as tmp
248
+
77
- ORDER BY distance ASC, created CreateDate;
249
+ ORDER BY distance ASC, create_at DESC;
78
-
79
- ```
80
-
81
- ※実際にはもっと複雑なためテーブル結合など一部省略し、単純化しています。
82
-
83
- 登録数は15000件ほど
84
-
85
- ### 発生している問題・エラーメッセージ
86
-
87
- 取得が遅い、DBサーバが停止してしまう(too many connections)
88
-
89
-
90
-
91
- ##### ステージング環境のログ
92
-
93
- ```sql
94
-
95
- show processlist;
96
-
97
- +---------+-------+------------------------------+------------------------------------------+
98
-
99
- | Command | Time | State | Info |
100
-
101
- +---------+-------+------------------------------+------------------------------------------+
102
-
103
- | Execute | 2068 | Copying to tmp table on disk | SELECT * (6371 * acos( |
104
-
105
- | Execute | 2067 | Copying to tmp table on disk | SELECT * (6371 * acos( |
106
-
107
- | Execute | 2066 | Copying to tmp table on disk | SELECT * (6371 * acos( |
108
-
109
- | Execute | 2066 | Copying to tmp table on disk | SELECT * (6371 * acos( |
110
-
111
- | Execute | 2065 | Copying to tmp table on disk | SELECT * (6371 * acos( |
112
-
113
- | Execute | 2064 | Copying to tmp table on disk | SELECT * (6371 * acos( |
114
-
115
- | Execute | 2064 | Copying to tmp table on disk | SELECT * (6371 * acos( |
116
-
117
- | Execute | 2063 | Copying to tmp table on disk | SELECT * (6371 * acos( |
118
-
119
- | Execute | 2062 | Copying to tmp table on disk | SELECT * (6371 * acos( |
120
-
121
- | Execute | 2061 | Copying to tmp table on disk | SELECT * (6371 * acos( |
122
-
123
- | Execute | 2060 | Copying to tmp table on disk | SELECT * (6371 * acos( |
124
-
125
- | Execute | 2060 | Copying to tmp table on disk | SELECT * (6371 * acos( |
126
-
127
- | Execute | 2059 | Copying to tmp table on disk | SELECT * (6371 * acos( |
128
-
129
- +---------+-------+------------------------------+------------------------------------------+
130
-
131
- ```
132
-
133
-
134
-
135
- ##### ローカル環境のログ
136
-
137
- 負荷実験のためcurlで同時に100アクセスした場合
138
-
139
- ※MySQL5.6（急ぎ環境が準備できなかったため）
140
-
141
- ※「Copying to tmp table on disk」は再現できず
142
-
143
- ※ログは一部抜粋
144
-
145
- ```sql
146
-
147
- show processlist;
148
-
149
- +---------+-------+------------------------+--------------------------+
150
-
151
- | Command | Time | State | Info |
152
-
153
- +---------+-------+------------------------+--------------------------+
154
-
155
- | Execute | 48 | Sending data | SELECT * (6371 * acos( |
156
-
157
- | Execute | 47 | Sending data | SELECT * (6371 * acos( |
158
-
159
- | Execute | 46 | Sending data | SELECT * (6371 * acos( |
160
-
161
- | Execute | 46 | Creating sort index | SELECT * (6371 * acos( |
162
-
163
- | Execute | 45 | Sending data | SELECT * (6371 * acos( |
164
-
165
- | Execute | 44 | Sending data | SELECT * (6371 * acos( |
166
-
167
- | Execute | 44 | Sending data | SELECT * (6371 * acos( |
168
-
169
- | Execute | 43 | Sending data | SELECT * (6371 * acos( |
170
-
171
- | Execute | 42 | Sending data | SELECT * (6371 * acos( |
172
-
173
- | Execute | 41 | Creating sort index | SELECT * (6371 * acos( |
174
-
175
- | Execute | 40 | Sending data | SELECT * (6371 * acos( |
176
-
177
- | Execute | 40 | Sending data | SELECT * (6371 * acos( |
178
-
179
- | Execute | 39 | Sending data | SELECT * (6371 * acos( |
180
-
181
- +---------+-------+------------------------+--------------------------+
182
250
 
183
251
  ```
184
252
 
@@ -188,66 +256,6 @@
188
256
 
189
257
  ```sql
190
258
 
191
- +----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+
192
-
193
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
194
-
195
- +----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+
196
-
197
- | 1 | SIMPLE | spot | ALL | IDX01_Spot | NULL | NULL | NULL | 17307 | Using where; Using temporary; Using filesort |
198
-
199
- +----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+
200
-
201
- ```
202
-
203
-
204
-
205
- ### 試したこと
206
-
207
- ローカル環境では起きないが本番ではログに「Copying to tmp table on disk」とあるので、
208
-
209
- EXLAINの「Using temporary」を疑い、ORDER BY原因があることがわかりました。
210
-
211
- 空間インデックスなどが使えないためサブクエリにして、メインクエリにORDER BYをつけたところ「Using temporary」はなくなり以前よりは早くなりました。
212
-
213
- ```sql
214
-
215
- SELECT *
216
-
217
- FROM(
218
-
219
- SELECT *
220
-
221
- (6371 * acos(
222
-
223
- cos(radians('35.439440'))
224
-
225
- * cos(radians(latitude))
226
-
227
- * cos(radians(longitude) - radians('139.634000'))
228
-
229
- + sin(radians('35.439440'))
230
-
231
- * sin(radians(latitude))
232
-
233
- )) AS distance
234
-
235
- FROM Spot
236
-
237
- WHERE latitude IS NOT NULL AND longitude IS NOT NULL
238
-
239
- ) as tmp
240
-
241
- ORDER BY distance ASC, created CreateDate;
242
-
243
- ```
244
-
245
-
246
-
247
- ##### EXPLAIN
248
-
249
- ```sql
250
-
251
259
  +----+-------------+------------+--------+-----------------+---------+---------+---------------+-------+----------------+
252
260
 
253
261
  | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |