技術共有|explain format=json詳細

7757 ワード

作者:胡呈清
愛可生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のコスト:
  • IOコスト=1*1.0
  • クエリ・オプティマイザは、インデックスの1つの範囲の区間のI/Oコストは、1つのページを読み込むのと同じであると乱暴に考えています.このSQLでは、kフィールドのフィルタ範囲は1つしかありません.k<200であり、1つのページを読み取るIOコストは1.0(io_block_read_cost)です.
  • CPUコスト=18*0.2
  • kインデックスから18行のデータを取り出した後、実際にもう一度計算し、各行の計算コストは0.2です.
    そしてselect*およびwhere id<100に必要なデータはインデックスk_にない3の中で、だから更に時計に戻って、時計のコストに戻ります:
  • IOコスト=18*1.0
  • インデックスからk<200を満たすデータを取り出すと合計18行になるので=18*1.0;
  • CPUコスト=18*0.2
  • この18行の完全なデータからid<100を満たすデータを計算するので、18回も計算する必要があります.
    総コスト=1*1.0+18*0.2+18*1+18*02=26.2.なぜならeval_costは扇出のCPUコスト:18*33.33%*0.2なので、read_cost = CPU - eval_costrows_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は異なるコストを表します.