mysqlのexplain 2
4717 ワード
前記:多くのものは簡単に見えますが、それはあなたが本当にそれを理解していないからです.
Explainコマンドは、実行効果を表示します.このコマンドはselectタイプの文と組み合わせてしか使用できませんが、update、deleteタイプの文のインデックス効果を表示したい場合は、あまり難しいことではありません.条件を変更せずにタイプをselectに変換すればいいです.
explainの構文は次のとおりです.
explain [extended] select ... from ... where ...
extendedを使用する場合は、explain文を実行した後、show warnings文を使用して適切な最適化情報をクエリーできます.
==============================================================
mk-visual-explain
ツールはexplainを拡張し、より直感的なツリー表現を提供し、使用方法は簡単です.
mk-visual-explain
mk-visual-explain -c
mysql -e "explain select * from mysql.user"| mk-visual-explain
MySQLコマンドラインでpagerを設定することで実行することもできます.
mysql> pager mk-visual-explain
mysql> explain [extended] select ... from ... where ...
==============================================================
本題に入り、例をより具体化するために、まず表を作成し、テストデータを挿入します.
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `article`
(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2');
デフォルトではプライマリ・キーが1つしか作成されておらず、他のインデックスは作成されていません.テストの時、もしあなたが時間に余裕があれば、できるだけ多くのテストデータを挿入しなければならない.データ量が少なすぎると、MySQLのインデックス選択の判断に影響する可能性があります.これにより、製品がオンラインになると、データ量が増加します.インデックスは往々にしてあなたの予想通りに働かない.
次のタスクを設定します:categoryをクエリーします.idが1でcommentsが1より大きい場合viewsが最も多いarticle_id.
質問は簡単ですが、SQLも簡単です.
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1
インデックス効果をexplainコマンドで表示します.
この場合、explainセクションの結果は次のとおりです.
type: ALL
key: NULL
Extra: Using where; Using filesort
インデックスが使用されず、プロセス中にファイルがソートされたデータベースが全テーブルスキャンされていることを示します.このような結果は悪いに違いありません.インデックスを作成することで最適化します.
ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` ) ;
この場合、explainセクションの結果は次のとおりです.
type: range
key: x
Extra: Using where; Using filesort
フルテーブルスキャンではありませんが、ファイルソートはまだ存在します.一般的に、ファイルのソートはORDER BY文と一緒に行われていますが、viewsフィールドを連合インデックスに入れましたが、なぜ効果がありませんか?これはBTreeの動作原理に従ってcategory_を先に並べ替えるためですid、同じcategoryに出会ったらidはcommentsを並べ替え、同じcommentsに遭遇した場合viewsを並べ替える.commentsフィールドが連合インデックスの中で中間位置にある場合、comments>1条件が1つの範囲値(いわゆるrange)であるため、MySQLは現在インデックスを利用して後のviews部分を検索することができず、comments in('a','b','c')のような多ければよいという点については、High Performance MySQLという本でAvoiding Multiple Range Conditionsという専門的な記述があり、複合インデックスには、rangeタイプのクエリーフィールドが1つしか保存されず、複合インデックスの最後に配置する必要があります.そうしないと、rangeタイプのクエリーフィールドの後ろのインデックスは無効になります.詳しい紹介は自分で調べることができます.そういう意味でこの時のcategory_id,comments,views複合インデックスの効果はcategory_に及ばないid,comments複合インデックスの効果は良いです.
ファイルのソートによってパフォーマンスに問題が発生するかどうかは、データの分散によって異なります.ここでは、Using index for ORDER BY vs restricting number of rowsの例を参照してください.
多くの場合、それを避けるべきです.インデックスを設定できます.
ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
この場合、explainセクションの結果は次のとおりです.
type: range
key: x
Extra: Using where; Using filesort
不思議なことに、システムは私たちが確立したばかりのyインデックスを無視し、xインデックスも使用しています.ファイルのソートがまだ存在します.
同様の状況が発生した場合は、強制インデックスを使用します.
EXPLAIN SELECT author_id
FROM `article`
FORCE INDEX ( y )
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1
この場合、explainセクションの結果は次のとおりです.
type: ref
key: y
Extra: Using where
xインデックスを削除することもできます.そうすると、システムは自動的にyインデックスを使用します(MySQLが馬鹿な場合がありますので、FORCE INDEXを使用する必要があります).
後述:Explainのtypeはアクセスタイプを示し、重要な指標であり、結果値は良いものから悪いものの順である.
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般的に、クエリが少なくともrangeレベルに達することを保証する必要があります.refに達することが望ましいです.そうしないと、パフォーマンスの問題が発生する可能性があります.
ExplainのExtra情報もかなり重要で、もしこの情報がUsing filesortあるいはUsing temporaryを表示するならば、悪夢は間もなく始まりますが、しかし必ずしもそうではありません.例えば1つのWHEREで...ORDER BY ... タイプのクエリでは、WHEREとORDER BYを両立させたインデックスを作成することができない場合が多いです.この場合、WHEREに従ってインデックスを決定すると、ORDER BYの場合、必然的にUsing filesortを引き起こすことになります.ファイルのソートが良いか悪いかはよく判断する必要があります.具体的には、Using index for ORDER BY vs restricting number of rowsを参照してください.
.
Explainコマンドは、実行効果を表示します.このコマンドはselectタイプの文と組み合わせてしか使用できませんが、update、deleteタイプの文のインデックス効果を表示したい場合は、あまり難しいことではありません.条件を変更せずにタイプをselectに変換すればいいです.
explainの構文は次のとおりです.
explain [extended] select ... from ... where ...
extendedを使用する場合は、explain文を実行した後、show warnings文を使用して適切な最適化情報をクエリーできます.
==============================================================
mk-visual-explain
ツールはexplainを拡張し、より直感的なツリー表現を提供し、使用方法は簡単です.
mk-visual-explain
mk-visual-explain -c
mysql -e "explain select * from mysql.user"| mk-visual-explain
MySQLコマンドラインでpagerを設定することで実行することもできます.
mysql> pager mk-visual-explain
mysql> explain [extended] select ... from ... where ...
==============================================================
本題に入り、例をより具体化するために、まず表を作成し、テストデータを挿入します.
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `article`
(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2');
デフォルトではプライマリ・キーが1つしか作成されておらず、他のインデックスは作成されていません.テストの時、もしあなたが時間に余裕があれば、できるだけ多くのテストデータを挿入しなければならない.データ量が少なすぎると、MySQLのインデックス選択の判断に影響する可能性があります.これにより、製品がオンラインになると、データ量が増加します.インデックスは往々にしてあなたの予想通りに働かない.
次のタスクを設定します:categoryをクエリーします.idが1でcommentsが1より大きい場合viewsが最も多いarticle_id.
質問は簡単ですが、SQLも簡単です.
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1
インデックス効果をexplainコマンドで表示します.
EXPLAIN SELECT author_id
FROM `article`
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1 この場合、explainセクションの結果は次のとおりです.
type: ALL
key: NULL
Extra: Using where; Using filesort
インデックスが使用されず、プロセス中にファイルがソートされたデータベースが全テーブルスキャンされていることを示します.このような結果は悪いに違いありません.インデックスを作成することで最適化します.
ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` ) ;
この場合、explainセクションの結果は次のとおりです.
type: range
key: x
Extra: Using where; Using filesort
フルテーブルスキャンではありませんが、ファイルソートはまだ存在します.一般的に、ファイルのソートはORDER BY文と一緒に行われていますが、viewsフィールドを連合インデックスに入れましたが、なぜ効果がありませんか?これはBTreeの動作原理に従ってcategory_を先に並べ替えるためですid、同じcategoryに出会ったらidはcommentsを並べ替え、同じcommentsに遭遇した場合viewsを並べ替える.commentsフィールドが連合インデックスの中で中間位置にある場合、comments>1条件が1つの範囲値(いわゆるrange)であるため、MySQLは現在インデックスを利用して後のviews部分を検索することができず、comments in('a','b','c')のような多ければよいという点については、High Performance MySQLという本でAvoiding Multiple Range Conditionsという専門的な記述があり、複合インデックスには、rangeタイプのクエリーフィールドが1つしか保存されず、複合インデックスの最後に配置する必要があります.そうしないと、rangeタイプのクエリーフィールドの後ろのインデックスは無効になります.詳しい紹介は自分で調べることができます.そういう意味でこの時のcategory_id,comments,views複合インデックスの効果はcategory_に及ばないid,comments複合インデックスの効果は良いです.
ファイルのソートによってパフォーマンスに問題が発生するかどうかは、データの分散によって異なります.ここでは、Using index for ORDER BY vs restricting number of rowsの例を参照してください.
多くの場合、それを避けるべきです.インデックスを設定できます.
ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
この場合、explainセクションの結果は次のとおりです.
type: range
key: x
Extra: Using where; Using filesort
不思議なことに、システムは私たちが確立したばかりのyインデックスを無視し、xインデックスも使用しています.ファイルのソートがまだ存在します.
同様の状況が発生した場合は、強制インデックスを使用します.
EXPLAIN SELECT author_id
FROM `article`
FORCE INDEX ( y )
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1
この場合、explainセクションの結果は次のとおりです.
type: ref
key: y
Extra: Using where
xインデックスを削除することもできます.そうすると、システムは自動的にyインデックスを使用します(MySQLが馬鹿な場合がありますので、FORCE INDEXを使用する必要があります).
後述:Explainのtypeはアクセスタイプを示し、重要な指標であり、結果値は良いものから悪いものの順である.
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般的に、クエリが少なくともrangeレベルに達することを保証する必要があります.refに達することが望ましいです.そうしないと、パフォーマンスの問題が発生する可能性があります.
ExplainのExtra情報もかなり重要で、もしこの情報がUsing filesortあるいはUsing temporaryを表示するならば、悪夢は間もなく始まりますが、しかし必ずしもそうではありません.例えば1つのWHEREで...ORDER BY ... タイプのクエリでは、WHEREとORDER BYを両立させたインデックスを作成することができない場合が多いです.この場合、WHEREに従ってインデックスを決定すると、ORDER BYの場合、必然的にUsing filesortを引き起こすことになります.ファイルのソートが良いか悪いかはよく判断する必要があります.具体的には、Using index for ORDER BY vs restricting number of rowsを参照してください.
.