尼恩说在前面

在40岁老架构师 尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团、蚂蚁、得物的面试资格，遇到很多很重要的相关面试题：

Mysql如何选择最优 执行计划，为什么？

《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请到文末公号【技术自由圈】获取

本文目录

- 尼恩说在前面

- 一、什么是执行计划（MySQL Execution Plan）？

- 二、MySQL 优化器的作用

    -1. 解析器（Parser）

    -2. 优化器（Optimizer）

    -3. 执行器（Executor）

    -三个核心组件之间的交互流程

- 三、一条完成的sql查询语句 执行流程 ，如下所示：

- 四、影响执行计划选择的因素

 - 因素1：每个执行计划的成本

 - 因素2：索引是否存在？

 - 因素3：复合索引的是否能最左匹配?

 - 因素4：连接类型和顺序？

 - 因素5：连接条件的选择性？

 - 因素6：数据分布情况？

 - 其他的考察因素：如表的数据量和增长趋势

- 五、通过EXPLAIN 查看每个执行计划的成本

- 六、 实操：通过执行计划查看每个执行计划的成本

 - （1）使用二级索引的执行计划 和执行成本

 - （2）执行成本的介绍

 - （3）禁用二级索引后的执行计划  和执行成本

- 两条执行计划的对比

- 五、优化执行计划的方法

- 尼恩架构团队的塔尖 sql 面试题

- 说在最后：有问题找老架构取经‍

一、什么是执行计划（MySQL Execution Plan）？

二、MySQL 优化器的作用

• 解析器（Parser）
• 优化器（Optimizer）
• 执行器（Executor）。

1. 解析器（Parser）

• SQL 词法分析：解析器首先对 SQL 语句进行词法分析，将 SQL 语句分割成多个“单词”或“标记”，如表名、列名、关键字等。
• 语法分析：接着，解析器会根据 SQL 语法规则生成对应的解析树（Parse Tree），用来描述 SQL 语句的逻辑结构。这个过程检查 SQL 语句的语法是否正确。
• 语义分析：确认 SQL 语句中涉及的数据库对象是否存在（比如表名、字段名是否有效），并且检查权限。

2. 优化器（Optimizer）

• 逻辑优化：优化器会对 SQL 语句进行逻辑优化，比如 SQL 语句重写、消除冗余操作、合并重复条件、重新排列 WHERE 子句中的条件等。
• 物理优化：在物理优化阶段，优化器会选择最优的访问路径和执行顺序。例如，它会决定使用哪种索引（如果有多个索引可选），是否做全表扫描，如何连接多张表（选择嵌套循环、哈希连接或排序合并连接等）。
• 成本估算：优化器会基于数据库的统计信息（例如表的大小、索引的选择性等）来估算不同执行计划的成本，选择代价最低的执行方案。

3. 执行器（Executor）

• 权限检查：在执行之前，执行器会首先检查用户是否有权限执行相应的操作。如果没有权限，则返回错误信息。
• 执行执行计划：执行器根据生成的执行计划，依次调用存储引擎的接口来执行具体的操作。例如，如果是查询操作，执行器会调用存储引擎来读取相应的数据；如果是插入操作，执行器则会调用存储引擎来插入数据。
• 结果返回：执行器根据查询的结果，将数据以合适的格式返回给客户端。如果涉及多个步骤（如 JOIN 操作），执行器会协调各个步骤的执行，并组合最终的结果集。

三个核心组件之间的交互流程

1. 解析器：SQL 语句转换为解析树。
2. 优化器：生成最优的执行计划。
3. 执行器：根据计划调用存储引擎执行操作并返回结果。

三：一条完成的sql查询语句 执行流程 ，如下所示：

• 根据sql的结构生成不同的执行计划，并选择一个最优的计划 是MySQL优化器 的主要任务。
• 中选择一个最优的计划， 这是MySQL执行器 的主要任务。

四、影响执行计划选择的因素

因素1：每个执行计划的成本

因素2：索引是否存在？

因素3：复合索引的是否能最左匹配?

因素4：连接类型和顺序？

因素5：连接条件的选择性？

因素6：数据分布情况？

其他的考察因素：如表的数据量和增长趋势

五、通过EXPLAIN 查看每个执行计划的成本

