《信创数据库运维管理》课件-项目三：数据库结构设计

信创数据库运维管理3.概念结构设计YOURLOGO什么是概念结构设计概念结构设计：收集和分析用户需求识别出需要存储的数据明确数据之间的关系用标准化的方式表达出来展示"房间"(实体)以及"房间"之间如何连接什么是概念结构设计面向用户使用用户熟悉的语言和概念易于理解图形化表示，直观清晰独立于实现不涉及具体数据库技术细节便于修改在早期阶段发现问题成本最低特点概念结构设计的步骤步骤1：需求分析通过沟通明确系统需要管理哪些数据学生管理系统要管理学生、课程、教师等信息每个学生有学号、姓名、专业等属性学生和课程之间存在选课关系概念结构设计的方法步骤2：标识实体实体：通常对应现实世界人：学生、教师物：图书、设备事件：订单、考试地点：教室、实验室概念结构设计的步骤步骤3：确定实体属性为每个实体定义详细的属性特征学号(主键)、姓名、性别、出生日期等课程编号(主键)、课程名称、学分、课时等注意：属性是不可再分的数据项，避免复合步骤4：识别实体间关系一对一(1:1)、一对多(1:n)、多对多(m:n)使用动词短语关系本身也可以有属性概念结构设计的步骤绘制E-R图矩形表示实体椭圆形表示属性菱形表示关系实体-关系图可视化概念模型连线表示关联概念结构设计的方法1.自顶向下法先定义高层次的概念逐步细化整体性强，不易遗漏初期需要全面了解需求需求明确的大型系统2.自底向上法局部功能出发，先设计各部分，再整合可以分模块并行设计后期整合可能发现不一致需求不明确或迭代开发概念结构设计的方法概念结构设计自底向上设计细节自顶向下规划框架3.混合策略概念结构设计的方法设计学校管理系统时先确定大模块(自顶向下)详细设计每个模块中的实体和关系概念结构设计的注意事项避免常见错误实体划分不当关系类型判断错误忽略重要约束条件属性分配不合理概念结构设计的方法2.验证设计质量是否涵盖了所有用户需求？是否存在数据冗余？能否支持所有必要的查询？是否易于扩展新功能？设计小技巧使用名词命名实体，动词命名关系主键属性加下划线标注多对多关系通常需要转换为关联实体定期与用户确认设计是否符合预期总结本节学习内容主要步骤包括需求分析、标识实体、确定属性、识别关系和绘制E-R图概念结构设计是将用户需求转化为信息模型的关键步骤常用设计方法有自顶向下、自底向上和混合策略需要注意避免常见错误，并通过验证确保设计质量概念结构设计是数据库开发的基石，好的概念设计可以大大减少后续开发中的问题。信创数据库运维管理2.逻辑结构设计YOURLOGO认识逻辑结构设计什么是逻辑结构设计逻辑结构设计E-R图设计图纸数据库表结构施工工程图主键很重要一个实体一张表实体转换的要点两个原则每个表都需要一个主键，用来唯一标识每一条记录。选择实体的标识符作为主键。每个实体都要转换成一张独立的表。实体转换的要点学生表的SQL创建语句CREATETABLEStudent学号CHAR(10)PRIMARYKEY姓名VARCHAR(20)NOTNULL班级编号CHAR(6)性别CHAR(1)学号设为主键不能为空一个字符存储关联班级表关系转换的方法1对多关系的处理添加班级编号作为外键通过编号能知道学生属于哪个班级关系转换的方法多对多关系的处理选课：需要创建一个中间表。学生学号和课程编号作为主键对应的SQL语句记录学生选哪门课以及获得的成绩教务管理系统的完整设计

