MySQLの効率的な検索の書く習慣の育成

8681 ワード

mysql最適化 mysql

How to write efficient MySQL query statements

WHERE句の表記上の注意事項

索引

WHERE句の中の書く注意事項

は、まずwhereおよびorder byに関連するカラムにインデックスを確立することを考慮すべきである.

以下の操作により、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことは、できるだけ避けるべきである.

1).where句では!=または<>オペレータ

が使用する.

2).where句の中でフィールドに対してnull値の判断を行います.例えば、

select id from t where num is null;

はnumにデフォルト値0を設定することができ、テーブルのnum列にnull値がないことを確認します.その後、クエリ:

select id from t where num=0;

3).where句でorを使用して条件を接続します.たとえば、

select id from t where num=10 or num=20;

は、

select id from t where num=10
union all
select id from t where num=20;

と問い合わせることができます.

4).inとnot inも慎重に使用してください.例えば、

select id from t where num in(1,2,3);

は連続する数値に対して、betweenでinではなく、

select id from t where num between 1 and 3;

ではexistsでinに代わることができます.例えば、

select num from a where num in(select num from b);

は次の文で置き換えられます.

select num from a where exists(select 1 from b where num=a.num);

5).where句でパラメータを使用します.SQLは実行時にのみローカル変数を解析しますが、オプティマイザはアクセス計画の選択を実行時に遅らせることはできません.コンパイル時に選択する必要があります.コンパイル時にアクセス計画が確立された場合、変数の値は不明であり、インデックスとして選択できない入力項目です.次の文がフルテーブルスキャンを行うようにすると、

select id from t where num=@num;

はインデックスを使用するクエリを強制するように変更できます:

select id from t with(index(   )) where num=@num

6).where句の中でフィールドに対して関数、算術演算あるいはその他の式演算を行う例えば:

select id from t where num/2=100;
select id from t where date(createdate)>='2016-07-01';

は:

select id from t where num=100*2
select id from t where createdate>='2005-11-30 00:00:00';

に変更すべきである.

ファジイクエリ(like)の場合の注意点

程度にファジイマッチングが必要な場合、全テーブルスキャンを招く.

select id from t where tablename like '%abc%'

効率を向上させるには、次の3つの点から着手します.

a.select id from t where tablename like 'abc%'のように「%」で開始しない.

b.like "%xxx"のsqlを使用する必要がある場合、REVERSE()の関数に基づいて関数インデックスを作成することができる.
参照先:
http://blog.csdn.net/wangjunj...

c.全文検索を考慮することができる.まず、テーブルのデフォルトがinnoDBである場合、このテーブルのタイプは全文検索をサポートしないため、MyISAMのタイプを変更します.

alter table song engine=MyISAM;

その後、対応する検索するフィールドの上に全文検索のインデックスを確立する:

alter table add fulltext index(songname);

複数のフィールドを同時に検索する場合は、

alter table add fulltext index(songname,singername);

が以上のステップを完了すると、テーブルの全文検索が可能になります.たとえば、

select * from song where match(singername) against('   ') ;

またはマルチフィールド:

select * from song where match(singername,songname) against('  ');

MYSQLは現在、英語文字の全文検索しかサポートされていない可能性があります.全文検索を使用するには、中国語をピンインに変換する必要があります.
テーブルに検索が必要なフィールドにピンインフィールドを追加し、検索時に直接ピンインを検索することができます.
参照先:
http://blog.sina.com.cn/s/blo...

索引

複合インデックスは、インデックスフィールドを条件として使用する場合、インデックスが複合インデックスである場合、システムがインデックスを使用することを保証するために、インデックスの最初のフィールドを条件として使用する必要があり、可能な限りフィールドの順序がインデックスの順序と一致するようにしなければならない.

インデックス列に大量のデータが重複している場合、SQLクエリーはインデックスを利用しない可能性があります.すべてのインデックスがクエリーに有効ではありません.SQLはテーブルのデータに基づいてクエリーを最適化します.1つのテーブルにフィールドsex[male|female]がほぼ半分ずつ存在する場合、sex列にインデックスが作成されてもクエリの効率は低下します.この場合、ENUM(列挙タイプ)ではなくVARCHAR(列挙タイプ)を用いることができ、表構造を最適化する(select column from table_name procedure analyse();)例えば、

SELECT loan_period FROM cbs_bsns_loan PROCEDURE ANALYSE();

インデックスは、多ければ多いほど良いインデックスではなく、対応するselectの効率を高めることができるが、同時にinsertおよびupdateの効率を低下させる.insertまたはupdateではインデックスが再構築される可能性があるため、インデックスをどのように構築するかは、状況に応じて慎重に考慮する必要があります.原則として、1つのテーブルのインデックス数は6つを超えないほうがいいです.多すぎる場合は、あまり使用されないカラムに作成されたインデックスが必要かどうかを考慮する必要があります.

