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

1：數(shù)據(jù)庫的設計

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

1）數(shù)據(jù)庫架構

根據(jù)不同的數(shù)據(jù)量和訪問量，來設計不同的架構。適合自己的才是***的。

• 單實例：數(shù)據(jù)讀取和寫入都是一個數(shù)據(jù)庫實例。（備份實例不算在內(nèi)）。這個適用于小型的企業(yè)內(nèi)部系統(tǒng)。缺點是只適合數(shù)據(jù)量少的場景，優(yōu)點是能達到數(shù)據(jù)的強一致性。

  

• 垂直拆分，多實例。不同的業(yè)務走不同的實例。同樣也是適用于單個業(yè)務，數(shù)據(jù)量不大，并且每個業(yè)務相對獨立，不產(chǎn)生關聯(lián)。

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

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

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

2）業(yè)務表的設計

常見業(yè)務表分類：

（1）配置表

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

（2）狀態(tài)表

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

（3）日志表

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

（4）歸檔表

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

（5）統(tǒng)計數(shù)據(jù)表

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

（6）統(tǒng)計結果表

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

對業(yè)務表類型的梳理，可以對所有的業(yè)務系統(tǒng)進行一個大體的劃分，做到心中有數(shù)。

下面是設計表的一些經(jīng)驗：

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

• 增加中間表：對于需要經(jīng)常聯(lián)合查詢的表，可以建立中間表以提高查詢效率。

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

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

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

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

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

  主鍵分自增主鍵和業(yè)務主鍵。

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

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

  一般情況下都是采用業(yè)務主鍵。

  索引大概分為三類。

  主鍵索引：InnoDB會自動在表的主鍵上創(chuàng)建索引，數(shù)據(jù)結構使用B+Tree。

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

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

2：數(shù)據(jù)庫的優(yōu)化（mysql）

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

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

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

• 多列索引和索引順序：

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

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

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

• 優(yōu)化關聯(lián)查詢：以小表驅動大表。

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

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

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

  修改前：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);

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

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

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

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

  （3）數(shù)據(jù)區(qū)分度不大的字段不宜使用索引

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

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

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

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

2）合理設置mysql的部分參數(shù)，達到***效。

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

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

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