歡迎光臨
每天分享高質量文章

技術核心 | MySQL性能結構優化原理

一、SQL查詢優化(重要)


 

1.1 獲取有性能問題SQL的三種方式

 

  • 通過用戶反饋獲取存在性能問題的SQL;
  • 通過慢查日誌獲取存在性能問題的SQL;
  • 實時獲取存在性能問題的SQL;

 

慢查日誌分析工具

 

相關配置引數:

 

slow_query_log # 啟動停止記錄慢查日誌,慢查詢日誌預設是沒有開啟的可以在配置檔案中開啟(on)

slow_query_log_file # 指定慢查日誌的儲存路徑及檔案,日誌儲存和資料從儲存應該分開儲存

long_query_time # 指定記錄慢查詢日誌SQL執行時間的閥值預設值為10秒通常,對於一個繁忙的系統來說,改為0.001秒(1毫秒)比較合適

log_queries_not_using_indexes #是否記錄未使用索引的SQL

 

 

常用工具:mysqldumpslow和pt-query-digest

pt-query-digest --explain h=127.0.0.1,u=root,[email protected]  slow-mysql.log

 

實時獲取有性能問題的SQL(推薦)

 

SELECT id,user,host,DB,command,time,state,infoFROM information_schema.processlistWHERE TIME>=60

 

查詢當前服務器執行超過60s的SQL,可以通過腳本周期性的來執行這條SQL,就能查出有問題的SQL。

 

1.2 SQL的解析預處理及生成執行計劃(重要)

 

查詢過程描述

 

 

通過上圖可以清晰的瞭解到MySQL查詢執行的大致過程:

  • 發送SQL陳述句。
  • 查詢快取,如果命中快取直接傳回結果。
  • SQL解析,預處理,再由優化器生成對應的查詢執行計劃。
  • 執行查詢,呼叫儲存引擎API獲取資料。
  • 傳回結果。

 

查詢快取對性能的影響(建議關閉快取)

 

第一階段:

相關配置引數:

 

query_cache_type # 設置查詢快取是否可用

query_cache_size # 設置查詢快取的記憶體大小

query_cache_limit # 設置查詢快取可用的儲存最大值(加上sql_no_cache可以提高效率)

query_cache_wlock_invalidate # 設置資料表被鎖後是否傳回快取中的資料

query_cache_min_res_unit # 設置查詢快取分配的記憶體塊的最小單

 

 

快取查找是利用對大小寫敏感的哈希查找來實現的,Hash查找只能進行全值查找(sql完全一致),

如果快取命中,檢查用戶權限,如果權限允許,直接傳回,查詢不被解析,也不會生成查詢計劃。

 

在一個讀寫比較頻繁的系統中,建議關閉快取,因為快取更新會加鎖。將query_cache_type設置為off,query_cache_size設置為0。

 

第二階段:MySQL依照執行計劃和儲存引擎進行交互

 

這個階段包括了多個子過程:

 

 

一條查詢可以有多種查詢方式,查詢優化器會對每一種查詢方式的(儲存引擎)統計信息進行比較,找到成本最低的查詢方式,這也就是索引不能太多的原因。

 

1.3 會造成MySQL生成錯誤的執行計劃的原因

 

  • 統計信息不准確
  • 成本估算與實際的執行計劃成本不同

     

     

 

 

  • 給出的最優執行計劃與估計的不同

 

 

  • MySQL不考慮併發查詢
  • 會基於固定規則生成執行計劃
  • MySQL不考慮不受其控制的成本,如儲存過程,用戶自定義函式

 

1.4 MySQL優化器可優化的SQL型別

 

查詢優化器:對查詢進行優化並查詢mysql認為的成本最低的執行計劃。

為了生成最優的執行計劃,查詢優化器會對一些查詢進行改寫

 

可以優化的sql型別

 

1. 重新定義表的關聯順序;

 

 

2. 將外連接轉換為內連接;

 

3. 使用等價變換規則;

4. 優化count(),min(),max();

 

 

5. 將一個運算式轉換為常數;

 

6. 子查詢優化;

 

 

7. 提前終止查詢,如發現一個不成立條件(如where id = -1),立即傳回一個空結果;

 

8. 對in()條件進行優化;

 

1.5 查詢處理各個階段所需要的時間

 

