MySQL 資料庫設計和注意事項

Tim-AutumnWind發表於2020-09-30
MySql資料庫

以下是個人見解，寫錯了，或者我本人理解錯誤，敬請諒解
如果採用本文章資訊自建 MySQL 出了問題，請不要來找我
如果有更好的方案，或者建議，歡迎提交版本
TimAutumnWind （轉載請註明出處 https://learnku.com/articles/50270）

1.請使用 InnoDB 儲存引擎

InnoDB 有更好的 CPU 和 IO 效能，更好的備份和鎖表機制，提高統計和除錯效率。
另外，作為一 個系統，InnoDB 支援多種關鍵功能，其中最重要的是事務日誌和行級鎖。事務日誌記錄真正的資料庫事務，但更重要的是資料崩潰恢復和回滾。
基於 InooDB 方式的 IO，能給予更安全資料保護和更好效能表現。另外，在大多數的情況下，行級鎖可以提供更高的併發效能，因為使用者只鎖定他們正在寫的資料，而讀資料永遠不會被阻塞

2.資料表、資料欄位必須加入中文註釋

方便日後新人小哥，更快理解熟悉；並且可讀性更好
同時在 status 這類欄位上標註：0 表示刪除，1 表示正常 等列舉值。

3.必須使用 UTF8mb4 字符集

utf8 是通用的字符集，mb4 在 utf8 上進行了擴充套件，支援 emoji 等新的字元。

4.禁止使用儲存過程、檢視、觸發器、Event、join等

高併發大資料的網際網路業務，架構設計思路是“解放資料庫 CPU，將計算轉移到服務層”，資料庫擅長儲存與索引，CPU 計算在業務層更合理。
如果是低併發，小流量，當我沒說

5.禁止儲存大檔案或者大照片

當圖片較多時，分頁查詢速度明顯變慢，之前1秒內響應，加了照片欄位後，需要4~5秒左右才能響應。大檔案和照片儲存在檔案系統，資料庫裡存 URI 更好

6.表必須有主鍵，例如自增主鍵

  1. 主鍵遞增，資料行寫入可以提高插入效能，可以避免 Page 分裂，減少表碎片提升空間和記憶體的使用
  2. 使用數字型別主鍵，較短的資料型別可以有效的減少索引的磁碟空間，提高索引的快取效率
  3. 無主鍵的表刪除，在 ROW 模式的主從架構，會導致備庫夯住
  4. 更多使用業務主鍵，在分庫分表會有更多便利性。

7.禁止使用外來鍵，如果有外來鍵完整性約束，需要應用程式控制

外來鍵會導致表與表之間耦合，Update 與 Delete 操作都會涉及相關聯的表，十分影響SQL的效能，甚至會造成死鎖。

8.把欄位定義為 NOT NULL 並且提供預設值

  1. null 的列使索引/索引統計/值比較都更加複雜，對 MySQL 來說更難優化。
  2. null 這種型別 MySQL 內部需要進行特殊處理，增加資料庫處理記錄的複雜性；同等條件下，表中有較多空欄位的時候，資料庫的處理效能會降低很多。
  3. null 值需要更多的儲存空，無論是表還是索引中每行中的 null 的列都需要額外的空間來標識。
  4. 對null 的處理時候，只能採用 is null 或 is not null ，而不能採用 =、in、<、<>、!=、not in 這些操作符號

9.禁止使用 TEXT、BLOB 型別

會浪費更多的磁碟和記憶體空間，非必要的大量的大欄位查詢會淘汰掉熱資料，導致記憶體命中率急劇降低，影響資料庫效能。

10.禁止使用小數儲存貨幣

都 0202年 了，使用整數吧，小數容易導致 錢對不上 或者 精度問題

11.使用 varchar(20) 儲存手機號

  1. 涉及到區號或者國家代號，可能出現+-()
  2. 手機號會去做數學運算麼？
  3. varchar 可以支援模糊查詢，例如：like“138%”

