本篇內(nèi)容介紹了“SQL優(yōu)化的知識點(diǎn)有哪些”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

成都創(chuàng)新互聯(lián)公司2013年成立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目做網(wǎng)站、網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢想脫穎而出為使命，1280元武寧做網(wǎng)站,已為上家服務(wù),為武寧各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:18982081108

背景

在當(dāng)今這個(gè)互聯(lián)網(wǎng)的時(shí)代無非要解決兩大難題，其一是信息安全，其二就是數(shù)據(jù)的存儲。而信息安全則是在數(shù)據(jù)存儲的基礎(chǔ)之上。一個(gè)公司從剛開始成立到發(fā)展成一個(gè)有上百人甚至上千人團(tuán)隊(duì)的時(shí)候，公司的業(yè)務(wù)量是呈上升趨勢，客戶及用戶也會越來越多；之前設(shè)計(jì)的表結(jié)構(gòu)可能會顯得不合理，表與表之間的聯(lián)系沒有一個(gè)穩(wěn)定的業(yè)務(wù)功能劃分，從而表現(xiàn)出來的是相關(guān)表的備用字段越來越不夠用甚至新加字段，最壞的情況就是不同業(yè)務(wù)表之間會有數(shù)據(jù)冗雜。從而暴露出一些設(shè)計(jì)的問題，這也就是SQL優(yōu)化點(diǎn)之一：數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計(jì)的合理性。近年來大數(shù)據(jù)越來越火，而大數(shù)據(jù)也是為了解決數(shù)據(jù)的存儲的手段之一，其目的是從海量的數(shù)據(jù)中收集到有價(jià)值的信息然后存儲到數(shù)據(jù)庫中，因?yàn)閿?shù)據(jù)量大傳統(tǒng)的數(shù)據(jù)庫無法儲存那么多的信息所以需要分析有價(jià)值的信息后再做決定是否持久化。

優(yōu)化點(diǎn)

• 前提必備知識

學(xué)會是用explain關(guān)鍵詞查看SQL語句性能，explain好像是從MySQL5.6.3開始支持 select、update、delete語句分析，之前只支持select語句?，F(xiàn)在我們普遍都是用5.7，所以的話不需要太擔(dān)心。這里的話不詳細(xì)講如何解讀explain輸出的性能信息。

• 優(yōu)化之一 - 從數(shù)據(jù)庫設(shè)計(jì)方面考慮

1. 表與表之間的業(yè)務(wù)聯(lián)系要明確：表之間其實(shí)是有業(yè)務(wù)聯(lián)系的，比如：class(primary key:class_id,所有班級信息表)、student(primary key:student_num,所有學(xué)生信息表)、student_class(primary key:stu_class_id,所有學(xué)生所在班級信息表)著三張表，如果現(xiàn)在需要一張老師對應(yīng)哪個(gè)班級的班主任的信息表；那么此時(shí)正確的方法是：新建 teacher、teacher_class表，而不是直接把老師的信息插入到student表中然后用一個(gè)字段來標(biāo)識是老師還是學(xué)生?？赡苣憧吹竭@個(gè)你會想 “我肯定會按正確的那種方式啊”，但是這只是舉一個(gè)例子，其實(shí)在實(shí)際項(xiàng)目開發(fā)過程中表與表結(jié)構(gòu)往往不會那么單一，這個(gè)時(shí)候你就會犯錯(cuò)誤而用字段標(biāo)識。但是也不能說是不能用字段標(biāo)識，這個(gè)要看字段標(biāo)識的兩種信息對應(yīng)的業(yè)務(wù)是否有交叉點(diǎn)來取舍。

2. 表字段盡量使用數(shù)值型：因?yàn)閿?shù)值型字段在MySQL底層應(yīng)用的時(shí)候相比string類型的話性能更好；具體為什么性能更好就需要了解MySQL底層機(jī)制了，反正記住這點(diǎn)就好。

3. 屬性盡量使用定長：以減少占用儲存空間；如果你定義了一個(gè) order_id varchar(32) ,當(dāng)在存儲的時(shí)候有一條記錄的order_id=20180910242360，此時(shí)order_id實(shí)際占用了14個(gè)字節(jié)但是這個(gè)字段的屬性長度是32，所以還有18個(gè)字節(jié)長度是無用的但卻占用著內(nèi)存空間。

4. 建立合理的索引：索引就是用某種數(shù)據(jù)結(jié)構(gòu)來查找對應(yīng)的信息，從而減低時(shí)間復(fù)雜度提高查找效率。建立索引的前提也要明確，綜合考慮再打算是否需要建立索引，畢竟索引是需要占用存儲空間的，有時(shí)候犧牲的空間卻換不回時(shí)間。
    

• 優(yōu)化之二 - 從SQL語句優(yōu)化方面考慮

1. 盡量將要輸出的字段寫出來；不要使用 select * from where xxxxx ；這種形式的語句。我在這測試時(shí)是使用*代替，但是記住在生產(chǎn)環(huán)境上盡量將字段替代*。

2. 合理使用連表查詢；不僅是表的連接需要較大的內(nèi)存消耗另外一方面如果表設(shè)計(jì)的不是很合理也會導(dǎo)致索引無效從而造成極壞的結(jié)果。

