データベースのランダム読み出しは要注意(翻訳)

概要 原著者の許諾を得て翻訳・公開いたします。 英語記事: Say No to Randos (in Your Database) 原文公開日: 2017/11/29 著者: Richard Schneeman データベースのランダム読み出しは要注意(翻訳) ORM(Object-Relation Mapper、またはObject-Relational Mapper)を初めて使ったとき、「ORMにrandom()メソッドがないのはどうしてなんだろうか?」と不思議に思ったものでした。こんなメソッドなら楽勝で追加できそうなものです。データベースのレコードをランダムに取り出したい理由はいろいろ考えられますが、順序をランダムにしたいレコード数がよほど少ないときでもない限り、SQLのORDER BY RANDOM()でやるべきではありません。本記事では、一見シンプルなSQL演算子によってどれほどパフォーマンスが低下するか、およびいくつかの修正方法について調査したいと思います。 ご存じの方もいらっしゃるかと思いますが、私はオープンソースを支援するのに最適なCodeTriageというサイトを運営しており、このサイトのデータベースパフォーマンス改善についていくつか記事を書きました(訳注: 以下はいずれも仮の日本語タイトルです)。 Rack Mini Profilerで遅いクエリを検出する HerokuのExpensive Query Dashboardで遅いクエリを検出する DBサーバーの負荷を80%軽減した話 最近私はheroku pg:outliersコマンドを実行して最適化後の様子を調べていたところ、RANDOM()を含むたった2つのクエリがデータベース時間の32%を占めていたことに気づいて驚きました。 $ heroku pg:outliers 14:52:35.890252 | 19.9% | 186,846 | 02:38:39.448613 | SELECT “repos”.* FROM “repos” WHERE (repos.id not in (?,?)) ORDER BY random() LIMIT $1 08:59:35.017667 | 12.1% | 2,532,339 | 00:01:13.506894 | SELECT “users”.* FROM “users” WHERE (“users”.”github_access_token” IS NOT NULL) ORDER BY RANDOM() LIMIT $1 これほど遅くなった原因を理解するために、最初のクエリをちょっと見てみましょう。 SELECT “repos”.* FROM “repos” WHERE (repos.id not in (?,?)) ORDER BY random() LIMIT $1 このクエリは週に一度実行され、オープンソースのリポジトリにアカウントを持っているがまだサイトに登録していないユーザーに、サイトに登録してissueを「トリアージ」するようユーザーに勧めます。ユーザーに送信されるメールには、ランダムなリポジトリを含む3つのおすすめリポジトリが含まれています。最終的にランダムな結果を得られたのですからRANDOM()の使い方としてはうまくいっているように思えますが、どこがまずいのでしょうか? PostgreSQLへのリクエストORDER BY random() LIMIT 1ではレコードを1件しか要求していませんが、このクエリが行っているのはそれだけではありません。その1件を返す前にすべてのレコードを並べ替えているのです。 このクエリはArray#sampleのようなものだろうと考える人もいるかもしれませんが、実際にやっているのはArray#shuffle.firstです。私がこのコードを書いたときは、データベースに登録されているリポジトリがほんの数件どまりだったので、かなり高速でした。しかし今やリポジトリ数は2761件に増加しています。そのため、このクエリを実行するたびにデータベースはリポジトリごとに多数の行を読み込み、CPUパワーをリポジトリの並べ替えに使わなければなりません。 もうひとつのクエリでは、同じことがusersテーブルで起きているのがわかります。 => EXPLAIN ANALYZE SELECT “users”.* FROM “users” \n WHERE (“users”.”github_access_token” IS NOT NULL) \n ORDER BY RANDOM() LIMIT 1; QUERY PLAN ———————————————————————————————————————– Limit (cost=1471.00..1471.01 rows=1 width=2098) (actual time=12.747..12.748 rows=1 loops=1) -> Sort (cost=1471.00..1475.24 rows=8464 width=2098) (actual time=12.745..12.745 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 26kB -> Seq Scan on users (cost=0.00..1462.54 rows=8464 width=2098) (actual time=0.013..7.327 rows=8726 loops=1) Filter: (github_access_token IS NOT NULL) Rows Removed by Filter: 13510 Total runtime: 12.811 ms (8 rows) 比較的小さなクエリを1つ実行するたびに13ms近くかかっています。 ではRANDOM()が犯人だとしたら、どう修正すればよいのでしょうか?これは驚くほど難しい質問です。アプリやデータへのアクセス方法によって大きく異なります。 … Continue reading データベースのランダム読み出しは要注意(翻訳)