大量のデータを取得して、GoogleMapにマーカーを全て表示したい。

約２万件の住所が入ったデータを「全件」取得して、GoogleMapにAPIを使用してマーカーを全て表示したいのですが、
かなり重くなってしまい、解決案があれば教えていただきたいです。

2万件ほどの住所データを「全件」取得する際に時間がかなりかかってしまっています。
[Google Maps JavaScript API]を使用して、マーカーを表示しております。

マップの表示範囲内のみのデータを取得していますが、
ズームボタンで縮小された時（日本地図が全てが範囲になったとき）に、2万件の住所データが全件必要になり、
2万件ほどの情報を取得しようとすると応答がなくなります。（1000件程度だと2秒ほどで、全マーカー表示できます。）

説明たらずや理解不足なことがあると思いますが、よろしくお願いいたします。

ーーーーーーーーーーーーーーーーーーーーーーーーー
・追加

データベース:mysql
|shop_table ||||||
|:--|:--|:--|
|ID|name|pref|address|category|status|LATITUDE|LONGITUDE|

|shopimg_table|||||
|:--|:--|:--|
|ID|shop_id|img_url|title|status|

|menu_table |||||||
|:--|:--|:--|
|ID|shop_id|name|price|category|status|img_url|

*Ajaxで処理

//データベースに接続
$db = new PDO($dsn, $username, $password, $options);

//現在のマップ表示範囲から検索

$search_sql = "SELECT `shop_table`.`ID` FROM `shop_table`
               WHERE `shop_table`.`status` = 1
               AND `shop_table`.`LATITUDE` >= '33.643378363341014'
               AND `shop_table`.`LATITUDE` <= '37.62418548727742'
               AND `shop_table`.`LONGITUDE` >= '137.78986649375'
               AND `shop_table`.`LONGITUDE` <= '141.70099930625'"
$search_res = $db->query($search_sql);
$shop_ids = $search_res->fetchAll();

＄shops = array();
foreach(＄shop_ids as $id){//下記ループ処理（マップ表示範囲の件数 最大2万件 をループ）

 $shop_sql = "SELECT * FROM `shop_table`
                WHERE `shop_table`.`status` = 1
                AND `shop_table`.`ID` = $id"
 $shop_res = $db->query($shop_sql);
 $shop = $shop_res->fetchAll();

 $menu_sql = "SELECT * FROM `menu_table`
              WHERE `menu_table`.`status` = 1
              AND `menu_table`.`shop_id` = $id"
 $menu_res = $db->query($menu_sql);
 $menu = $menu_res->fetchAll();

 $image_sql = "SELECT * FROM `shopimg_table`
              WHERE `shopimg_table`.`status` = 1
              AND `shopimg_table`.`shop_id` = $id"
 $image_res = $db->query($image_sql);
 $image = $image_res->fetchAll();


 ＄shops[$id][shop_name] = ＄shop[name]
 ＄shops[$id][shop_address] = ＄shop[address]
 ...
 ＄shops[$id][menu_name] = ＄menu[name]
 ＄shops[$id][menu_price] = ＄menu[price]
 ...
 ＄shops[$id][image_title] = ＄image[title]
 ...
}

//取得した情報を配列に置き換えて、Jsonにしています。
＄shopsをJaonにして返す。

phpMyAdminで、EXPLAINをSQL文の先頭に付けて実行した結果

type => ref
key => status
rows => 5500
Extra => Using Where

ループ処理内の一文のsqlは、Extra => nullに変わっていました。

Using Whereでrowsが多い場合は、大半がWHERE句で絞り込まれるということなんですが、
インデックスがうまく施せていないのでしょうか。

microtime(true)の関数を使用して処理スピードを計測した結果

ループ処理内の全てのsqlの処理で、全体の9割の時間をとっていました。
3000件ほどで実行時、全ての処理が、約8.2秒

検索sql => (ほぼ０秒)
ループ処理内sql => (7秒)
(shop_table=>3.3,shopimg_table=>3.2,menu_table=>0.5)
ループ処理内配列格納 => (1秒)
jsonに変更 => (ほぼ０秒)

ループ処理で大半の時間がかかっていることが分かりました。
まず、ここのSQL文の組み立てを、変えていかないといけないかと思いますが、
どのようにすれば、パフォーマンス的にも良いでしょうか。

Lhankor_Mhy

2021/05/12 01:17

ボトルネックとなっているのはどこですか？

fujiraidar

2021/05/12 01:56

php(ajax)で2万件ほどの住所データを取得する際に時間がかなりかかってしまっています。取得したJSONデーターからマーカーの設置は早く処理できています。

