数百万のエントリを含む大きなテーブルがあります。完全なカウントは非常に遅いです。以下のコードを参照してください。これは MySQL InnoDB テーブルでは一般的なことでしょうか?このプロセスを高速化する方法はありませんか? クエリ キャッシュを使用しても、依然として「遅い」です。 また、280 万のエントリを持つ「通信」テーブルのカウントが、450 万のエントリを持つ「トランザクション」テーブルのカウントよりも遅い理由も知りたいです。
where 句を使用した方が速いことはわかっています。パフォーマンスが悪いのが普通なのかどうか疑問に思っています。
Amazon RDS MySQL 5.7 および m4.xlarge (4 CPU、16 GB RAM、500 GB ストレージ) を使用します。より多くの CPU と RAM を備えたより大きなインスタンスも試しましたが、クエリ時間に大きな変化はありませんでした。
リーリー
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
これは、Multiple Version Concurrency Control (MVCC) を使用してサポートされます。
InnoDB を使用すると、データ行の読み取りと書き込みを同時に行う他のクライアントをブロックすることなく、クエリをトランザクション内で分離できます。これらの同時更新は、トランザクションのデータ表示には影響しません。
しかし、カウント中に多くの行が追加または削除されることを考慮すると、テーブル内の行数はどれくらいになるでしょうか?答えは曖昧です。
トランザクションは、トランザクションの開始後に作成された行バージョンを「確認」できないようにする必要があります。同様に、他の誰かが行の削除を要求した場合でも、トランザクションは行をカウントする必要がありますが、その要求はトランザクションの開始後に行われます。
答えは、
SELECT COUNT(*)または複数の行のチェックが必要な他の種類のクエリを実行するとき、InnoDB は現在の行を確認するために 各 行にアクセスする必要があるということです。バージョンはデータベースのトランザクション ビューに表示され、表示されている間カウントされます。トランザクションや同時更新をサポートしていないテーブル (MyISAM など) では、ストレージ エンジンはテーブルのメタデータとして合計行数を保持します。ストレージ エンジンは、複数のスレッドによる行の同時更新をサポートできないため、行の合計はそれほど曖昧ではありません。したがって、MyISAM テーブルから SELECT COUNT(*) をリクエストすると、メモリ内の行数のみが返されます (ただし、これは SELECT COUNT(*) を実行する場合には役に立ちません) WHERE を使用します。句 by 一部の条件は行の一部をカウントするため、この場合は実際にそれらをカウントする必要があります)。
一般に、ほとんどの人は、InnoDB の同時更新のサポートにはそれだけの価値があると考えており、
SELECT COUNT(*)の最適化を犠牲にすることをいとわないと考えています。ビルが言ったこと以外は...
最小インデックス
InnoDB は、
リーリーCOUNT(*)を実行するために「最小の」インデックスを選択します。すべてのcommunicationのインデックスがtransactionの最小インデックスより大きい可能性があるため、時間差が生じます。インデックスのサイズを決定するときは、セカンダリ インデックスとともにPRIMARY KEY列を含めます:サイズを測定する場合、
PRIMARY KEYにはテーブルのすべての列が (クラスタリングにより) 含まれているため、大きくなります。INDEX(フラグ)は「5バイト」です。INDEX(name)平均して数十バイトになる可能性があります。SELECT COUNT(*)は、明示的にINDEX(flag)を選択します。どうやら
Transactionsには「小さい」インデックスがありますが、Communicationsにはありません。TEXT/BLOG列は「ログに記録されない」状態で保存されることがあります。したがって、PK インデックスのサイズには含まれません。クエリキャッシュ
「クエリ キャッシュ」がオンになっている場合、クエリ の 2 回目の実行は最初の実行よりも はるかに高速になる可能性があります。ただし、これはテーブルが変更されていない場合にのみ発生します。テーブルを変更すると、そのテーブルのすべての QC エントリが無効になるため、QC が運用システムで役立つことはほとんどありません。 「より速い」とは、1.44 秒ではなく、約 0.001 秒を意味します。
1m38s と 1.44s の違いは、buffer_pool (InnoDB の一般キャッシュ領域) にキャッシュされている内容に起因する可能性があります。最初の実行ではおそらく RAM 内に「最小限の」インデックスが見つからなかったため、大量の I/O が実行され、そのインデックスの 450 万行すべてを取得するのに 98 秒かかりました。 2 回目の実行では、すべてのデータがbuffer_pool にキャッシュされていることがわかり、CPU 速度 (I/O なし) で実行されたため、はるかに高速になりました。
十分です
この場合、
COUNT(*)を行う必要性には根本的に疑問があります。あたかも有効数字 2 桁で「十分」であるかのように「280 万エントリ」と表現していることに注目してください。 UI 上でユーザーにカウントを表示できれば「十分」ではないでしょうか?その場合、パフォーマンスを向上させる 1 つの解決策は、1 日 1 回カウントしてどこかに保存することです。これにより、「十分な」値にすぐにアクセスできるようになります。他にもテクノロジーはあります。 1 つは、アクティビティ タグまたは何らかの形式の概要テーブルを使用してカウンターを更新し続けることです。
ハードウェアを投入する
ハードウェアを変更しても効果がないことがわかりました。
SELECT COUNT(*)..(RAM が少なすぎると 2 番目の実行が非常に遅くなります) まで、「小さい」インデックス全体をbuffer_pool に入れることができます。)