1. 一个表里是不是索引搞的越多越好？

现在,做一个简单的索引知识总结，并分析索引的优缺点。

首先，大家都知道，通常在向一个表插入数据时，系统会自动基于主键为我们建立聚簇索引，聚簇索引的结构大致如下所示。

随着我们不断向表中插入数据，数据页会逐渐被填满，填满后就会发生分裂，生成多个数据页。此时，索引页就需要指向这些数据页。

如果数据页数量变得非常多，索引页指向的数据页指针也会增多，最终索引页本身也会填满，这时索引页也会发生分裂，生成更高层次的索引页。

通过这种逐步演化，你就能看到前面提到的图示。这一过程我们之前已经详细分析过，相信大家看了文字说明后能够理解。

默认情况下，MySQL会根据主键的值来构建聚簇索引，聚簇索引的叶子节点就是数据页，这些数据页中存放的就是我们插入的完整数据。

在索引的B+树中，数据页和索引页的记录构成了一个单向链表，且这些记录是按数据大小有序排列的。同时，数据页和索引页之间相互连接成双向链表，同样按照数据大小有序排列。因此，B+树索引是一个完全有序的数据结构，无论是在单一的页内还是页与页之间。

正是因为B+树索引具备这种有序结构，我们在查找数据时，可以从根节点开始，按照数据的大小逐层向下查找，这使得查找的效率非常高。

当我们为主键以外的字段建立索引时，本质上是为该字段的值重新建立一颗B+树索引。在这颗索引B+树的叶子节点中，存放的是数据页，而每个数据页中存储的是该字段的值及其对应的主键值。同时，每一层的索引页存放的是下层页的引用，包含页内和页间的排序规则。B+树索引的搜索规则也与聚簇索引一致。

需要特别注意的是，如果我们基于其他字段的索引进行查询，首先会通过该字段的B+树索引快速找到对应的主键值，然后再通过主键值回表查询，即从聚簇索引的B+树中重新查找完整的数据。

以上就是我们之前分析的完整的MySQL B+树索引原理，包括B+树的索引结构、排序规则、插入过程、查询原理，以及为不同字段建立独立B+树索引和回表查询的过程。

在之前总结的基础上，来回答一个问题：在MySQL表中为某些字段建立索引有什么好处呢？

好处显而易见：通过某个字段的B+树索引，我们可以直接定位到数据，而不需要进行全表扫描，这样性能提升非常显著。

但是，索引也有它的缺点，主要有两个方面：空间上的和时间上的。

从空间上讲:

如果为许多字段创建多个索引，那么你会有很多棵B+树，每一棵索引树都占用相当多的磁盘空间。因此，如果索引过多，会消耗大量的磁盘空间。
此外，索引过多还会带来维护上的问题。每次进行增删改查操作时，必须维护所有索引的有序性。每个索引B+树都要求数据页内部按照值的大小排序，页与页之间的值也必须按顺序排列。如果发生了数据页插入错误，导致数据页的值不再有序，就必须重新调整数据页的位置以恢复顺序。
同时，在不断插入数据时，索引的数据页可能会频繁分裂，新增索引页，这个过程是非常耗时的。

所以，如果一个表的索引过多，虽然查询速度可能会提升，但增删改操作的性能会受到很大影响。通常，我们不建议为一个表创建过多索引。

那么，如何才能在保证查询需求的同时，避免过多的索引占用过多磁盘空间，并影响增删改的性能呢？这就要求我们深入理解索引的使用规则，并优化SQL语句，以便合理利用索引B+树进行查询。

2. 深入理解联合索引查询原理以及全值匹配规则

现在, 我们将通过一步步的图解，深入了解由多个字段组成的联合索引的查询原理，以及全值匹配规则。

讨论联合索引的原因是因为，在实际系统设计中，我们通常会使用联合索引，较少使用单字段索引。我们之前提过，为了避免占用过多磁盘空间以及影响增删改的性能，我们应该尽量减少索引的数量。

