Mysql最佳化相關總結

標籤：

最佳化順序：

• 選擇適當的引擎和表結構和資料類型
• 建立索引，最佳化sql。
• 增加緩衝，redis、memcache。
• 主從、主主，讀寫分離。
• mysql內建分區表
• 根據業務耦合垂直分割，分散式資料庫
• 水平分割，選擇合理的sharding key。

引擎區別與選擇：

• Innodb採用聚簇索引，聚簇索引包含data。輔助索引（複合索引、首碼索引、唯一索引）儲存的是主索引的值，所以尋找時需要兩次B-Tree搜尋，讀的效率稍低。但是覆蓋索引和自適應的雜湊索引可以一定程度上緩解這個問題（自適應雜湊索引不需要使用者指定，Innodb在運行時動態根據距離的訪問頻率和模式為一部分頁建立雜湊索引，但只存在記憶體中，停庫會丟失，重啟後慢慢重新維護）。Innodb使用的是行鎖，粒度更小，並發更強。並且讀寫之間是可以並發的，讀不需要加鎖，根據隔離等級不同，遇到鎖時讀取快照。所以讀寫並發是很好的。另外，Innodb相比MyIsam提供事務和外鍵功能。
• MyIsam資料和索引是分開儲存的，索引緩衝在記憶體中，索引儲存的是資料的磁碟地址。所以不需要多次查詢B-Tree，讀的效能好。但是MyIsam是表鎖，只有讀讀之間是並發的，因此寫的效率差。並且寫優先順序高，大量寫可能導致操作餓死。
• 綜上所述：如果應用的讀寫操作比例非常大，或需要全文索引，那麼可以使用MyIsam（5.6開始Innodb也支援全文索引）。其他情況，除了特殊情況特殊分析，推薦Innodb。

資料類型的選擇：

• char、varchar：char是固定長度的，varchar是可變長度的，所以char的處理速度要快得多。char適合長度變化不大的資料列，或要求查詢速度很嚴苛的情境。Myisam建議用char，Innodb建議用varchar。
• text、blob：blob可以存二進位，text只能存字元資料。可以通過合成索引來提高大文字欄位的檢索效能（建立一個額外的列儲存大文本列資料的散列值），但這種方法只適用於精確完整的匹配查詢。首碼索引也有助於查詢，但查詢條件不能以%開頭。
• 浮點數、定點數：float、double是浮點數，精度超出時會四捨五入。decimal、numberic是定點數，實際使用字串儲存的，所以精度更高，超出精度會警告或直接報錯。所以：貨幣等精度敏感的資料要用定點數，因為浮點數存在誤差問題。
• 日期類型：DATETIME能儲存年月日時分秒，比TIMESTAMP能表示的年份更久遠。如果涉及時區問題，用TIMESTAMP。

字元集： 

• 不同的字元集可能涉及隱式的轉換，而導致索引失效。

表結構：

• 選擇合適的主鍵（選擇性，長短），不要寬表，不要太多關聯，不要大欄位。
• 關於範式：
  • 第一，一個欄位只存一個意義的值。{學號，性別+年齡} 。解決辦法：{學號，性別，年齡}
  • 第二，如果主鍵是複合主鍵，非主鍵欄位不能依賴主鍵的一部分，必須依賴全部。{學號，學科，分數，總分}，分數依賴複合主鍵，但是總分只依賴學科一個欄位，所以不符合第二範式。解決辦法：{學號，學科，分數} {學科，總分}
  • 第三，不能有冗餘。{學號，班級，班主任}班主任欄位就會有大量冗餘。解決辦法：（{學號，班級} {班級，班主任}）
  • 範式不一定要嚴格遵守，要根據具體情況抉擇，適當的違反有時候會帶來好處更多一些。例如，

• qq的使用者表格儲存體qq表徵圖的點亮情況，如果每個表徵圖用一個欄位儲存，那麼幾十個表徵圖會使表很寬、很浪費空間。如果用位元影像儲存所有的點亮情況，一個或者兩個欄位就可以。雖然違反了第一範式，但是依然是個好辦法。
• 第二、第三範式雖然保證了表的嚴謹，但是可能帶來更多的串連。適當違反可以減少串連，特別是當分布式部署時，會省去很多麻煩。

索引的設計和使用：

