MysqlスローSQLの分析方法

コンテンツの概要:

スロークエリーログキャプチャスローSQL

を開く

explainを使用してスローSQL

を分析

show profileを使用してSQL実行詳細をクエリー

一般的なSQL文最適化

一、スロークエリーログを開いてスローSQLをキャプチャする
①mysqlがスローログキャプチャを開始するかどうかを問い合わせる:

SHOW VARIABLES LIKE '%slow_query_log%';

まだ開いていなければ、開いています.

SET GLOBAL slow_query_log=1;

②遅いクエリの時間の欠落値を表示する:

SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

実際の状況に応じて時間を調整できます.

SET GLOBAL long_query_time=2;

③クエリのSQLが遅いクエリ時間の欠落値を超えているか:

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

④MySQLが提供するログ分析ツールmysqldumpslowを使用して、差分SQLを取得する
例:
--スローログで最大10個のSQLを取得

./mysqldumpslow -s r -t 10 /PATH/TO/     

--時間順に並べ替えられた最初の10の中に左の接続が含まれているクエリ文を取得します.

mysqldumpslow -s t -t 10 -g "left join"      

その他の使用方法:./mysqldumpslow--help表示
二、explainを使って遅いSQLを分析する
例:

EXPLAIN SELECT a.username FROM tb_admin a LEFT JOIN tb_group p ON a.groupId = p.id WHERE a.username = 'xiaophai' LIMIT 1

1、id
SELECTクエリのシリアル番号は、クエリでSELECT文または操作テーブルを実行する順序が3つの場合を含む数字のセットを含む.
①idは同じで、実行順序は上から下まで②idが異なり、サブクエリであればidシーケンス番号が増加し、id値が大きいほど優先度が高くなり、先に実行される③は同じであり、異なる.idが同じグループであると判断した場合、実行順序は上から下までである.すべてのグループでid値が大きいほど優先度が高くなり、先に実行されます.
2、select_type
SIMPLEE:単純SELECTクエリー、クエリーにサブクエリーまたはUNIONは含まれていません
PRIMARY:クエリーには複雑なサブセクション、最外層のクエリーが含まれています.
SUBQUERY:SELECTまたはWHEREに含まれるサブクエリ部分
DERIVED:FROMに含まれるサブクエリはDERIVER(派生)としてマークされ、MySQLはこれらのサブクエリを再帰的に実行し、結果をテンポラリ・テーブルに格納します
UNION:2番目のSELECTでUNIONが現れるとUNIONと表記され、UNIONがFROM句のサブクエリに含まれると外層サブクエリはDERIVEDと表記される
UNION RESULT:UNIONテーブルから結果を取得するSELECT
3、table    
この行のデータがどのテーブルについて表示されますか?
4、type     
typeはアクセスタイプを示し、重要な指標であり、結果値は最良から最悪の順である.

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

