導(dǎo)讀歡迎關(guān)注頭條號(hào)：老顧聊技術(shù)，接下來我們就來聊聊關(guān)于sql語句開發(fā)教程?以下內(nèi)容大家不妨參考一二希望能幫到您!sql語句開發(fā)教程歡迎關(guān)注頭條號(hào)：老顧聊技術(shù)精品....

歡迎關(guān)注頭條號(hào)：老顧聊技術(shù)，接下來我們就來聊聊關(guān)于sql語句開發(fā)教程?以下內(nèi)容大家不妨參考一二希望能幫到您!

sql語句開發(fā)教程

歡迎關(guān)注頭條號(hào)：老顧聊技術(shù)

精品原創(chuàng)技術(shù)分享，知識(shí)的組裝工

---

目錄

1. 前言
2. 命名規(guī)范
3. 基礎(chǔ)設(shè)計(jì)規(guī)范
4. 字段設(shè)計(jì)規(guī)范
5. 索引設(shè)計(jì)規(guī)范
6. SQL開發(fā)規(guī)范
7. 總結(jié)

前言

我們小伙伴們經(jīng)常使用到Mysql數(shù)據(jù)庫，一般就這么一用，很少會(huì)考慮mysql里面的細(xì)節(jié)問題，如sql語句的規(guī)范，或索引有沒有起到相應(yīng)的效果，今天老顧就給大家介紹一下mysql實(shí)戰(zhàn)

命名規(guī)范

1、所有數(shù)據(jù)庫對(duì)象都要小寫字母、并用下劃線分割

2、所有數(shù)據(jù)庫對(duì)象不要用mysql關(guān)鍵字命名

3、庫表的命名要達(dá)到看到此名稱，就大概知道是干嘛的

4、臨時(shí)庫表要以tmp_為前綴，日期為后綴

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

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

基礎(chǔ)設(shè)計(jì)規(guī)范

1、在新建表時(shí)，要使用InnoDB引擎

因?yàn)镮nnoDB支持事務(wù)、行鎖、性能更好

2、新庫使用utf8mb4字符集

兼容更好，可以避免產(chǎn)生亂碼，防止索引創(chuàng)建失敗

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

方便以后的系統(tǒng)維護(hù)

4、禁止使用存儲(chǔ)過程、視圖、觸發(fā)器、Event

能夠不占用數(shù)據(jù)庫的資源，就不要占用；讓這些計(jì)算上移到服務(wù)層。

將來的進(jìn)行數(shù)據(jù)拆分方便，存儲(chǔ)過程等是針對(duì)單實(shí)例的，無法適用分庫分表的架構(gòu)

5、單表數(shù)據(jù)量，控制在500萬以內(nèi)

當(dāng)然mysql可以存儲(chǔ)1000萬數(shù)據(jù)，但過大后會(huì)影響mysql 的性能以及維護(hù)工作。

想要存儲(chǔ)更多的數(shù)據(jù)，可以對(duì)數(shù)據(jù)進(jìn)行拆分，分庫分表設(shè)計(jì)來控制單表數(shù)據(jù)量

6、謹(jǐn)慎利用Mysql分區(qū)功能

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

7、減少表的寬度、冷熱數(shù)據(jù)分離、必須有主鍵

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

寬度越大，加載在內(nèi)存中占用內(nèi)存就越大，IO消耗越大。表的寬度建議在30左右，

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

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

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

這個(gè)是不是和我們理解的不一樣，為什么不要外鍵？

外鍵會(huì)導(dǎo)致表與表之間耦合，這樣更新操作都會(huì)涉及到相關(guān)聯(lián)的表，十分影響sql的性能，且容易造成死鎖。

9、禁止使用預(yù)留字段

很多小伙伴為了以后的業(yè)務(wù)擴(kuò)展，都喜歡在表中建立類似DEMO_1、DEMO_2字段，列名沒有任何業(yè)務(wù)含義，而且類型都是用String代替。

預(yù)留字段另一個(gè)好處就是業(yè)務(wù)改變后，利用預(yù)留字段，SQL語句不需要改變，其實(shí)這個(gè)問題用一些ORM工具就能夠很好的解決

字段設(shè)計(jì)規(guī)范

