✅什么时候索引失效反而提升效率？

典型回答

在数据库查询过程中，通常认为索引是提高数据查询速度的有效方式。然而，在某些情况下，索引可能不仅不会提高效率，反而会降低查询性能。

比较常见的是，当表中的数据量非常小的时候，使用索引可能不会带来性能提升。数据库可能会选择全表扫描而非使用索引，因为在这种情况下，索引的维护成本（如更新、插入或删除数据时维护索引结构）可能超过了它的查询效率优势。

有人疑惑了，为啥优化器这种情况下还会选择索引呢？直接扫全表不就行了么？

确实，这种时候优化器基本就直接扫全表了。但是万一你用了force index，也可能用索引。那么去除force index，让索引失效，也能提升效率。

类似，如果一个索引，他的过滤性不好，数据库优化器可能也会决定全表扫描比使用索引更高效。这是因为如果查询结果包含了表中大部分行，那么使用索引可能需要更多的I/O操作来遍历索引再获取数据，而全表扫描可以更连续地读取磁盘数据。

还有一个就是如果区分度不高也是一样的，就是数据存在严重的不均匀导致倾斜的时候，在这种情况下，优化器可能认为全表扫描比通过索引过滤这些行更有效。

其实以上几条，就是我们在下面这篇文章中讲过的：

✅为什么MySQL会选错索引，如何解决？

还有一种情况也可以通过让索引失效来提升效率。比如说一条查询语句，走A索引和B索引都行，但是优化器选错了，选择了A索引，那么如果我能让他失效，那么他就会选择效率更高的B索引。

---

很多人会奇怪了，优化器为啥会选错？

---

这个问题的答案也在上面的那个链接中，你比如说我的这个问题排查过程：

✅如何做SQL调优：用了主键索引反而查询很慢？

SELECT
  *
FROM
  `table_name`
WHERE
  `DELETED` = 0
  AND `STATE`  = "INIT"
  AND `ID` >= 474968311
  AND event_type = ""
ORDER BY
  id
LIMIT
  100

因为有order by，优化器会倾向于选择ID索引，但是其实STATE的索引过滤性更高好。这个就是一个典型的因为优化器选错了索引导致效率低。那么如果我们能让ID的这个索引失效，那么也能提升效率。

可以用：

SELECT
  *
FROM
  `table_name`
WHERE
  `DELETED` = 0
  AND `STATE`  = "INIT"
  AND `ID` >= 474968311
  AND event_type = ""
ORDER BY
  id + 0
LIMIT
  100

通过这种用函数的方式来让ID索引失效。

原文: https://www.yuque.com/hollis666/xkm7k3/ctpkpgi7gxkgklk8