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類型。

所以，最終優化後的學生-課程-教室關係圖如下：

五、總結

以上通過一個具體案例解釋了資料庫的表設計與優化方法，包括表結構優化（如適度冗餘、欄位拆分），欄位優化。

如果文中有不完善的地方，歡迎大家討論交流！