• Myisam和Innodb的索引都是BTree索引，B代表平衡樹。都支援首碼索引，首碼索引有個缺點：order by和group by不能使用該索引。Myisam支援全文索引（5.6開始innodb也支援全文索引）。
• 最適合做索引的列是出現在where子句和連結子句中的列，而不是select中的。
• 索引列要使用短列，如果長字元列做索引，盡量用首碼索引，在最短的長度內，滿足足夠的選擇性。
• 不要過度索引，給插入和更新帶來很大負擔。
• Innodb盡量用自增列做主索引，如果不能，也不要用大欄位。
• 最左首碼原則是BTree索引使用的首要原則，即索引條件中列的順序，按複合索引中列的順序，從左至右盡量覆蓋，中間不能間斷，且盡量以精確的“=”為條件。從左至右第一個使用範圍比較的條件可以使用索引，但後面的條件列不再能使用索引。
• 常見不能使用索引的情境：
  • 以%開頭的Like查詢。可以考慮用全文索引。或利用Innodb的聚簇索引，掃索引比掃錶快得多。例如：一個表有主鍵id，輔助索引name。現在想根據name模糊搜尋 name like %end%，直接select * from table where name like %end%，會引起表的全掃描，效率低下。因為Innodb每個輔助索引中存的都是主鍵的值，所以可以改為select * from (select id from table where name like %end%) a, table b where a.id = b.id;  這樣子查詢中因為id和name在輔助索引中滿足了覆蓋索引，只掃索引就可以拿到所有滿足條件的id，然後根據id再去查詢最終結果。
  • 出現類型轉換、函數、運算、轉碼時。
  • 不包含複合索引左側列。
  • mysql猜測掃表比使用索引快的時候。
  • or串連的條件中，每個欄位必須都能用到索引，否則將全不使用索引。

事務和鎖：

• Innodb提供事務功能，Myisam不提供。
• Innodb採用行級鎖，也支援表級鎖。Myisam提供表級鎖。Innodb的讀寫並發性更好，但行級鎖有出現死結的可能。
• Innodb的行級鎖是對索引項目加鎖實現的，意味著不通過索引檢索就會鎖定所有記錄，與表鎖相同。行鎖分為三種：
  • record lock：索引項目加鎖
  • gap lock：間隙鎖
  • Next-key lock：前面兩個的組合
• 事務：
  • 原子性、一致性、持久性、隔離性
  • 並發交易處理能提高效率和資源使用率，但是也帶來了問題：
    • 更新丟失
    • 髒讀
    • 不可重複讀取
    • 幻讀
  • 隔離等級：髒讀、不可重複讀取、幻讀都是一致性問題，需要隔離機制來解決。隔離機制有兩種：加鎖、產生一致性快照。事務隔離越嚴格，副作用越小，代價越大。現有4個隔離等級，可以根據業務不同進行選擇：
    • 未提交讀 read uncommit
    • 已提交讀 read commit ： 解決髒讀
    • 可重複讀 repeatable read ： 解決不可重複讀取 （預設層級）
    • 可序列化 serializable ： 解決幻讀
  • 預設的隔離界別可重複讀，會出現幻讀的問題。select...for update可以解決幻讀問題。eg：select * from data where id < 100 fro update;  會鎖定id小於100的所有記錄以及不存在的記錄”間隙“也加鎖，也級是next-key lock，所以就避免了幻讀。但容易造成嚴重的鎖等待，盡量不用，而用精確等於的條件訪問更新資料。值得說明的是，當用等於條件來請求一個不存在的資料時，也會加next-key lock。
• 死結預防：
  • 以相同順序訪問表
  • 事務中，如果需要更新記錄，應該直接申請足夠層級的鎖：排它鎖。
  • 
    
• 分散式交易：5.0.3開始支援，且只有Innodb。兩段式提交，但是效率不理想。

表的拆分：

• 垂直分割：主碼和一部分列放在一個表， 主碼和另一部分放在另外一個表。好處是行更小，資料頁能放更多緩衝，壞處是需要管理冗餘，擷取全部資料需要join。
• 水平分割：當表很大或表中資料本來就具有獨立性時。優點：降低索引層數，缺點：給應用帶來複雜性。

分區表：

• 把一個表分成多個小的部分，對應用來說是透明的。可以儲存更多的資料，提高查詢輸送量。where字句包含分區條件時，可以只掃描部分分區，提高效率。sum、count等操作，可以在分區上並行進行再匯總。
• 同一個表的所有分區必須相同引擎。
• 分區類型：RANGE 範圍，LIST 根據枚舉，HASH 散列，KEY 類似hash。不能使用主鍵、唯一鍵以外的欄位做分區欄位。

最佳化手段：

