ヒットによるコンテンツの人気のクエリの最適化

database-design mysql performance php sql
ヒットによるコンテンツの人気のクエリの最適化
 +
私はこれを検索しましたが、何も思いついていません。誰かが私を正しい方向に向けることができるかもしれません。 +私は、MySQLデータベースに多くのコンテンツを含むWebサイトと、ヒットによって最も人気のあるコンテンツをロードするPHPスクリプトを持っています。 これは、ヒットした各コンテンツをアクセス時間とともにテーブルに記録することにより行われます。 次に、選択クエリを実行して、過去24時間、7日間、または最大30日間で最も人気のあるコンテンツを見つけます。 cronjobは、ログテーブル内の30日より古いものをすべて削除します。

私が今直面している問題は、Webサイトが大きくなり、ログテーブルのヒットレコードが1m以上になり、選択クエリが本当に遅くなることです(10〜20秒)。 最初は、コンテンツのタイトル、URLなどを取得するためにクエリに含まれていた結合が問題でしたが、 しかし、テストでは結合を削除してもクエリが高速化されないので、私は確信していません。

だから私の質問は、この種の人気の保存/選択を行うベストプラクティスは何ですか? これに適したオープンソーススクリプトはありますか? または、あなたは何を提案しますか?

テーブルスキーム

__
「ポピュラリティ」ヒットログテーブル+ nid | insert_time | tid + nid:コンテンツのノードID + insert_time:タイムスタンプ(2011-06-02 04:08:45)+ tid:用語/カテゴリID

「ノード」コンテンツテーブル+ nid |タイトル|ステータス| (さらにありますが、これらは重要なものです)+ nid:ノードID +タイトル:コンテンツタイトル+ステータス:公開されたコンテンツです(0 = false、1 = true)
__

  • SQL *

SELECT node.nid, node.title, COUNT(popularity.nid) AS count
FROM `node` INNER JOIN `popularity` USING (nid)
WHERE node.status = 1
  AND  popularity.insert_time >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
GROUP BY popularity.nid
ORDER BY count DESC
LIMIT 10;

  4  0


ベストアンサー

私たちはちょうど同じような状況に出くわしたばかりであり、これが私たちがそれを回避した方法です。 何が起こったのか正確に「時間」を気にせず、それが起こった日のみを決定しました。 次に、これを行いました。

  1. すべてのレコードには「合計ヒット」レコードがあり、毎回増分されます
    何かが起こる

  2. ログテーブルには、これらの「合計ヒット数」が1日あたり1レコードごとに記録されます(cron
    job)

  3. このログテーブルで指定された2つの日付の差を選択すると、
    2つの日付の間の「ヒット」を非常に迅速に推測できます。

この利点は、ログテーブルのサイズがNumRecords * NumDaysと同じ大きさであり、この場合は非常に小さいことです。 また、このログテーブルに対するクエリは非常に高速です。

短所は、時刻ごとにヒットを推測する能力を失うことですが、これが必要ない場合は検討する価値があるかもしれません。

2


実際には、さらに先に解決する2つの問題があります。

まだ実行されていないが、必要以上に早い可能性があるのは、統計表内にスループットを挿入することです。

質問で概説したもう1つは、実際に統計を使用しています。

” ” ‘

入力スループットから始めましょう。

まず、そうする場合、キャッシングを使用する可能性のあるページの統計を追跡しないでください。 空のjavascriptまたは1ピクセルの画像として自分自身を宣伝するphpスクリプトを使用し、追跡するページに後者を含めます。 そうすることで、サイトの残りのコンテンツを簡単にキャッシュできます。

電話会社では、電話の請求に関連する実際の挿入を行うのではなく、物事はメモリに置かれ、ディスクと定期的に同期されます。 そうすることで、ハードドライブを満足させながら、巨大なスループットを管理できます。

同様に処理を進めるには、アトミック操作とメモリ内ストレージが必要です。 最初の部分を行うためのいくつかのmemcacheベースの擬似コードは次のとおりです…​

各ページには、Memcache変数が必要です。 Memcacheでは、http://php.net/manual/en/memcached.increment.php [increment()]はアトミックですが、add()、set()などはアトミックではありません。 したがって、並行プロセスが同時に同じページを追加する場合、ヒットをミスカウントしないように注意する必要があります。

$ns = $memcache->get('stats-namespace');
while (!$memcache->increment("stats-$ns-$page_id")) {
  $memcache->add("stats-$ns-$page_id", 0, 1800); // garbage collect in 30 minutes
  $db->upsert('needs_stats_refresh', array($ns, $page_id)); // engine = memory
}

定期的に、たとえば5分ごとに(それに応じてタイムアウトを構成します)、同時プロセスが互いに影響したり既存のヒットカウントに影響したりすることなく、これらすべてをデータベースに同期する必要があります。 このため、何かを行う前に名前空間をインクリメントし(これにより、すべての意図と目的のために既存のデータをロックします)、必要に応じて以前の名前空間を参照する既存のプロセスが終了するように少しスリープします。

$ns = $memcache->get('stats-namespace');
$memcache->increment('stats-namespace');
sleep(60); // allow concurrent page loads to finish

