teratail
回答率
85.47%
質問するログイン新規登録
トップMySQLに関する質問MySQLのSELECT時間を早くするために、カラム毎に複数回に分けてSELECTする事はありますか？

編集履歴

質問編集履歴

7

追加のテスト結果を掲載しました

2023/06/10 15:48

投稿

ID_7UGzV8hCHGs5
ID_7UGzV8hCHGs5

スコア58

test CHANGED
File without changes
test CHANGED
@@ -232,5 +232,92 @@
232
232
  Windows10、メモリ32GB(16GB*2)、Corei7-8700、SSD
233
233
 
234
234
 
235
+
236
+ # SQL_NO_CACHE をつけて、ORDER BY のカラムを変更してテストした結果 追記(2023/6/11 00:42)
237
+
238
+ 結果、「1カラムずつSELECTしても、3カラムまとめてSELECTしても、トータル時間に大差はない」事が分かりました。
239
+
240
+ 備考：３つのカラムそれぞれに、INDEXキーを指定してあります。
241
+ ![イメージ説明](https://ddjkaamml8q8x.cloudfront.net/questions/2023-06-11/079ca9a4-467d-45b4-ba3d-8d178f156c4f.png)
242
+
243
+ 以下、実行したSQLと、実行時間です。
244
+
245
+ ```php
246
+ /******************************************************************************************
247
+ ORDER BY に `id`を指定
248
+ /******************************************************************************************/
249
+
250
+ /* id カラム のみSELECT*/
251
+ SELECT SQL_NO_CACHE `id`
252
+ FROM `test_subdivided_question` ORDER BY `id` LIMIT 10000
253
+ 時間: 904 msec
254
+
255
+ /* question_no カラム のみSELECT*/
256
+ SELECT SQL_NO_CACHE `question_no`
257
+ FROM `test_subdivided_question` ORDER BY `id` LIMIT 10000
258
+ 時間: 903 msec
259
+
260
+ /* is_trash カラム のみSELECT*/
261
+ SELECT SQL_NO_CACHE `is_trash`
262
+ FROM `test_subdivided_question` ORDER BY `id` LIMIT 10000
263
+ 時間: 903 msec
264
+
265
+ /* 3つのカラムを同時にSELECT */
266
+ SELECT SQL_NO_CACHE `id`, `question_no`, `is_trash`
267
+ FROM `test_subdivided_question`ORDER BY `id` LIMIT 10000
268
+ 時間: 907 msec
269
+
270
+
271
+
272
+ /******************************************************************************************
273
+ ORDER BY に `is_trash`を指定
274
+ /******************************************************************************************/
275
+
276
+ /* id カラム のみSELECT*/
277
+ SELECT SQL_NO_CACHE `id`
278
+ FROM `test_subdivided_question` ORDER BY `is_trash` LIMIT 10000
279
+ 時間: 5 msec
280
+
281
+ /* question_no カラム のみSELECT*/
282
+ SELECT SQL_NO_CACHE `question_no`
283
+ FROM `test_subdivided_question` ORDER BY `is_trash` LIMIT 10000
284
+ 時間: 1950 msec
285
+
286
+ /* is_trash カラム のみSELECT*/
287
+ SELECT SQL_NO_CACHE `is_trash`
288
+ FROM `test_subdivided_question` ORDER BY `is_trash` LIMIT 10000
289
+ 時間: 5 msec
290
+
291
+ /* 3つのカラムを同時にSELECT */
292
+ SELECT SQL_NO_CACHE `id`, `question_no`, `is_trash`
293
+ FROM `test_subdivided_question`ORDER BY `is_trash` LIMIT 10000
294
+ 時間: 1956 msec
295
+
296
+
297
+
298
+ /******************************************************************************************
299
+ ORDER BY に `question_no`を指定
300
+ /******************************************************************************************
301
+
302
+ /* id カラム のみSELECT*/
303
+ SELECT SQL_NO_CACHE `id`
304
+ FROM `test_subdivided_question` ORDER BY `question_no` LIMIT 10000
305
+ 時間: 6 msec
306
+
307
+ /* question_no カラム のみSELECT*/
308
+ SELECT SQL_NO_CACHE `question_no`
309
+ FROM `test_subdivided_question` ORDER BY `question_no` LIMIT 10000
310
+ 時間: 6 msec
311
+
312
+ /* is_trash カラム のみSELECT*/
313
+ SELECT SQL_NO_CACHE `is_trash`
314
+ FROM `test_subdivided_question` ORDER BY `question_no` LIMIT 10000
315
+ 時間: 2163 msec
316
+
317
+ /* 3つのカラムを同時にSELECT */
318
+ SELECT SQL_NO_CACHE `id`, `question_no`, `is_trash`
319
+ FROM `test_subdivided_question`ORDER BY `question_no` LIMIT 10000
320
+ 時間: 2159 msec
235
- ```
321
+ ```
236
-
322
+
323
+
MySQL

6

コードを１つにまとめました。

2023/06/10 06:09

投稿

ID_7UGzV8hCHGs5
ID_7UGzV8hCHGs5

スコア58

test CHANGED
File without changes
test CHANGED
@@ -37,139 +37,30 @@
37
37
 
38
38
 
39
39
  # 実際のコードと、実行時間
40
+ 改変(2023/6/10 15:06)
41
+ 使用しているclassなど、必要な物を1つのphpファイルに全てまとめて実行しました
42
+
40
43
  ```php
41
- /***************************************************************************************************************
42
- * DB接続オブジェクト作成
43
- /***************************************************************************************************************/
44
- $DB = new \my_namespace\my_database_class(DB_ホストネーム, 'my_test', DB_ユーザーネーム, DB_パスワード);
45
-
46
-
47
-
48
- /******************************************************************************************
49
- /* 1つのカラムを取得 (列id) */
50
- /******************************************************************************************/
51
- $ストップウォッチ = new ストップウォッチ();
52
- $ストップウォッチ->start();
53
-
54
- $sql = 'SELECT `id`'
55
- .'FROM `test_subdivided_question` '
56
- .'LIMIT 10000 '
57
- .'';
58
- $返り値_配列_id = $DB->query($sql);
59
-
60
- $ストップウォッチ->end(); //時間: 5 msec
61
-
62
-
63
-
64
- /******************************************************************************************
65
- /* 1つのカラムを取得 (列question_no) */
66
- /******************************************************************************************/
67
- $ストップウォッチ = new ストップウォッチ();
68
- $ストップウォッチ->start();
69
- $sql = 'SELECT `question_no` '
70
- .'FROM `test_subdivided_question` '
71
- .'LIMIT 10000 '
72
- .'';
73
- $返り値_配列_question_no = $DB->query($sql);
74
- $ストップウォッチ->end(); //時間: 5 msec
75
-
76
-
77
-
78
- /******************************************************************************************
79
- /* 1つのカラムを取得 (列is_trash) */
80
- /******************************************************************************************/
81
- $ストップウォッチ = new ストップウォッチ();
82
- $ストップウォッチ->start();
83
- $sql = 'SELECT `is_trash`'
84
- .'FROM `test_subdivided_question` '
85
- .'LIMIT 10000 '
86
- .'';
87
- $返り値_配列_is_trash = $DB->query($sql);
88
- $ストップウォッチ->end(); //時間: 5 msec
89
-
90
-
91
-
92
- /******************************************************************************************
93
- /* 3つのカラムを1つの配列にまとめる */
94
- /******************************************************************************************/
95
- $ストップウォッチ = new ストップウォッチ();
96
- $ストップウォッチ->start();
97
-
98
- $返り値_配列 = [];
99
- for($i=0; $i<count($返り値_配列_id); $i++){
100
- $返り値_配列[$i] = array(
101
- 'id' =>$返り値_配列_id[$i]['id'],
102
- 'question_no' =>$返り値_配列_question_no[$i]['question_no'],
103
- 'is_trash' =>$返り値_配列_is_trash[$i]['is_trash']
104
- );
105
- }
106
-
107
- $ストップウォッチ->end(); //時間: 1 msec
108
-
109
-
110
-
111
-
112
-
113
- /******************************************************************************************
114
- /* 3つのカラムを同時にSELECT */
115
- /******************************************************************************************/
116
- $ストップウォッチ = new ストップウォッチ();
117
- $ストップウォッチ->start();
118
- $sql = 'SELECT `id`, `question_no`, `is_trash`'
119
- // $sql = 'SELECT `id`, `question_no` '
120
- .'FROM `test_subdivided_question`'
121
- .'LIMIT 10000 '
122
- .'';
123
- $返り値_配列 = $DB->query($sql);
124
- $ストップウォッチ->end(); //時間: 891 msec
125
-
126
-
127
- ```
128
-
129
-
130
- # 環境
131
-
132
- ## PHP
133
- ```
134
- C:\xampp\htdocs>php -v
135
- PHP 8.2.0 (cli) (built: Dec 6 2022 15:31:23) (ZTS Visual C++ 2019 x64)
136
- Copyright (c) The PHP Group
137
- Zend Engine v4.2.0, Copyright (c) Zend Technologies
138
-
139
- C:\xampp\htdocs>php -i | findstr memory_limit
140
- memory_limit => 8192M => 8192M
141
- ```
142
-
143
- ## PC
144
- Windows10、メモリ32GB(16GB*2)、Corei7-8700、SSD
145
-
146
-
147
- # 時間計測に使用した ストップウォッチclass
148
- ```php
44
+ <?php
45
+
149
46
  class ストップウォッチ
150
47
  {
151
- private $startTime;
48
+ private $startTime;
152
-
49
+
153
- public function start()
50
+ public function start()
154
- {
51
+ {
155
- $this->startTime = microtime(true);
52
+ $this->startTime = microtime(true);
156
- }
53
+ }
157
-
54
+
158
- public function end()
55
+ public function end()
159
- {
56
+ {
160
-
57
+
161
- $時間 = microtime(true) - $this->startTime;
58
+ $時間 = microtime(true) - $this->startTime;
162
- print("時間: " . floor(($時間 * 1000)). " msec". PHP_EOL);
59
+ print("時間: " . floor(($時間 * 1000)). " msec". PHP_EOL);
163
-
60
+
164
- }
61
+ }
165
62
 
166
63
  }
167
- ```
168
-
169
-
170
- 以下追記(2023/6/10 14:58)
171
- # 利用したDB接続クラス
172
- ```php
173
64
 
174
65
  class my_database_class {
175
66
  private $conn;
@@ -184,7 +75,6 @@
184
75
  $this->conn = new PDO($dsn, $username, $password, $options);
185
76
  }
186
77
 
187
-
188
78
  public function query($引数_query){
189
79
  /*引数の生のsql文を実行するだけ*/
190
80
 
@@ -198,6 +88,18 @@
198
88
 
199
89
  // 変更を確定する
200
90
  $this->conn->commit();
91
+
92
+ /*凄い力技で、返り値を決める
93
+ 引数_query に SELECT が入っているならば、下記を実行する
94
+ */
95
+ if(strpos($引数_query, "SELECT") !== false){
96
+ return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
97
+ }
98
+
99
+ /*引数_query に INSERT、DELETE、UPDATEが入っているならば、処理した行数を返す*/
100
+ /*rowCountメソッドは、直近のINSERT、UPDATE、DELETE文で影響を受けた行数を返します。*/
101
+ return $this->stmt->rowCount();
102
+
201
103
 
202
104
  } catch (Exception $e) {
203
105
  // エラーが発生した場合、ロールバックする
@@ -209,9 +111,126 @@
209
111
  }
210
112
 
211
113
  }
