MySQL 单列索引 vs 组合索引 通过 OPTIMIZER_TRACE 跟踪分析 对比性能 | Eddie'Blog
MySQL 单列索引 vs 组合索引 通过 OPTIMIZER_TRACE 跟踪分析 对比性能

MySQL 单列索引 vs 组合索引 通过 OPTIMIZER_TRACE 跟踪分析 对比性能

eddie 257 2021-05-20

目录

单列索引 vs 组合索引

  • SQL存在多个条件,多个单例索引,会使用索引合并(取交集)
  • 如果出现索引合并,往往说明索引不够合理
  • 如果SQL暂时没有行能问题,暂时可以不管
  • 组合索引要注意索引列顺序 【最左前缀原则

示例语句

-- type=ALL 全表扫描
-- 在没有索引情况下,平均耗时 0.493s 范围
EXPLAIN 
SELECT * FROM salaries 
    WHERE from_date = '1986-06-26' 
    and to_date = '1987-06-26';

创建单列索引

-- 创建单例索引后,平均耗时 0.045s 
CREATE INDEX salaries_from_date_index ON salaries (from_date);
CREATE INDEX salaries_to_date_index ON salaries (to_date);
EXPLAIN关注的字段
字段说明
typeindex_merge索引合并
possible_keyssalaries_from_date_index,
salaries_to_date_index
可能的索引选择
keysalaries_from_date_index,
salaries_to_date_index
实际选择的索引
ExtraUsing intersect(salaries_to_date_index,salaries_from_date_index);
Using where
使用索引交集,使用条件查询
OPTIMIZER_TRACE 跟踪

SQL语句

-- 开启 OPTIMIZER_TRACE
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

-- 查询 salaries,使用索引字段
SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26';

-- 通过 OPTIMIZER_TRACE 查看刚刚 查询 salaries 表的结果 
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE WHERE QUERY LIKE '%salaries%' LIMIT 30;

