一、主键相关

1. 主键和唯一键的区别

• 一张表只能有一个主键，可以有多个唯一索引；
• 主键字段不允许为 NULL，唯一索引字段可以存最多一条 NULL；
• InnoDB 主键是聚簇索引，唯一索引是普通二级索引，叶子节点存主键值，所以使用主键查询更快；
• 只有主键能设置自增 auto_increment；
• 主键逻辑代表行唯一标识，可作为外键关联。
• 主键使用场景：
  • 每条数据的唯一标识，关联外键；
  • 分页、根据 ID 查询、分库分表分片键；
  • 需要自增 ID 场景
• 唯一键使用场景：业务字段唯一性校验，如手机号、邮箱、身份证、订单编号、设备编号等，业务上不能重复，但不适合做主键（可能修改、允许为空）。

2. 为什么生产环境每张表必须设置主键？

• InnoDB 表数据依托聚簇索引存储，无主键会自动生成隐藏 rowid，查询、分页、更新性能差；
• 分库分表、乐观锁、分布式 ID、关联外键都依赖主键；
• 批量更新 / 删除、binlog 同步、事务锁机制都以主键为基准；
• 避免全表扫描，精准定位单行数据。

3. 自增主键 vs 雪花 ID vs UUID 优缺点

• 自增 int/bigint ：有序、索引紧凑、插入快、占用空间小。但分库分表会 ID 冲突，容易暴露业务数据量。
• 雪花算法 ID ：分布式唯一、有序、数字类型，适合分表。但依赖机器时间，时钟回拨会产生重复 ID。
• UUID ：本地生成无冲突； 但无序，插入时页分裂严重，索引膨胀，查询慢，占用空间大。

4. 联合主键有什么问题？

• 索引字段变长，占用更多磁盘内存；
• 关联其他表作为外键时字段过多；
• 更新、索引维护成本更高； 规范：尽量用单一 bigint 主键，业务唯一约束交给唯一索引。

5. 如果主键删除了，表会怎么样？

InnoDB 会使用隐藏 6 字节 rowid 作为聚簇索引，业务查询性能大幅下降，不推荐。

二、索引相关

1. 索引及其作用和优缺点

• 索引：数据库的目录结构，B+Tree 为主。
• 作用：加快查询速度，避免全表扫描；
• 优点：查询、排序、分组速度提升；
• 缺点：占用磁盘空间；插入 / 更新 / 删除需要维护索引，降低写入性能。

2. InnoDB 聚簇索引和二级索引区别

• 聚簇索引（主键索引）：叶子节点存储完整整行数据，整张表按主键排序，一张表只有一个。
• 二级索引（普通 / 唯一 / 联合索引）：叶子节点只存储主键值，查询到主键后需要回表，再走聚簇索引拿完整数据。

3. 回表是什么？什么场景不会回表？

• 回表：通过二级索引拿到主键，再去聚簇索引查询完整数据。
• 覆盖索引：查询字段全部包含在联合索引内，不需要回表，性能最优。

4. B 树和 B + 树索引区别，为什么 MySQL 用 B + 树？

• B + 树所有数据只存在叶子节点，非叶子只存索引键；B 树每行节点都存数据；
• B + 树叶子节点链表相连，范围查询、分页遍历极快；
• 非叶子节点更小，内存可缓存更多索引，IO 次数更少。

5. Hash 索引和 B + 树索引适用场景

• Hash：等值查询极快，不支持范围、排序、模糊匹配，Memory 引擎使用；
• B+Tree：支持等值、范围、排序、分页，InnoDB 默认。

三、索引相关

1. 什么是最左前缀匹配原则

建立联合索引 idx(a,b,c)，查询条件必须匹配最左边前列字段，才能走索引。如：

• 能走索引：where a=? /where a=? and b=? /where a=? and b=? and c=?
• 索引失效：where b=? /where c=? /where b=? and c=?

2. 联合索引什么时候失效

• 不满足最左前缀；
• 索引列使用函数、运算，如where date(create_time) = '2026-01-01'；
• 索引列使用隐式类型转换：字符串字段用数字查询；
• 模糊查询 %xxx 前置通配符；
• or 连接无索引字段。
• not in / != / is not null；
• 索引列参与四则运算；
• MySQL 优化器判断全表扫描更快，主动放弃索引。