退会済みユーザー

2021/05/12 02:14 編集

質問者さんは当たり前に作っているつもりかもしれませんが、データベースやjsライブラリの組み合わせ方でどんなふうにでも作れてしまう話に見えますので、せめてどういう技術、どういうライブラリを使っているかなど、もっと具体的に話を聞かせてもらわないとアドバイスできません。

Lhankor_Mhy

2021/05/12 02:27

サーバでの処理に時間がかかっているのですか？　それとも通信に時間がかかっているのですか？

fujiraidar

2021/05/12 03:27

サーバでの処理に時間がかかっていると思います。

fujiraidar

2021/05/12 03:33

jsライブラリなどは、特に使用しておりません。データベースはmysqlです。データベースやjsライブラリの組み合わせ方で改善方法があるのでしょうか。

Lhankor_Mhy

2021/05/12 04:01

「サーバでの処理に時間がかかっていると思います」とのことですが、計測しないのですか？ --- 私ならば、2万件全てを送信するのではなくて、地図の表示範囲だけを送信するようにすることを検討します。

fujiraidar

2021/05/12 05:41

サーバサイドの知識はあまりないのですが、console.time()では計測した結果は、1万件で20000msでした。地図の表示範囲だけを送信するように設定しているのですが、マップを縮小された時に必要データがどんどん増えていってしまいます。

Lhankor_Mhy

2021/05/12 05:57 編集

そうではなくて、20000ms のうち、1000msがサーバ処理、18000msが送受信、1000msがクライアントの描画、とかであれば、対策する場所が変わってきませんか？　という話です。「サーバでの処理に時間がかかっている」ことに自信を持っていて、改めて計測するまでもない、ということであれば、もちろん結構ですが。 --- >__マップを縮小された時に必要データがどんどん増えていってしまいます。 __ ご希望のマクドナルドを見てきましたが、全国で2915件、レスポンスに462msでした。マクドナルド級のリソースを使っても、20000件は単純計算で3～4秒かかることになります。 DBには詳しくないので的外れかもしれませんが、JOINなどをしてレスポンスが重くなっているなら、位置情報だけ先に返して地図を描画し、住所などの情報は必要になった時（たとえばマーカークリック時）に取得するようにすれば、多少は負荷が減るのではないでしょうか。位置情報が変化しないような店舗一覧などであれば、キャッシュも効くと思いますし。

Lhankor_Mhy

2021/05/12 06:03

ちなみに、私は本職が不動産営業なのですが、同じように物件一覧（20000件ぐらいありました）を地図に表示するコードを書いたことがあります。その際は、地図を細かく切り分けて、それぞれのエリアごとに読み込みをし、地図範囲が広がった時に読み込み済みの情報は再読み込みをかけないような実装にしました。その時に書いたライブラリがこれでした。 http://realtor-readyabooks.hatenablog.com/entry/20100901/1283339228

退会済みユーザー

2021/05/12 06:14

まぁ、はやくEXPLAINやってほしい。クエリーのSQL文だけなら質問文に追記してくれたら、それに即したアドバイスもしやすいし。

fujiraidar

2021/05/14 02:38

EXPLAINを実行してみました。一度インデックスを再度確認し、設定してみてから、マーカークリック時に取得する方法も検討するようにします。ライブラリも使わせていただくかもしれません。ありがとうございます。

phper.k

2021/05/14 04:41

PHPのコード見せれば、こんなに長引くことないのに、なんで出さないの？

fujiraidar

2021/05/14 05:57

当初は、phpやデータベースのソースに問題があるということを考えておりませんでした。

phper.k

2021/05/14 11:15 編集

典型的な 1 + N 問題じゃん。そりゃ遅くなるわ https://qiita.com/muroya2355/items/d4eecbe722a8ddb2568b

fujiraidar

2021/05/17 01:03

ありがとうございます。Joinを使ったsqlだと、早くなりました。ただ、2万件になるとまだ重いので、データベースのインデックスの見直しや空間インデックスの作成をしております。

行動規範の内容に同意します

回答1件

ベストアンサー

SQLの実行計画のチェック。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化

インデックスが適切に施されていない、あるいは施されていても効いていないって話かと。

それと、コードを見ていないので当てずっぽう感ありますが、
2万件をどうやってjsonに加工しているのか。
文字列の連結を都度繰り返すのか、implode()で一発で連結するのかとか、
処理の区切りごとにmicrotime(true)で測定して比較するとか。
PHPの処理速度を計測 - Qiita

