MySQL 最佳化、設計規則淺談

標籤：計算   字元型   它的   order by   合并   sha   支援   time   建立   

當資料量大，資料庫相應慢時都會針對資料庫進行最佳化。這時都是要針對具體情況，具體業務需求進行最佳化的。

但是有些步驟和規則應該適合各種情況的。這裡綜合網上找的資料簡單分析一下。

第一最佳化你的sql和索引；

第二加緩衝，memcached,redis；

第三以上都做了後，還是慢，就做主從複製或主主複製，讀寫分離，可以在應用程式層做，效率高，也可以用三方工具，第三方工具推薦360的atlas,其它的要麼效率不高，要麼沒人維護；

第四如果以上都做了還是慢，不要想著去做切分，mysql內建分區表，先試試這個，對你的應用是透明的，無需更改代碼,但是sql語句是需要針對分區表做最佳化的，sql條件中要帶上分區條件的列，從而使查詢定位到少量的分區上，否則就會掃描全部分區；

第五如果以上都做了，那就先做垂直分割，其實就是根據你模組的耦合度，將一個大的系統分為多個小的系統，也就是分布式系統；第六才是水平切分，針對資料量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的sharding key,為了有好的查詢效率，表結構也要改動，做一定的冗餘，應用也要改，sql中盡量帶sharding key，將資料定位到限定的表上去查，而不是掃描全部的表；

 

 設計規則

規則1：一般情況可以選擇MyISAM儲存引擎，如果需要事務支援必須使用InnoDB儲存引擎。

注意：MyISAM儲存引擎 B-tree索引有一個很大的限制：參與一個索引的所有欄位的長度之和不能超過1000位元組。另外MyISAM資料和索引是分開，而InnoDB的資料存放區是按聚簇(cluster)索引有序排列的，主鍵是預設的聚簇(cluster)索引，因此MyISAM雖然在一般情況下，查詢效能比InnoDB高，但InnoDB的以主鍵為條件的查詢效能是非常高的。

規則2：命名規則。

• 資料庫和表名應儘可能和所服務的業務模組名一致
• 服務與同一個子模組的一類表應盡量以子模組名(或部分單詞)為首碼或尾碼
• 表名應盡量包含與所存放資料對應的單詞
• 欄位名稱也應盡量保持和實際資料相對應
• 聯合索引名稱應盡量包含所有索引鍵欄位名或縮寫，且各欄位名在索引名中的順序應與索引鍵在索引中的索引順序一致，並盡量包含一個類似idx的首碼或尾碼，以表明期物件類型是索引。
• 約束等其他對象也應該儘可能包含所屬表或其他對象的名稱，以表明各自的關係

規則3：資料庫欄位類型定義

• 經常需要計算和排序等消耗CPU的欄位,應該盡量選擇更為迅速的欄位，如用TIMESTAMP(4個位元組，最小值1970-01-01 00:00:00)代替Datetime（8個位元組，最小值1001-01-01 00:00:00）,通過整型替代浮點型和字元型
• 變長欄位使用varchar，不要使用char
• 對於二進位多媒體資料，流水隊列資料(如日誌)，超大文本資料不要放在資料庫欄位中

規則4：商務邏輯執行過程必須讀到的表中必須要有初始的值。避免業務讀出為負或無窮大的值導致程式失敗

規則5：並不需要一定遵守範式理論，適度的冗餘，讓Query盡量減少Join

規則6：訪問頻率較低的大欄位拆分出資料表。有些大欄位佔用空間多，訪問頻率較其他欄位明顯要少很多，這種情況進行拆分，頻繁的查詢中就不需要讀取大欄位，造成IO資源的浪費。

規則7：大表可以考慮水平分割。大表影響查詢效率，根據業務特性有很多拆分方式，像根據時間遞增的資料，可以根據時間來分。以id劃分的資料，可根據id%資料庫個數的方式來拆分。

一.資料庫索引

規則8：業務需要的相關索引是根據實際的設計所構造sql語句的where條件來確定的，業務不需要的不要建索引，不允許在聯合索引（或主鍵）中存在多於的欄位。特別是該欄位根本不會在條件陳述式中出現。

規則9：唯一確定一條記錄的一個欄位或多個欄位要建立主鍵或者唯一索引，不能唯一確定一條記錄，為了提高查詢效率建普通索引

規則10：業務使用的表，有些記錄數很少，甚至只有一條記錄，為了約束的需要，也要建立索引或者設定主鍵。

