序言
MySQL中提高查询性能的一个最有效的方式是对数据表设计合理的索引。索引提供了访问高效数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。，接下来分析一下MySQL中索引失效的案列。
1. 全值匹配我最爱
在MySQL中，索引全值匹配是指在查询中使用的条件完全匹配索引中的索引列，并且这些列的顺序与索引定义的顺序一致。例如，有一个联合索引(col1,col2,col3),当查询条件是WHERE col1 = value1 AND col2 = value2 AND col3 = value3时，就实现了索引全值匹配的原则，这种情况下，数据库可以高效地利用索引来定位和检索数据。
2. 最佳左前缀法则
这种方式类似于上一种全值匹配，在MySQL中建立联合索引时会遵守最佳左前缀法则，即最左优化。对于联合索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。 如果查询条件中没有使用这些字段中第1个字段时，联合索引不会被使用。
3. 主键插入顺序
对于一个使用InnoDB存储引擎的表来说，在我们没有显示创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点上。而记录(数据)是存储在数据页(MySQL和磁盘数据交互的基本单位)中的，数据页和记录又是按照主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽小忽大的话，则可能会造成页面分裂和记录移位。
- 页面分裂：
- 概念：在数据库存储（如基于 B + 树索引的存储结构）中，数据通常存储在页面（Page）里。页面分裂是指当一个页面没有足够的空间来插入新的数据记录时，数据库系统会将这个页面分裂成两个页面的操作。
- 发生场景与过程：- 以 B + 树索引为例，假设一个叶子节点页面（存储实际数据记录的页面）已经快满了，当需要插入一条新的数据记录时，数据库系统会检查该页面是否有足够的空间。如果没有，就会将这个页面的部分数据复制到一个新的页面中，并且调整索引结构中的指针。这样原来的一个页面就分裂成了两个页面，新的数据记录就可以插入到合适的页面中。
- 影响：
- 性能下降：页面分裂是一个比较复杂的操作，它涉及到数据的复制、指针的调整等。这会消耗额外的系统资源，包括 CPU 时间、磁盘 I/O 等，从而导致插入操作的性能下降。
- 索引结构变化：页面分裂会改变索引的结构，特别是 B + 树的形状。如果频繁发生页面分裂，可能会导致 B + 树的高度增加，进而影响查询操作的性能。因为查询操作在 B + 树索引中需要从根节点遍历到叶子节点，树的高度增加意味着查询路径变长。
- 记录移位：
- 概念：- 记录移位是指在数据存储结构中，为了给新插入的记录腾出空间，将现有的记录在存储位置上进行移动的操作。
- 发生场景与过程：- 例如在一些简单的线性存储结构（如数组形式存储的数据）中，如果要插入一条新记录，而存储空间是连续的，可能需要将一部分现有记录向后移动，以便为新记录腾出空间。在数据库的某些存储方式中，比如堆存储（Heap Storage），如果没有足够的空闲空间来插入新记录，并且没有采用页面分裂等其他方式，可能会对记录进行移位操作。
- 影响：
- 性能损耗：记录移位同样需要消耗一定的资源，包括 CPU 时间用于数据的移动，以及可能产生的磁盘 I/O（如果数据存储在磁盘上）。频繁的记录移位会导致插入操作的速度变慢。
- 数据位置变化可能影响相关操作：记录移位会改变数据记录的物理位置。如果有其他操作（如通过指针引用这些记录）依赖于数据记录的原始位置，可能会导致这些操作出现错误或者性能下降。例如，一些应用程序可能已经缓存了数据记录的位置信息，记录移位后这些缓存信息就会失效。
4. 计算、函数、类型转换(自动或手动)导致索引失效
- 计算失效：
- 原理阐述：数据库的索引是按招列的原始值进行构建和排序的。当在索引列上进行计算操作时，数据库无法直接使用索引来定位数据。因为索引存储结构是基于列的原始形式，而计算后的条件与索引的存储循序不匹配。
- 举例说明：- 假设有一个名为
students的表，其中age列有索引。正常的查询语句如SELECT * FROM students WHERE age > 18可以有效地利用索引，因为数据库可以直接根据age列的索引值来查找大于 18 岁的学生记录。但是，如果查询语句变为SELECT * FROM students WHERE age * 2 > 36，这里在age列上进行了乘法计算。数据库在处理这个查询时，不能直接依据age列已有的索引进行查找，因为它需要对每一行的age值进行计算后再比较，这就导致了索引失效。
- 函数失效：
- 原理阐述：函数操作会改变索引列的值的比较方式。索引时按照列的原始数据值构建的，挡在索引列上应用函数时，数据库需要对每一个索引的值执行函数操作，然后再进行比较，这与索引的原始结构不匹配，从而导致索引无法直接使用。
- 举例说明：- 假设在
employees表中，hire_date列有索引。对于查询SELECT * FROM employees WHERE hire_date > '2024-01-01'，数据库可以利用hire_date列的索引来查找在指定日期之后入职的员工记录。然而，若查询变为SELECT * FROM employees WHERE YEAR(hire_date) > 2024，由于在hire_date列上应用了YEAR函数，数据库需要对每一个hire_date值提取年份后再进行比较，而不能直接使用hire_date列的索引，所以索引失效。
- 类型转换失效：
- 原理阐述：- 索引是基于特定的数据类型构建的。当发生类型转换时，无论是自动转换还是手动转换，数据的比较方式都会发生改变。数据库在比较数据时，可能无法按照索引的原始数据类型和结构进行有效的查找，从而导致索引失效。
- 举例说明：- 假设有一个
products表，product_id列是整数类型并且有索引。正常的查询语句如SELECT * FROM products WHERE product_id = 123可以利用索引来查找特定产品记录。但如果查询语句是SELECT * FROM products WHERE product_id = '123'（这里将整数123写成了字符串形式），数据库可能会进行自动类型转换。在这种情况下，索引可能会失效，因为数据库在比较时，不是按照整数索引的原始比较方式进行操作。同样，手动进行类型转换，如SELECT * FROM products WHERE CAST(product_id AS VARCHAR) = '123'也会导致索引失效。
5. 不等于(!=或者<>)索引失效
在查询条件中使用 “!=” 或 “<>” 操作符时，数据库引擎可能会认为无法有效利用索引来缩小查询范围。因为这两个操作符表示的是 “不等于” 条件，这意味着数据库需要检查索引列中的大部分或所有值来确定哪些记录不符合这个条件。
例如，假设有一个包含员工信息的表employees，其中employee_id列是主键并且有索引。如果执行查询SELECT * FROM employees WHERE employee_id!= 10，数据库需要扫描索引中的许多（甚至几乎所有）条目来找到不符合employee_id = 10的记录。相比之下，像employee_id = 10这样的等值查询可以直接通过索引快速定位到符合条件的记录。
6. like以通配符%开头索引失效
- 当在
LIKE操作符中以%开头时，索引失效主要是因为数据库引擎很难利用索引来快速定位满足条件的数据。索引通常是按照列值的顺序进行组织的，例如 B - 树索引（在许多数据库中广泛使用）。 - 正常情况下，如果
LIKE操作符是在列值的中间或结尾使用通配符（如abc%或%abc），索引可以先定位到符合部分条件的值，然后再对剩余部分进行筛选。但是，当通配符%在开头（如%abc）时，索引就不知道从哪里开始查找，因为几乎每个索引项都有可能是满足条件的开头部分。 - 例如，假设有一个
products表，其中product_name列有索引。如果执行查询SELECT * FROM products WHERE product_name LIKE '%phone'，数据库无法从索引中直接找到以任何字符开头且结尾为phone的产品名称。它需要对product_name列中的每个值进行检查，看是否符合这个通配符模式，这就相当于进行了全表扫描。
7. OR 前后存在非索引的列，索引失效
- 当在
OR条件中前后存在非索引列时，数据库查询优化器可能会选择不使用索引。这是因为数据库在处理OR条件时，需要分别评估每个条件分支。如果其中一个分支涉及非索引列，为了确保查询结果的准确性，数据库可能会放弃使用索引来处理整个OR条件。 - 例如，假设有一个
students表，其中student_id列有索引，而student_name列没有索引。如果执行查询SELECT * FROM students WHERE student_id = 10 OR student_name = 'John'，数据库可能会认为使用索引来处理student_id = 10这个条件是有帮助的，但由于student_name = 'John'这个非索引列条件的存在，它不确定如何有效地结合索引查询和非索引查询来得到结果。在这种情况下，优化器可能会决定进行全表扫描，因为这样可以确保所有满足OR条件的记录都能被找到，而不用去处理复杂的索引和非索引条件组合的问题。
8. 数据库和表的字符集统一使用utf8mb4
同意使用utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效
