數據庫的那些事（全是干貨）

談到數據庫，大家第一想法就是怎么去優化，怎么讓查詢操作更快。我認為最好的方式就是從開始數據庫設計的時候就要盡量考慮周全。如果不幸是個老項目，就得從優化入手了。接下就從設計和優化談一下我的一些認識和經驗。

1：數據庫的設計

一個好的數據庫設計方案對于數據庫的性能常常會起到事半功倍的效果。數據庫的設計包含數據庫架構和業務表的設計。

1）數據庫架構

根據不同的數據量和訪問量，來設計不同的架構。適合自己的才是最好的。

• 單實例：數據讀取和寫入都是一個數據庫實例。（備份實例不算在內）。這個適用于小型的企業內部系統。缺點是只適合數據量少的場景，優點是能達到數據的強一致性。

  

• 垂直拆分，多實例。不同的業務走不同的實例。同樣也是適用于單個業務，數據量不大，并且每個業務相對獨立，不產生關聯。

• 讀寫分離，主從架構。通過主從結構，主庫抗寫壓力，通過從庫來分擔讀壓力。適用于寫少讀多，數據一致性的實時性要求不高的應用。

• 主從，集群結構。適用于寫多，讀也多的應用。實現是最為復雜的一種，需要考慮數據如何路由，后期擴容也相對麻煩。需要設計初期在理解業務的基礎上，選擇好合適的路由策略。例如range，hash等

• 云數據庫：阿里云rds等。適合沒有專業數據庫運維團隊的公司，使用非常方便和簡單。

2）業務表的設計

常見業務表分類：

（1）配置表

這種表通常存放業務一些基礎的配置信息或者字典信息。表的數據量一般都比較小，修改變化的操作不太頻繁，通常都是Select查詢操作。

（2）狀態表

這種表通常存放在業務系統中實體讀象的狀態信息，常見的有用戶信息表，訂單信息表等。這種表的數據量與實體讀象的規模有直接關系，比如一個APP有多少注冊用戶，通常這個APP的用戶表都會有多少條記錄。狀態表的變化通常比較頻繁，而且Insert、Update、Select操作都會有，Delete操作是否有，通常會根據業務情況的規定決定。

（3）日志表

這種表通常用來記錄業務系統中某種實體的狀態信息，常見的有用戶登錄表、充值信息記錄表等。這種表的數據規模通常比較大，而且如果業務狀態變化頻繁，記錄的變化信息比較多，這種表的數據量和插入性能都要求比較高。日志表的操作，通常會以Insert操作為主，個別業務會對日志表進行查詢。MySQL五種特殊需求架構中的高性能寫入架構，主要就是應用這種表的需求。

（4）歸檔表

這種表，是將上面三種OLTP業務表的數據進行歸檔或者冷熱分離的表。對線上業務三類表進行數據歸檔、冷熱分離，一方面可以控制線上業務表的數據規模，保證業務表性能；另一方面進行歸檔后，可用于對歸檔歷史數據進行更好的查詢反映和支持。歸檔表的數據量大小與對應的線上表大小、歸檔周期有關。歸檔表的操作，除了歸檔過程的數據加載外，主要就是Select查詢操作了，歸檔后就算是只讀表。

（5）統計數據表

統計數據表，是指業務有離線統計分析需求時，需要將各種線上表和歸檔表的數據，通過ETL過程流轉到線上OLAP統計分析系統中的原始數據表。這類表通常數據量會非常大，一個OLAP統計分析平臺會匯總多個線上業務系統的數據進行統計分析。統計數據表的操作，除了數據流轉動作外，主要就是各種統計分析程序的訪問計算。

（6）統計結果表

統計結果表是在業務有離線統計分析需求時，各種統計分析過程訪問統計數據表中的數據，按照一定的邏輯進行統計分析后的結果數據。這種統計結果數據，通常數據量會比較小。統計結果表的操作，處理結果流轉動作外，主要就是供訪問接口進行Select查詢。

對業務表類型的梳理，可以對所有的業務系統進行一個大體的劃分，做到心中有數。

下面是設計表的一些經驗：

• 將字段很多的表分解成多個表：對于字段較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來，因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢。

• 增加中間表：對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。

• 增加冗余字段：合理的加入冗余字段可以提高查詢速度。（冗余字段會導致一些問題，比如，冗余字段的值在一個表中被修改了，就要同步關聯的表，否則會導致數據不一致。這要根據實際情況，平衡數據庫性能，進行冗余字段的設計。）

• 所有字段均定義為NOT NULL，除非你真的想存儲null。
  

• 提前做好數據量的預估，進行分表設計。不要等需要拆分時再拆，一般把表的數據量控制在千萬級別。當單表數據量達到一定程度時（MySQL5.x時代的性能拐點則為1KW - 2KW行級別，具體需根據實際情況測試），為了提升性能，最為常用的方法就是分表。分表的策略可以是垂直拆分（比如：不同訂單狀態的訂單拆分到不同的表），也可以是水平拆分（比如：按月將訂單拆分到不同表）。如果在業務層分表，會將邏輯變得復雜，而且分散。可以引入分表的中間件屏蔽分表后的細節，讓業務層像查詢單表一樣查詢分表后的數據。比如Mycat。（訪問量不大，但是表數據很多的表，我們可以采取分區表，實現起來也比較簡單）

