[db] explian

[Alice52]

1. explain sql

   -- analyze/format=tree
   explain format=json    SELECT jud.id FROM all_star_judgement jud
   left join account_member am on am.inserted_time = jud.inserted_time;

   

   • filtered 越小越好, if rows is 1000 and filtered is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500.

2. explain 是数据概况

   SELECT * FROM INFORMATION_SCHEMA.TABLES
   WHERE Table_schema="mc-fb"
   AND `TABLE_NAME`="all_star_judgement"
   OR `TABLE_NAME`="account_member"
   or `TABLE_NAME`='account_member_follower';

3. json result

   {
       "query_block": {
           "select_id": 1,
           "cost_info": {
               "query_cost": "9787081287.50"
           },
           "nested_loop": [
               {
                   "table": {
                       "table_name": "jud",
                       "access_type": "ALL",
                       "rows_examined_per_scan": 493173,
                       "rows_produced_per_join": 493173,
                       "filtered": "100.00",
                       "cost_info": {
                           "read_cost": "2741.17",
                           "eval_cost": "49317.30",
                           "prefix_cost": "52058.47",
                           "data_read_per_join": "4G"
                       },
                       "used_columns": [
                           "id",
                           "inserted_time"
                       ]
                   }
               },
               {
                   "table": {
                       "table_name": "am",
                       "access_type": "ALL",
                       "rows_examined_per_scan": 198450,
                       "rows_produced_per_join": 978 7018 1850,
                       "filtered": "100.00",
                       "using_join_buffer": "hash join",
                       "cost_info": {
                           "read_cost": "11044.03",
                           "eval_cost": "9787018185.00",
                           "prefix_cost": "9787081287.50",
                           "data_read_per_join": "1P"
                       },
                       "used_columns": [
                           "inserted_time"
                       ],
                       "attached_condition": "<if>(is_not_null_compl(am), (`mc-fb`.`am`.`inserted_time` = `mc-fb`.`jud`.`inserted_time`), true)"
                   }
               }
           ]
       }
   }

   

   • cost_info: min{prefix_cost}
   • read_cost: unit
   • eval_cost: row's relative
   • prefix_cost: read_cost + eval_cost
   • data_read_per_join: space
   • rows_examined_per_scan: rows
   • rows_produced_per_join: rows, 该 join 参与之后的最终会产生的行数
   • attached_condition: 表示join之后用于过滤的条件

[Alice52]

data_read_per_join 值得计算

1. 读取一行的空间 * 读取的行数
   • 一行的空间空间值: explain format=json SELECT * FROM all_star_judgement jud where jud.id = 1
   • 一行的空间空间值的具体计算公式: varchar(100) 在 utf8mb4 下表示可以存放100个字符(汉字，字母, 符号), 每个字符占 4 字节, 所以需要 100 * 4 = 400 字节
2. 读取该表时累计的数量

rows_examined_per_scan & eval_cost 存在一个默认为10倍的关系

1. mysql.cost 表配置的