关于单个字段索引的组织结构和查询原理，我们已经讲解得很清楚，无需再重复。

现在假设我们有一个表存储学生成绩，表中有一个自增主键 id，并默认基于这个主键建立了聚簇索引，这部分我们不再赘述。

此外，表中还有学生班级、姓名、科目名称和成绩四个字段。对于查询，最常见的场景是查询某个班级、某个学生的某个科目成绩。因此，我们可以为“学生班级”、“学生姓名”和“科目名称”这三个字段创建联合索引。

接下来，我们展示了这个三个字段的联合索引的部分内容。从图中可以看到：

有两个数据页，第一页包含三条数据，每条数据都包括联合索引的三个字段的值以及对应的主键值。数据页内的记录按照顺序排序。
首先按“班级”字段的值排序，如果班级值相同，再按“姓名”字段排序，如果姓名也相同，最后按“科目名称”字段排序。因此，数据页内的数据按这三个字段的值排序，并且形成了单向链表。
数据页之间也有顺序，第二个数据页中的字段值一定大于第一个数据页的字段值。比较规则也是依次按照班级、姓名、科目名称来比较的，数据页之间形成了双向链表。
索引页包含两条记录，分别指向两个数据页。每条记录存储了对应数据页中的最小值。可以看到，索引页中指向数据页的索引项保存了数据页中最小值的信息。
索引页内部的数据页按顺序形成单向链表。如果有多个索引页，它们之间也会按顺序形成双向链表。

假设我们现在需要查找：1班、张小强、数学的成绩。你可能会写出类似下面的SQL语句：

select * from student_score where class_name='1班' and student_name='张小强' and subject_name='数学';

这个查询涉及到一个索引使用的规则，即：SQL语句中的 WHERE 条件字段都是通过等值比较进行查询，并且使用的是等号。而且这些字段的顺序和联合索引的顺序完全一致。这就是所谓的“等值匹配规则”，在这种情况下，SQL语句就能完全利用联合索引进行查询。

查询过程也非常简单。首先会在索引页中查找，索引页包含了多个数据页的最小值记录。此时，我们可以使用二分查找法，在索引页中查找对应的值。首先根据班级名称查找“1班”，找到它对应的数据页，直接定位到该数据页，如下图所示。

接下来，你可以直接找到索引指向的数据页。在数据页内部，数据也被组织成单向链表结构，同样可以使用二分查找来定位数据。首先，按照“1班”这个值查找，你会发现多个数据条目都属于“1班”。接着，你可以按照“张小强”这个姓名值继续进行二分查找，找到对应的“张小强”的数据项。再按照科目名称“数学”进行二分查找。

很快，你就能定位到下图所示的数据，即：1班、张小强、数学科目对应的数据，且其数据的id为127。

然后，通过主键id=127，你可以在聚簇索引中按照相同的思路进行查找。首先，从聚簇索引的根节点开始，利用二分查找迅速定位到下一个层级的页，再根据相应的顺序继续查找，最终可以快速定位到id=127的那条数据。然后，你就可以提取其中的所有字段，包括分数，完成查询。

这个过程就是联合索引的查找过程，同时也展现了全值匹配规则。只要你的SQL语句中的where条件所使用的字段名称和顺序与索引中的字段一致，并且使用等号进行等值匹配，MySQL就会按照上面描述的方式进行查询。

对于联合索引来说，它会依次按照每个字段的顺序进行二分查找。首先定位到第一个字段的对应值所在的数据页，然后如果第一个字段的值重复，接着根据第二个字段的值继续查找，以此类推。最终，你会定位到某条或几条数据，从而实现高效的查询。

3. 👍👍几个最常见和最基本的索引使用规则

当我们建立了一个联合索引之后，如何编写SQL语句才能有效地使用这个索引呢？

我们继续使用之前的例子来说明。

