MySQLデータベースの数千万件のデータクエリとストレージの詳細な説明

百万レベルのデータ処理ソリューション

データストレージ構造設計

テーブルフィールドの設計

• テーブル フィールドは null ではありません。null 値はクエリが難しく、余分なインデックス スペースを占有するため、デフォルトの数値 0 が推奨されます。
• ステータス、タイプなどのデータ ステータス タイプのフィールドでは、-1 などの負の数値を定義しないようにしてください。この方法では UNSIGNED を追加できるため、値の容量が 2 倍になります。
• 可能であれば、INT の代わりに TINYINT、SMALLINT などを使用し、スペースをあまり占有しない BIGINT は使用しないようにしてください。
• 文字列型のフィールドは数値型のフィールドよりも多くのスペースを占めるため、文字列ではなく整数を使用するようにしてください。多くのシナリオでは、コーディング ロジックを通じて代わりに整数を使用できます。
• 文字列の長さを任意に設定しないでください。ビジネス要件を満たしながら、できるだけ短くしてください。
• IP を保存するには整数を使用します。
• 1 つのテーブルに含まれるフィールドの数は多すぎないようにしてください。20 個未満のフィールドを含めることをお勧めします。
• データ量が多くなるほどデータ構造の変更に時間がかかるようになるため、予測可能なフィールドについては事前に予約しておいてください。

インデックス設計

• インデックス、時間に対する空間の最適化戦略、基本的にビジネス ニーズに応じてインデックスを設計し、数百万のデータ量に対処するのに十分です。explain を使用する習慣を身に付けてください。また、explain にアクセスして、SQL の記述をより実用的にして詳細を学ぶこともできます。
• 常識: インデックスは多ければ多いほど良いです。インデックスはデータ書き込みのパフォーマンスを低下させます。
• インデックス フィールドの長さはできるだけ短くする必要があります。これにより、インデックス スペースを大幅に節約できます。
• 外部キーをキャンセルすると、プログラムは外部キーを制約できるようになり、パフォーマンスが向上します。
• 複合インデックスは、左端の列のルールと一致します。インデックスの順序は、クエリ条件と一致している必要があります。不要な単一列インデックスを削除するようにしてください。
• 分布値が少ない（一意の値が少ない）フィールドは、インデックス作成に適していません。たとえば、値が 2 つまたは 3 つしかない性別などのフィールドにインデックスを作成してもあまり意味がありません。
• インデックスはソートされ、クエリのパフォーマンスを向上させることができるため、ソートする必要があるフィールドにインデックスを追加することをお勧めします。
• 文字列フィールドでは、完全なフィールド インデックスではなくプレフィックス インデックスが使用されるため、インデックス スペースを大幅に削減できます。

クエリステートメントの最適化

• 複雑なインライン クエリの代わりに短いクエリを使用するようにしてください。
• クエリには select * を使用しないでください。テーブルに戻らないように、インデックス付きのフィールドをクエリするようにしてください。
• クエリの数を制限するには、limit を使用してください。
• クエリ フィールドは、可能な限りインデックス (特に複合インデックス) に配置する必要があり、左端のプレフィックスの一致にさらに注意を払う必要があります。
• 大規模な削除/挿入操作を分割すると、テーブルがロックされ、他の業務操作に影響を及ぼします。一方、MySQL では SQL 文の長さにも制限があります。
• MySQL 関数や計算を使用することは推奨されません。これらはまずプログラムで処理できます。上記の点から、プログラムで処理できる場合は、データベースに負荷をかけないようにしてください。ほとんどのサーバーパフォーマンスのボトルネックはデータベースにあるためです。
• クエリ数、パフォーマンス: count(1) = count(*) > count(主キー) > count(その他のフィールド)。
• クエリ演算子が between を使用できる場合は、in を使用しないでください。in を使用できる場合は、or を使用しないでください。
• != や <>、IS NULL や IS NOT NULL、IN、NOT IN などの演算子は、これらのクエリではインデックスを使用できないため、使用しないでください。
• SQL はできるだけシンプルに保ち、結合を少なくし、結合を 2 つ以上使用することは推奨されません。

1000万レベルのデータ処理ソリューション

データストレージ構造設計

