探索MySQL的InnoDB索引失效

MySQL 8+

InnoDB

-

 

序章

索引失效，发生在 已经建立索引，但是，查询（SELECT）时没有用到建立的（预期会用到）索引的情况下。

失效原因有两个方面：

1、建立索引的方式错误

需要弄清楚 字段的 #区分度（极其重要） 这个概念。选择 区分度高 的建立索引。

2、某些SELECT语句不支持 使用索引

注意，除了索引失效，导致 慢查询 的另一个重要原因是——没有建立索引，没有在 创建表 的时候第一时间建立索引。

 

本文探索 MySQL使用时常见的索引失效情况，做个汇总。

 

通义千问

问题：InnoDB：索引失效会在什么情况下发生？

回答：

在 InnoDB 存储引擎中，索引失效 是指 查询优化器 决定不使用已存在的索引，而是 选择全表扫描 或 其他方式来执行查询。

以下是一些常见的索引失效情况：

1）选择性低的索引

原因：如果索引的选择性很低（即索引列的重复值很多），MySQL 可能会选择不使用该索引，因为全表扫描可能更快。

#区分度

2）使用 OR 运算符

原因：当查询条件中包含 OR 运算符时，如果 OR 连接的条件不能使用同一个索引，MySQL 可能会选择不使用索引。

3）使用函数或表达式

原因：在索引列上使用函数或表达式会导致索引失效，因为 MySQL 无法直接使用索引进行查找。

4）使用 LIKE 模糊查询

原因：如果 LIKE 模糊查询的前缀是通配符（如 %），索引将失效。

5）数据类型不匹配

原因：如果查询条件中的数据类型与索引列的数据类型不匹配，MySQL 可能会选择不使用索引。

6）使用 NOT 运算符

原因：使用 NOT 运算符（如 != 或 <>）时，MySQL 可能会选择不使用索引，因为这些运算符通常会导致全表扫描。

7）使用 IN 运算符

原因：如果 IN 运算符中的值列表非常大，MySQL 可能会选择不使用索引。

8）索引列上有大量 NULL 值

原因：如果索引列中有大量 NULL 值，MySQL 可能会选择不使用该索引。

9）复合索引的使用不当

原因：如果复合索引的前导列没有出现在查询条件中，MySQL 可能会选择不使用该复合索引。

10）表统计信息不准确

原因：如果表的统计信息不准确，MySQL 的查询优化器可能会做出错误的决策，导致索引失效。

11）使用 LIMIT 时

原因：在某些情况下，使用 LIMIT 时，MySQL 可能会选择不使用索引，特别是当 LIMIT 的偏移量很大时。

12）使用 JOIN 时

原因：在复杂的 JOIN 查询中，如果 JOIN 条件不使用索引，MySQL 可能会选择不使用索引。

 

解决方法

1、优化查询条件：确保查询条件尽可能精确，避免使用函数、表达式和通配符。
2、使用合适的索引：为查询条件中的列创建合适的索引，特别是复合索引。
3、定期更新统计信息：使用 ANALYZE TABLE 命令定期更新表的统计信息。
4、避免使用 SELECT *：只选择需要的列，减少数据传输量。
5、使用 EXPLAIN 分析查询：使用 EXPLAIN 命令分析查询计划，确保索引被正确使用。

 

注，大家可以自行去提问。

 

参考资料#2

补充了2条：

13）数据表过度索引：太多

文中提到，索引太多，可能导致 查询优化器 选择错乱。

14）数据表过度庞大

文中提到，数据表太大时，索引的效果减弱了。

文中提到，可以使用 分区（PARTITION BY）、分片（ShardingSphere 等工具） 解决该问题。

或许，还有 分库，还有 使用 其它大数据存储工具。

 

小结

基本的 索引失效 大概就这些了，足够平时开发使用了。

 

进一步探索：TODO

1、分区、分表、分库 的索引如何建立？

2、MySQL 的 一些集群方案中怎么建立索引？这涉及到 大数据方面使用 MySQL 了吧？不太懂。

 

---END---

 

本文链接：

https://www.cnblogs.com/luo630/p/18448905

 

参考资料

1、通义千问

2、MySQL索引失效及避免策略：优化查询性能的关键

作者：糜终

2024-09-30

https://developer.aliyun.com/article/1615655

3、

 

ben发布于博客园

ben发布于博客园