MYSQLのインデックス

MYSQLのインデックス

インデックス

インデックス最適化ポリシー

インデックス最適化推奨

SQL文パフォーマンス最適化

管理インデックス

インデックス作成

削除インデックス

インデックスを表示

最適化表領域

インデックスの表示

EXPLAIN

出力情報説明

索引
インデックス最適化ポリシー

独立にカラムを使用する:演算に関与することをできるだけ避ける、独立したカラムはインデックスカラムが式の一部ではなく、関数のパラメータでもなく、where条件では常にインデックスカラムを比較記号の片側

に単独で置く.

左プレフィックスインデックス:指定したインデックスフィールドの左側の文字数を構築し、インデックス選択性によってインデックス選択性を評価するには、重複しないインデックス値とデータテーブルのレコード総数の比

を使用します.

マルチカラムインデックス:AND操作の場合、各カラムに個別のインデックス

を作成するのではなく、マルチカラムインデックスを使用するのに適しています.

適切なインデックス列の順序を選択:ソートとパケットがない場合、選択性は最大

に配置されます.
インデックス最適化の推奨事項

カラムにNULL値が含まれている限り、この例ではインデックスを設定しないほうがよい.複合インデックスにNULL値がある場合、このカラムは使用時にインデックス

を使用しない.

できるだけ短いインデックスを使用し、できれば接頭辞長

を作成する必要があります.

where句でよく使用される列については、インデックス

を設定することが望ましい.

複数の列whereまたはorder by句の場合、複合インデックス

を確立する必要があります.

like文の場合、%または'-'で始まるインデックスは使用されず、%で終わるとインデックス

が使用されます.

列での演算(関数操作と式操作)

はできるだけ避ける.

not inおよび<>操作

はできるだけ使用しないでください.
SQL文のパフォーマンスの最適化

クエリーの場合、できるだけ使わないで、できるだけ全フィールド名

を書きます.

ほとんどの場合、接続効率はサブクエリ

よりはるかに大きい.

マルチテーブル接続時は、できるだけ小さなテーブルで大きなテーブル、すなわち小さなテーブルjoin大きなテーブル

を駆動する.

大量の記録があるテーブルのページング時にlimit

を使用する

よく使用されるクエリに対して、キャッシュ

を開くことができる.

explainとprofileを使用してクエリー文

を分析することが多い

遅いクエリー・ログを表示し、実行時間の長いsql文最適化

を見つけます.
インデックスの管理
索引の作成

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;

create index idx_age on  students(age); --               
create index idx_name_age on students(name,age);  --                   
create unique index uni_age on testlog(age);  -- testlog            ，unique  index  

索引の削除

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

索引の表示

SHOW INDEXES FROM [db_name.]tbl_name;

show indexes from students\G;  --           

表領域の最適化

OPTIMIZE TABLE tb_name;

索引の使用の表示

SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

EXPLAIN
インデックスの有効性の分析

explain select *from students where name like 's%' and age<20;

出力情報説明
id:現在のクエリ文のうち、各SELECT文の番号select_type:単純クエリはSIMPLE複雑クエリ:SUBQUERY単純サブクエリPRIMARY最外のSELECT DERIVED FROMでのサブクエリUNION UNION文の最初の後のSELECT文UNION RESULT匿名テーブルtable:SELECT文に関連付けられたテーブルtype:関連タイプまたはアクセスタイプ、すなわちMySQLが決定したテーブルの行をどのようにクエリするか、以下の順序で、性能は低いから高いALLまで:全表スキャンindex:インデックスの順序によって全表スキャンを行う;Extra列に「Using index」が表示されると、テーブル全体のスキャンではなくオーバーライドインデックスが使用されることを示します.範囲制限のあるインデックスに基づいて範囲スキャンを実現します.スキャン位置は、インデックス内の点から始まり、別の点refで終了します.インデックスに基づいて、テーブル内の単一の値に一致するすべての行eq_を返します.ref:1つのローのみが返されますが、参照値と追加で比較する必要がある場合は、system:単一のローpossible_が直接返されます.keys:クエリーで使用可能なインデックスkey:クエリーで使用されるインデックスkey_len:インデックスで使用するバイト数ref:keyフィールドで表されるインデックスを使用してクエリーを完了する際に使用するカラムまたは定数値rows:MySQLがすべてのターゲット行を探すために読み取る必要があると推定する行数Extra:追加情報Using index:MySQLは、訪問テーブルUsing where:MySQLサーバがストレージエンジンで取得する後、再フィルタUsing temporary:MySQL結果をソートするときにテンポラリ・テーブルUsing filesortを使用します:結果を外部インデックスでソートします