使用profile

 

set profiling = 1; #啟動profile,這是一個session級的配製執行查詢

show profiles; # 查詢每一個查詢所消耗的總時間的信息

show profiles for query N; # 查詢的每個階段所消耗的時間

 

performance_schema是5.5引入的一個性能分析引擎(5.5版本時期開銷比較大)

 

啟動監控和歷史記錄表:use performance_schema

 

update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%';update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';

 

1.6 特定SQL的查詢優化

 

 

大表的資料修改

 

大表的結構修改

 

 

1.利用主從複製,先對從服務器進入修改,然後主從切換

 

2.(推薦)

 

添加一個新表(修改後的結構),老表資料匯入新表,老表建立觸發器,修改資料同步到新表,老表加一個排它鎖(重命名), 新表重命名, 刪除老表。

 

修改陳述句這個樣子:

alter table sbtest4 modify c varchar(150) not null default ''

 

利用工具修改:

 

優化not in 和 <> 查詢

 

子查詢改寫為關聯查詢:

 

二、分庫分表


 

2.1 分庫分表的幾種方式

 

分擔讀負載 可通過 一主多從,升級硬體來解決。

 

把一個實體中的多個資料庫拆分到不同實體(集群)

 

拆分簡單,不允許跨庫。但並不能減少寫負載

 

把一個庫中的表分離到不同的資料庫中

 

 

該方式只能在一定時間內減少寫壓力。以上兩種方式只能暫時解決讀寫性能問題。

 

資料庫分片

 

對一個庫中的相關表進行水平拆分到不同實體的資料庫中

 

如何選擇分割槽鍵

 

1.分割槽鍵要能盡可能避免跨分割槽查詢的發生

2.分割槽鍵要盡可能使各個分割槽中的資料平均

 

分片中如何生成全域性唯一ID

 

擴展:表的垂直拆分和水平拆分


 

隨著業務的發展,資料庫成為了整個系統性能的一個瓶頸,這時候就需要對資料庫進行優化,但是單單是優化只能提高有限的一點性能,這時候要想解決問題需要的是從資料庫架構層面去思考問題。資料庫的架構是一個很大的課題,裡面最實用的有兩個,一個是資料庫拆分,一個是讀寫分離。今天就來談談資料庫的兩種拆分方式。

 

一、垂直拆分

 

垂直拆分很簡單,就是根據不同的業務來劃分不同的資料庫。比如一個電商系統根據業務可以分成商品表、會員表、訂單表。原先,這些表都是放在同一個資料庫服務器上,現在需要垂直拆分資料庫,就是將商品表單獨放在一個資料庫中,會員表單獨放在一個資料庫中,訂單表單獨放在一個資料庫中,這樣就解決了表與表之間的io競爭。

 

二、水平拆分

 

垂直拆分比較簡單,水平拆分就比較複雜了,要考慮很多東西。垂直拆分根據業務來拆分,或者說的直白點就是根據表名來拆分,而水平拆分是根據表裡面的欄位來拆分(記住是根據欄位來拆分,而不是拆分欄位,拆分後的每一張表的表結構都是一樣)。比如要拆分用戶表,可以根據用戶的註冊時間這一欄位來拆分整個表,2016年註冊的用戶放在用戶表1中,2017年註冊的用戶放在用戶表2中,2018年註冊的用戶放在用戶表3中。這就是水平拆分,看似很簡單,實際上要考慮的東西是很多的。就比如上述的例子,我們用時間來拆分,就會有局限性。一個好產品上線後,在開始的時候用戶數量都是很少的,都需要一定時間的沉澱,才會有一個用戶數量的爆發期。如果用時間來拆分,就會出現一種情況,就是用戶表1的規模很小,而用戶表2的規模卻很大,是用戶表1的好幾倍,而用戶表三可能是用戶表1的好幾十倍。這樣的話,拆分水平拆分的意義就不大了。一般用戶表都是用戶id來拆分的,具體還要結合實際業務去分析。所以,水平拆分是一件很複雜的事情,大家在進行水平拆分的時候一定要考慮到方方面面,這樣才能設計出優秀的資料庫架構方案。

 

出處:https://www.jianshu.com/p/999537f158b1

    已同步到看一看
    赞(0)

    分享創造快樂