212
-
213
- /* 以下省略 */
214
114
  }
215
115
 
116
+
117
+
118
+ /***************************************************************************************************************
119
+ * DB接続オブジェクト作成
120
+ /***************************************************************************************************************/
121
+ $DB = new my_database_class('localhost', 'my_test', 'root', 'root');
122
+
123
+
124
+
125
+ /******************************************************************************************
126
+ /* 1つのカラムを取得 (列id) */
127
+ /******************************************************************************************/
128
+ $ストップウォッチ = new ストップウォッチ();
129
+ $ストップウォッチ->start();
130
+
131
+ $sql = 'SELECT `id`'
132
+ .'FROM `test_subdivided_question` '
133
+ .'LIMIT 10000 '
134
+ .'';
135
+ $返り値_配列_id = $DB->query($sql);
136
+
137
+ $ストップウォッチ->end(); //時間: 5 msec
138
+
139
+
140
+
141
+ /******************************************************************************************
142
+ /* 1つのカラムを取得 (列question_no) */
143
+ /******************************************************************************************/
144
+ $ストップウォッチ = new ストップウォッチ();
145
+ $ストップウォッチ->start();
146
+ $sql = 'SELECT `question_no` '
147
+ .'FROM `test_subdivided_question` '
148
+ .'LIMIT 10000 '
149
+ .'';
150
+ $返り値_配列_question_no = $DB->query($sql);
151
+ $ストップウォッチ->end(); //時間: 5 msec
152
+
153
+
154
+
155
+ /******************************************************************************************
156
+ /* 1つのカラムを取得 (列is_trash) */
157
+ /******************************************************************************************/
158
+ $ストップウォッチ = new ストップウォッチ();
159
+ $ストップウォッチ->start();
160
+ $sql = 'SELECT `is_trash`'
161
+ .'FROM `test_subdivided_question` '
162
+ .'LIMIT 10000 '
163
+ .'';
164
+ $返り値_配列_is_trash = $DB->query($sql);
165
+ $ストップウォッチ->end(); //時間: 5 msec
166
+
167
+
168
+
169
+ /******************************************************************************************
170
+ /* 3つのカラムを1つの配列にまとめる */
171
+ /******************************************************************************************/
172
+ $ストップウォッチ = new ストップウォッチ();
173
+ $ストップウォッチ->start();
174
+
175
+ $返り値_配列 = [];
176
+ for($i=0; $i<count($返り値_配列_id); $i++){
177
+ $返り値_配列[$i] = array(
178
+ 'id' =>$返り値_配列_id[$i]['id'],
179
+ 'question_no' =>$返り値_配列_question_no[$i]['question_no'],
180
+ 'is_trash' =>$返り値_配列_is_trash[$i]['is_trash']
181
+ );
182
+ }
183
+
184
+ $ストップウォッチ->end(); //時間: 1 msec
185
+
186
+
187
+
188
+
189
+
190
+ /******************************************************************************************
191
+ /* 3つのカラムを同時にSELECT */
192
+ /******************************************************************************************/
193
+ $ストップウォッチ = new ストップウォッチ();
194
+ $ストップウォッチ->start();
195
+ $sql = 'SELECT `id`, `question_no`, `is_trash`'
196
+ // $sql = 'SELECT `id`, `question_no` '
197
+ .'FROM `test_subdivided_question`'
198
+ .'LIMIT 10000 '
199
+ .'';
200
+ $返り値_配列 = $DB->query($sql);
201
+ $ストップウォッチ->end(); //時間: 891 msec
202
+
203
+
204
+
205
+ /* 結果(sublimetext3 のツール→ビルド で実行しております)
206
+ 時間: 5 msec
207
+ 時間: 5 msec
208
+ 時間: 5 msec
209
+ 時間: 1 msec
210
+ 時間: 892 msec
211
+ [Finished in 1.0s]
212
+ */
213
+
214
+
216
- ```
215
+ ```
216
+
217
+
217
-
218
+ # 環境
219
+
220
+ ## PHP
221
+ ```
222
+ C:\xampp\htdocs>php -v
223
+ PHP 8.2.0 (cli) (built: Dec 6 2022 15:31:23) (ZTS Visual C++ 2019 x64)
224
+ Copyright (c) The PHP Group
225
+ Zend Engine v4.2.0, Copyright (c) Zend Technologies
226
+
227
+ C:\xampp\htdocs>php -i | findstr memory_limit
228
+ memory_limit => 8192M => 8192M
229
+ ```
230
+
231
+ ## PC
232
+ Windows10、メモリ32GB(16GB*2)、Corei7-8700、SSD
233
+
234
+
235
+ ```
236
+
MySQL

5

DB接続クラスを追加しました

2023/06/10 05:59

投稿

ID_7UGzV8hCHGs5
ID_7UGzV8hCHGs5

スコア58

test CHANGED
File without changes
test CHANGED
@@ -165,3 +165,53 @@
165
165
 
166
166
  }
167
167
  ```
