mysql 索引失效

难崩侠 #索引 #mysql

如何防止索引失效

  1. like %字符串
  2. 对索引列函数计算
  3. 对索引列表达式计算
  4. 索引列是字符串，被隐身转换成数字，（相当于2 情况了）
  5. 不符合最左匹配原则
  6. or 表达式中有非索引列
  7. 包含范围过大，如 between 和 >/< 包含的范围过大，还有 索引列 null 值过多（范围坑）

具体解释如下

数据库的索引（Index）就像是书本的目录或者字典的拼音检字表。它的核心原理是有序排列。只要你破坏了这个“有序性”，数据库就没法像翻目录一样快速定位，只能从头到尾一页一页翻（全表扫描）。

既然你希望生动形象地理解，我们就把 MySQL 的 B+ 树索引 想象成一本按“姓名拼音”排序的电话簿。

1. 模糊匹配：like %xx 或 like %xx%

💡 形象比喻

你在查电话簿，如果我告诉你：“帮我找姓‘张’的人”，你一眼就能翻到。但如果我告诉你：“帮我找名字结尾是‘亮’的人”，或者“名字里包含‘亮’的人”，你就傻眼了。因为“张亮”、“王亮”、“诸葛亮”分散在书的各个角落，你必须把整本书翻一遍。

⚙️ 技术原理

索引是按照从左到右的字符顺序构建的。like '张%' 能走索引，是因为它确定了开头，范围是可控的。而 % 在左边，意味着起始位置不确定，索引的有序性在搜索时完全失效。

2. 对索引列使用函数

💡 形象比喻

假设电话簿是按“出生日期”排序的。我让你找“1990年出生的人”，你可以很快定位。但如果我说：“请帮我找**出生年份（YEAR函数）**等于 1990 的人”，虽然逻辑一样，但对机器来说，它必须把每一行数据的日期都取出来，塞进 YEAR() 函数算一遍，再看结果是不是 1990。

⚙️ 技术原理

索引树里存的是列的原始值，而不是函数计算后的值。一旦加了函数，MySQL 就不知道这个结果在树的哪个位置了。

晦涩词语解释：全表扫描（Full Table Scan） —— 数据库放弃使用索引，像盲人摸象一样把磁盘上整张表的数据一条条读出来对比。

3. 对索引列进行表达式计算

💡 形象比喻

你要找 id 为 100 的员工。如果你写 WHERE id = 100，没问题。但如果你写 WHERE id + 1 = 101，数据库虽然知道数学上是一样的，但它不会先帮你解方程。它会死板地把每个 id 拿出来加 1，再判断等不等于 101。

⚙️ 技术原理

B+ 树的搜索路径是基于索引列值的直接比较。表达式改变了操作数，使得索引引擎无法直接在树节点上进行二分查找。

4. 隐式类型转换（字符串转数字）

💡 形象比喻

假设你的手机号在数据库里存的是字符串 "13800138000"。

  • 查询时你写：WHERE phone = 13800138000（注意没加引号，是数字）。
  • MySQL 为了能比较，会悄悄调用 CAST(phone AS SIGNED) 把索引列转成数字。
    这就回到了第 2 点：对索引列使用了函数。

⚙️ 背景补充

为什么 WHERE 字符串索引 = 数字 会失效，而 WHERE 数字索引 = '字符串' 有可能不失效？
因为 MySQL 的规则是“字符串转数字”。当前者发生时，函数作用在索引列上；后者发生时，函数作用在你的输入参数上，索引列本身没动，所以能走索引。

5. 联合索引与最左匹配原则

💡 形象比喻

联合索引 (省, 市, 区) 就像邮寄地址。它是先按“省”排，省一样再按“市”排，市一样再按“区”排。

  • 如果你只拿“市”去查，没有“省”，这本地址簿就没法用了，因为每个省都有可能有一个“朝阳区”。
  • 必须从左往右用：你可以查“广东省”，或者“广东省+深圳市”，但不能跳过省查“深圳市”。

6. OR 语句中存在非索引列

💡 形象比喻