12.禁止使用ENUM，可使用TINYINT代替

  1. 增加新的 ENUM 值要做 DDL 操作
  2. ENUM 的內部實際儲存就是整數，你以為自己定義的是字串？

13.關於索引設計

  1. 單表索引建議控制在 5個 以內

  2. 索引並不是越多越好！索引可以提高效率同樣可以降低效率。

  3. 索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率。

  4. 因為 MySQL 優化器在選擇如何優化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，就會增加 MySQL 優化器生成執行計劃的時間，同樣會降低查詢效能。

  5. 禁止在更新十分頻繁、區分度不高的屬性上建立索引，例如：年齡，性別這種

  6. 更新會變更B+樹，更新頻繁的欄位建立索引會大大降低資料庫效能

  7. 性別 這種區分度不大的屬性，建立索引是沒有什麼意義的，不能有效過濾資料，效能與全表掃描類似

  8. 建立組合索引，必須把區分度高的欄位放在前面，能夠更加有效的過濾資料

14.關於SQL使用規範

  1. 不要使用 INSERT INTO t_xxx VALUES(xxx)，必須顯示指定插入的列屬性，容易在增加或者刪除欄位後出現程式BUG

  2. 不要在 WHERE 條件的屬性上使用函式或者表示式

  3. 例如

  4. 會導致全表掃描

  5. SELECT uid FROM t_user WHERE from_unixtime(day)>=’2019-10-09’

  6. 優化寫法

  7. SELECT uid FROM t_user WHERE day>=unix_timestamp(‘2019-10-09 00:00:00’)

  8. 不要負向查詢，以及 %開頭 的模糊查詢

  9. 負向查詢條件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會導致全表掃描

  10. %開頭 的模糊查詢，會導致全表掃描

  11. 如果需要搜尋，可以使用全文索引

  12. 不要在大表使用 JOIN 查詢，禁止大表使用子查詢，會產生臨時表，消耗較多 記憶體 與 CPU，極大影響資料庫效能

  13. 儘量不要使用 OR 條件，必須改為 IN 查詢。舊版本 Mysql 的 OR 查詢是不能命中索引的，即使能命中索引，也沒有必要

  14. 應用程式必須捕獲SQL異常，並有相應處理

15. 多使用 EXPLAIN 優化

做MySQL優化，我們要善用 EXPLAIN 檢視SQL執行計劃

以下是優化的註釋

key 列 => 使用到的索引名。如果沒有選擇索引，值是 NULL。可以強制索引

type 列 => 連線型別，一個好的 sql 語句最好可以達到 range 級別。杜絕出現 all 級別

rows列 => 掃描行數，該值是個預估值，心裡有數就好了
extra列 => 詳細說明，注意常見的不太友好的值有：Using filesort, Using temporary
key_len列 => 索引長度

16.SQL 語句中 IN 包含的值不應過多

在使用 IN 的時候，MySQL 對於 IN 做了相應的優化，即將 IN 中的常量全部儲存在一個陣列裡面，而且這個陣列是排好序的。但是如果數值較多，產生的消耗也是比較大的。再例如：select id from table_name where num in(1,2,3)對於連續的數值，能用 between 就不要用 in 了，再或者使用連線來替換。

17. SELECT 語句務必指明欄位名稱

SELECT * 增加很多不必要的消耗（cpu、io、記憶體、網路頻寬）；增加了使用覆蓋索引的可能性。當表結構發生改變時，前斷也需要更新。所以要求直接在 select 後面接上欄位名。

18. 當只需要一條資料的時候，使用最好使用 limit 1

這是為了使 EXPLAIN 中 type 列達到 const 型別

19. 如果排序欄位沒有用到索引，就儘量少排序

20. 如果限制條件中其他欄位沒有索引，儘量少用or

or 兩邊的欄位中，如果有一個不是索引欄位，而其他條件也不是索引欄位，會造成該查詢不走索引的情況。很多時候使用 union all 或者是 union (必要的時候)的方式來代替“or”會得到更好的效果

21. 儘量用union all代替union

