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

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

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

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

4回答

13518閲覧

サブクエリが遅いのですが

vc3000

総合スコア196

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2015/08/01 07:51

編集2015/08/03 22:36

よくあるブログのデータベースで、users(ユーザー)とposts(投稿)が1対多で結びついているとします。
ユーザーごとの投稿数をつけてユーザー一覧を出力したい場合、以下のようなSQLを書くとだいぶ遅くなってしまいます(同じサブクエリを2回書いているのも気になります)。

SELECT * ,(SELECT Count(*) FROM posts WHERE posts.user_id = users.id) cnt FROM users ORDER BY (SELECT Count(*) FROM posts WHERE posts.user_id = users.id) DESC

こういう場合、usersテーブルかどこかに投稿数のカラムを作っておくべきでしょうか?それともSQLの工夫でもっと速くする方法がありますか?
(MySQLを使っていますが、他のDBの場合も知りたいです)

追記:
ORDER BYは普通にcntと書けますね。

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

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

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

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

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

guest

回答4

0

ベストアンサー

MySQLでしたら
コレで出して頂いた物よりはある程度速くなるんじゃないかなと思います。

SQL

1SELECT 2 U.*, 3 IFNULL(P.post_count, 0) AS cnt 4FROM 5 users AS U 6LEFT JOIN 7 (SELECT 8 COUNT(1) AS post_count, 9 user_id 10 FROM 11 posts 12 GROUP BY user_id ) AS P 13ON 14 U.id = P.user_id 15ORDER BY IFNULL(P.post_count, 0) DESC

また、INDEXの見直しや絞り込めるようならWHEREで絞り込んだ方が速くなると思います。

投稿2015/08/01 08:00

編集2015/08/01 08:05
nanndemoiikara

総合スコア775

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

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

vc3000

2015/08/01 08:15

ありがとうございます。 確かに実行時間が1/2~1/3になりました!
guest

0

このSQLが重いのは、SELECTやORDERでのサブクエリが相関サブクエリになっていて、抽出行数と同じ回数のサブクエリが実行されているためではないかと思います。
(確認してませんが、サブクエリ内のusers.idが行毎に異なるので恐らく)
EXPLAINで調べてみると具体的にどうなっているか分かるかも知れません。参考ページ

ではどうすれば良いかというと、nanndemoiikara氏と殆ど同じです。
こちらのサブクエリ実行回数は1回で済むので、抽出行数が増えれば増えるほど実行コストは(相関サブクエリに比べて)小さくなると思います。
(その代わりusers各行とのマッチングコストは増えますが前者に比べて微々たるものです。またこのコストはusers.idにindexを作成することで軽減できます。)

SQL

1SELECT U.*, IFNULL(P.post_count, 0) AS cnt 2FROM users AS U 3LEFT JOIN ( 4 SELECT COUNT(*) AS post_count,user_id 5 FROM posts 6 GROUP BY user_id 7 ) AS P ON U.id = P.user_id 8ORDER BY cnt DESC

order by の関数実行省いたのと、count()に変えただけです。
countはpostgresだと(
)の方が早かった(index使うから?)と思うのですが、mysqlだと(1)のほうが早いのかも知れません。

投稿2015/08/01 09:57

hirohiro

総合スコア2068

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

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

nanndemoiikara

2015/08/01 10:37

postgresだとアスタリスクの方が速くなるんですね。 勉強になります。
hirohiro

2015/08/01 10:49 編集

indexのついた項目だと、範囲指定や一致条件なら何番目~何番目のように抽出できるので行数を計算で出せるから抽出行が増えるほど...みたいな理屈だったはずですが、昔見たものなので今はどうなんでしょう?実際の所、どちらでも返答時間には殆ど影響無いです。
guest

0

基本通りに書くのが一番速そうですけど、どうでしょうね。

SQL

1SELECT 2 users.* 3 , count(DISTINCT post_id) AS cnt 4FROM 5 users 6LEFT JOIN 7 posts 8ON 9 users.user_id = posts.user_id 10GROUP BY 11 users.user_id;

sql