老师说：“请班里**有车（索引列）或者家里有猫（无索引列）**的同学站起来”。

  • 找有车的很快，看登记表就行。
  • 但找有猫的，老师必须挨个问全班同学。
    既然为了找“有猫的”已经要把全班问一遍了（全表扫描），那索性找“有车的”也顺便一起问了，不再费劲去翻登记表了。

⚙️ 技术原理

OR 要求满足其一即可。如果其中一个条件必须全表扫描，那么为了获取最终结果集，MySQL 往往认为直接全表扫描比“先查索引再全表扫描最后合并”代价更低。

🚀 总结与建议

导致失效的行为解决方法
左模糊查询尽量使用右模糊 xx%，或使用全文索引（Elasticsearch）。
函数/计算将计算移到等号右边。如 id = 100-1 而非 id+1 = 100。
隐式转换参数类型一定要与字段类型严格匹配（该加引号加引号）。
联合索引查询条件必须包含索引的最左侧列。

您是否需要我针对其中某一种情况，为您展示一下具体的 SQL 优化前后对比代码？

你说得非常对！这是一个非常实用的观察。在数据库优化中，我们管这个现象叫 “查询回表代价过大”。

即便是你完美避开了前面说的函数、类型转换，且字段上有索引，MySQL 依然可能主动作死，放着索引不用，直接去全表扫描。

💡 形象比喻：去果园摘苹果

假设你在一张 100 万行的表中找数据，索引就像是**“采摘指南”**，告诉你哪一排哪一棵树上有你要的苹果。

  1. 范围小（between 1 and 10）：
    指南说：“去第 5 排找”。你走过去，摘下 10 个苹果，轻松愉快。这是索引扫描。
  2. 范围大（between 1 and 900,000）：
    指南说：“第 1 排有、第 2 排有...一直到第 90 万排都有”。
    这时候你发现：我每看一眼指南（查索引），都要跑去树下摘一次（回表）。跑 90 万次来回，腿都要断了！
    你心横： “算了，我直接开着收割机从第一排推到最后一排吧（全表扫描）。”

⚙️ 技术原理：为什么会“失效”？

这里涉及到一个核心概念：回表 (Look up / Random I/O)。

  • 二级索引（非主键索引）：叶子节点存的是主键 ID。
  • 查询过程：MySQL 先在索引树里找到主键 ID，然后再拿着 ID 去主键索引树（聚簇索引）里找整行数据。

为什么范围大会导致全表扫描？

当 BETWEEN 的范围占到全表数据的 20% ~ 30% 以上时（这个比例由 MySQL 的优化器根据统计信息动态计算），MySQL 会认为：

  1. 索引查找：需要先读索引页，再产生大量的随机 I/O 去回表取数据。
  2. 全表扫描：虽然数据量大，但是顺序 I/O，一次性读出一大片磁盘块。

**优化器（Optimizer）**经过计算发现，与其在索引和表之间反复横跳，不如直接“一把梭”全扫了更快。所以，索引并没消失，只是被优化器嫌弃了。

🛠️ 还有哪些类似的“范围坑”？

除了 BETWEEN，以下情况如果范围过大，也会导致同样的结果：

  1. IN (...) 列表太长：
    WHERE id IN (1, 2, 3 ... 5000个)。
  2. > / < 大于小于：
    WHERE age > 18（如果表里 99% 都是成年人）。
  3. IS NOT NULL：
    如果这一列几乎全是数据（只有极少数 NULL），查 NOT NULL 也会触发全表扫描。

🚀 怎么解决这种“范围过大”？

如果你确实需要查询这么大的范围，但又不想全表扫描拖垮数据库，可以试试这几招：

  • 索引覆盖 (Covering Index)：
    如果你只需要查询 id 和 name，而你的索引刚好就是 (id, name)。这时候 MySQL 发现不需要回表（在索引树里就能拿到所有数据），那么即使是 BETWEEN 全表，它也会走索引。
  • 分批查询：
    不要一次 between 1 and 1000000，而是分十次，每次查 10 万条。
  • 强制索引 (Force Index)：
    虽然可以用 FORCE INDEX 强迫 MySQL 走索引，但通常不建议。因为优化器通常比人更懂磁盘读写代价，强制走索引可能反而更慢。