3.1. 等值匹配规则

首先，上面我们提到的等值匹配规则：即在 WHERE 语句中的字段名称和联合索引的字段完全一致，且都是使用等号进行等值匹配时，MySQL会百分百使用该索引进行查询。这里有一个小细节，

‼️‼️虽然 WHERE 语句中的字段顺序可以与联合索引中的字段顺序不同，但MySQL会自动优化为按照联合索引中的字段顺序来进行查询。这意味着，即使你的 WHERE 条件字段顺序不同，MySQL依然能够合理利用索引。

3.2. 最左侧列匹配规则

第二个规则是最左侧列匹配规则。这个规则的意思是，假设我们有一个联合索引，像 KEY(class_name, student_name, subject_name)，并不一定要在WHERE语句中使用所有的字段进行查询。你只要根据联合索引中的最左侧部分字段来查询，也是可以的。

举个例子，你可以写类似这样的SQL语句：

SELECT * FROM student_score WHERE class_name='' AND student_name='';

这种查询是可以利用联合索引的，查询某个学生所有科目的成绩没有问题。

但是，如果你写：

SELECT * FROM student_score WHERE subject_name='';

这种查询就无法使用联合索引了，因为联合索引的B+树结构要求查询时必须先按class_name来查，再按student_name来查，不能直接跳过前两个字段，去按subject_name查。

另外，如果你写：

SELECT * FROM student_score WHERE class_name='' AND subject_name='';

这种查询虽然包含了class_name和subject_name，但是subject_name字段是无法在索引中查到的。因为索引的设计是按顺序来查找的，MySQL会先用class_name来搜索，subject_name无法在跳过student_name的情况下直接使用索引。

所以，建立联合索引时，需要合理规划字段顺序，确保你的SQL查询能有效地利用索引进行搜索。

3.3. 最左前缀匹配原则

第三个规则是最左前缀匹配原则。这个规则主要是针对 LIKE 语法的查询。

如果你使用LIKE查询，并且在查询中给定了一个明确的最左前缀，索引依然可以被利用。例如：

SELECT * FROM student_score WHERE class_name LIKE '1%';

在这个查询中，你查询的是以”1”开头的班级成绩，class_name字段有明确的最左前缀（即”1”），那么MySQL会使用联合索引来加速查询。由于联合索引是按照class_name排序的，因此索引能够帮助快速定位所有以”1”开头的班级记录。

然而，如果你写：

SELECT * FROM student_score WHERE class_name LIKE '%班';

这种查询就无法利用联合索引了。因为你在LIKE语法中用的是左侧模糊匹配（即'%班'），这意味着MySQL无法知道查询的最左前缀是什么。由于没有明确的前缀，索引无法有效地应用到这种查询上，MySQL会进行全表扫描。

总结来说，只有当LIKE查询的模式能够明确指定最左前缀时，索引才会被使用。如果LIKE语法中出现左侧模糊匹配，索引将无法帮助查询加速。

命中联合索引
SELECT * FROM student_score WHERE class_name LIKE '1%' AND student_name LIKE '张%';
部分命中联合索引
SELECT * FROM student_score WHERE class_name LIKE '1%' and student_name '张%' and subject_name like '%学';

尽管查询中的 subject_name LIKE '%学' 无法完全利用索引，但查询仍然能部分命中联合索引，这意味着查询效率相对于完全不使用索引的情况还是有所提升的。

为什么会有效率提升？

class_name LIKE '1%' 和 student_name LIKE '张%'：这两个条件能够利用联合索引中的前两列 class_name 和 student_name。MySQL 会先利用这些条件筛选数据，减少了扫描的数据量，因为它能够直接从索引中快速定位符合条件的行。
subject_name LIKE '%学'：虽然这个条件不能利用索引，但由于前面两个条件已经限制了结果集的范围，MySQL 只需在更小的结果集上执行 subject_name 的模糊匹配，而不是在整个表中执行全表扫描。因此，虽然不能完全使用索引，但整体查询性能仍然会优于没有任何索引的情况下。

