怎么優化SQL數據庫

怎么優化SQL數據庫？這篇文章詳細介紹了SQL數據庫的優化原因和優化方案，閱讀完整文相信大家對SQL數據庫的優化有了一定的認識。

一：優化說明
A：有數據表明，用戶可以承受的最大等待時間為8秒。數據庫優化策略有很多，設計初期，建立好的數據結構對于后期性能優化至關重要。因為數據庫結構是系統的基石，基礎打不好，使用各種優化策略，也不能達到很完美的效果。

B：數據庫優化的幾個方面 
 
 可以看出來，數據結構、SQL、索引是成本最低，且效果最好的優化手段。

C：性能優化是無止境的，當性能可以滿足需求時即可，不要過度優化。

二：優化方向
1. SQL以及索引的優化
首先要根據需求寫出結構良好的SQL，然后根據SQL在表中建立有效的索引。但是如果索引太多，不但會影響寫入的效率，對查詢也有一定的影響。

2. 合理的數據庫是設計
根據數據庫三范式來進行表結構的設計。設計表結構時，就需要考慮如何設計才能更有效的查詢。

數據庫三范式： 
第一范式：數據表中每個字段都必須是不可拆分的最小單元，也就是確保每一列的原子性； 
第二范式：滿足一范式后，表中每一列必須有唯一性，都必須依賴于主鍵； 
第三范式：滿足二范式后，表中的每一列只與主鍵直接相關而不是間接相關(外鍵也是直接相關)，字段沒有冗余。

注意：沒有最好的設計，只有最合適的設計，所以不要過分注重理論。三范式可以作為一個基本依據，不要生搬硬套。

有時候可以根據場景合理地反規范化： 
A：分割表。 
B：保留冗余字段。當兩個或多個表在查詢中經常需要連接時，可以在其中一個表上增加若干冗余的字段，以 避免表之間的連接過于頻繁，一般在冗余列的數據不經常變動的情況下使用。 
C：增加派生列。派生列是由表中的其它多個列的計算所得，增加派生列可以減少統計運算，在數據匯總時可以大大縮短運算時間。

數據庫五大約束： 
A：PRIMARY key:設置主鍵約束； 
B：UNIQUE：設置唯一性約束，不能有重復值； 
C：DEFAULT 默認值約束 
D：NOT NULL：設置非空約束，該字段不能為空； 
E：FOREIGN key :設置外鍵約束。

字段類型選擇： 
A：盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數類型而非INT，如果非負則加上UNSIGNED 
B：VARCHAR的長度只分配真正需要的空間 
C：使用枚舉或整數代替字符串類型 
D：盡量使用TIMESTAMP而非DATETIME 
E：單表不要有太多字段，建議在20以內 
F：避免使用NULL字段，很難查詢優化且占用額外索引空間

3. 系統配置的優化
例如：MySQL數據庫my.cnf

4. 硬件優化
更快的IO、更多的內存。一般來說內存越大，對于數據庫的操作越好。但是CPU多就不一定了，因為他并不會用到太多的CPU數量，有很多的查詢都是單CPU。另外使用高的IO（SSD、RAID），但是IO并不能減少數據庫鎖的機制。所以說如果查詢緩慢是因為數據庫內部的一些鎖引起的，那么硬件優化就沒有什么意義。

三：優化方案
代碼優化
之所以把代碼放到第一位，是因為這一點最容易引起技術人員的忽視。很多技術人員拿到一個性能優化的需求以后，言必稱緩存、異步、JVM等。實際上，第一步就應該是分析相關的代碼，找出相應的瓶頸，再來考慮具體的優化策略。有一些性能問題，完全是由于代碼寫的不合理，通過直接修改一下代碼就能解決問題的，比如for循環次數過多、作了很多無謂的條件判斷、相同邏輯重復多次等。

舉個例子： 
一個update操作，先查詢出entity，再執行update，這樣無疑多了一次數據庫交互。還有一個問題，update語句可能會操作一些無需更新的字段。 
 
我們可以將表單中涉及到的屬性，以及updateTime，updateUser等賦值到entity，直接通過pdateByPrimaryKeySelective，去update特定字段。