主要表结构设计出来教务管理系统院系表班级表教师表授课表完整地记录教务管理系统中所有的数据总结与思考本节学习内容实体之间的关系要通过外键或中间表来表示每个实体对应一张表，实体的属性就是表的列设计时要考虑数据的完整性和查询效率总结与思考多次修改和完善数据库设计设计出结构合理、易于维护的数据库优化表结构如果学校有上万人，需要做那些调整信创数据库运维管理3.物理结构设计YOURLOGO什么是物理结构设计画施工图决定用什么材料、怎么施工逻辑设计物理设计物理结构设计根据逻辑模型和具体DBMS特性确定数据库的物理存储结构和存取方法的过程什么是物理结构设计物理设计的主要内容确定存储结构：文件组织方式、记录格式设计存取路径：索引的建立分配存储空间：表空间、磁盘规划设置系统参数：缓冲区大小、并发控制教务管理系统物理设计1.确定存储结构表名存储结构选择理由学生表(Student)堆文件组织学生数据频繁插入且随机查询课程表(Course)采用哈希文件组织经常通过课程编号精确查询选课表(SC)B+树文件组织需要支持范围查询（如查询某学期成绩）教务管理系统物理设计学生表主键索引（自动创建）CREATETABLEStudent(学号CHAR(10)PRIMARYKEY,自动创建聚集索引...);为常用查询条件创建索引CREATEINDEXidx_student_classONStudent(班级编号);选课表成绩范围查询索引CREATEINDEXidx_sc_scoreONSC(成绩);2.设计索引策略教师姓名查询索引CREATEINDEXidx_teacher_nameONTeacher(姓名);教务管理系统物理设计索引设计原则为主键和外键自动创建索引为频繁查询的列创建索引为范围查询的列创建索引避免为频繁更新的列创建过多索引教务管理系统物理设计3.分配存储空间MySQL分区表示例CREATETABLESC(学号CHAR(10),课程编号CHAR(8),成绩DECIMAL(5,2),学期CHAR(10),PRIMARYKEY(学号,课程编号,学期))PARTITIONBYRANGECOLUMNS(学期)(PARTITIONp2020VALUESLESSTHAN('2021-01'),PARTITIONp2021VALUESLESSTHAN('2022-01'),PARTITIONpmaxVALUESLESSTHANMAXVALUE);学生数据预计每年新增3000条，每条约200字节，5年需要约3MB。选课记录每个学生平均选30门课，5年约450,000条，每条约50字节，需要约22MB。课程数据约500门课程，每条约100字节，需要约50KB。教务管理系统物理设计对于大型表考虑分区存储4.设计分区策略选课表按学期分区学生表按入学年份分区MySQL分区表示例CREATETABLESC(学号CHAR(10),课程编号CHAR(8),成绩DECIMAL(5,2),学期CHAR(10),PRIMARYKEY(学号,课程编号,学期))PARTITIONBYRANGECOLUMNS(学期)(PARTITIONp2020VALUESLESSTHAN('2021-01'),PARTITIONp2021VALUESLESSTHAN('2022-01'),PARTITIONpmaxVALUESLESSTHANMAXVALUE);教务管理系统物理设计5.设置系统参数根据系统负载调整数据库参数缓冲区大小：为频繁访问的学生表分配更多并发控制：选课高峰期提高最大连接数MySQL参数设置示例

SETGLOBALinnodb_buffer_pool_size=2G;SETGLOBALmax_connections=500;SETGLOBALinnodb_log_file_size=256M;日志设置：确保关键数据的恢复能力物理设计优化技巧数据存储优化对大文本字段单独存储使用适当的数据类型考虑数据压缩存储物理设计优化技巧查询性能优化为多表连接查询创建复合索引使用覆盖索引避免回表操作对统计查询考虑物化视图物理设计优化技巧系统维护考虑设计定期归档策略规划备份恢复方案设置监控报警机制教务管理系统物理设计方案存储结构索引设计存储规划系统参数学生表、教师表：堆文件组织课程表：哈希组织选课表：B+树组织学生表：班级编号索引选课表：复合索引教师表：姓名索引当前数据表空间：50GB索引表空间：20GB临时表空间：10GB日志空间：10GB缓冲池：8GB最大连接数：300查询缓存：1GB总结与思考数据库物理结构设计的核心内容主要工作包括存储结构选择、索引设计、空