3.4. 范围查找规则

第四个规则是范围查找规则。这条规则说明了在SQL查询中，使用范围查询（如>、<、BETWEEN等）时，只有联合索引的最左侧列可以利用索引。

例如，考虑以下查询：

SELECT * FROM student_score WHERE class_name > '1班' AND class_name < '5班';

在这个查询中，class_name的范围查询（>和<）会利用到联合索引，因为联合索引的B+树是有序的，最下层的数据页形成双向链表，可以顺利地在索引中定位到'1班'和'5班'之间的所有数据。索引能够有效帮助快速定位符合条件的数据。

但是，如果你添加了一个额外的范围查询条件，比如：

SELECT * FROM student_score WHERE class_name > '1班' AND class_name < '5班' AND student_name > '李华';

在这个查询中，class_name的范围查询仍然能使用联合索引，但student_name的范围查询将无法利用索引。因为联合索引的规则是：只有在最左侧的列进行范围查询时，才可以利用索引。

换句话说，如果在联合索引中，class_name、student_name和subject_name的顺序是固定的，那么范围查询只能作用于class_name，而不能跨越到student_name。一旦在student_name字段中使用范围查询，索引的应用就会受到限制。

总结：范围查询仅能应用于联合索引中的最左侧列，其他列若涉及范围查询，则无法使用索引优化查询，通常会导致全表扫描。

3.5. 等值匹配与范围匹配相结合

第五个规则是等值匹配与范围匹配相结合的规则。假设我们使用以下查询语句：

SELECT * FROM student_score WHERE class_name='1班' AND student_name > '' AND subject_name < '';

在这种情况下，首先通过 class_name 字段，我们能够精确地在索引中定位到一组数据。接着，这组数据中的 student_name 字段是按顺序排列的，因此我们也能基于索引来执行 student_name > '' 的范围查询。然而，接下来对 subject_name 字段的范围查询 subject_name < '' 就无法利用索引了。

查询条件分析：

class_name = '1班'：

这是一个等值匹配查询，它使用了索引的第一列 class_name。MySQL 可以利用该索引精确查找所有 class_name = '1班' 的记录，并定位到符合条件的数据。

student_name > ''：

这是一个范围匹配查询，student_name 是索引的第二列。因为 student_name 与 class_name 组合成了联合索引的一部分，当 MySQL 找到所有符合 class_name = '1班' 的记录时，student_name 的数据是有序的（因为索引是有序的），因此它可以利用索引的顺序来进行 student_name > '' 的范围查询。

subject_name < ''：

这是一个范围匹配查询，subject_name 是索引的第三列。由于 subject_name 是联合索引的最后一列，因此此查询在 subject_name 上的范围条件不能完全利用索引来优化查询。即使在索引中它排在 student_name 后面，但由于索引是左前缀的，前面已经有了范围条件（student_name > ''），因此后面的条件（subject_name < ''）就无法通过联合索引继续进行优化了。

总结：

class_name = '1班'：可以完全命中索引，进行精准查找。
student_name > ''：可以利用索引继续执行范围查询。
subject_name < ''：由于它是联合索引的最后一列，且存在范围查询，它不能完全利用索引，可能需要回表来执行这个范围查询。

综上所述，当我们编写 SQL 语句时，通常会根据联合索引的最左侧字段进行等值匹配和范围查询，或者使用最左侧字段进行前缀模糊匹配，或者根据最左侧字段进行范围搜索。只有按照这些规则书写 SQL 语句，我们才能有效利用已经建立的联合索引进行优化查询。

4. 👍👍👍 SQL里排序，如何才能使用索引？

在上面的讲解中，我们已经讨论了如何在SQL的 WHERE 语句中编写条件来确保能有效利用联合索引进行数据筛选。总结来说，无论采用什么规则，只要尽量从联合索引的最左侧字段开始使用，就能使索引树发挥作用。

