MySQL 核心机制与性能优化实战总结
在数据库开发中，理解底层原理往往比死记硬背配置更重要。本文梳理了 MySQL 的核心知识点，涵盖存储引擎、索引策略、锁机制及事务隔离等关键领域，旨在帮助开发者在实际项目中做出更优的技术选型。
一、存储引擎选择
MySQL 支持多种存储引擎，最常用的是 MyISAM 和 InnoDB。两者的设计目标截然不同：MyISAM 专注于读取性能，而 InnoDB 则专注于事务处理。
主要区别：
- 事务支持：InnoDB 支持 ACID 事务，MyISAM 不支持。
- 外键约束：仅 InnoDB 支持。
- 索引结构：InnoDB 采用聚集索引（聚簇索引），数据文件本身就是索引文件，主键索引的叶子节点存储完整数据记录；MyISAM 则是非聚簇索引，叶节点存储的是数据记录的地址。
- 行数统计：InnoDB 不保存表的具体行数，
SELECT COUNT(*)需要全表扫描；MyISAM 用一个变量保存总行数，查询极快。 - 全文索引：MyISAM 原生支持，InnoDB 早期版本不支持（5.6+ 已支持）。
实际开发中，除非有极端的读性能需求且无需事务，否则默认推荐使用 InnoDB。
二、索引原理与使用
索引是提升查询效率的关键，但用不好也会成为瓶颈。
1. B+ 树 vs Hash 索引 B+ 树是平衡多叉树，层级间有序且指针相连，适合范围查询和排序。Hash 索引通过哈希算法直接定位，仅适用于等值查询，不支持范围、排序或联合索引的最左前缀匹配。在 HEAP 表中若基数大且无范围查询，Hash 索引优势明显。
2. 为什么推荐自增主键？ 使用自增列（INT/BIGINT）作为主键时，写入顺序与 B+ 树叶子节点分裂顺序一致，能减少页分裂带来的性能损耗。若未指定自增主键且无唯一索引，InnoDB 会隐式生成 ROWID，虽可用但不如显式自增可控。
3. 联合索引与最左前缀 联合索引是两个或多个列上的索引，遵循最左前缀匹配原则。利用附加列可缩小搜索范围，但不能简单等同于多个单列索引。注意，范围查询后的字段索引可能会失效。
4. 何时避免建索引？
- 表记录太少。
- 频繁增删改的表。
- 数据重复度高且分布均匀的字段。
- 经常与主字段一起查询但主字段索引值过多的情况。
三、锁机制与并发控制
并发场景下，锁的管理直接影响系统稳定性。
1. 锁的粒度
- 表级锁：MyISAM 默认，锁定整张表，开销小但冲突概率高。
- 行级锁：InnoDB 默认，只锁定操作行，并发度高但开销大，可能引发死锁。
2. InnoDB 行锁算法 包括 Record Lock（记录锁）、Gap Lock（间隙锁）和 Next-key Lock（临键锁，二者组合）。了解这些有助于排查死锁问题。
3. MVCC（多版本并发控制）
这是 InnoDB 实现读写不冲突的核心。在读提交（RC）和可重复读（RR）级别下生效。快照读读取历史可见版本不加锁，当前读（如 SELECT ... FOR UPDATE）则加锁保证最新数据。RR 级别下通过 MVCC 防止幻读。
4. 死锁处理
当线程循环等待资源时发生死锁。可通过设置 innodb_lock_wait_timeout 超时等待，或开启 innodb_deadlock_detect 主动检测并回滚部分事务。
四、事务与隔离级别
事务需满足 ACID 特性。InnoDB 提供四种隔离级别：读未提交、读提交、可重复读（默认）、串行化。不同级别对脏读、不可重复读和幻读的防护能力不同，需根据业务一致性要求权衡。
五、表分区与优化建议
表分区将逻辑上的一张表物理拆分为多个部分，便于管理海量数据。支持 RANGE、LIST、HASH、KEY 等类型。需注意分区字段必须包含所有主键或唯一索引列，且无法使用外键。
