索引付き

インデックスの概念

インデックスは、データ検索の効率を向上させるためにデータベースが維持するデータ構造です。本のカタログに似ており、データベースがスキャンするデータの行数を減らすのに役立ちます。

インデックスはクエリ速度を向上させますが、追加のストレージスペースを占有し、データの追加、変更、削除に伴うメンテナンスコストが増加します。したがって、インデックスはより良いものではなく、実際のクエリ条件に基づいて設計する必要があります。

一般的な索引の種類

プライマリ·キー·インデックス

テーブルのプライマリ·キーは、プライマリ·キー·インデックスを自動的に作成します。プライマリ·キー値は一意である必要があり、NULLにはできません。

ユニークインデックス

一意のインデックスは、カラム値を重複できないように制限するために使用されます。My SQLの一意のインデックスは通常、複数のNULLを許可しますが、その動作はデータベースのバージョンと列定義にも依存します。

通常のインデックス

通常のインデックスは主にクエリ速度の向上に使用され、データの一意性を保証しません。

複合索引

複合インデックスは、複数のカラムを組み合わせて構成されます。たとえば、インデックス·カラムの順序がjob_id、salaryの場合、次のクエリ条件は通常サポートされます。

where job_id = ?

where job_id = ? and salary >= ?

しかし、salaryクエリのみを使用する場合、この複合インデックスの左端のカラムを効率的に利用することはできません。これは複合インデックスの最左接頭辞の原則です。

全文索引付き

全文索引は、テキスト·コンテンツの検索に使用されます。My SQLは全文検索にMATCH()とAGAINST()を組み合わせることができ、中国語全文検索はngramテゴニストと組み合わせることが多い。

インデックスのストレージ構造

My SQLのInnoDBストレージエンジンは、主にB+ツリーを使用してプレーンインデックスとプライマリキーインデックスを整理します。

B+木には以下の特徴がある。

• データキー値の順序付け。
• ツリーの高さは低く、ディスクおよびページストレージに適しています。
• リーフノード間の順序付けられた接続は、範囲クエリに適しています。
• クエリ時のディスク·ページ·アクセス回数を減らすことができます。

ハッシュ構造は等価検索には適しているが、範囲クエリやソートには適していない。InnoDBの通常のインデックスは通常のハッシュインデックスではない。

インデックスの作成と削除

共通インデックスの作成

create index employee_index1
on employee(first_name);

複合インデックスの作成

create index employee_index2
on employee(job_id, salary);

ユニークなインデックスの作成

create unique index employee_index3
on employee(phone_number);

全文索引の作成

alter table employee
add address varchar(255);

create fulltext index employee_index_addr
on employee(address)
with parser ngram;

全文索引クエリの使用

自然言語モードの検索：

select address
from employee
where match(address) against('大庆');

Boolean Modeクエリ：

select address
from employee
where match(address)
against('+黑龙江 -大庆' in boolean mode);

インデックスの削除

drop index employee_index1 on employee;

インデックスはALTER TABLEから削除することもできます。

alter table employee
drop index employee_index1;

InnoDBとMyISAMの違い

InnoDB

InnoDBはクラスタード·インデックスを使用してテーブル·データを整理する。プライマリキーインデックスのリーフノードは完全な行データを保持し、セカンダリインデックスのリーフノードはプライマリキーを保持します。

セカンダリインデックスを介してインデックスなしの列をクエリする場合、データベースは通常、セカンダリインデックスから主キーを取得し、主キーインデックスを介して完全な行を検索します。

テーブルに明示的なプライマリ·キーがない場合、InnoDBは適切な一意の空でないインデックスを選択するか、内部の隠しロー識別子を作成してクラスタインデックスを整理します。したがって、InnoDBテーブルは通常、クリーンで安定したプライマリキーを積極的に設計する。

MyISAM

MyISAMのインデックスとデータファイルの分離。インデックスリーフノードはデータレコードの物理アドレスを保持し、プライマリキーインデックスと通常インデックスは、InnoDBクラスタードインデックスとセカンダリインデックスの違いはありません。

InnoDBはトランザクションと行レベルのロックをサポートし、My SQLのデフォルトストレージエンジンです。MyISAMはトランザクションをサポートしておらず、現代のビジネスシステムではInnoDBが優先される。

EXPLAINを使用したクエリーの解析