当在SQL语句中使用 ORDER BY 进行排序时，如何才能高效利用索引。

通常，我们可以想象一下，如果有类似这样的SQL语句：SELECT * FROM table WHERE xxx = xxx ORDER BY xxx，首先通过WHERE 语句利用索引筛选出符合条件的数据，然后将这些数据放入内存或临时磁盘文件中，再通过排序算法按照指定字段排序，最后返回排序后的结果。这种方式通常比较慢，特别是当数据量较大时，无法完全使用内存进行排序时，就需要依赖磁盘文件。这种情况下，MySQL会使用术语filesort 来表示磁盘排序，而这显然会导致性能下降。

为了避免这种情况，特别是在类似于SELECT * FROM table ORDER BY xx1, xx2, xx3 LIMIT 100的SQL查询中，涉及多个字段排序并返回前100条数据时，最好的做法是利用已经创建的联合索引。例如，如果我们创建了INDEX(xx1, xx2, xx3)这样的联合索引，那么索引树本身就已经按照xx1, xx2, xx3这三个字段的值排序好了。

因此，执行SELECT * FROM table ORDER BY xx1, xx2, xx3 LIMIT 100时，MySQL可以直接利用索引树的数据顺序，不需要额外的磁盘排序。它只需要按照索引树的顺序，从最小值开始，直接取出前100条数据，获得相应的主键，然后再根据主键从聚簇索引中查找其他字段的值。

为了充分利用联合索引的排序特性，ORDER BY 语句中的排序字段应该尽量按照联合索引的字段顺序进行。如果ORDER BY中的字段顺序和联合索引一致，那么可以直接从索引树中获取已排序的数据。

需要注意的是，ORDER BY 语句中的排序规则也有一些限制：

你可以按升序或降序排序，但不能混合升序和降序。
如果排序的字段不在联合索引中，或者使用了复杂的函数进行排序，则无法利用索引进行排序。

总结来说，如果你的SQL语句的 ORDER BY 语句能

够按照联合索引的字段顺序进行排序，
并且字段没有混合升降序，MySQL就能直接利用索引树中已排序的数据，从而大大提升查询性能。

这对于需要分页查询的管理系统来说，尤其重要，因为分页查询通常会用到 ORDER BY 和 LIMIT 语句，通过合理利用索引，性能将得到显著提升。

5. 👍👍👍 SQL里进行分组，如何才能使用索引？

SQL语句中，如何利用索引来优化 GROUP BY 操作，尤其是当我们需要进行分组和聚合统计时，比如使用 COUNT、SUM等聚合函数。

假设你执行类似SELECT COUNT(*) FROM table GROUP BY xx这样的查询，传统常规没有索引的方式可能需要将所有数据放入一个临时磁盘文件中，并通过内存辅助的方式进行分组和聚合，这种做法通常性能较差，因为它涉及大量的磁盘交互。

但如果你能够利用索引，情况会有所不同。因为索引树本身是按照字段的顺序排序的，而字段值相同的数据会被聚集在一起。如果你能直接基于索引来执行分组操作，性能会大大提升，因为索引已经在内部完成了排序，避免了临时磁盘操作和内存排序。

因此，对于 GROUP BY 操作的字段，最好按照联合索引中最左侧的字段顺序进行排列，这样你就可以直接利用索引提取分组数据，然后在每个组上执行聚合操作。这样做不仅能避免低效的磁盘排序，还能提高查询性能。

其实，GROUP BY 和 ORDER BY 使用索引的原理是类似的，关键在于 GROUP BY 和 ORDER BY 后面的字段顺序要与联合索引中的字段顺序一致，才能充分利用索引树的排序特性。这样，你可以快速地根据已排序的数据进行后续的分组和聚合操作，而不必依赖临时文件进行排序和分组，这样的性能将更好。