それと、
MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.5.3.5 空間分析の最適化
のように空間情報のためのインデックスもあるので、
例えばマップ表示中の中心点の緯度経度情報を元に、
表示範囲に相当する範囲のPOIだけ抽出して送信する、
みたいなクエリーだってできるはず。
いきなり2万件送信するんじゃなくて。

それと、
Googleマップを表示したいからGoogle Map APIを使うというのはある意味正しいですが、
Mapbox GL JS(有料)やOpenLayersとかLeafletなどの地図を扱えるjsライブラリを組み合わせつつ、
サーバー側も然るべき仕組み（MapServerとか）を組み合わせることで、
表示範囲に限定した情報のみ非同期に取得して地図情報反映する仕組みが実現しやすいです。
自前で処理を起こさず、仕組み建てを変えてみるのも良いかと。
サーバーサイドが明るくないのであればなおさら。

とはいえ、仕組みをいじるのは腰が重いでしょうから、
データベース周りをしっかりやってほしいかな、
ヘボいクエリーを叩き直すことで応答までの時間がケタ１つ２つ変わるなんて話はよくあるし。
主キー設定してますか、
テーブルをJOINするお互いのキーにインデックスはありますか、
JOINするときに関数で加工しているなら止めましょうね、
全部を一度の連結しなくても、情報を小出しにして何度も取得しに行くよう、一度に取得するデータの粒度を小さくするアルゴリズムを考えるのも大事。
２万件が相手だとしても、サーバーのチューニングも含めて徹底的に手を施すと
クエリーだけなら６秒以上待たせる場面は減らせそうな気がする。（独断と偏見。）

クエリー3種類、それをphpコードでどう処理しているのか、
っていうところも見たかったですが。

緯度経度を伴うテーブルの、緯度経度を文字列のまま保持しているんでしょうか？
mysqlでもpostgresqlでも空間情報を扱う機能があるので、
然るべき測地系の空間データとして格納し、
空間インデックスを仕込むべきです。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.5 空間データの拡張
この近辺をよく読んで、空間データとして扱えるよう変換したテーブルを作って変換してください。
参考になる記事：【MySQL】Geometry型で位置情報を扱う - Qiita

それと。
shop_table.idに対してshopimg_table.idは複数、
同じく
shop_table.idに対してmenu_table.idも複数、
っていう感じでしょうか。
クエリー結果をもとにループしている中で、
別のクエリーを実行するのは、あまり良くないような。
プリペアドステートメントの仕組みを使って、
パラメータのみ変えて実行するならまだSQL文の解釈にかかる処理時間を省くことができますが、
もしも、一番外側のループの値を都度、SQL文に文字列連結してクエリーを作っているなら、
即刻やめて
PDO::prepare
PDOStatement::bindValue
PDOStatement::execute
を組み合わせてプリペアドステートメントによるクエリー実行に直します。

PHPでデータベースに接続するときのまとめ - Qiita
をなぞって、prepare→bindValue→executeの流れを丁寧にやるといいです。

最後に、
出力用のjson文字列を作るのに、最後にjson_encode()にかけるとして
その直前までひとつの文字列変数に文字列連結して文字列連結して文字列連結してってやり方をしていると、
パフォーマンスが落ちるかもしれません。
配列で保持できるようであれば、
implode()を使って配列を文字列に連結するようにしたらよいかと。

それと余談。
狭いところに2万件もプロットする見せ方も、考え直すべきですね。
先の空間データ型を使うようになると、
地域（例えば市町村、大字、町丁目とか）の形をMULTIPOLYGON型で格納したテーブルを用意すると、
A市に含まれるPOIが何件、B市に含まれるPOIが何件、
みたいなクエリーも作れます。
何個も何十個もプロットせず、ざっくり数字だけ表示しておいて省略表示するべきでしょうね。
ネット上に誰かが作ったデータがあるかもしれませんし、
自分でQGISを使って作ることもできますね。

あー、php上で配列要素から配列要素に、
ループを回して代入するのは、ほんとに無駄なことをやってますね。
それを2万件も回しているかと思うと呆れてきます。

そういうことは、データベースに任せてください。
クエリーの仕事です。
とてもphp上でやるべきことではない。
つまり、テーブル同士を欲しい情報のカタチにouter joinして
データの結合をデータベース内にやらせてください。
適材適所というか、データの並べ替えや整理が得意なデータベースの仕事を
わざと不得意なphpにやらせてるのが悪いです。

outer joinでテーブルのデータ構造の主従関係に沿って連結して、
ほしい並び順はorder by句でコントロール。
それができれば、単にfetchAll()で完結しませんかね。

投稿2021/05/12 02:21

編集2021/05/17 01:19

退会済みユーザー

総合スコア0

fujiraidar

