MySQLのどのフィールドがインデックスに適しているかについての簡単な説明

1 データベース インデックスを作成するための一般的なルールは次のとおりです。

1. テーブルの主キーと外部キーにはインデックスが必要です。
2. 300 を超えるデータを持つテーブルにはインデックスが必要です。
3. 他のテーブルに頻繁に接続されるテーブルの場合は、接続フィールドにインデックスを作成する必要があります。
4. Where 句に頻繁に出現するフィールド、特に大きなテーブル内のフィールドにはインデックスを付ける必要があります。
5. インデックスは選択性の高いフィールドに構築する必要があります。
6. インデックスは小さなフィールドに作成する必要があります。大きなテキスト フィールドや非常に長いフィールドにはインデックスを作成しないでください。
7. 複合インデックスの作成には慎重な分析が必要です。代わりに単一フィールド インデックスの使用を検討してください。

A. 複合インデックス内のプライマリ列フィールド（通常は選択性の高いフィールド）を正しく選択します。
B. 複合インデックスの複数のフィールドが AND モードの Where 句に同時に出現することがよくありますか?単一フィールドクエリはほとんどないか、まったくありませんか?はいの場合は、複合インデックスを作成できます。それ以外の場合は、単一フィールド インデックスを検討してください。
C. 複合インデックスに含まれるフィールドが Where 句に単独で頻繁に出現する場合は、複合インデックスを複数の単一フィールド インデックスに分解します。
D. これらのフィールドに単一フィールド インデックスと複合インデックスがある場合、通常は複合インデックスを削除できます。

8. 頻繁にデータ操作を実行するテーブルには、インデックスをあまり多く作成しないでください。
9. 実行プランへの悪影響を避けるために、不要なインデックスを削除します。

上記は、インデックスを設定する際の一般的な判断基準です。
指標の設定は慎重に行う必要があります。各指標の必要性を慎重に分析し、設定の根拠を明確にする必要があります。
インデックスが多すぎる場合や、不十分または不正確なインデックスはパフォーマンスに何のメリットもありません。テーブルに作成された各インデックスはストレージのオーバーヘッドを増加させ、インデックスによって挿入、削除、および更新操作の処理オーバーヘッドも増加します。 さらに、単一フィールド インデックスがある場合、複合インデックスが多すぎると通常は意味がありません。逆に、データの追加や削除を行うときにパフォーマンスが低下します。特に、頻繁に更新されるテーブルの場合は、悪影響が大きくなります。
一般的に、小さなテーブルにはインデックスを付けるべきではありません。
または、データベースに 1 億を超えるデータ項目が記録される場合は、非リレーショナル データベースを使用することをお勧めします。
BLOB フィールドや CLOB フィールドなど、インデックス作成にはまったく適さない特殊なフィールドを持つデータベースもいくつかあります。
実際、この質問はソフトウェア プロジェクトを実行した経験に関するもののように感じられます。

2. 数千万件のレコードを持つMySQLデータベースのインデックス作成とパフォーマンス向上方法に関する事項

1. 注記:

まず、テーブルスペースとディスクスペースが十分にあるかどうかを検討する必要があります。インデックスもデータの一種であり、インデックスを作成すると必然的に多くのテーブルスペースを占有することになります。したがって、大きなテーブルのインデックスを作成する場合、最初に考慮すべきことはスペース容量です。
次に、インデックスを作成するときにテーブルをロックする必要があるため、業務がアイドル状態のときに操作を実行するように注意する必要があります。

2. パフォーマンス調整:

最初に考慮すべき点はディスク I/O です。物理的には、インデックスとデータを（アレイに関係なく）異なるディスクに分散させるようにしてください。論理的には、データ テーブルスペースはインデックス テーブルスペースとは別です。これは、インデックスを構築するときに従うべき基本原則です。

次に、インデックスを作成するときはテーブル全体をスキャンする必要があることがわかっているので、初期化パラメータ db_file_multiblock_read_count の値を増やすことを検討する必要があります。通常は 32 以上に設定します。

また、インデックスを作成する際には、テーブル全体のスキャンに加え、データに対する大量のソート操作も必要になります。そのため、ソート領域のサイズを調整する必要があります。

9i より前では、セッション レベルで sort_area_size のサイズを増やすことができます (たとえば、100 m 以上に設定)。

9i 以降では、初期化パラメータ workarea_size_policy の値が TRUE の場合、ソート領域は pga_aggregate_target から自動的に割り当てられます。

最後に、インデックスを作成するときに、nologging オプションを追加できます。これにより、インデックス作成中に生成される再実行の量が削減され、実行速度が向上します。

MySqlインデックスを最適化する際に注意すべき問題

適切に設計された MySql インデックスにより、データベースのパフォーマンスが向上し、データベースの効率が大幅に向上します。 MySQL インデックスを設計する際には、いくつか注意すべき点があります。

1. インデックスを作成する

クエリが主な焦点となるアプリケーションの場合、インデックスは特に重要です。多くの場合、パフォーマンスの問題は、単にインデックスの追加を忘れたり、より効果的なインデックスを追加できなかったりすることによって引き起こされます。インデックスが追加されていない場合、特定のデータが 1 つだけであっても、特定のデータを見つけるためにテーブル全体のスキャンが実行されます。テーブルに大量のデータがあるが、条件を満たす結果が少ない場合、インデックスを追加しないと致命的なパフォーマンスの低下が発生します。ただし、すべての状況でインデックスを作成する必要はありません。たとえば、性別には 2 つの値しかない場合があります。インデックスを作成しても利点がないだけでなく、更新速度にも影響します。これを過剰インデックスと呼びます。

