一、定位慢查詢

定位慢查詢很簡單，主要有以下方式：

1. 通過運維工具分析
2. 開啟mysql慢日志

在mysql的配置文件/etc/my.cnf中配置并開啟慢日志功能

# 開啟MySQL慢日志查詢開關
slow_query_log=1
# 設置慢日志的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日志
long_query_time=2

• 分析某一條具體的sql語句

EXPLAIN/SELECT   字段列表   FROM   表名   WHERE  條件 ;

執行結果：

通過key和key_len檢查是否命中了索引（索引本身存在是否有失效的情況）

通過type字段查看sql是否有進一步的優化空間，是否存在全索引掃描或全盤掃描

通過extra建議判斷，是否出現了回表的情況，如果出現了，可以嘗試添加索引或修改返回字段來修復

索引

• 索引是幫助MySQL高效查找數據的滿足特定查找算法的數據結構
• 索引的底層數據結構是B+樹

聚集索引和二級索引

聚集索引選取規則：

• 當存在主鍵或唯一索引時，優先使用主鍵或第一個唯一索引作為聚集索引
• 如果主鍵和唯一索引都沒有，InnoDB會自動生成一個rowid作為隱藏的聚集索引

回表查詢：

通過二級索引查找到對應主鍵值，然后再根據主鍵值查詢整行數據，這個過程叫做回表

覆蓋索引:

指查詢使用了索引，并且所有需返回的字段全部能在該索引中直接找到

聯合索引：

指由多個字段共同構成的索引

索引失效情況

最左前綴匹配法則：

創建了聯合索引(a,b,c)后，只有當前面的字段相同時，才會去比較后面的字段，優先按照聯合索引最左字段進行匹配

索引失效情況：

① 如果不遵循最左匹配原則，則可能會導致索引失效

• where b='' c='': 此時索引全部失效
• where a='' c='':此時僅有索引a生效

② 范圍查詢右邊的列，不能使用索引

• where a='' b>'': 此時c索引失效

③ 在索引字段上進行運算，索引也會失效

④ 字符串不加單引號，索引也會失效(造成類型轉換)

⑤ 模糊查詢%xxx模式，也會導致索引失效

SQL優化經驗

1.表的設計優化，數據類型的選擇

2.索引優化，索引創建原則

3.sql語句優化，避免索引失效，避免使用select * ….

4.主從復制、讀寫分離，不讓數據的寫入，影響讀操作

5.分庫分表

事務

• 并發事務可能出現的問題

1. 臟讀
2. 不可重復讀
3. 幻讀

• 解決方案 —— 事務的隔離級別

1. 讀未提交
2. 讀已提交：只能解決臟讀
3. 可重復讀：只能解決臟讀、不可重復讀
4. 串行化：全部解決

MySQL日志

• redo log：記錄的是數據頁的物理變化，當服務器宕機或其他情況導致數據不一致時，可用來同步數據，保證了事務的持久性
• undo log：記錄的是邏輯日志，當事務回滾時，通過逆操作來恢復數據，保證了事務的原子性和一致性

保證事務的隔離性

1. 排他鎖：用于數據修改操作，確保不會同時同一資源進行多重更新
2. MVCC(多版本并發控制)：指維護一個數據的多個版本，使得讀寫操作沒有沖突

• 隱式字段

①trx_id(事務id)，記錄每一次操作的事務id，是自增的

②roll_pointer(回滾指針)，指向上一個版本的事務版本記錄地址

• undo log

①回滾日志，存儲老版本數據

②版本鏈：多個事務并行操作某一行記錄，記錄不同事務修改數據的版本，通過roll_pointer指針形成一個鏈表

• readview

  • 根據readView的匹配規則和當前的一些事務id判斷該訪問那個版本的數據

  • 不同的隔離級別快照讀是不一樣的，最終的訪問的結果不一樣

    RC ：每一次執行快照讀時生成ReadView

    RR：僅在事務中第一次執行快照讀時生成ReadView，后續復用

數據庫主從同步原理

①主庫在事務提交時，會把數據變更記錄在二進制日志文件 Binlog 中。

②從庫讀取主庫的二進制日志文件 Binlog ，寫入到從庫的中繼日志 Relay Log 。

③從庫重做中繼日志中的事件，將改變反映它自己的數據

分庫分表

• 水平分庫
• 水平分表
• 垂直分庫
• 垂直分表