MySQL后端开发核心知识点

一、存储引擎只重点 InnoDBMySQL 5.5 以后默认引擎是InnoDB也是现代企业项目唯一使用的引擎。InnoDB 特点支持事务支持行级锁并发性能好支持外键实际开发基本不用基于B 树索引结构依靠MVCC 实现无锁并发读写MyISAM 不支持事务和行锁并发差已基本淘汰。InnoDB引擎中事务原理redo log重做日志记录做了什么修改实现事务的持久性WALwrite-ahead-logging先写日志再写磁盘事务提交只需写redo log顺序IO极快在刷盘时出现错误可进行数据恢复undo log回滚日志记录数据被修改前的信息当执行回滚时可以从undo log中的逻辑记录读取相应的内容并进行回滚保证原子性开始事务 【1】生成 Undo Log保存旧数据用来回滚 【2】修改内存中的数据页还没写磁盘 【3】写入 Redo LogWAL 原则先写日志 【4】提交事务commit 【5】后台慢慢把内存数据刷到磁盘随机IO二、索引重中之重索引是提高查询效率的数据结构InnoDB 使用B 树。常见索引类型主键索引PRIMARY KEY普通索引INDEX唯一索引UNIQUE联合索引多字段组合最左前缀原则联合索引(a,b,c)能命中a、a,b、a,b,c跳过前面字段无法命中整个索引索引失效常见场景where字段使用函数、运算模糊查询%前缀前缀不能模糊类型隐式转换字符串要加 全表扫描比索引更快时优化器会放弃索引or连接有一个字段没索引违反最左前缀原则三、EXPLAIN 执行计划用于查看 MySQL如何执行 SQL是否使用索引。重点看这几列type索引查询效率system const ref range index ALL至少要达到range / refALL 代表全表扫描需要优化key实际命中的索引rows扫描行数越小越好Extra额外信息如Using index、Using filesort等作用判断 SQL 是否走索引定位慢查询原因优化联合索引、查询语句四、事务与 ACID事务是一组不可分割的 SQL要么全部成功要么全部失败。ACID 四大特性原子性Atomicity不可分割一致性Consistency执行前后数据合法隔离性Isolation事务之间互不干扰持久性Durability提交后永久生效Undo Log → 保证原子性AtomicityRedo Log → 保证持久性DurabilityMVCC 锁 → 保证隔离性Isolation日志 锁 刷盘 → 保证一致性Consistency五、事务隔离级别读未提交READ UNCOMMITTED会出现脏读、不可重复读、幻读读已提交READ COMMITTEDOracle 默认解决脏读可重复读REPEATABLE READMySQL 默认解决脏读、不可重复读通过间隙锁 MVCC解决幻读串行化SERIALIZABLE最高级别无并发问题性能最差企业开发常用READ COMMITTED 或 REPEATABLE READ六、MySQL 锁机制InnoDB 主要使用行锁MyISAM 只支持表锁。行锁是加在索引上的不是加在数据行上。如果查询没有命中索引行锁会退化为全表加行锁并发极低。间隙锁锁索引之间的空隙RR级别生效专门防止范围查询时出现幻读ab间隙锁GAP 锁 行锁 临键锁 类似ab]作用在RR 隔离级别下解决幻读记录锁锁单行防修改。间隙锁锁空隙防插入。临键锁锁区间 记录RR 默认彻底解决幻读。Java 后端开发不需要手动加锁只需要合理建索引避免无索引更新避免长事务七、MVCC 多版本并发控制MVCC 是 InnoDB 实现高并发、无锁读写的核心机制。基本思想每行数据保存多个版本读不加锁写加锁读写不阻塞依靠undo log 版本链readView可见性判断 在RR级别仅在事务中第一次指向快照读时生成readView后续复用 在RC级别每次执行快照读时都生成新的readView隐藏字段1DB_TRX_ID记录生成这行数据的事务ID判断这个版本是谁改的、是否可见2DB_ROLL_PTR: 回滚指针指向undo log里的上一个历史版本靠他串联版本 3DB_ROW_ID: 隐藏主键表没有主键时就用这个当主键了解为主实现效果快照读普通 select无锁当前读select for update /update/insert加锁八、开发与面试总结业务开发只需要掌握正确建表、建索引能用 EXPLAIN 分析 SQL理解事务与隔离级别知道行锁与索引的关系了解 MVCC 思想读不加锁写加锁读写互不阻塞既保证事务隔离又提升并发性能存储过程、触发器、函数、视图、运维等高阶内容Java 后端基本不用。把逻辑写在 Java 代码中数据库只做存储是现代企业标准规范。Redo LogInnoDB→ 崩溃恢复保证不丢数据BinlogServer→ 主从库复制、数据恢复保证数据能复制、能回滚Undo LogInnoDB→ 回滚 MVCC保证原子性、多版本