MySQL表设计与优化

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

一、业务需求

这里，就以学生-教师-课程业务作为示例。数据库需要存放学生、教师、课程相关信息。学生信息包括学号、姓名、性别、专业、年级、班级等；教师信息包括教师编号、姓名、入职时间等；课程表包括课程id、课程名称、课程概述、课时安排等信息。

根据需求，学生可以选修多门课程，具有一对多关系；教师也可以任教多门课程，具有一对多关系。所以，还需要建立对应的中间表。

二、初始构建

通过以上需求分析，可以构建出如下的关系图：

在上图可以看出，表中字段已经满足了各个实体的需求，中间表也体现出了实体之间的对应关系。并且，表设计符合数据库第三范式。

三、表结构优化

1、适度冗余

现在有一个需求，查询姓名为“张三”的学生选修的每门课程的总成绩。在初始构建的表中，需要先通过学生姓名查询出对应的学生id，再查询对应的课程和分数。查询语句如下：

-- 查询姓名为“张三”的学生选修的每门课程的总成绩
select s.name, c.name, c.total_grade 
from (
  select id, name from tb_student where name = '张三'
) s, 
(
  select sc.student_id, c.name, total_grade from tb_course c , tb_student_course sc WHERE c.id = sc.course_id
) c
where s.id = c.student_id;

得到结果：

使用Explain分析，可以看到检索了三张表才得到结果。

在实际场景中，我们经常使用学生姓名而不是学生id来进行查询，所以，可以在学生-课程中间表上添加冗余字段（学生姓名、课程名称）来优化查询，减少join连接查询。虽然冗余字段破坏了第三范式，但是从性能角度和使用场景分析，可以提高整体的效率。以下是优化后的学生-课程表：

这时，查询姓名为“张三”的学生选修的每门课程的总成绩就不需要多表查询了，其查询语句如下：

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