mysqlリクエストを最適化する方法は？

debugcn 投稿 Dev

TomasBalčiūnas

mysqlリクエストに問題があります。現在、約700〜1300msで動作します（間違いなく遅すぎます）。テーブルには約100万行あります。

Select * 
  from `numbers` 
 where `code` = ? 
   and `id` not in (?) 
   and `open` = ? 
   and `views` >= ? 
   and `last_visit` >= ? 
   and `last_visit` <= ? 
 order 
    by `views` desc
 limit 24

田畑：

"id"   => mediumint, primary, unique
"code" => smallint, not unique, unsigned, not null
"open" => tinyint, unsigned, not null
"views" => smallint, unsigned, not null

このリクエストでパフォーマンスを向上させるにはどうすればよいですか？または、キャッシュ結果など、他の何かを試してみませんか？ありがとう！

ビルカーウィン

テストテーブルを作成し、100万行で埋めました。ランダムデータを使用しました。

これは、テーブルにインデックスが定義されていないクエリのEXPLAINです。インデックスがあるとは言わなかったので、インデックスがないと思いました。

           id: 1
  select_type: SIMPLE
        table: numbers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 523516
     filtered: 0.04
        Extra: Using where; Using filesort

調査された推定行は、100万行の約50％です。また、「ファイルソートの使用」で示されているように、結果をソートする必要があります。これらは両方とも、クエリのコストが高くなる原因になります。

次のインデックスを作成しました。

alter table numbers add key bk1 (code,open,views,last_visit);

このインデックスでこれらの列が順序付けられている理由は次のとおりです。

最初に、等式条件で参照される列codeとopen列。
次のviews列は、結果に必要な順序であるため、この順序でインデックスを読み取ると、ファイルソートを実行する必要がなくなります。
Last_visitも役立ちます。おそらく、ストレージエンジンが事前フィルタリングを実行できる可能性があります（これはインデックス条件プッシュダウンと呼ばれます）。

idInnoDBセカンダリインデックスには、列のリストの最後に主キーが暗黙的に追加されるため、インデックスに追加する必要はありません。

これで、同じクエリのEXPLAINは次のようになります。

           id: 1
  select_type: SIMPLE
        table: numbers
   partitions: NULL
         type: range
possible_keys: PRIMARY,bk1
          key: bk1
      key_len: 10
          ref: NULL
         rows: 527
     filtered: 5.56
        Extra: Using where; Backward index scan; Using index

テーブルをインデックス順に読み取ることができるため、ファイルソートを実行していないことに注意してください。

後方インデックススキャンノートは、MySQL8.0の新しいものです。以前のバージョンのMySQLを使用している場合、これが表示されない場合があります。

また、検査される行の数は1000分の1に削減されます。結果は異なる場合があります。

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]