2. 総合指数

例えば、次のような文があります。

エリアが「北京」かつ年齢が22のユーザーから*を選択します。 

MySQL クエリは一度に 1 つのインデックスしか使用できないため、それぞれ area と age に単一のインデックスを作成すると、インデックスなしの完全なテーブル スキャンに比べて効率が大幅に向上しますが、area 列と age 列に複合インデックスを作成すると、さらに効率が向上します。 （エリア、年齢、
給与) の場合、(area,age,salary)、(area,age)、(area) の 3 つのインデックスを作成することと同じになり、これは最良左プレフィックス機能と呼ばれます。したがって、複合インデックスを作成するときは、制限として最も頻繁に使用される列を降順で左側に配置する必要があります。

3. インデックスにはNULL値を持つ列は含まれません

列に NULL 値が含まれている限り、その列はインデックスに含まれません。複合インデックス内の 1 つの列に NULL 値が含まれている限り、その列はこの複合インデックスに対して無効になります。したがって、データベースを設計するときに、フィールドのデフォルト値を NULL に設定しないでください。

4. 短いインデックスを使用する

シリーズ列にインデックスを付ける場合、可能であればプレフィックスの長さを指定する必要があります。たとえば、CHAR(255)列があり、最初の10文字または20文字の範囲内でほとんどの値が一意である場合は、列全体にインデックスを付けないでください。短いインデックスを使用すると、クエリ速度が向上するだけでなく、ディスク領域と I/O 操作も節約できます。

5. ソートインデックスの問題

MySQL クエリは 1 つのインデックスのみを使用するため、where 句でインデックスがすでに使用されている場合、order by 句の列ではインデックスは使用されません。したがって、データベースのデフォルトのソートで要件を満たすことができる場合は、ソート操作を使用しないでください。複数の列のソートを含めないようにしてください。必要な場合は、これらの列に複合インデックスを作成するのが最適です。

6. 類似文操作

一般的に言えば、like 操作の使用は推奨されません。使用する必要がある場合、どのように使用するかも問題です。 「%a%」のようにはインデックスを使用しませんが、「aaa%」のようにはインデックスを使用できます。

7. 列に対して操作を実行しない

YEAR(adddate) のユーザーから * を選択します

8. NOT INや演算は使用しない

NOT IN 操作も IN 操作もインデックスを使用せず、完全なテーブルスキャンが実行されます。 NOT INはNOT EXISTSに置き換えることができ、id3の場合はid>3またはidを使用できます。

インデックスの追加例:

[shanghaiDB].[dbo].[Activity](AUDITSTATUS) に(ONLINE=ON) を指定して IDX_AUDITSTATUS インデックスを作成します。
 
[nantongDB].[dbo].[Orders](ANUM,MID) に IDX_ANUMMID インデックスを作成します (ONLINE=ON)
 
Usercenter.[dbo].MO(SiteCode) に IDX_SiteCode インデックスを作成します (ONLINE=ON)
 
[all].[dbo].[AccessLog](AccessDt) で(ONLINE=ON) を指定して IDX_AccessDt インデックスを作成します。

インデックスの作成 注: 大きなテーブルのインデックスを作成する場合は、ONLINE パラメータを追加することを忘れないでください。

最近、データベースを最適化していました。2億件のレコードがあるテーブルがあり、ジョイントインデックスを追加する必要があることがわかりました。共通のcreate index index_name on tablename (entp_id,sell_date)を使用しました。結果は悲惨でした。すべてのDMLステートメントがブロックされ、システムを正常に使用できなくなりました。幸いなことに、夜の10時で、ユーザーはあまりいませんでした。1時間後、インデックスが完了し、ブロックが解消されました。

オンラインで調べたところ、オンライン パラメータを追加すると、すべての DML ステートメントをブロックせずにオンラインでインデックスを作成できることがわかりました。これは苦労して学んだ教訓であり、皆さんと共有したいと思います。オンラインで追加する場合と追加しない場合の具体的な違いは次のとおりです。

インデックス作成に対する DML 操作の影響。 作成操作中に他のプロセスがこのインデックスに対応するデータに対して DML 操作を実行している場合、作成操作は影響を受けます。

SQL> テーブルテストを作成します (ID 番号、名前 varchar2(20)); 
テーブルが作成されました。

次にセッションを再開します。

SQL> テスト値に挿入（1、 'lms'）;
1 行が作成されました。
<コミットなし> 
SQL> test(id) にインデックス t1 を作成します。
test(id)にインデックスt1を作成する
*
1行目にエラーがあります:
ORA-00054: リソースがビジー状態のため、NOWAIT を指定して取得しました

パラメータ「online」を追加します。このパラメータを追加すると、作成プロセス中にインデックスをオンラインに保つだけでなく、Oracle はインデックスを作成する前にすべての DML 操作が完了するのを待機し、その後 DDL ロックを取得して作成を開始します。

SQL> test(id) にインデックス t1 をオンラインで作成します。
<コミットする前に保留>
<コミット後> 
SQL> コミット; 
コミットが完了しました。
インデックスが変更されました。

コミットしない場合は、上記の操作は保留されます。

したがって、将来的にインデックスを作成および再構築するときには、オンラインで追加するのが最適です。

MySQL フィールドがインデックスに適しているかどうかについては、これで説明は終わりです。MySQL フィールド インデックスに関するより詳しい内容については、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。