数据库性能优化
性能影響
拋開業務復雜度,影響程度依次是硬件配置 > MySQL配置 > 數據表設計 > 索引優化
數據庫優化方向
- 數據庫服務器內核優化
- 由專業的數據庫開發人員去做
- my.cnf配置,搭配壓力測試進行調試
- 由運維人員去做
- SQL調優
- 由業務開發人員去做
- 代碼層面減少對數據庫的訪問
- 由業務開發人員去做
注:數據庫服務器內核優化普通人一般接觸不到,能夠接觸到的就只有從運維角度去進行的my.cnf配置和從業務開發人員角度的SQL調優與代碼層面減少對數據庫的訪問
代碼層面
- 減少同數據庫的交互次數(即減少訪問)
- 削峰填谷采用的限流、消息隊列等高并發場景下的性能優化
- 使用緩存(如Redis)優化查詢,經常查到的數據放入緩存,后續獲取直接從緩存中取出
- 由應用程序來處理數據(例:數據格式化),不推薦使用數據庫(例:數據庫函數)
- 由應用程序保證數據準確性,不推薦使用外鍵約束
- 寫多讀少場景,由應用程序保證唯一性,不推薦使用唯一索引
- 適當冗余字段,嘗試建立中間表,用應用程序計算中間結果,用空間換時間
- 不允許執行極度耗時的事務,配合應用程序拆分成更小的事務
- 預估重要數據表(比如訂單表)的負載和數據增長態勢,提前優化(分庫分表)
SQL調優
-
預編譯語句(即預先完成sql語句檢查、編譯,使用時只傳入參數)
- 減少SQL編譯所需要的時間,還可以解決動態SQL帶來的SQL注入問題
- 只傳參數比傳SQL語句更高效
- 相同語句一次解析,多次使用,提高處理效率
-
永久連接(數據庫連接是一次創建永久有效的)
- 在一些極端的環境中,Apache會不斷的發出HTTP請求,創建子進程去請求數據庫,數據庫壓力過大
-
選擇正確的數據庫引擎
- MyISAM對大數據量查詢友好,對DELETE、UPDATE、INSERT等不夠友好
- InnoDB對DELETE、UPDATE、INSERT等友好,對大數據量查詢不友好
- 數據庫主從存儲引擎可以不一致
-
擅用EXPLAIN執行計劃
-
SQL語句大寫
- SQL語句在執行的時候,是先轉化為大寫字母然后執行,直接大寫可省去轉化這一步
-
數據類型使用盡可能小的
- 硬盤上存儲占用越小速度越快,但不是越小越好,如果不能支撐業務,后續保存所需數據都保存不了,則不可取
-
選擇合適的數據類型
-
先執行的SQL語句能檢索出的數據越少越好
- 先執行的SQL語句檢索出的數據越少,下一次檢索的計算量越小,性能耗費越小
- 為了提高GROUP BY、JOIN等的效率,可以在執行到該語句前,用WHERE把不需要的記錄過濾掉
- 先執行的SQL語句檢索出的數據越少,下一次檢索的計算量越小,性能耗費越小
-
索引的使用
- WHERE子句、JOIN子句、ORDER BY、GROUP BY、HAVING子句、DISTINCT等里出現的列需要建索引
- 索引種類:普通索引、組合索引、唯一索引、組合唯一索引、主鍵索引、全文索引
- 全文索引由于查詢精度以及擴展性不佳,更多企業選擇Elasticsearch
- 索引不要建立在有大量重復數據的列上
- 索引有助于快速訪問到符合條件的數據,該列數據大量重復,則建立索引沒有意義
- 善用覆蓋索引
- 即索引已囊括所查數據,無需回表查詢,僅訪問索引即可查到所需數據
- 回表查詢即根據索引查到聚簇索引即主鍵,又根據聚簇索引查到其他所需要的列數據
- 即索引已囊括所查數據,無需回表查詢,僅訪問索引即可查到所需數據
- 善用聯合索引
- 遵循最左匹配原則,查詢條件順序如果不遵循最左匹配原則會失效
- 當我們創建了一個聯合索引(k1,k2,k3)時,相當于創建了(k1)、(k1,k2)、(k1,k2,k3)三個索引
- 查詢條件是多個單列索引時,會對兩個單列索引查到的結果做一個并集的操作,聯合索引的區分度(同時滿足兩個條件的記錄數量更少)與性能是高于多個單列索引的,且隨著數據量的增加,索引不能全部加載到內存,而是要從磁盤去讀,這樣索引的個數越多,讀磁盤的開銷就越大,因此聯合索引的建立是必要的
- 區分度最高的列放在聯合索引最左處
- 字段長度小的列放在聯合索引的最左側
- 字段長度越小,一頁能存儲的數據量越大,IO性能越好
- 使用最頻繁的列放在聯合索引的最左側
- 可以較少的建立一些索引
- 遵循最左匹配原則,查詢條件順序如果不遵循最左匹配原則會失效
- 刪除冗余和重復索引
- 如某一個索引包含在另一個聯合索引的生效范圍內,則可刪除該索引
- 重復的索引需維護,且優化器查詢時也要逐個考慮
- 索引不宜太多
- 索引越多,雖查詢效率越高,但插入、修改、刪除時因維護索引的緣故效率越低
- 索引是需要存儲的,相當于數據庫記錄的目錄,插入、修改、刪除數據時還需維護該目錄的內容
- 索引消耗磁盤和CPU,索引越多,消耗越大,造成不必要的浪費
- 索引需要存儲,消耗磁盤
- 維護和讀取索引都占用很多資源(包括CPU)
- 不建議建索引的情況
- 頻繁增刪改的表不要建索引
- 頻繁更新的字段不要建索引
- 索引越多,雖查詢效率越高,但插入、修改、刪除時因維護索引的緣故效率越低
- 每張表都要有主鍵
- 無論通過什么方式去查詢,最后都會通過主鍵定位到數據(因為MySQL是先走非聚集索引,然后走聚集索引,主鍵即聚集索引)
- 主鍵對集群、分區非常重要
- 主鍵使用BIGINT,避免使用VARCHAR
- 避免在索引列上進行表達式操作,或對索引列使用MySQL的內置函數
- 對索引進行運算會使索引失效
- 可以使用索引 = 表達式或內置函數,例:可time = 10 +1,不可time + 1 = 10
- 避免SQL語句中查詢變量與字段定義類型不匹配
- 數據類型不匹配,MySQL會做隱式的轉換,函數作用于表字段,不僅浪費性能,如果是索引列,還會導致索引失效
- 例如:字符串不加單引號,日期使用字符串
- 列中盡量不要null,應用默認值代替
- null值更新到非null值無法做到原地更新,容易發生索引分裂影響性能
- 比如索引存儲中,某一頁剛好放滿數據,其中一條數據某一列本來為空,改成有值的,那一頁就放不下了(格式:主鍵id,索引列)
- null值沒有索引(有些數據庫引擎有)
- null值更新到非null值無法做到原地更新,容易發生索引分裂影響性能
- 避免用!=、<>、not in、is not null、is null等操作符
- 因為null值沒有索引,其次帶有非的操作符也不太好命中索引,一般情況下,查詢的成本高,優化器會自動放棄索引的
- 避免用OR
- 使用OR可能會使索引失效,從而全表掃描
- OR兩邊一個加了索引,一個沒加,即使第一個條件走了索引,第二個條件還是要全表掃描,也就是全表掃描+索引掃描+合并,如果它一開始就走全表掃描,直接一遍掃描就完事。MySQL自身有優化器,出于成本和效率考慮,遇到OR條件,索引失效合情合理
- 模糊查詢避免進行左側模糊查詢
- 字符串(如CHAR、VARCHAR、TEXT、BLOB等)使用的是前綴索引
- 左側模糊查詢不走索引(索引遵循最左匹配原則)
- 如果有需要左側模糊查詢的業務,可以存一個字段是該字段的顛倒值,或者考慮全文索引(不推薦)以及Elasticsearch
- 避免修改clustered索引數據列(聚簇索引,且一個表最多只有一個)
- 因為clustered索引數據列的順序就是表記錄的物理存儲順序,一旦調整會耗費相當大的資源
-
避免使用SELECT *,盡量使用SELECT 具體字段
- 查得越多速度越慢
- 消耗更多的CPU、IO、網絡帶寬資源
- 無法使用覆蓋索引
- 減少表結構變更帶來的影響
-
避免嵌套子查詢,盡量用JOIN
- IN適合主表大子表小,EXIST適合主表小子表大
- 由于查詢優化器的不斷升級,很多場景,這兩者性能差不多一樣了
- IN的值不要超過500個
- IN適合主表大子表小,EXIST適合主表小子表大
-
多表連接時,使用表的別名,并前綴于列上
- 減少解析時間,并減少來自于那些由列歧義引起的語法錯誤
-
不要有超過5個以上的表連接
- 連接表越多,編譯的時間和開銷也就越大,單次查詢涉及數據量也會很大
- 把連接表拆開成較小的幾個執行,可讀性更高
- 如果一定要連接很多表才能得到數據,那么意味著糟糕的設計了
-
INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、CROSS JOIN,優先使用INNER JOIN,如果是LEFT JOIN,左邊表結果盡量小,如果是RIGHT JOIN,右邊表結果盡量小,避免使用FULL JOIN、CROSS JOIN
- 如果內連接是等值連接、不等值連接、自然連接(因為內連接可以沒有ON,此時相當于交叉連接,要避免),或許返回的行數比較少,所以性能相對會好一點
- 使用了左連接,左邊表數據結果盡量小,條件盡量放到左邊處理,意味著返回的行數可能比較少,同理,右連接則右邊表數據結果盡量小,條件盡量放到右邊處理
- 全連接即左連接加右連接,交叉連接是兩表的笛卡爾積
-
連接表時盡量保持兩個字段一致
-
清空表數據用TRUNCATE,而不用DELETE
- TRUNCATE會重置auto_increment的值,釋放磁盤空間;不走事務,不鎖表;不產生大量日志寫入日志文件
- DELETE不重置auto_increment的值,但插入的數據會覆蓋在之前刪除的數據上;走事務,會鎖表,清空數據量過大會導致長時間內業務無法使用;會產生大量日志寫入日志文件
- 不過使用TRUNCATE會有無法回滾,數據無法恢復的風險,但是它快呀,慎用
-
INSERT、UPDATE數據過多,考慮批量INSERT、UPDATE
- 批量INSERT、UPDATE性能好,更加省時間
-
DELETE、UPDATE、SELECT后加LIMIT
- 明確確定有幾條數據要DELETE、UPDATE、SELECT時,加LIMIT 數量
- 命中后可避免繼續全表掃描,若表中數據量過大時避免因鎖表時間長將CPU打滿,以致用到相關表的業務系統長時間內無法使用
- DELETE、UPDATE的數據量過大時進行拆分處理,不要一次LIMIT太多
- 原因同第一條
- 誤DELETE、UPDATE代價小
- DELETE、UPDATE走事務,大事務會導致主從延遲
- 拆分方案
- 循環刪除、更新
- 人為并發(即多線程)刪除更新(會導致死鎖且無法回滾)
- SELECT用LIMIT進行分頁
- 原因同第一條
- 查詢數據越多,速度越慢,用戶等待時間長
- SELECT 1 FROM xxx WHERE xxx = ? LIMIT 1 判斷是否存在
- 原因同第一條
- 僅針對第一條,可不加LIMIT的前提是搜索條件加了索引,MySQL中加鎖都是基于索引的,如果是以某個加了索引的字段為條件DELETE、UPDATE、SELECT,如果該字段沒索引就會掃描到主鍵索引上,那么就算符合搜索條件的記錄只有一條,也會鎖表(加了索引后,除了被鎖的記錄,其他記錄仍可操作)
- 明確確定有幾條數據要DELETE、UPDATE、SELECT時,加LIMIT 數量
-
優化LIMIT分頁
- 例如:LIMIT 10000,10,雖然只取10條,且有索引,但還是要重新查詢計算偏移量,效率一樣很慢
- 方案
- ORDER BY + 索引字段(注:不適用于混合排序,混合排序還是會全表掃描)
- 以上一次查詢的記錄的排序字段最大值(即最后一條記錄的排序字段)作為條件
- 使用游標
-
ORDER BY必須和LIMIT聯用,否則會被優化掉
-
LIMIT以后可根據索引只取少量數據,不加LIMIT,ORDER BY 索引沒有任何意義
-
如果你將LIMIT row_count子句與ORDER
BY子句組合在一起使用的話,MySQL會在找到排序結果的第一個row_count行后立即停止排序,不會對結果集的任何剩余部分進行排序。這種行為的一種表現形式是,一個ORDER BY查詢帶或者不帶LIMIT可能返回行的順序是不一樣的,甚至多次查詢的順序也可能是不一樣的
-
-
只更新必要字段,減少binlog日志
-
使用UNION ALL代替UNION,如果結果集允許重復的話或已知不可能出現重復
- 不管檢索結果有沒有重復,都會嘗試合并,并在輸出之前進行排序,允許重復或已知不可能出現重復,UNION ALL比UNION效率更高
-
慎用DISTINCT
- 查詢一個或幾個很少字段時,會帶來優化效果,但很多時,卻會大大降低查詢效率,因為使用DISTINCT,數據庫引擎會對數據進行比較,過濾掉重復數據,但這個比較、過濾的過程會占用系統資源、CPU時間
my.cnf配置
- 修改max_connections、max_used_connections
- 并發數指同一時刻數據庫能處理多少個請求,由max_connections、max_used_connections決定
- max_connections是指MySQL實例的最大連接數,上限值是16384
- max_used_connections是指每個數據庫用戶的最大連接數
- MySQL會為每個連接提供緩沖區,意味著消耗更多的內存,如果連接數設置太高硬件吃不消,太低又不能充分利用硬件,一般要求兩者比值超過10%,計算公式如:max_used_connections / max_connections * 100% = 3 / 100 * 100%
- 查看max_connections、max_used_connections
- 并發數指同一時刻數據庫能處理多少個請求,由max_connections、max_used_connections決定
- 將單次查詢耗時控制在0.5秒內
- 0.5秒是個經驗值,源于用戶體驗的3秒原則。如果用戶的操作3秒內沒有響應,將會厭煩甚至退出。響應時間=客戶端UI渲染耗時+網絡請求耗時+應用程序處理耗時+查詢數據庫耗時,0.5秒就是留給數據庫1/6的處理時間
總結
- 上一篇: ssm 微信扫码支付
- 下一篇: 【复杂网络系列】复杂网络研究常用载体集