mysql查询性能的优化：避免全表扫描与索引失效

WHERE条件中对索引列使用函数会导致索引失效，因索引存储原始值，无法匹配函数计算结果；应将函数移至右侧或创建函数索引、生成列加索引。

为什么 WHERE 条件里用函数会让索引失效

mysql 在执行查询时，如果在索引列上使用函数（比如 UPPER()、date()、CONCAT()），优化器无法直接利用该列的 B+ 树索引做快速定位，只能退化为全表扫描。

常见错误写法：

select * FROM users WHERE UPPER(username) = 'ADMIN';

即使 username 有索引，这条语句也会扫全表。因为索引里存的是原始值，不是大写后的结果，MySQL 没法拿索引树里的值去和 UPPER() 的输出比对。

• 改法：把函数移到右边，保持左边是纯列名 —— WHERE username = UPPER('admin')（前提是业务允许大小写一致）
• 更稳妥的方案：建函数索引（MySQL 8.0.13+ 支持）：

  CREATE INDEX idx_username_upper ON users ((UPPER(username)));

• 旧版本替代方案：加一个计算列并索引它：

  ALTER TABLE users ADD column username_upper VARCHAR(50) STOred AS (UPPER(username));
  CREATE INDEX idx_username_upper ON users (username_upper);

LIKE 查询以 % 开头为什么会触发全表扫描

当 LIKE 模式以通配符 % 开头（如 LIKE '%abc'），MySQL 无法使用索引的有序性做前缀匹配，只能逐行检查字段内容。

而 LIKE 'abc%' 是安全的，它能走索引范围扫描。

• 避免 LIKE '%xxx'：改用全文索引（FULLTEXT）或倒排索引方案（如 elasticsearch）处理模糊前缀需求
• 如果必须支持前后模糊，且数据量不大，可考虑 GENERATED COLUMN + INDEX 存储反向字符串：

  ALTER TABLE users ADD COLUMN username_rev VARCHAR(50) STORED AS (REVERSE(username));
  CREATE INDEX idx_username_rev ON users (username_rev);
  -- 查询 "包含 abc" 可拆解为：LIKE 'abc%' OR REVERSE() LIKE 'cba%'

• 注意：字符集影响排序行为，utf8mb4_bin 排序规则下索引区分大小写，utf8mb4_0900_as_cs 更适合大小写敏感场景

联合索引的最左前缀原则不是“从左开始连续用”，而是“跳过中间列后索引就断了”

假设你建了联合索引 (a, b, c)，以下查询能用上索引：

• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND b = 2
• WHERE a = 1
• WHERE a = 1 AND c = 3 —— 这条 只能用到 a 列索引，c 不会参与索引查找（b 缺失导致断层）

更隐蔽的问题是范围查询（>、、BETWEEN、LIKE 'xxx%'）之后的列无法用于索引查找：

WHERE a = 1 AND b > 2 AND c = 3

这里 c = 3 不会走索引 —— 因为 b > 2 已经让索引扫描停在某个区间，后续 c 的值在 B+ 树里不再有序可跳。

• 调整列顺序：把等值查询列放前面，范围查询列放后面，尽量减少“断层”
• 必要时拆成两个索引：比如高频查 (a, c)，就单独建一个，别指望靠 (a, b, c) 覆盖所有组合
• 用 EXPLaiN 看 key_len：数值越小，说明实际用到的索引前缀越短

OR 条件很容易让索引失效，尤其混用不同列时

写成 WHERE a = 1 OR b = 2，即使 a 和 b 各自都有索引，MySQL 通常也不会合并使用两个索引（除非开启 index_merge 且满足严格条件）。

默认行为是选其中一个索引，再回表过滤另一部分；或者干脆全表扫描。

• 优先改写为 union：

  SELECT * FROM t WHERE a = 1
  UNION ALL
  SELECT * FROM t WHERE b = 2 AND a != 1;

  这样两条都能独立走索引

• 确认是否启用了 index_merge：SELECT @@optimizer_switch; 中查看是否含 index_merge=on，但别依赖它 —— 它在复杂条件下容易不生效
• 避免在 OR 中混用索引列和非索引列，比如 WHERE a = 1 OR c = 'x'（c 无索引），整条基本放弃索引

真正难的不是加索引，而是看懂 EXPLAIN 输出里 type 是 ALL 还是 range，以及 Extra 有没有出现 using filesort 或 Using temporary —— 那些才是性能拐点。