MySQLデータベース最適化技術のインデックス活用スキルまとめ_MySQL

この記事の例は、MySQL データベース最適化テクノロジのインデックスの使用法をまとめたものです。参考のために皆さんと共有してください。詳細は次のとおりです:

ここでは、前回の記事「MySQL データベース最適化テクノロジーの構成テクニックの概要」に続き、インデックス最適化テクニックをさらに分析します。

(7) テーブルの最適化

1. 適切なデータ エンジンを選択する

MyISAM: 大量の読み取り操作に適したテーブル

InnoDB: 大量の書き込みと読み取りに適したテーブル

2.適切な列タイプを選択します

SELECT * FROM TB_TEST PROCEDURE ANALYSE() を使用して、このテーブルの各フィールドを分析し、列の型を最適化するための提案を提供します

3. NULL 値を格納しない列には NOT NULL を使用します。これはインデックスを作成する列にとって特に重要です。

4. 適切なインデックスを作成します

5. 可変長フィールドより高速な固定長フィールドを使用する

(8) インデックス作成の原則

1. インデックスを適切に使用する

テーブルはクエリ内で 1 つのインデックスのみを使用できます。EXPLAIN ステートメントを使用してオプティマイザーの動作を確認します

分析を使用して、オプティマイザーがインデックスの使用状況をより正確に予測できるようにします

2. 検索、並べ替え、グループ化などの操作に関連するデータ列にインデックスを作成する必要があります

3. 重複データが少ないデータ列にインデックスを構築してみてください。これは一意であるため最適です。

例: 誕生日列にはインデックスを付けることができますが、性別列にはインデックスを付けるべきではありません

4. 短い値のインデックスを作成してみてください

ディスク IO 操作を削減し、インデックス バッファーがより多くのキー値を収容できるようになり、ヒット率が向上します

長い文字列のインデックスを作成する場合は、プレフィックスの長さを指定できます

5. 複数列インデックスの適切な使用

クエリで複数の条件を組み合わせる必要が頻繁にある場合は、複数列インデックスを使用する必要があります (テーブルのクエリに使用できるインデックスは 1 つだけであり、複数の単一列インデックスの作成に使用できるインデックスも 1 つだけであるため)

6. 一番左の接頭辞を活用する

つまり、複数列インデックス内の列の順序は合理的に配置され、最もよく使用されるものが最初にランク付けされる必要があります

7. インデックスを作成しすぎないでください

where、order by、group by で頻繁に使用されるフィールドのみインデックスを作成する必要があります。

8. スロークエリログを使用してスロークエリを見つける (log-slow-queries、long_query_time)

(9)インデックスを使いこなす

1. 同じデータ型のデータ列を比較してみます

2. 比較式内でインデックス列をできるだけ独立させます。WHERE mycol 3. クエリフィールドに関数を追加しないようにしてください。

例: WHERE YEAR(date_col) WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < カットオフは WHERE date_col < に変換されます

4. LIKE パターンの先頭にワイルドカードを使用しないでください

5. オプティマイザーを FROM 句の順序で強制的に結合するには、ストレート結合を使用します。すべての結合を強制するには、ストレート結合 b から * を選択します。

6. 強制インデックスを使用して、指定したインデックスの使用を強制します。たとえば、select * from Song_lib Force Index(song_name) order by Song_name は、強制インデックスを使用しないより効率的です

。

7. MySQL の自動型変換の使用は避けてください。そうしないと、インデックスを使用できなくなります。たとえば、int 型の num_col には where num_col='5' を使用します。

(10) SQL文の最適化

1. 適切な統計中間結果テーブルを作成して、大きなテーブルからデータをクエリする可能性を減らします

2. サブクエリの使用を避け、代わりに結合を使用してください。例: リーリー

次のように変更できます:

リーリー

リーリー

次のように変更されました:

リーリー

3. 重複キーを特定するために挿入する場合は、ON DUPLICATE KEY UPDATE:

を使用します。

コードは次のとおりです:

insert into db_action.action_today(user_id,song_id,action_count) value(1,1,1) ON DUPLICATE KEY UPDATE action_count=action_count+1;

