mysqlでSELECTが遅すぎる。（チューニング）

ご覧頂きありがとうございます！
初投稿ですがよろしく願いします。

前提・実現したいこと

目的: 指定した緯度経度に近い順にスポット情報を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」の後に消えました。
③副問合せにすると一時テーブルがなくなるのはなぜでしょうか？
※参考サイトをご共有いただけるだけでも大変助かります。
検索してもわからなくて、、

よろしく願いします。

yambejp

2020/03/16 04:53

create table+insertでサンプルデータを提示ください生成列にインデックスはったり、triggerを利用して別テーブルで distanceを管理したりすることになるのかなとは思いますがやってみないとなんとも言えません

hukutoro

2020/03/16 04:57

早速のご回答ありがとうございます！「生成列にインデックスはったり、triggerを利用して」とのことですが、冒頭に記載していますように、こちらではテーブル定義の変更ができないのです。サンプルデータは準備でき次第添付させていただきますね。

yambejp

2020/03/16 05:01

> テーブル定義の変更ができないなるほど、そうなると抜本的な高速化は無理ですね遅いものだと諦めるか、その検索をしないかしかありません。

hukutoro

2020/03/16 05:08

やはりそうですよね。。実際には汎用区分テーブル(Entity-Attribute-Value)を何度も結合していたりして厄介なのですが。。本文中の「お聞きしたいこと」に記載しております3点に関して、もしお分かりでしたらご回答いただけると大変助かります。

yambejp

2020/03/16 05:25 編集

とにかく検証ができないのでサンプルが提示できないなら自力でなんとかしてください

hukutoro

2020/03/16 06:58

遅くなりすみません。サンプルファイルを追記しましたのでよろしく願いします。

yambejp

2020/03/16 07:20

リレーション状況がわからないのですがleft joinはinner joinでは思った通りのものがヒットできないのでしょうか？

hukutoro

2020/03/16 07:32

案件のコードを共有できないので、サンプルコードだと確かに分かりづらくてすみません。基幹システムとの兼ね合いでLEFT JOIN でなくては想定のデータがヒットしないようです。

yambejp

2020/03/16 07:36

left joinが全体のパフォーマンスを下げている感は否めません inner joinをうまくからめてやれば高速処理ができそうな気がします

hukutoro

2020/03/16 07:40

何度も迅速なご回答ありがとうございます！今回の質問の趣旨ですが、「どうしたら高速化できるか」というものではありません。本文にも記載しております通り下記3点をご回答いただきたく質問させていただいております。

hukutoro

2020/03/16 07:40

①この対応でDBサーバが停止してしまう(too many connections)は防ぐことができるのでしょうか？ ②PHPで取得処理が完了し、スクリプトが終了後にshow processlistを実行すると「Sending data」が表示されたままでしたがなぜでしょうか？ ※1分ほどすると「Sleep」の後に消えました。 ③副問合せにすると一時テーブルがなくなるのはなぜでしょうか？

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

回答1件

ElasticSearchを導入してgeo_point queryを使う方が良さそうに思いました。
https://www.elastic.co/guide/en/elasticsearch/reference/6.8/geo-point.html

投稿2020/03/17 05:57

rysh

総合スコア874

hukutoro

2020/03/17 06:02

ご回答いただきありがとうございます！この案件のルール上外部サービスが使えないのです、そのためクライアントのレガシーな自社サーバを使うことになっている経緯があります。今回の質問の趣旨ですが、「どうしたら高速化できるか」というものではありません。本文にも記載しております通り下記3点をご回答いただきたく質問させていただいております。 ①この対応でDBサーバが停止してしまう(too many connections)は防ぐことができるのでしょうか？ ②PHPで取得処理が完了し、スクリプトが終了後にshow processlistを実行すると「Sending data」が表示されたままでしたがなぜでしょうか？ ※1分ほどすると「Sleep」の後に消えました。 ③副問合せにすると一時テーブルがなくなるのはなぜでしょうか？

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