OPTIMIZER_TRACE 结果分析

  1. 主要部分使用 ## 标记
  2. rows_estimation 块下的内容,
    1. 索引、行数、开销
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`salaries`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`salaries`",
                "field": "from_date",
                "equals": "DATE'1986-06-26'",
                "null_rejecting": false
              },
              {
                "table": "`salaries`",
                "field": "to_date",
                "equals": "DATE'1987-06-26'",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [   ## 主要查看
              {
                "table": "`salaries`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 2709068,   ## 行
                    "cost": 274687     ## 开销
                  } /* table_scan */,
                  "potential_range_indexes": [  ## 分析这张表所有的索引
                    {
                      "index": "PRIMARY",   ## 主键不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "salaries_from_date_index",  ## 可以的 from_date 索引
                      "usable": true,
                      "key_parts": [
                        "from_date",
                        "emp_no"
                      ] /* key_parts */
                    },
                    {
                      "index": "salaries_to_date_index", ## 可以的 to_date 索引
                      "usable": true,
                      "key_parts": [
                        "to_date",
                        "emp_no",
                        "from_date"
                      ] /* 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 */,
                  "skip_scan_range": {   ## MySQL 8.0 后出的
                    "potential_skip_scan_indexes": [
                      {
                        "index": "salaries_from_date_index",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      },
                      {
                        "index": "salaries_to_date_index",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": {  ## 最重要的部分
                    "range_scan_alternatives": [
                      {
                        "index": "salaries_from_date_index",  ## 使用from_date索引
                        "ranges": [
                          "0xda840f <= from_date <= 0xda840f"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 88,              ## 预估扫描 88 行
                        "cost": 66.155,          ## 预估开销 66.155
                        "chosen": true           ## 证明已经在用索引了
                      },
                      {
                        "index": "salaries_to_date_index",  ## 使用to_date索引
                        "ranges": [
                          "0xda860f <= to_date <= 0xda860f"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 86,              ## 预估扫描 86 行
                        "cost": 64.667,          ## 预估开销 64.667
                        "chosen": true           ## 证明已经在用索引了
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {     ## 索引合并
                      "intersecting_indexes": [           ## 有哪些交集索引
                        {
                          "index": "salaries_to_date_index",
                          "index_scan_cost": 1.1034,
                          "cumulated_index_scan_cost": 1.1034,
                          "disk_sweep_cost": 55.312,
                          "cumulated_total_cost": 56.415,
                          "usable": true,
                          "matching_rows_now": 86,
                          "isect_covering_with_this_index": false,
                          "chosen": true   ##  salaries_to_date_index 交集索引
                        },
                        {
                          "index": "salaries_from_date_index",
                          "index_scan_cost": 1.1061,
                          "cumulated_index_scan_cost": 2.2095,
                          "disk_sweep_cost": 0,
                          "cumulated_total_cost": 2.2095,
                          "usable": true,
                          "matching_rows_now": 0.0028,
                          "isect_covering_with_this_index": false,
                          "chosen": true    ##  salaries_from_date_index 交集索引
                        }
                      ] /* intersecting_indexes */,
                      "clustered_pk": {
                        "clustered_pk_added_to_intersect": false,
                        "cause": "no_clustered_pk_index"
                      } /* clustered_pk */,
                      "rows": 1,
                      "cost": 2.2095,   ## 求交集的开销
                      "covering": false,
                      "chosen": true
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "index_roworder_intersect",
                      "rows": 1,
                      "cost": 2.2095,
                      "covering": false,
                      "clustered_pk_scan": false,
                      "intersect_of": [
                        {
                          "type": "range_scan",
                          "index": "salaries_to_date_index",
                          "rows": 86,
                          "ranges": [
                            "0xda860f <= to_date <= 0xda860f AND 0xda840f <= from_date <= 0xda840f"
                          ] /* ranges */
                        },
                        {
                          "type": "range_scan",
                          "index": "salaries_from_date_index",
                          "rows": 88,
                          "ranges": [
                            "0xda840f <= from_date <= 0xda840f"
                          ] /* ranges */
                        }
                      ] /* intersect_of */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.2095,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`salaries`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "salaries_from_date_index",
                      "rows": 88,
                      "cost": 65.501,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "salaries_to_date_index",
                      "rows": 86,
                      "cost": 64.012,
                      "chosen": true
                    },
                    {
                      "rows_to_scan": 1,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "intersect(salaries_to_date_index,salaries_from_date_index)"
                      } /* range_details */,
                      "resulting_rows": 1,
                      "cost": 2.3095,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 2.3095,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`salaries`",
                  "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`salaries`",
                "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
                "final_table_condition   ": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`salaries`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

创建组合索引

-- 删除原来单例索引
ALTER TABLE `employees`.`salaries` 
DROP INDEX `salaries_from_date_index`,
DROP INDEX `salaries_to_date_index`

-- 创建组合索引
ALTER TABLE `employees`.`salaries` 
ADD INDEX `salaries_from_date_to_date_index`(`from_date`, `to_date`)

-- 查看 salaries 有哪些索引
SHOW INDEX FROM `employees`.`salaries`
EXPLAIN关注的字段
-- type=ref 
EXPLAIN 
SELECT * FROM salaries 
    WHERE from_date = '1986-06-26' 
    and to_date = '1987-06-26';
字段说明
typeref当满足索引的最左前缀规则,或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行,性能也是不错的
possible_keyssalaries_from_date_to_date_index可能的索引选择
keysalaries_from_date_to_date_index实际选择的索引
Extranull使用索引交集,使用条件查询

ref会比index_merge好

对比单例索引查询耗时
-- 单例:0.045s 
-- 组合:0.043s 
SELECT * FROM salaries 
    WHERE from_date = '1986-06-26' 
    and to_date = '1987-06-26';

会发现其实差异并不大,主要是因为数据量不大,索引的交集不大而查询耗时没有太大的差异

OPTIMIZER_TRACE 跟踪

SQL语句

-- 开启 OPTIMIZER_TRACE
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

-- 查询 salaries,使用索引字段
SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26';

-- 通过 OPTIMIZER_TRACE 查看刚刚 查询 salaries 表的结果 
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE WHERE QUERY LIKE '%salaries%' LIMIT 30;

OPTIMIZER_TRACE 结果分析

  1. 主要部分使用 ## 标记
  2. rows_estimation 块下的内容,
    1. 索引、行数、开销
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`salaries`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`salaries`",
                "field": "from_date",
                "equals": "DATE'1986-06-26'",
                "null_rejecting": false
              },
              {
                "table": "`salaries`",
                "field": "to_date",
                "equals": "DATE'1987-06-26'",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [       ## 主要查看
              {
                "table": "`salaries`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 2709068,          ## 行     vs 单例:2709068
                    "cost": 273561            ## 开销   vs 单例:274687
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "salaries_from_date_to_date_index",
                      "usable": true,
                      "key_parts": [
                        "from_date",
                        "to_date",
                        "emp_no"
                      ] /* 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 */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "salaries_from_date_to_date_index",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "salaries_from_date_to_date_index",
                        "ranges": [
                          "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 86,              ## 预估扫描 86 行      vs 单例:88
                        "cost": 47.963,          ## 预估开销 47.963     vs 单例:66.155
                        "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": "salaries_from_date_to_date_index",
                      "rows": 86,
                      "ranges": [
                        "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 86,
                    "cost_for_plan": 47.963,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`salaries`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "salaries_from_date_to_date_index",
                      "rows": 86,
                      "cost": 47.501,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "salaries_from_date_to_date_index"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 86,
                "cost_for_plan": 47.501,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`salaries`",
                  "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`salaries`",
                "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
                "final_table_condition   ": null
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`salaries`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

# MySQL