• 選擇統一的字符集。MySQL采用“階梯”式的方式來設定字符集默認值，每個數據庫，每張表都有自己的默認值，它們逐層繼承，最終最靠底層的默認設置將影響你創建的對象。不同字符集和校對規則之間的轉換可能會帶來額外的系統開銷，影響數據庫的性能。

• 合理的設置主鍵和索引。

  主鍵分自增主鍵和業務主鍵。

  自增主鍵：寫入、查詢效率和磁盤利用率都高，但每次查詢都需要兩級索引，因為線上業務不會有直接使用主鍵列的查詢。

  業務主鍵：寫入、查詢效率和磁盤利用率都低，但可以使用一級索引，依賴覆蓋索引的特性，某些情況下在非主鍵索引上也可以實現1次索引完成查詢

  一般情況下都是采用業務主鍵。

  索引大概分為三類。

  主鍵索引：InnoDB會自動在表的主鍵上創建索引，數據結構使用B+Tree。

  非主鍵索引：非主鍵列上的索引為二級索引(因為一次查詢需要查找兩個索引樹)

  聯合索引：聯合索引也叫多列索引，索引結構的key包含多個字段，排序時先第一列比較，如果相同再按第二列比較，以此類推。
  

2：數據庫的優化（mysql）

說起mysql優化，一定要了解一下mysql原理，這樣才能深入的理解那些sql規則。下圖展示了MySQL的邏輯架構圖。

MySQL客戶端/服務端通信協議是“半雙工”的：在任一時刻，要么是服務器向客戶端發送數據，要么是客戶端向服務器發送數據，這兩個動作不能同時發生。一旦一端開始發送消息，另一端要接收完整個消息才能響應它，所以我們無法也無須將一個消息切成小塊獨立發送，也沒有辦法進行流量控制。客戶端用一個單獨的數據包將查詢請求發送給服務器，所以當查詢語句很長的時候，需要設置max_allowed_packet參數。當服務器響應客戶端請求時，客戶端必須完整的接收整個返回結果，而不能簡單的只取前面幾條結果，然后讓服務器停止發送。因而在實際開發中，盡量保持查詢簡單且只返回必需的數據，減小通信間數據包的大小和數量是一個非常好的習慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。

1）sql的優化。通過explain和show profiles進行執行計劃分析，找出問題，進行針對性的優化。其中創建高效索引是最有效的一個手段。

• 多列索引和索引順序：

  出現多個索引做相交操作時（多個AND條件），通常來說一個包含所有相關列的索引要優于多個獨立索引。

  在選擇性高的字段上建立索引，可以讓MySQL在查詢時過濾掉更多的行。對于多列索引，哪個索引字段在前面，取決于索引的選擇性的高低。選擇性高的索引排在前面，有利于提高查詢效率。例如聯合索引（user_group_id，trade_amount）用戶的群組肯定比訂單的交易金額的選擇性高。

• 覆蓋索引：如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：

• 優化關聯查詢：以小表驅動大表。

• 子查詢盡量換成join。這是因為join，MySQL不需要在內存中創建臨時表來完成這個邏輯上的需求。
  

• 確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列，這樣MySQL才有可能使用索引來優化。

• 優化LIMIT分頁：一個常見的問題是當偏移量非常大的時候，比如：LIMIT 10000 20這樣的查詢，MySQL需要查詢10020條記錄然后只返回20條記錄，前面的10000條都將被拋棄，這樣的代價非常高。優化這種查詢一個最簡單的辦法就是盡可能的使用覆蓋索引掃描，而不是查詢所有的列。然后根據需要做一次關聯查詢再返回所有的列。對于偏移量很大時，這樣做的效率會提升非常大。考慮下面的查詢：

  修改前：SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

  修改后：

  SELECT film.film_id,film.description

  FROM film INNER JOIN (

  SELECT film_id FROM film ORDER BY title LIMIT 50,5

  ) AS tmp USING(film_id);

• 優化UNION：除非確實需要服務器去重，否則就一定要使用UNION ALL，如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這會導致整個臨時表的數據做唯一性檢查，這樣做的代價非常高。

• 避免導致索引失效的寫法

  （1）負向條件查詢不能使用索引（not in/not exists都不是好習慣）

  （2）前導模糊查詢不能使用索引（like'XX%'）

  （3）數據區分度不大的字段不宜使用索引

  （4）在屬性上進行計算不能命中索引

  （5）復合索引最左前綴不滿足

• 強制類型轉換會全表掃描

• 如果明確知道只有一條結果返回，limit 1能夠提高效率
  

2）合理設置mysql的部分參數，達到最高效。

• thread_pool_size：如果主引擎(primary storage engine)為InnoDB,thread_pool_size最佳設置可能在16和36之間，最常見的優化值傾向于24到36。

• thread_pool_stall_limit：用處理被阻塞和長時間運行的語句，確保服務器不完全被阻塞。設置過長會導致線程被阻塞，引起性能問題。

• tmp_table_size:通過設置tmp_table_size選項來增加一張臨時表的大小，例如做order by ,GROUP BY操作生成的臨時表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯接查詢速度的效果，建議盡量優化查詢，要確保查詢過程中生成的臨時表在內存中，避免臨時表過大導致生成基于硬盤的MyISAM表。