• mysql的trace追踪SQL工具，进行sql优化

    trace是MySQL5.6版本后提供的SQL跟踪工具，通过使用trace可以让我们明白optimizer（优化器）如何选择执行计划。

    注意：开启trace工具会影响mysql性能，所以只适合临时分析sql使用，用完之后请立即关闭。

    测试数据脚本：

    1. DROP TABLE IF EXISTS `t_student`;
    2. CREATE TABLE `t_student` (
    3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    4.   `std_name` varchar(30) NOT NULL,
    5.   `age` tinyint(3) unsigned NOT NULL,
    6.   `class_id` int(11) unsigned NOT NULL,
    7.   `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    8.   PRIMARY KEY (`id`),
    9.   KEY `idx_std_age` (`age`),
    10.   KEY `idx_std_name_age_class` (`std_name`,`age`,`class_id`) USING BTREE
    11. ) ENGINE=InnoDB AUTO_INCREMENT=100766 DEFAULT CHARSET=utf8mb4;
    12.  
    13. --添加测试数据的存储过程
    14.  
    15. DROP PROCEDURE IF EXISTS proc_insert_student; 
    16. DELIMITER $$
    17. CREATE PROCEDURE proc_insert_student()        
    18. BEGIN
    19.   DECLARE i INT;                   
    20.   SET i=1;                          
    21.   WHILE i<=100000 DO                 
    22.     INSERT INTO t_student(std_name,age,class_id) VALUES(CONCAT('Li Lei',i), (i MOD 120)+1 ,(i MOD 3)+1);   
    23.     SET i=i+1;                       
    24.   END WHILE;
    25. END $$
    26.  
    27. -- 执行存储过程
    28. call proc_insert_student();

    trace工具用法

    一.查看trace开关状态，默认关闭的

    show variables like 'optimizer_trace';

    二.开启face

    1.会话级别临时开启，只在当前会话生效。

    set session optimizer_trace="enabled=on",end_markers_in_json=on;

    2.永久开启（重启失效）

    注意用完关闭

    set optimizer_trace="enabled=on";

    三.用法

    1.在查询sql后加上固定sql，例：

    1. set session optimizer_trace="enabled=on",end_markers_in_json=on;  
    2. select * from t_student where std_name > 'a' order by age;
    3. SELECT * FROM information_schema.OPTIMIZER_TRACE;

    2.查看执行计划，并未使用到联合索引，联合索引name使用的范围查询，一般都不会用到索引。 如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高 。

    EXPLAIN SELECT * FROM t_student WHERE std_name > 'a' ORDER BY age;

    EXPLAIN SELECT std_name,age,class_id FROM t_student WHERE std_name > 'a' ORDER BY age;

    3。如果查询是联合索引字段，那就使用了覆盖索引，这样的name范围查询，联合索引才会使用到，这样只需要遍历name字段的联合索引树就能拿到所有结果，叶子节点只存放二级索引的数据，这就不用回表操作。

    TRACE列的json数据拷贝出来查看。主要是看cost值，成本

    SELECT * FROM information_schema.OPTIMIZER_TRACE;
    1. {
    2.   "steps": [
    3.     {
    4.       "join_preparation": {  -- 第一阶段：SQL准备阶段，格式化sql
    5.         "select#": 1,
    6.         "steps": [
    7.           {
    8.             "expanded_query": "/* select#1 */ select `t_student`.`id` AS `id`,`t_student`.`std_name` AS `std_name`,`t_student`.`age` AS `age`,`t_student`.`class_id` AS `class_id`,`t_student`.`gmt_create` AS `gmt_create` from `t_student` where (`t_student`.`std_name` > 'a') order by `t_student`.`age`"
    9.           }
    10.         ] /* steps */
    11.       } /* join_preparation */
    12.     },
    13.     {
    14.       "join_optimization": {  -- 第二阶段：SQL优化阶段
    15.         "select#": 1,
    16.         "steps": [
    17.           {
    18.             "condition_processing": {  -- 条件处理
    19.               "condition": "WHERE",
    20.               "original_condition": "(`t_student`.`std_name` > 'a')",
    21.               "steps": [
    22.                 {
    23.                   "transformation": "equality_propagation",
    24.                   "resulting_condition": "(`t_student`.`std_name` > 'a')"
    25.                 },
    26.                 {
    27.                   "transformation": "constant_propagation",
    28.                   "resulting_condition": "(`t_student`.`std_name` > 'a')"
    29.                 },
    30.                 {
    31.                   "transformation": "trivial_condition_removal",
    32.                   "resulting_condition": "(`t_student`.`std_name` > 'a')"
    33.                 }
    34.               ] /* steps */
    35.             } /* condition_processing */
    36.           },
    37.           {
    38.             "substitute_generated_columns": {
    39.             } /* substitute_generated_columns */
    40.           },
    41.           {
    42.             "table_dependencies": [  -- 表依赖详情
    43.               {
    44.                 "table": "`t_student`",
    45.                 "row_may_be_null": false,
    46.                 "map_bit": 0,
    47.                 "depends_on_map_bits": [
    48.                 ] /* depends_on_map_bits */
    49.               }
    50.             ] /* table_dependencies */
    51.           },
    52.           {
    53.             "ref_optimizer_key_uses": [
    54.             ] /* ref_optimizer_key_uses */
    55.           },
    56.           {
    57.             "rows_estimation": [  -- 预估表的访问成本
    58.               {
    59.                 "table": "`t_student`",
    60.                 "range_analysis": {
    61.                   "table_scan": {   -- 全表扫描
    62.                     "rows": 100300,  -- 行数
    63.                     "cost": 20351  -- 查询消耗
    64.                   } /* table_scan */,
    65.                   "potential_range_indexes": [  -- 查询可能使用的索引
    66.                     {
    67.                       "index": "PRIMARY",  -- 主键索引
    68.                       "usable": false,  -- 未使用
    69.                       "cause": "not_applicable"  -- 原因：不适合
    70.                     },
    71.                     {
    72.                       "index": "idx_std_age",  -- age索引
    73.                       "usable": false,  -- 未使用
    74.                       "cause": "not_applicable"  -- 原因：不适合
    75.                     },
    76.                     {
    77.                       "index": "idx_std_name_age_class",  -- stdname,age,class的组合索引
    78.                       "usable": true,  -- 使用
    79.                       "key_parts": [
    80.                         "std_name",
    81.                         "age",
    82.                         "class_id",
    83.                         "id"
    84.                       ] /* key_parts */
    85.                     }
    86.                   ] /* potential_range_indexes */,
    87.                   "setup_range_conditions": [
    88.                   ] /* setup_range_conditions */,
    89.                   "group_index_range": {  -- group 用到的索引
    90.                     "chosen": false,  -- 未使用
    91.                     "cause": "not_group_by_or_distinct"  -- 原因：未使用group by 或者 distinct
    92.                   } /* group_index_range */,
    93.                   "analyzing_range_alternatives": {   -- 分析各个索引使用成本
    94.                     "range_scan_alternatives": [
    95.                       {
    96.                         "index": "idx_std_name_age_class",
    97.                         "ranges": [
    98.                           "a < std_name"  -- 索引使用范围
    99.                         ] /* ranges */,
    100.                         "index_dives_for_eq_ranges": true,
    101.                         "rowid_ordered": false,  -- 使用该索引获取的记录是否按照主键排序
    102.                         "using_mrr": false,
    103.                         "index_only": false,  -- 是否使用覆盖索引
    104.                         "rows": 50150,  -- 索引扫描行数
    105.                         "cost": 60181,   -- 索引使用成本
    106.                         "chosen": false,  -- 是否选择该索引：否
    107.                         "cause": "cost"  -- 原因：消耗
    108.                       }
    109.                     ] /* range_scan_alternatives */,
    110.                     "analyzing_roworder_intersect": {  -- 分析使用索引合并的成本
    111.                       "usable": false,
    112.                       "cause": "too_few_roworder_scans"
    113.                     } /* analyzing_roworder_intersect */
    114.                   } /* analyzing_range_alternatives */
    115.                 } /* range_analysis */
    116.               }
    117.             ] /* rows_estimation */
    118.           },
    119.           {
    120.             "considered_execution_plans": [  -- 分析出的执行计划
    121.               {
    122.                 "plan_prefix": [
    123.                 ] /* plan_prefix */,
    124.                 "table": "`t_student`",
    125.                 "best_access_path": {  -- 最优访问路径
    126.                   "considered_access_paths": [  --分析出的最终访问路径
    127.                     {
    128.                       "rows_to_scan": 100300,
    129.                       "access_type": "scan",  -- 访问类型：为scan，全表扫描
    130.                       "resulting_rows": 100300,
    131.                       "cost": 20349,
    132.                       "chosen": true,  -- 确定选择
    133.                       "use_tmp_table": true
    134.                     }
    135.                   ] /* considered_access_paths */
    136.                 } /* best_access_path */,
    137.                 "condition_filtering_pct": 100,
    138.                 "rows_for_plan": 100300,
    139.                 "cost_for_plan": 20349,
    140.                 "sort_cost": 100300,
    141.                 "new_cost_for_plan": 120649,
    142.                 "chosen": true
    143.               }
    144.             ] /* considered_execution_plans */
    145.           },
    146.           {
    147.             "attaching_conditions_to_tables": {   -- 为查询的表添加条件
    148.               "original_condition": "(`t_student`.`std_name` > 'a')",
    149.               "attached_conditions_computation": [
    150.               ] /* attached_conditions_computation */,
    151.               "attached_conditions_summary": [    -- 添加条件结果
    152.                 {
    153.                   "table": "`t_student`",
    154.                   "attached": "(`t_student`.`std_name` > 'a')"
    155.                 }
    156.               ] /* attached_conditions_summary */
    157.             } /* attaching_conditions_to_tables */
    158.           },
    159.           {
    160.             "clause_processing": {   -- order by 处理
    161.               "clause": "ORDER BY",
    162.               "original_clause": "`t_student`.`age`",
    163.               "items": [
    164.                 {
    165.                   "item": "`t_student`.`age`"
    166.                 }
    167.               ] /* items */,
    168.               "resulting_clause_is_simple": true,
    169.               "resulting_clause": "`t_student`.`age`"
    170.             } /* clause_processing */
    171.           },
    172.           {
    173.             "reconsidering_access_paths_for_index_ordering": {    -- 重构索引处理顺序
    174.               "clause": "ORDER BY",
    175.               "steps": [
    176.               ] /* steps */,
    177.               "index_order_summary": {
    178.                 "table": "`t_student`",
    179.                 "index_provides_order": false,
    180.                 "order_direction": "undefined",
    181.                 "index": "unknown",
    182.                 "plan_changed": false
    183.               } /* index_order_summary */
    184.             } /* reconsidering_access_paths_for_index_ordering */
    185.           },
    186.           {
    187.             "refine_plan": [
    188.               {
    189.                 "table": "`t_student`"
    190.               }
    191.             ] /* refine_plan */
    192.           }
    193.         ] /* steps */
    194.       } /* join_optimization */
    195.     },
    196.     {
    197.       "join_execution": {    -- 第三阶段：SQL执行阶段
    198.         "select#": 1,
    199.         "steps": [
    200.           {
    201.             "filesort_information": [
    202.               {
    203.                 "direction": "asc",
    204.                 "table": "`t_student`",
    205.                 "field": "age"
    206.               }
    207.             ] /* filesort_information */,
    208.             "filesort_priority_queue_optimization": {
    209.               "usable": false,
    210.               "cause": "not applicable (no LIMIT)"
    211.             } /* filesort_priority_queue_optimization */,
    212.             "filesort_execution": [
    213.             ] /* filesort_execution */,
    214.             "filesort_summary": {
    215.               "rows": 100000,
    216.               "examined_rows": 100000,
    217.               "number_of_tmp_files": 14,
    218.               "sort_buffer_size": 262016,
    219.               "sort_mode": ""
    220.             } /* filesort_summary */
    221.           }
    222.         ] /* steps */
    223.       } /* join_execution */
    224.     }
    225.   ] /* steps */
    226. }

    MySQL认为 全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描，

    4.关闭trace

     set session optimizer_trace="enabled=off";

  • 相关阅读:
    【Excel函数】Trim函数删除前后多余的空格
    Kubernetes 集群部署 Prometheus 和 Grafana
    gitlab数据备份和恢复
    win10部署 Mistral-7B 文本生成模型
    CompletableFuture-CompletionStage接口源码分析和四大静态方法初讲
    1985-2020（8个版次）全球地表覆盖下载与介绍
    渗透测试之内核安全系列课程：Rootkit技术初探（三）
    Laravel 的事件监听器与服务提供者和服务容器的二三事
    如何为linux kernel贡献代码
    自动融合，驰骋海外丨跨境电商YescomUSA携手云扩实现一站式自动化服务
  • 原文地址：https://blog.csdn.net/fengge55/article/details/136602068