MySQLクエリインターセプトの詳細な分析

MySQLクエリインターセプトの詳細な分析

1. クエリの最適化

1. MySQLチューニングの概要

  • 遅いクエリを有効にしてキャプチャする
  • 説明+遅いSQL分析
  • show profile は、MySQL サーバー内の SQL の実行の詳細とライフサイクルを照会します。
  • SQLデータベースサーバーのパラメータチューニング

2. 小さなテーブルが大きなテーブルを動かす

MySQL 結合の実装原則は、駆動テーブルのデータを基礎として使用し、「ネストされたループ」を使用して駆動テーブル内のレコードを一致させることです。駆動テーブルのインデックスは無効になりますが、駆動対象テーブルのインデックスは有効になります。

#テーブル a に 10,000 のデータがあり、テーブル b に 20 のデータがあると仮定します。select * from a join b on a.bid =b.id
テーブル a はテーブル b を次のように駆動します。
20 個のデータが 10000 個のデータに一致する場合 (a.bid=b.id の結合条件に基づいて、B+ ツリー検索を実行します)
検索回数: 20+ log10000
テーブル b はテーブル a を駆動して 10,000 個のデータに対して 20 個のデータを一致させます (a.bid=b.id の結合条件に基づいて、B+ ツリー検索が実行されます)。検索回数は 10,000+ log20 です。

3. に存在する

の使用が存在する

  • EXISTS構文: EXISTS(サブクエリ)はTRUEまたはFALSEのみを返すため、サブクエリ内のSELECT *はSELECT 1またはその他の場合もあります。公式の声明では、SELECTリストは実際の実行中に無視されるため、違いはありません。
    • SELECT ... FROM テーブル WHERE EXISTS(サブクエリ)
    • この構文は、条件検証のためにクエリされたデータをサブクエリに入れ、検証結果 (TRUE または FALSE) に基づいてメインクエリのデータ結果を保持するかどうかを決定するものとして理解できます。
  • EXISTS サブクエリの実際の実行プロセスは、私たちが理解しているような 1 対 1 の比較ではなく、最適化されている可能性があります。効率の問題が懸念される場合は、実際のテストを実行して、効率の問題があるかどうかを判断できます。
  • EXISTS サブクエリは、多くの場合、条件式、他のサブクエリ、または JOIN に置き換えることができます。最適なソリューションには、特定の問題の特定の分析が必要です。
#inが使用される場合、内部テーブルBが外部テーブルAを駆動します
A から * を選択、ID は (B から ID を選択)
#existsを使用すると、外部テーブルAが内部テーブルBを駆動します
A から * を選択 (存在する場合) (B.id = A.id の場合、B から 1 を選択)

結論は:

BテーブルデータセットがAテーブルデータセットより小さい場合は、
テーブルAのデータセットがテーブルBのデータセットよりも小さい場合は、existを使用します。

4. orderbyでテーブルを作成する

テーブルtblA(を作成
 #id int 主キー null ではない auto_increment,
 年齢 int、
 出生タイムスタンプが null ではありません
);

tblA(age, birth) に values(22, now()) を挿入します。
tblA(年齢、誕生)に値(23、現在())を挿入します。
tblA(age, birth) に values(24, now()) を挿入します。
#複合インデックスを作成します。create index idx_A_ageBirth on tblA(age, birth); 

Orderbyがインデックスにヒットする

Orderby はインデックスにヒットしません

  • MySQL は、インデックスの使用とファイルソートの使用という 2 つのソート方法をサポートしています。ファイルソートは効率が悪く、インデックスソートを使用するには、ソートが可能な限りインデックス列で行われ、インデックスの最適な左プレフィックスが追跡されるという 2 つの条件を満たす必要があります。
    • order by文自体はインデックスの左端の列を使用する。
    • where句とorder by句の条件列の組み合わせを使用して、左端の列に一致させます。
  • インデックス列に順序が指定されていない場合は、ファイルソートアルゴリズムが使用されます。双方向ソートと一方向ソートです。
    • MySQL 4.1 より前では、双方向ソートが使用されていました。これは、文字通り、ディスクを 2 回スキャンして最終的にデータを取得することを意味します。行ポインタを読み取り、列順に並べ替え、並べ替えたリストをスキャンし、リスト内の値に応じてリストから対応するデータ転送を再度読み取ります。
    • クエリに必要なすべての列をディスクから読み取り、列の順序に従ってバッファー内で並べ替え、並べ替えられたリストをスキャンして出力します。これにより、処理速度が向上し、データの 2 回目の読み取りが回避され、ランダム IO がシーケンシャル IO に変換されますが、各行をメモリ内に保持するため、より多くのスペースが使用されます。