1CREATE TABLE `users` ( 2 `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, 3 `user_name` varchar(11) DEFAULT NULL, 4 PRIMARY KEY (`user_id`) 5) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 6 7CREATE TABLE `posts` ( 8 `post_id` int(11) unsigned NOT NULL AUTO_INCREMENT, 9 `user_id` int(11) DEFAULT NULL, 10 PRIMARY KEY (`post_id`), 11 KEY `user_id` (`user_id`) 12) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

手元の環境で試してみたけど、レコード数が少ないので、他の方の回答でも大差ありませんでしたが、Explain してみてやはり基本通りの方がインでクスが適切に使われる様子ですね。

投稿2015/08/01 09:39

編集2015/08/01 09:58
退会済みユーザー

退会済みユーザー

総合スコア0

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

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

nanndemoiikara

2015/08/01 10:38 編集

usersテーブルなのにuser_idは構造としてどうなのでしょうか? postsのuser_idにunsignedが付いていないのはなぜでしょうか?
退会済みユーザー

退会済みユーザー

2015/08/01 11:07 編集

手元で試すだけだからそうしただけです。 なんせ、質問者自身が構造を出してないので、自分はこういう構造で試しましたよということがわかるようにしただけですね。 > usersテーブルなのにuser_idは構造としてどうなのでしょうか? 何がおかしいと思うの?任意の名前をつけることができるんだからおかしくはないでしょう。
nanndemoiikara

2015/08/01 12:21

ご返信ありがとうございます。 > 手元で試すだけだからそうしただけです。 。。。 > 何がおかしいと思うの?任意の名前をつけることができるんだからおかしくはないでしょう。 いえ、単純に上記のSQL文のusers.user_idという表記に違和感を感じた為、質問させて頂きました。 users.id ならuserの一意のIDなんだろうなぁ。とわかるかと思うのですが users.user_idだと頭痛が痛いみたいな表記になってしまってなんだか気になるなと思い 何か理由があれば教えて頂けたらなと思って質問させて頂きました。 ありがとうございます。
退会済みユーザー

退会済みユーザー

2015/08/01 13:01

まあ、基本的に自作のフレームワークではそうしている、というより、そうしないといけないフレームワークにしています。 フィールド名だけでもどのテーブルを参照するのかがわかるようにしていますね。モデルを実装するときに、プロパティとフィールド名は共通の表記にすることでますを防ぐという目的もあってそうしています。
退会済みユーザー

退会済みユーザー

2015/08/01 13:05 編集

追記 cakephpは確かid出なくてはいけないとかあったはず…設計思想の問題でしょうね。
nanndemoiikara

2015/08/01 14:40 編集

ご回答ありがとうございます。 > フィールド名だけでもどのテーブルを参照するのかがわかるようにしていますね。 これが理由で、postsにuser_idというカラムを使用するというのはわかるのですが。。。 例えばusersテーブルをAS句でAに変更して A.idにしたとして A.post_idだ!参照するテーブルはpostsだな!とはなると思いますが A.user_idだ!参照するテーブルはpostsだな!とはならないと思います。 > モデルを実装するときに、プロパティとフィールド名は共通の表記にすることでますを防ぐという目的もあってそうしています。 ? プロパティとはPHPのメンバ変数の事ですか? DBから取得した時に$data->idとなっているのが気に入らないならSQL文のAS句でuser_idにしてしまえば良いだけではないかなとも思いますが。 色々な設計思想があるんですね。
guest

0

ちなみに、users, postsはそれぞれ何件で、遅いというのは具体的に何秒なのでしょうか?

SQLは単にこれでいいのではないかと思います。

SQL

1SELECT * 2 , (SELECT Count(*) 3 FROM posts 4 WHERE posts.user_id = users.id) cnt 5FROM users 6ORDER BY cnt DESC

そこはさておき、このような検索だったら、postsのuser_idにインデックスをつけるべきです。絶対に。
それはやっていますか?やっていないから遅い気がするのですが…。違うのかな。

あと、usersの件数が多いようなら、*など使わずに実際に使用するカラムだけを指定すべきです。それだけで読み込みバイト数が大幅に削減でき、速度改善につながります。

投稿2015/08/01 09:01

miu_ras

総合スコア902

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

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

nanndemoiikara

2015/08/01 09:38

usersだけではなく、postsのcountもINDEXの値の方が、速くなる 可 能 性 がありますね。
miu_ras

2015/08/01 10:12

>usersだけではなく、postsのcountもINDEXの値の方が、速くなる 可 能 性 がありますね。 usersテーブルへのアクセスは全く変わらないでしょう。一方でpostsテーブルへのアクセスは全く行わなくてよくなります。たぶん…。 レコード件数がわかればいいので、インデックスアクセスを行いインデックスにあった件数を数えるだけで済むからです。論理的にはそうですよね。 そして私の知る限りでは、OracleやDB2ではそのようになっています。この程度の工夫はMySQLでも実装されていてもいいと思いますが、もしかしたらMySQLではダメかもしれません。
vc3000

2015/08/01 12:34

手元ではusers、postsともに10万行のデータで、私の元のSQLがMySQLで1.26秒、Postgresで0.8秒かかっています。 今実際にこのSQLが問題になっているのではなくて、一般的にこの形状の(各行ごとの集計が必要になる)SQLは遅くなりがちなので、画面表示で使う場合はどうするのが普通なのだろうと疑問に思っていました。背景を説明不足ですみませんでした。 教えていただいたSQLでもレスポンスタイムの要件を満たせない場合は、あらかじめ件数を集計して持っておくしかないですよね(非正規化というのでしょうか?)
nanndemoiikara

2015/08/01 14:48

10万行ですか。。。 > 教えていただいたSQLでもレスポンスタイムの要件を満たせない場合は、あらかじめ件数を集計して持っておくしかないですよね 他の方もおっしゃっている用にSQL文だけでなくINDEX等の構造部分を見直してみてはいかがでしょうか? ただ、集計して持っておく速度には劣ってしまうとおもいますが。。。
miu_ras

2015/08/01 16:24

10万行で1秒ですか…。1秒は遅すぎに感じます。 DBサーバに使用しているパソコンもしくはサーバの性能にもよるとは思いますけど、3年以内発売の10万円程度のデスクトップパソコン程度の性能で、かつその程度の処理内容であれば、0.1秒以内に処理出来ると思いますけど…。 >教えていただいたSQLでもレスポンスタイムの要件を満たせない場合は、 >あらかじめ件数を集計して持っておくしかないですよね そうですね。最終手段として十分にありです。また、リアルタイムに集計せずとも、1日1~3回とかバッチで集計しても十分だと思います。 ただ、この件に限らないことですが「しかない」という思い込み・決め付け思考はやめたほうがいいと思います。対応方法の可能性を狭めてしまうので。 ただその前にインデックスです。postsのuser_idにインデックスは付いているのでしょうか?もしもインデックスがないのならSQLを変更する前にインデックスをつけましょう。それだけですべてが解決するかもしれません。
vc3000

2015/08/03 22:35

ありがとうございます。インデックスは付いています。 環境はさくらVPS 512MB、1コアなので、スペックは低いと思います。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問