EXPLAIN SELECT * FROM users JOIN orders ON users.user_id = orders.user_id;

• id列：表示查询中每个 SELECT 子句的标识符。如果是简单的单表查询，id通常为 1。在复杂的子查询或连接查询中，id可以帮助区分不同的子查询或连接部分。
• select_type列：描述了查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。不同的查询类型可能会有不同的执行计划。
• table列：显示查询涉及的表的名称。
• type列：表示表的访问类型，这是评估执行计划效率的一个重要指标。常见的访问类型有ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）等。一般来说，eq_ref和ref的效率较高，而ALL效率较低。
• possible_keys列：显示查询可能使用的索引。这只是一个参考，优化器可能会根据实际情况选择不使用其中的某些索引。
• key列：实际使用的索引。如果这个列显示为NULL，表示没有使用索引，可能是因为优化器认为使用索引的成本更高或者没有合适的索引。
• key_len列：表示使用的索引的长度。这个长度可以帮助判断索引是否被充分利用。例如，对于一个复合索引，如果key_len只覆盖了索引的一部分列，可能表示索引没有被完全利用。
• ref列：显示索引的引用情况。如果是通过索引进行连接操作，这个列会显示连接所引用的列。
• rows列：估算的需要扫描的行数。这个数字越小，通常表示执行计划越高效。
• Extra列：包含了一些额外的信息，如是否使用了临时表、是否使用了文件排序等。这些信息可以帮助发现潜在的性能问题。

1. TRADITIONAL（默认格式）：
   以传统的表格形式展示执行计划信息，包括id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra等列。每一列都代表了执行计划的不同方面，例如：
2. JSON：
   以 JSON 格式输出执行计划信息，更易于解析和处理，尤其是在需要进行自动化分析或与其他工具集成时。
   JSON 格式的输出包含了更多详细的信息，如成本估算、访问路径等。

六 实操：通过执行计划查看每个执行计划的成本

CREATE TABLE `test_user` (
`id` int NOT NULL AUTO_INCREMENT, 
`id_card` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL, 
`age` int DEFAULT '0', 
`user_name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
`height` int DEFAULT '0',  
`address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,  
PRIMARY KEY (`id`), 
KEY `idx_id_card_age_user_name` (`id_card`,`age`,`user_name`), 
KEY `idx_height` (`height`))
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

（1）使用二级索引的执行计划 和执行成本

EXPLAIN FORMAT=JSON SELECT * from `test_user`   where height = 120;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7252.80"
    },
    "table": {
      "table_name": "test_user",
      "access_type": "ref",
      "possible_keys": [
        "idx_height"
      ],
      "key": "idx_height",
      "used_key_parts": [
        "height"
      ],
      "key_length": "5",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 6044,
      "rows_produced_per_join": 6044,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "6044.00",
        "eval_cost": "1208.80",
        "prefix_cost": "7252.80",
        "data_read_per_join": "1M"
      },
      "used_columns": [
        "id",
        "id_card",
        "age",
        "user_name",
        "height",
        "address"
      ]
    }
  }
}

1. EXPLAIN：用于获取 SQL 查询的执行计划信息，帮助了解 MySQL 如何执行该查询。
2. FORMAT=JSON：指定输出格式为 JSON，这种格式可以更清晰地展示执行计划的各个方面，并且方便解析和处理。
3. SELECT * from test_user IGNORE index(idx_height) where height = 120;：这是一个查询语句，从名为 test_user 的表中选择所有列的数据，条件是 height = 120，并且明确忽略名为 idx_height 的索引。

1. "query_block":

• 代表查询块信息，描述了整个查询的执行计划相关内容。

• 查询的唯一标识符为 1。

• "query_cost": "7252.80": 查询的预估总成本为 7252.80。这个成本是 MySQL 优化器根据多种因素估算出来的，包括读取数据的成本、评估条件的成本等。一般来说，成本越低，查询的执行效率可能越高。

• "read_cost": "6044.00": 读取数据的成本为 6044.00。
• "eval_cost": "1208.80": 评估条件的成本为 1208.80。
• "prefix_cost": "7252.80": 前缀成本为 7252.80，通常是指查询的总成本减去某些特定操作的成本。

