Q&A
ありがとうございます。
教えて頂いたオプションを是非試してみます。
取り急ぎお礼申し上げます。
気になる事
3カラムをSELECTする場合、このようにSELECTを書くと思いますが
sql
1SELECT `id`, `question_no`, `is_trash` FROM `my_table` LIMIT 10000
このように、3回SELECTして、結果をPHP上で1つの配列にまとめると、速度が速くなりました
sql
1SELECT `id` FROM `my_table` LIMIT 10000
sql
1SELECT `question_no` FROM `my_table` LIMIT 10000
sql
1SELECT `is_trash` FROM `my_table` LIMIT 10000
1回のSELECTで3カラム取得にかかった時間は、889msecでした。
カラム毎にSELECTして、結果をまとめるのにかかった時間は、合計16msecでした。
(1回のSELECT時間 5msec *3 + まとめる時間 1msec)
SELECT時間を短くするために、上記みたいなコードを書くことはありますか？
また、このようなコードで何か問題が生じる可能性はありますでしょうか。
補足:
※皆がアクセスするDBだと、整合性の問題でこんな変なコードを書くことは無いかと思いますが、
自宅PCのDBなので私しかDBにアクセスしません。1つのプログラムしか実行しない為、SELECT中に 他のSQLがUPDATEしたりすることもありません。
※ちなみに、2カラムSELECTだと速度は速く(6msec)、3カラム以上のSELECTにした途端、遅く(891msec)なりました。
※カラムのデータ型は下記の通りで、文字列とか、サイズの大きい値は入っていません。
id: int(11) (auto_increment)
question_no: int(11)
is_trash: tinyint(1)
実際のコードと、実行時間
改変(2023/6/10 15:06)
使用しているclassなど、必要な物を1つのphpファイルに全てまとめて実行しました
php
1<?php 2 3class ストップウォッチ 4{ 5 private $startTime; 6 7 public function start() 8 { 9 $this->startTime = microtime(true); 10 } 11 12 public function end() 13 { 14 15 $時間 = microtime(true) - $this->startTime; 16 print("時間: " . floor(($時間 * 1000)). " msec". PHP_EOL); 17 18 } 19 20} 21 22class my_database_class { 23 private $conn; 24 private $stmt; 25 26 public function __construct($host, $dbname, $username, $password) { 27 $dsn = "mysql:host=$host;dbname=$dbname"; 28 $options = [ 29 PDO::ATTR_EMULATE_PREPARES => false, 30 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION 31 ]; 32 $this->conn = new PDO($dsn, $username, $password, $options); 33 } 34 35 public function query($引数_query){ 36 /*引数の生のsql文を実行するだけ*/ 37 38 $this->conn->beginTransaction(); 39 40 try { 41 $this->stmt = $this->conn->prepare($引数_query); 42 43 /*execute()の結果は、insertだろうがselectだろうが、bool値が戻る。*/ 44 $result = $this->stmt->execute(); 45 46 // 変更を確定する 47 $this->conn->commit(); 48 49 /*凄い力技で、返り値を決める 50 引数_query に SELECT が入っているならば、下記を実行する 51 */ 52 if(strpos($引数_query, "SELECT") !== false){ 53 return $this->stmt->fetchAll(PDO::FETCH_ASSOC); 54 } 55 56 /*引数_query に INSERT、DELETE、UPDATEが入っているならば、処理した行数を返す*/ 57 /*rowCountメソッドは、直近のINSERT、UPDATE、DELETE文で影響を受けた行数を返します。*/ 58 return $this->stmt->rowCount(); 59 60 61 } catch (Exception $e) { 62 // エラーが発生した場合、ロールバックする 63 $this->conn->rollback(); 64 65 echo "Error: " . $e->getMessage(); 66 67 return false; 68 } 69 70 } 71} 72 73 74 75/*************************************************************************************************************** 76 * DB接続オブジェクト作成 77/***************************************************************************************************************/ 78$DB = new my_database_class('localhost', 'my_test', 'root', 'root'); 79 80 81 82/****************************************************************************************** 83/* 1つのカラムを取得 (列id) */ 84/******************************************************************************************/ 85$ストップウォッチ = new ストップウォッチ(); 86$ストップウォッチ->start(); 87 88 $sql = 'SELECT `id`' 89 .'FROM `test_subdivided_question` ' 90 .'LIMIT 10000 ' 91 .''; 92 $返り値_配列_id = $DB->query($sql); 93 94$ストップウォッチ->end(); //時間: 5 msec 95 96 97 98/****************************************************************************************** 99/* 1つのカラムを取得 (列question_no) */ 100/******************************************************************************************/ 101$ストップウォッチ = new ストップウォッチ(); 102$ストップウォッチ->start(); 103 $sql = 'SELECT `question_no` ' 104 .'FROM `test_subdivided_question` ' 105 .'LIMIT 10000 ' 106 .''; 107 $返り値_配列_question_no = $DB->query($sql); 108$ストップウォッチ->end(); //時間: 5 msec 109 110 111 112/****************************************************************************************** 113/* 1つのカラムを取得 (列is_trash) */ 114/******************************************************************************************/ 115$ストップウォッチ = new ストップウォッチ(); 116$ストップウォッチ->start(); 117 $sql = 'SELECT `is_trash`' 118 .'FROM `test_subdivided_question` ' 119 .'LIMIT 10000 ' 120 .''; 121 $返り値_配列_is_trash = $DB->query($sql); 122$ストップウォッチ->end(); //時間: 5 msec 123 124 125 126/****************************************************************************************** 127/* 3つのカラムを1つの配列にまとめる */ 128/******************************************************************************************/ 129$ストップウォッチ = new ストップウォッチ(); 130$ストップウォッチ->start(); 131 132$返り値_配列 = []; 133for($i=0; $i<count($返り値_配列_id); $i++){ 134 $返り値_配列[$i] = array( 135 'id' =>$返り値_配列_id[$i]['id'], 136 'question_no' =>$返り値_配列_question_no[$i]['question_no'], 137 'is_trash' =>$返り値_配列_is_trash[$i]['is_trash'] 138 ); 139} 140 141$ストップウォッチ->end(); //時間: 1 msec 142 143 144 145 146 147/****************************************************************************************** 148/* 3つのカラムを同時にSELECT */ 149/******************************************************************************************/ 150$ストップウォッチ = new ストップウォッチ(); 151$ストップウォッチ->start(); 152 $sql = 'SELECT `id`, `question_no`, `is_trash`' 153 // $sql = 'SELECT `id`, `question_no` ' 154 .'FROM `test_subdivided_question`' 155 .'LIMIT 10000 ' 156 .''; 157 $返り値_配列 = $DB->query($sql); 158$ストップウォッチ->end(); //時間: 891 msec 159 160 161 162/* 結果(sublimetext3 のツール→ビルド で実行しております) 163時間: 5 msec 164時間: 5 msec 165時間: 5 msec 166時間: 1 msec 167時間: 892 msec 168[Finished in 1.0s] 169*/ 170 171
環境
PHP
C:\xampp\htdocs>php -v PHP 8.2.0 (cli) (built: Dec 6 2022 15:31:23) (ZTS Visual C++ 2019 x64) Copyright (c) The PHP Group Zend Engine v4.2.0, Copyright (c) Zend Technologies C:\xampp\htdocs>php -i | findstr memory_limit memory_limit => 8192M => 8192M
PC
Windows10、メモリ32GB(16GB*2)、Corei7-8700、SSD
SQL_NO_CACHE をつけて、ORDER BY のカラムを変更してテストした結果 追記(2023/6/11 00:42)
結果、「1カラムずつSELECTしても、3カラムまとめてSELECTしても、トータル時間に大差はない」事が分かりました。
備考：３つのカラムそれぞれに、INDEXキーを指定してあります。
以下、実行したSQLと、実行時間です。
php
1/****************************************************************************************** 2ORDER BY に `id`を指定 3/******************************************************************************************/ 4 5/* id カラム のみSELECT*/ 6SELECT SQL_NO_CACHE `id` 7FROM `test_subdivided_question` ORDER BY `id` LIMIT 10000 8時間: 904 msec 9 10/* question_no カラム のみSELECT*/ 11SELECT SQL_NO_CACHE `question_no` 12FROM `test_subdivided_question` ORDER BY `id` LIMIT 10000 13時間: 903 msec 14 15/* is_trash カラム のみSELECT*/ 16SELECT SQL_NO_CACHE `is_trash` 17FROM `test_subdivided_question` ORDER BY `id` LIMIT 10000 18時間: 903 msec 19 20/* 3つのカラムを同時にSELECT */ 21SELECT SQL_NO_CACHE `id`, `question_no`, `is_trash` 22FROM `test_subdivided_question`ORDER BY `id` LIMIT 10000 23時間: 907 msec 24 25 26 27/****************************************************************************************** 28ORDER BY に `is_trash`を指定 29/******************************************************************************************/ 30 31/* id カラム のみSELECT*/ 32SELECT SQL_NO_CACHE `id` 33FROM `test_subdivided_question` ORDER BY `is_trash` LIMIT 10000 34時間: 5 msec 35 36/* question_no カラム のみSELECT*/ 37SELECT SQL_NO_CACHE `question_no` 38FROM `test_subdivided_question` ORDER BY `is_trash` LIMIT 10000 39時間: 1950 msec 40 41/* is_trash カラム のみSELECT*/ 42SELECT SQL_NO_CACHE `is_trash` 43FROM `test_subdivided_question` ORDER BY `is_trash` LIMIT 10000 44時間: 5 msec 45 46/* 3つのカラムを同時にSELECT */ 47SELECT SQL_NO_CACHE `id`, `question_no`, `is_trash` 48FROM `test_subdivided_question`ORDER BY `is_trash` LIMIT 10000 49時間: 1956 msec 50 51 52 53/****************************************************************************************** 54ORDER BY に `question_no`を指定 55/****************************************************************************************** 56 57/* id カラム のみSELECT*/ 58SELECT SQL_NO_CACHE `id` 59FROM `test_subdivided_question` ORDER BY `question_no` LIMIT 10000 60時間: 6 msec 61 62/* question_no カラム のみSELECT*/ 63SELECT SQL_NO_CACHE `question_no` 64FROM `test_subdivided_question` ORDER BY `question_no` LIMIT 10000 65時間: 6 msec 66 67/* is_trash カラム のみSELECT*/ 68SELECT SQL_NO_CACHE `is_trash` 69FROM `test_subdivided_question` ORDER BY `question_no` LIMIT 10000 70時間: 2163 msec 71 72/* 3つのカラムを同時にSELECT */ 73SELECT SQL_NO_CACHE `id`, `question_no`, `is_trash` 74FROM `test_subdivided_question`ORDER BY `question_no` LIMIT 10000 75時間: 2159 msec
回答2件
0
ベストアンサー
SELECT時間を短くするために、上記みたいなコードを書くことはありますか？
ないです。
このようなコードで何か問題が生じる可能性はありますでしょうか。
select 文に order by がないと順番の保証がありません。
3 つに分ける = php と MySQL の間で 3 回ラウンドトリップが発生する・・・ということで、かえって遅くなると思うのですが。
【追記】
普通に考えてそのような大差があるとは思えません。データベースでの select 文の実行にかかる時間以外の、アプリ側での接続にかかる時間の影響があるのではないですか？
例えば、接続プールを使っている場合、
(1) 一番最初にアプリが接続を open する時に接続プールと接続を作成します。当然それには時間がかかります。
(2) 最初の select 処理が終わって、アプリが接続を close すると (1) で作った接続はプールに戻されます。
(3) 次の select 処理のためアプリが接続を open すると、(1) で作ってプールに戻された接続を取得し、それを使ってデータベースへの接続が行われます。
(4) つまり、2 回目以降は接続プールと接続の作成は行わないのでその分時間は短くなります。
そのあたり質問者さんの環境ではどうなっているのか分かりませんが、とにかく測るべき処理だけが正しく計測されているか考えた方が良さそうと思います。
投稿2023/06/09 21:48
編集2023/06/10 04:28
退会済みユーザー
総合スコア0
0
計測するなら、SQL_NO_CACHEオプションを使いましょう。
多分、1回目の結果のキャッシュが使われて早く見えるだけだと思います。
インデックスがある場合は、インデックスにある項目だけを使用すると、＊などインデックスに含まれない項目を指定するより早くはなります。
投稿2023/06/10 06:58
総合スコア25173
SQL_NO_CACHEオプションを使ってテストしてみました。
今回は、↓こちらが要因で早くなっていたようです。
>インデックスがある場合は、インデックスにある項目だけを使用すると、＊などインデックスに含まれない項目>を指定するより早くはなります。
３つのカラムそれぞれに、まさにインデックスを指定してありました。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2023/06/10 06:13
2023/06/10 15:58