3. 联合索引字段顺序怎么设计

• 区分度高的字段放前面；
• 等值查询字段放前面，范围查询放最后； 例：idx(uid,status,create_time)，create_time 是范围，放末尾

4. 唯一索引、普通索引、主键索引、覆盖索引

• 主键索引：聚簇索引，唯一非空；
• 唯一索引：二级索引，值唯一，允许一条 null；
• 普通索引：无唯一性限制，仅加速查询。

• 覆盖索引：查询字段全部在联合索引中，不需要回表，explain Extra 显示 Using index。 写法：select id,name from user where uid=? 索引包含 uid,id,name。

5. 索引优化

1) 索引越多越好吗？

索引并不是越多越好。插入、更新、删除时需要同步修改所有索引，写入性能下降；索引占用磁盘，内存缓冲池压力变大。

2) 百万大表如何建索引？

• 业务低峰期执行；
• 使用 ALTER TABLE ... ADD INDEX LOCK=NONE 避免锁表；
• 优先建立高频查询、区分度高的联合索引；
• 避免过长字符串作为索引，可前缀索引。

3) 前缀索引使用场景

• 长字符串（url、文本），只取前 N 位建立索引，节省空间；
• 缺点：无法用于 order by、覆盖索引。

4) 分页深偏移优化

• limit 100000,10 偏移过大扫描大量数据；
• 优化：主键过滤 where id>100000 limit 10；延迟关联、覆盖索引分页。

6. 锁与索引关联

• 不走索引会升级为表锁；走索引仅锁匹配行（行锁）；
• 唯一索引等值命中唯一数据，锁范围极小；范围查询会产生间隙锁，容易死锁。

7. Explain 相关

• explain 不能看到锁和真实执行耗时。explain 仅生成执行计划，不会真实执行 SQL，无锁、无耗时；要看真实执行耗时，使用explain analyze（MySQL8.0+），会真实执行并输出实际扫描行数、耗时；

• 联表查询 explain 中小表是驱动表。id 相同的情况下，type 更好、rows 更少的表为驱动表，小表驱动大表；

• limit 100000,10 explain 的问题是rows 扫描行数巨大，偏移量大，需要扫描前面十万行。优化方案是主键分页、延迟关联、覆盖索引；

• type ：性能级别，衡量查询好坏。性能从优到劣排序是 system > const > eq_ref > ref > range > index > ALL。需要特别注意的是：
  • type 是 all的原因是索引失效，优化器判断使用索引成本更高，主动放弃索引走全表扫描。 常见原因：数据倾斜、隐式转换、字段运算、左模糊 % xxx；

  • type 是 ref 但很慢的原因：

    • 扫描 rows 预估数量很大；

    • 需要大量回表，没有覆盖索引；

    • 出现 Using filesort / Using temporary；

    • 索引区分度极低，大量重复数据。

• key：实际使用索引；
• key_len：使用索引字段占用字节长度，用来判断联合索引使用了几个列。越短越好；可判断是否满足最左匹配；字符串、是否允许 null 会占用额外字节：

  • key_len 判断联合索引用了几列是通过字节长度累加判断，比如索引 (a,b,c)，key_len 只包含 a 说明只用到第一列，最左匹配失效；

• rows：MySQL 预估需要扫描的行数，数值越大越慢，仅预估，不完全准确；
• Extra 优化关键：
  • Using index 覆盖索引，不需要回表，性能最优；
  • Using filesort 文件排序，必须优化 。代表 MySQL 无法利用索引排序，额外开辟内存 / 磁盘排序，出现 order by 无索引。 解决方案：排序字段加入联合索引末尾；
  • Using temporary 临时表，临时表，严重损耗性能。 group by、distinct、多表联查无索引时生成临时表。 优化：分组字段建立索引。

  • Not exists ：仅出现在 LEFT JOIN + 右表主键 IS NULL 的场景，匹配到第一条关联数据就终止扫描，减少 IO，是 MySQL 的内置优化策略，不需要优化 SQL；

  • Range for each record ：in 子查询低效，建议改成 join 联表。