歡迎關注頭條號：老顧聊技術

精品原創技術分享，知識的組裝工

目錄

1. 前言
2. 命名規範
3. 基礎設計規範
4. 字段設計規範
5. 索引設計規範
6. SQL開發規範
7. 總結

前言

我們小夥伴們經常使用到mysql數據庫，一般就這麼一用，很少會考慮mysql裡面的細節問題，如sql語句的規範，或索引有沒有起到相應的效果，今天老顧就給大家介紹一下mysql實戰

命名規範

1、所有數據庫對象都要小寫字母、並用下劃線分割

2、所有數據庫對象不要用mysql關鍵字命名

3、庫表的命名要達到看到此名稱，就大概知道是幹嘛的

4、臨時庫表要以tmp_為前綴，日期為後綴

5、備份庫表要以bak_為前綴，日期為後綴

6、相同的數據，在所有表中的列名和類型要一致

基礎設計規範

1、在新建表時，要使用InnoDB引擎

因為InnoDB支持事務、行鎖、性能更好

2、新庫使用utf8mb4字符集

兼容更好，可以避免產生亂碼，防止索引創建失敗

3、表和字段必須加入中文註釋

方便以後的系統維護

4、禁止使用存儲過程、視圖、觸發器、Event

能夠不佔用數據庫的資源，就不要佔用；讓這些計算上移到服務層。

將來的進行數據拆分方便，存儲過程等是針對單實例的，無法適用分庫分表的架構

5、單表數據量，控制在500萬以內

當然mysql可以存儲1000萬數據，但過大後會影響mysql 的性能以及維護工作。

想要存儲更多的數據，可以對數據進行拆分，分庫分表設計來控制單表數據量

6、謹慎利用Mysql分區功能

在分區表中物理上面是多個文件，但邏輯上是一個文件，靈活度不夠，而且跨分區查詢效率低；還是建議使用物理分區，市面上也有一些中間件mycat、sharding-jdbc等

7、減少表的寬度、冷熱數據分離、必須有主鍵

a、mysql表的列數限制可以為4096列，每一行的數據大小不能超過65535字節；

寬度越大，加載在內存中佔用內存就越大，IO消耗越大。表的寬度建議在30左右，

b、要把經常用的數據列放在一起，這樣可以一次性讀取出來；把經常用不到的數據分離出去，這樣極大提高效率

c、主鍵的好處，就是更好的利用索引，提高查詢效率。不明白原理，可以看老顧之前的文章

8、禁止使用外鍵，交給程序控制

這個是不是和我們理解的不一樣，為什麼不要外鍵？

外鍵會導致表與表之間耦合，這樣更新操作都會涉及到相關聯的表，十分影響sql的性能，且容易造成死鎖。

9、禁止使用預留字段

很多小夥伴為了以後的業務擴展，都喜歡在表中建立類似DEMO_1、DEMO_2字段，列名沒有任何業務含義，而且類型都是用String代替。

預留字段另一個好處就是業務改變後，利用預留字段，SQL語句不需要改變，其實這個問題用一些ORM工具就能夠很好的解決

字段設計規範

1、優先選擇符合業務的最小存儲類型

可以有效節省數據庫的空間，查詢的時候也能夠減少IO消耗

2、字段定義為Not Null，且提供默認值

null值的列，很難對索引優化；

null的列對佔用更多的空間，因為需要額外的空間來標識。

null的查詢操作，也過於麻煩，只能採用is null或is not null，而不能採用=、in、<、<>、not in 、!=操作符，如：where name!='laogu'，是不會查詢出name為null的值的。

3、禁止使用Text、BLOB類型

Mysql內存臨時表不支持Text、Blob類型，如果查詢中包含這些類型，就不能使用內存臨時表，而會採用磁盤臨時表，導致性能很差

會浪費更多的磁盤和內存空間，導致數據庫內存命中率低，影響數據庫性能

如果一定要使用，建立單獨的擴展表

4、禁止使用ENUM、可用Tinyint代替

修改Enum值時，需要使用alter語句

order by操作效率低

5、禁止使用小數

直接使用整數，小數容易有精度差異，導致金額對不上

6、使用Timestamp或Datetime類型存儲時間

經常小夥伴們用String類型儲存時間

缺點1:無法用日期函數進行計算比較

缺點2:用戶字符串存儲，佔用更多的空間

索引設計規範

1、每張表索引不要超過5個

一般常識索引可以增加查詢效率，但同樣降低了插入和更新的效率。

但針對查詢，索引也不是越多越好。因為mysql優化器在選擇如何優化查詢時，會根據查詢信息，對每一個用到的索引進行評估，以生成一個最好的執行計劃，如果有很多個索引，就會增加mysql優化器的執行時間，反而降低了查詢性能

2、區分度不高、更新頻繁的列 不建議加索引

更新頻繁會變更B+樹，大大降低數據庫的性能。

區分度（區分度=列中不同值的數量/列的總行數），區分度不高（如：性別，只有男、女、未知）建立索引沒有意義，性能和全表掃描差不多

3、聯合索引時，把區分度高的放到最左側

因為mysql的索引結構原理，聯合索引有一個原則，就是最左索引原則。

a、儘量把區分度高的放在聯合索引的最左側

b、把查詢頻繁的列放在最左側

