MySQL - 使用trace工具来窥探MySQL是如何选择执行计划的
生活随笔
收集整理的這篇文章主要介紹了
MySQL - 使用trace工具来窥探MySQL是如何选择执行计划的
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
文章目錄
- 生猛干貨
- Pre
- 演示Demo
- trace工具使用
- Trace分析
- Trace解讀
- 搞定MySQL
生猛干貨
帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試
Pre
有的時候，明明某個字段有索引，那我們一般認為走索引好一些，結果mysql走了全表掃描 ， 那怎么看mysql是怎么選擇的呢？ 來 今天來看一看MySQL是如何循著合適的執行計劃的？
演示Demo
還是那個老表 employees
CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',PRIMARY KEY (`id`),KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';來看個執行計劃
mysql> EXPLAIN select * from employees where name > 'a'; +----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 100175 | 50 | Using where | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+ 1 row in set推測一下，為何走了全表掃描
MySQL - 索引優化案例實操 # Case 1 : 聯合索引第一個字段用范圍不一定會走索引
name > ‘a’ , 一般都不會用到索引。 如果用name索引需要遍歷name字段聯合索引樹，然后還需要根據遍歷出來的主鍵值去主鍵索引樹里再去查出最終數據，成本比全表掃描還高 。
優化呢？
再來看個執行計劃 ，僅僅把比對的value 換一下
mysql> EXPLAIN select * from employees where name > 'zzz' ; +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 1 | 100 | Using index condition | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set搞得我一臉懵逼。。。。。 這樣也行？
很明顯，第二個 > zzz , key_len=74 = 3 * 24(表定義的varch長度) +2 ， 使用了聯合索引中的name ， Extra也可以知一二，使用了部分索引條件。
那咋辦？ 到底是為啥？ 僅僅是因為value的變化，導致mysql選擇了不同的執行計劃？
trace工具使用
開啟trace工具會影響mysql性能，所以只能臨時分析sql使用，用完之后立即關閉 .
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --開啟trace mysql> select * from employees where name > 'a' order by position; --- 執行SQL mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE; ---查看trace mysql> set session optimizer_trace="enabled=off"; --關閉trace第二條和第三條 一起執行 ，切記
Trace分析
把結果copy出來
三大步
重點呢 就是第二步 優化階段
Trace解讀
{"steps": [{"join_preparation": { ---優化準備工作 "select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"}] /* steps */} /* join_preparation */},{"join_optimization": { ---- 優化階段"select#": 1,"steps": [{"condition_processing": { ---- 條件處理 "condition": "WHERE","original_condition": "(`employees`.`name` > 'a')","steps": [{"transformation": "equality_propagation","resulting_condition": "(`employees`.`name` > 'a')"},{"transformation": "constant_propagation","resulting_condition": "(`employees`.`name` > 'a')"},{"transformation": "trivial_condition_removal","resulting_condition": "(`employees`.`name` > 'a')"}] /* steps */} /* condition_processing */},{"substitute_generated_columns": {} /* substitute_generated_columns */},{"table_dependencies": [ ------表依賴情況分析 {"table": "`employees`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": [] /* depends_on_map_bits */}] /* table_dependencies */},{"ref_optimizer_key_uses": [] /* ref_optimizer_key_uses */},{"rows_estimation": [ --- 預估表的訪問成本 {"table": "`employees`","range_analysis": {"table_scan": { ---全表掃描"rows": 100175, --- 掃描的行數"cost": 20390 ---COST查詢成本} /* table_scan */,"potential_range_indexes": [ --- 潛在的可以使用的索引{"index": "PRIMARY", ---主鍵索引"usable": false, "cause": "not_applicable"},{"index": "idx_name_age_position", ---- 輔助索引"usable": true, "key_parts": ["name","age","position","id"] /* key_parts */}] /* potential_range_indexes */,"setup_range_conditions": [] /* setup_range_conditions */,"group_index_range": {"chosen": false,"cause": "not_group_by_or_distinct"} /* group_index_range */,"analyzing_range_alternatives": { --- 分析各個索引的使用成本"range_scan_alternatives": [{"index": "idx_name_age_position","ranges": ["a < name"] /* ranges */,"index_dives_for_eq_ranges": true,"rowid_ordered": false, --使用該索引獲取的記錄是否按照主鍵排序"using_mrr": false, "index_only": false, --- 是否使用覆蓋索引"rows": 50087, ---索引掃描行數"cost": 60105, ---所用COST成本"chosen": false, ---是否選擇該索引"cause": "cost" ---原因}] /* range_scan_alternatives */,"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */} /* range_analysis */}] /* rows_estimation */},{"considered_execution_plans": [ -----建議執行計劃{"plan_prefix": [] /* plan_prefix */,"table": "`employees`","best_access_path": { ---- 最優訪問路徑 "considered_access_paths": [ ---- 最終選擇的訪問路徑{"rows_to_scan": 100175,"access_type": "scan", --- 訪問類型 scan 即全表掃描"resulting_rows": 100175,"cost": 20388,"chosen": true, ------ true 確定選擇"use_tmp_table": true}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100,"rows_for_plan": 100175,"cost_for_plan": 20388,"sort_cost": 100175,"new_cost_for_plan": 120563,"chosen": true}] /* considered_execution_plans */},{"attaching_conditions_to_tables": {"original_condition": "(`employees`.`name` > 'a')","attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [{"table": "`employees`","attached": "(`employees`.`name` > 'a')"}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{"clause_processing": {"clause": "ORDER BY","original_clause": "`employees`.`position`","items": [{"item": "`employees`.`position`"}] /* items */,"resulting_clause_is_simple": true,"resulting_clause": "`employees`.`position`"} /* clause_processing */},{"reconsidering_access_paths_for_index_ordering": {"clause": "ORDER BY","steps": [] /* steps */,"index_order_summary": {"table": "`employees`","index_provides_order": false,"order_direction": "undefined","index": "unknown","plan_changed": false} /* index_order_summary */} /* reconsidering_access_paths_for_index_ordering */},{"refine_plan": [{"table": "`employees`"}] /* refine_plan */}] /* steps */} /* join_optimization */},{"join_execution": { ----- 階段三 執行SQL "select#": 1,"steps": [{"filesort_information": [{"direction": "asc","table": "`employees`","field": "position"}] /* filesort_information */,"filesort_priority_queue_optimization": {"usable": false,"cause": "not applicable (no LIMIT)"} /* filesort_priority_queue_optimization */,"filesort_execution": [] /* filesort_execution */,"filesort_summary": {"rows": 100003,"examined_rows": 100003,"number_of_tmp_files": 31,"sort_buffer_size": 262056,"sort_mode": "<sort_key, packed_additional_fields>"} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */ }MySQL認為 全表掃描的成本低于索引掃描，所以mysql最終選擇全表掃描 。
同樣的 看看看 zzz的
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --開啟trace mysql> select * from employees where name > 'zzz' order by position; --- 執行SQL mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE; ---查看trace mysql> set session optimizer_trace="enabled=off"; --關閉trace同樣的套路，我相信你也能看出一二
搞定MySQL
總結
以上是生活随笔為你收集整理的MySQL - 使用trace工具来窥探MySQL是如何选择执行计划的的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MySQL - 索引下推 Index C
- 下一篇： MySQL - order by和 gr