从这个角度看，大家应该理解一个重要的概念：在设计表的索引时，需要充分考虑后续SQL语句的使用情况，包括将会使用哪些字段进行WHERE 筛选、ORDER BY排序和 GROUP BY分组。基于这些需求，你可以设计出覆盖常见查询的索引，这样大部分查询都能利用索引，从而确保系统查询性能不会出现瓶颈。

6. 👍👍👍 回表查询对性能的损害以及覆盖索引是什么？

通过之前的学习，我们了解到每个我们创建的索引（无论是单列索引还是联合索引）都会对应一棵独立的索引B+树。B+树的每个节点只包含索引字段的值和主键值。

当我们根据索引B+树进行查询时，虽然能够迅速找到符合条件的数据，但是我们拿到的数据只包含索引字段和主键值。如果我们查询的是select * from table，这时候查询结果中需要的其他字段就不包含在索引中，这时就需要进行回表操作。回表操作是通过主键去聚簇索引中查找完整的数据，获取完整的字段值。

举个例子，假设你执行了select * from table order by xx1, xx2, xx3的SQL语句，MySQL会通过联合索引的B+树按顺序取出数据，然后再通过主键去聚簇索引查询每一条数据的其他字段。如果返回的数据量很大，MySQL可能会发现，这样的查询效率低下，甚至可能会直接选择全表扫描，而不使用联合索引，因为扫描两个索引（联合索引和聚簇索引）并不比扫描一个索引更高效。

不过，如果你的SQL是select * from table order by xx1, xx2, xx3 limit 10，执行引擎会知道它只需要返回10条数据。因此，它会先从联合索引中筛选出这10条数据，然后根据主键去聚簇索引中查找10次数据，这样就能高效地完成查询。

在这种情况下，我们引入了“覆盖索引”的概念。覆盖索引不是一种新的索引类型，而是一种基于索引的查询方式。覆盖索引的意思是：如果你查询的字段已经包含在索引中，那么你不需要回表去聚簇索引查询其他字段，而是直接从索引中获取所有需要的字段。

比如，假设你有一个SQL语句select xx1, xx2, xx3 from table order by xx1, xx2, xx3，如果这些字段都包含在联合索引中，那么你只需要扫描联合索引的B+树就能获得所有需要的结果，而不需要回表去查询主键对应的数据。这样的查询方式就叫做“覆盖索引”，因为所有需要的数据都可以通过索引提供，避免了回表操作。

因此，写SQL时要特别注意两点：

是否会导致大量回表操作。回表操作会导致查询性能下降，因此在可能的情况下，尽量避免通过select *去查询所有字段，最好明确列出需要的字段。
尽量利用覆盖索引来优化查询，这样就能避免回表到聚簇索引，提高查询效率。

即使在必须回表的情况下，我们可以通过 LIMIT、WHERE 等条件限制回表的次数，减少需要回表的数据量，这样也能提高性能。简言之，先从联合索引筛选出少数数据，再回表去查询主键对应的数据，能够提升查询的效率。

综上所述，我们已经讲解了索引的基本工作原理以及如何通过合理的SQL语句来最大限度地利用索引。下一讲我们将深入探讨设计索引时的一些通用原则，帮助你更好地选择和设计索引。

7. 总结

一旦你所有的查询都能够利用索引，整体查询的速度和性能通常不会太慢。如果查询仍然存在性能问题，那么就需要深入分析查询的执行计划，了解查询的执行原理，并根据执行计划进行SQL优化。

此外，在更新操作方面，我们需要关注三个关键问题：

索引数量不宜过多，过多的索引会导致更新时需要维护多个索引树，从而影响性能。
索引可能会导致锁等待和死锁问题，影响更新操作的性能。
更新操作可能涉及MySQL连接池、redo log文件等问题，这些都会影响更新效率。

目录CONTENT

34-深入研究索引-02