MySQL性能调优的底层逻辑：索引优化、查询计划与锁机制深度拆解一、数据库性能的基石 1. 索引的本质与B+树结构索引

一、数据库性能的基石

索引的本质：索引是一种有序的数据结构，通过减少全表扫描的行数，大幅提升查询速度。
B+树的优势：多叉树结构：降低树的高度（3层可存储2000万数据），减少磁盘I/O次数。数据全在叶子节点：非叶节点仅存储键值，提升查找效率。双向链表连接叶子节点：支持高效范围查询（如BETWEEN、ORDER BY）。

B+树索引：适用场景：等值查询（=, IN）、范围查询（>, <, BETWEEN）、排序（ORDER BY）。设计原则：复合索引的最左前缀原则：如索引(user_id, status)，可匹配user_id的查询，但无法匹配status的单独查询。避免冗余索引：如idx_user_id和idx_user_id_status可能重复。
哈希索引：适用场景：Memory引擎的等值查询（=），如Session缓存。局限性：不支持范围查询，存在哈希冲突问题。
全文索引：适用场景：文本搜索（如LIKE '%keyword%'），替代低效的模糊查询。

type（访问类型） ：最优到最差：system > const > eq_ref > ref > fulltext > index_merge > range > index > ALL。目标：尽量避免ALL（全表扫描）和index（全索引扫描）。
key（实际使用的索引） ：目标：确保查询命中预期索引，而非使用possible_keys中的其他索引。
rows（预估扫描行数） ：目标：越小越好，若rows接近表总行数，需检查索引设计。
Extra（附加信息） ：常见问题：Using filesort：未命中排序索引，需添加复合索引。Using temporary：未命中分组索引，需优化GROUP BY条件。

案例1：慢查询优化
- 原始查询：SELECT * FROM orders WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 10;
- 问题：未命中排序索引，导致Using filesort。
- 优化方案：添加复合索引(user_id, create_time)，覆盖查询条件与排序字段。
案例2：分页性能瓶颈
- 原始查询：SELECT * FROM users ORDER BY score DESC LIMIT 10000, 10;
- 问题：LIMIT offset, size需扫描前10010行，性能随页码增大而下降。
- 优化方案：使用“延迟关联”：
- sql
- 深色版本
- SELECT * FROM users INNER JOIN ( SELECT id FROM users ORDER BY score DESC LIMIT 10000, 10 ) AS tmp ON users.id = tmp.id;
- 通过先扫描索引score的10行，再关联主表数据，减少扫描行数。

按性能分类：乐观锁：假设冲突概率低，通过版本号或CAS实现（如WHERE version = old_version）。悲观锁：假设冲突必然发生，主动加锁（如SELECT ... FOR UPDATE）。
按粒度分类：表锁（MyISAM默认）：开销小但并发度低，适合读多写少场景。行锁（InnoDB默认）：开销大但并发度高，适合高并发OLTP场景。

间隙锁（Gap Lock） ：作用：防止其他事务在范围内插入数据（解决RR隔离级别下的幻读问题）。风险：可能导致锁竞争，需通过缩小查询范围（如使用唯一索引）规避。
临键锁（Next-key Lock） ：组合锁：行锁 + 间隙锁，覆盖索引记录及其间隙。
MVCC（多版本并发控制） ：原理：通过Undo日志版本链实现读写不阻塞，适用于RC和RR隔离级别。优化点：合理设置innodb_undo_log_truncate，避免Undo日志膨胀。

死锁排查：工具：通过SHOW ENGINE INNODB STATUS查看死锁日志，分析事务的加锁顺序。修复策略：按固定顺序加锁，或使用SET innodb_deadlock_detect=OFF关闭死锁检测（需谨慎）。
锁升级问题：现象：大量行锁导致性能下降，优化器自动升级为表锁。解决：优化索引设计，缩小锁定范围。

读写分离：将读操作与写操作分离，使用共享锁（LOCK IN SHARE MODE）或从库读取。
锁粒度控制：行级锁：适用于高并发更新场景（如订单状态修改）。表级锁：适用于批量操作（如数据迁移）。
事务隔离级别优化：RC（Read Committed） ：允许不可重复读，但减少锁冲突。RR（Repeatable Read） ：通过间隙锁避免幻读，但可能增加锁竞争。

场景：订单表（1000万行数据）查询用户订单耗时2.3秒。
问题：未命中索引，导致全表扫描。
优化方案：添加复合索引(user_id, status, create_time)，覆盖查询条件。使用分页优化技术，减少扫描行数。调整事务隔离级别为RC，减少间隙锁竞争。
效果：查询耗时从2.3秒降至3毫秒，性能提升766倍。