名前が "zs" であるユーザーから * を選択し、年齢順に並べ替えます
#デュアルソート 1) name = 'zs'を満たす最初の主キーIDをnameから見つける
2) 主キー ID に基づいて行全体を取得し、ソート フィールド age と主キー ID をソート バッファーに格納します。3) name = 'zs' のレコードを満たす、name から次の主キー ID を取得します。
4) name = 'zs' が満たされなくなるまで 2 と 3 を繰り返します。
5) 年齢フィールドと主キー ID を、年齢フィールドに従って sort_buffer でソートします 6) ソートされた ID と年齢フィールドをトラバースし、ID 値に従って元のテーブルに戻り、すべてのフィールドの値を取得してクライアントに返します #単方向ソート 1) 条件 name = 'zs' を満たす最初の主キー ID を名前から見つけます
2) 主キーIDに基づいて行全体を取得し、すべてのフィールドの値を取り出し、sort_buffer(ソートバッファ)に格納します。 3) インデックス名から条件名= 'zs'を満たす次の主キーIDを検索します。
4) name = 'zs' が満たされなくなるまで手順 2 と 3 を繰り返します。
5) sort_buffer内のデータをageフィールドでソートし、結果をクライアントに返す

シングルパスソートの問題と最適化

質問:
 シングルパス アルゴリズムは改良されたアルゴリズムであるため、sort_buffer では一般にデュアルパス アルゴリズムよりも優れています。メソッド B はすべてのフィールドを取り出すため、メソッド A よりもはるかに多くのスペースを占有します。したがって、取り出されるデータの合計サイズが sort_buffer の容量を超える可能性があり、その結果、ソートごとに sort_buffer の容量までしかデータが取り出されなくなり (tmp ファイルの作成、複数のパスのマージ)、sort_buffer の容量までデータを取り出し、再度ソートする... という結果になり、複数の I/O が発生します。
最適化戦略:
 sort_buffer_sizeパラメータの設定を増やします。max_length_for_sort_dataパラメータの設定を増やします。注:
  並べ替え時に * を選択するのは絶対にタブーです。必須フィールドのみを照会してください。フィールドの数が増えると、メモリに保存するデータが増えるため、I/O ごとにロードされるデータ列が少なくなります。

5. Groupbyの最適化

1) グループ化は、実際には最初に並べ替えてから、インデックスの最も左のプレフィックスに従ってグループ化します。
2) インデックス列が使用できない場合は、max_length_for_sort_dataパラメータ設定を増やし、sort_buffer_sizeパラメータ設定を増やします。
3) where は having より上位です。where に条件を記述できる場合は、having を使用しないでください。
4) 残りのルールはorder byと同じです。

2. スロークエリログ

1. スロークエリログとは何ですか?

  1. MySQL スロー クエリ ログは、MySQL が提供するログ レコードの一種です。これは、応答時間がしきい値を超えた MySQL のステートメントを記録するために使用されます。具体的には、実行時間が long_query_time 値を超えた SQL ステートメントがスロー クエリ ログに記録されます。
  2. long_query_time のデフォルト値は 10 です。つまり、10 秒を超えて実行される SQL ステートメントが記録されます。
  3. これは、どの SQL ステートメントが最大許容時間値を超えているかを確認するために使用されます。たとえば、SQL ステートメントの実行に 5 秒以上かかる場合、その SQL ステートメントは低速であるとみなされます。5 秒以上かかる SQL ステートメントを収集し、以前の説明に基づいて包括的な分析を実施したいと考えています。

