質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.42%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

2回答

790閲覧

MySQLのSELECT時間を早くするために、カラム毎に複数回に分けてSELECTする事はありますか?

ID_7UGzV8hCHGs5

総合スコア58

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2023/06/09 20:00

編集2023/06/10 15:48

気になる事

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しても、トータル時間に大差はない」事が分かりました。

備考:3つのカラムそれぞれに、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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ID_7UGzV8hCHGs5

2023/06/10 06:13

ありがとうございます。 ご指摘通り全てのSELECT文に、`ORDER BY `id`'をつけて、再度実行したところ、 どの結果も、920msec前後となり、大差がありませんでした。 どうもありがとうございました。
ID_7UGzV8hCHGs5

2023/06/10 15:58

ORDER BY を付けていないSELECTの場合は、テーブルの一番上から順番にSELECTされていくと、致命的な勘違いしておりました。 おっしゃる通り、ORDER BY を付けていないSELECT結果は、まったく順番が異なっており、使えないデータになっておりました。 ありがとうございました。
guest

0

計測するなら、SQL_NO_CACHEオプションを使いましょう。
多分、1回目の結果のキャッシュが使われて早く見えるだけだと思います。

インデックスがある場合は、インデックスにある項目だけを使用すると、などインデックスに含まれない項目を指定するより早くはなります。

投稿2023/06/10 06:58

sazi

総合スコア25256

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ID_7UGzV8hCHGs5

2023/06/10 07:51

ありがとうございます。 教えて頂いたオプションを是非試してみます。 取り急ぎお礼申し上げます。
ID_7UGzV8hCHGs5

2023/06/10 15:56

SQL_NO_CACHEオプションを使ってテストしてみました。 今回は、↓こちらが要因で早くなっていたようです。 >インデックスがある場合は、インデックスにある項目だけを使用すると、*などインデックスに含まれない項目>を指定するより早くはなります。 3つのカラムそれぞれに、まさにインデックスを指定してありました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.42%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問