168
+
169
+
170
+ 以下追記(2023/6/10 14:58)
171
+ # 利用したDB接続クラス
172
+ ```php
173
+
174
+ class my_database_class {
175
+ private $conn;
176
+ private $stmt;
177
+
178
+ public function __construct($host, $dbname, $username, $password) {
179
+ $dsn = "mysql:host=$host;dbname=$dbname";
180
+ $options = [
181
+ PDO::ATTR_EMULATE_PREPARES => false,
182
+ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
183
+ ];
184
+ $this->conn = new PDO($dsn, $username, $password, $options);
185
+ }
186
+
187
+
188
+ public function query($引数_query){
189
+ /*引数の生のsql文を実行するだけ*/
190
+
191
+ $this->conn->beginTransaction();
192
+
193
+ try {
194
+ $this->stmt = $this->conn->prepare($引数_query);
195
+
196
+ /*execute()の結果は、insertだろうがselectだろうが、bool値が戻る。*/
197
+ $result = $this->stmt->execute();
198
+
199
+ // 変更を確定する
200
+ $this->conn->commit();
201
+
202
+ } catch (Exception $e) {
203
+ // エラーが発生した場合、ロールバックする
204
+ $this->conn->rollback();
205
+
206
+ echo "Error: " . $e->getMessage();
207
+
208
+ return false;
209
+ }
210
+
211
+ }
212
+
213
+ /* 以下省略 */
214
+ }
215
+
216
+ ```
217
+
MySQL