それが完了すると、ページIDを安全にループし、それに応じて統計を更新し、needs_stats_refreshテーブルをクリーンアップできます。 後者は、page_id int pkey、ns_id int)の2つのフィールドのみを必要とします。 ただし、スクリプトから実行される単純な選択、挿入、更新、および削除の文よりも少し多くあります。

別の回答者が示唆したように、目的のために中間の統計を維持することは非常に適切です:個々のヒットではなくヒットのバッチを保存します。 せいぜい、1時間ごとの統計または15分ごとの統計が必要だと想定しているので、15分ごとにバッチロードされる小計を処理しても問題ありません。

さらに重要なのは、これらの合計を使用して投稿を注文しているため、集計された合計を保存し、後者にインデックスを付けることです。 (さらに先に進みます。)

合計を維持する1つの方法は、統計テーブルへの挿入または更新時に、必要に応じて統計合計を調整するトリガーを追加することです。

その際、デッドロックに特に注意してください。 2回の「$ ns」の実行がそれぞれの統計を混合することはありませんが、2つ以上のプロセスが上記の「$ nsの増分」ステップを同時に起動し、その後更新しようとするステートメントを発行する可能性があります同時にカウントします。 advisory lockを取得することは、これに関連する問題を回避するための最も簡単で安全かつ最速の方法です。

アドバイザリロックを使用すると仮定すると、文を更新する際にtotal = total + subtotalを使用してもまったく問題ありません。

ロックのトピックについては、合計を更新するには、影響を受ける各行で排他ロックが必要になることに注意してください。 あなたは彼らによって注文しているので、それらをすべて一度に処理したくないのは、それが長期間にわたって排他的ロックを維持することを意味するかもしれないからです。 ここで最も簡単なのは、より小さなバッチ(たとえば1000)の統計への挿入を処理し、それぞれにコミットが続くことです。

中間統計(毎月、毎週)の場合、いくつかのブールフィールド(MySQLのビットまたはtinyint)を統計テーブルに追加します。 月ごと、週ごと、日ごとの統計などでカウントするかどうかをこれらの各ストアに設定します。 stat_totalsテーブル内の該当する合計を増減させるように、トリガーも配置します。

最後に、実際のカウントを保存する場所について考えてください。 インデックス付きフィールドである必要があり、後者は大幅に更新される予定です。 通常、(はるかに大きい)デッド行でページテーブルが乱雑になるのを避けるために、ページテーブルではなく、独自のテーブルに保存する必要があります。

” ” ‘

上記のすべてを実行したとすると、最終的なクエリは次のようになります。

select p.*
from pages p join stat_totals s using (page_id)
order by s.weekly_total desc limit 10

weekly_totalのインデックスを使用すると、かなり高速になります。

最後に、最も明白なことを忘れないでください。これらの同じ合計/月間/週ごとなどのクエリを繰り返し実行する場合、結果もmemcacheに配置する必要があります。

1


インデックスを追加してSQLを調整できますが、ここでの実際の解決策は結果をキャッシュすることです。

実際には、過去7/30日間のトラフィックを1日1回計算するだけで済みます。

そして、あなたは過去24時間を1時間ごとにできますか?

5分に1回実行したとしても、すべてのユーザーのすべてのヒットに対して(高価な)クエリを実行するよりも大幅に節約できます。

0


  • RRDtool *

多くのツール/システムは独自のロギングとログ集約を構築しませんが、時系列データを効率的に処理するためにhttp://oss.oetiker.ch/rrdtool/[RRDtool](ラウンドロビンデータベースツール)を使用します。 RRDtoolsには強力なグラフ作成サブシステムも付属しており、(http://en.wikipedia.org/wiki/RRDtool [Wikipedia]によると)PHPおよび他の言語のバインディングがあります。

あなたの質問から、私はあなたが特別で凝った分析を必要としないと思います、そしてRRDtoolはあなた自身のシステムを実装して調整する必要なしにあなたが必要とすることを効率的にします。

0


バックグラウンドで、たとえば詐欺のような「集計」を行うことができます。 役立つ可能性のあるいくつかの提案(順不同):

*1. 1時間ごとの結果を含むテーブルを作成します。*これは、必要な統計を作成できることを意味しますが、データ量を(24 * 7 * 4 = 1ページあたり月あたり約672レコード)に減らします。

あなたのテーブルはこれに沿ったどこかにあります:

hourly_results (
nid integer,
start_time datetime,
amount integer
)

それらを集計テーブルに解析した後、多かれ少なかれそれらを削除できます。

  • 2。結果のキャッシュ(memcache、apc)を使用する*結果は(http://www.webdeveloperjuice.com/2010/01/ 25 / 10-baby-steps-to-install-memcached-server-and-access-it-with-php / [memcache database](これもcronjobから更新できます)、http://www.phpを使用します.net / manual / en / function.apc-store.php [apc user cache](cronjobからは更新できません)またはhttp://christian.roy.name/blog/re-using-serialized-を使用しますメモリが不足している場合にオブジェクト/結果をシリアル化するphp-data [ファイルキャッシュ]。

3. データベースの最適化 10秒は長い時間です。 データベースで何が起こっているかを調べてください。 メモリ不足ですか? さらにインデックスが必要ですか?

0


タイトルとURLをコピーしました