MySQL必看表設計經驗彙總-下(精華版)

⛰️個人主頁:     蒾酒

🔥系列專欄：《mysql經驗總結》

本内容一共分上下兩篇

上：MySQL必看表設計經驗彙總-上(精華版)-CSDN博客

下：MySQL必看表設計經驗彙總-下(精華版)-CSDN博客

目錄

7.定義字段盡可能not null

8.合理添加索引

9.不需要嚴格遵守3NF，通過業務字段冗餘來減少表關聯

11.避免使用MySQL保留字

12.不搞外鍵關聯，一般都在代碼維護

13.字段要注釋

14.時間類型的選擇

附加内容

有關SQL編寫的一些優化經驗

 最近發現了一個巨牛的人工智能學習網站，通俗易懂，風趣幽默，忍不住分享一下給大家。

    點擊跳轉到學習網站

本内容共分上下兩篇建議從上篇開始看，下篇銜接上篇

7.定義字段盡可能not null

如果沒有特殊的理由，一般都建議将字段定義爲NOT NULL。爲什麽呢?

首先，NOT NULL 可以防止出現空指針問題

其次，NULL值存儲也需要額外的空間的，它世會導緻比較運算更爲複雜，使優化器難以優化SQL。

NULL值有可能會導緻索引失效

如果将字段默認設置成一個空字符串或常量值并沒什麽不同，且都不會影響到應用邏輯，那就可以将這個字段設置爲NOT NULL。

8.合理添加索引

當設計表時，需要考慮哪些字段需要加索引，可以遵循以下幾個原則：

1.根據查詢條件進行選擇(高頻使用)：:如果在查詢中使用了某個字段作爲查詢條件，那麽這個字段就應該建立索引。例如，在用戶表中，如果需要根據用戶的姓名進行查詢，那麽就應該爲姓名字段建立索引。

2.區分度高的字段優先：如果一字段的取值範圍非常小，例如性别隻有男女兩種可能，那麽這個字段就不适合建立索引。相反，如果一個字段的取值範圍很大且區分度高，例如用戶ID，那麽這個字段就非常适合建立索引。

3.不要建立過多的索引：每個表所建立的索引數量應該控制在一個合理的範圍内，一般不要超過5個。因爲過多的索引會導緻寫入速度變慢，并占用更多的存儲空間。

4.聯合索引優化：在某些情況下，可以通過聯合索引的方式來優化查詢速度，減少所需的索引數量。例如，在用戶表中，如果需要根據用戶姓名和年齡進行查詢，那麽可以将這兩個字段組合成聯合索引。

假設你有一個訂單表，包含訂單ID、用戶ID、訂單金額、訂單狀态等字段。現在需要根據用戶ID和訂單狀态進行查詢，可以考慮爲用戶ID和訂單狀态這兩個字段建立聯合索引。

9.不需要嚴格遵守3NF，通過業務字段冗餘來減少表關聯

簡單來說就是反範式設計常見形式是在第三範式(3NF)的基礎上進一步進行冗餘，從而減少表關聯

數據庫三範式(3NF)：