4. カーソルの使用を避ける

カーソルの操作効率は非常に低いため、一時テーブルの追加、複数テーブルのクエリ、複数テーブルの更新などを使用することでタスクを完了できます。カーソルは使用しないでください。

(11) SQL ステートメントによるインデックスの使用を分析するには、Explain を使用します

キーワード EXPLAIN を SELECT ステートメントの前に置くと、MySQL は SELECT をどのように処理するかを説明し、テーブルがどのように結合されるか、およびどのような順序でテーブルにインデックスを付ける必要があるかを知ることができます。 get インデックスを使用してレコードを検索する高速な SELECT。また、オプティマイザーが最適な順序でテーブルを結合しているかどうかもわかります。オプティマイザーに SELECT ステートメントに対して特定の結合順序を使用させるには、STRAIGHT_JOIN 句を追加します。 。

EXPLAIN コマンドの一般的な構文は次のとおりです: EXPLAIN 例: Explain select * from an inner join b on a.id=b.id

EXPLAINの解析結果パラメータの詳細説明:

1.table: これはテーブルの名前です。

2.type: 接続操作のタイプ。

システム: テーブルにはレコードが 1 つだけあります (実際のアプリケーションでは、データが 1 つだけ含まれるテーブルが使用されることはほとんどありません)

const: テーブルには一致する行が最大 1 つあり、PRIMARY KEY または UNIQUE インデックスのすべての部分を定数値

と比較するときに使用されます。

例:

リーリー

(song_id はテーブルの主キーです)

eq_ref: 前のテーブルの行の組み合わせごとに、UNIQUE または PRIMARY KEY のインデックスを使用してテーブルから行を読み取ります。

例:

コードは次のとおりです:

select * from Song_lib a inner join singer_lib b on a.singer_id=b.singer_id

(bの型値はeq_refです)
ref: 前のテーブルの行の組み合わせごとに、UNIQUE または PRIMARY KEY 以外のインデックスを使用してテーブルから行を読み取ります

例:

コードは次のとおりです:

select * from Song_lib a inner join singer_lib b on a.singer_name=b.singer_name

そして

コードは次のとおりです:

select * from singer_lib b where singer_name='ccc'

(b.singer_name は通常のインデックスであるため、b の型値は ref です)

ref_or_null: この結合タイプは ref に似ていますが、MySQL を追加すると、NULL 値を含む行を具体的に検索できます。

例:

コードは次のとおりです:

select * from singer_lib where singer_name='ccc' または singer_name が null

index_merge: この結合タイプは、インデックス マージ最適化メソッドが使用されていることを示します

キー: MySQL が実際に使用するインデックスの名前を示します。空 (または NULL) の場合、MySQL はインデックスを使用しません。

key_len: インデックスの使用されている部分の長さ (バイト単位)。

3.ref: ref 列は、テーブルから行を選択するためにキーとともに使用される列または定数を示します

4.rows: MySQL が正しい結果を見つける前にスキャンする必要があると考えるレコードの数。明らかに、ここでの理想的な数は 1 です。

5.追加: ここにはさまざまなオプションが表示されますが、そのほとんどはクエリに悪影響を及ぼします。通常は次のものが含まれます:

using where: where 条件を使用することを意味します

using filesort: ファイルの並べ替えが使用されること、つまり order by 句が使用されることを意味しますが、order by のフィールドのインデックスは使用されないため、追加の並べ替えオーバーヘッドが必要になります。したがって、using filesort が表示される場合は、それを意味します。ソート効率が非常に低いため、強制インデックスを使用するなどの最適化が必要です

さらに MySQL 関連のコンテンツに興味のある読者は、このサイトの特別トピックをチェックしてください:「MySQL インデックス操作スキルの概要」、「MySQL ログ操作スキルの総合集」、「MySQL トランザクション操作スキルのまとめ」、「 MySQL ストアド プロシージャ スキルの総合集」、「MySQL データベース ロック関連スキルのまとめ」、「よく使われる MySQL 関数のまとめ」

この記事が皆さんの MySQL データベース計画に役立つことを願っています。