一、基础原理类
Q1：为什么 MySQL InnoDB 用 B+ 树做索引，而不是 B 树、红黑树、哈希表？
B+ 树非叶子节点只存键，不存数据，同样磁盘页能存更多键，树更矮，磁盘 I/O 更少。
叶子节点用链表相连，范围查询和排序只需顺着链表扫，非常高效。
B 树非叶子也存数据，单页键少、树高，范围查询要中序遍历，效率低。
红黑树是二叉，树高远大于 B+ 树，磁盘 I/O 次数多。
哈希表只支持等值查询，不支持范围和排序，且哈希冲突、扩容成本高。
Q2：B+ 树和 B 树的区别？
Q3：一次查询大概要几次磁盘 I/O？
树高一般 3~4 层，根节点常驻内存，实际约 2~3 次磁盘 I/O 到叶子。
如果是二级索引还要回表，再走一次主键 B+ 树。
Q4：聚簇索引和非聚簇索引（二级索引）的区别？
聚簇索引：InnoDB 主键索引，叶子节点存整行数据，一张表只有一个。
二级索引：叶子节点存索引列 + 主键值，查非索引列需要回表。
MyISAM 都是非聚簇，索引叶子存行地址。
Q5：为什么推荐用自增主键？
自增主键插入顺序追加，减少页分裂和碎片。
主键不宜过大，因为二级索引叶子都要存主键值，主键越大二级索引越大。
UUID 随机插入会导致页分裂、性能下降。
二、索引类型与结构类
Q6：联合索引的存储结构是怎样的？
按定义顺序逐列排序：先按第一列排，第一列相同再按第二列排，以此类推。
所以查询条件必须从最左列开始连续匹配才能充分利用索引。
Q7：什么是覆盖索引？
查询需要的列全部在索引里，不需要回表。
例：索引
(a,b)，查询SELECT a,b FROM t WHERE a=1，EXPLAIN的Extra会显示Using index。好处：减少回表 I/O，提升性能。
Q8：什么是索引下推（ICP）？
MySQL 5.6+ 优化：把 WHERE 中能在索引里判断的条件下推到存储引擎层过滤，减少回表次数。
例：索引
(name, age)，WHERE name LIKE '张%' AND age=20，age 的判断在引擎层完成，不用回表后再过滤。
Q9：唯一索引和普通索引怎么选？
查询性能差距很小。
写入时：普通索引能用 change buffer 缓存非唯一二级索引的写，唯一索引必须读盘校验唯一性，写多读少时普通索引更优。
业务需要唯一约束时才用唯一索引。
Q10：前缀索引是什么？有什么取舍？
对长字符串取前 N 个字符建索引，减小索引体积。
取舍：前缀区分度要够高，否则回表次数多；且不支持覆盖索引（因为不包含完整列值）。
三、最左前缀与索引失效
Q11：什么是联合索引的最左前缀原则？
索引
(a,b,c)能命中的查询：a、a,b、a,b,c。不能命中：
b、c、b,c。范围查询会中断后续列：
a=1 AND b>2 AND c=3，c 用不上索引。
Q12：哪些情况会导致索引失效？
对索引列做函数或运算：
WHERE YEAR(create_time)=2024。隐式类型转换：字符串列传数字，或数字列传字符串。
LIKE以%开头：LIKE '%abc'。OR连接的条件有一侧没索引。不满足最左前缀。
!=、NOT IN、IS NOT NULL有时导致全表扫。优化器估算走索引成本更高时主动放弃。
Q13：WHERE a=1 AND b>2 AND c=3，索引 (a,b,c) 能用到几列？
用到
a和b，c用不上（b 是范围，后面中断）。如果改成
a=1 AND b=2 AND c>3，三列都能用。
Q14：LIKE 'abc%' 和 LIKE '%abc' 有什么区别？
abc%可以用索引（最左前缀匹配）。%abc不能用索引，全表扫。
四、执行与优化器
Q15：EXPLAIN 中 type 的常见取值和优劣？
从优到劣：system > const > eq_ref > ref > range > index > ALL
const：主键或唯一索引等值。ref：普通二级索引等值。range：范围查询。index：扫整个索引树。ALL：全表扫，需优化。
Q16：Extra 中常见信息含义？
Using index：覆盖索引，好。Using where：Server 层过滤。Using filesort：需要额外排序，尽量优化。Using temporary：用了临时表，常见于 GROUP BY、DISTINCT。Using index condition：索引下推。
Q17：优化器为什么会选错索引？怎么办？
原因：统计信息不准、采样偏差、估算成本与实际不符。
办法：
ANALYZE TABLE更新统计；FORCE INDEX强制；改写 SQL；调整索引。
MySQL 优化器是基于成本估算（Cost-Based Optimizer, CBO）选择索引的。它选错，本质是「估算的成本」和「实际执行的成本」不一致。具体原因可以分成几类：
1. 统计信息不准
InnoDB 的统计信息是采样得到的，不是全量精确统计
innodb_stats_persistent开启时，默认采样 20 个页（innodb_stats_persistent_sample_pages）数据分布倾斜、热点更新、大批量导入后，统计信息会严重滞后
2. 采样偏差
采样页数太少 → 基数（cardinality）估算偏差大
比如某列实际有 100 万不同值，采样后估算成 1 万，优化器就会误判索引选择性
3. 索引基数估算错误
SHOW INDEX FROM t里的Cardinality是估算值，不是精确值多列索引、函数索引、前缀索引的基数估算更容易偏
4. 回表成本估算偏差
二级索引查询需要回表，优化器会估算回表行数
如果估算回表行数远小于实际，就会误选二级索引
典型场景：
WHERE a=1 AND b>100，优化器以为只回表几行，实际回表几十万行
5. 数据分布不均匀 / 数据倾斜
某些值占绝大多数行，某些值只有几行
统计信息无法精确刻画直方图（MySQL 8.0 才有直方图，且需手动创建）
6. 索引选择性与实际不符
优化器按「平均选择性」估算，但实际查询命中的是倾斜值
例：
status列 99% 是 1，1% 是 0，查status=1时走索引反而更慢
7. 成本模型参数与实际硬件不匹配
innodb_random_read_cost、innodb_seq_read_cost等参数默认值偏理想化SSD 和 HDD 的随机/顺序读成本差异巨大，默认参数未必适配
8. 查询本身写法导致无法用索引 / 用错索引
隐式类型转换：
WHERE varchar_col = 123函数包裹列：
WHERE DATE(create_time) = '2026-01-01'OR、!=、NOT IN等导致索引失效或选择变差
9. 多表 JOIN 顺序估算错误
JOIN 顺序、驱动表选择错误，会连带索引选择错误
统计信息不准时，JOIN 行数估算偏差会指数级放大
10. 索引本身设计不合理
冗余索引、重复索引让优化器「选择困难」
缺少覆盖索引，导致回表成本被低估
Q18：ORDER BY 怎么利用索引避免 filesort？
让
ORDER BY的列顺序与索引顺序一致，且满足最左前缀。例：索引
(a,b)，ORDER BY a,b可避免排序；ORDER BY b不行。
五、锁与索引的关系
Q19：索引和锁有什么关系？
InnoDB 行锁是加在索引上的。没走索引会退化成锁全表（锁所有行或间隙）。
走唯一索引等值命中，加记录锁；走普通索引或范围，可能加间隙锁/临键锁。
Q20：为什么更新没走索引会锁很多行？
无法定位具体行，只能扫描并锁定扫描到的所有行/间隙，并发急剧下降。
所以 UPDATE/DELETE 的 WHERE 条件一定要走索引。
六、实战与设计类
Q21：一张表建很多索引好不好？
不好。索引占空间；写入时要维护所有索引，写放大；优化器选择成本变高。
原则：为高频查询建索引，联合索引尽量复用，控制数量。
Q22：如何判断一个索引该不该建？
看查询频率、区分度（
cardinality）、是否覆盖、写入代价。区分度低（如性别）一般不适合单独建索引。
Q23：深分页为什么慢？怎么优化？
LIMIT 100000,10要扫描并丢弃前 100000 行。优化：
延迟关联：先用覆盖索引取主键，再回表。
游标：
WHERE id > last_id LIMIT 10。业务上限制页码。
Q24：联合索引 (a,b,c)，查询 WHERE b=1 AND c=2 能走索引吗？
不能有效利用，违反最左前缀。
如果查询列都在索引里，可能走索引全扫（
type=index），比全表扫略好，但仍不理想。
Q25：COUNT(*)、COUNT(1)、COUNT(列) 区别？
COUNT(*)和COUNT(1)统计所有行，InnoDB 会选最小索引扫。COUNT(列)不统计该列为 NULL 的行。性能上
COUNT(*)通常最优。
评论