c、把字段長度小的放到最左側，這樣內存頁存儲數據量越大，IO性能越好

SQL開發規範

1、禁止使用select *

要用select 列名 代替 select *

原因：

1、消耗更多的CPU、IO開銷

2、無法使用覆蓋索引

3、可減少表結構的改動，帶來的代碼影響

2、禁止使用屬性隱式轉換

隱式轉換會導致索引失效，如：select name from customer where id='1000'；

id為整型,正確的寫法select name from customer where id=1000

3、建議使用預編譯語句進行數據庫操作

預編譯語句可以重複使用優化計劃，減少SQL編譯時間，避免SQL注入

4、禁止使用不含字段的insert語句

如：insert into t_xxxx values(xxx,xxx,xxx)

應使用insert into t_xxx(c1,c2,c3) values(xxx,xxx,xxxx)

防止表結構變化

5、禁止負向查詢，以及%開頭的模糊查詢

負向查詢為：not、!=、<>、not in、not like等，會導致全表掃描

%開頭也會導致全表掃描

6、一個SQL只能利用複合索引中的一列進行範圍查詢

如：有c1、c2、c3三個列建立聯合索引，在查詢條件中有c1列的範圍查詢，則在c2、c3列上的索引將不會被用到。如果一定要用c1做範圍查詢，那把c1列放到聯合索引的最右側

7、禁止在where條件上對屬性使用函數或表達式

如：select id from t_order where from_unixtime(create_time) >= '20190101'

應改為

select id from t_order where create_time >= unix_timestamp('20190720')

8、禁止大表使用join查詢，禁止大表使用子查詢

會產生臨時表，消耗較多的內存、cpu資源，影響性能

9、避免使用JOIN關聯太多的表

對於Mysql來說，是有關聯緩存的，緩存的大小是由join_buffer_size參數進行設置。

對於同一個SQL多關聯一個表，就會多分配一個關聯緩存，越多的join，就消耗越多的內存。

如果join_buffer_size設置不合理，就會導致數據庫內存溢出，影響性能和穩定性

10、禁止使用OR條件，必須改為IN查詢

絕大多數情況下，Mysql的OR查詢是不能命中索引的

11、儘量減少與數據庫的交互次數

能夠一次性讀取儘可能多的數據，減少和數據庫的交互，可以極大提升數據庫的吞吐量

12、禁止使用order by rand()進行排序

會把表中的所有數據都加到內存中，然後在對內存的數據進行隨機排序，會消耗較多的CPU、IO以及內存資源

推薦在程序中生成一個隨機值，傳給數據庫的方式

總結

上面有很多規範，也許小夥伴一時間記不住，慢慢練習就會越熟練。老顧這裡給大家分享一個轉載網上的索引口訣，方便記憶

索引優化口訣

全值匹配我最愛，最左前綴要遵守；

帶頭大哥不能死，中間兄弟不能斷；

索引列上少計算，範圍之後全失效；

Like百分寫最右，覆蓋索引不寫星；

不等空值還有or，索引失效要少用；

VAR引號不可丟，SQL高級也不難！

如果不能夠理解，可以私信老顧哦！謝謝！！！

-End-

推薦閱讀

1、不說“分佈式事務”理論，直接上大廠阿里的解決方案，絕對實用

2、女程序員問到這個問題，讓我思考了半天，Mysql的“三高”架構

3、大廠二面：CAP原則為什麼只能滿足其中兩項？而不能同時滿足

4、阿里P7二面：聊聊零拷貝的原理

5、秒殺系統的核心點都在這裡，快來取

6、你瞭解如何利用token方式實現分佈式Session嗎？

7、Mysql索引結構演變，為什麼最終會是那個結構呢？讓你一看就懂

8、一場比賽涉及到的知識，用通俗易通的方式介紹併發協調

9、企業實戰Redis全方面思考，你思考了嗎？

10、面試題：Thread的start和run的區別

11、面試題：什麼是CAS？CAS的作用以及缺點

12、如何訪問redis中的海量數據？避免事故產生

13、如何解決Redis熱點問題？以及如何發現熱點？

14、如何設計API接口，實現統一格式返回？

15、你真的知道在生產環境下如何部署tomcat嗎？

16、分享一線互聯網大廠分佈式唯一ID設計 之 snowflake方案

17、分享大廠分佈式唯一ID設計方案，快來圍觀

18、你想了解一線大廠的分佈式唯一ID生成方案嗎？

19、你知道如何處理大數據量嗎？(數據拆分篇)

20、如何永不遷移數據和避免熱點? 根據服務器指標分配數據量(揭祕篇)

21、你知道怎麼分庫分表嗎？如何做到永不遷移數據和避免熱點嗎？

22、你瞭解大型網站的頁面靜態化嗎？

23、你知道如何更新緩存嗎？如何保證緩存和數據庫雙寫一致性？

24、你知道怎麼解決DB讀寫分離，導致數據不一致問題嗎？

25、DB讀寫分離情況下，如何解決緩存和數據庫不一致性問題？

26、你真的知道怎麼使用緩存嗎？

27、如何利用鎖，防止緩存擊穿？重構思想的重要性

28、海量訂單產生的業務高峰期，如何避免消息的重複消費？

29、你知道如何保障生產端100%消息投遞成功嗎？

30、微服務下的分佈式session該如何管理？