1、優(yōu)先選擇符合業(yè)務(wù)的最小存儲(chǔ)類型

可以有效節(jié)省數(shù)據(jù)庫的空間，查詢的時(shí)候也能夠減少IO消耗

2、字段定義為Not Null，且提供默認(rèn)值

null值的列，很難對(duì)索引優(yōu)化；

null的列對(duì)占用更多的空間，因?yàn)樾枰~外的空間來標(biāo)識(shí)。

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

3、禁止使用Text、BLOB類型

Mysql內(nèi)存臨時(shí)表不支持Text、Blob類型，如果查詢中包含這些類型，就不能使用內(nèi)存臨時(shí)表，而會(huì)采用磁盤臨時(shí)表，導(dǎo)致性能很差

會(huì)浪費(fèi)更多的磁盤和內(nèi)存空間，導(dǎo)致數(shù)據(jù)庫內(nèi)存命中率低，影響數(shù)據(jù)庫性能

如果一定要使用，建立單獨(dú)的擴(kuò)展表

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

修改Enum值時(shí)，需要使用alter語句

order by操作效率低

5、禁止使用小數(shù)

直接使用整數(shù)，小數(shù)容易有精度差異，導(dǎo)致金額對(duì)不上

6、使用Timestamp或Datetime類型存儲(chǔ)時(shí)間

經(jīng)常小伙伴們用String類型儲(chǔ)存時(shí)間

缺點(diǎn)1:無法用日期函數(shù)進(jìn)行計(jì)算比較

缺點(diǎn)2:用戶字符串存儲(chǔ)，占用更多的空間

索引設(shè)計(jì)規(guī)范

1、每張表索引不要超過5個(gè)

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

但針對(duì)查詢，索引也不是越多越好。因?yàn)閙ysql優(yōu)化器在選擇如何優(yōu)化查詢時(shí)，會(huì)根據(jù)查詢信息，對(duì)每一個(gè)用到的索引進(jìn)行評(píng)估，以生成一個(gè)最好的執(zhí)行計(jì)劃，如果有很多個(gè)索引，就會(huì)增加mysql優(yōu)化器的執(zhí)行時(shí)間，反而降低了查詢性能

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

更新頻繁會(huì)變更B 樹，大大降低數(shù)據(jù)庫的性能。

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

3、聯(lián)合索引時(shí)，把區(qū)分度高的放到最左側(cè)

因?yàn)閙ysql的索引結(jié)構(gòu)原理，聯(lián)合索引有一個(gè)原則，就是最左索引原則。

a、盡量把區(qū)分度高的放在聯(lián)合索引的最左側(cè)

b、把查詢頻繁的列放在最左側(cè)

c、把字段長度小的放到最左側(cè)，這樣內(nèi)存頁存儲(chǔ)數(shù)據(jù)量越大，IO性能越好

SQL開發(fā)規(guī)范

1、禁止使用select *

要用select 列名 代替 select *

原因：

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

2、無法使用覆蓋索引

3、可減少表結(jié)構(gòu)的改動(dòng)，帶來的代碼影響

2、禁止使用屬性隱式轉(zhuǎn)換

隱式轉(zhuǎn)換會(huì)導(dǎo)致索引失效，如：select name from customer where id=1000；

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

3、建議使用預(yù)編譯語句進(jìn)行數(shù)據(jù)庫操作

預(yù)編譯語句可以重復(fù)使用優(yōu)化計(jì)劃，減少SQL編譯時(shí)間，避免SQL注入

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

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

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

防止表結(jié)構(gòu)變化

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

負(fù)向查詢?yōu)椋簄ot、!=、<>、not in、not like等，會(huì)導(dǎo)致全表掃描

%開頭也會(huì)導(dǎo)致全表掃描

6、一個(gè)SQL只能利用復(fù)合索引中的一列進(jìn)行范圍查詢

如：有c1、c2、c3三個(gè)列建立聯(lián)合索引，在查詢條件中有c1列的范圍查詢，則在c2、c3列上的索引將不會(huì)被用到。如果一定要用c1做范圍查詢，那把c1列放到聯(lián)合索引的最右側(cè)

7、禁止在where條件上對(duì)屬性使用函數(shù)或表達(dá)式

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

