単一の MySQL テーブル内の行数が 500 万を超えてはいけないのはなぜですか?

今日は、興味深いトピックについてお話ししましょう。データベースとテーブルを分割することを検討する前に、単一の MySQL テーブルにどれくらいのデータが必要でしょうか? 2,000 万行という人もいれば、500 万行という人もいます。それで、どのくらいの値が適切だとお考えですか?

かつて中国のインターネット技術界では、「MySQL テーブル 1 つあたりのデータ量が 2,000 万行を超えると、パフォーマンスが大幅に低下する」という格言が広く流布していました。実はこの噂は百度から始まったと言われています。具体的な状況はおおよそ次のようになります。DBA が MySQL のパフォーマンスをテストしたところ、単一テーブルの行数が 2,000 万行に達すると、SQL 操作のパフォーマンスが急激に低下することが分かりました。そのため、この結論に至りました。その後、Baiduのエンジニアが業界内の他の企業に移籍し、この情報を持ち帰ったと言われ、この噂は業界内に広まりました。

その後、アリババの「Java 開発マニュアル」では、単一テーブルの行数が 500 万を超える場合、または単一テーブルの容量が 2 GB を超える場合にのみ、データベースとテーブルを分割することを推奨しました。 Alibaba の黄金律はこれをサポートしています。そのため、多くの人がビッグデータ ストレージを設計するときに、これをテーブル パーティショニングを実行するための標準として使用しています。

それで、どのくらいの値が適切だとお考えですか?なぜ 300 万行や 800 万行ではなく、500 万行なのでしょうか?おそらく、これはアリの最高の実戦値であると言えるでしょうか?それで、問題は、この値はどのように評価されるかということです。ちょっと待ってください、少し考えてください。

実際、この値は実際のレコード数とは関係なく、MySQL の構成とマシンのハードウェアに関連しています。パフォーマンスを向上させるために、MySQL はテーブルのインデックスをメモリにロードします。 InnoDB バッファ サイズが十分であれば、すべてのデータをメモリにロードでき、クエリに問題は発生しません。ただし、単一テーブルのデータベースが一定の上限に達すると、メモリにインデックスを保存できなくなるため、後続の SQL クエリでディスク IO が発生し、パフォーマンスが低下します。もちろん、これは特定のテーブル構造の設計にも関係しており、最終的な問題はメモリの制限です。ここで、ハードウェア構成を増やすと、パフォーマンスがすぐに向上する可能性があります。

したがって、データベースとテーブルのシャーディングに関する私の意見は、実際のニーズと組み合わせる必要があり、過剰に設計すべきではないということです。データベースとテーブルのシャーディングは、プロジェクトの開始時に採用すべきではありません。代わりに、ビジネスが成長し、最適化を継続できなくなったときに、システムのパフォーマンスを向上させるためにデータベースとテーブルのシャーディングを検討する必要があります。この点に関して、Alibaba の「Java 開発マニュアル」では次のように付け加えています。「3 年以内にデータ量がこのレベルに達しないと見積もられる場合は、テーブルを作成するときにデータベースまたはテーブルを分割しないでください。」それで、元の質問に戻りますが、どの値が適切だとお考えですか?ご自身のマシンの状況を踏まえて総合的に判断することをお勧めします。基準が決まっていない場合は、比較的妥協できる値である 500 万行を暫定的に統一基準として使用してください。

皆さんに役立つSQLの書き方について、いくつかポイントを見てみましょう。

SQLの記述を最適化する必要がある

• クエリ結果のレコードを制限するには、limitを使用します。
• select * を避け、検索する必要があるフィールドをリストします。
• サブクエリの代わりに結合を使用する
• 大きな削除または挿入ステートメントを分割する
• スロークエリログをオンにすると、遅いSQLを見つけることができます。
• 列操作なし: SELECT id WHERE age + 1 = 10。データベース チュートリアル関数、計算式など、列に対するすべての操作はテーブル スキャンになります。クエリを実行するときは、操作を等号の右側に移動するようにしてください。
• SQL 文はできるだけシンプルにする必要があります。1 つの SQL 文は 1 つの CPU でのみ実行できます。大きな文はロック時間を短縮するために小さな文に分割する必要があります。1 つの大きな SQL 文はデータベース全体をブロックする可能性があります。
• OR を IN に書き換えます。OR の効率は n ですが、IN の効率は log(n) です。IN の数は 200 以内に抑えることをお勧めします。
• 実装には関数やトリガーは必要ありません
• %xxx 形式のクエリを避ける
• JOINは控えめに使用してください
• 比較には同じ型を使用します（例: '123' と '123'、123 と 123）
• WHERE 句では != または <> 演算子の使用を避けてください。そうしないと、エンジンはインデックスを放棄し、テーブル全体のスキャンを実行します。
• 連続した値の場合は、IN ではなく BETWEEN を使用します: SELECT id FROM t WHERE num BETWEEN 1 AND 5
• リスト データにテーブル全体を使用しないでください。LIMIT を使用してデータをページに分割します。ページ数は大きすぎないようにしてください。