• "table_name": "test_user": 查询涉及的表名为 test_user。
• "access_type": "ref": 访问类型为 ref，表示通过非唯一索引进行查找。在这个例子中，说明查询使用了索引进行查找，并且不是唯一索引。
• "possible_keys": ["idx_height"]: 可能使用的索引列表为 idx_height，这表明优化器考虑了这个索引，并最终选择了它。
• "key": "idx_height": 实际使用的索引是 idx_height，说明优化器认为使用这个索引可以提高查询性能。
• "used_key_parts": ["height"]: 使用的索引部分是 height 列，表明索引 idx_height 是基于 height 列创建的，并且在查询中只使用了这个索引的 height 部分。
• "key_length": "5": 索引的长度为 5，可能与存储 height 列的值所需的空间有关。
• "ref": ["const"]: 索引引用的值是常量，这里可能是查询条件中的 height = 120 中的 120。
• "rows_examined_per_scan": 6044: 每次扫描检查的行数为 6044。这表示在执行查询过程中，需要检查的行数估计为 6044 行。
• "rows_produced_per_join": 6044: 每次连接产生的行数为 6044。如果查询涉及多个表的连接，这个值表示从这个表中产生的行数，用于连接操作。
• "filtered": "100.00": 过滤后的行数比例为 100%。这意味着经过查询条件 height = 120 过滤后，所有满足条件的行数占总检查行数的比例为 100%。
• "cost_info":
• "data_read_per_join": "1M": 每次连接读取的数据量为 1M（可能是 1 兆字节）。
• "used_columns": ["id", "id_card", "age", "user_name", "height", "address"]: 查询使用的列包括 id、id_card、age、user_name、height 和 address。这意味着查询将从表中读取这些列的数据。

（2）执行成本的介绍

1. 执行成本的单位

• MySQL 优化器的成本单位是一个相对的抽象概念，没有一个具体的物理单位（如秒、字节等）与之对应。它是一个综合衡量指标，用于比较不同执行计划的资源消耗情况。成本主要由 I/O 成本（从磁盘读取数据的开销）和 CPU 成本（对数据进行处理的开销）组成。

• I/O 成本通常与磁盘读取操作相关。在 MySQL 中，读取一个数据页（一般是 16KB）的 I/O 成本默认被估算为 1.0。例如，若一个执行计划需要读取 10 个数据页，那么其 I/O 成本估算值可能为 10.0 左右。这个值会根据存储引擎的不同特性以及系统的硬件配置等因素有所变化。
• 对于 InnoDB 存储引擎，它有自己的缓冲池（Buffer Pool）。如果数据已经在缓冲池中，那么读取这些数据的 I/O 成本会相对较低，因为不需要从磁盘进行物理读取。优化器在计算成本时会考虑数据在缓冲池中的可能性，这是基于缓冲池的命中率等因素来估算的。

• CPU 成本涉及到对数据的处理操作，如比较、排序、函数计算等。例如，在进行索引查找时，需要在索引结构中进行比较操作，这会产生 CPU 成本。CPU 成本的估算相对复杂，它取决于操作的类型和数据量等因素。
• 对于一个简单的比较操作（如判断一个列的值是否等于某个常量），其 CPU 成本相对较低；而对于复杂的操作，如对大量数据进行排序或者使用复杂的函数进行数据转换，CPU 成本会较高。在计算成本时，优化器会根据操作的复杂度和涉及的数据量来估算 CPU 成本。

• 优化器在计算总成本时，会将 I/O 成本和 CPU 成本相加。例如，一个执行计划的 I/O 成本估算为 5.0，CPU 成本估算为 3.0，那么总成本就是 8.0。优化器会比较不同执行计划的总成本，选择成本最低的执行计划来执行查询。
• 不过，成本估算只是一个参考，实际的性能还可能受到硬件性能（如磁盘 I/O 速度、CPU 速度）、系统负载、数据分布等因素的影响。有时候，优化器选择的成本最低的执行计划在实际运行中可能并不是最优的，这就需要通过性能测试和调优等手段来进一步优化查询。

（3）禁用二级索引后的执行计划 和执行成本

EXPLAIN FORMAT=JSON SELECT * from `test_user`  IGNORE index(idx_height) where height = 120;