クエリ文の前にEXPLAINまたはDESCを追加して、オプティマイザが選択した実行プランを表示できます。

explain
select *
from employee
where first_name = 'Tom';

一般フィールドは以下の通り。

フィールド	摘要
typeは	テーブルアクセス、通常はフルテーブルスキャンから定数クエリまで徐々に改善
possible_keys	使用可能な索引
key	実際に選択されたインデックス
key_len	使用するインデックス長
rows	スキャンされる予定のロー数
Extra	追加の実装情報

typeによく見られる値としては，ALL，index，range，ref，eq_ref，constがある.一般的には、大きなテーブル上のALLフルテーブルスキャンに焦点を当てるべきですが、SQLが妥当かどうかを単一のフィールドだけで判断することはできません。

eq_refは、接続条件がプライマリ·キーまたは一意のNULLでないインデックスを使用する場合によく使用されます。refは通常のインデックスを使用した等価クエリでよく使われる。

ゆっくり検索位置

SQL 最適化は通常、問題を特定し、実行計画を分析し、SQLまたはインデックスを調整します。

スロークエリを開始する

低速クエリログはMy SQL 構成ファイルで設定できます。設定ファイルの場所は、システムやインストール方法によって異なる場合があります。

slow_query_log=ON
slow_query_log_file=WW-slow.log
long_query_time=10

構成を変更するには、通常、My SQLサービスを再起動するか、適切な動的システム変数を使用します。本番環境では、10 秒を固定するのではなく、ビジネスに応じて合理的なしきい値を設定する必要があります。

遅いSQLの分析

低速 SQLを特定したら、EXPLAINを使用して、インデックスの選択、ジョインの順序、スキャンされる予定のローの数、テンポラリ·テーブルまたは追加のソートが発生しているかどうかを確認します。

SQLとインデックス最適化の原則

複合インデックスの適切な設計

複合インデックスのカラムの順序は、可用性に影響します。単一のクエリフィールドだけを見るのではなく、等価条件、範囲条件、ソート、グループ化の要件を組み合わせて設計する必要があります。

インデックス·カラムに対する不要な操作の回避

以下の条件は、通常のインデックスを直接見つけることが困難になります。

where salary + 1000 > 8000

より適切な文章は：

where salary > 7000

インデックス·カラムの不要な関数処理を避ける

where year(create_time) = 2026

范囲问合せとしてきできます。

where create_time >= '2026-01-01'
and create_time < '2027-01-01'

INとEXISTSを理解する

INは必ずしもEXISTSより遅いわけではなく、オプティマイザは両方を類似した実行計画に変換することができる。機械的な置換ではなく、データ量、インデックス、EXPLAIN結果を組み合わせて選択してください。

NULLの注意点

ビジネス意味が許す場合、NOT NULLと合理的なデフォルトを使用してデータ処理を簡素化できます。しかし、インデックス最適化のために、真の“未知”状態を無意味な値に置き換えることはできません。

無意味なフル·カラム·クエリの回避

ビジネスに必要な列のみをクエリすることで、ネットワーク転送とテーブルバックデータの量を減らし、オーバーレイインデックスを形成する機会を得ることができます。

select employee_id, first_name
from employee
where first_name = 'Tom';

適切な区別のインデックス·カラムの選択

重複する値が多い列は個別にインデックスされ、メリットは低くなります。プレフィックスインデックスは長い文字列に適していますが、区別とクエリ要件に合わせてプレフィックス長を選択する必要があります。

create index employee_email_prefix
on employee(email(12));

接続、ソート、グループ化のためのインデックスの設計

接続フィールドは一貫したデータ型を持ち、クエリの頻度に基づいてインデックスが付けられている必要があります。外部キー制約とインデックスは、参照整合性を保証する外部キーとアクセス効率を向上させるインデックスの2つの概念です。InnoDBは外部キーを作成する際に関連するカラムに利用可能なインデックスが必要ですが、“外部キーがあればクエリが最速”とは言えません。

ブラインドインデックス作成を避ける

通常、次の列は、通常のインデックスを個別に作成するのに適していません。

• データ量が非常に少ないテーブル。
• 更新が頻繁で、クエリが少ないカラム。
• 区別が非常に低く、他の組み合わせクエリ値を持たない列。
• クエリー、ジョイン、ソート、またはグループ条件には表示されない列。

最終的にインデックスが必要かどうかは、実際のクエリと実行計画に基づくべきです。