本文共 2319 字，大约阅读时间需要 7 分钟。
MySQL数据库基础知识
数据库的三大范式
数据库的设计需要遵循三大范式，以保证数据的一致性和完整性。以下是三大范式的详细解释：
第一范式（1NF）
确保数据库表字段的原子性。字段内容必须是单一的、不可再分割的值。例如，字段 userInfo: 广东省 10086 必须拆分为两个字段：userInfo: 广东省 和 userTel: 10086。 第二范式（2NF）
首先满足第一范式，另外包含两部分内容： - 表必须有一个主键。
- 非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。例如，选课关系表
student_course 的主键为 (student_no, course_name)，如果学生课程中 student_no 的学分完全依赖于 course_name，则需要拆分为三个表：学生表、课程表和选课关系表。
第三范式（3NF）
首先满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。例如，学生关系表中的学院信息如果依赖于学生的学院 ID，而学院的地点和电话也依赖于学院 ID，则需要拆分为两个表：学生表和学院表。 2NF 和 3NF 的区别
- 2NF 的依据是非主键列是否完全依赖于主键。
- 3NF 的依据是非主键列是否直接依赖于主键，不能存在传递依赖。
事务的四大特性（ACID）
事务特性包括：
原子性（Atomicity）：所有操作要么全部成功，要么全部失败回滚。 一致性（Consistency）：事务执行前后数据库处于一致性状态。 隔离性（Isolation）：一个事务只能读到已经提交的修改。 持久性（Durability）：一旦提交，数据改变是永久的，即使在数据库故障时也不会丢失。 事务隔离级别
MySQL 提供四种隔离级别：
脏读（Dirty Read）：未提交事务的数据可以被其他事务读取。 不可重复读（Repeatable Read）：同一事务范围内多次查询会返回相同数据。 幻读（Phantom Read）：查询范围内的记录在事务期间发生了变化。 串行化（Serializable）：确保事务不会相互冲突，避免幻读。 默认隔离级别为 Repeatable Read。
代码示例
-- 查看当前隔离级别SELECT @@transaction_isolation;-- 设置隔离级别为读已提交SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
生产环境数据库的隔离级别选择
在生产环境中，通常使用 RC（Read Committed）作为默认隔离级别。
- RR（Repeatable Read） 随着并发性较高，容易导致死锁和索引冲突。
- RC 在并发性和安全性之间找到平衡，适合大多数场景。
编码与字符集
数据库中的数据存储和传输都是以二进制形式进行的。为了处理文本数据，需要使用编码机制。
- ASCII：使用 1 个字节表示符号，支持 128 个基础符号和 128 个扩展符号。
- GB2312：用于中文编码，支持更多字符。
- UTF-8：基于 Unicode 编码，支持多语言，字符表示方式更加灵活。
- UTF-8mb4：是 UTF-8 的扩展版本，支持更多字符，适合现代应用需求。
索引的作用与设计
索引的作用
索引通过存储引擎（如 B+ 树）实现，用于快速定位数据。 索引的优缺点
- 优点：提高查询效率，降低磁盘 I/O 消耗。
- 缺点：占用物理空间，影响增删改操作性能。
索引的分类
- 主键索引：唯一且非空。
- 唯一索引：字段值唯一，但可为空。
- 组合索引：多个字段组合。
- 全文索引：适用于
CHAR、VARCHAR 和 TEXT 类型字段。 - 普通索引：字段可以为空。
索引的设计原则
- 对经常查询的字段建立索引。
- 避免对区分度低的字段建立索引。
- 使用短索引，减少磁盘读取时间。
存储引擎
MySQL 提供四种存储引擎：
InnoDB：默认事务存储引擎，支持行级锁和外键。 MyISAM：适用于只读或小数据量的场景。 MEMORY：数据存储在内存中，速度快但易失数据。 ARCHIVE：适合存储大量历史数据，性能较低。 MyISAM 和 InnoDB 的区别
- 存储结构：MyISAM 存储在多个文件中，而 InnoDB 存储在单个文件中。
- 事务支持：InnoDB 支持事务，MyISAM 不支持。
- 锁机制：InnoDB 支持行级锁，MyISAM 只支持表级锁。
- 恢复能力：InnoDB 支持崩溃恢复，MyISAM 不支持。
锁机制
- 按粒度分类：行级锁、表级锁、页级锁。
- 按级别分类：共享锁、排他锁、意向锁。
- 乐观锁：通过版本号机制控制并发。
- 悲观锁：通过加锁机制控制并发。
MVCC 实现原理
MVCC（多版本并发控制）通过版本链和快照读实现并发控制。
- 版本链：记录数据修改的历史版本。
- 快照读：读取数据的快照版本，避免幻读。
- 读写分离：提升并发性能。
分库分表优化
- 垂直划分：根据业务功能拆分数据库。
- 水平划分：根据数据分布规则拆分表。
- 分区表：通过分区实现物理存储和逻辑存储的结合。
查询优化
- 查询执行流程：权限校验、查询缓存、分析器、优化器、执行器。
- 分页优化：通过分页技术提高性能。
- 大表查询优化：通过索引和分区技术降低查询压力。
其他 MySQL 功能
- 主从同步：支持数据复制和高并发操作。
- 触发器：自动执行数据库事件。
- 存储过程和函数：扩展数据库功能。
- 二进制日志：记录数据库操作，支持数据恢复和复制。
以上是对 MySQL数据库基础知识的系统总结，涵盖了从基础到高级功能的内容。
转载地址：http://fedfk.baihongyu.com/