文章目录
1. 索引创建原则有哪些？2. 什么情况下索引会失效？3. 深度拓展的问答4. 总结
前置知识：索引相关精简概念，快速理解
1. 索引创建原则有哪些？
核心原则： 索引的目的是为了提高查询效率，但同时也增加了写入的开销。因此，创建索引需要权衡利弊，选择最合适的方案。
详细原则及解释：
表中的数据量超过10万以上时考虑创建索引。
解释： 对于数据量较小的表，全表扫描的开销可能并不比索引查找的开销大多少，甚至可能更小。只有当数据量达到一定规模时，索引带来的查询效率提升才能弥补其带来的写入开销。类比： 如果你只有几本书，你可能不需要目录就能很快找到你想找的内容。但如果你有成千上万本书，没有目录就会非常耗时。 选择查询频繁的字段作为索引，如查询条件、排序字段或分组字段。
解释： 索引的核心作用就是加速查询。因此，最应该为那些经常出现在 WHERE 子句（查询条件）、ORDER BY 子句（排序）和 GROUP BY 子句（分组）中的字段创建索引。类比： 书的目录通常会包含章节标题、关键词等，这些都是我们经常用来查找内容的依据。 尽量使用复合索引，覆盖SQL的返回值。
解释： 复合索引（也叫组合索引）是指在多个字段上创建的索引。如果一个查询的 WHERE 条件、排序字段和需要返回的字段都在一个复合索引中，那么数据库可以直接通过索引获取所有需要的数据，而无需回表查询（即再去数据行中获取数据）。这被称为“覆盖索引”。覆盖索引是性能最优的一种情况。类比： 假设你的书的目录不仅列出了章节标题，还列出了每章的关键人物。如果你想找关于某个章节中某个关键人物的内容，直接看目录就能找到，而不需要翻到那一页去看正文。 如果字段区分度不高，可以将其放在组合索引的后面。
解释： 字段的区分度（也叫散列度）是指字段中不同值的数量占总行数的比例。区分度越高的字段，通过索引过滤掉的数据就越多，查询效率提升越明显。如果一个字段区分度很低（比如性别字段，只有男和女两个值），单独为它创建索引效果不佳。如果将其放在复合索引中，最好放在区分度高的字段后面，这样可以利用前面字段的过滤效果。类比： 在书的目录中，如果按照“是否为虚构人物”来排序，这个区分度就很低。如果先按照“章节标题”排序，再按照“是否为虚构人物”排序，那么前面“章节标题”的排序已经过滤掉了大部分内容，再按照“是否为虚构人物”排序就更有意义。 对于内容较长的字段，考虑使用前缀索引。
解释： 对于 VARCHAR、TEXT 等内容较长的字段，如果直接创建完整索引会占用大量存储空间，并且索引效率可能不高。前缀索引是指只对字段的前面一部分字符创建索引。这样可以减小索引大小，提高索引效率。但需要注意的是，前缀索引只能用于等值查询和前缀匹配查询，不能用于范围查询和后缀匹配查询。类比： 如果你有一本书的目录，只列出了每章标题的前几个字，你仍然可以根据前几个字快速找到大部分章节，但对于标题很相似的章节，可能需要进一步查找。 控制索引数量，因为索引虽然可以提高查询速度，但也会影响插入、更新的速度。
解释： 每次对表进行插入、更新或删除操作时，数据库都需要同时更新相应的索引。索引越多，更新的开销就越大，导致写入性能下降。此外，过多的索引也会占用额外的存储空间。类比： 每当你往书架上放一本新书，你都需要更新你的图书目录。书越多，目录越复杂，更新目录的时间就越长。
总结索引创建原则：
创建索引是一个权衡的过程，需要在查询效率和写入效率之间找到平衡。核心思想是：
为查询频繁的字段创建索引。考虑使用复合索引来覆盖查询。关注字段的区分度。对于长文本字段考虑前缀索引。控制索引数量，避免过度索引。
2. 什么情况下索引会失效？
索引失效是指数据库在执行查询时，没有按照预期的使用索引，而是进行了全表扫描或其他效率较低的操作。理解索引失效的原因非常重要，可以帮助我们写出更高效的SQL语句。
核心原因： 数据库优化器会根据SQL语句、索引信息和统计信息来决定是否使用索引以及如何使用索引。如果SQL语句的写法导致优化器认为使用索引不如全表扫描或其他方式更高效，或者无法利用索引的特性，索引就会失效。
详细情况及解释：
没有遵循最左匹配原则。
解释： 最左匹配原则是复合索引的一个重要特性。对于一个创建在字段 A, B, C 上的复合索引，它可以用于查询条件是 A、A AND B、A AND B AND C 的情况。但如果查询条件只有 B 或 C，或者只有 B AND C，那么这个复合索引就无法完全发挥作用，可能导致索引失效。类比： 你的图书目录是按照“作者，书名，出版年份”的顺序排列的。如果你想找某个作者的书，你可以很快找到。如果你想找某个作者的某个书名的书，也可以很快找到。但如果你只知道书名，或者只知道出版年份，就很难直接利用这个目录了。 使用了模糊查询且%号在前面。
解释： 模糊查询使用 LIKE 关键字。如果 % 号在搜索字符串的开头（例如 LIKE '%关键词'），数据库无法利用索引来快速定位匹配的数据，因为索引是按照从左到右的顺序排列的。类比： 你的图书目录是按照书名的首字母排序的。如果你想找书名以“Java”开头的书，你可以很快找到。但如果你想找书名中包含“Java”的书，并且“Java”可能出现在书名的任何位置，那么目录就帮不上忙了。 在索引字段上进行了运算或类型转换。
解释： 如果在索引字段上进行了函数运算（如 YEAR(date_field)）、数学运算（如 price * 1.1）或隐式的类型转换，数据库优化器可能无法直接利用索引。因为它需要先计算出运算结果或进行类型转换，然后再进行比较，这会破坏索引的有序性。类比： 你的图书目录是按照书名的原文排列的。如果你想找书名经过加密处理后等于某个值的数据，你需要先对目录中的书名进行加密，然后再进行比较，这样就无法直接利用目录的排序了。 使用了复合索引但在中间使用了范围查询，导致右边的条件索引失效。
解释： 对于一个创建在字段 A, B, C 上的复合索引，如果查询条件是 WHERE A = 10 AND B > 20 AND C = 30，那么索引可以用于过滤 A = 10 和 B > 20 的数据。但是，由于 B > 20 是一个范围查询，索引在处理完 B 的范围后，就无法继续利用 C 的索引顺序了，因此 C = 30 的条件将无法利用索引。类比： 你的图书目录是按照“作者，书名，出版年份”的顺序排列的。如果你想找某个作者，书名在某个范围内，并且出版年份是某个值的数据。你可以很快找到某个作者，以及书名在某个范围内的数据。但是，在书名范围内的书，它们的出版年份可能不是有序的，所以无法继续利用出版年份的索引。 使用 OR 连接条件，且 OR 前后的条件中有一个没有索引。
解释： 如果使用 OR 连接多个查询条件，并且其中一个条件涉及的字段没有索引，那么数据库为了保证结果的正确性，可能会放弃使用索引，而进行全表扫描。即使 OR 连接的两个条件都有索引，也可能导致索引失效，因为 OR 的语义比较复杂，优化器可能难以有效利用索引。类比： 你想找书名是“Java编程思想”或者作者是“Bruce Eckel”的书。如果只有书名有索引，数据库可能需要扫描全表来查找作者是“Bruce Eckel”的书，然后将结果与通过书名索引找到的结果合并。 使用 NOT IN、NOT LIKE 等否定条件。
解释： 否定条件的查询通常难以利用索引进行快速过滤，数据库可能选择全表扫描。 使用 IS NULL 或 IS NOT NULL。
解释： 某些数据库（如 MySQL 的 MyISAM 存储引擎）的索引不存储 NULL 值，因此 IS NULL 条件可能导致索引失效。然而，对于 InnoDB 存储引擎，索引是存储 NULL 值的，所以 IS NULL 和 IS NOT NULL 条件是可以使用索引的。这取决于具体的数据库和版本。 查询的数据量占总数据量的比例过大。
解释： 如果一个查询需要返回的数据量占总数据量的比例很高（例如超过20%或30%），数据库优化器可能会认为全表扫描比使用索引更高效，从而放弃使用索引。
总结索引失效情况：
索引失效通常是由于SQL语句的写法不当，或者数据库优化器无法有效利用索引的特性造成的。常见的失效情况包括：
违反最左匹配原则。模糊查询 % 在前。在索引字段上进行运算或类型转换。复合索引中途遇到范围查询。使用 OR 连接无索引字段的条件。使用否定条件。某些情况下使用 IS NULL 或 IS NOT NULL。
3. 深度拓展的问答
面试官：你刚才提到了索引创建原则和索引失效的情况。现在我们来深入探讨一下。关于复合索引，你提到了最左匹配原则。你能详细解释一下为什么没有遵循最左匹配原则会导致索引失效吗？
A： 好的。复合索引的底层通常是B+树结构。B+树中的数据是按照索引字段的顺序进行排序的。例如，对于一个创建在字段 A, B, C 上的复合索引，数据会先按照字段 A 排序，在 A 相同的情况下再按照字段 B 排序，在 A 和 B 都相同的情况下再按照字段 C 排序。
当我们使用 WHERE A = 10 进行查询时，数据库可以直接利用B+树的特性，快速定位到 A = 10 的数据块。
当我们使用 WHERE A = 10 AND B = 20 进行查询时，数据库首先定位到 A = 10 的数据块，然后在这些数据块中，再利用字段 B 的排序，快速定位到 B = 20 的数据。
但是，如果我们只使用 WHERE B = 20 进行查询，由于B+树是先按照 A 排序的，直接查找 B = 20 是没有捷径的。数据库需要扫描整个索引树，或者回表进行全表扫描，才能找到所有 B = 20 的数据。这就是为什么没有遵循最左匹配原则会导致索引失效。
面试官：那么，对于复合索引 A, B, C，如果我的查询条件是 WHERE A = 10 AND C = 30，会发生什么？索引会完全失效吗？
A： 这种情况不会完全失效。数据库仍然可以利用索引来过滤 A = 10 的数据。在找到所有 A = 10 的数据后，数据库会回表去获取这些数据行的完整信息，然后在内存中对这些数据进行过滤，找出满足 C = 30 条件的数据。虽然字段 C 的索引没有被利用到，但字段 A 的索引仍然起到了过滤作用，比全表扫描要高效。
面试官： 你提到了模糊查询 % 在前会导致索引失效。那么，如果 % 在后面（例如 LIKE '关键词%'），索引会失效吗？
A： 如果 % 在后面，索引是不会失效的。因为索引是按照从左到右的顺序排列的，LIKE '关键词%' 这样的查询条件可以利用索引的前缀匹配特性，快速定位到以“关键词”开头的数据。
面试官： 你还提到了在索引字段上进行运算或类型转换会导致索引失效。你能举个具体的例子吗？
A： 好的。假设我们有一个 user 表，其中有一个 create_time 字段，类型是 DATETIME，并且我们在 create_time 字段上创建了索引。
如果我们的查询是 SELECT * FROM user WHERE create_time = '2023-07-20'，这是可以直接利用索引的。
但是，如果我们的查询是 SELECT * FROM user WHERE DATE(create_time) = '2023-07-20'，这里对 create_time 字段使用了 DATE() 函数进行运算。数据库需要对表中的每一行数据的 create_time 字段都执行 DATE() 函数，然后将结果与 '2023-07-20' 进行比较。这个过程无法利用 create_time 字段的索引，导致索引失效。
类似地，如果 create_time 字段是 VARCHAR 类型，而我们使用 WHERE create_time = 1678886400 进行查询（这里 1678886400 是一个时间戳），数据库可能会进行隐式的类型转换，将 create_time 字段的值转换为数字类型再进行比较，这也会导致索引失效。
面试官： 你认为一个表最多可以有多少个索引比较合适？有没有一个硬性的限制？
A： 关于索引数量的限制，不同的数据库系统有不同的硬性限制。例如，MySQL 的 InnoDB 存储引擎的单表索引数量上限是 64 个。但是，这只是一个硬性限制，实际应用中我们通常不会创建这么多索引。
我认为并没有一个固定的“合适”的索引数量。这取决于具体的业务场景、表的特点、查询和写入的频率等因素。一般来说，如果一个表的数据量较大，查询非常频繁，而写入操作相对较少，可以适当多创建一些索引。反之，如果写入操作非常频繁，而查询相对较少，就需要严格控制索引数量。
更重要的是，我们应该创建有效的索引，而不是仅仅追求数量。创建索引后，应该通过 EXPLAIN 命令来分析SQL语句的执行计划，确认索引是否被有效利用。如果一个索引创建后并没有被经常使用或者没有带来明显的查询效率提升，那么就应该考虑删除它，以减少写入开销。
我个人认为，对于大多数业务表来说，创建 5-10 个索引可能是一个比较常见的范围。但这仅仅是一个经验值，具体情况需要具体分析。
4. 总结
1. 索引的优点和缺点
优点：
显著提高数据检索速度。可以加速排序和分组操作。可以实现数据的唯一性约束。 缺点：
占用额外的存储空间。增加插入、更新、删除操作的开销（因为需要同时维护索引）。过多的索引会降低写入性能。
2. 索引创建原则
数据量阈值： 表中数据量超过10万以上时考虑创建索引。高频字段： 选择查询频繁的字段作为索引，如 WHERE、ORDER BY、GROUP BY 子句中的字段。复合索引与覆盖索引： 尽量使用复合索引，并努力实现覆盖索引，避免回表查询。字段区分度： 将区分度不高的字段放在复合索引的后面。前缀索引： 对于内容较长的字段，考虑使用前缀索引。控制数量： 控制索引数量，权衡查询和写入性能。
3. 索引失效情况
最左匹配原则： 复合索引查询条件没有从最左边的字段开始。模糊查询： LIKE '%关键词'。字段运算/类型转换： 在索引字段上进行函数运算、数学运算或隐式类型转换。复合索引范围查询： 复合索引中途使用范围查询（>、<、BETWEEN 等）会导致右边的索引失效。OR 连接： 使用 OR 连接条件，且 OR 前后的条件中有一个没有索引。否定条件： 使用 NOT IN、NOT LIKE 等。IS NULL/IS NOT NULL： 在某些数据库或版本中可能导致索引失效（InnoDB 存储引擎通常不会）。统计信息不准确： 数据库优化器判断错误。查询结果集过大： 优化器认为全表扫描更高效。
4. 如何避免索引失效？
遵循最左匹配原则： 合理设计复合索引和查询语句。避免 % 在前： 如果需要模糊查询，考虑使用搜索引擎或其他方案。避免在索引字段上进行运算或类型转换： 尽量在查询条件中使用原始的索引字段。合理使用复合索引： 理解复合索引的特性，避免在中间使用范围查询。谨慎使用 OR： 如果必须使用 OR，确保所有连接的字段都有索引，或者考虑使用 UNION 或 UNION ALL。优化否定条件： 考虑使用其他方式来实现否定查询。定期更新统计信息： 确保数据库优化器能够做出准确的判断。分析执行计划： 使用 EXPLAIN 命令查看SQL语句的执行计划，确认索引是否被有效利用。 确保所有连接的字段都有索引，或者考虑使用 UNION 或 UNION ALL。优化否定条件： 考虑使用其他方式来实现否定查询。定期更新统计信息： 确保数据库优化器能够做出准确的判断。分析执行计划： 使用 EXPLAIN 命令查看SQL语句的执行计划，确认索引是否被有效利用。