4

時間計測に使用したストップウォッチclassを追加しました。

2023/06/09 20:08

投稿

ID_7UGzV8hCHGs5
ID_7UGzV8hCHGs5

スコア58

test CHANGED
File without changes
test CHANGED
@@ -142,3 +142,26 @@
142
142
 
143
143
  ## PC
144
144
  Windows10、メモリ32GB(16GB*2)、Corei7-8700、SSD
145
+
146
+
147
+ # 時間計測に使用した ストップウォッチclass
148
+ ```php
149
+ class ストップウォッチ
150
+ {
151
+ private $startTime;
152
+
153
+ public function start()
154
+ {
155
+ $this->startTime = microtime(true);
156
+ }
157
+
158
+ public function end()
159
+ {
160
+
161
+ $時間 = microtime(true) - $this->startTime;
162
+ print("時間: " . floor(($時間 * 1000)). " msec". PHP_EOL);
163
+
164
+ }
165
+
166
+ }
167
+ ```
MySQL

3

コードの実行順序を変更したコードに差し替えました(MySQL側にキャッシュ的なものが残っており、それで時間が早くなっている可能性を考慮して、実行順序を変更しました。しかし結果は変わりませんでした。

2023/06/09 20:06

投稿

ID_7UGzV8hCHGs5
ID_7UGzV8hCHGs5

スコア58

test CHANGED
File without changes
test CHANGED
@@ -38,24 +38,10 @@
38
38
 
39
39
  # 実際のコードと、実行時間
40
40
  ```php
41
-
42
41
  /***************************************************************************************************************
43
42
  * DB接続オブジェクト作成
44
43
  /***************************************************************************************************************/
45
44
  $DB = new \my_namespace\my_database_class(DB_ホストネーム, 'my_test', DB_ユーザーネーム, DB_パスワード);
46
-
47
-
48
- /******************************************************************************************
49
- /* 3つのカラムを同時にSELECT */
50
- /******************************************************************************************/
51
- $ストップウォッチ = new ストップウォッチ();
52
- $ストップウォッチ->start();
53
- $sql = 'SELECT `id`, `question_no`, `is_trash`'
54
- .'FROM `my_table`'
55
- .'LIMIT 10000 '
56
- .'';
57
- $返り値_配列 = $DB->query($sql);
58
- $ストップウォッチ->end(); //時間: 891 msec
59
45
 
60
46
 
61
47
 
@@ -66,7 +52,7 @@
66
52
  $ストップウォッチ->start();
67
53
 
68
54
  $sql = 'SELECT `id`'
69
- .'FROM `my_table` '
55
+ .'FROM `test_subdivided_question` '
70
56
  .'LIMIT 10000 '
71
57
  .'';
72
58
  $返り値_配列_id = $DB->query($sql);
@@ -81,7 +67,7 @@
81
67
  $ストップウォッチ = new ストップウォッチ();
82
68
  $ストップウォッチ->start();
83
69
  $sql = 'SELECT `question_no` '
84
- .'FROM `my_table` '
70
+ .'FROM `test_subdivided_question` '
85
71
  .'LIMIT 10000 '
86
72
  .'';
87
73
  $返り値_配列_question_no = $DB->query($sql);
@@ -95,7 +81,7 @@
95
81
  $ストップウォッチ = new ストップウォッチ();
96
82
  $ストップウォッチ->start();
97
83
  $sql = 'SELECT `is_trash`'
98
- .'FROM `my_table` '
84
+ .'FROM `test_subdivided_question` '
99
85
  .'LIMIT 10000 '
100
86
  .'';
101
87
  $返り値_配列_is_trash = $DB->query($sql);
@@ -120,6 +106,24 @@
120
106
 
121
107
  $ストップウォッチ->end(); //時間: 1 msec
122
108
 
109
+
110
+
111
+
112
+
113
+ /******************************************************************************************
114
+ /* 3つのカラムを同時にSELECT */
115
+ /******************************************************************************************/
116
+ $ストップウォッチ = new ストップウォッチ();
117
+ $ストップウォッチ->start();
118
+ $sql = 'SELECT `id`, `question_no`, `is_trash`'
119
+ // $sql = 'SELECT `id`, `question_no` '
120
+ .'FROM `test_subdivided_question`'
121
+ .'LIMIT 10000 '
122
+ .'';
123
+ $返り値_配列 = $DB->query($sql);
124
+ $ストップウォッチ->end(); //時間: 891 msec
125
+
126
+
123
127
  ```
124
128
 
125
129
 
MySQL

2

文法の修正

2023/06/09 20:03

投稿

ID_7UGzV8hCHGs5
ID_7UGzV8hCHGs5

スコア58

test CHANGED
File without changes
test CHANGED
@@ -136,5 +136,5 @@
136
136
  memory_limit => 8192M => 8192M
137
137
  ```
138
138
 
139
- ##PC
139
+ ## PC
140
140
  Windows10、メモリ32GB(16GB*2)、Corei7-8700、SSD
MySQL

1

誤った日本語の修正

2023/06/09 20:01

投稿

ID_7UGzV8hCHGs5
ID_7UGzV8hCHGs5

スコア58

test CHANGED
File without changes
test CHANGED
@@ -27,7 +27,7 @@
27
27
 
28
28
  補足:
29
29
  ※皆がアクセスするDBだと、整合性の問題でこんな変なコードを書くことは無いかと思いますが、
30
- 自宅PCのDBなので私しかしかDBにアクセスしません。1つのプログラムしか実行しない為、SELECT中に 他のSQLがUPDATEしたりすることもありません。
30
+ 自宅PCのDBなので私しかDBにアクセスしません。1つのプログラムしか実行しない為、SELECT中に 他のSQLがUPDATEしたりすることもありません。
31
31
  ※ちなみに、2カラムSELECTだと速度は速く(6msec)、3カラム以上のSELECTにした途端、遅く(891msec)なりました。
32
32
 
33
33
  ※カラムのデータ型は下記の通りで、文字列とか、サイズの大きい値は入っていません。
MySQL