数据库索引

---

一、索引使用避坑

1. 索引列不能用函数/计算（最基础考点）

• 坑：WHERE YEAR(dt) = '2019' 这类写法，会让索引失效
• 原因：索引存的是原始值，数据库无法直接通过索引计算函数结果，只能全表扫描
• 优化写法：改成范围查询，让索引直接匹配原始值

  -- 坏写法（索引失效）
  SELECT * FROM t1 WHERE YEAR(dt) = '2019';
  
  -- 好写法（索引生效）
  SELECT * FROM t1 WHERE dt BETWEEN '2019-01-01' AND '2019-12-31';

2. 联合索引的「最左匹配原则」

• 规则：索引 idx(a,b,c) 只能匹配 a、a+b、a+b+c 作为查询条件的情况
• 失效场景：跳过前面的列直接用后面的列，比如 WHERE b=? AND c=?，索引完全失效
• 示例：索引 idx(col1, col2)，WHERE col2=10 无法使用索引，因为没有匹配最左列 col1

3. 模糊查询 LIKE 的索引使用规则

• 有效场景：前缀匹配 LIKE 'sql%'，可以走索引
• 失效场景：
  • 前后都带 %：LIKE '%sql%'，无法走索引
  • 后缀匹配 LIKE '%sql'，无法走索引
• 原因：索引是有序存储的，前缀能定位起始位置，前后通配符则需要遍历所有值

4. 覆盖索引与回表优化

• 定义：查询的所有列都在索引中，不需要回表读取聚簇索引的数据
• 性能差异：
  • 覆盖索引：直接从索引树获取所有数据，性能极高
  • 非覆盖索引：通过二级索引找到主键，再回表查数据，额外IO开销大
• 示例：索引 idx(col1, col3)，查询 SELECT col3 FROM t5 WHERE col1=99 GROUP BY col3 就是覆盖索引，性能比带 col2 条件的查询更快（后者需额外过滤数据，无法完全利用索引）

---

二、补充面试必考点

1. 索引设计的额外规则

• 联合索引的顺序：区分度高的列放前面，等值条件列放前面，范围条件列放后面
• 避免创建过多索引：增删改时需要同步维护所有索引，索引越多写操作性能越差
• 避免重复/冗余索引：比如已有 idx(a,b)，就不需要再建 idx(a)，前者能完全覆盖后者的查询场景

2. 索引失效的其他常见场景

• 隐式类型转换：索引列是字符串，查询条件传数字，会导致索引失效
• OR 连接的条件中，有列没有索引，会导致整个查询无法走索引
• 数据分布严重倾斜：比如性别字段，男女比例99:1，数据库可能选择不走索引直接全表扫描

3. 聚簇索引 vs 非聚簇索引（面试高频）

• 聚簇索引：索引和数据存储在一起，InnoDB的主键就是聚簇索引，叶子节点存完整数据
• 非聚簇索引：索引和数据分开存储，叶子节点存主键值，需要回表查询
• 区别：聚簇索引查询更快（直接读数据），非聚簇索引需要额外IO（回表）

---

三、EXPLAIN 中常见的访问类型（type 字段）

按性能从高到低排序，面试要能说清含义：

类型	含义	性能说明
system	系统表，只有一行数据	性能最高
const	主键/唯一索引等值查询，最多匹配一行	性能极高
eq_ref	关联查询中，主键/唯一索引等值匹配	性能很高
ref	普通索引等值查询，匹配多行	性能良好
range	索引范围查询（BETWEEN、IN、LIKE前缀匹配）	性能较好
index	遍历索引树（覆盖索引除外）	性能一般
ALL	全表扫描，无索引可用	性能最差

---

四、EXPLAIN Extra 字段关键信息（面试常问）

• Using index：使用覆盖索引，不需要回表，性能优秀
• Using where：索引失效，需要服务器层过滤数据
• Using temporary：使用临时表，通常是分组/排序操作导致，性能差
• Using filesort：无法利用索引排序，需要文件排序，性能差

---

五、面试索引优化题实战

题目1：日期字段索引失效

-- 原查询（性能差，索引失效）
SELECT * FROM t1 WHERE YEAR(dt) = '2019';

-- 优化后（性能优，索引生效）
SELECT * FROM t1 WHERE dt BETWEEN '2019-01-01' AND '2019-12-31';

考点：索引列不能用函数，需改写为范围查询

题目2：联合索引的最左匹配

-- 索引：idx(col1, col2)
-- 查询1（走索引）：WHERE col1=99 AND col2=10
-- 查询2（无法走索引）：WHERE col2=10

考点：最左匹配原则，查询2缺少最左列 col1

题目3：覆盖索引优化

-- 索引：idx(col1, col3)
-- 查询1（覆盖索引，性能优）：SELECT col3 FROM t5 WHERE col1=99 GROUP BY col3;
-- 查询2（需回表，性能差）：SELECT * FROM t5 WHERE col1=99 AND col2=10 GROUP BY col3;

考点：覆盖索引避免回表，减少IO开销

---

面试速记口诀

1. 索引列无函数，无计算，类型不隐转
2. 联合索引按顺序，最左匹配不能乱
3. 模糊查询看前缀，前后%就失效
4. 覆盖索引是王道，回表性能直接掉
5. 访问类型看 type，const/ref/range 是好样，ALL 就是性能灾