2021/05/12 05:54 編集

ありがとうございます！サーバサイドの知識はあまりないのですが、インデックスが適切に施こした場合、処理スペードが劇的に早くなるものなのでしょうか。データを取得して、Jsonにする処理までをconsole.time()で計測した結果、一万件で20000msでした。表示範囲に相当する範囲を表示するようにしていますが、マップには縮小機能もつけてまして、範囲が縮小するにつれ大きくなってしまい、日本全体が範囲になると２万件のデータが必要となりました。

退会済みユーザー

2021/05/12 05:56

書いているコードがほとんど示されていない状況なので、とにかくどこの処理が重いのかを計測するためにログ出力を強化すること、クエリーを（デバッグ出力するなどして）単体で抜き出してEXPLAINにかけてクエリー自体のボトルネック調査をする、をやってください。 jsonを返す処理だと標準出力上にデバッグ情報を出すわけにも行かないので、ファイルへのログ出力関数を定義しましょう。第三者にもわかるような実態把握がないと、仮定や想像の上で議論してもはじまらないのです。

fujiraidar

2021/05/13 10:33

いただいた記事からEXPLAINや、処理の計測を行なってみました。

退会済みユーザー

2021/05/14 01:41 編集

shopimg_table=>3.2 ってマジですか？ where shop_id=?とかやれば、1レコードとか数レコード？に絞り込めて応答は早そうな気がするのですが、まさか、where句を書かずにphpでループを回して選別しているんじゃないですよね？

fujiraidar

2021/05/14 03:44 編集

空間インデックスやJsonについては、いただいた記事を参考にして、時間がかかると思いますがやっていこうと思います。ありがとうございます。 shop_table.idに対してshopimg_table.idとmenu_table.idは複数の関係ですが、mapに表示させるのは1つずつにしています。経度緯度の検索から取得したショップのIDの配列をphpでループさせています。where句は書いてます。 select shopimg_table where shop_id = ショップのID 3.2秒は、3000件ほどループした時のこのsql実行の合計時間です。 (このsqlが多くて2万回実行されているのですが、ここも問題でしょうか）

退会済みユーザー

2021/05/14 02:59

それで3秒以上かかるって、shop_idに対するインデックスがshopimg_tableやmenu_tableに対して、適切に施されていないんじゃないかな。至急点検を。

fujiraidar

2021/05/14 03:53

3.2秒は、3000件ほどループした時のこのsql(×3000回)実行の合計時間です。単純計算すると、一件0.001秒になるのですが、遅い感じでしょうか。 select shopimg_table のSQLの先頭にEXPLAINを付けて実行しましたが、上記内容と同じでした。

退会済みユーザー

2021/05/14 04:17 編集

クエリーの結果をループ処理しながら、別のクエリーを実行して、みたいな二重ループ三重ループかとおもったら、そうではない？日本全体ってことだけど、情報の更新頻度が低ければ、いちいちクエリー結果を返さずに「作り置き」したらいいんじゃない？毎晩定時刻にクエリーを実行して「作り置き」を生成し直すっていう。あと、クエリー自体はまともだと仮定したら、phpでの加工処理だわ。常にクエリーを実行して結果を返したいのを速くするのに、そもそもサーバーの能力が足りていないかもしれないし。それを評価するためにも、なにかの関数実行するごとのマイクロ秒単位の処理時間を積み上げていかないと。クエリー全体とかで秒単位の評価じゃ甘すぎる。とはいえ、2万件をいちリクエストで返すんだから、各処理をコンマゼロゼロ何秒の単位で丁寧にチェックしたらいいと思う。

fujiraidar

2021/05/17 01:54 編集

クエリーの結果をループ処理しながら、別のクエリーを実行して、みたいな二重ループ三重ループかとおもったら、そうではない？ →そうだったかもしれないです。Join句を使用するとましになりました。 shop_idに対するインデックスがうまく作れていなかったので、作り直すと、早くなりました。今までは、statusカラムに貼っていたので、shop_idに対しても、早くなりました。以上で、2万件でも、2.78sほどで表示が可能になりました。そのうちの2sほどが検索のsql実行時の時間なので、空間インデックスを作成して、改善していこうと思います。あと、パフォーマンス的にも良いと感じたため、地図範囲が広がった時、ドラッグした時に読み込み済みの情報は再読み込みをかけないような実装もしていきたいと考えております。コメントでライブラリ情報をいただいたので一度試してみたいなと思います。バックエンドやサーバーサイドの知識は、ほとんどなかったのですが、みなさんのご教授のもと、かなり身についた気がします！.. ありがとうございました。

行動規範の内容に同意します