この段階では、データ自体に大きな価値があります。通常のビジネスニーズを満たすだけでなく、データ分析のニーズもいくつかあります。現時点では、データの変動性は高くなく、基本的に元の構造の変更は考慮されていません。一般的に、最適化は、パーティション分割、テーブル シャーディング、データベース シャーディングの 3 つの側面から検討されます。

パーティション：

• パーティショニングとは、データベースが特定のルールに基づいてテーブルを複数のより小さく管理しやすい部分に分解する水平分割です。これはアプリケーションに対して完全に透過的であり、アプリケーションのビジネス ロジックに影響を与えません。つまり、コードを変更する必要はありません。そのため、より多くのデータを保存でき、クエリや削除もパーティションごとの操作をサポートし、最適化の目的を達成できます。パーティション分割を検討している場合は、事前に準備して次の制限を回避できます。
• テーブルには最大 1024 個のパーティションを設定できます (MySQL 5.6 以降では 8192 個のパーティションがサポートされます)。しかし、実際の操作では、パーティションを開くのにも時間がかかるため、一度に 100 を超えるパーティションを開かない方がよいでしょう。
• パーティション フィールドに主キーまたは一意のインデックス列がある場合は、すべての主キー列と一意のインデックス列を含める必要があります。テーブルに主キーまたは一意のインデックスがある場合は、パーティション キーは主キーまたは一意のインデックスである必要があります。
• パーティション化されたテーブルでは外部キー制約は使用できません。
• NULL 値はパーティション フィルタリングを無効にし、データはデフォルトのパーティションに配置されます。パーティション フィールドに NULL 値が表示されないようにしてください。
• すべてのパーティションは同じストレージ エンジンを使用する必要があります。

サブテーブル:

サブテーブルは、水平サブテーブルと垂直サブテーブルに分かれています。

水平テーブルパーティション分割は、データベースの読み取りと書き込みの負荷を軽減するために、テーブルを table1、table2 などの同じデータ構造を持つ小さなテーブルに分割します。

垂直テーブル分割は、いくつかのフィールドを分離して新しいテーブルを形成することです。各テーブルのデータ構造は異なるため、高い同時実行性の下でテーブルをロックする状況を最適化できます。

ご想像のとおり、テーブルを分割する場合は、プログラムのロジックを変更する必要があります。そのため、通常はプロジェクトの初期段階で、大量のデータが予想される場合に、テーブルの分割を検討します。コストが非常に高くなるため、後の段階でテーブルを分割することはお勧めしません。

サブライブラリ:

サブデータベースは、通常、マスター スレーブ モードです。データベース サーバーのマスター ノードは、1 つ以上のスレーブ ノードの複数のデータベースに複製されます。マスター データベースは書き込み操作を担当し、スレーブ データベースは読み取り操作を担当するため、マスターとスレーブの分離、高可用性、データ バックアップなどの最適化の目標が達成されます。

もちろん、マスタースレーブモードにも、マスタースレーブ同期の遅延、binlog ファイルが大きすぎるために発生する問題など、いくつかの欠陥があります。ここでは詳細には触れません (著者はもうそれを学ぶことができません)。

他の：

ホットメーターとコールドメーターは分離されています。履歴データについては、クエリを実行して使用する人が少ない場合は、別のコールド データベースに移動してクエリ専用に提供することで、ホット テーブル内の大量のデータを軽減できます。

データベーステーブルの主キー設計

データベースの主キーの設計については、時間属性を持つ自己増加する数値 ID を個人的に推奨します。 （分散型自己増分ID生成アルゴリズム）

• スノーフレークアルゴリズム
• Baidu 分散 ID アルゴリズム
• Meituan 分散 ID アルゴリズム

なぜこれらのアルゴリズムを使用するのでしょうか? これは、MySQL のデータ ストレージ構造に関連しています。

ビジネスの観点から:

データベースを設計するときに、どのフィールドを主キーとして設定するかを考える必要はありません。そして、これらのフィールドは理論上のみ一意です。たとえば、書籍番号を主キーとして使用する場合、書籍番号は理論上のみ一意ですが、実際には重複が発生する可能性があります。そのため、業務に関係のない自動増分IDを主キーとして設定し、帳票番号に一意制約を追加するのが良いでしょう。

技術的に言えば:

1. テーブルが自動増分主キーを使用している場合、新しいレコードが挿入されるたびに、そのレコードは現在のインデックス ノードの後続の位置に順番に追加されます。ページがいっぱいになると、新しいページが自動的に開かれます。 一般的に、クエリと挿入のパフォーマンスが向上します。

