MYSQL專題-絕對實用的MYSQL優化總結

相信大家不管是在面試中，或者是在實際的開發過程中，都有接觸過SQL優化相關的事情。之前有看到過類似知識的小夥伴可能能說出點東西來，對于沒有涉及過相關知識的夥伴也不用着急，看了這篇，相信也足夠你去應對面試或者在實際開發工程中使用了。關注收藏，随時拿出來看看，相信對你會有很大的幫助。當然以下SQL優化政策适用于資料量較大的場景下，如果資料量較小，沒必要以此為準，以免畫蛇添足。

這裡不僅僅針對SQL優化進行歸納和總結，我們從最初的建表，包括建表需要注意的事項以及規約，到最後的SQL語句，包括SQL語句書寫需要注意的事項以及對于某些SQL語句的優化這一整個過程做一個系統的總結，當然整個過程都涉及到索引的建立與使用，這個是SQL優化的關鍵，是以這裡我會一共分為三個部分進行總結：建表規範及優化、SQL規範及優化、索引規範及優化。當然有些是建議不作為SQL指定規範，大家适當甄别。

建表規範及優化

• （1） 庫名、表名、字段名均小寫，下劃線風格，不超過32個字元，必須見名知意，禁止拼音英文混用。
• （2）資料庫表、表字段必須加入中文注釋。
• （3）盡量統一使用UTF8字元集，在Mysql中的UTF8并非“真正的UTF-8”，而utf8mb4”才是真正的“UTF-8”，如果需要存儲表情，那麼選擇utf8mb4來進行存儲，注意它與utf-8編碼的差別。
• （4）存儲引擎必須使用InnoDB，因為InnoDB支援事物、行級鎖、并發性能更好，CPU及記憶體緩存頁優化使得資源使用率更高。
• （5）每張表必須設定一個主鍵ID，create_time，update_time。且這個主鍵ID使用自增主鍵（在滿足需要的情況下盡量短），除非在分庫分表環境下。
  • InnoDB組織資料的方式決定了需要有一個主鍵，而且若是這個主鍵ID是單調遞增的可以有效提高插入的性能，避免過多的頁分裂、減少表碎片提高空間的使用率。
  • 在分庫分表環境下，需要統一來配置設定各個表中的主鍵值，進而避免整個邏輯表中主鍵重複。
• （6）單表列數目必須小于30，若超過則應該考慮将表拆分。單表列數太多使得Mysql伺服器處理InnoDB傳回資料之間的映射成本太高。
• （7）禁止使用外鍵，如果有外鍵完整性限制，需要應用程式控制，因為外鍵會導緻表與表之間耦合，UPDATE與DELETE操作都會涉及相關聯的表，十分影響SQL的性能，甚至會造成死鎖。
• （8）必須把字段定義為NOT NULL并且提供預設值
  • NULL的列使索引/索引統計/值比較都更加複雜，對MySQL來說更難優化 ；
  • NULL這種類型Msql内部需要進行特殊處理，增加資料庫處理記錄的複雜性；
  • 同等條件下，表中有較多空字段的時候，資料庫的處理性能會降低很多；
  • NULL值需要更多的存儲空，無論是表還是索引中每行中的NULL的列都需要額外的空間來辨別。不要以為 NULL 不需要空間，比如char(100) 型，在字段建立時，空間就固定了， 不管是否插入值（NULL也包含在内），都是占用 100個字元的空間的，如果是varchar這樣的變長字段， null 不占用空間。
• （9）禁用保留字，如DESC、RANGE、MARCH等，請參考Mysql官方保留字
• （10）在一些場景下，考慮使用TIMESTAMP代替DATETIME
  • 這兩種類型的都能表達"yyyy-MM-dd HH:mm:ss"格式的時間
  • TIMESTAMP隻需要占用4個位元組的長度，可以存儲的範圍為(1970-2038)年，在各個時區，所展示的時間是不一樣的；
  • DATETIME類型占用8個位元組，對時區不敏感，可以存儲的範圍為(1001-9999)年。
• （11）盡可能的使用 varchar/nvarchar 代替 char/nchar
  • 因為首先變長字段存儲空間小，可以節省存儲空間；
  • 對于查詢來說，在一個相對較小的字段内搜尋效率顯然要高些。
• （12）索引命名要規範
  • 主鍵索引名為 pk_ 字段名，pk_即primary key；
  • 唯一索引名為 uk _字段名 ，uk_即unique key；
  • 普通索引名則為 idx _字段名，idx_即index 。

SQL規範及優化

SQL防範優化

• （1）寫完SQL先explain檢視執行計劃，分析一下，尤其注意走不走索引，然後針對性的進行調整優化；

explain SELECT 
   id,name
FROM subactivity where id=22184;
           

SEQ	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	1	SIMPLE	subactivity	NULL	const	PRIMARY	PRIMARY	8	const	1	100	NULL
           

• （2）操作delete或者update語句，加個limit，這樣可以降低寫錯SQL的代價
  • 加了limit 1，如果第一條就命中目标return，提高SQL效率， 沒有limit的話，就會繼續執行掃描表；
  • delete執行時，如果相關字段加了索引，MySQL會将所有相關的行加寫鎖和間隙鎖，所有執行相關行會被鎖住，如果删除數量大，會直接影響相關業務無法使用；
  • 删除資料量很大時，不加 limit限制一下記錄數，容易把cpu打滿，導緻越删越慢的。
