数据库常见问题小结

一、主键相关1.主键和唯一键的区别一张表只能有一个主键可以有多个唯一索引主键字段不允许为 NULL唯一索引字段可以存最多一条 NULLInnoDB 主键是聚簇索引唯一索引是普通二级索引叶子节点存主键值所以使用主键查询更快只有主键能设置自增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. 索引及其作用和优缺点索引数据库的目录结构BTree 为主。作用加快查询速度避免全表扫描优点查询、排序、分组速度提升缺点占用磁盘空间插入 / 更新 / 删除需要维护索引降低写入性能。2. InnoDB 聚簇索引和二级索引区别聚簇索引主键索引叶子节点存储完整整行数据整张表按主键排序一张表只有一个。二级索引普通 / 唯一 / 联合索引叶子节点只存储主键值查询到主键后需要回表再走聚簇索引拿完整数据。3. 回表是什么什么场景不会回表回表通过二级索引拿到主键再去聚簇索引查询完整数据。覆盖索引查询字段全部包含在联合索引内不需要回表性能最优。4. B 树和 B 树索引区别为什么 MySQL 用 B 树B 树所有数据只存在叶子节点非叶子只存索引键B 树每行节点都存数据B 树叶子节点链表相连范围查询、分页遍历极快非叶子节点更小内存可缓存更多索引IO 次数更少。5. Hash 索引和 B 树索引适用场景Hash等值查询极快不支持范围、排序、模糊匹配Memory 引擎使用BTree支持等值、范围、排序、分页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普通索引无唯一性限制仅加速查询。覆盖索引查询字段全部在联合索引中不需要回表explainExtra 显示 Using index。 写法select id,name from user where uid?索引包含 uid,id,name。5. 索引优化1) 索引越多越好吗索引并不是越多越好。插入、更新、删除时需要同步修改所有索引写入性能下降索引占用磁盘内存缓冲池压力变大。2) 百万大表如何建索引业务低峰期执行使用ALTER TABLE ... ADD INDEX LOCKNONE避免锁表优先建立高频查询、区分度高的联合索引避免过长字符串作为索引可前缀索引。3) 前缀索引使用场景长字符串url、文本只取前 N 位建立索引节省空间缺点无法用于 order by、覆盖索引。4) 分页深偏移优化limit 100000,10偏移过大扫描大量数据优化主键过滤where id100000 limit 10延迟关联、覆盖索引分页。6. 锁与索引关联不走索引会升级为表锁走索引仅锁匹配行行锁唯一索引等值命中唯一数据锁范围极小范围查询会产生间隙锁容易死锁。7. Explain 相关explain不能看到锁和真实执行耗时。explain 仅生成执行计划不会真实执行 SQL无锁、无耗时要看真实执行耗时使用explain analyzeMySQL8.0会真实执行并输出实际扫描行数、耗时联表查询 explain 中小表是驱动表。id 相同的情况下type 更好、rows 更少的表为驱动表小表驱动大表limit 100000,10 explain 的问题是rows 扫描行数巨大偏移量大需要扫描前面十万行。优化方案是主键分页、延迟关联、覆盖索引type 性能级别衡量查询好坏。性能从优到劣排序是system const eq_ref ref range index ALL。需要特别注意的是type 是all的原因是索引失效优化器判断使用索引成本更高主动放弃索引走全表扫描。常见原因数据倾斜、隐式转换、字段运算、左模糊 % xxxtype 是 ref 但很慢的原因扫描 rows 预估数量很大需要大量回表没有覆盖索引出现 Using filesort / Using temporary索引区分度极低大量重复数据。key实际使用索引key_len使用索引字段占用字节长度用来判断联合索引使用了几个列。越短越好可判断是否满足最左匹配字符串、是否允许 null 会占用额外字节key_len 判断联合索引用了几列是通过字节长度累加判断比如索引 (a,b,c)key_len 只包含 a 说明只用到第一列最左匹配失效rowsMySQL 预估需要扫描的行数数值越大越慢仅预估不完全准确Extra 优化关键Using index覆盖索引不需要回表性能最优Using filesort 文件排序必须优化。代表 MySQL 无法利用索引排序额外开辟内存 / 磁盘排序出现 order by 无索引。 解决方案排序字段加入联合索引末尾Using temporary 临时表临时表严重损耗性能。 group by、distinct、多表联查无索引时生成临时表。 优化分组字段建立索引。Not exists 仅出现在LEFT JOIN 右表主键 IS NULL的场景匹配到第一条关联数据就终止扫描减少 IO是 MySQL 的内置优化策略不需要优化 SQLRange for each record in 子查询低效建议改成 join 联表。