技術共有|explain format=json詳細
7757 ワード
作者:胡呈清
愛可生DBAチームメンバー、故障分析、性能最適化、個人ブログ:https://www.jianshu.com/u/a95...、討論を歓迎します.
オリジナル投稿
*爱可生开源社区出品,原创内容无许可使用,转载请联系小编,并注明出所.
explain format=jsonは、詳細な実行計画コストを印刷できます.次の2つの例では、コスト出力を表示する方法と、コストを計算する方法を示します.
表の構造は次のとおりです.
例1
eval_cost
これは簡単で、扇出のCPUコストを計算します.条件k<200を適用する場合、インデックス18行をスキャンする必要があり、ここで18は正確な値(index dive)であり、次いで、オプティマイザは、条件id<100を満たす割合が33.33%であることを啓発規則(heuristic)というアルゴリズムを用いて推定し、
注意:rows_examined_per_scan*filteredこそ扇出数で、簡単にrowsを使うことはできません.produced_per_joinが表す.
read_cost
ここには、IOコスト+(CPUコスト-eval_cost)がすべて含まれる.まず、このSQLの総コストをどう計算すればいいかを見てみましょう.
二次インデックスk_へのアクセス3のコスト: IOコスト= クエリ・オプティマイザは、インデックスの1つの範囲の区間のI/Oコストは、1つのページを読み込むのと同じであると乱暴に考えています.このSQLでは、kフィールドのフィルタ範囲は1つしかありません.k<200であり、1つのページを読み取るIOコストは1.0(io_block_read_cost)です. CPUコスト= kインデックスから18行のデータを取り出した後、実際にもう一度計算し、各行の計算コストは0.2です.
そしてselect*およびwhere id<100に必要なデータはインデックスk_にない3の中で、だから更に時計に戻って、時計のコストに戻ります: IOコスト= インデックスからk<200を満たすデータを取り出すと合計18行になるので= CPUコスト= この18行の完全なデータからid<100を満たすデータを計算するので、18回も計算する必要があります.
総コスト=
例2
joinクエリの総コスト計算式の簡略化:
上記の例では、ドライバテーブルへのアクセスコスト=26.21、ドライバテーブルのファンアウト数=18*33.33%=6、シングルアクセスドライバテーブルのコスト=1.0+0.2の合計コスト=26.21+6(1.0+0.2)=33.41
注意:ドライバテーブルと被ドライバテーブルのread_cost、eval_costは異なるコストを表します.
愛可生DBAチームメンバー、故障分析、性能最適化、個人ブログ:https://www.jianshu.com/u/a95...、討論を歓迎します.
オリジナル投稿
*爱可生开源社区出品,原创内容无许可使用,转载请联系小编,并注明出所.
explain format=jsonは、詳細な実行計画コストを印刷できます.次の2つの例では、コスト出力を表示する方法と、コストを計算する方法を示します.
表の構造は次のとおりです.
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` varchar(90) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4316190 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
## sbtest3
mysql> show create table sbtest3\G
*************************** 1. row ***************************
Table: sbtest3
Create Table: CREATE TABLE `sbtest3` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` varchar(66) COLLATE utf8mb4_bin DEFAULT NULL,
KEY `k_3` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
例1
mysql> explain format=json select * from sbtest3 where id<100 and k<200\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "26.21" ##
},
"table": {
"table_name": "sbtest3", ##
"access_type": "range", ## range,
"possible_keys": [
"k_3"
],
"key": "k_3", ##
"used_key_parts": [
"k"
],
"key_length": "4",
"rows_examined_per_scan": 18, ## k_3 :18( index dive )
"rows_produced_per_join": 5, ## id<100 :5
"filtered": "33.33", ## id<100
"index_condition": "(`sbtest`.`sbtest3`.`k` < 200)", ##
"cost_info": {
"read_cost": "25.01", ## IO + CPU
"eval_cost": "1.20", ## CPU
"prefix_cost": "26.21", ##read_cost+eval_cost
"data_read_per_join": "4K"
},
"used_columns": [
"id",
"k",
"c",
"pad"
],
"attached_condition": "(`sbtest`.`sbtest3`.`id` < 100)"
}
}
}
eval_cost
これは簡単で、扇出のCPUコストを計算します.条件k<200を適用する場合、インデックス18行をスキャンする必要があり、ここで18は正確な値(index dive)であり、次いで、オプティマイザは、条件id<100を満たす割合が33.33%であることを啓発規則(heuristic)というアルゴリズムを用いて推定し、
18*33.33%
回の計算を行ったCPUコストは18*33.33%*0.2=1.2
に等しく、ここで0.2はコスト定数(すなわちrow_evaluate_cost)である.注意:rows_examined_per_scan*filteredこそ扇出数で、簡単にrowsを使うことはできません.produced_per_joinが表す.
read_cost
ここには、IOコスト+(CPUコスト-eval_cost)がすべて含まれる.まず、このSQLの総コストをどう計算すればいいかを見てみましょう.
二次インデックスk_へのアクセス3のコスト:
1*1.0
18*0.2
そしてselect*およびwhere id<100に必要なデータはインデックスk_にない3の中で、だから更に時計に戻って、時計のコストに戻ります:
18*1.0
18*1.0
;18*0.2
総コスト=
1*1.0+18*0.2+18*1+18*02=26.2
.なぜならeval_costは扇出のCPUコスト:18*33.33%*0.2
なので、read_cost = CPU - eval_cost
、rows_examined_per_scan*(1-filtered)*0.2
とも言える.例2
mysql> explain format=json select t1.id from sbtest1 t1 join sbtest3 t3 \
on t1.id=t3.id and t3.k<200 and t3.id<100\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "33.41" ##
},
"nested_loop": [ ##join :NLJ
{
"table": {
"table_name": "t3", ##t3
"access_type": "range", ## range,
"possible_keys": [
"k_3"
],
"key": "k_3", ## :k_3
"used_key_parts": [ ## :k
"k"
],
"key_length": "4",
"rows_examined_per_scan": 18, ##k_3 :18
"rows_produced_per_join": 5, ##( ) 18 , id<200
"filtered": "33.33", ##( ) 18 , id<200 ,
"index_condition": "(`sbtest`.`t3`.`k` < 200)",
"cost_info": {
"read_cost": "25.01", ## IO + CPU
"eval_cost": "1.20", ## CPU
"prefix_cost": "26.21", ## :read_cost+eval_cost
"data_read_per_join": "4K"
},
"used_columns": [
"id",
"k"
],
"attached_condition": "(`sbtest`.`t3`.`id` < 100)"
}
},
{
"table": {
"table_name": "t1", ##t1
"access_type": "eq_ref", ##
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY", ##
"used_key_parts": [ ## id
"id"
],
"key_length": "4",
"ref": [
"sbtest.t3.id"
],
"rows_examined_per_scan": 1, ## , 1 ( )
"rows_produced_per_join": 5, ## ,
"filtered": "100.00", ## ,
"using_index": true,
"cost_info": { ## :rows_examined_per_scan*filtered, 18*33.33%=6
"read_cost": "6.00", ## IO * 。6*1.0=6,1.0
"eval_cost": "1.20", ## CPU * 。6*0.2=1.2,0.2
"prefix_cost": "33.41", ## = + (read_cost+eval_cost)
"data_read_per_join": "5K"
},
"used_columns": [
"id"
]
}
}
]
}
}
joinクエリの総コスト計算式の簡略化:
= + *
.explain実行計画詳細1には、関連クエリーにおけるfilteredの重要性を説明します.上記の例では、ドライバテーブルへのアクセスコスト=26.21、ドライバテーブルのファンアウト数=18*33.33%=6、シングルアクセスドライバテーブルのコスト=1.0+0.2の合計コスト=26.21+6(1.0+0.2)=33.41
注意:ドライバテーブルと被ドライバテーブルのread_cost、eval_costは異なるコストを表します.