定位慢SQL，并優化
這是最常用、每一個技術人員都應該掌握基本的SQL調優手段（包括方法、工具、輔助系統等）。這里以MySQL為例，最常見的方式是，由自帶的慢查詢日志或者開源的慢查詢系統定位到具體的出問題的SQL，然后使用explain、profile等工具來逐步調優，最后經過測試達到效果后上線。

SqlServer執行計劃：
通過執行計劃，我們能得到哪些信息： 
A：哪些步驟花費的成本比較高 
B：哪些步驟產生的數據量多，數據量的多少用線條的粗細表示，很直觀 
C：每一步執行了什么動作

具體優化手段：
A：盡量少用（或者不用）sqlserver 自帶的函數 
select id from t where substring(name,1,3) = ’abc’ 
select id from t where datediff(day,createdate,’2005-11-30′) = 0 
可以這樣查詢： 
select id from t where name like ‘abc%’ 
select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’

B：連續數值條件，用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5 
C：Update 語句，如果只更改1、2個字段，不要Update全部字段，否則頻繁調用會引起明顯的性能消耗 
D：盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型 
E：不建議使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。盡量避免向客戶 端返回大數據量，若數據量過大，應該考慮相應需求是否合理 
F：表與表之間通過一個冗余字段來關聯，要比直接使用JOIN有更好的性能 
G：select count(*) from table；這樣不帶任何條件的count會引起全表掃描 
連接池調優 
我們的應用為了實現數據庫連接的高效獲取、對數據庫連接的限流等目的，通常會采用連接池類的方案，即每一個應用節點都管理了一個到各個數據庫的連接池。隨著業務訪問量或者數據量的增長，原有的連接池參數可能不能很好地滿足需求，這個時候就需要結合當前使用連接池的原理、具體的連接池監控數據和當前的業務量作一個綜合的判斷，通過反復的幾次調試得到最終的調優參數。

合理使用索引
索引一般情況下都是高效的。但是由于索引是以空間換時間的一種策略，索引本身在提高查詢效率的同時會影響插入、更新、刪除的效率，頻繁寫的表不宜建索引。

選擇合適的索引列，選擇在where，group by，order by，on從句中出現的列作為索引項，對于離散度不大的列沒有必要創建索引。 
主鍵已經是索引了，所以primay key 的主鍵不用再設置unique唯一索引

索引類型 
主鍵索引 （PRIMARY KEY） 
唯一索引 （UNIQUE） 
普通索引 （INDEX） 
組合索引 （INDEX） 
全文索引 （FULLTEXT）

可以應用索引的操作符 
大于等于 
Between 
IN 
LIKE 不以 % 開頭

不能應用索引的操作符 
NOT IN 
LIKE %_ 開頭

如何選擇索引字段 
A：字段出現在查詢條件中，并且查詢條件可以使用索引 
B：通常對數字的索引和檢索要比對字符串的索引和檢索效率更高 
C：語句執行頻率高，一天會有幾千次以上 
D：通過字段條件可篩選的記錄集很小 
無效索引 
A：盡量不要在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描 
B：應盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。 
C：應盡量避免在 where 子句中使用 or 來連接條件，如果一個字段有索引，一個字段沒有索引，將導致引擎放棄使用索引而進行全表掃描 
select id from t where num=10 or Name = ‘admin’ 
可以這樣查詢： 
select id from t where num = 10 
union 
select id from t where Name = ‘admin’ 
union all 返回所有數據，不管是不是重復。 union會自動壓縮，去除重復數據。

D：不做列運算 
where age + 1 = 10，任何對列的操作都將導致表掃描，它包括數據庫教程函數、計算表達式等 
E：查詢like，如果是 ‘%aaa’ 不會使用到索引

分表
分表方式 
水平分割（按行）、垂直分割(按列)

分表場景 
A： 根據經驗，mysql表數據一般達到百萬級別，查詢效率就會很低。 
B： 一張表的某些字段值比較大并且很少使用。可以將這些字段隔離成單獨一張表，通過外鍵關聯，例如考試成績，我們通常關注分數，不關注考試詳情。

水平分表策略 
按時間分表：當數據有很強的實效性，例如微博的數據，可以按月分割。 
按區間分表：例如用戶表 1到一百萬用一張表，一百萬到兩百萬用一張表。 
hash分表：通過一個原始目標id或者是名稱按照一定的hash算法計算出數據存儲的表名。

