影響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.uj5u.com/shujuku/550277.html
標籤：MySQL
上一篇：day01-Redis入門