は、clusteredインデックスデータ列clusteredインデックスデータ列の更新を可能な限り避ける順序がテーブルレコードの物理的記憶順序であり、カラム値が変更されるとテーブルレコード全体の順序の調整を招き、かなりのリソースが消費される.アプリケーションシステムがclusteredインデックスデータ列を頻繁に更新する必要がある場合、そのインデックスをclusteredインデックスとして構築すべきかどうかを考慮する必要があります.
MySQL InnoDbのインデックスは
Clustered Index(クラスタインデックス)と
Secondary Index(二次インデックス);
Clustered Index:InnoDBテーブルごとに特殊なインデックスがあります.
clustered index、一般的には、
clustered indexと
plimary keyは同じ意味です.
InnoDBはclustered indexを選択する原則は以下の通りである:1表にprimary keyが定義されている場合、primary keyをclustered indexとして使用する.②primary keyが定義されていない場合は、clustered indexとして最初の空でないUNIQUEインデックスを選択します.したがって,テーブルに空でないUNIQUEインデックスが1つしかない場合,InnoDBはそれをマスタとする.③primary keyがなく、適切なUNIQUEインデックスもない場合、InnoDB内部には、各行のrow IDが含まれている非表示列が生成され、row IDは新しい行の挿入に伴って単調に増加する.次に、この非表示列にclustered indexとしてインデックスを作成します.
Secondary Index:Clustered Index以外のインデックスはSecondary Indexであり、各Secondary Indexのレコードにはインデックス列の値に加えてプライマリ・キー値が含まれます.2次インデックスクエリによってプライマリ・キー値が最初に検出され、その後、InnoDBは、検出されたプライマリ・キー値に基づいてプライマリ・キー/クラスタ・インデックスによって対応するデータ・ブロックを見つけます.

フィールドタイプ

数値フィールドを使用できるだけ数値情報のみを含むフィールドを文字型に設計しないでください.これにより、クエリーと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します.エンジンはクエリと接続を処理するときに文字列の各文字を1つずつ比較するので、数値型では1回だけ比較すれば十分です.たとえば、私たちのテーブルの多くは文字列でプライマリ・キーまたは接続フィールドを作成しています.ここでは、ビジネス・プライマリ・キーとして文字列を使用することができますが、プライマリ・キーと接続フィールドとして自己増加idを使用します.

は、varchar/nvarcharの代わりにできるだけchar/nchar(指定された長さ)を使用して、まず長くなるフィールドの記憶空間が小さくなり、記憶空間を節約することができる.次に、クエリの場合、比較的小さなフィールドでの検索効率は明らかに高くなります.

は、少数の列挙値のフィールドに対して、ENUMではなくVARCHARの列挙タイプを使用し、性別(男性、女性、未知)などの値の範囲を限定する.ENUMタイプは非常に速くコンパクトで、TINYINTが保存されていますが、外観は文字列として表示されます.これにより、このフィールドでいくつかのオプションリストを作るのはかなり完璧になります.「性別」、「国」、「民族」、「状態」または「部門」などのフィールドがある場合は、これらのフィールドの値が限られており、固定されていることを知っています.VARCHARではなくENUMを使用する必要があります.
注意:mysqlでenumというフィールドタイプを設定することは推奨されませんが、保存されている値は「0」、「1」、「2」などの数字です.
表構造最適化推奨

select column from table_name procedure analyse();

表接続時の注意事項

できるだけinner joinでLEFT JOINとNULLを避ける.

は、onのwhereとonの実行順序ではなく、whereの条件(ONの条件式のON)をできるだけ書いて、Bテーブルからデータ行を検索する方法を決定するために使用します.一致フェーズが完了した後にのみ、A LEFT JOIN B ON句の条件が使用され、一致フェーズで生成されたデータからフィルタが取得されます.したがって、接続を使用する場合、特にWHERE(またはleft join)の場合、マッチングを必要とするデータ量を低減し、Whereの実行を低減するために、できるだけ多くのマッチング満足条件を先に与えなければならない.
接続の使用方法についての参考:
http://www.cnblogs.com/BeginM...

その他の注意事項

は、right joinを使用せずに、特定のフィールドを適用します.

は、テンポラリ・テーブルの代わりにできるだけテーブル変数を使用します.テーブル変数に大量のデータが含まれている場合は、インデックスが非常に限られていることに注意してください(プライマリ・キー・インデックスのみ).

は、システム・テーブル・リソースの消費を減らすために、テンポラリ・テーブルの頻繁な作成と削除を回避します.

テンポラリ・テーブルを新規作成する際に、一度にデータを挿入する量が大きい場合は、create tableの代わりにselect intoを使用して、大量のlogを回避し、速度を向上させることができます.データ量が少ない場合は、システムテーブルのリソースを緩和するために、まずcreate tableを作成し、それからinsertします.

テンポラリ・テーブルが使用される場合、ストレージ・プロシージャの最後に必ずすべてのテンポラリ・テーブルを明示的に削除し、truncate tableを先に削除し、drop tableを実行することで、システム・テーブルの長時間のロックを回避できます.

クライアントに大きなデータ量を返すことをできるだけ避け、データ量が大きすぎる場合は、対応する需要が合理的かどうかを考慮しなければならない.

ORDER BY RAND()は絶対にしないでください.

プログラマーレベル2|疲労JS

DockerとPostgreSQLでkeycloakを設定する