翼度科技»论坛 编程开发 mysql 查看内容

MySQL表设计与优化

5

主题

5

帖子

15

积分

新手上路

Rank: 1

积分
15
影响MySQL查询性能的因素有很多,我们经常会对查询语句、索引字段做一些优化,而其实在表设计的阶段就可能产生一些问题。对于表设计,可以对表结构进行优化,也可以对表字段进行优化。以下通过一个具体的案例演示一些常用的表设计优化的方法。
一、业务需求

这里,就以学生-教师-课程业务作为示例。数据库需要存放学生、教师、课程相关信息。学生信息包括学号、姓名、性别、专业、年级、班级等;教师信息包括教师编号、姓名、入职时间等;课程表包括课程id、课程名称、课程概述、课时安排等信息。
根据需求,学生可以选修多门课程,具有一对多关系;教师也可以任教多门课程,具有一对多关系。所以,还需要建立对应的中间表。
二、初始构建

通过以上需求分析,可以构建出如下的关系图:
在上图可以看出,表中字段已经满足了各个实体的需求,中间表也体现出了实体之间的对应关系。并且,表设计符合数据库第三范式
三、表结构优化

1、适度冗余

现在有一个需求,查询姓名为“张三”的学生选修的每门课程的总成绩。在初始构建的表中,需要先通过学生姓名查询出对应的学生id,再查询对应的课程和分数。查询语句如下:
  1. -- 查询姓名为“张三”的学生选修的每门课程的总成绩
  2. select s.name, c.name, c.total_grade
  3. from (
  4.   select id, name from tb_student where name = '张三'
  5. ) s,
  6. (
  7.   select sc.student_id, c.name, total_grade from tb_course c , tb_student_course sc WHERE c.id = sc.course_id
  8. ) c
  9. where s.id = c.student_id;
复制代码
得到结果:
使用Explain分析,可以看到检索了三张表才得到结果。
在实际场景中,我们经常使用学生姓名而不是学生id来进行查询,所以,可以在学生-课程中间表上添加冗余字段(学生姓名、课程名称)来优化查询,减少join连接查询。虽然冗余字段破坏了第三范式,但是从性能角度和使用场景分析,可以提高整体的效率。以下是优化后的学生-课程表:
这时,查询姓名为“张三”的学生选修的每门课程的总成绩就不需要多表查询了,其查询语句如下:
  1. select student_name, course_name, total_grade from tb_student_course where student_name = '张三';
复制代码
2、大字段、不常用字段拆分

在课程表中,有两个较大的字段,分别为课程概述和课时计划,详细地介绍了课程的一些相关信息。在实际场景中,我们更经常查询课程教室、课程时间等信息。但是,当我们查询课程教室和课程时间字段的时候,数据库并不是只读取我们需要的字段,而是读取整条记录的字段,包括了课程概述和课时计划两个大字段。由于大字段所占的空间比例很大,所以会造成较大的资源浪费。
所以,我们可以将这两个不常用的大字段进行拆分,来提高查询性能。优化后的关系图如下:
四、字段优化

一般来说,字段类型要在符号需求的情况下选择尽量小的类型。
1、数字类型

在学生-课程表中,包含了平时成绩、期末成绩、总成绩的字段,使用了double类型,保留两位小数。但对于成绩字段来说,其实并不需要这么大的字段,可以使用int类型来存放。对于保留两位小数,可以通过乘以100的固定系数转换为整数来存放。
2、时间类型

在设计时间类型时,要根据业务需求选用合适的时间类型。如果只需要记录年份,使用year类型;如果只需要记录日期YYYY-MM-DD,不需要具体时间,可以使用date类型;如果只需要具体时间hh:mm:ss,不需要日期,可以使用time类型。使用timestamp时,需要注意它的范围大小是否能满足需求。
3、字符类型

对于固定长度的字段,可以使用char类型;对于可变长度字段,可以使用varchar类型。varchar用于存储可变长度字符串,它比char类型更加节省空间,但是varchar需要使用1个或2个额外字节记录字符串的长度。
例如,性别字段,只需要用‘M’和‘F’来表示男、女,这时,可以使用char(1)。或者,可以使用tinyint(1)存放,用0表示男、1表示女。
对于身份证号,因为其是固定长度为18位,所以,可以采用char类型。
对于课程名称、详情等字段,它们的长度是不固定的,可以采用varchar类型。
所以,最终优化后的学生-课程-教室关系图如下:
五、总结

以上通过一个具体案例解释了数据库的表设计与优化方法,包括表结构优化(如适度冗余、字段拆分),字段优化。
如果文中有不完善的地方,欢迎大家讨论交流!

来源:https://www.cnblogs.com/xwangkk/p/17323201.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具