[MySQL 面试题]-索引 35-36 哪些情况会导致索引失效？

35 哪些情况会导致索引失效？

-- 1. 使用 or 导致无法走索引（可以使用索引（索引合并））
select * from user where username = 'a' or age = 20

-- 2. 对索引字段使用函数计算
where year(birthday) = 2000
where salary + 100 = 2000

-- 3. 隐式类转换
where phone = 123456789 (字段为varchar)

-- 4. 使用 !=、<>
where status != 1

-- 5. 使用 is null / is not null（部分版本）

-- 6. ，like %开头 无法利用前缀索引

-- 7.联合索引未遵循最左前缀原则
index(a,b,c) 中只用 where b = ？ 会失效

-- 8. 数据分布极度不均（低选择性）
where gender = ‘M’ （男女只有两个值）

-- 9.索引列参与范围查询后再匹配其他字段
where a > 10 and  b = 20, b 失效

-- 10. 使用 not in / not  exists（部分情况）有些场景会触发全表扫描

1. or

EXPLAIN SELECT * FROM customer WHERE username = 'john_doe' OR age = 25;
{
  "id": 1,
  "select_type": "SIMPLE",
  "table": "customer",
  "partitions": null,
  "type": "index_merge",         -- 使用索引合并优化
  "possible_keys": "idx_username,idx_age,idx_username_age,idx_composite",
  "key": "idx_username,idx_age", -- 使用多个索引
  "key_len": "83,5",
  "ref": null,
  "rows": "2",                   -- 预估检查 2 行记录
  "filtered": 100,               -- 没有额外的过滤，100%的扫描行会被返回
  "Extra": "Using union(idx_username,idx_age); Using where"
}

-- using union 表示使用了‘索引合并-集算法’
-- using where 表示在索引检索后还需要应用where条件过滤

索引合并的工作原理

1. 索引合并-并集（index marge union）算法

   1. 同时扫描 idx_username 和 idx_age 两个索引
   2. 获取满足username条件的行ID集合
   3. 获取满足 age 条件的行ID集合
   4. 对两个集合取并集
   5. 根据合并后的ID集合回表查询完整数据

2.对索引字段使用函数计算

对索引字段使用函数计算
EXPLAIN SELECT * FROM customer WHERE YEAR(birth_date) = 1990;
EXPLAIN SELECT * FROM customer WHERE income + 100 = 5100;
/*
    {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "customer",
    "partitions": null,
    "type": "ALL",             -- 全表扫描
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": "20",
    "filtered": 100,
    "Extra": "Using where"
    }
*/

3.隐式类型转换

EXPLAIN SELECT * FROM customer WHERE mobile = 13800138000;
/*
    {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "customer",
    "partitions": null,
    "type": "ALL",
    "possible_keys": "idx_mobile",
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": "20",
    "filtered": 10,
    "Extra": "Using where"
    }
*/

6. ，like %开头 无法利用前缀索引

EXPLAIN SELECT * FROM customer WHERE username LIKE '%doe';
{
  "id": 1,
  "select_type": "SIMPLE",
  "table": "customer",
  "partitions": null,
  "type": "ALL",
  "possible_keys": null,
  "key": null,
  "key_len": null,
  "ref": null,
  "rows": "20",
  "filtered": 11.11,
  "Extra": "Using where"
}

36 为什么 LIKE '%abc'以 %开头会导致索引失效？

有序前缀匹配（left-to-right prefix match）

like 查询只有在能确定前缀的情况下，才能高效使用索引。

开头是通配符 % ，查询条件无法锁定任何前缀，B+ 树无法确定从哪一个节点开始查找，因此无法使用索引，只能全表扫描。

如何优化？

1. 考虑使用倒排索引（全文索引）

ALTER TABLE users ADD FULLTEXT(name);
SELECT * FROM users WHERE MATCH(name) AGAINST ('abc');

1. 采用 Elasticsearch 等搜索引擎，处理模糊或全文搜索。