一般的には、クエリが少なくともrangeレベルに達することを保証し、refに達することが望ましい.
System:テーブルには1行のレコード(システムテーブルに等しい)しかありません.これはconstタイプの特例で、普段は現れません.
const:インデックスで順次見つかった場合、constはプライマリ・キー・インデックスまたはuniqueインデックスを比較するために使用されます.1行のデータしか一致しないので、すぐに.プライマリ・キーをwhereリストに配置すると、MySQLはクエリを定数に変換できます.
eq_ref:一意性インデックススキャンで、インデックスキーごとにテーブルに1つのレコードしか一致しません.プライマリ・キーまたはユニーク・インデックス・スキャンに共通
ref:非一意性インデックススキャンで、個別の値に一致するすべてのローを返します.本質的にはインデックス・アクセスであり、個別の値に一致するすべてのローを返しますが、複数の条件に合致するローが見つかる可能性があるので、検索とスキャンのブレンドに属する必要があります.
range:指定した範囲のローのみを取得し、インデックスを使用してローを選択します.key列には、どのインデックスが使用されているかが表示されます.一般的には、where文にbetween、inなどのクエリーが表示されます.この範囲のスキャンインデックスは、すべてのテーブルスキャンよりも優れています.縮小された点で開始するだけで、すべてのインデックスをスキャンする必要はありません.
index:Full Index Scan、indexとALLの違いはindexタイプがインデックスツリーのみを巡回することであり、これは通常ALLよりも速く、インデックスファイルは通常データファイルより小さいためである.(つまりALLもindexも全テーブルを読みますが、indexはインデックスから読み出され、ALLはハードディスクから読み出されます)
all:Full Table Scan,全テーブルを巡回して一致する行を得る
5、possible_keys    
このテーブルに適用可能なインデックスを1つ以上表示します.クエリーに関連するフィールドにインデックスが存在する場合、そのインデックスはリストされますが、クエリーが実際に使用されるとは限りません.
6、key    
実際に使用したインデックス.NULLの場合、インデックスは使用されません.     
クエリーに上書きインデックスが表示されると、そのインデックスはkeyリストにのみ表示されます.
7、key_len    
インデックスで使用されるバイト数を表し、クエリで使用されるインデックスの長さを計算します.精度を損なわない場合は、長さが短いほど良い.    
key_lenに表示される値は、インデックスフィールドの最大可能な長さであり、実際に使用されている長さ、すなわちkey_ではありません.lenは、テーブル定義に基づいて計算され、テーブル内で取得されるものではありません.
8、ref    
インデックスのどのカラムが使用されているか、インデックスのカラムの値を検索するために使用されているカラムまたは定数が表示されます.
9、rows    
テーブル統計およびインデックスの選択状況に基づいて、必要なレコードを見つけるのに多くの読み取りが必要なロー数を概算します.
10、Extra    
他の列には表示されませんが、非常に重要な追加情報が含まれています.
①Using filesort:MySQLは、テーブル内のインデックス順ではなく、外部のインデックスを使用してデータをソートすることを示します.MySQLでインデックスを使用できないソート操作を「ファイルソート」と呼びます.
②Using temporary:テンポラリ・テーブルを使用して中間結果を保存し、MySQLはクエリ結果のソート時にテンポラリ・テーブルを使用します.ソートorder byとパケットクエリーgroup byによく見られる
③Using index:対応するSELECT操作でカバーインデックス(Covering Index)を使用していることを示し、テーブルのデータ行へのアクセスを避け、効率が良い.同時にusing whereが表示されると、インデックスキー値の検索にインデックスが使用されることを示します.検索アクション上書きインデックス(Covering Index)ではなく、インデックスがデータを読み取るために使用されていることを示すusing whereが同時に存在しない場合:理解方式1:SELECTのデータ列はインデックスから読み取るだけで、データ行を読み取る必要はなく、MySQLはインデックスを利用してSELECTリストのフィールドに戻ることができ、インデックスに基づいてデータファイルを再び読み取る必要はありません.すなわち、クエリー・カラムが作成されるインデックスによって上書きされる理解2:インデックスはローを効率的に見つける方法の1つですが、一般的なデータベースではインデックスを使用してカラムのデータを見つけることができるので、ロー全体を読み込む必要はありません.結局、インデックスリーフノードはインデックスのデータを格納します.インデックスを読み取ることで所望のデータが得られるようになると、行を読み取る必要がなくなり、1つのインデックスにクエリ結果を満たすデータが含まれていることを上書きインデックス注意といいます.上書きインデックスを使用する場合は、必要な列のみをSELECTリストから取り出すことに注意してください.SELECT*は使用できません.すべてのフィールドが一緒にインデックスを作成すると、インデックスファイルが大きすぎてクエリーのパフォーマンスが低下するためです.
④impossible where:WHERE句の値は常にfalseであり、どのタプルも取得できない
⑤select tables optimized away:GROUP BY句がない場合、インデックスに基づいてMIN/MAX操作を最適化したり、MyISAMストレージエンジンに対してCOUNT(*)操作を最適化したりして、実行段階まで計算する必要がなく、実行計画生成の段階を問い合わせると最適化が完了する
⑥distinct:distinct操作を最適化し、最初に一致する元祖を見つけた後、同じ値を探す操作を停止する
三、show profileを使ってSQL実行の詳細を問い合わせる
Show ProfileはMySQLが現在のセッションで文が実行されているリソースの消費量を分析し、SQLのチューニング測定に使用できる
分析手順
1、状態を確認する:SHOW VARIABLES LIKE'profiling;
2、オープン:set profiling=on;
3、結果の表示:show profiles;
4、診断SQL:show profile cpu、block io for query前ステップSQL数字番号;
ALL:全てのオーバーヘッド情報を表示
BLOCK IO:IO関連オーバーヘッドを表示
CONTEXT SWITCHES:コンテキスト切替関連オーバーヘッドの表示
CPU:CPU関連オーバーヘッドを表示する
IPC:送受信関連オーバーヘッドを表示
MEMORY:メモリ関連オーバーヘッドを表示
PAGE FAULTS:ページエラー関連オーバーヘッドの表示
SOURCE:表示とSource_function,Source_file,Source_line関連オーバーヘッド
SWAPS:交換回数関連オーバーヘッドを表示
注意(これらの状況に遭遇した場合は最適化する)
converting HEAP to MyISAM:クエリーの結果が大きすぎて、メモリがディスクに運ぶのに十分ではありません.
Creating tmp table:テンポラリ・テーブルの作成
Copying to tmp table on disk:メモリ内のテンポラリ・テーブルをディスクにコピーする
locked
四、よくあるSQL文の最適化
1、where文でorを使用して条件を接続しないようにしてください.そうしないと、インデックスの使用を放棄して全テーブルスキャンを行います.