讀寫分離
當一臺服務器不能滿足需求時，采用讀寫分離【寫: update/delete/add】的方式進行集群。 
一臺數據庫支持最大連接數是有限的，如果用戶的并發訪問很多，一臺服務器無法滿足需求，可以集群處理。mysql集群處理技術最常用的就是讀寫分離。

主從同步：數據庫最終會把數據持久化到磁盤，集群必須確保每個數據庫服務器的數據是一致的。從庫讀主庫寫，從庫從主庫上同步數據。 
讀寫分離：使用負載均衡實現，寫操作都往主庫上寫，讀操作往從服務器上讀。

緩存
緩存分類 
本地緩存：HashMap/ConcurrentHashMap、Ehcache、Guava Cache等 
緩存服務：Redis/Tair/Memcache等

使用場景 
短時間內相同數據重復查詢多次且數據更新不頻繁，這個時候可以選擇先從緩存查詢，查詢不到再從數據庫加載并回設到緩存的方式。此種場景較適合用單機緩存。 
高并發查詢熱點數據，后端數據庫不堪重負，可以用緩存來扛。

緩存作用 
減輕數據庫的壓力，減少訪問時間。

緩存選擇 
如果數據量小，并且不會頻繁地增長又清空（這會導致頻繁地垃圾回收），那么可以選擇本地緩存。具體的話，如果需要一些策略的支持（比如緩存滿的逐出策略），可以考慮Ehcache；如不需要，可以考慮HashMap；如需要考慮多線程并發的場景，可以考慮ConcurentHashMap。 
其他情況，可以考慮緩存服務。目前從資源的投入度、可運維性、是否能動態擴容以及配套設施來考慮，我們優先考慮Tair。除非目前Tair還不能支持的場合（比如分布式鎖、Hash類型的value），我們考慮用Redis。

緩存穿透 
一般的緩存系統，都是按照key去緩存查詢，如果不存在對應的value，就應該去后端系統查找（比 
如DB）。如果key對應的value是一定不存在的，并且對該key并發請求量很大，就會對后端系統造 
成很大的壓力。這就叫做緩存穿透。

對查詢結果為空的情況也進行緩存，緩存時間設置短點，或者該key對應的數據insert了之后清理緩存。

緩存并發 
有時候如果網站并發訪問高，一個緩存如果失效，可能出現多個進程同時查詢DB，同時設置緩存的情況， 
如果并發確實很大，這也可能造成DB壓力過大，還有緩存頻繁更新的問題。

對緩存查詢加鎖，如果KEY不存在，就加鎖，然后查DB入緩存，然后解鎖；其他進程如果發現有鎖就 
等待，然后等解鎖后返回數據或者進入DB查詢。

緩存雪崩(失效) 
當緩存服務器重啟或者大量緩存集中在某一個時間段失效，這樣在失效的時候，也會給后端系統(比如DB) 
帶來很大壓力。 
不同的key，設置不同的過期時間，讓緩存失效的時間點盡量均勻.

防止緩存空間不夠用 
① 給緩存服務，選擇合適的緩存逐出算法，比如最常見的LRU。 
② 針對當前設置的容量，設置適當的警戒值，比如10G的緩存，當緩存數據達到8G的時候，就開始發出報警，提前排查問題或者擴容。 
③ 給一些沒有必要長期保存的key，盡量設置過期時間。

我們看下圖，在WebServer（Dao層）和DB之間加一層cache，這層cache一般選取的介質是內存，因為我們都知道存入數據庫的數據都具有持久化的特點，那么讀寫會有磁盤IO的操作，內存的讀寫速度遠比磁盤快得多。（選用存儲介質，提高訪問速度：內存>>磁盤；減少磁盤IO的操作，減少重復查詢，提高吞吐量）

常用開源的緩存工具有：ehcache、memcache、redis。

ehcache 是一個純Java的進程內緩存框架，hibernate使用其做二級緩存。同時，ehcache可以通過多播的方式實現集群。本人主要用于本地的緩存，數據庫上層的緩存。 
memcache是一套分布式的高速緩存系統，提供key-value這樣簡單的數據儲存，可充分利用CPU多核，無持久化功能。在做web集群中可以用做session共享，頁面對象緩存。 
redis高性能的key-value系統，提供豐富的數據類型，單核CPU有抗并發能力，有持久化和主從復制的功能。本人主要使用redis的redis sentinel，根據不同業務分為多組。

