5 月 29 日数据库学习笔记

一、数据库设计规范
（一）数据库设计的基本原则
范式设计
第一范式（1NF） ：确保每列的原子性，即每列不可再分。例如，不能将姓名和年龄存储在同一列中。
第二范式（2NF） ：在满足 1NF 的基础上，确保非主属性完全依赖于主键。例如，在学生选课表中，成绩只与选课相关，而不与学生或课程单独相关。
第三范式（3NF） ：在满足 2NF 的基础上，确保非主属性不传递依赖于主键。例如，学生表中的学院名称不应依赖于学院地址，而应直接依赖于学院 ID。
合理命名
表名和字段名应具有明确的业务含义，避免使用缩写或模糊的名称。例如，使用 user_account 而不是 ua。
避免使用保留字和特殊字符，以免引起语法错误或混淆。
数据类型选择
根据数据的特点选择合适的数据类型，以节省存储空间并提高查询效率。例如，使用 INT 存储整数，使用 VARCHAR 存储可变长度的字符串，使用 DATE 存储日期。
索引设计
为经常用于查询条件的列创建索引，以加快查询速度。例如，在用户表的用户名列上创建索引。
避免为低选择性的列创建索引，因为这可能降低插入和更新的效率。
（二）数据库设计的示例
以下是一个简单的数据库设计示例，展示如何遵循范式设计原则：
表 1：学生表（students）
表格

字段名 数据类型 描述
student_id INT 学生 ID（主键）
name VARCHAR 学生姓名
age INT 学生年龄
class_id INT 班级 ID（外键）
表 2：班级表（classes）
表格

字段名 数据类型 描述
class_id INT 班级 ID（主键）
class_name VARCHAR 班级名称
表 3：选课表（enrollments）

字段名 数据类型 描述
enrollment_id INT 选课 ID（主键）
student_id INT 学生 ID（外键）
course_id INT 课程 ID（外键）
grade FLOAT 成绩
二、数据库索引
（一）索引的概念
索引（Index）是数据库中用于提高查询效率的一种数据结构。它类似于书籍的目录，可以快速定位数据的位置，从而减少查询时间。
（二）索引的类型
单列索引
在单个列上创建的索引。例如，在用户表的用户名列上创建索引：
sql

CREATE INDEX idx_username ON users(username);
组合索引
在多个列上创建的索引。例如，在选课表的 student_id 和 course_id 列上创建组合索引：
sql

CREATE INDEX idx_student_course ON enrollments(student_id, course_id);
唯一索引
用于确保索引列的值唯一。例如，在用户表的邮箱列上创建唯一索引：
sql

CREATE UNIQUE INDEX idx_email ON users(email);
全文索引
用于全文搜索，适用于大文本字段。例如，在文章表的内容列上创建全文索引：
sql

CREATE FULLTEXT INDEX idx_content ON articles(content);
（三）索引的优缺点
优点
提高查询速度：通过索引可以快速定位数据，减少磁盘 I/O 操作。
提高排序效率：索引中的数据已经是有序的，可以加快排序操作。
缺点
增加存储开销：索引需要额外的存储空间。
降低插入、更新和删除效率：每次对表进行插入、更新或删除操作时，都需要维护索引，这会增加操作的时间。
（四）索引的使用场景
频繁查询的列
对于经常用于查询条件的列（如用户表的用户名、邮箱等），应创建索引以提高查询速度。
连接条件的列
在多表连接查询中，用于连接条件的列（如外键）应创建索引，以加快连接速度。
排序和分组的列
对于经常用于排序（ORDER BY）或分组（GROUP BY）的列，创建索引可以提高排序和分组的效率。
（五）索引的维护
定期重建索引
随着数据的不断插入和更新，索引可能会变得碎片化，影响查询性能。定期重建索引可以优化索引结构，提高查询效率。
避免过度索引
不要为每个列都创建索引，应根据实际查询需求合理创建索引。过多的索引会增加存储开销和维护成本。
三、数据库优化
（一）查询优化
使用索引
确保查询条件中使用了索引列，以加快查询速度。例如：
sql

SELECT * FROM users WHERE username = 'Alice'; -- 使用索引
SELECT * FROM users WHERE age > 25; -- 如果 age 没有索引，查询速度可能较慢
选择合适的查询结果集
只查询需要的列，避免使用 SELECT *。例如：
sql

SELECT username, email FROM users; -- 更高效
SELECT * FROM users; -- 可能返回过多数据，降低查询效率
使用连接查询替代子查询
在某些情况下，使用连接查询可以替代子查询，提高查询效率。例如：
sql

-- 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 连接查询
SELECT users.* FROM users INNER JOIN orders ON users.id = orders.user_id;
使用分页查询
对于大量数据的查询，使用分页查询可以减少单次查询的数据量，提高查询效率。例如：
sql

SELECT * FROM users LIMIT 10 OFFSET 0; -- 第一页
SELECT * FROM users LIMIT 10 OFFSET 10; -- 第二页
（二）数据库结构优化
表分区
将大表分成多个小表（分区表），可以提高查询和管理效率。例如，可以按日期、范围或列表进行分区。
归档旧数据
将不再频繁使用的旧数据归档到单独的表或数据库中，减少主表的数据量，提高查询效率。
垂直拆分
将表中不常用的列或大文本字段拆分到单独的表中，减少主表的行大小，提高查询效率。
（三）数据库服务器优化
硬件升级
增加内存、使用更快的磁盘（如 SSD）或升级 CPU，可以显著提高数据库性能。
配置优化
调整数据库服务器的配置参数（如缓冲池大小、连接数限制等），以适应实际的工作负载。
定期维护
定期进行数据库备份、日志清理和索引重建等维护操作，保持数据库的良好运行状态。