MySQL 索引失效的多种情况及例子展示

MySQL 索引失效通常有以下几种情况：

1. 数据类型不匹配：当我们在进行查询时，如果将字符串类型的列与数字类型的数字比较，则索引将失效，例如：

   SELECT * FROM users WHERE age = '18';
   

   在这个例子中，age 是一个整型的列，但是我们用一个字符串类型的 ‘18’ 进行比较，因此索引失效。

2. 函数运算：如果在查询中使用了函数操作，那么索引也会失效，例如：

   SELECT * FROM users WHERE YEAR(create_time) = 2021;
   

   在这个例子中，查询语句使用了 YEAR 函数获取 create_time 列的年份信息，进而与 2021 进行比较。因为使用了函数操作，所以索引失效。

3. 模糊查询：当使用像 %、_ 这样的模糊查询操作时，索引也会失效。例如：

   SELECT * FROM users WHERE name LIKE '%Tom%';
   

   在这个例子中，查询条件使用了 %Tom%，也就是要查找名字中包含 Tom 的所有用户。由于查询条件不是以具体值开头，因此无法使用索引加速查询。

4. 小数据范围查询：如果我们要查询的数据非常小，那么数据库查询优化器可能会选择直接全表扫描，而不是使用索引进行查询，例如：

   SELECT * FROM users WHERE age > 99 AND age < 100;
   

   在这个例子中，查询条件 age > 99 AND age < 100 只查询了一小部分数据，此时全表扫描比使用索引进行查询更加高效。

5. 范围查询：当我们进行范围查询(>、<、BETWEEN、IN)时，如果查询结果中的数据太多，MySQL 可能会认为使用索引扫描的代价过高，此时会放弃使用该索引，而采用全表扫描的方式进行查询。例如：

   SELECT * FROM users WHERE age > 18;
   

   在这个例子中，查询条件 age > 18 查询结果中包含大量数据，此时使用索引扫描的代价过高，因此 MySQL 可能会放弃使用索引。

6. 字段参与计算：当索引列被包含在某个计算表达式中时，也可能会导致索引失效。例如：

   SELECT * FROM users WHERE age + 10 = 28;
   

   在这个例子中，查询条件 age + 10 = 28 将 age 列包含在了计算表达式中，因此该索引将失效。

7. 索引列顺序不符合查询条件：当查询条件中的列排列顺序与创建索引的列顺序不一致时，也可能导致索引失效。例如，如果创建了 (age, gender) 的联合索引，而查询语句中使用了 gender 列的查询条件，此时该索引将失效。

   SELECT * FROM users WHERE gender = 'male' AND age > 18;
   

8. 查询条件中使用了 IS NULL 或 IS NOT NULL：当使用 IS NULL 或 IS NOT NULL 查询条件时，MySQL 可能会忽略索引而执行全表扫描。例如：

   SELECT * FROM users WHERE age IS NULL;
   

   在这个例子中，查询条件使用了 IS NULL 操作符，此时 MySQL 可能会忽略 age 列的索引而执行全表扫描。

9. 查询表达式中有 OR：当查询表达式中包含 OR 操作符时，MySQL 可能会选择全表扫描而忽略索引，特别是当 OR 操作符前面的查询条件无法利用索引时。例如：

   SELECT * FROM users WHERE age = 18 OR name = 'Tom';
   

   在这个例子中，OR 操作符前面的 age 查询条件可以利用索引，但是后面的 name 条件无法利用索引，因此 MySQL 可能会选择全表扫描而忽略索引。

10. 列类型被强制转换：当查询中使用了 CAST 或 CONVERT 等函数强制转换列类型时，MySQL 可能会忽略索引而执行全表扫描。例如：

SELECT * FROM users WHERE CAST(age AS VARCHAR(10)) = '18';

在这个例子中，查询语句使用了 CAST 函数将 age 列转换为字符串类型，此时 MySQL 可能会忽略 age 列的索引而执行全表扫描。

11. 隐式转换：当查询条件中的数据类型与索引列的类型不匹配时，MySQL 会进行数据类型的隐式转换，这种转换会使索引失效。例如：

SELECT * FROM users WHERE age = '18';

在这个例子中，查询条件中的 age 是字符串类型，但 age 列是整型，此时 MySQL 会进行隐式转换，使索引失效。

12. 表连接顺序不当：当进行表连接操作时，正确的连接顺序有助于提高查询的效率。如果连接的顺序不当，可能导致索引失效。例如：

SELECT * FROM users u JOIN orders o ON u.user_id = o.user_id WHERE u.age = 18;

在这个例子中，我们需要在 users 表和 orders 表之间进行 join 操作，如果 join 的顺序不当，可能导致索引失效。

13. 数据分布不均匀：如果索引列中的数据分布不均匀，例如某一列中有大量重复值，则索引对查询的优化效果可能就不显著，甚至可能导致全表扫描。这种情况下，可以考虑在索引中加入更多的列，以提高数据的唯一性。

14. 索引字段过长：当索引字段过长时，可能会导致索引失效，因为索引字段的长度直接影响索引的大小和查询性能。通常情况下，应该尽量缩短索引字段的长度，以更快地查询和更节省的存储空间。

15. 元素过多的 IN 子句：当使用 IN 子句查询时，如果元素过多，例如超过了 1000 个，那么 MySQL 可能会选择全表扫描而不是使用索引，因为这样可以避免在内存中创建太多的数据结构。

16. 子查询问题：子查询也可能会导致索引失效，因为子查询可能会生成临时表，或者在查询条件中使用了不一致的数据类型，都会使索引失效。可以通过将子查询转换成连接查询或使用临时表的形式进行优化。

17. 数据库表过大：当数据库表过大时，索引失效的概率也会增加，因为索引需要排序、查找等操作，如果数据量过大，这些操作可能会消耗很长的时间。在这种情况下，可以考虑对表进行分区或者拆分，以减少索引失效的可能性。

18. 多表 JOIN：当进行多表 JOIN 操作时，如果 JOIN 的表过多，可能会导致索引失效，因为 MySQL 只能使用其中一个表的索引进行查询。在这种情况下，可能需要优化 SQL 语句中的表连接顺序以及各表之间的关联方式，以使索引能够最大化地发挥作用。

19. 索引使用率低：当索引使用率比例低时，也可能会导致索引失效。例如，如果表中的数据量很少，但是创建了许多索引，就会使得索引失去作用。针对这种情况，可以通过删除不必要的索引、合并索引、缩短索引长度等方法进行优化。

20. 数据库服务器配置不当：如果数据库服务器的配置过低，例如 CPU、内存、硬盘等配置不足，那么索引可能会失效，从而影响查询性能。在这种情况下，可以考虑升级硬件或者优化数据库服务器配置以提高性能。

综上所述，MySQL 索引失效的原因有很多，需要我们在实际应用中仔细排查并解决问题。在创建索引时，需要考虑查询类型、数据类型和数据分布等多方面因素，以选择合适的索引方式和策略；在查询优化中，需要执行 EXPLAIN 分析、优化 SQL 语句、查看系统性能指标等，以找到瓶颈并进行优化。