1. 性能调试：有时候开发人员可能怀疑某个索引没有被正确使用或者正在尝试不同的查询执行计划，通过忽略特定索引可以观察查询在没有该索引时的性能表现，从而更好地理解数据库的执行计划和索引的实际影响。
2. 解决索引冲突：如果某个索引的存在导致了查询执行计划的问题，比如产生了错误的连接顺序或者不必要的索引扫描，通过忽略该索引可以强制数据库使用其他可能更优的执行方式。
3. 特殊需求：在某些特定的业务场景下，可能根据具体的查询需求故意不使用某个索引，以满足特定的性能或数据一致性要求。

架构师尼恩提示： 上面的例子，就是对应到场景3.

架构师尼恩提示：谨慎使用 “IGNORE INDEX”，因为在大多数情况下，数据库的优化器会根据统计信息和各种因素自动选择最合适的索引来提高查询性能。如果随意忽略索引，可能会导致查询性能下降。

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "100134.80"
    },
    "table": {
      "table_name": "test_user",
      "access_type": "ALL",
      "rows_examined_per_scan": 489294,
      "rows_produced_per_join": 5966,
      "filtered": "1.22",
      "cost_info": {
        "read_cost": "98941.40",
        "eval_cost": "1193.40",
        "prefix_cost": "100134.80",
        "data_read_per_join": "1M"
      },
      "used_columns": [
        "id",
        "id_card",
        "age",
        "user_name",
        "height",
        "address"
      ],
      "attached_condition": "(`store`.`test_user`.`height` = 120)"
    }
  }
}

1. "query_block":

• "query_cost": 查询的总成本估计。这是一个综合考虑了 I/O 成本和 CPU 成本的数值，值越小通常表示执行计划越高效。

• "select_id": 查询的标识符。
• "cost_info":

• "name": 表的名称，这里是 test_user。
• "access_type": 访问表的方式，可能的值有 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。如果忽略了索引，可能会出现全表扫描，即 access_type 为 ALL。
• "possible_keys": 该查询可能使用的索引列表。由于使用了 IGNORE index(idx_height)，所以这里不会显示 idx_height。
• "key": 实际使用的索引，如果没有使用任何索引，该值为 NULL。
• "rows": 预估需要扫描的行数。
• "filtered": 表示经过筛选条件过滤后剩余行数的比例。

两条执行计划的对比

五、优化执行计划的方法

1. 优化索引
   根据查询语句的需求，创建合适的索引。对于频繁用于查询条件、连接条件和排序的列，应该考虑创建索引。但要注意避免创建过多的索引，因为索引本身也会占用磁盘空间，并且在插入、更新和删除数据时需要维护索引，会增加额外的开销。
2. 调整查询语句
   优化查询语句的写法可以影响执行计划。
   如，尽量避免在查询条件中使用函数或者表达式，因为这可能会导致索引无法使用。
   比如，SELECT * FROM users WHERE YEAR(birth_date) = 1990，这个查询中的YEAR()函数会使得birth_date列的索引无法使用。
   可以将查询改写为SELECT * FROM users WHERE birth_date BETWEEN '1990 - 01 - 01' AND '1990 - 12 - 31'，这样就有可能使用索引了。
3. 优化表结构
   合理的表结构设计也有助于优化执行计划。
   例如，将经常一起查询的列放在同一个表或者同一个列族（如果是基于列存储的数据库）中，可以减少数据的读取量。
   对于大数据量的表，可以考虑进行垂直或水平拆分，以提高查询性能。

尼恩架构团队的塔尖 sql 面试题

• sql查询语句的执行流程：

• 索引

• 索引下推 ?

• 索引失效

• MVCC

• binlog、redolog、undo log

• mysql 事务

• 分布式事务

• mysql 调优

说在最后：有问题找老架构取经‍

空窗8月：中厂大龄34岁，被裁8月收一大厂offer， 年薪65W，转架构后逆天改命!

空窗2年：42岁被裁2年，天快塌了，急救1个月，拿到开发经理offer，起死回生

空窗半年：35岁被裁6个月， 职业绝望，转架构急救上岸，DDD和3高项目太重要了

空窗1.5年：失业15个月，学习40天拿offer， 绝境翻盘，如何实现？