• 通過慢查詢日誌查看已經執行的慢語句記錄。show processlist查看當前mysql正在啟動並執行線程。
• EXPLAIN查看慢sql的執行計畫。
  • select_type：select的類型。SIMPLE 簡單表、PRIMARY 主查詢、UNION 聯合中第二個或後面的、SUBQUERY子查詢。
  • table：表名
  • type：在表中找到所需行的方式。效率從低到高：
    • ALL：全表掃描
    • index：索引全掃描
    • range：索引範圍掃描
    • ref：使用非唯一索引或唯一索引的首碼索引
    • eq-ref：使用唯一索引
    • const   system ： 單表只有最多一個匹配行，可以非常迅速的找到。
    • NULL：不需要訪問表或者索引。
  • possible_keys：可能使用的索引
  • keys：實際使用的索引
  • key_len：使用到的索引欄位的長度
  • rows：掃描行的數量
  • Extra：額外的資訊
• explain extended 加上show warnings能夠看到sql真正執行前，最佳化器做了哪些修改。
• show profiles可以查看當前線程每個查詢。show profile for query + id（show profiles得到的），可以看每一步的耗時。還可以進一步在cpu io block等層級查看在使用什麼資源時，耗時高。例如：show profile cpu for query + id。
• 5.6提供了trace對sql進行跟蹤，進一步瞭解最佳化器選擇最終執行計畫的原因。
  • 首先開啟trace：set optimizer_trace="enabled=on",END_MARKERS_IN_JSON=on;
  • 緩衝大小：set  optimizer_trace_max_mem_size=1000000;
  • 執行sql
  • select * from information_schema.optimizer_trace; 查看追蹤檔案。

sql語句最佳化：

• insert：如果單個用戶端插入多條，盡量insert into test values(1,2),(3,4),(5,6)如此同時插入，減少互動
• order by：btree索引是有序儲存的，可以利用。所以盡量減少額外的filesort，通過索引直接返回有序資料。做法：order by與where使用相同的索引、複合索引。並且order by的欄位都是升序或都是降序。 如果做不到，排序操作很多，資料較多時，適當開大sort_buffer_size讓排序盡量在記憶體中完成，這個值是每個線程獨佔的，多個線程就多個buffer，注意！
• group by：預設情況下group by c1, c2會對c1,c2...的所有欄位排序，如果不需要刻意通過顯示的加一個order by null禁止排序，提高效率。
• 巢狀查詢：有些情況可以使用串連代替。
• or：保證每個列都能用到索引，會發現mysql處理時，將每個欄位分別查詢後進行了UNION操作。
• 分頁查詢：limit1000,10 會排序出前1010行，最後只去10行。效率低。
  • 利用覆蓋索引：子查詢先利用覆蓋索引查詢到滿足條件的主鍵，再利用主鍵回表尋找記錄。eg：select name, value from data order by name limit 1000, 10; 改為 select name, value from data a inner join (select id from data order by name limit 1000, 10) b on a.id = b.id;
  • 紀錄上一次結果的最後一個排序列的值，然後：where name > lastvalue order by name limit 10; 這種方法不適合排序欄位有重複值的情況，會丟紀錄。
• SQL提示：
  • USE INDEX ： 讓mysql參考提供的索引。eg：select * from data use index (idx_id);
  • IGNORE INDEX：忽略某索引
  • FORCE INDEX：強制使用某索引
• 對大表的統計操作：建立暫存資料表，講所需資料匯入暫存資料表，再統計。好處：隔離，可以臨時加欄位、索引。
• in not in  exist  not exist：    
• 技巧：
  • order by rand() limit 5；隨機選出5行。

應用最佳化：

• 串連建立的代價較大，應用串連池。
• 如果表更新操作不頻繁，可以利用查詢快取。一旦表發生一點更新，整個表的緩衝都將失效。
• 增加cache層。
• 主從來分攤讀寫壓力，但是存在延遲，需考慮。
• 分散式資料庫CLUSTER。

其他最佳化：

• 定期分析表：analyze table test; 分析表可以使系統得到準確的統計資訊，sql能產生更正確的執行計畫。
• 最佳化表：optimize table test;合并空間片段。
• 上述兩個操作會鎖表！！！

叢集：

• Mysql Cluster ：
  •  節點類型：管理節點（1個）、SQL節點（應用和資料節點間的橋樑）、資料節點（存放資料，有多個鏡像節點應對宕機）
• MMM架構：
  • 雙主複製架構，只有一個主提供寫，另一個提供一部分讀。
• MHA架構：
  • 兩部分組成：MHA Manager管理節點、MHA 

Mysql最佳化相關總結