2. スロークエリログを有効にする

デフォルトでは、MySQL スロー クエリ ログは有効になっていません。チューニングに必要でない場合は、スロー クエリ ログを有効にするとパフォーマンスに影響するため、通常、このパラメータを有効にすることは推奨されません。スロー クエリ ログは、ログ レコードをファイルに書き込むことをサポートしています。

a) スロークエリログを有効にする

#スローログが有効になっているかどうかを確認します。show variables like 'slow_query_log%';
# スロークエリログを有効にします。永続的にするには、my.cnf で global slow_query_log = 1 を設定します。 

b) スロークエリログのしきい値を設定する

#スロークエリログを表示するためのデフォルトのしきい値時間は10秒です
'long_query_time%' のような変数を表示します。
#3秒に設定します。再起動は失敗します。永続的にしたい場合は、my.cnfでglobal long_query_time = 3を設定します。
# 再度表示するには、ウィンドウを切り替えて 'long_query_time%' などの変数を表示する必要があります。 

c) 永続的なスロークエリログと時間しきい値

[mysqld]
#永続的なスロークエリログ slow_query_log=1;
slow_query_log_file=/var/lib/mysql/hadoop102-slow.log
長いクエリ時間 = 3;
log_output=ファイル

d) クエリが遅い場合

#クエリ待機4秒
スリープ(4)を選択します。
#Linuxシステムでは、スロークエリログを表示します。cat /var/lib/mysql/hadoop102-slow.log

e) 現在のシステム内のスロークエリログの数を確認する

'%Slow_queries%' のようなグローバル ステータスを表示します。

3. ログ解析コマンド mysqldumpslow

a) パラメータの説明

-s: ソート方法を示します
c: 訪問回数
l: ロック時間
r: レコードを返す
t: クエリ時間
al: 平均ロック時間
ar: 返されたレコードの平均数
at: 平均クエリ時間
-t: 返されるレコードの数
-g: 大文字と小文字を区別しない通常の一致パターンが続く

b) 一般的な方法

#最も多くのレコードを返す 10 個の SQL ステートメントを取得します
mysqldumpslow -sr -t 10 /var/lib/mysql/hadoop102-slow.log
#最もアクセス数の多い 10 件の SQL を取得する
mysqldumpslow -sc -t 10 /var/lib/mysql/hadoop102-slow.log
# 左結合を含む最初の 10 個のクエリを時間順に取得します。mysqldumpslow -st -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log
#これらのコマンドは | と more と組み合わせて使用​​します。mysqldumpslow -sr -t 10 /var/lib/mysql/hadoop102-slow.log | more

3. データスクリプトのバッチ書き込み

1. テーブルを作成する

テーブル部門の作成
(
 deptno int 符号なし主キー auto_increment,
 dname varchar(20) NULLでないデフォルト ''
 loc varchar(8) NULLでないデフォルト ''
)ENGINE=INNODB デフォルト文字セット=utf8;

テーブルempを作成する
(
 id int unsigned 主キー auto_increment,
 empno mediumint unsigned not null デフォルト 0,
 ename varchar(20) NULLでないデフォルト ''
 ジョブvarchar(9) NULLでないデフォルト ''
 mgr mediumint unsigned not null デフォルト 0,
 雇用日がnullではありません。
 sal 小数点(7,2) は null ではありません。
 comm 10進数(7,2) は null ではありません。
 deptno mediumint unsigned not null デフォルト 0
)ENGINE=INNODB デフォルト文字セット=utf8;

2. ストアド関数の作成者を信頼するかどうかを設定する

# binlog ステータスを表示し、「log_bin%」のような変数を表示します。
# 信頼できるストレージ関数作成者を追加します。set global log_bin_trust_function_creators = 1; 

3. 関数を作成する

ランダムな文字列を生成する関数

