03-MySQL 精通学习资料面向已经掌握 CRUD、准备深入原理与工程实战的开发者。学完应能读懂执行计划、设计高性能索引、处理并发与锁、搭建主从、做备份与恢复、对慢查询做调优。前置01-入门学习资料.md、02-学习练习.md。1. 存储引擎InnoDB vs MyISAM特性InnoDB默认MyISAM事务✅ 支持❌行级锁✅仅表锁外键✅❌崩溃恢复✅redo log弱全文索引✅5.6✅读写并发高读快写慢结论99% 场景用 InnoDB。MyISAM 仅适合只读、不需事务的日志/报表类表。查看/指定引擎SHOWENGINES;CREATETABLEt(...)ENGINEInnoDB;2. 事务隔离级别与并发问题四种隔离级别MySQL 默认REPEATABLE READ靠 MVCC 实现级别脏读不可重复读幻读READ UNCOMMITTED❌可能可能可能READ COMMITTED✅避免可能可能REPEATABLE READ✅✅避免基本避免间隙锁SERIALIZABLE✅✅✅脏读读到别的事务未提交的数据。不可重复读同一事务内两次读同一行结果不同被别的事务改了。幻读同一查询条件两次查到行数不同别的事务插入/删除了符合条件的行。SELECTtransaction_isolation;-- 查看当前级别SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;MVCC多版本并发控制每行隐藏trx_id与roll_pointer读不加锁靠 undo log 构建历史版本实现「读不阻塞写、写不阻塞读」。3. 锁机制行锁InnoDB 默认锁索引记录。UPDATE ... WHERE id1锁该行。表锁LOCK TABLESMyISAM 常见显式LOCK TABLES t WRITE。间隙锁Gap Lock锁住索引记录之间的「间隙」防止插入解决幻读。Next-Key Lock 行锁 间隙锁是 RR 级别默认算法。死锁两个事务互相等待对方持有的锁。InnoDB 自动检测并回滚代价小的事务。SHOWENGINEINNODBSTATUS;-- 查看最近死锁与锁信息SELECT*FROMperformance_schema.data_locks;-- 8.0 查看当前锁避免死锁经验事务尽量短小尽快提交。多表更新时各事务按固定顺序访问表/行。避免大事务 高并发更新同一批热点行。4. 索引底层B 树InnoDB 索引使用B 树非叶子节点只存键叶子节点存数据聚簇索引或主键二级索引。聚簇索引Clustered主键即数据本身按主键物理有序存放。一张表只有一个。二级索引叶子节点存主键值查到主键后回表再去聚簇索引取整行。覆盖索引查询列都在索引里无需回表性能最好。-- 覆盖索引示例idx_city_age(city, age)查询只需这两列SELECTcity,ageFROMusersWHEREcityBeijing;-- 不必回表联合索引最左前缀(a,b,c)可命中a、(a,b)、(a,b,c)跳a单独查b用不上。索引下推ICP5.6把WHERE条件下推到存储引擎层过滤减少回表。可用EXPLAIN的Using index condition看到。索引失效常见原因对索引列做函数/LIKE %x前模糊/隐式类型转换/列运算。用OR连接非索引列。违反最左前缀。5. EXPLAIN 执行计划EXPLAINSELECT*FROMordersWHEREuser_id1;重点看这几列列含义关注type访问类型system const eq_ref ref range index ALL目标至少到ref/range杜绝ALL全表扫描key实际用到索引NULL 没用索引rows预估扫描行数越大越慢Extra额外信息Using index(覆盖) 好Using filesort/Using temporary需优化慢查询日志定位问题 SQLSETGLOBALslow_query_log1;SETLONG_QUERY_TIME1;-- 超过1秒记录SHOWVARIABLESLIKEslow_query_log_file;6. 查询优化实战6.1 避免 SELECT *只取需要的列利于覆盖索引、减少 IO。6.2 大分页优化LIMIT 100000, 10很慢改为游标/延迟关联SELECT*FROMordersWHEREid100000ORDERBYidLIMIT10;6.3 COUNT 优化COUNT(*)走最小索引大表统计可用估算或缓存。6.4 深分页 JOIN先用索引拿 ID再 JOIN 回原表。6.5 合理使用 EXISTS vs IN外表大用 EXISTS内表大用 IN。7. 分区表把一张大表按规则拆成物理分区查询可「分区裁剪」只扫相关分区。CREATETABLElogs(idINT,created_atDATE)PARTITIONBYRANGE(TO_DAYS(created_at))(PARTITIONp202601VALUESLESS THAN(TO_DAYS(2026-02-01)),PARTITIONp202602VALUESLESS THAN(TO_DAYS(2026-03-01)));类型RANGE / LIST / HASH / KEY。适合日志、历史数据归档。注意分区键必须包含在查询条件与唯一索引中否则失效。8. 视图 / 存储过程 / 触发器 / 事件-- 存储过程DELIMITER$$CREATEPROCEDUREadd_user(INunameVARCHAR(50))BEGININSERTINTOusers(username)VALUES(uname);END$$DELIMITER;CALLadd_user(neo);-- 触发器插入订单后扣库存CREATETRIGGERafter_order_item_insertAFTERINSERTONorder_itemsFOR EACH ROWBEGINUPDATEproductsSETstockstock-NEW.qtyWHEREidNEW.product_id;END;-- 事件定时任务需开启 event_schedulerCREATEEVENT daily_cleanupONSCHEDULE EVERY1DAYDODELETEFROMlogsWHEREcreated_atNOW()-INTERVAL30DAY;生产建议业务逻辑尽量放应用层存储过程/触发器难调试、难版本管理只在性能或一致性强制要求时用。9. 复制Replication主从复制异步原理主库写 binlog从库 IO 线程拉 binlog 写入 relay log从库 SQL 线程重放。# 主库 my.cnf server-id 1 log-bin mysql-bin # 从库 server-id 2-- 从库CHANGE MASTERTOMASTER_HOST主库IP,MASTER_USERrepl,MASTER_PASSWORDxxx,MASTER_LOG_FILEmysql-bin.000001,MASTER_LOG_POS154;STARTSLAVE;SHOWSLAVESTATUS\G-- 看 Slave_IO_Running / Slave_SQL_Running 是否 YesGTID 复制用全局事务 ID 替代文件名位点切换更省心。半同步复制主库等至少一个从库接收才提交提升数据安全。读写分离写走主库读走从库配合 ProxySQL / ShardingSphere。10. 备份与恢复方式工具特点逻辑备份mysqldump生成 SQL慢、锁表可 --single-transaction适合小库/迁移物理备份xtrabackup热备、快、支持增量适合大库增量binlog基于时间点恢复PITR# 逻辑全备mysqldump-uroot-p--single-transaction--routines--triggersshopshop.sql# 恢复mysql-uroot-pshopshop.sql# 基于 binlog 恢复到指定时间点mysqlbinlog --stop-datetime2026-08-04 10:00:00binlog.000123|mysql-uroot-p务必定期演练恢复没恢复过的备份等于没备份。11. 参数调优常见# my.cnf 关键项 innodb_buffer_pool_size 物理内存的 60%~75% # 最关键的缓存越大越好 innodb_log_file_size 1G # redo log写密集可调大 max_connections 500 # 按需 innodb_flush_log_at_trx_commit 1 # 1最高持久性2/0性能优先 sync_binlog 1 # 与上面配合保证不丢数据监控连接与缓冲命中率SHOWSTATUSLIKEThreads_connected;SHOWENGINEINNODBSTATUS;-- Buffer pool hit rate 应接近 100%12. 高并发设计基础分库分表单表超千万行考虑。按用户 ID 哈希 / 按时间 range。工具ShardingSphere、MyCat。读写分离读多写少场景标配。热点行优化如库存扣减用UPDATE ... SET stock stock - 1 WHERE stock 0原子判断避免先查后改的竞态。连接池应用用连接池HikariCP 等避免频繁建连。精通自测能解释 B 树为什么适合数据库索引说清回表、覆盖索引、最左前缀能读懂 EXPLAIN 并定位全表扫描讲出四种隔离级别与幻读解决方案会搭建一主一从并验证同步能设计 mysqldump binlog 的恢复流程知道 innodb_buffer_pool_size 调优方向达标 → 进入04-扩展学习资料.md。
)