union 和 union all的差異主要是前者需要將結果集合並後再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。當然，union all 的前提條件是兩個結果集沒有重複資料。

22. 不使用ORDER BY RAND()

select id from table_name order by rand() limit 10000;
上面的sql語句，可優化為
select id from table_name t1 join (select rand() * (select max(id) from table_name) as nid) t2 ont1.id > t2.nid limit 1000;

23. 使用合理的分頁方式以提高分頁的效率

select id,name from table_name limit 866613, 20
使用上述 sql 語句做分頁的時候，可能有人會發現，隨著表資料量的增加，直接使用 limit 分頁查詢會越來越慢。
優化的方法如下：可以取前一頁的最大行數的id，然後根據這個最大的 id 來限制下一頁的起點。比如此列中，上一頁最大的 id 是 866612 。sql可以採用如下的寫法：
select id,name from table_name where id> 866612 limit 20

24. 避免在 where 子句中對欄位進行 null 值判斷

對於 null 的判斷會導致引擎放棄使用索引而進行全表掃描。前文有說，不使用 null

25. 避免在 where 子句中對欄位進行表示式操作

比如
select user_id,user_project from table_name where age*2=36;
中對欄位就行了算術運算，這會造成引擎放棄使用索引，建議改成：
select user_id,user_project from table_name where age=36/2;

26. 對於聯合索引來說，要遵守最左字首法則

舉列來說索引含有欄位id,name,school，可以直接用id欄位，也可以id,name這樣的順序，但是name;school都無法使用這個索引。所以在建立聯合索引的時候一定要注意索引欄位順序，常用的查詢欄位放在最前面

27. 必要時可以使用force index來強制查詢走某個索引

有的時候 MySQL 優化器採取它認為合適的索引來檢索 sql 語句，但是可能它所採用的索引並不是我們想要的。這時就可以採用 force index 來強制優化器使用我們制定的索引。同時也可以為某些條件下，用不到索引的語句，進行強制索引搜尋

28. 注意範圍查詢語句

對於聯合索引來說，如果存在範圍查詢，比如between,>,<等條件時，會造成後面的索引欄位失效。

29. 軟優化

  1. 注意查詢語句優化
  2. 優化子查詢
  3. 使用索引
  4. 分解表，冷欄位也可以和熱欄位分開
  5. 中間表
  6. 儘量使用 inner join**，避免** left join
  7. 利用小表去驅動大表，利用
  8. 分析表，檢查表，優化表
  9. 增加**冗餘欄位，減少查詢，別什麼都拆的很散**
  10. 分庫分表，問就是 一主多從 或者 直接上 多主多從
  11. 快取叢集

30. 硬優化 - 錢錢錢

a. 配置多核心和頻率高的 cpu ,多核心可以執行多個執行緒.
b. 配置大記憶體,提高記憶體,即可提高快取區容量,因此能減少磁碟 I/O 時間,從而提高響應速度.

c. 配置高速磁碟或合理分佈磁碟:高速磁碟提高 I/O ,分佈磁碟能提高並行操作的能力.

d. 打個比喻，阿里雲的伺服器做自建資料庫，伺服器硬碟最好是 ESSD 那種級別，別給我上高效雲盤

31. 優化資料庫引數

  1. MySQL 服務的配置引數都在 my.cnf 或 my.ini ,下面列出效能影響較大的幾個引數.
  2. key_buffer_size => 索引緩衝區大小
  3. table_cache => 能同時開啟表的個數
  4. query_cache_size =>查詢緩衝區大小
  5. query_cache_type => 前面引數的開關, 0 表示不使用緩衝區,1表示使用緩衝區
  6. 但可以在查詢中使用 SQL_NO_CACHE 表示不要使用緩衝區
  7. 表示在查詢中明確指出使用緩衝區才用緩衝區, 即 SQL_CACHE.
  8. sort_buffer_size => 排序緩衝區
  9. 更多引數 => www.mysql.com/cn/why-mysql/perform...
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章