# 終了を示すために 2 つの $$ を定義します (元の ; を置き換えます)
区切り文字 $$ 
関数 rand_string(n int) を作成し、varchar(255) を返します。
始める
 chars_str varchar(100) を宣言し、デフォルトは 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' です。
 return_str varchar(255) をデフォルト '' として宣言します。
 i int をデフォルトで 0 と宣言します。
 i < n ながら
 return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1)); と設定します。
 i=i+1 と設定します。
 終了しながら;
 return_str を返します。
終了 $$

部門番号をランダムに生成する機能

区切り文字 $$
関数 rand_num() を作成し、int(5) を返す
始める
 i int をデフォルトで 0 と宣言します。
 i=floor(100+rand()*10) を設定します。
 i を返します。
終了 $$

4. ストアドプロシージャを作成する

empテーブルにデータを挿入するためのストアドプロシージャを作成する

区切り文字 $$
プロシージャ insert_emp(in start int(10),in max_num int(10)) を作成します。
始める
 i int をデフォルトで 0 と宣言します。
 自動コミットを 0 に設定します。
 繰り返す
 i = i+1 と設定します。
 emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) に値((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num()); を挿入します。
 i=max_numまで
 繰り返し終了;
 専念;
終了 $$

dept テーブルにデータを挿入するためのストアド プロシージャを作成する

区切り文字 $$
プロシージャ insert_dept(in start int(10),in max_num int(10)) を作成します。
始める
 i int をデフォルトで 0 と宣言します。
 自動コミットを 0 に設定します。
 繰り返す
 i = i+1 と設定します。
 dept(deptno,dname,loc) に値((start+i),rand_string(10),rand_string(8)) を挿入します。
 i=max_numまで
 繰り返し終了;
 専念;
終了 $$

5. ストアドプロシージャを呼び出してデータを生成する

#部門テーブルに 10 件のレコードを挿入します DELIMITER;
insert_dept(100, 10)を呼び出します。
#従業員テーブルに 500,000 件のレコードを挿入します。CALL insert_emp(100001, 500000);

4. プロフィールを表示する

1. はじめに

  • SHOW PROFILE は、MySQL が提供するコマンドで、現在のセッションでのステートメント実行のリソース消費を分析するために使用できます。 SQL チューニング測定に使用できます。
  • デフォルトでは、パラメータはオフになっており、過去 15 回の実行結果が保存されます。

2. 開く

#プロファイルの表示が有効になっているかどうかを確認します。show variables like 'profiling%';
#番組プロフィールを開く
プロファイリングをオンに設定します。

3. ショープロフィールを使用する

テストデータの作成

empグループからid%10の制限150000で*を選択します。
empグループからid%10の制限150000で*を選択します。
emp から * を選択し、id%10 でグループ化し、5 で並べ替えます。
empから*を選択
部門から*を選択
emp 左から * を選択し、emp.deptno = dept.deptno で dept を結合します。

ショープロファイルを実行する

クエリ Query_ID に対して show profile cpu, block io を実行します。

検索パラメータ

ALL: すべてのコスト情報を表示
BLOCK IO: ブロックIO関連のオーバーヘッドを表示します
コンテキストスイッチ: コンテキストスイッチ関連のオーバーヘッド
CPU: CPU関連のオーバーヘッド情報を表示します
IPC: 送受信関連のオーバーヘッドに関する情報を表示します。
メモリ: メモリ関連のオーバーヘッド情報を表示します
ページフォールト: ページフォールト関連のオーバーヘッド情報を表示します
SOURCE: Source_function、Source_file、Source_lineに関連するオーバーヘッド情報を表示します。
SWAPS: オーバーヘッドに関連するスワップの数に関する情報を表示します。

結果を返す

HEAP を MyISAM に変換中: クエリ結果が大きすぎてメモリに収まらないため、ディスクに移動する必要があります。
tmp テーブルの作成: 一時テーブルを作成します。MySQL は最初にデータを一時テーブルにコピーし、使用後に一時テーブルを削除します。
ディスク上の tmp テーブルにコピーしています: メモリ内の一時テーブルをディスクにコピーしています。危険です! ! !
ロック: ロックされたテーブル

5. グローバルクエリログ

実稼働環境ではこの機能を有効にしないでください

my.cnfで設定する

# general_log=1 を有効にする
# ログファイルのパスを記録します general_log_file=/path/logfile
# 出力形式 log_output=FILE

エンコードが有効

グローバル general_log=1 を設定します。
グローバル log_output='TABLE' を設定します。

設定が完了すると、MySQLデータベースのgeneral_logテーブルに記録されます。

mysql.general_log から * を選択します。

要約する

これで、MySQL クエリ インターセプションに関するこの記事は終了です。MySQL クエリ インターセプションに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • Mysql 文字列インターセプション関数 SUBSTRING の使用方法
  • MySqlはDATE_FORMATを使用してDateTimeフィールドの日付値をインターセプトします。
  • MySQL インターセプションと文字列分割関数の使用例
  • Mysql 文字列の傍受と指定された文字列内のデータの取得
  • MySQL インターセプト文字列関数 substring_index の使用
  • mysqlは指定された2つの文字列間の内容を傍受します
  • MySQLは文字列関数のSQL文をインターセプトします
  • MySQLインターセプション関数の使用手順
  • MySQLの文字列インターセプト関連関数の概要
  • MySQLのユーザー情報をループし、ターゲットテーブルの対応するフィールドに挿入します。

<<:  WeChat アプレットのカスタム下部ナビゲーション バー コンポーネント

>>:  Linux 仮想ホストで SourceGuardian (sg11) 暗号化コンポーネントを有効にする詳細な手順

推薦する

MySQL トリガーの紹介、トリガーの作成、使用制限の分析

この記事では、例を使用して、MySQL トリガーの概要、トリガーの作成方法、およびトリガーの使用上の...

Vue+elementを使用してページ上部のタグを実装する方法の詳細な説明

目次1. ページレンダリング2. タグを切り替える3. タグを削除するこのようなタグはどのように記述...

アプレットにおけるwx.getUserProfileインターフェースの具体的な使用

最近、WeChatミニプログラムは、監査ミニプログラムのwx.loginおよびwx.getUserI...

Vue+Element UIはドロップダウンメニューのカプセル化を実現します

この記事の例では、ドロップダウンメニューのカプセル化を実装するためのVue + Element UI...

WindowsでMysql5.7.17のインストールと起動に失敗する問題を解決する

マシンに初めて MySQL をインストールします。オペレーティングシステムはwin7ですmysqlの...

vue3 のさまざまな構文形式を比較したサンプルコード

デフォルトのテンプレートメソッドはvue2に似ており、コンポーネント内のセットアップ関数を使用します...

MySQL 8.0.20 のインストールと設定の詳細なチュートリアル

この記事では、MySQL 8.0.20のインストールと設定方法についての詳細なチュートリアルを参考ま...

Dockerコンテナのホスト間通信におけるダイレクトルーティングの詳細な説明

概要Docker 自体の現在のデフォルト ネットワークについては、単一ホスト上の異なる Docker...

MySQL 8.0 で列を素早く追加する方法

序文: MySQL 8.0 では高速な列追加がサポートされ、数秒で大きなテーブルにフィールドを追加で...

Docker のポート解放失敗の解決策

今日、非常に奇妙な状況に遭遇しました。docker イメージを更新した後、docker-compos...

スライドによるページめくり効果とクリックイベント問題をモバイル端末上で実装する

前述のこの記事はとても短いです〜主な目的は、モバイル端末上のクリックと js イベントのメカニズムに...

Ubuntu 18.04 Server に静的 IP を設定する方法

1. 背景Netplan は、Ubuntu システムのネットワーク設定を簡単に管理および構成できるよ...

JavaScript バブルソートの例

目次1. バブルソートとは何か2. 例を挙げるラウンド1:第2ラウンド:第3ラウンド:第4ラウンド:...

ウェブページ内の 2 つのボックス モデル (W3C ボックス モデル、IE ボックス モデル)

Web ページ ボックス モデルには 2 種類あります。 1: 標準 W3C ボックス モデル。2:...

VMware を使用して PXE バッチ インストール サーバーをテストする詳細なプロセス

目次1. 準備1. 環境を整える2. インストール方法3. ネットワークカードの構成2. インストー...