3. 查詢的時(shí)候要注意是否走索引：假如你在name列建立了一個(gè) name_index索引，查詢你使用 name Like'%xxxx' 或者 name Like'%xxxx%' 這種模糊查詢，那么此時(shí)可能就不會走索引；你應(yīng)該這樣  name Like'xxxx%' 。以下就是實(shí)際的一個(gè)例子：

建立索引：

-- 為cust_third_acct 建立一個(gè)普通索引
alter table
cust_info
add index cust_third_acct_index(cust_third_acct);

a：通過SQL查詢信息： select * from sp_tunnel_user where cust_third_acct like'0200%';　 　以下就是滿足查詢條件的部分信息

b：分析Like'%xxxx%'的查詢性能： select * from sp_tunnel_user where cust_third_acct like'%0200%';  通過Explain性能分析命令可以知道：在這種查詢條件下并沒有執(zhí)行索引，type=all表明該語句執(zhí)行的時(shí)候進(jìn)行的是全表掃描；雖然我們在 cust_third_acct  這個(gè)字段建立了索引，但是 possible_keys=null 則說明了 用 like'%0200%' 這種形式的條件是一定無法使用到  cust_third_acct_index  這個(gè)索引。(其他字段的解析請參照 《MySQL優(yōu)化之Explain命令解讀》 這篇文章，這里不做過多的分析)。

c：分析Like'xxxx%'的查詢性能： select * from sp_tunnel_user where cust_third_acct like'0200%';  與b查詢語句相比這個(gè)查詢的  possible_keys=cust_third_acct_index  ，這說明這個(gè)語句可能會用到 cust_third_acct_index 這個(gè)索引，但是key=null表明在實(shí)際的執(zhí)行過程中并沒有用到  cust_third_acct_index  索引；剛才我們也說了這種條件查詢只是可能會走索引但是不一定發(fā)生，這個(gè)跟MySQL的存儲引擎相關(guān)，但是我們使用的時(shí)候盡量以這種方式去查詢。

4. 使用索引遵循最佳左前綴特性，建立聯(lián)合索引的時(shí)候?qū)⒊Ｓ玫膶傩苑旁谧筮?。比如：我們需在在一張表?cust_id 和 cust_tp 建立一個(gè)聯(lián)合索引 cust_id_type,設(shè)定cust_id（不是唯一） 是比較常用的那么我們就將cust_id放在左邊。

建立聯(lián)合索引：

-- 為cust_id與cust_tp建立一個(gè)聯(lián)合索引
alter table
cust_info
add index  cust_id_type(cust_id,cust_tp);

5.使用符合索引的時(shí)候需要注意：使用聯(lián)合索引需要從左往右不間斷，索引才會生效，也就是說聯(lián)合索引使用的時(shí)候必須要連續(xù)但不要求全部使用。如：以上4我們建立了一個(gè)  cust_id_type  索引,當(dāng)我們在使用的時(shí)候如果where條件中只使用了 cust_id,那么也會走索引；如果where條件中只使用了 cust_tp，那么這條語句不會走索引，以下就是一個(gè)實(shí)例：

a：select * from sp_tunnel_user where cust_id='8888888888' and cust_tp='04'；  當(dāng)查詢條件用到cust_id與cust_tp兩個(gè)字段并且cust_id在前面的時(shí)候，就會用到聯(lián)合索引；通過 key=cust_id_type可以看到實(shí)際執(zhí)行過程中是用到索引了的。

b：select * from sp_tunnel_user where cust_id='8888888888' ;  當(dāng)查詢條件只用到cust_id一個(gè)字段時(shí)，也用到了聯(lián)合索引；通過 key=cust_id_type可以看到實(shí)際執(zhí)行過程中是用到索引了的，這就是左前綴原則。

c：select * from sp_tunnel_user where cust_tp='04' ;  當(dāng)查詢條件只用到cust_tp一個(gè)字段時(shí)，但卻沒有用到索引；通過 key=null 可以看到實(shí)際執(zhí)行過程并沒有用到索引，這也是左前綴原則。

• 優(yōu)化之三 - 讀寫分離與分庫分表

當(dāng)數(shù)據(jù)量達(dá)到一定的數(shù)量之后，限制數(shù)據(jù)庫存儲性能的就不再是數(shù)據(jù)庫層面的優(yōu)化就能夠解決的；這個(gè)時(shí)候往往采用的是讀寫分離與分庫分表同時(shí)也會結(jié)合緩存一起使用，而這個(gè)時(shí)候數(shù)據(jù)庫層面的優(yōu)化只是基礎(chǔ)。讀寫分離適用于較小一些的數(shù)據(jù)量；分表適用于中等數(shù)據(jù)量；而分庫與分表一般是結(jié)合著用，這就適用于大數(shù)據(jù)量的存儲了，這也是現(xiàn)在大型互聯(lián)網(wǎng)公司解決數(shù)據(jù)存儲的方法之一。至于怎么讀寫分離、怎么分表、怎么分庫，這里不做過多的闡述后續(xù)文章會有相關(guān)知識分享。

“SQL優(yōu)化的知識點(diǎn)有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！