Mysql-索引失效

0.背景

借鉴抖音“老猿说开发”总结的一句口诀。

索引可能失效场景如下：模型数空运最快

序号	关键字	含义	解释
1	模	模糊查询	当使用模糊查询（比如使用LIKE操作符）时，如果查询条件的开头使用了通配符（如%），那么索引可能会失效。因为通配符在查询开始位置时，数据库无法利用索引进行高效的检索，需要进行全表扫描。
2	型	数据类型	当索引列与查询条件的数据类型不匹配时，索引可能会失效。比如，如果索引列是整数类型，但是查询条件使用了字符串类型，那么索引可能无法被使用。
3	数	内部函数	当在查询条件中使用了内部函数时，索引可能会失效。因为内部函数会对查询条件进行处理，导致无法直接利用索引进行匹配。
4	空	NULL、空值	当查询条件中涉及到NULL或空值时，索引可能会失效。因为索引一般不会包含NULL值，所以在涉及到NULL的查询条件下，索引可能无法被有效利用。
5	运	运算	当查询条件中包含了运算操作时，索引可能会失效。比如，如果在查询条件中使用了算术运算符或逻辑运算符，索引可能无法被利用。
6	最	最左前缀原则	MySQL遵循最左前缀原则，即只有在索引的最左前缀被使用时，索引才能被利用。如果查询条件不满足最左前缀原则，那么索引可能会失效。
7	快	全表扫描更快时	在某些情况下，全表扫描可能比使用索引更快，比如当数据量很小或者索引列的基数（即不同值的个数）非常低时，使用索引可能会导致额外的I/O开销，反而不如直接进行全表扫描快速。

1.解析

1.1 使用模糊查询

当使用 LIKE 操作符进行模糊查询时，如果查询条件的开头是一个通配符 %，如 LIKE '%abc'，那么索引可能会失效。因为通配符 % 表示任意字符，包括零个字符，这导致数据库无法利用索引的前缀来快速定位匹配的行。

考虑一个实际的例子，假设我们有一个名为 names 的表，其中包含一个名为 name 的列，我们想要查询所有以 "abc" 结尾的名字。

如果我们执行以下查询：

SELECT * FROM names WHERE name LIKE '%abc';

在这个查询中，LIKE 操作符的模式是以 % 开头，这意味着我们要查找所有以 "abc" 结尾的名字。数据库无法利用索引来直接定位到以 "abc" 结尾的行，因为索引是按照顺序存储的，而查询条件的开头是一个通配符。这就导致了索引失效，数据库可能会选择进行全表扫描来满足查询条件，这可能会降低性能，尤其是在大型表中。

在设计数据库时，如果需要进行模糊查询，应尽量避免在查询条件的开头使用通配符，以便数据库可以有效利用索引来提高查询性能。

1.2 数据类型不匹配

当索引列与查询条件的数据类型不匹配时，索引可能会失效。这可能会导致数据库无法使用索引来加速查询，从而降低查询性能。

假设我们有一个包含用户信息的表 users，其中包含一个名为 age 的列，存储用户的年龄。假设 age 列是整数类型，并且我们在 age 列上创建了一个索引。

现在，如果我们执行以下查询：

SELECT * FROM users WHERE age = '30';

在这个查询中，我们试图查询所有年龄为 30 的用户。然而，如果 age 列是整数类型，而我们在查询条件中使用了字符串 '30'，那么这两者的数据类型不匹配。

即使我们在 age 列上有一个索引，数据库也无法有效利用索引来加速查询，因为它无法将字符串 '30' 转换为整数类型进行比较。这将导致索引失效，数据库可能会选择进行全表扫描来满足查询条件，这可能会降低查询性能，尤其是在大型表中。

在设计数据库时，需要确保索引列与查询条件的数据类型匹配，以便数据库可以有效地利用索引来加速查询。

1.3 使用了内部函数

当在查询条件中使用了内部函数时，索引可能会失效。

这是因为内部函数会对查询条件进行处理，导致无法直接使用索引来加速查询。

假设我们有一个包含用户信息的表 users，其中包含一个名为 created_at 的列，存储用户创建账户的时间。

现在，如果我们执行以下查询：

SELECT * FROM users WHERE DATE(created_at) = '2022-01-01';

在这个查询中，我们试图查询所有在 2022 年 1 月 1 日创建账户的用户。虽然我们在 created_at 列上可能有一个索引，但是由于我们在查询条件中使用了 DATE() 函数对 created_at 列进行了处理，数据库无法直接利用索引来加速查询。

当我们使用内部函数时，数据库需要在查询执行之前对每一行数据进行函数计算，然后再进行条件判断。这会导致无法利用索引来加速查询，可能会选择进行全表扫描来满足查询条件，尤其是在大型表中，这可能会降低查询性能。

