MYSQL专题-绝对实用的MYSQL优化总结
相信大家不管是在面試中，或者是在實(shí)際的開(kāi)發(fā)過(guò)程中，都有接觸過(guò)SQL優(yōu)化相關(guān)的事情。之前有看到過(guò)類似知識(shí)的小伙伴可能能說(shuō)出點(diǎn)東西來(lái)，對(duì)于沒(méi)有涉及過(guò)相關(guān)知識(shí)的伙伴也不用著急，看了這篇，相信也足夠你去應(yīng)對(duì)面試或者在實(shí)際開(kāi)發(fā)工程中使用了。關(guān)注收藏，隨時(shí)拿出來(lái)看看，相信對(duì)你會(huì)有很大的幫助。當(dāng)然以下SQL優(yōu)化策略適用于數(shù)據(jù)量較大的場(chǎng)景下，如果數(shù)據(jù)量較小，沒(méi)必要以此為準(zhǔn)，以免畫(huà)蛇添足。
這里不僅僅針對(duì)SQL優(yōu)化進(jìn)行歸納和總結(jié)，我們從最初的建表，包括建表需要注意的事項(xiàng)以及規(guī)約，到最后的SQL語(yǔ)句，包括SQL語(yǔ)句書(shū)寫(xiě)需要注意的事項(xiàng)以及對(duì)于某些SQL語(yǔ)句的優(yōu)化這一整個(gè)過(guò)程做一個(gè)系統(tǒng)的總結(jié)，當(dāng)然整個(gè)過(guò)程都涉及到索引的建立與使用，這個(gè)是SQL優(yōu)化的關(guān)鍵，所以這里我會(huì)一共分為三個(gè)部分進(jìn)行總結(jié)：建表規(guī)范及優(yōu)化、SQL規(guī)范及優(yōu)化、索引規(guī)范及優(yōu)化。當(dāng)然有些是建議不作為SQL指定規(guī)范，大家適當(dāng)甄別。
建表規(guī)范及優(yōu)化
- （1） 庫(kù)名、表名、字段名均小寫(xiě)，下劃線風(fēng)格，不超過(guò)32個(gè)字符，必須見(jiàn)名知意，禁止拼音英文混用。
- （2）數(shù)據(jù)庫(kù)表、表字段必須加入中文注釋。
- （3）盡量統(tǒng)一使用UTF8字符集，在Mysql中的UTF8并非“真正的UTF-8”，而utf8mb4”才是真正的“UTF-8”，如果需要存儲(chǔ)表情，那么選擇utf8mb4來(lái)進(jìn)行存儲(chǔ)，注意它與utf-8編碼的區(qū)別。
- （4）存儲(chǔ)引擎必須使用InnoDB，因?yàn)镮nnoDB支持事物、行級(jí)鎖、并發(fā)性能更好，CPU及內(nèi)存緩存頁(yè)優(yōu)化使得資源利用率更高。
- （5）每張表必須設(shè)置一個(gè)主鍵ID，create_time，update_time。且這個(gè)主鍵ID使用自增主鍵（在滿足需要的情況下盡量短），除非在分庫(kù)分表環(huán)境下。
- InnoDB組織數(shù)據(jù)的方式?jīng)Q定了需要有一個(gè)主鍵，而且若是這個(gè)主鍵ID是單調(diào)遞增的可以有效提高插入的性能，避免過(guò)多的頁(yè)分裂、減少表碎片提高空間的使用率。
- 在分庫(kù)分表環(huán)境下，需要統(tǒng)一來(lái)分配各個(gè)表中的主鍵值，從而避免整個(gè)邏輯表中主鍵重復(fù)。
- （6）單表列數(shù)目必須小于30，若超過(guò)則應(yīng)該考慮將表拆分。單表列數(shù)太多使得Mysql服務(wù)器處理InnoDB返回?cái)?shù)據(jù)之間的映射成本太高。
- （7）禁止使用外鍵，如果有外鍵完整性約束，需要應(yīng)用程序控制，因?yàn)橥怄I會(huì)導(dǎo)致表與表之間耦合，UPDATE與DELETE操作都會(huì)涉及相關(guān)聯(lián)的表，十分影響SQL的性能，甚至?xí)斐伤梨i。
- （8）必須把字段定義為NOT NULL并且提供默認(rèn)值
- NULL的列使索引/索引統(tǒng)計(jì)/值比較都更加復(fù)雜，對(duì)MySQL來(lái)說(shuō)更難優(yōu)化 ；
- NULL這種類型Msql內(nèi)部需要進(jìn)行特殊處理，增加數(shù)據(jù)庫(kù)處理記錄的復(fù)雜性；
- 同等條件下，表中有較多空字段的時(shí)候，數(shù)據(jù)庫(kù)的處理性能會(huì)降低很多；
- NULL值需要更多的存儲(chǔ)空，無(wú)論是表還是索引中每行中的NULL的列都需要額外的空間來(lái)標(biāo)識(shí)。不要以為 NULL 不需要空間，比如char(100) 型，在字段建立時(shí)，空間就固定了， 不管是否插入值（NULL也包含在內(nèi)），都是占用 100個(gè)字符的空間的，如果是varchar這樣的變長(zhǎng)字段， null 不占用空間。
- （9）禁用保留字，如DESC、RANGE、MARCH等，請(qǐng)參考Mysql官方保留字
- （10）在一些場(chǎng)景下，考慮使用TIMESTAMP代替DATETIME
- 這兩種類型的都能表達(dá)"yyyy-MM-dd HH:mm:ss"格式的時(shí)間
- TIMESTAMP只需要占用4個(gè)字節(jié)的長(zhǎng)度，可以存儲(chǔ)的范圍為(1970-2038)年，在各個(gè)時(shí)區(qū)，所展示的時(shí)間是不一樣的；
- DATETIME類型占用8個(gè)字節(jié)，對(duì)時(shí)區(qū)不敏感，可以存儲(chǔ)的范圍為(1001-9999)年。
- （11）盡可能的使用 varchar/nvarchar 代替 char/nchar
- 因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間；
- 對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。
- （12）索引命名要規(guī)范
- 主鍵索引名為 pk_ 字段名，pk_即primary key；
- 唯一索引名為 uk _字段名 ，uk_即unique key；
- 普通索引名則為 idx _字段名，idx_即index 。
SQL規(guī)范及優(yōu)化
SQL防范優(yōu)化
- （1）寫(xiě)完SQL先explain查看執(zhí)行計(jì)劃，分析一下，尤其注意走不走索引，然后針對(duì)性的進(jìn)行調(diào)整優(yōu)化；
- （2）操作delete或者update語(yǔ)句，加個(gè)limit，這樣可以降低寫(xiě)錯(cuò)SQL的代價(jià)
- 加了limit 1，如果第一條就命中目標(biāo)return，提高SQL效率， 沒(méi)有l(wèi)imit的話，就會(huì)繼續(xù)執(zhí)行掃描表；
- delete執(zhí)行時(shí)，如果相關(guān)字段加了索引，MySQL會(huì)將所有相關(guān)的行加寫(xiě)鎖和間隙鎖，所有執(zhí)行相關(guān)行會(huì)被鎖住，如果刪除數(shù)量大，會(huì)直接影響相關(guān)業(yè)務(wù)無(wú)法使用；
- 刪除數(shù)據(jù)量很大時(shí)，不加 limit限制一下記錄數(shù)，容易把cpu打滿，導(dǎo)致越刪越慢的。
- （3）變更SQL操作先在測(cè)試環(huán)境執(zhí)行，寫(xiě)明詳細(xì)的操作步驟以及回滾方案，并在上生產(chǎn)前review。
- （4）修改或刪除重要數(shù)據(jù)前，要先備份。
- （5）修改或者刪除SQL，先寫(xiě)WHERE查一下，確認(rèn)后再補(bǔ)充 delete 或 update。
SQL查詢優(yōu)化
-
（1）避免使用select *
- 使用select * 取出全部列，會(huì)讓優(yōu)化器無(wú)法完成索引覆蓋掃描這類優(yōu)化，會(huì)影響優(yōu)化器對(duì)執(zhí)行計(jì)劃的選擇，也會(huì)增加網(wǎng)絡(luò)帶寬消耗，更會(huì)帶來(lái)額外的I/O，內(nèi)存和CPU消耗。
-
（2）多表關(guān)聯(lián)查詢時(shí)，小表在前，大表在后，且使用表的別名
- 執(zhí)行 from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的（Oracle相反），第一張表會(huì)涉及到全表掃描，所以將小表放在前面，先掃小表，掃描快效率較高，在掃描后面的大表，或許只掃描大表的前100行就符合返回條件并return了。
-
（3）調(diào)整Where字句中的連接順序
- MySQL采用從左往右，自上而下的順序解析where子句。根據(jù)這個(gè)原理，應(yīng)將過(guò)濾數(shù)據(jù)多 的條件往前放，最快速度縮小結(jié)果集。
-
（4）用where字句替換HAVING字句。
- HAVING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾
- where是在聚合前刷選記錄，如果能通過(guò)where字句限制記錄的數(shù)目，那就能減少這方面的開(kāi)銷；
- HAVING中的條件一般用于聚合函數(shù)的過(guò)濾，除此之外，應(yīng)該將條件寫(xiě)在where字句中。
-
（5）對(duì)于復(fù)雜的查詢，可以使用中間臨時(shí)表 暫存數(shù)據(jù)
-
（6）建議使用union all
- MySQL通過(guò)創(chuàng)建并填充臨時(shí)表的方式來(lái)執(zhí)行union查詢。除非確實(shí)要消除重復(fù)的行，否則建議使用union all。
- 如果沒(méi)有all這個(gè)關(guān)鍵詞，MySQL會(huì)給臨時(shí)表加上distinct選項(xiàng)，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表的數(shù)據(jù)做唯一性校驗(yàn)，這樣做的消耗相當(dāng)高。
-
（7）拆分復(fù)雜SQL為多個(gè)小SQL，避免大事務(wù)
- 簡(jiǎn)單的SQL容易使用到MySQL的QUERY CACHE；
- 減少鎖表時(shí)間特別是使用MyISAM存儲(chǔ)引擎的表；
- 可以使用多核CPU。
-
（8）查詢數(shù)據(jù)量大的表 會(huì)造成查詢緩慢，主要的原因是掃描行數(shù)過(guò)多。這個(gè)時(shí)候可以通過(guò)程序，分段分頁(yè)進(jìn)行查詢，循環(huán)遍歷，將結(jié)果合并處理進(jìn)行展示。
SQL增刪改優(yōu)化
- （1）如果同時(shí)執(zhí)行大量的插入，建議使用多個(gè)值的INSERT語(yǔ)句，這比使用分開(kāi)INSERT語(yǔ)句快，一般情況下批量插入效率有幾倍的差別。
- （2）命令行修改數(shù)據(jù)，使用begin + commit 事務(wù)。
- （3）當(dāng)刪除全表中記錄時(shí)，使用truncate代替delete。
- 使用delete語(yǔ)句的操作會(huì)被記錄到undo塊中，刪除記錄也記錄binlog，當(dāng)確認(rèn)需要?jiǎng)h除全表時(shí)，會(huì)產(chǎn)生很大量的binlog并占用大量的undo數(shù)據(jù)塊，此時(shí)既沒(méi)有很好的效率也占用了大量的資源；
- 使用truncate替代，不會(huì)記錄可恢復(fù)的信息，數(shù)據(jù)不能被恢復(fù)。也因此使用truncate操作有其極少的資源占用與極快的時(shí)間。另外，使用truncate可以回收表的水位，使自增字段值歸零。
索引規(guī)范及優(yōu)化
索引建立規(guī)范
- （1）表的主鍵、外鍵必須有索引；
- （2）經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；
- （3）對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
- （4）盡量使用數(shù)字型字段（如性別，男：1 女：2）
- 若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷；
- 引擎在處理查詢和連接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。
索引失效場(chǎng)景
- （1）盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。
- （2）盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描
- （3）盡量避免在 where 子句中使用 or 來(lái)連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，可以改用union或union all
- （4）盡量避免在字段開(kāi)頭模糊查詢，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。
- （5）盡量避免使用in 和not in，會(huì)導(dǎo)致引擎走全表掃描。如果是連續(xù)數(shù)值，可以用between代替，如果是子查詢，可以用exists代替。
- （6）盡量避免在where條件中等號(hào)的左側(cè)進(jìn)行表達(dá)式、函數(shù)操作，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。可以將表達(dá)式、函數(shù)操作移動(dòng)到等號(hào)右側(cè)。
- （7）當(dāng)數(shù)據(jù)量大時(shí)，避免使用where 1=1的條件。通常為了方便拼裝查詢條件，我們會(huì)默認(rèn)使用該條件，數(shù)據(jù)庫(kù)引擎會(huì)放棄索引進(jìn)行全表掃描。用代碼拼裝sql時(shí)進(jìn)行判斷，沒(méi) where 條件就去掉 where，有where條件就加 and。
- （8）where條件僅包含復(fù)合索引非前置列會(huì)導(dǎo)致不會(huì)走聯(lián)合索引。
- （9）盡量避免隱式類型轉(zhuǎn)換，會(huì)造成不使用索引。
最后要說(shuō)的是，索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
總結(jié)
以上是生活随笔為你收集整理的MYSQL专题-绝对实用的MYSQL优化总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 记一次fastjson转jackson的
- 下一篇： MYSQL专题-MySQL三大日志bin