知識星球一、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,p=p@ssWord 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
朋友會在“發現-看一看”看到你“在看”的內容