MySQL統計データテーブルの設計方法

キャッシュデータテーブルは統計データを収集するときによく使用されるため、統計データとも呼ばれます。たとえば、従業員と部門のデータテーブルの場合、部門に何人の従業員がいるかを照会する必要がある場合があります。これを実現するには 3 つの方法があります。

部門の従業員数フィールドを追加します。従業員が追加、変更、または削除されるたびに、従業員数を同期的に更新する必要があります (従業員が部門を変更する場合は、複数の部門の従業員数を更新する必要があります)。この方法はリアルタイムのパフォーマンスを保証できますが、非常に非効率的です。操作頻度が低い場合は問題ありませんが、操作頻度が高い場合は、その都度 2 つのテーブルを操作する必要があり、同時に業務コードを処理する必要があり、統計業務と通常業務が深く結びついてしまいます。
クエリが実行されるたびに、従業員テーブルから SUM 関数が実行され、部門内の従業員数が取得されます。この方法では、埋もれたポイントを回避できますが、従業員データテーブルを毎回合計する必要があり、従業員データの量が多い場合は非効率的になります。
従業員テーブルから各部門の人数を定期的に集計する新しい統計テーブルを作成します。固定時間にデータを抽出するこの方法では、リアルタイム性はある程度犠牲になりますが、コードの結合度が減ります。部門がそれほど多くないため、このテーブルのサイズは予測可能であり、データアクセスの効率も向上します。この方法はキャッシュされたデータテーブルと呼ばれます。

Nuggets のモバイルパーソナルセンターを例にとると、各ユーザーのフォロワー数、フォロワー数、Nuggets パワー値を表示するには、各クエリごとに SUM を実行することはできません。つまり、複数のテーブルの合計操作を実行する必要があり、非常に非効率的になります。また、Nuggets パワー値の計算には、より複雑な計算方法 (記事の閲覧数やいいね数に関連) が含まれます。したがって、一般的なテーブル設計を推測することができ、ユーザーの個人ホームページ情報を照会するときに、すべてのデータをこのテーブルから読み取ることができます。

t_user_summay を作成します (
  id INT 主キー、
  ユーザーID BIGINT(20)
  フォーカスされたユーザー数 INT、
  followed_user_cnt INT、
  ユーザー値 INT、
  ユーザーレベル ENUM('Lv1'、'Lv2'、...、'Lv8')、
  created_time 日付時刻、
  updated_time 日付時刻、
);

リアルタイム更新は必要ですか?

実際のアプリケーションでは、統計表を更新する方法は 2 つあります。1 つはリアルタイム更新、もう 1 つはデータの定期的な再構築です。どちらの方法にも長所と短所があります。リアルタイム更新ではクエリデータの即時性が保証されますが、パフォーマンスが犠牲になり、コードの埋め込みが必要になります。また、データの更新が不定期であるため、断片化が発生する可能性があります。データの定期的な再構築は、リアルタイムのパフォーマンスを犠牲にします。データのほとんどが変更されない場合、不要な統計計算につながります。ただし、データが頻繁に変更される場合は、データの定期的な再構築の方が明らかに効率的であり、ポイントが埋もれる状況を回避できます。もちろん、アプリケーションの埋め込みを回避するにはトリガーを使用することもできます。//www.jb51.net/article/213062.htm を参照してください。

マテリアライズドビューツール (Flexviews)

MySQL には、データベース binlog からデータを抽出してデータ統計を完成するために使用される Flexviews と呼ばれるオープンソースツールがあります。これはビューに似ていますが、ビューとは異なります。Flexviews によって生成されるデータテーブルは物理テーブルであるため、マテリアライズドビューと呼ばれます。さらに、Flexviews は増分更新と完全更新もサポートします。すべての行の統計を再構築する必要がある状況を回避するために、増分更新を使用することをお勧めします。増分更新では、更新を実行する前にどの行が変更されたかをチェックします。これにより、完全更新よりもパフォーマンスが高くなります。ただし、データの変更を検出するには、データ行の変更ログを記録するビューを導入する必要があります。

カウントテーブル

実際の開発では、記事の閲覧数やいいね数などの操作をカウントする必要があることがよくあります。カウント値を同じテーブルに入れると、更新時に同時実行性の問題が発生する可能性が高くなります。別のカウントテーブルを使用すると、クエリキャッシュの無効化の問題を回避し、より高度なテクニックを有効にできます。たとえば、記事の閲覧数といいね数をカウントするデータテーブルは次のようになります。

テーブル t_article_counter を作成します (
  article_id INT 主キー、
  read_cnt INT UNSIGNED NOT NULL、
  praise_cnt INT UNSIGNED NOT NULL
);

読み取りカウントを更新するときは、MySQL の組み込み増分操作を使用できます。

更新 t_article_counter 
SET read_cnt = read_cnt + 1
ここで、article_id = 1;

この方法では、操作を単一行にして相互に排他的にすることができるため、トランザクションをシリアル化して同時実行の問題を回避できます。ただし、同時リクエストの数には影響します。同時実行性を高めるために、記事に複数のスロットを追加できます。

テーブル t_article_counter を作成します (
  id INT NOT NULL 主キー、
  スロット TINYINT UNSIGNED、
  記事ID INT、
  read_cnt INT UNSIGNED NOT NULL、
  praise_cnt INT UNSIGNED NOT NULL、
  INDEX(記事ID)
);

このとき、データを初期化するためのスロットを 100 個作成し、更新時に次の操作を実行できます。

更新 t_article_counter
SET read_cnt = read_cnt + 1 
ここで、 slot = RAND() * 100 であり、 article_id = 1 です。

記事の合計読み取り数を取得するには、SUM 演算を使用する必要があります。

SUM(read_cnt) を t_article_counter から選択します
ここで、article_id = 1;

この方法は、実際にはスペースを時間と交換し、同時実行性を高めます。

要約する

この記事では、統計データテーブルの設計方法を紹介します。ポイントは業種にあります。更新頻度が低く、データ量が少ないテーブルの場合は、リアルタイム同期や Direct SUM を使用しても問題ありません。更新頻度の高い大規模なデータテーブルの場合は、独立した統計テーブルを使用できます。同時に、同時実行性が高い状況では、統計テーブルは各エンティティに複数のスロットを追加して同時実行性を高めることを検討できます。データを定期的に同期する場合は、Flexviews マテリアライズドビュープラグインを使用することもできます。

上記は、MySQL で統計データテーブルを設計する方法の詳細です。MySQL で統計データテーブルを設計する方法の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: