行数が大きく異なるにもかかわらず、クエリがデータを取得するのに同じ時間がかかるのはなぜですか?

Question

VISITS テーブルには 29,938,766 行あり、次のようになります。

＃＃＃等...＃＃＃ ＃＃＃等...＃＃＃ このクエリを実行すると、17 ～ 20 秒かかり、63,514 が返されます (ユーザーの訪問回数は 63,514 回です) リーリー このクエリを実行すると、17 ～ 20 秒かかり、193 が返されます (ユーザーの訪問回数は 193 回です)

USER_ID (INT)	VISITED_IN(日時)
65	2020-08-26 07:57:43
1182	2019-03-15 02:46:48
1564	2015-07-04 10:59:44
73	2021-03-18 00:25:08
3791	2017-10-17 12:22:45
51	2022-05-02 19:11:09
917	2017-11-20 15:32:06
3	2019-12-29 15:15:51
51	2015-02-08 17:48:30
1531	2020-08-05 08:44:55

リーリー

問題は、ユーザーの訪問回数が 3、50、70、または 1,000,000 回のみであっても、29,938,766 行のクエリには常に 17 ～ 20 秒かかることです。

問題は、すべての行をループしているためだと思いますか?

2 番目のクエリは最初のクエリよりも高速である必要があります。行数によって異なります。ただし、両方のクエリにかかる時間は同じです。

この問題を回避するための提案はありますか?

テーブル構造

---

更新: これが新しい推奨シナリオです:

---

ユーザーが自分または他の人のプロフィールにアクセスすると、プロフィールの訪問数を確認し、この方法で訪問をフィルタリングできます。 リーリー さらに、このコマンドを毎日実行する定期的なイベントを作成します。

リーリー

また、VISITS テーブルに新しい行を追加するときは、必ず VISITS 列をインクリメントします。

ああああ

Answer 1

リーリー

は、あなたが言及したすべての SELECTs を高速化します。インデックスの大部分をスキャンする必要がありますが、「テーブル全体をスキャン」する必要はありません。

DELETE `INDEX(visited_in) が必要です。ただし、十分な頻度で実行しないと、問題が発生する可能性があります。これは、一度に数千行を削除すると問題が発生する可能性があるためです。少なくとも 1 時間ごとに削除操作を実行することを検討してください。

テーブルが非常に大きい場合などは、「時系列」パーティショニングの使用を検討してください。 DROP PARTITION を使用すると、速度が 速くなります。 ＃＃＃＃＃＃パーティション＃＃＃＃＃＃＃＃＃ どのキャッシュ サービスでも古いカウントが提供されますが、場合によってはその方が高速です。 「誰かがページを開くたびにデータベースにアクセスできます」が、クエリが十分に効率的である場合に限ります。インデックスを作成します。 あなたの別の質問に対する私の

回答

では、サマリー テーブルがどのようにして処理をさらに高速化できるかについて説明しました。ただし、「過去 N 日間」は午前 0 時から午前 0 時まで測定されると想定しています。現在のクエリは

NOW() - INTERVAL N DAY

です。これは、真夜中よりも実装が複雑です。 「過去 N 日間」の意味を変更しますか?

(インデックスの基本...) インデックスの重要な理由は、特定の列に基づいて行を迅速に検索できることです。

INDEX

は、行にマップされたキーのリストです。

UNIQUE INDEX
• は、INDEX に一意性制約を加えたものです。つまり、インデックス内の 2 つの行が同じ値を持つことはありません。
• UNIQUE PRIMARY KEY は、テーブル内の各行を一意に識別するために指定された一意のインデックスです。
「キー」と「インデックス」は同義語です。
• インデックス (MySQL の InnoDB エンジン内) は BTree (実際には B ツリー、Wikipedia を参照) として実装されます。 PK の場合、残りの列には PK 値が設定されます。 「セカンダリ」キーの場合、BTree の「値」部分は PK 列です。
どのインデックスにも 1 つ以上の列を含めることができます (「複合」と呼ばれます)

INDEX(姓)

一意である可能性は低い

INDEX(lastname,firstname)

は依然として一意である可能性は低いですが、「複合」です。