• （3）變更SQL操作先在測試環境執行，寫明詳細的操作步驟以及復原方案，并在上生産前review。
• （4）修改或删除重要資料前，要先備份。
• （5）修改或者删除SQL，先寫WHERE查一下，确認後再補充 delete 或 update。

SQL查詢優化

• （1）避免使用select *
  • 使用select * 取出全部列，會讓優化器無法完成索引覆寫掃描這類優化，會影響優化器對執行計劃的選擇，也會增加網絡帶寬消耗，更會帶來額外的I/O，記憶體和CPU消耗。
• （2）多表關聯查詢時，小表在前，大表在後，且使用表的别名
  • 執行 from 後的表關聯查詢是從左往右執行的（Oracle相反），第一張表會涉及到全表掃描，是以将小表放在前面，先掃小表，掃描快效率較高，在掃描後面的大表，或許隻掃描大表的前100行就符合傳回條件并return了。
• （3）調整Where字句中的連接配接順序
  • MySQL采用從左往右，自上而下的順序解析where子句。根據這個原理，應将過濾資料多 的條件往前放，最快速度縮小結果集。
• （4）用where字句替換HAVING字句。
  • HAVING隻會在檢索出所有記錄之後才對結果集進行過濾
  • where是在聚合前刷選記錄，如果能通過where字句限制記錄的數目，那就能減少這方面的開銷；
  • HAVING中的條件一般用于聚合函數的過濾，除此之外，應該将條件寫在where字句中。
• （5）對于複雜的查詢，可以使用中間臨時表 暫存資料
• （6）建議使用union all
  • MySQL通過建立并填充臨時表的方式來執行union查詢。除非确實要消除重複的行，否則建議使用union all。
  • 如果沒有all這個關鍵詞，MySQL會給臨時表加上distinct選項，這會導緻對整個臨時表的資料做唯一性校驗，這樣做的消耗相當高。
• （7）拆分複雜SQL為多個小SQL，避免大事務
  • 簡單的SQL容易使用到MySQL的QUERY CACHE；
  • 減少鎖表時間特别是使用MyISAM存儲引擎的表；
  • 可以使用多核CPU。
• （8）查詢資料量大的表 會造成查詢緩慢，主要的原因是掃描行數過多。這個時候可以通過程式，分段分頁進行查詢，循環周遊，将結果合并處理進行展示。

SQL增删改優化

• （1）如果同時執行大量的插入，建議使用多個值的INSERT語句，這比使用分開INSERT語句快，一般情況下批量插入效率有幾倍的差别。
• （2）指令行修改資料，使用begin + commit 事務。
• （3）當删除全表中記錄時，使用truncate代替delete。
  • 使用delete語句的操作會被記錄到undo塊中，删除記錄也記錄binlog，當确認需要删除全表時，會産生很大量的binlog并占用大量的undo資料塊，此時既沒有很好的效率也占用了大量的資源；
  • 使用truncate替代，不會記錄可恢複的資訊，資料不能被恢複。也是以使用truncate操作有其極少的資源占用與極快的時間。另外，使用truncate可以回收表的水位，使自增字段值歸零。

索引規範及優化

索引建立規範

• （1）表的主鍵、外鍵必須有索引；
• （2）經常與其他表進行連接配接的表，在連接配接字段上應該建立索引；
• （3）對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
• （4）盡量使用數字型字段（如性别，男：1 女：2）
  • 若隻含數值資訊的字段盡量不要設計為字元型，這會降低查詢和連接配接的性能，并會增加存儲開銷；
  • 引擎在處理查詢和連接配接時會 逐個比較字元串中每一個字元，而對于數字型而言隻需要比較一次就夠了。

索引失效場景

• （1）盡量避免在 where 子句中使用!=或<>操作符，否則将引擎放棄使用索引而進行全表掃描。
• （2）盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描
• （3）盡量避免在 where 子句中使用 or 來連接配接條件，否則将導緻引擎放棄使用索引而進行全表掃描，可以改用union或union all
• （4）盡量避免在字段開頭模糊查詢，會導緻資料庫引擎放棄索引進行全表掃描。
• （5）盡量避免使用in 和not in，會導緻引擎走全表掃描。如果是連續數值，可以用between代替，如果是子查詢，可以用exists代替。
• （6）盡量避免在where條件中等号的左側進行表達式、函數操作，會導緻資料庫引擎放棄索引進行全表掃描。可以将表達式、函數操作移動到等号右側。
• （7）當資料量大時，避免使用where 1=1的條件。通常為了友善拼裝查詢條件，我們會預設使用該條件，資料庫引擎會放棄索引進行全表掃描。用代碼拼裝sql時進行判斷，沒 where 條件就去掉 where，有where條件就加 and。
• （8）where條件僅包含複合索引非前置列會導緻不會走聯合索引。
• （9）盡量避免隐式類型轉換，會造成不使用索引。

最後要說的是，索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，是以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。