MySQL データベースのクエリには一定の時間がかかります

Question

24,000 行を含む大規模なメッセージ データベースがあります。

行 0 ～ 24 を表示します (合計 2455455 行、クエリには 0.0006 秒かかります)。 

メッセージがあるため、会話をより速く読み込む必要があります。会話の少ないユーザーの場合、読み込みは次のようになります (ユーザーの会話数は 3.2k):

行 0 ～ 24 を表示します (合計 3266 行、クエリには 0.0345 秒かかります) [id: 5009666... - 4375619...]。 

会話が多いユーザー (会話数が 40,000 件のユーザー) の読み込みが遅くなります:

行 0 ～ 24 を表示します (合計 40296 行、クエリには 5.1763 秒かかります) [id: 5021561... - 5015545...]。 

これらの列にはインデックス キーを使用します:

id、to_id、from_id、時刻、参照時間

データベーステーブル:

CREATE TABLE `messages` (
  `id` int(255) NOT NULL、
  `to_id` int(20) NOT NULL、
  `from_id` int(20) NOT NULL、
  「メッセージ」の長いテキストは NULL ではありません、
  `time` double NOT NULL、
  `seen` int(2) NOT NULL、
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `messages` (`id`、`to_id`、`from_id`、`message`、`time`、`seen`) 値
(2、6001、2、「こんにちは」、1587581995.5222、1);


ALTER TABLE `メッセージ`
  主キー (`id`) を追加します。
  ADD KEY `time_idx` (`time`)、
  ADD KEY `from_idx` (`from_id`)、
  ADD KEY `to_idx` (`to_id`)、
  ADD KEY `seenx` (`seen`)、
  ADD KEY `idx` (`id`);


ALTER TABLE `メッセージ`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT、AUTO_INCREMENT=5021570;
COMMIT;

次のクエリを使用します:

SELECT
  *
から
  メッセージ、
  (
    選択する
      MAX(id) として lastid
    から
      メッセージ
    どこ
      (
        messages.to_id = '1' -- 比較する ID (ログインしているユーザーの ID)
        ORmessages.from_id = '1' -- 比較する ID (ログインしているユーザーの ID)
      ）
    グループ化
      CONCAT(
        LEAST(messages.to_id,messages.from_id),
        「。」、
        GREATEST(messages.to_id,messages.from_id)
      ）
  ) 会話として
どこ
  id = 会話.lastid
注文方法
  messages.id DESC

データベース構造を再作成する必要がある場合、会話が多いユーザーの処理を高速化する方法がわかりません。

Answer 1

うーん、テーブルにインデックスを追加してみてはいかがでしょうか: https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql -tables#:~:text=インデックスの作成&text=インデックスを作成するステートメント。インデックスは個別である必要があります。 クエリを実行する行に基づいて複合インデックスを追加してください。

これによってクエリ時間が改善されない場合は、クエリを改善する必要があります。

Answer 2

＃＃＃知らせ：＃＃＃

OR の代わりに UNION を使用します (以下を参照)

• 冗長なキーがあります。 PRIMARY KEYはキーなのでKEY(id)
を削除します。
• すべての列にインデックスをやみくもに作成しないでください。代わりに、クエリを使用して、どのインデックス (特に複合インデックス) が実際に役立つかを判断してください。
• GROUP BY と ORDER BY では、CONCAT は不要であり、逆効果になる可能性があります。
• INT 型の場合、長さフィールドは無視されます。値の制限は 20 億です。 (0 か 1 しかないと仮定すると、これはやりすぎでしょうか?)
• 新しい構文 JOIN..ON を使用します。
• true/false のみが表示される場合は、そのインデックスを削除します。 (または、役に立つと思われるクエリを見せてください。)
CONCAT-LEAST-GREATEST - これは「friends_id」を構築するためのものですか?おそらく本当に必要なのは「conversation_id」なのでしょうか？現在、2 人のユーザーが複数の「会話」を行うことはできませんよね。

必要に応じて、conversation_id の新しい列を作成します。 (現時点では、GROUP BY は非効率的です。) 次のコードにより、そのような ID が不要になります。

リーリー

そして、これらの「カバー」インデックスと「複合」インデックスを用意します:

リーリー

新しいインデックスでこれら 2 つのインデックスの他のすべてのタスクを処理できるため、KEY(to_id)、KEY(from_id) を削除します。

これは同じ効果があると思いますが、実行速度が速くなります。

それらを組み合わせる:

リーリー

(これら 2 つのインデックスを追加します)

＃＃＃＃＃＃もっと＃＃＃＃＃＃

私はかつて、conversation_id の必要性を UNION DISTINCT で置き換えることができると誤って考えていました。しかしそれは真実ではありません。すぐにいくつかの解決策が見つかりました:

conversation_id を追加し、重複排除に使用します。 (また、UNION DISTINCT を UNION ALL に変更して、結果を変えることなくクエリをわずかに高速化しました。)

(from_id、to_id、latestid) を含む一時テーブルにクエリ結果を入力し、CONCAT-LEAST-GREATEST テクニックを使用して会話を重複排除し、最後にメッセージ テーブルと JOIN して追加の列を取得します。

この一時テーブル テクノロジにより、書き込みとデバッグが容易になります。私の 3 番目の提案は、これらの部分を 3 レベルの深さでネストされた Select ステートメントを含む 1 つの (判読できない) クエリに結合することです。