2. InnoDB の場合、主キー インデックスはインデックス値と行データの両方をリーフ ノードに格納します。つまり、データ ファイル自体は b+ ツリー形式でデータを格納することになります。

3. 主キーが定義されていない場合は、空でない UNIQUE キーが主キーとして使用されます。空でない UNIQUE キーがない場合、システムは 6 バイトの rowid を主キーとして生成します。クラスター化インデックスでは、N 行が 1 ページを形成します (ページのサイズは通常 16K です)。不規則なデータが挿入されると、B+ツリーのバランスを保つために、ページ分割やページローテーションが頻繁に発生し、挿入速度が遅くなります。したがって、クラスター化インデックスの主キー値は、ランダムな値ではなく、継続的に増加する値にする必要があります (ランダムな文字列や UUID は使用しないでください)。

4. したがって、InnoDB の主キーには、整数、および増加する整数を使用するようにしてください。これはストレージとクエリの両方において非常に効率的です。

MySQL 面接の質問

MySQLデータベース数千万データクエリ最適化ソリューション

制限ページングクエリが遅くなるほど、クエリは遅くなります。このことから、次のような結論も導き出されます。

1. 制限ステートメントのクエリ時間は、開始レコードの位置に比例します。

2. MySQL の limit ステートメントは非常に便利ですが、レコード数が多いテーブルに直接使用するには適していません。

テーブルはストレージエンジンとしてInnoDBを使用し、自動増分主キーとしてidを使用し、デフォルトの主キーインデックスを使用します。

テストの制限9000000,100からIDを選択します。

現在、最適化ソリューションには、サブクエリを使用してクエリ条件として id を使用する方法と、結合を使用する方法の 2 つがあります。

1. id>= (サブクエリ) フォームの実装

select * from test where id >= (select id from test limit 9000000,1)limit 0,100

参加フォームを使用してください。

SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 9000000,100) b ON a.id = b.id

これら 2 種類の最適化されたクエリの使用にかかる時間は比較的近いです。実際、どちらも同じ原理を使用しているため、効果は似ています。しかし、個人的には結合を使用し、サブクエリの使用を最小限に抑えることをお勧めします。注: 現在、クエリは数千万レベルです。数百万レベルに増やすと、速度は速くなります。

SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 1000000,100) b ON a.id = b.id

どの MySQL ストレージ エンジンを使用したことがありますか?

それぞれの特徴と違いは何でしょうか？

これは上級開発者の面接でよく聞かれる質問です。実際、私たちは日々の開発の中でこれによく遭遇します。 MySQL ストレージ エンジンは数多くありますが、最もよく使用されるのは InnoDB と MyISAM です。したがって、面接官が MySQL にどのようなストレージ エンジンがあるか尋ねた場合、よく使用される 2 つのエンジンを伝えるだけで済みます。

では、それぞれの特徴と違いは何でしょうか?

MyISAM: 従来の ISAM タイプに基づいたデフォルトのテーブル タイプです。ISAM は Indexed Sequential Access Method (インデックス シーケンシャル アクセス メソッド) の略で、レコードとファイルを保存するための標準的な方法です。トランザクションセーフではなく、外部キーをサポートしていません。多数の選択が実行される場合は、insert MyISAM の方が適しています。

InnoDB: トランザクションセキュリティをサポートするエンジン。外部キー、行ロック、トランザクションをサポートしているのが最大の特徴です。更新や挿入が大量に発生する場合、特に複数の同時実行や高 QPS の状況では、InnoDB を使用することをお勧めします。注意: MySQL 5.5 より前のバージョンでは、デフォルトの検索エンジンは MyISAM です。MySQL 5.5 以降のバージョンでは、デフォルトの検索エンジンは InnoDB に変更されます。

MyISAM と InnoDB の違い

1. InnoDB はトランザクションをサポートしますが、MyISAM はサポートしません。 InnoDB の場合、各 SQL ステートメントはデフォルトでトランザクションにカプセル化され、自動的にコミットされるため、速度に影響します。したがって、トランザクションを形成するには、begin と commit の間に複数の SQL ステートメントを配置するのが最適です。

2.InnoDB は外部キーをサポートしますが、MyISAM はサポートしません。