規則11：對於取值不能重複，經常作為查詢條件的欄位，應該建唯一索引(主鍵預設唯一索引)，並且將查詢條件中該欄位的條件置於第一個位置。沒有必要再建立與該欄位有關的聯合索引。

規則12：對於經常查詢的欄位，其值不唯一，也應該考慮建立普通索引，查詢語句中該欄位條件置於第一個位置，對聯合索引處理的方法同樣。

規則13：業務通過不唯一索引訪問資料時，需要考慮通過該索引值返回的記錄稠密度，原則上可能的稠密度最大不能高於0.2，如果稠密度太大，則不合適建立索引了。

當通過這個索引尋找得到的資料量佔到表內所有資料的20%以上時，則需要考慮建立該索引的代價，同時由於索引掃描產生的都是隨機I/O，生其效率比全表順序掃描的順序I/O低很多。資料庫系統最佳化query的時候有可能不會用到這個索引。

規則14：需要聯合索引(或聯合主鍵)的資料庫要注意索引的順序。SQL語句中的匹配條件也要跟索引的順序保持一致。

注意：索引的順勢不正確也可能導致嚴重的後果。

規則15：表中的多個欄位查詢作為查詢條件，不含有其他索引，並且欄位聯合值不重複，可以在這多個欄位上建唯一的聯合索引，假設索引欄位為 (a1,a2,...an),則查詢條件(a1 op val1,a2 op val2,...am op valm)m<=n,可以用到索引，查詢條件中欄位的位置與索引中的欄位位置是一致的。

規則16：聯合索引的建立原則(以下均假設在資料庫表的欄位a,b,c上建立聯合索引(a,b,c))

• 聯合索引中的欄位應盡量滿足過濾資料從多到少的順序，也就是說差異最大的欄位應該房子第一個欄位
• 建立索引盡量與SQL語句的條件順序一致，使SQL語句盡量以整個索引為條件，盡量避免以索引的一部分(特別是首個條件與索引的首個欄位不一致時)作為查詢的條件
• Where a=1,where a>=12 and a<15,where a=1 and b<5 ,where a=1 and b=7 and c>=40為條件可以用到此聯合索引；而這些語句where b=10,where c=221,where b>=12 and c=2則無法用到這個聯合索引。
• 當需要查詢的資料庫欄位全部在索引中體現時，資料庫可以直接查詢索引得到查詢資訊無須對整個表進行掃描(這就是所謂的key-only)，能大大的提高查詢效率。當a，ab，abc與其他表欄位關聯查詢時可以用到索引
• 當a，ab，abc順序而不是b，c，bc，ac為順序執行Order by或者group不要時可以用到索引
• 以下情況時，進行表掃描然後排序可能比使用聯合索引更加有效

　　　　a.表已經按照索引組織好了
　　　　b.被查詢的資料站所有資料的很多比例。

規則17：重要業務訪問資料表時。但不能通過索引訪問資料時，應該確保順序訪問的記錄數目是有限的，原則上不得多於10.

二.Query語句與應用系統最佳化

規則18：合理構造Query語句

• Insert語句中，根據測試，批量一次插入1000條時效率最高，多於1000條時，要拆分，多次進行同樣的插入，應該合并批量進行。注意query語句的長度要小於mysqld的參數 max_allowed_packet
• 查詢條件中各種邏輯操作符效能順序是and,or,in,因此在查詢條件中應該盡量避免使用在大集合中使用in
• 永遠用小結果集驅動大記錄集，因為在mysql中，只有Nested Join一種Join方式，就是說mysql的join是通過嵌套迴圈來實現的。通過小結果集驅動大記錄集這個原則來減少嵌套迴圈的迴圈次數，以減少IO總量及CPU運算次數
• 盡量最佳化Nested Join內層迴圈。
• 只取需要的columns，盡量不要使用select *
• 僅僅使用最有效過濾欄位，where 字句中的過濾條件少為好
• 盡量避免複雜的Join和子查詢
• Mysql在並發這塊做得並不是太好，當並發量太高的時候，整體效能會急劇下降，這主要與Mysql內部資源的爭用鎖定控制有關，MyIsam用表鎖，InnoDB好一些用行鎖。

規則19：應用系統的最佳化

• 合理使用cache，對於變化較少的部分活躍資料通過應用程式層的cache緩衝到記憶體中，對效能的提升是成數量級的。
• 對重複執行相同的query進行合并，減少IO次數。
• 事務相關性最小原則

內容來自 知乎zhuqz 和 騰雲閣

MySQL 最佳化、設計規則淺談