MYSQL sql執行過程的一些跟蹤分析(二.mysql優化器追蹤分析)
以下 left join語句，d表與s表關聯，當where條件在d.deptid上時，s表無法走索引。因此通過開啟trace方式做一些追蹤。 root@(none) 09:20:20>explain SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.DEPTID = '00001111'; +----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+ | 1 | SIMPLE | d | NULL | const | PRIMARY,INDEX_DEPARTMENT_5 | PRIMARY | 130 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ALL | NULL | NULL | NULL | NULL | 978629 | 100.00 | Using where | +----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+
開啟optimizer_trace：
set optimizer_trace='enabled=on';
set optimizer_trace_max_mem_size=1000000;
set end_markers_in_json=on;
執行語句
select * from information_schema.optimizer_trace\G;
root@(none) 09:39:58> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.DEPTID = '00001111'
TRACE: {
"steps": [
#準備階段
{
"join_preparation": {
"select#": 1,
"steps": [
{
#expanded_query，解析查詢語句，"*" 轉換成欄位，left join on 處轉化成on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))))
"expanded_query": "/* select#1 */ select `SSS`.`d`.`Organid` AS `Organid`,。。。`s`.`Status` AS `Status`,`ppp`.`s`.`Stylecategoryid` AS `Stylecategoryid`,`ppp`.`s`.`Turnontime` AS `Turnontime` from (`SSS`.`department` `d` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4)))) where (`SSS`.`d`.`Deptid` = '00001111')"
},
{
#轉化成的nested join語句：
"transformations_to_nested_joins": {
"transformations": [
"parenthesis_removal"
] /* transformations */,
"expanded_query": "/* select#1 */ select `SSS`.`d`.`Organid`。。。 `SSS`.`d`.`Guidecode` AS `Guidecode`,`SSS`.`d`.`Createdate` AS `Createdate`,`SSS`.`d`.`Plateformuser` AS `Plateformuser`,`SSS`.`d`.`Plateformdept` AS `Plateformdept`,`SSS`.`d`.`Agentuser` AS `Agentuser`,`SSS`.`d`.`Agentdept` AS `Agentdept`,`SSS`.`d`.`Shopstatus` AS `Shopstatus`,`SSS`.`d`.`Deptshortname` AS `Deptshortname`,`SSS`.`d`.`Storetype` AS `Storetype`,`SSS`.`d`.`Depttype` AS `Depttype`,`ppp`.`s`.`Shopid` AS `Shopid`,`ppp`.`s`.`Objectid` AS `Objectid`,`ppp`.`s`.`Shopname` AS `Shopname`,`ppp`Tel`,`ppp`.`s`.`Introduce` AS `Introduce`,`ppp`.`s`.`Industry` AS `Industry`,`ppp`.`s`.`Address` AS `Address`,`ppp`.`s`.`Shop360image` AS `Shop360image`,`ppp`.`s`.`Domain` AS `Domain`,`ppp`.`s`.`Organid` AS `Organid`,`ppp`.`s`.`Deptid` AS `Deptid`,`ppp`.`s`.`Brandids` AS `Brandids`,`ppp`.`s`.`Extdata` AS `Extdata`,`ppp`.`s`.`Ranking` AS `Ranking`,`ppp`.`s`.`Isdelete` AS `Isdelete`,`ppp`.`s`.`District` AS `District`,`ppp`.`s`.`City` AS `City`,`ppp`.`s`.`Province` AS `Province`,`ppp`.`s`.`Phone` AS `Phone`,`ppp`.`s`.`Watermarkimage` AS `Watermarkimage`,`ppp`.`s`.`Drawingimage` AS `Drawingimage`,`ppp`.`s`.`Contactuser` AS `Contactuser`,`ppp`.`s`.`Panoloadingimage` AS `Panoloadingimage`,`ppp`.`s`.`Lngandlat` AS `Lngandlat`,`ppp`.`s`.`Createtime` AS `Createtime`,`ppp`.`s`.`Shoptype` AS `Shoptype`,`ppp`.`s`.`Status` AS `Status`,`ppp`.`s`.`Stylecategoryid` AS `Stylecategoryid`,`ppp`.`s`.`Turnontime` AS `Turnontime` from `SSS`.`department` `d` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))) where (`SSS`.`d`.`Deptid` = '00001111')"
} /* transformations_to_nested_joins */
}
] /* steps */
} /* join_preparation */
},#準備階段結束
{
#優化階段：
"join_optimization": {
"select#": 1,
"steps": [
{
#處理where條件部分，化簡條件：
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`SSS`.`d`.`Deptid` = '00001111')",---原始條件
"steps": [
{
"transformation": "equality_propagation", ----等式處理
"resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
},
{
"transformation": "constant_propagation",-----常量處理
"resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
},
{
"transformation": "trivial_condition_removal",----去除多餘無關的條件處理
"resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
}
] /* steps */
} /* condition_processing */
},#結束，因為這裡已經夠簡化了，所以三次處理後都是同樣的。
{
#替代產生的欄位
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
#表依賴關係檢查
"table_dependencies": [
{
"table": "`SSS`.`department` `d`", ------表d
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`ppp`.`shop` `s`", --------表s
"row_may_be_null": true,
"map_bit": 1,
"depends_on_map_bits": [
0
] /* depends_on_map_bits */
}
] /* table_dependencies */
}, #表依賴關係檢查結束
{#找出可使用索引的欄位：
"ref_optimizer_key_uses": [
{
"table": "`SSS`.`department` `d`",
"field": "Deptid", ----------可用的是Deptid
"equals": "'00001111'",
"null_rejecting": false ---
},
{
"table": "`SSS`.`department` `d`",
"field": "Deptid",
"equals": "'00001111'",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{#評估每個表單表訪問行數及相應代價。
"rows_estimation": [
{
"table": "`SSS`.`department` `d`",
"rows": 1, ---返回1行
"cost": 1, ---代價為1
"table_type": "const", ---d表使用的方式是const，即根據主鍵索引獲取
"empty": false
},
{
"table": "`ppp`.`shop` `s`",
"table_scan": { -------s表直接使用全表掃描
"rows": 978662, ------掃描978662行
"cost": 8109 ------代價為8109
} /* table_scan */
}
] /* rows_estimation */
},
{#評估執行計劃，這裡考慮兩表連線
"considered_execution_plans": [
{
"plan_prefix": [------------------執行計劃的字首，這裡是d表，因為是left join 我認為指的應該是驅動表的意思
"`SSS`.`department` `d`"
] /* plan_prefix */,
"table": "`ppp`.`shop` `s`",
"best_access_path": {-------最優訪問路徑
"considered_access_paths": [考慮的訪問路徑
{
"rows_to_scan": 978662,---掃描978662行
"access_type": "scan",--------全表掃描的方式
"resulting_rows": 978662,
"cost": 203841,----------使用代價
"chosen": true-------選中
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,條件過濾率100%，指的是這裡與上一個表進行行過濾的行數
"rows_for_plan": 978662,------執行計劃的掃描行數978662
"cost_for_plan": 203841,-------執行計劃的cost203841
"chosen": true---------選中
}
] /* considered_execution_plans */
},
{#檢查帶常量表的條件
"condition_on_constant_tables": "('00001111' = '00001111')",
"condition_value": true
},
{ #將常量條件作用到表，這裡主要是將d表的中的deptid條件作用到s表的deptid
"attaching_conditions_to_tables": {
"original_condition": "('00001111' = '00001111')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`ppp`.`shop` `s`",
"attached": "<if>(is_not_null_compl(s), ('00001111' = convert(`ppp`.`s`.`Deptid` using utf8mb4)), true)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`ppp`.`shop` `s`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
以上優化器的主要步驟：
1.join_preparation ：準備階段，包查詢語句轉換，轉換成巢狀迴圈語句等
expanded_query
transformations_to_nested_joins
2.join_optimization ：優化階段，包括以下主要階段
condition_processing ：處理where條件部分，主要包括等式處理、常量處理、多餘條件處理
table_dependencies ：表依賴檢查
ref_optimizer_key_uses ：評估可用的索引
rows_estimation ：評估訪問單表的方式，及掃描的行數與代價
considered_execution_plans ：評估最終可使用的執行計劃
condition_on_constant_tables :檢查帶常量表的條件
attaching_conditions_to_tables ：將常量條件作用到表
refine_plan 改進計劃，不理解
3.join_execution ：執行階段
通過以上可以看錯，當優化器一開始對優化器進行評估時就直接選擇了全表掃描的方式，即是說此時優化器直接忽視了s表已有的索引IND_SHOP_DEPTID。
我們將以下的d.DEPTID = '00001111' 換成s.DEPTID = '00001111',發現其可以選擇了索引，此時s表看起來做了驅動表。
SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE s.DEPTID = '00001111'; root@SSS 04:28:39>explain SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE s.DEPTID = '00001111'; +----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s | NULL | ref | IND_SHOP_DEPTID | IND_SHOP_DEPTID | 99 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | d | NULL | eq_ref | PRIMARY,INDEX_DEPARTMENT_5 | PRIMARY | 130 | func | 1 | 100.00 | Using where | +----+-------------+-------+------------+--------+----------------------------+-----------------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
追蹤優化器過程：
1.在ref_optimizer_key_uses 過程找到s表可以通過"'00001111'"走索引，並且通過"Deptid"等值訪問
2.在rows_estimation過程中s表選擇IND_SHOP_DEPTID的cost最低。
3.在considered_execution_plans過程選擇IND_SHOP_DEPTID的訪問路徑，並訪問方式是ref。
{
"ref_optimizer_key_uses": [
{
"table": "`SSS`.`department` `d`",
"field": "Deptid",
"equals": "convert(`ppp`.`s`.`Deptid` using utf8mb4)",
"null_rejecting": false
},
{
"table": "`SSS`.`department` `d`",
"field": "Deptid",
"equals": "convert(`ppp`.`s`.`Deptid` using utf8mb4)",
"null_rejecting": false
},
{
"table": "`ppp`.`shop` `s`",
"field": "Deptid",
"equals": "'00001111'",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`SSS`.`department` `d`",
"table_scan": {
"rows": 911858,
"cost": 7212
} /* table_scan */
},
{
"table": "`ppp`.`shop` `s`",
"range_analysis": {
"table_scan": {
"rows": 959814,
"cost": 200074
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "IND_SHOP_DEPTID",
"usable": true,
"key_parts": [
"Deptid",
"Shopid"
] /* key_parts */
},
{
"index": "IND_SHOP_DOMAIN",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "IND_SHOP_DEPTID",
"ranges": [
"00001111 <= Deptid <= 00001111"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "IND_SHOP_DEPTID",
"rows": 1,
"ranges": [
"00001111 <= Deptid <= 00001111"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`ppp`.`shop` `s`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "IND_SHOP_DEPTID",
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "IND_SHOP_DEPTID"
} /* range_details */,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.2,
"rest_of_plan": [
{
"plan_prefix": [
"`ppp`.`shop` `s`"
] /* plan_prefix */,
"table": "`SSS`.`department` `d`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 1.2,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"access_type": "scan",
"cost": 189584,
"rows": 911858,
"chosen": false,
"cause": "cost"
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 2.4,
"chosen": true
}
] /* rest_of_plan */
}
] /* considered_execution_plans */
至此，仍然無法搞清楚為何上一條sql當優化器一開始對優化器進行評估時就直接選擇了對s表全表掃描的方式。後面將繼續分析。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-2565095/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MYSQL sql執行過程的一些跟蹤分析(一)MySql
- MySQL 5.7 跟蹤優化器MySql優化
- mysql如何跟蹤執行的sql語句MySql
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- linux strace追蹤mysql執行語句LinuxMySql
- 使用SQL_TEACE的跟蹤檔案分析執行計劃SQL
- 藉助10053跟蹤事件理解SQL執行過程事件SQL
- 【MySQL】如何對SQL語句進行跟蹤（trace）？MySql
- SQL追蹤和事件追蹤SQL事件
- 跟蹤客戶端執行的SQL客戶端SQL
- 使用sqltrace跟蹤session執行的sqlSQLSession
- SQL調整優化與10053跟蹤分析一例SQL優化
- SQL調整優化與10053跟蹤分析一例(zt)SQL優化
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- sql執行過程分析SQL
- 使用10046事件跟蹤分析執行計劃事件
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- SQLServer進行SQL跟蹤SQLServer
- 程式碼效能分析-Dottrace跟蹤程式碼執行時間
- 追蹤應用程式所執行的sql語句SQL
- 跟蹤執行命令T
- Mysql慢SQL分析及優化MySql優化
- 基於行跟蹤的ROWDEPENDENCIES實現資訊變化跟蹤
- 做一個位元組碼追蹤器，從內部理解 Python 的執行過程Python
- mysql執行sql語句過程MySql
- 使用xhprof進行線上PHP效能追蹤及分析PHP
- tkprof: 分析ORACLE跟蹤檔案Oracle
- 動態跟蹤分析VB程式
- MySQL語句執行分析（二）MySql
- SQLNET跟蹤tnsping過程SQL
- PHP 效能追蹤及分析工具（XHPROF）PHP
- golang 程式記憶體追蹤、分析Golang記憶體
- Mapreduce(二)：MR的執行過程分析
- 用 Explain 命令分析 MySQL 的 SQL 執行AIMySql
- MySQL 中一條 sql 的執行過程MySql
- MySQL探祕(二)：SQL語句執行過程詳解MySql
- PostgreSQL 原始碼解讀（15）- Insert語句(執行過程跟蹤)SQL原始碼
- 演算法分析__遞迴跟蹤演算法遞迴