第四篇：trace工具查看MySQL如何选择索引

最新推荐文章于 2024-03-10 14:53:42 发布

Jayden　

最新推荐文章于 2024-03-10 14:53:42 发布

阅读量315

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 trace

本文链接：https://blog.csdn.net/qq_42396796/article/details/124774138

版权

MySQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

本文通过实例展示了MySQL在不同数据量和查询条件下的索引使用策略。测试了范围查询、强制走索引、IN和OR操作符的影响，以及LIKE查询的索引下推效果。内容包括全表扫描与索引扫描的成本分析，揭示了MySQL如何根据数据量选择执行计划。

摘要由CSDN通过智能技术生成

一个有趣的案例

我在本地建了两个表（表结构是一样的）user，user_copy，两个表都有一个联合索引idx_name_age_address。user表中共有10w条数据，user_copy中没有数据。

/*
Navicat MySQL Data Transfer

Source Server         : MySQL
Source Server Version : 50714
Source Host           : localhost:3306
Source Database       : db

Target Server Type    : MYSQL
Target Server Version : 50714
File Encoding         : 65001

Date: 2022-05-13 08:07:06
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `address` varchar(20) NOT NULL DEFAULT '' COMMENT '地址',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_address` (`name`,`age`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100003 DEFAULT CHARSET=utf8 COMMENT='用户表';

部分数据如下：

下面我们先看几个测试：

测试1：相同表结构，测试走索引情况及扫描行数（user表10w条数据，user_copy表0条数据）

对比结论：上方两条SQL的区别是user表10w条数据，user_copy表没有数据。可以看出MySQL在范围查询时，如果第一个字段就用范围查找，会根据数据量大小来判断是否要走索引。第一个字段就范围查询，MySQL可能认为结果集很大就直接扫全表了。

测试2：强制走索引与不强制走索引，SQL执行时间

可以看到强制走索引可能查询的行数是5w多行，没有强制走索引的可能查询的行数是10w行，我们关闭查询缓存看看二者的执行时间：

‐‐ 关闭查询缓存
set global query_cache_size=0;
set global query_cache_type=0;
‐‐ 执行时间0.1s
SELECT * FROM user WHERE name > 'linguo';
‐‐ 执行时间0.4s
SELECT * FROM user force index(idx_name_age_address) WHERE name > 'linguo';

对比结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

测试3: 数据量大小对in，or的影响

对比结论：in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

对比4：数据量，对比like ‘kk%’ 效果

对比结论：不论数据量多少，都会走索引，like ‘kk%’ 是范围查询，之所以索引生效是用到了索引下推。

所谓索引下推是MySQL 5.6引入的，它可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数.

trace工具

trace工具是MySQL自带的功能，它可以清楚的看到每条SQL执行时，MySQL计算出执行的cost成本。默认是不打开的，因为极其耗费性能。

我们来看这name > ‘a’ 这条sql的MySQL是如何选择的，下方大家只看cost字段，这是数字越大代表MySQL执行的成本越高。下方有两个cost：

全表扫描：rows10666,cost 20368
走联合索引idx_name_age_address：rows50033，cost60041
虽然全表扫描可能扫描的行数比较多，但是mysql计算出来扫全表比较快，因此上图通过explain看到的结果是选择不走索引。当然这也不是决定的，比如当select的字段都是联合索引中的字段时，这条SQL就会走联合索引（即cost成本比全表低）

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`address` AS `address`,`user`.`create_time` AS `create_time` from `user` where (`user`.`name` > 'a') order by `user`.`address`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`user`.`name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`user`.`name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`user`.`name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`user`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`user`",
                "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": "`user`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 100066,
                    "cost": 20368
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_address",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "address",
                        "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_address",
                        "ranges": [
                          "a < name"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 50033,
                        "cost": 60041,
                        "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": "`user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 100066,
                      "access_type": "scan",
                      "resulting_rows": 100066,
                      "cost": 20366,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100066,
                "cost_for_plan": 20366,
                "sort_cost": 100066,
                "new_cost_for_plan": 120432,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`user`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`user`",
                  "attached": "(`user`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`user`.`address`",
              "items": [
                {
                  "item": "`user`.`address`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`user`.`address`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`user`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "unknown",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`user`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`user`",
                "field": "address"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 100002,
              "examined_rows": 100002,
              "number_of_tmp_files": 31,
              "sort_buffer_size": 261960,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

这里我们再看下name>‘z’下，全表扫描与联合索引的成本。这里为什么使用’a’与’z’ ，原因B+树是有序的，英文字母是通过a~z这个顺序排序。这个时候尽管只是字母不一样，但是成本是截然不同的，name> ‘a’ 意外着扫全表，name> 'z’意味着查询的数据量很小。
看看两个cost：

全表扫描：rows10666,cost 20368
走联合索引idx_name_age_address：rows1，cost2.21
全表扫描的cost成本还是不变，但是联合索引的成本大大降低。cost2.21，奈斯。所以对于索引的理解在于对B+树的理解，在于你心里能不能生成一颗B+树。

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`address` AS `address`,`user`.`create_time` AS `create_time` from `user` where (`user`.`name` > 'z') order by `user`.`address`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`user`.`name` > 'z')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`user`.`name` > 'z')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`user`.`name` > 'z')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`user`.`name` > 'z')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`user`",
                "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": "`user`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 100066,
                    "cost": 20368
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_address",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "address",
                        "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_address",
                        "ranges": [
                          "z < name"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "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": "idx_name_age_address",
                      "rows": 1,
                      "ranges": [
                        "z < name"
                      ] /* 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": "`user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_name_age_address"
                      } /* range_details */,
                      "resulting_rows": 1,
                      "cost": 2.41,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 2.41,
                "sort_cost": 1,
                "new_cost_for_plan": 3.41,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`user`.`name` > 'z')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`user`",
                  "attached": "(`user`.`name` > 'z')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`user`.`address`",
              "items": [
                {
                  "item": "`user`.`address`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`user`.`address`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`user`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_name_age_address",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`user`",
                "pushed_index_condition": "(`user`.`name` > 'z')",
                "table_condition_attached": null
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`user`",
                "field": "address"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 0,
              "examined_rows": 0,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 261960,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}