• 第一範式：對屬性的原子性，要求屬性具有原子性，不可再分解
• 第二範式:：記錄的唯一性，要求記錄有唯一标識，即實體的唯一性，即不存在部分依賴
• 第三範式：對字段的冗餘性，要求任何字段不能由其他字段派生出來，它要求字段沒有冗餘，即不存在傳遞依賴

  假設需要設計個産品訂單表，包含以下字段: 訂單ID、用戶ID、訂單日期、産品名稱、産品價格品數量以及訂單總價。正常情況下，可能會分别設計訂單表和産品表，并使用外鍵進行關聯，例如:

  Python

  create table orders
  (
      id         int(11) not null auto_increment,
      user_id    int(11) not null, -- 用戶ID
      order_date date    not null, -- 訂單日期
      product_id int(11) not null, -- 産品ID
      quantity   int(11) not null, -- 數量
      primary key (id),
      foreign key (product_id) references product (id)
  );
  create table product
  (
      id    int(11)        not null auto_increment,
      name  varchar(256)   not null, -- 産品名稱
      price decimal(10, 2) not null, -- 價格
      primary key (id)
  );

  這種設計在使用時會比較麻煩，你要先查訂單表再查産品表通過數量跟單價才可以計算出總價。

  這個設計方式符合範式要求，但在查詢時需要進行表關聯操作，可能會降低查詢效率。爲了提高查詢效率，我們可以使用反範式的設計方式，将訂單表中的産品名稱、産品價格和訂單總價冗餘存儲到訂單表中，從而避免關聯查詢。例如:

  Python

  create table orders
  (
      id            int(11)        not null auto_increment, -- 訂單ID，自增類型
      user_id       int(11)        not null,                -- 用戶ID
      order_date    date           not null,                -- 下單日期
      product_name  varchar(256)   not null,                -- 産品名稱
      product_price decimal(10, 2) not null,                -- 産品單價，保留兩位小數
      quantity      int(11)        not null,                -- 購買數量
      total_price   decimal(10, 2) not null,                -- 訂單總價，保留兩位小數
      primary key (id)
  );

  通過這種反範式的設計方式，我們可以避免表關聯操作，提高查詢效率，但也帶來了一些缺點，如數據冗餘，數據更新困難等，所以在實際應用中根據情況做取舍。

  11.避免使用MySQL保留字

  如果庫名、表名、字段名等屬性含有保留字時，SQL語句必須用反引号（`）來引用屬性名稱，這将使得SQL語句書寫、shell腳本中變量的轉義等變得非常複雜

  如果你需要使用這些保留字作爲表名、列名或其他标識符，你可以考慮以下方法來避免沖突:

  在标識符前或後添加下劃線:例如，将表名命名爲“my_table"，列名命名爲“column_name

  使用不同的單詞或短語:例如，将表名命名爲"orders_table"，列名命名爲"order_status"

  使用反引号（`）将标識符括起來:例如，将表名命名爲“table"，列名命名爲"column""。請注意在使用反引号時要小心，确保使用正确的語法和規範

  MySQL常見保留字：

  SELECT、INSERT、UPDATE、DELETE、CREATE、ALTER、DROP、FROM、WHERE、AND、OR、NOT、ORDER BY、GROUP BY

  12.不搞外鍵關聯，一般都在代碼維護

  在數據庫設計中，使用外鍵關聯是一種良好的實踐，可以确保數據的完整性和一緻性。外鍵關聯可以幫助維護表之間的關系，防止無效或不一緻的數據插入、更新或删除操作。然而，在某些情況下，也存在些缺點，這可能是導緻現在不太推薦使用外鍵關聯的原因之一。以下是一些這種情況:：

  • 可能會導緻性能問題，尤其是在對大型數據集進行操作時。這是因爲每次插入、更新或删除操作都需要進行約束檢查，這可能會導緻額外的開銷和延遲。
  • 可能會限制數據庫的靈活性和可擴展性。例如，如果需要對數據庫進行分區或垂直分割，外鍵關聯可能會導緻額外的複雜性和限制。
  • 可能會導緻死鎖和死循環，特别是在進行并發操作時。這可能會導緻數據庫出現不穩定的狀态，從而影響系統的性能和可用性。
  • 可能會導緻數據庫的維護和管理成本的增加。這是因爲外鍵關聯需要額外的管理和維護工作，例如添加、修改或删除外鍵約束時需要額外的測試和驗證。

    因此，在決定是否使用外鍵關聯時，需要考慮實際業務需求和場景，并進行權衡和決策。在某些情況下，可以采用其他方法來保證數據的完整性和一緻性，例如使用應用程序邏輯或數據庫觸發器來實現約束檢查和數據操作。同時，需要注意數據庫設計的基本原則和最佳實踐，例如避免數據幾餘、遵循規範化原則和正常化理論等。

    13.字段要注釋

    設計表時每個字段的含義要注釋清楚，包括枚舉類型。比如說

    Python

     'order_status' varchar(2) not null comment '訂單狀态 01:待支付，02:已支付，03:已發貨，04:已完成，05:已取消'

    14.時間類型的選擇

    時間類型的選擇一般都要好好考慮，因爲不同的類型存儲的格式不同。

    對于MySQL來說，主要有datedatetime、time、timestamp 和 year。

    • date: 表示的日期值格式yyyy-mm-dd,範圍1000-01-01 到 9999-12-31，3字節
    • time:表示的時間值，格式 hh:mm:ss，範圍-838:59:59 到838:59:59，3字節
    • datetime: 表示的日期時間值，格式yyyy-mm-dd hh:mm:ss，範圍1000-01-01 00:00:00到9999-12-31 23:59:59，8字節，跟時區無關
    • timestamp: 表示的時間截值，格式爲yyyymmddhhmmss，範圍1970-01-01 00:00:01到2038-01-19 03:14:07，4字節，跟時區有關
    • year: 年份值，格式爲yyyy。範圍1901到2155，1字節

      推薦優先使用datetime類型來保存日期和時間，因爲存儲範圍更大，且跟時區無關

      附加内容

      有關SQL編寫的一些優化經驗

      1. 避免使用SELECT*FROM 語句，應該隻選擇需要的列，以減少網絡傳輸和提高查詢性能。
      2. 使用索引來提高查詢速度，特别是在對大型表進行查詢時
      3. 避免使用外鍵約束，因爲它們可能會導緻性能問題，特别是在對大型表進行插入、更新和删除操作時。
      4. 使用LIMIT1來限制查詢結果隻有一條記錄。
      5. 避免在where子句中使用OR來連接條件，應使用UNION來連接查詢。
      6. 注意優化LIMIT深分頁問題，可以使用OFFSET來替代LIMIT.。
      7. 使用where條件限制要查詢的數據，避免返回多餘的行。
      8. 盡量避免在索引列上使用MySQL的内置函數，這可能導緻索引失效。
      9. 應盡量避免在where子句中對字段進行表達式操作，這可能導緻索引失效。
      10. 應盡量避免在where子句中使用!=或操作符，這可能導緻索引失效。
      11. 使用聯合索引時，注意索引列的順序，一般遵循最左匹配原則。
      12. 對查詢進行優化，應考慮在where及order by涉及的列上建立索引。
      13. 如果插入數據過多，考慮批量插入。
      14. 在适當的時候，使用覆蓋索引(查詢列都是索引列)，避免了回表。
      15. 使用EXPLAIN 分析你SQL的計劃。主要用來看sql走沒走期望的索引