MySQL基础
MySQL 基础核心
（1）核心定义与优势
MySQL 是开源免费的关系型数据库，基于关系模型存储数据，默认端口 3306，默认存储引擎为 InnoDB。核心优势包括成熟稳定、开源免费、文档丰富、操作简单、兼容性好、支持事务与高可用方案（分库分表、读写分离），广泛用于各类系统的数据持久化存储。
（2）核心字段类型
MySQL 字段类型分为三大类，重点关注高频类型及选型：
- 数值类型：整型（TINYINT/SMALLINT/INT/BIGINT）、浮点型（FLOAT/DOUBLE）、定点型（DECIMAL）。
UNSIGNED属性可让正整数上限翻倍（如 TINYINT UNSIGNED 取值 0-255），DECIMAL 适用于货币等高精度场景，避免浮点精度损失。 - 字符串类型：CHAR（定长，适合短字符串 / 长度固定场景，如身份证号）、VARCHAR（变长，适合长度不固定场景，如昵称）。两者 M 均表示字符数，存储相同字符串时磁盘占用一致，但 VARCHAR (100) 比 VARCHAR (10) 消耗更多内存。
- 日期时间类型：DATETIME（8 字节，无时区，范围 1000-9999 年）、TIMESTAMP（4 字节，含时区，范围 1970-2038 年）。
- 避坑选型：手机号用 VARCHAR（支持格式符、加密存储），不用 INT/BIGINT；避免使用 TEXT/BLOB（无默认值、检索低效、无法直接建索引）；布尔值用 TINYINT (1) 表示（0 为 false，1 为 true）。
（3）NULL 与空字符串（''）区别
- NULL 表示缺失 / 未知数据，不等于任何值（包括自身），需用
IS NULL判断，聚合函数忽略 NULL； - '' 表示已知的空字符串，可直接比较（'' = '' 为 true），聚合函数会计入统计；
- 建议避免用 NULL 作为列默认值，易导致查询复杂和性能问题。
MySQL 架构与存储引擎
（1）基础架构
MySQL 分为 Server 层和存储引擎层，Server 层包含连接器（身份认证、权限验证）、查询缓存（MySQL 8.0 移除）、分析器（语法解析）、优化器（生成执行计划）、执行器（执行 SQL 并调用存储引擎）；存储引擎层采用插件式架构，负责数据存储与读取。
（2）核心存储引擎对比（InnoDB vs MyISAM）
| 对比维度 | InnoDB（默认） | MyISAM |
|---|---|---|
| 事务支持 | 支持 ACID 事务，默认隔离级别 REPEATABLE-READ | 不支持事务 |
| 锁机制 | 支持行级锁 + 表级锁（默认行级锁） | 仅支持表级锁 |
| 外键支持 | 支持 | 不支持 |
| 崩溃恢复 | 支持（依赖 redo log） | 不支持 |
| MVCC 支持 | 支持（提升并发性能） | 不支持 |
| 索引实现 | 聚簇索引（数据文件即索引文件） | 非聚簇索引（索引与数据分离） |
| 适用场景 | 高并发、需事务、数据一致性要求高场景 | 读密集、无事务需求的简单场景 |
（3）存储引擎选型
优先使用 InnoDB，几乎适配所有业务场景；MyISAM 因不支持事务和行级锁，仅适用于只读、无并发写的特殊场景。
MySQL 索引核心
（1）索引本质与类型
索引是优化查询效率的数据结构（主流为 B + 树），核心作用是加速数据检索。常见类型包括普通索引、唯一索引、主键索引、复合索引、前缀索引，InnoDB 中主键索引为聚簇索引，数据存储在索引叶节点。
（2）关键索引规则
- 复合索引遵循 “最左匹配原则”，需按索引列顺序查询；
- 索引失效场景：索引列参与函数操作、隐式类型转换、使用
!=/NOT IN/ 左模糊查询（% xxx）； - 避免过度索引，索引会降低增删改效率。
MySQL 事务与隔离级别
（1）事务 ACID 特性
- 原子性（Atomicity）：操作要么全执行，要么全回滚；
- 一致性（Consistency）：事务前后数据完整性一致（如转账总额不变）；
- 隔离性（Isolation）：并发事务互不干扰；
- 持久性（Durability）：事务提交后数据修改永久有效。
（2）并发事务问题
- 脏读：读取未提交的修改数据；
- 不可重复读：同一事务内多次读同一数据，结果不一致（因其他事务修改）；
- 幻读：同一事务内多次查询，结果集行数不一致（因其他事务新增 / 删除）。
（3）隔离级别（SQL 标准 + InnoDB 实现）
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
| READ-UNCOMMITTED | ✅ | ✅ | ✅ | 最低级别，几乎不用 |
| READ-COMMITTED | ❌ | ✅ | ✅ | 避免脏读，Oracle 默认 |
| REPEATABLE-READ | ❌ | ❌ | ≈❌ | MySQL 默认，InnoDB 通过 MVCC+Next-Key Lock 解决幻读 |
| SERIALIZABLE | ❌ | ❌ | ❌ | 最高级别，串行执行，并发低 |
MySQL 锁机制
（1）锁粒度
- 表级锁：锁定整张表，实现简单、开销小，MyISAM 仅支持，并发差；
- 行级锁：锁定单行 / 多行，粒度细、并发高，InnoDB 支持，需基于索引加锁（无索引则升级为表锁）。
（2）锁类型
- 共享锁（S 锁 / 读锁）：允许多事务同时获取，支持读读并行；
- 排他锁（X 锁 / 写锁）：禁止其他事务获取任何锁，支持写写 / 写读互斥；
- 意向锁（IS/IX 锁）：表级锁，快速判断表中是否有行锁，避免全表遍历；
- 行锁细分：记录锁（锁定单条记录）、间隙锁（锁定范围，不含记录）、临键锁（Record+Gap，InnoDB 默认，解决幻读）。
（3）当前读与快照读
- 快照读：普通 SELECT，基于 MVCC 读取历史版本，不加锁，性能高；
- 当前读：SELECT ... FOR UPDATE/LOCK IN SHARE MODE、INSERT/UPDATE/DELETE，加锁读取最新版本，保证数据一致性。
MySQL 性能优化核心
（1）慢 SQL 定位与分析
- 开启慢查询日志（
slow_query_log=ON），记录执行超时（默认 1 秒）的 SQL； - 用
EXPLAIN分析执行计划，重点关注type（访问方式，如 ALL 为全表扫描）、key（实际使用的索引）、rows（预计扫描行数）。
（2）核心优化手段
- 索引优化：创建高频查询 / 过滤性强的索引，优先复合索引，避免索引失效；
- SQL 优化：避免
SELECT *、用 JOIN 代替子查询、批量操作代替单条执行、分页用WHERE id > xxx LIMIT 10优化深度分页； - 表结构优化：选择合适字段类型、避免冗余、合理使用范式与反范式；
- 架构优化：读写分离（读多写少场景）、分库分表（单表数据量大）、数据冷热分离（热数据存高性能介质）、缓存热点数据（如 Redis）。
（3）其他优化
- IP 存储：用
INET_ATON()转为整型存储，INET_NTOA()还原，节省空间且提升性能； - 避免外键与级联：阿里开发手册强制禁止，建议在应用层维护数据一致性，避免锁冲突与更新风暴；
- 文件存储：不直接存储图片 / 音视频，数据库仅存文件地址，文件由对象存储服务（如 OSS/MinIO）管理。
高级特性
（1）查询缓存
MySQL 8.0 已移除查询缓存（ scalability 差、易成为瓶颈），5.6+ 默认禁用，无需依赖。
（2）自增锁
InnoDB 自增列依赖自增锁，innodb_autoinc_lock_mode=2（MySQL 8.0 默认）为交错模式，用轻量级互斥锁实现，支持并发插入，需注意主从同步时 Binlog 格式为 Statement 可能导致数据不一致。
（3）读写分离与分库分表
- 读写分离：写操作走主库，读操作走从库，提升并发处理能力；
- 分库分表：水平分表（按数据范围 / 哈希拆分）、垂直分表（按字段拆分），降低单表数据量，提升查询效率，需权衡维护复杂性。
