1. 执行计划概述
一开始设计表时就必须同时设计索引吗？实际上并非如此。
一般开发流程是：先设计表结构，确保它能满足业务需求，然后编写代码。等代码写好后，再根据实际的查询需求来设计索引，选择哪些字段、设计什么样的联合索引、字段顺序如何安排，这样可以确保查询语句能充分利用索引。
接下来，我们将进入MySQL学习的一个关键环节——执行计划分析和SQL优化，这是每个开发人员都应该掌握的技能。很多人对MySQL内核的理解较为浅显，对索引结构和查询优化的原理也不够了解，甚至不能完全理解MySQL的执行计划。
如果你还不了解执行计划的概念，怎么谈得上进行SQL优化呢？
有的人可能会问，你之前讲了索引结构和使用原理，SQL优化不就是让SQL语句都能利用索引吗？这个说法有道理，但还不完全准确。让SQL语句利用索引确实是SQL优化的一部分，但这只是其中之一，不能简单等同于全部。
SQL优化不仅仅是设计索引和确保查询能用上索引，实际上它涵盖了更多内容。比如，有时你会发现，尽管表设计和SQL语句中都有索引，查询性能仍然不理想。为什么会这样？
实际上，当表结构复杂，数据量庞大，SQL查询也很复杂时，性能问题就可能出现。很多时候，表面上看SQL用了索引，但查询速度依然很慢。这时，你必须从执行计划入手，理解复杂SQL的执行过程，才能找到优化的突破口。
当你有多个表，每个表有聚簇索引和其他二级索引，SQL语句在执行时是如何利用这些索引的？如何筛选数据、排序、分组等过程是怎样的？这些过程都由执行计划决定。MySQL的查询优化器会根据每个SQL的语义生成执行计划，它会指定如何使用索引、如何排序、如何做分组等。
通过理解执行计划，你就能掌握SQL优化的核心。如果能根据执行计划分析SQL语句的执行过程，调整SQL结构或索引设计，就能有效提升查询性能。这就是SQL调优的实质。
2. 看看执行计划包含哪些内容
现在, 我们就通过MySQL单表查询来分析执行计划，看看它具体包含了哪些内容。
只要你跟着之前的学习内容一步步来，理解执行计划其实并不困难。不过，如果你之前对数据页、索引及其使用规则等概念不够扎实，可能会感到有些吃力。如果此时感到困惑，建议回过头复习之前的知识，打好基础。
现在聊的执行计划内容，其实就是之前讲过的一些内容，主要是将这些概念与MySQL执行计划中的术语进行对应。通过这种方式，你会更容易理解MySQL执行计划中的专业术语，知道它们在底层对应的具体行为。
2.1. const [查询速度非常快，几乎可以视为常量级的]
举个例子，如果你写了一个简单的查询，比如 select * from table where id=x或者select * from table where name=x，这类查询通常通过聚簇索引或二级索引+聚簇索引回源，就能高效地找到数据。在执行计划中，这个过程被称为 const，表示查询速度非常快，几乎可以视为常量级的。
以后如果在执行计划中看到const，你就知道它表示通过索引快速定位到数据。需要注意的是，只有二级索引是唯一索引时，才能被视为const。也就是说，必须创建唯一索引（unique key），确保二级索引的每个值都是唯一的，才能达到这种高效查询的效果。
2.2. ref、ref_or_null [查询速度依然很快]
如果是普通的二级索引（即普通的KEY索引），那么执行计划中显示的将是ref。举个例子，如果你写了select * from table where name=x，而name是普通二级索引，查询速度依然很快，这时执行计划中就会显示ref。
对于包含多个列的普通索引，如果你写了类似 select * from table where name=x and age=x and xx=xx，并且索引是KEY(name, age, xx)，那么这个查询会被认为是ref方式。值得注意的是，如果查询语句中涉及 IS NULL，无论name是主键还是唯一索引，它依然会走ref的方式。
而如果查询语句中包含了IS NULL，并且是针对二级索引进行查询，比如 select * from table where name=x or name IS NULL，在执行计划中，这种情况会被标记为 ref_or_null。也就是说，在这种情况下，执行计划会先在二级索引中查找匹配值以及NULL，然后再回源查找聚簇索引。
总结一下: ref就是在普通索引上进行查询或在主键/唯一索引上进行IS NULL/IS NOT NULL查询时的表现。
在分析执行计划时
看到const意味着查询使用了主键或唯一索引，速度极快。
而ref则表示使用了普通索引，或在主键/唯一索引上进行了IS NULL/IS NOT NULL的查询。
2.3. range [通常不会有太大问题]
顾名思义，当SQL查询中涉及范围查询时，就会使用这种方式。例如，假设写了 select * from table where age>=x and age<=y，如果age是普通索引，那么在执行时，MySQL会使用索引进行范围筛选，这时执行计划中的访问方式就是range。
到这里，我们总结一下，如果在执行计划里看到const、ref或range，它们是什么意思呢？它们都是基于索引查询的，通常不会有太大问题。问题可能出现在通过索引查询时，返回的数据量过大。比如，范围查询可能会返回大量数据，假如一次返回10万条数据，这对于MySQL来说可是一个不小的挑战。
2.4. index [稍微逊色一些]
接下来，我们讲解一种较为特殊的访问方式——index。有些人可能会以为，index和ref、range一样，都是通过索引查找数据，只是从索引的根节点开始，通过二分查找不断跳转。实际上，这种理解是错误的。
假设我们有一个表，包含一个联合索引KEY(x1, x2, x3)，并且我们写了一个查询 select x1, x2, x3 from table where x2=xxx。许多人可能会认为这个查询无法直接通过联合索引的根节点进行查找，因为x2不是最左侧的字段。但实际情况并非如此。尽管x2不是最左侧字段，查询仍然会利用联合索引的索引树。
查询会遍历KEY(x1, x2, x3)这个联合索引的叶子节点，而这些叶子节点存储的不是完整的数据，而是x1、x2、x3以及主键的值。因此，查询不需要回源到聚簇索引去。遍历这些二级索引的叶子节点比遍历聚簇索引要快，因为二级索引叶子节点存储的数据少，所以访问速度较快。
这种仅通过遍历二级索引就能得到结果，而不需要回源到聚簇索引的访问方式被称为index访问方式。与我们最初的理解不同，index并不是基于二分查找，而是通过遍历二级索引的叶子节点来完成查询。
所以，要理解执行计划，首先需要对索引结构和索引使用原理有深刻理解。在此基础上，我们能轻松理解不同执行计划中的访问方式，并在心中形成执行路径的可视化。
目前，我们已经讲解了const、ref、range和index。这些访问方式的性能差异如何呢？前面提到的const、ref和range都基于索引树的二分查找或多层跳转，查询性能通常都比较高。而index则稍微差一些，因为它需要遍历二级索引的叶子节点，虽然它比全表扫描要快，但与前面三种方式相比，速度相对较慢。
2.5. all [ 全表扫描-最差]
执行计划中的访问方式：const、ref、range和index。这些方式本质上都是基于索引查询，只要索引查出来的数据量不是特别大，性能通常非常高效。而index稍微逊色一些，需要遍历二级索引，但因为二级索引较小，性能依然不错。
最差的一种访问方式是all，即全表扫描。在执行时，MySQL会扫描聚簇索引的所有叶子节点，逐行扫描数据。如果表的数据量非常大（比如几十万或几百万行），全表扫描的性能会非常差。
3. 普通例子
现在让我们来看一些常见的SQL语句，并结合执行计划分析它们的执行方式。
第一个例子：select * from table where x1=xx and x2>=xx
假设我们在表上有两个字段 x1 和 x2，并且可能为这两个字段分别建立了索引。如果你有两个索引，例如：(x1, x3) 和 (x2, x4)，那么这条SQL语句只能选择其中一个索引。MySQL的查询优化器会选择数据量较小的那个索引，通常选择在索引里做等值比较的数据较少的条件。
例如: 如果 x1=xx 这个条件只会返回少量数据，MySQL可能会选择 x1 的索引。此时，执行计划中的访问方式就是 ref，它会通过 x1 的索引查找匹配的记录，然后回表（即回到聚簇索引）获取完整数据。接下来，MySQL会根据 x2>=xx 的条件对返回的结果进行筛选。
第二个例子：select * from table where x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL
在这个例子中，x1 字段有索引，而其他字段 c1、c2、c3 没有索引。对于这种情况，查询优化器会选择通过 x1 字段的索引来进行查询。这种情况在实际开发中非常常见，因为并非每个字段都会建立索引，通常我们会针对常用的筛选字段设计索引。
执行计划会显示，通过 x1 字段的索引执行 ref 查找，找到符合条件的数据后，MySQL会回表到聚簇索引中获取完整的记录。然后，MySQL会在这些返回的结果中进一步应用 c1=xx、c2>=xx 和 c3 IS NOT NULL 的条件，进行最终的筛选。
在这种情况下，x1 字段的索引设计尤为重要，设计时需要确保 x1=xx 的条件能在索引树中查找出较少的数据，这样才能保证查询的高效性。
总结
通过这两个例子，我们可以看到，SQL语句的执行计划和索引的设计密切相关。对于一个复杂的查询，尽量让查询条件中的字段能够利用索引，从而避免全表扫描，提升查询性能。查询优化器会根据索引的选择、数据量的多少和执行的效率来生成最佳的执行计划。
4. 比较特殊的例子
我们来探讨一个特殊的SQL查询和它的执行计划。我们通常说，一个SQL语句只能使用一个二级索引，但在一些特殊情况下，MySQL优化器可能会使用多个二级索引来执行查询。今天我们就来看一个这样的例子。
示例SQL：
select * from table where x1=xx and x2=xx;假设x1和x2这两个字段各自有一个单独的二级索引，查询优化器有可能会生成一个执行计划，利用这两个索引来进行查询。执行计划的步骤可能如下：
查找x1索引：首先，MySQL会通过x1字段的索引树查找符合条件的记录。
查找x2索引：接着，MySQL会通过x2字段的索引树查找符合条件的记录。
交集操作：将x1和x2查出的结果集进行交集操作，只保留同时满足x1=xx和x2=xx两个条件的记录。
回表查询：然后，MySQL会根据主键值回表（即从聚簇索引中获取完整的记录）来查找最终的完整数据。
为什么会使用多个索引？
在大多数情况下，如果一个SQL语句中有多个条件，并且每个条件都有索引，MySQL的查询优化器会选择其中一个索引来执行查询，并在查询结果出来后再进行其他条件的筛选。然而，在某些特殊情况下，优化器可能会选择同时使用多个索引并取交集，这样做是为了提高性能。
比如，在我们的示例中，假设通过x1索引查找出来的数据量非常大，可能会导致回表查询时的性能下降。这时，如果优化器能够同时从x2的索引树中查找符合条件的数据，并取两个索引结果的交集，那么交集后的数据量可能会大大减少。这样做的好处是，减少了回表查询时需要处理的数据量，从而提高查询效率。
硬性条件和约束
然而，能在一个SQL中使用多个索引并取交集的情况并不是随时都能发生的，它有一定的条件限制：
联合索引的要求：如果你有一个联合索引，那么SQL中的条件必须包括索引中的所有字段，并且必须是等值匹配。否则，MySQL就不能利用联合索引来加速查询。
主键查询+其他索引匹配：在某些情况下，你可能会通过主键查询和其他字段的二级索引匹配来利用多个索引的交集。
数据量影响：多个索引取交集的方式适用于当其中某个索引结果的数据量较大时，优化器通过交集操作减少回表查询的数据量，从而提高性能。
总结
在SQL查询优化过程中，确实有可能会同时使用多个索引，并通过交集操作来减少回表查询的数据量。这种方式在某些特定情况下能提高性能，但前提是满足一些硬性条件。
虽然这种多索引查询的执行方式可能让人感到困惑，但只要理解了它的原理，就能在实际的执行计划分析中灵活应用。
5. 总结
MySQL的执行计划，这些知识对后续的SQL调优非常重要。如果没有扎实掌握这些基本概念，你将很难理解后续的调优案例，因为调优的核心就是搞清楚SQL的执行过程，找到性能瓶颈，并通过合理的索引设计或者SQL优化来解决问题。
到目前为止，我们了解了常见的单表查询的执行计划。重要的执行方式包括：
const、ref、range：高效的索引查询方式。
index：扫描二级索引，效率较低。
all：全表扫描，性能最差。
5.1. 基础执行计划类型
首先，我们已经讨论过几种常见的执行方式，如 const、ref、range，这些方式都是性能最优的，表示查询能够直接在索引树上快速定位数据。使用这些方式时，MySQL会高效地找到需要的数据，性能也较好。
const：表示查询仅返回一行数据，通常是通过主键或唯一索引查询。
ref：表示通过索引查找多个符合条件的行，通常是等值查询。
range：表示通过索引扫描一个范围的数据，通常是像>=、BETWEEN等条件。
当查询涉及到多个字段时，如果它们有索引，查询优化器可能会选择使用多个索引，分别对每个字段进行查找，然后取交集或并集来得到最终的结果。
然而，index和all是执行效率相对较低的方式：
index：表示扫描二级索引的叶子节点，虽然比全表扫描要快一些，但性能较差，因为它不会直接通过索引树根节点开始查找。
all：表示全表扫描，也就是扫描聚簇索引的叶子节点，速度很慢，特别是在表数据量大时，这种方式会显著影响性能。
5.2. 多个索引的使用
对于x1=xx or x2=xx这种情况，查询优化器可能会选择使用多个索引。不同于交集操作（intersection），这种情况下是通过并集（union）将两个条件下的数据合并。虽然这种方式也能提高查询效率，但它并不是每次都会发生。MySQL根据执行计划会选择是否使用多个索引，这取决于具体的查询条件和索引设计。
评论区