redis注意事項 
A：在增加 key 的時候盡量設置過期時間，不然 Redis Server 的內存使用會達到系統物理內存的最大值，導致 Redis 使用 VM 降低系統性能； 
B：Redis Key 設計時應該盡可能短,Value 盡量不要使用復雜對象； 
C：將對象轉換成 JSON 對象（利用現成的 JSON 庫）后存入 Redis； 
D：將對象轉換成 Google 開源二進制協議對象（Google Protobuf，和 JSON 數據格式類似，但是因為是二進制表現，所以性能效率以及空間占用都比 JSON 要小；缺點是 Protobuf 的學習曲線比 JSON 大得多）； 
E：Redis 使用完以后一定要釋放連接。

讀取緩存中是否有相關數據，如果緩存中有相關數據，則直接返回，這就是所謂的數據命中“hit” 
如果緩存中沒有相關數據，則從數據庫讀取相關數據，放入緩存中，再返回。這就是所謂的數據未命中“miss”

緩存的命中率 = 命中緩存請求個數/總緩存訪問請求個數 = hit/(hit+miss) 
/*java框架項目案例 www.1b23.com*/

NoSQL
與緩存的區別 
先說明一下，這里介紹的和緩存不一樣，雖然redis等也可以用來做數據存儲方案（比如Redis或者Tair），但NoSql是把它作為DB來用。如果當作DB來用，需要有效保證數據存儲方案的可用性、可靠性。

使用場景 
需要結合具體的業務場景，看這塊業務涉及的數據是否適合用NoSQL來存儲，對數據的操作方式是否適合用NoSQL的方式來操作，或者是否需要用到NoSQL的一些額外特性（比如原子加減等）。 
如果業務數據不需要和其他數據作關聯，不需要事務或者外鍵之類的支持，而且有可能寫入會異常頻繁，這個時候就比較適合用NoSQL（比如HBase）。 
比如，美團點評內部有一個對exception做的監控系統，如果在應用系統發生嚴重故障的時候，可能會短時間產生大量exception數據，這個時候如果選用MySQL，會造成MySQL的瞬間寫壓力飆升，容易導致MySQL服務器的性能急劇惡化以及主從同步延遲之類的問題，這種場景就比較適合用Hbase類似的NoSQL來存儲。 
視圖/存儲過程 
普通業務邏輯盡量不要使用存儲過程，定時任務或報表統計函數可以根據團隊資源情況采用存儲過程處理。

GVM調優
通過監控系統（如沒有現成的系統，自己做一個簡單的上報監控的系統也很容易）上對一些機器關鍵指標（gc time、gc count、各個分代的內存大小變化、機器的Load值與CPU使用率、JVM的線程數等）的監控報警，也可以看gc log和jstat等命令的輸出，再結合線上JVM進程服務的一些關鍵接口的性能數據和請求體驗，基本上就能定位出當前的JVM是否有問題，以及是否需要調優。

異步/多線程
針對某些客戶端的請求，在服務端可能需要針對這些請求做一些附屬的事情，這些事情其實用戶并不關心或者用戶不需要立即拿到這些事情的處理結果，這種情況就比較適合用異步的方式處理這些事情。

異步作用 
A：縮短接口響應時間，使用戶的請求快速返回，用戶體驗更好。 
B：避免線程長時間處于運行狀態，這樣會引起服務線程池的可用線程長時間不夠用，進而引起線程池任務隊列長度增大，從而阻塞更多請求任務，使得更多請求得不到技術處理。 
C：線程長時間處于運行狀態，可能還會引起系統Load、CPU使用率、機器整體性能下降等一系列問題，甚至引發雪崩。異步的思路可以在不增加機器數和CPU數的情況下，有效解決這個問題。

異步實現 
A：額外開辟線程，這里可以采用額外開辟一個線程或者使用線程池的做法，在IO線程（處理請求響應）之外的線程來處理相應的任務，在IO線程中讓response先返回。 
B：使用消息隊列（MQ）中間件服務

關于SQL數據庫的優化就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。