3. InnoDB はクラスター化インデックスであり、インデックス構造として B+Tree を使用します。データ ファイルは (主キー) インデックスにバインドされます (テーブル データ ファイル自体は B+Tree で編成されたインデックス構造です)。主キーが存在する必要があり、主キー インデックスの効率は非常に高くなります。 MyISAM は非クラスター化インデックスであり、インデックス構造として B+Tree も使用します。インデックスとデータ ファイルは別々であり、インデックスはデータ ファイルへのポインタを保存します。主キー インデックスとセカンダリ インデックスは独立しています。

4. InnoDB はテーブル内の特定の行数を保存しないため、select count(*) from table を実行するときにテーブル全体をスキャンする必要があります。 MyISAM は、テーブル全体の行数を保存するために変数を使用します。上記のステートメントを実行するときは、変数を読み取るだけでよいため、非常に高速です。

5. Innodb はフルテキスト インデックスをサポートしていませんが、MyISAM はサポートしています。MyISAM の方がクエリ効率が高くなります。InnoDB 5.7 以降ではフルテキスト インデックスがサポートされています。

6. InnoDB はテーブルレベルと行レベルのロック (デフォルト) をサポートしますが、MyISAM はテーブルレベルのロックをサポートします。 ;

7. InnoDB テーブルには主キーが必要です (ユーザーが指定しない場合は、テーブル自体が主キーを見つけるか生成します)。一方、Myisam には主キーがありません。

8.Innodb ストレージ ファイルは frm と ibd ですが、Myisam は frm、MYD、MYI です。

9.Innodb: frm はテーブル定義ファイル、ibd はデータ ファイルです。

10.Myisam: frm はテーブル定義ファイル、myd はデータ ファイル、myi はインデックス ファイルです。

MySQLの複雑なクエリ文の最適化

複雑な SQL の最適化に関しては、ほとんどの場合、複数のテーブルの関連付けが原因で、多数の複雑な SQL ステートメントが発生します。では、このような SQL をどのように最適化すればよいのでしょうか。実際には最適化のためのルーチンがあり、そのルーチンに従うだけで済みます。複雑な SQL 最適化ソリューション:

1. EXPLAIN キーワードを使用して SQL をチェックします。 EXPLAIN は、クエリ ステートメントまたはテーブル構造のパフォーマンス ボトルネックを分析するのに役立ちます。EXPLAIN のクエリ結果では、インデックスの主キーがどのように使用されているか、データ テーブルがどのように検索およびソートされているか、完全なテーブル スキャンがあるかどうかなどもわかります。

2. クエリ条件にインデックス フィールドを使用するようにします。テーブルに複数の条件がある場合は、複合インデックス クエリを使用するようにします。複合インデックスを使用する場合は、フィールドの順序に注意してください。

3. 複数のテーブルを関連付けるために、可能な限り結合を使用し、サブクエリの使用を減らします。テーブルの関連フィールドが主キーを使用できる場合は、主キーを使用します。つまり、インデックス フィールドを可能な限り使用します。関連付けられたフィールドがインデックス フィールドでない場合は、状況に応じてインデックスを追加することを検討できます。

4. ページング バッチ クエリに制限を使用し、一度にすべてを取得しないでください。

5. select * の使用は絶対に避け、特定の必須フィールドを選択するようにし、不要なフィールドのクエリを減らします。

6. すべてを変換または結合してみます。

7. is null または is not null の使用は避けてください。

8. のような使用に注意してください。事前ぼかしと完全ぼかしではインデックスは使用されません。

9. 関数はインデックス エラーの原因となるため、Where の後のクエリ フィールドでの関数の使用を最小限に抑えるようにしてください。

10. インデックスを使用しないため、等しくない (!=) の使用は避けてください。

11. in の代わりに exists を使用し、not in の代わりに not exists を使用すると、より効率的になります。

12. HAVING 句の使用は避けてください。HAVING は、すべてのレコードが取得された後にのみ結果セットをフィルター処理するため、並べ替え、合計、およびその他の操作が必要になります。 WHERE 句を使用してレコード数を制限できる場合は、このオーバーヘッドを削減できます。

13. ORDER BY RAND() は使用しない

上記は、MySQL データベースにおける数千万件のデータのクエリと保存に関する詳細な説明です。MySQL データベースにおける数千万件のデータのクエリと保存の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。