私たちは MySQL から PGSQL への移行を進めており、1 億行のテーブルを持っています。
両方のシステムがどれだけのスペースを使用しているかを確認しようとしたところ、テーブルについてはそれほど違いはありませんでしたが、インデックスについては大きな違いがあることがわかりました。
MySQL インデックスはテーブル データ自体よりも多くのサイズを占めていましたが、postgres はそれよりもかなり小さいサイズを使用していました。
理由を調べてみたら、MySQLはインデックスを保存するためにB+ツリーを使っていて、postgresは用途B ツリー。
MySQL のインデックスの使用方法は少し異なり、インデックスとともにデータを保存します (そのためサイズが増加します) が、Postgres では保存しません。
さて、質問です:
データベースの観点から B ツリーと B+ ツリーを比較すると、範囲クエリ O(m) + O(logN) に適しているため、B+ ツリーを使用する方が適切です。ここで、m は範囲内にあり、検索は B+ ツリーでは対数になります。
B ツリーでは、範囲クエリの検索は対数的であり、データ ノードのリンク リストの基礎構造がないため、O(N) まで急上昇します。そうは言っても、なぜ postgres は B ツリーを使用するのでしょうか。範囲クエリのパフォーマンスは良好ですか (良好ですが、B ツリーを内部的にどのように処理しますか)。
上記の質問は postgres の観点からのものですが、MySQL の観点から見ると、なぜ postgres よりも多くのストレージを使用するのでしょうか。実際に B+tree を使用することによるパフォーマンス上の利点は何でしょうか。
多くのことを見逃したり誤解したりしている可能性がありますので、ここで私の理解を訂正してください。
リック・ジェームスの質問に答えるための編集
- 私はMySQLにInnoDBエンジンを使用しています
- データを入力した後、インデックスを構築しました - postgresで行ったのと同じ方法です
- インデックスはUNIQUEインデックスではなく、通常のインデックスです
- ランダム挿入はなく、postgres と MySQL の両方で csv ロードを使用し、その後でのみインデックスを作成しました。
- Postgres のインデックスとデータのブロック サイズは両方とも 8KB です。MySQL についてはわかりませんが、変更していないので、デフォルトのはずです。
- 行は大きいとは言えませんが、長さ 200 文字のテキスト フィールドが 4 つ、小数点フィールドが 4 つ、bigint フィールドが 2 つ (数字が 19 個) あります。
- PK は 19 個の数字を持つ bigint 列ですが、これがかさばるかどうかはわかりません。かさばるデータとかさばらないデータをどのような基準で区別すればよいでしょうか。
- MySQL テーブル サイズは 600 MB、Postgres はインデックスを含めて約 310 MB でした。計算が正しければ、サイズは 48% 大きくなります。しかし、テーブル サイズを除いて MySQL のインデックス サイズだけを測定する方法はありますか? そうすると、より良い数値が得られると思います。
- マシン情報: すべてのテーブルとインデックスを収めるのに十分な RAM (256 GB) がありましたが、このルートをトラバースする必要はまったくないと思います。どちらでも目立ったパフォーマンスの違いは見られませんでした。
追加の質問
- 断片化が発生するとしたら、これ以上何もする必要がないと言えるようなデフラグを行う方法はありますか。ちなみに、私は Cent OS を使用しています。
- MySQL で、クラスター化されている主キーを無視してインデックス サイズを測定する方法はありますか。これにより、実際にどのタイプがより多くのサイズを占有しているかを確認できます。
ベストアンサー1
まず第一に、使用していない場合翻訳この質問を閉じて、InnoDBで再構築してから、質問を再度開く必要があるかどうかを確認してください。MyISAMはない好ましいことであり、議論すべきではありません。
どのようにしていたインデックスを構築するMySQL では、明示的または暗黙的にインデックスを構築する方法はいくつかありますが、それによってパッキングの質が上がったり下がったりします。
MySQL: データとインデックスは、以下のB+ツリーに格納されます。16KBブロック。
MySQL:UNIQUEインデックス（ を含むPRIMARY KEY）更新する必要がある行を挿入するときに、UNIQUEインデックスには必然的に多くのブロック分割などが含まれることになります。
MySQL:PRIMARY KEYクラスター化されているデータと一緒にロードされるため、実質的にスペースをまったく占有しません。データを PK 順にロードすると、ブロックの断片化は最小限に抑えられます。
非UNIQUEセカンダリ キーはオンザフライで構築される可能性があり、これにより断片化が発生します。または、テーブルがロードされた後に構築される可能性があり、これにより、より高密度のパッキングが発生します。
セカンダリ キー (UNIQUEまたはそうでないキー)PRIMARY KEYには暗黙的に が含まれます。PK が「大きい」場合、セカンダリ キーは大きくなります。あなたの PK は何ですか? これが「答え」ですか?
理論上、BTreeに完全にランダムに挿入すると、ブロックは約69% 満杯おそらくこれが答えでしょう。MySQL は 45% 大きい (1/69%) のでしょうか?
1 億行の場合、必要なすべてのデータやインデックス ブロックをキャッシュするのに十分な RAM がないため、多くの操作が I/O バウンドになる可能性があります。すべてがキャッシュされている場合、B ツリーと B+ ツリーの違いはあまりありません。完全にキャッシュされていない場合に範囲クエリで何が起こる必要があるかを分析してみましょう。
どちらのタイプのツリーでも、操作はツリーのドリルダウンから始まります。MySQL の場合、1 億行には約 4 レベルの深さの B+ ツリーがあります。3 つの非リーフ ノード (これも 16KB ブロック) はキャッシュされ (まだキャッシュされていない場合)、再利用されます。Postgres でも、このキャッシュはおそらく発生します (Postgres については知りません)。次に、範囲スキャンが開始されます。MySQL では、ブロックの残りを順に調べます (経験則: 1 つのブロックに 100 行)。Postgres でも同じでしょうか?
ブロックの最後では、何か別のことが起きる必要があります。MySQL の場合、次のブロックへのリンクがあります。そのブロック (100 行以上) は、ディスクから取得されます (キャッシュされていない場合)。B ツリーの場合、非リーフ ノードを再度トラバースする必要があります。2 レベル、おそらく 3 レベルがまだキャッシュされています。別の非リーフ ノードをディスクから取得する必要があるのは、1/10K 行だけです (10K = 100*100)。つまり、Postgres は、"コールド" システムであっても、MySQL よりも 1% 多くディスクにアクセスする可能性があります。
一方、行数が非常に多いため、16K ブロックに 1 行または 2 行しか収まらない場合は、私が使用していた「100」は「2」に近くなり、1% はおそらく 50% になります。つまり、大きな行がある場合、これが「答え」になるかもしれません。 それは...ですか？
Postgres のブロックサイズはどれくらいですか?上記の計算の多くは、ブロックとデータの相対的なサイズに依存することに注意してください。これが答えでしょうか?
結論：4 つの可能な回答を示しました。これらのそれぞれが当てはまるかどうかを確認または反論するために、質問を補足しますか? (セカンダリ インデックスの存在、大きな PK、セカンダリ インデックスの非効率的な構築、大きな行、ブロック サイズなど)
PRIMARY KEYに関する補足
InnoDB の場合、注意すべきもう 1 つの点があります...PRIMARY KEYデータをロードする前に、テーブルの定義に を含めることをお勧めします。 の前に、データを PK 順にソートすることもお勧めします。またはキーLOAD DATAを指定しないと、InnoDB は非表示の 6 バイト PK を構築します。これは通常、最適ではありません。PRIMARY KEYUNIQUE