『MySQLプレミアム』インデックス分析と最適化ノート(下)

「MySQLプレミアム」高陽先生がインデックスコースを説明するノートを勉強し、本編はorder byソート分析に重点を置いている.

表を作る

#  
CREATE TABLE tblA(
#id int primary key not null autp_increment,
age int,
birth timestamp not null
);

insert into tblA(age,birth) values(22,now());
insert into tblA(age,birth) values(23,now());
insert into tblA(age,birth) values(24,now());

#  
CREATE INDEX idx_A_ageBirth on tblA(age,birth);

select * from tblA;

Order By最適化（インデックス解析）

このテーブルには2つのフィールドage,birth,複合インデックスが上書きされているため,select*はselect age,birthに相当し,クエリは直接インデックスを削除し,テーブルに戻る必要はない.ここでは、ソート(order by)がファイルソート(filesort)を表示するかどうかにのみ注目します.

1.1 explain select * from tblA where age > 20 order by age;

+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+

ソートはageフィールドのインデックスを使用しており、filesortは表示されません.

1.2 explain select * from tblA where age > 20 order by age, birth;

+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+

ソート、age、birthは複合インデックスの順序に合致するため、ソートはage、birthの2つのフィールドのインデックスを使用し、filesortは表示されません.

*1.3 explain select * from tblA where age > 20 order by birth;

+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+

クエリーにageフィールドのインデックスが使用されます.ソート時、ageは範囲であり、範囲の後ろは完全に失効するため、age、birthインデックスをソートできないとfilesortが発生します.
ageが等値クエリーの場合、ソート時にfilesortは表示されません.次の文を参照してください.

mysql> explain select * from tblA where age = 22 order by birth;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | ref  | idx_A_ageBirth | idx_A_ageBirth | 5       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------+

1.4 explain select * from tblA where age > 20 order by birth, age;

+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+

ソートするとbirth、ageが複合インデックスの順序に合わないためfilesortが表示されます.

2.1 explain select * from tblA order by birth;

+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+

ソートするとbirthのみfilesortが表示されます.

2.2 explain select * from tblA where birth > '2016-01-28 00:00:00' order by birth;

+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+

ソート時にwhereとorder byのフィールド順序が複合インデックス順序に合致しない場合、ファイルソートが表示されます.

*2.3 explain select * from tblA where birth > '2016-01-28 00:00:00' order by age;

+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+

ソート時、whereとorder byのフィールド順序はオプティマイザによって最適化され、インデックスに一致する順序に一致し、ファイルソートは表示されません.

*2.4 explain select * from tblA order by age asc, birth desc;

+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+

ソート時、order byのフィールドage、birthはインデックス順に一致しますが、ageは昇順、birthは降順となり、インデックスでソートできずファイルソートが発生します.age、birthが同昇または同降であれば、ファイルソートは表示されません.

Order Byがインデックスを使用できない場合の最適化

Order Byがインデックスを使用できない場合、filesortが表示されます.filesortには2つのアルゴリズムがあります. と です.
ダブルソート
MySQL4.1以前は2ウェイソートを使用していましたが、文字通りディスクを2回スキャンし、最終的にデータを取得することを意味していました.
行ポインタとorderby列を読み取り、ソートしたリストをスキャンし、リストの値に従って対応するデータ出力をリストから読み直します.ディスクからソートフィールドを取り、bufferでソートし、ディスクから別のフィールドを取ります.
一連のデータを取り、ディスクを2回スキャンすることはよく知られているが、I/Oは時間がかかるのでmysql 4.1の後,2つ目の改良アルゴリズムが出現し,単一ルートソートである.
シングルウェイソート
クエリーに必要なすべてのカラムをディスクから読み込み、order byカラムに従ってbufferでソートし、ソート後のリストをスキャンして出力します.効率が向上し、2回目のデータの読み取りを回避できます.ランダムIOをシーケンスIOに変更しましたが、行ごとにメモリに保存されているため、より多くのスペースが使用されます.
一般的には のほうがいいですが、問題も発生します.これはなぜですか.
sort_bufferでは、 が よりも多くのスペースを占有している. はすべてのフィールドを取り出すため、取り出す可能性のあるデータの総サイズがsort_bufferの容量を超え、毎回sort_しか取れないbuffer容量サイズのデータを並べ替え(tmpファイルの作成、多重化)、並べ替えてからsort_を取りますbuffer容量サイズ、再配置...これにより、I/Oが複数回行われます.
I/O操作を一度省こうと思ったが、かえって大量のI/O操作を招き、かえって損をしなかった.
最適化方法sort_buffer_size容量とmax_length_for_sort_data容量を増やすことで

Order Byの速度を上げる

1.Order byのselect*はタブーで、必要なフィールドだけを調べることが重要です。ここでの影響は次のとおりです。

1.1 Queryのフィールドサイズの合計がmaxより小さい場合length_for_sort_DataでソートフィールドがTEXT|BLOBタイプでない場合、改善されたアルゴリズム-単一ソート、そうでない場合は古いアルゴリズム-多重ソートが使用されます.
1.2両アルゴリズムのデータはsort_を超える可能性があるbufferの容量が超過するとtmpファイルが作成されてマージソートされ、複数回のI/Oが発生しますが、単一のソートアルゴリズムを使用するリスクが大きいのでsort_を向上させますbuffer_size.

2.sort_の向上を試みるbuffer_size

どのアルゴリズムを使っても、このパラメータを高めると効率が向上します.もちろん、このパラメータはプロセスごとに向上するので、システムの能力に応じて向上します.

3.max_を上げてみるlength_for_sort_data

このパラメータを上げると,改良アルゴリズムを用いる確率が増加する.しかし、設定が高すぎると、データの総容量がsort_を超えます.buffer_sizeの確率が高くなり、明らかな症状は高いディスクI/O活動と低いプロセッサ使用率である.

Group By最適化

1.Gourp Byは実際には先に並べ替えてからグループ化し、複合インデックスの最適な左接頭辞の原則を遵守する.
2.インデックス列が使用できない場合は、Order Byのようにmax_を増やす必要があります.length_for_sort_dataとsort_buffer_size;
3.whereはhavingより先に、できるだけwhereで条件フィルタリングを行う.

SQLチューニング順序

スロー・クエリー取得スロー・ログを開く

explain+分析スローSQL

show profileクエリSQL MySQLサーバでの実行の詳細とライフサイクル状況

MySQLサーバパラメータチューニング(DBA)

[参考]
B駅『MySQLプレミアム』48.MySQLプレミアム_ソートのインデックスOrderBy最適化の使用 MySQL高級編(高陽)建表sql文大全