應(yīng)改為

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

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

會(huì)產(chǎn)生臨時(shí)表，消耗較多的內(nèi)存、cpu資源，影響性能

9、避免使用JOIN關(guān)聯(lián)太多的表

對(duì)于Mysql來說，是有關(guān)聯(lián)緩存的，緩存的大小是由join_buffer_size參數(shù)進(jìn)行設(shè)置。

對(duì)于同一個(gè)SQL多關(guān)聯(lián)一個(gè)表，就會(huì)多分配一個(gè)關(guān)聯(lián)緩存，越多的join，就消耗越多的內(nèi)存。

如果join_buffer_size設(shè)置不合理，就會(huì)導(dǎo)致數(shù)據(jù)庫內(nèi)存溢出，影響性能和穩(wěn)定性

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

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

11、盡量減少與數(shù)據(jù)庫的交互次數(shù)

能夠一次性讀取盡可能多的數(shù)據(jù)，減少和數(shù)據(jù)庫的交互，可以極大提升數(shù)據(jù)庫的吞吐量

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

會(huì)把表中的所有數(shù)據(jù)都加到內(nèi)存中，然后在對(duì)內(nèi)存的數(shù)據(jù)進(jìn)行隨機(jī)排序，會(huì)消耗較多的CPU、IO以及內(nèi)存資源

推薦在程序中生成一個(gè)隨機(jī)值，傳給數(shù)據(jù)庫的方式

總結(jié)

上面有很多規(guī)范，也許小伙伴一時(shí)間記不住，慢慢練習(xí)就會(huì)越熟練。老顧這里給大家分享一個(gè)轉(zhuǎn)載網(wǎng)上的索引口訣，方便記憶

索引優(yōu)化口訣

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

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

索引列上少計(jì)算，范圍之后全失效；

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

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

VAR引號(hào)不可丟，SQL高級(jí)也不難！

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

---

-End-

推薦閱讀

1、不說“分布式事務(wù)”理論，直接上大廠阿里的解決方案，絕對(duì)實(shí)用

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

3、大廠二面：CAP原則為什么只能滿足其中兩項(xiàng)？而不能同時(shí)滿足

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

5、秒殺系統(tǒng)的核心點(diǎn)都在這里，快來取

6、你了解如何利用token方式實(shí)現(xiàn)分布式Session嗎？

7、Mysql索引結(jié)構(gòu)演變，為什么最終會(huì)是那個(gè)結(jié)構(gòu)呢？讓你一看就懂

8、一場(chǎng)比賽涉及到的知識(shí)，用通俗易通的方式介紹并發(fā)協(xié)調(diào)

9、企業(yè)實(shí)戰(zhàn)Redis全方面思考，你思考了嗎？

10、面試題：Thread的start和run的區(qū)別

11、面試題：什么是CAS？CAS的作用以及缺點(diǎn)

12、如何訪問redis中的海量數(shù)據(jù)？避免事故產(chǎn)生

13、如何解決Redis熱點(diǎn)問題？以及如何發(fā)現(xiàn)熱點(diǎn)？

14、如何設(shè)計(jì)API接口，實(shí)現(xiàn)統(tǒng)一格式返回？

15、你真的知道在生產(chǎn)環(huán)境下如何部署tomcat嗎？

16、分享一線互聯(lián)網(wǎng)大廠分布式唯一ID設(shè)計(jì) 之 snowflake方案

17、分享大廠分布式唯一ID設(shè)計(jì)方案，快來圍觀

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

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

20、如何永不遷移數(shù)據(jù)和避免熱點(diǎn)? 根據(jù)服務(wù)器指標(biāo)分配數(shù)據(jù)量(揭秘篇)

21、你知道怎么分庫分表嗎？如何做到永不遷移數(shù)據(jù)和避免熱點(diǎn)嗎？

22、你了解大型網(wǎng)站的頁面靜態(tài)化嗎？

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

24、你知道怎么解決DB讀寫分離，導(dǎo)致數(shù)據(jù)不一致問題嗎？

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

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

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

28、海量訂單產(chǎn)生的業(yè)務(wù)高峰期，如何避免消息的重復(fù)消費(fèi)？

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

30、微服務(wù)下的分布式session該如何管理？