在设计数据库时，应尽量避免在查询条件中使用内部函数，以确保数据库可以有效地利用索引来加速查询。如果需要对列进行函数处理，可以考虑在应用程序层面进行处理，而不是在数据库查询中使用内部函数。

1.4 NULL或空值

当查询条件涉及到 NULL 或空值时，索引可能会失效。这是因为索引一般不包含 NULL 值，当查询条件中涉及到 NULL 值时，数据库可能无法有效利用索引来加速查询。

假设我们有一个名为 users 的表，其中包含一个名为 email 的列，存储用户的电子邮件地址，我们在 email 列上创建了一个索引。

现在，如果我们执行以下查询：

SELECT * FROM users WHERE email IS NULL;

在这个查询中，我们试图查询所有电子邮件地址为空的用户。虽然我们在 email 列上可能有一个索引，但是由于我们在查询条件中使用了 IS NULL 条件，数据库可能无法有效利用索引来加速查询。

因为索引一般不包含 NULL 值，当查询条件涉及到 NULL 值时，数据库可能会选择进行全表扫描来满足查询条件，这可能会导致性能下降，尤其是在大型表中。

同理，假设我们有一个名为 products 的表，其中包含一个名为 category 的列，存储产品的类别信息，我们在 category 列上创建了一个索引。

现在，如果我们执行以下查询：

SELECT * FROM products WHERE category = '';

在这个查询中，我们试图查询所有类别为空的产品。虽然我们在 category 列上可能有一个索引，但是由于我们在查询条件中使用了空值，数据库可能无法有效利用索引来加速查询。

在设计数据库时，需要考虑到查询条件涉及到 NULL 或空值的情况，并确保数据库可以有效利用索引来加速查询。

1.5 进行运算

当查询条件中包含运算符时，索引可能会失效。这是因为索引存储的是原始数据的值，而不是计算结果。

假设我们有一个名为 orders 的表，其中包含一个名为 total_amount 的列，存储订单的总金额，我们在 total_amount 列上创建了一个索引。

现在，如果我们执行以下查询：

SELECT * FROM orders WHERE total_amount + 10 = 100;

在这个查询中，我们试图查询所有总金额加上10等于100的订单。

虽然我们在 total_amount 列上可能有一个索引，但是由于查询条件中包含了运算符 +，数据库无法直接利用索引来加速查询。

因为索引存储的是原始数据的值，而不是计算结果。当查询条件中包含运算符时，数据库可能会选择进行全表扫描来满足查询条件，这可能会导致性能下降，尤其是在大型表中。

在设计数据库时，需要考虑到查询条件中是否包含运算符，以及如何优化查询以利用索引来加速查询。

1.6 不符合最左前缀匹配原则

如果一个复合索引（即包含多个列的索引）被创建，那么在查询时，只有从索引的最左边开始的列被使用时，索引才能被有效地利用。

假设我们有一个包含用户信息的表 users，其中包含了 first_name 和 last_name 两列，并且我们在这两列上创建了一个复合索引 (first_name, last_name)。

现在，如果我们执行以下查询：

SELECT * FROM users WHERE last_name = 'Smith';

在这个查询中，我们只使用了复合索引的第二列 last_name，而没有使用第一列 first_name。根据最左前缀原则，如果查询中没有使用索引的第一列，那么索引就无法被有效利用，相当于索引失效。

因此，即使我们在 first_name 和 last_name 列上创建了复合索引，但是在这个查询中，数据库可能会选择进行全表扫描来满足查询条件，这可能会导致性能下降，尤其是在大型表中。

为了确保索引能够被有效利用，应该尽量保证查询条件中包含复合索引的前缀列，以满足最左前缀原则。

1.7 全表扫描更快时

在某些情况下，全表扫描可能比使用索引更快。这可能发生在索引的基数（不同值的数量）非常低，或者数据量非常小的情况下。

假设我们有一个名为 students 的表，其中包含了 grade 列，存储了学生的年级信息，我们在 grade 列上创建了一个索引。

现在，如果我们执行以下查询：

SELECT * FROM students WHERE grade = '12th';

假设在我们的数据集中，只有很少的学生在 12 年级，这意味着索引的基数非常低。在这种情况下，数据库可能会选择执行全表扫描，而不是使用索引。因为进行全表扫描可能比使用索引更快，尤其是在数据量较小的情况下。

此外，如果表中的数据量非常小，比如只有几十行，那么即使使用索引也不会带来明显的性能提升。在这种情况下，数据库可能会选择进行全表扫描，而不是使用索引。

在某些情况下，全表扫描可能比使用索引更快，尤其是当索引的基数非常低或者数据量非常小的情况下。

在设计数据库时，需要综合考虑查询条件、索引的基数以及数据量的大小，以选择最合适的查询执行计划。