请问MySQL索引是如何选择的？on、where、order by中的字段谁的优先级更高？

Question

请问MySQL索引是如何选择的？on、where、order by中的字段谁的优先级更高？

该SQL为啥没有用idx_issue_id索引？

Answer 1

MySQL索引选择机制

MySQL 优化器在选择索引时，会综合考虑多种因素来决定使用哪个索引能以最高效的方式执行查询：

• 统计信息：优化器依赖存储引擎提供的索引统计信息，例如索引的基数（不同值的数量）等。基数越高，索引在过滤数据时就越有可能高效，优化器会倾向于选择基数大的索引来减少扫描的数据量。
• 查询条件：评估每个可能的索引对 WHERE 子句、JOIN 条件等查询条件的满足程度。如果某个索引能够覆盖更多的查询条件过滤数据，就更有可能被选择。
• 索引类型：不同类型的索引（如 B - Tree 索引、哈希索引等）在不同场景下有不同的性能表现。例如，B - Tree 索引适合范围查询，哈希索引适合等值查询，优化器会根据查询类型选择合适类型的索引。
• 索引覆盖：如果一个索引包含了查询所需的所有列（即覆盖索引），那么使用该索引可以避免回表操作，减少 I/O 开销，优化器会优先考虑这种索引。

on、where、order by中字段优先级

在 MySQL 中，ON、WHERE、ORDER BY 子句中的字段并没有固定的绝对优先级顺序。优化器会根据具体的查询语句结构、表结构、索引情况等进行综合分析和判断。

• ON 子句：主要用于表连接时定义连接条件，在连接操作中起着关键作用，它决定了如何将不同表的数据进行匹配。
• WHERE 子句：用于筛选行，过滤出满足特定条件的记录。优化器会优先考虑能够快速过滤掉大量数据的条件，不管这些条件是在 ON 还是 WHERE 中。
• ORDER BY 子句：用于对查询结果进行排序。如果有合适的索引可以满足排序需求（索引的顺序与 ORDER BY 一致），那么可以避免额外的排序操作（Filesort），否则可能会影响查询性能。

该SQL未使用idx_issue_id索引的原因

在给定的 SQL 中，未使用 idx_issue_id 索引可能有以下原因：

• 索引选择性问题：虽然存在 idx_issue_id 索引，但优化器评估后发现它对过滤数据的效果不如其他索引（如 idx_teacher_id）。例如，idx_teacher_id 索引对应的列 teacher_id 的值为固定值 57，在过滤数据时可能更有效，而 idx_issue_id 索引可能在过滤数据方面效果不佳，导致优化器选择了其他索引。
• 索引覆盖情况：从查询的整体需求来看，idx_teacher_id 等索引可能更能够满足查询结果的获取，例如可能包含了更多查询需要的列，避免了回表操作，而 idx_issue_id 索引无法满足这种覆盖需求，所以优化器没有选择它 。
• 统计信息不准确：MySQL 优化器依赖的索引统计信息可能不准确，导致优化器做出了错误的索引选择决策。可以通过 ANALYZE TABLE 语句来更新表的统计信息，让优化器获取更准确的数据从而做出更合理的索引选择。

Answer 2

你可以先了解一下一个 SQL 语句在 MySQL 中的执行流程,基本会执行以下阶段
连接器（Connection Manager）：负责处理客户端与服务器之间的连接。它接受来自客户端的请求，并进行身份验证和权限检查，建立和管理连接。

• 查询缓存（Query Cache）：在旧版 MySQL 中有，但在较新的版本中已不推荐使用。它能够缓存查询和对应的结果，以提高查询性能。然而，在高并发和大型数据库中，它反而可能成为性能瓶颈，因为它在某些情况下会引起锁和不必要的开销。
• 分析器（Parser）：负责分析 SQL 查询语句，验证其语法和语义，确保查询的正确性。它将 SQL 语句转换成内部数据结构供优化器和执行器使用。
• 优化器（Optimizer）：接收来自分析器的查询请求，并决定如何最有效地执行查询。优化器的目标是找到最佳的执行路径，选择合适的索引、连接顺序和访问方法，以提高查询性能。
• 执行器（Executor）：负责执行优化器生成的执行计划，获取存储引擎返回的数据，并处理客户端请求。它与存储引擎交互，执行查询并返回结果给用户。

你的问题就设计到优化器的选择，优化器的选择并不是根据语句中的on、where、order by 来进行选择使用的索引的。而是会对成本进行估算,优化器会为每个候选计划估算成本。成本基于以下因素：

• I/O 成本：数据从磁盘读取到内存的成本。
• CPU 成本：处理每行数据的计算成本。
• 行数估计：查询中每个步骤的结果集大小估计。

根据每个执行计划的估算成本，选择成本最低的执行计划。(这就涉及一个小的 tips: 索引失效的情况其中其中有一个就是: 当某个值出现在表的数据行中的百分比很高的时候比如说type=1占百分之80的数据你使用type索引时,优化器发现成本很高的时候,就会出现忽略掉索引使用全表查询)。

结论: Mysql 索引的选择是优化器基于查询的成本来进行选择的,并不存在优先级,建议你可以去了解可以看一下这几篇文章,会对你理解索引有更好的帮助

• MySQL索引原理及慢查询优化
• 15个必知的Mysql索引失效场景

Answer 3

看标题描述，应该是有2个疑问待解答，发表下个人的看法，希望能帮到您

1. MySQL 的查询优化器会选择一个最合适的索引来加速查询。即使该表上有多个可用的索引（possible_keys），优化器也只能选择其中一个索引作为实际使用的索引（key）。
2. SELECT语句执行顺序是 FROM > JOIN > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT等等。

改进建议是：使用复合索引，甚至覆盖索引来满足要求。

Answer 4

在这个语句中，其实已经能看到，选择 idx_teacher_id 是因为 ref 为 const，这个成本几乎是最低的，因为要先通过这一步来缩小筛选范围。

而后面的 as_status 则是因为是 <> 查询，所以铁定不走索引的。

因为现在 teacher_id 的成本很低，所以如果你想要这里的 issues_id 也走上索引，可以考虑创建一个 teacher_id+issuse_id 的联合索引。

这样应该就能照顾到了。