Q&A
create table+insertでサンプルデータを提示ください
生成列にインデックスはったり、triggerを利用して別テーブルで
distanceを管理したりすることになるのかなとは思いますが
やってみないとなんとも言えません
ご覧頂きありがとうございます！
初投稿ですがよろしく願いします。
前提・実現したいこと
目的: 指定した緯度経度に近い順にスポット情報をDBから取得
既存の基幹システムとの連携のためテーブル定義は変更できない
サーバはレガシー(MySQL5.1・PHP5.3)
テーブル定義
CREATE TABLE `spot` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'スポットID', `latitude` decimal(9,6) DEFAULT NULL COMMENT '緯度', `longitude` decimal(9,6) DEFAULT NULL COMMENT '経度', `create_at` datetime NOT NULL COMMENT '作成日', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `spot_review` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'レビューID', `spot_id` int(11) NOT NULL COMMENT 'スポットID', `score` int(11) NOT NULL COMMENT '点数', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
サンプルファイル ※teratailでのファイル添付方法がわからず外部ストレージです
該当のソースコード
sql
1SELECT spot.*, spot_review.score, 2 (6371 * acos( 3 cos(radians('35.439440')) 4 * cos(radians(latitude)) 5 * cos(radians(longitude) - radians('139.634000')) 6 + sin(radians('35.439440')) 7 * sin(radians(latitude)) 8 )) AS distance 9FROM spot 10LEFT JOIN spot_review ON spot.id = spot_review.spot_id 11WHERE latitude IS NOT NULL AND longitude IS NOT NULL 12ORDER BY distance ASC, create_at DESC 13LIMIT 0, 10;
※実際にはもっと複雑なためテーブル結合など一部省略し、単純化しています。
登録数は15000件ほど
発生している問題・エラーメッセージ
取得が遅い、DBサーバが停止してしまう(too many connections)
ステージング環境のログ
sql
1show processlist; 2+---------+-------+------------------------------+------------------------------------------+ 3| Command | Time | State | Info | 4+---------+-------+------------------------------+------------------------------------------+ 5| Execute | 2068 | Copying to tmp table on disk | SELECT * (6371 * acos( | 6| Execute | 2067 | Copying to tmp table on disk | SELECT * (6371 * acos( | 7| Execute | 2066 | Copying to tmp table on disk | SELECT * (6371 * acos( | 8| Execute | 2066 | Copying to tmp table on disk | SELECT * (6371 * acos( | 9| Execute | 2065 | Copying to tmp table on disk | SELECT * (6371 * acos( | 10| Execute | 2064 | Copying to tmp table on disk | SELECT * (6371 * acos( | 11| Execute | 2064 | Copying to tmp table on disk | SELECT * (6371 * acos( | 12| Execute | 2063 | Copying to tmp table on disk | SELECT * (6371 * acos( | 13| Execute | 2062 | Copying to tmp table on disk | SELECT * (6371 * acos( | 14| Execute | 2061 | Copying to tmp table on disk | SELECT * (6371 * acos( | 15| Execute | 2060 | Copying to tmp table on disk | SELECT * (6371 * acos( | 16| Execute | 2060 | Copying to tmp table on disk | SELECT * (6371 * acos( | 17| Execute | 2059 | Copying to tmp table on disk | SELECT * (6371 * acos( | 18+---------+-------+------------------------------+------------------------------------------+
ローカル環境のログ
負荷実験のためcurlで同時に100アクセスした場合
※MySQL5.6（急ぎ環境が準備できなかったため）
※「Copying to tmp table on disk」は再現できず
※ログは一部抜粋
sql
1show processlist; 2+---------+-------+------------------------+--------------------------+ 3| Command | Time | State | Info | 4+---------+-------+------------------------+--------------------------+ 5| Execute | 48 | Sending data | SELECT * (6371 * acos( | 6| Execute | 47 | Sending data | SELECT * (6371 * acos( | 7| Execute | 46 | Sending data | SELECT * (6371 * acos( | 8| Execute | 46 | Creating sort index | SELECT * (6371 * acos( | 9| Execute | 45 | Sending data | SELECT * (6371 * acos( | 10| Execute | 44 | Sending data | SELECT * (6371 * acos( | 11| Execute | 44 | Sending data | SELECT * (6371 * acos( | 12| Execute | 43 | Sending data | SELECT * (6371 * acos( | 13| Execute | 42 | Sending data | SELECT * (6371 * acos( | 14| Execute | 41 | Creating sort index | SELECT * (6371 * acos( | 15| Execute | 40 | Sending data | SELECT * (6371 * acos( | 16| Execute | 40 | Sending data | SELECT * (6371 * acos( | 17| Execute | 39 | Sending data | SELECT * (6371 * acos( | 18+---------+-------+------------------------+--------------------------+
EXPLAIN
sql
1+----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+ 2| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 3+----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+ 4| 1 | SIMPLE | spot | ALL | IDX01_Spot | NULL | NULL | NULL | 17307 | Using where; Using temporary; Using filesort | 5+----+-------------+------------+--------+-----------------+---------+---------+-------+-------+----------------------------------------------+
試したこと
ローカル環境では起きないが本番ではログに「Copying to tmp table on disk」とあるので、
EXLAINの「Using temporary」を疑い、ORDER BY原因があることがわかりました。
空間インデックスなどが使えないためサブクエリにして、メインクエリにORDER BYをつけたところ「Using temporary」はなくなり以前よりは早くなりました。
sql
1SELECT * 2FROM( 3 SELECT spot.*, spot_review.score, 4 (6371 * acos( 5 cos(radians('35.439440')) 6 * cos(radians(latitude)) 7 * cos(radians(longitude) - radians('139.634000')) 8 + sin(radians('35.439440')) 9 * sin(radians(latitude)) 10 )) AS distance 11 FROM spot 12 LEFT JOIN spot_review ON spot.id = spot_review.spot_id 13 WHERE latitude IS NOT NULL AND longitude IS NOT NULL 14) as tmp 15ORDER BY distance ASC, create_at DESC 16LIMIT 0, 10;
EXPLAIN
sql
1+----+-------------+------------+--------+-----------------+---------+---------+---------------+-------+----------------+ 2| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 3+----+-------------+------------+--------+-----------------+---------+---------+---------------+-------+----------------+ 4| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 8654 | Using filesort | 5| 2 | DERIVED | spot | ALL | IDX01_Spot | NULL | NULL | NULL | 17307 | Using where | 6+----+-------------+------------+--------+-----------------+---------+---------+---------------+-------+----------------+
お聞きしたいこと
①この対応でDBサーバが停止してしまう(too many connections)は防ぐことができるのでしょうか？
②PHPで取得処理が完了し、スクリプトが終了後にshow processlistを実行すると「Sending data」が表示されたままでしたがなぜでしょうか？
※1分ほどすると「Sleep」の後に消えました。
③副問合せにすると一時テーブルがなくなるのはなぜでしょうか？
※参考サイトをご共有いただけるだけでも大変助かります。
検索してもわからなくて、、
よろしく願いします。
早速のご回答ありがとうございます！
「生成列にインデックスはったり、triggerを利用して」とのことですが、
冒頭に記載していますように、こちらではテーブル定義の変更ができないのです。
サンプルデータは準備でき次第添付させていただきますね。
> テーブル定義の変更ができない
なるほど、そうなると抜本的な高速化は無理ですね
遅いものだと諦めるか、その検索をしないかしかありません。
やはりそうですよね。。
実際には汎用区分テーブル(Entity-Attribute-Value)を何度も結合していたりして厄介なのですが。。
本文中の「お聞きしたいこと」に記載しております3点に関して、もしお分かりでしたらご回答いただけると大変助かります。
とにかく検証ができないのでサンプルが提示できないなら自力でなんとかしてください
遅くなりすみません。
サンプルファイルを追記しましたのでよろしく願いします。
リレーション状況がわからないのですがleft joinはinner joinでは
思った通りのものがヒットできないのでしょうか？
案件のコードを共有できないので、サンプルコードだと確かに分かりづらくてすみません。
基幹システムとの兼ね合いでLEFT JOIN でなくては想定のデータがヒットしないようです。
left joinが全体のパフォーマンスを下げている感は否めません
inner joinをうまくからめてやれば高速処理ができそうな気がします
何度も迅速なご回答ありがとうございます！
今回の質問の趣旨ですが、「どうしたら高速化できるか」というものではありません。
本文にも記載しております通り下記3点をご回答いただきたく質問させていただいております。
①この対応でDBサーバが停止してしまう(too many connections)は防ぐことができるのでしょうか？
②PHPで取得処理が完了し、スクリプトが終了後にshow processlistを実行すると「Sending data」が表示されたままでしたがなぜでしょうか？
※1分ほどすると「Sleep」の後に消えました。
③副問合せにすると一時テーブルがなくなるのはなぜでしょうか？
回答1件
0
ElasticSearchを導入してgeo_point queryを使う方が良さそうに思いました。
https://www.elastic.co/guide/en/elasticsearch/reference/6.8/geo-point.html
投稿2020/03/17 05:57
総合スコア874
ご回答いただきありがとうございます！
この案件のルール上外部サービスが使えないのです、そのためクライアントのレガシーな自社サーバを使うことになっている経緯があります。
今回の質問の趣旨ですが、「どうしたら高速化できるか」というものではありません。
本文にも記載しております通り下記3点をご回答いただきたく質問させていただいております。
①この対応でDBサーバが停止してしまう(too many connections)は防ぐことができるのでしょうか？
②PHPで取得処理が完了し、スクリプトが終了後にshow processlistを実行すると「Sending data」が表示されたままでしたがなぜでしょうか？
※1分ほどすると「Sleep」の後に消えました。
③副問合せにすると一時テーブルがなくなるのはなぜでしょうか？
あなたの回答
tips
プレビュー
