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 です。

#inが使用される場合、内部テーブルBが外部テーブルAを駆動します
A から * を選択、ID は (B から ID を選択)
#existsを使用すると、外部テーブルAが内部テーブルBを駆動します
A から * を選択 (存在する場合) (B.id = A.id の場合、B から 1 を選択)

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

テーブル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); 

名前が "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 ごとにロードされるデータ列が少なくなります。

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

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

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

#クエリ待機4秒
スリープ(4)を選択します。

#Linuxシステムでは、スロークエリログを表示します。cat /var/lib/mysql/hadoop102-slow.log

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

#最も多くのレコードを返す 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

テーブル部門の作成
（
 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;

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

# 終了を示すために 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 を返します。
終了 $$

区切り文字 $$
プロシージャ 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まで
 繰り返し終了;
 専念;
終了 $$

区切り文字 $$
プロシージャ 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まで
 繰り返し終了;
 専念;
終了 $$

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

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

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

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

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

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