select id from user where username='15623695987' or mobile='15623695987';

次のように問い合わせることができます.

select id from user where username='15623695987' union all select id from user where mobile='15623695987';

2、likeクエリーを正しく使用します.
%xx%クエリの結果、インデックスが使用できなくなります.

select id from user where username like '%test%'

インデックスを正しく使用するには、次の手順に従います.

select id from user where username like 'test%'

3、whereの文の中でフィールドに対して式の操作を行うことをできるだけ避けます
誤った方法:

select id from user where score/2 =100;

次のように変更します.

select id from user where score =100*2;

4、クエリ結果の数を確認した場合、できるだけlimitを追加する

select id from user where username='test' limit 1;

5、暗黙的な変換は不要
エラー例:

select id from user where mobile=15689764359 limit 1;
select username from user where id='15' limit 1;

正しい方法:

select id from user where mobile='15689764359' limit 1;
select username from user where id=15 limit 1;

6、複合索引を正しく使う
例:userテーブル、index idx_username(username,group_id)
インデックスを使用できます.

select id from user where username='test' and group_id=1;
select id from user where username='test'
select id from user where group_id=1 and username='test'

インデックスは使用できません:

select id from user where group_id=1;

まとめ:インデックスとidx_を使用するかどうかusername(username,group_id)は、この2つのフィールドの前後順に関係しています.
7、joinを使用している場合は、できるだけ小さなテーブルjoin大きなテーブルを使用してください
8、existsとinを正しく使う
① in

select `user`.id,`user`.username from `user` where `user`.id in (select `order`.user_id from `order`)

② exists

select `user`.id,`user`.username from `user` where exists (select`order`.user_id from `order` where `user`.id = `order`.user_id)

シーンリファレンスの適用:サブクエリの結果セットのレコードが少ない場合は、プライマリ・クエリのテーブルが大きく、インデックスがある場合はinを使用します.逆に、外部レイヤのプライマリ・クエリのレコードが少ない場合は、サブクエリのテーブルが大きく、インデックスがある場合はexistsを使用します.