MySQL-日志-優化

介紹一下mysql 的日志

redo log 和binlog 的區別及應用場景

redo log 和 binlog 在恢復數據庫有什么區別?

redo log 是怎么實現持久化的?

redo log除了崩潰恢復還有什么其他作用? （順序寫）

redo log 怎么刷入磁盤的知道嗎？

兩階段提交的過程

為什么需要兩階段提交?(兩個狀態保證邏輯上的一致) 先備份再恢復（老數據）先恢復再備份（備份數據不是最新）

SQL 優化

怎樣查看一條語句是否走了索引？

explain的結果有哪些？有哪些信息去告訴你怎么優化？

慢 SQL

如何定位呢？

慢sql日志怎么開啟？

有哪些方式優化 SQL？

如何優化慢 SQL? （優化數據查詢、拆分查詢、聯合索引進行覆蓋查詢、避免索引失效、對排序進行優化（相應字段建立索引））

深分頁場景如何優化？

如果 SQL 和索引都沒問題，查詢還是很慢怎么辦?

介紹一下mysql 的日志

undo log（回滾日志）：是 Innodb 存儲引擎層生成的日志，實現了事務中的原子性，主要用于事務回滾和 MVCC。
redo log（重做日志）：是 Innodb 存儲引擎層生成的日志，實現了事務中的持久性，主要用于掉電等故障恢復；
binlog （歸檔日志）：是 Server 層生成的日志，主要用于數據備份和主從復制；
慢查詢日志（Slow Query Log）：記錄執行時間超過 long_query_time 值的所有 SQL 語句。這個時間值是可配置的，默認情況下，慢查詢日志功能是關閉的。可以用來識別和優化慢 SQL。

----------------------------------------------------------------------------------------

錯誤日志（Error Log）：記錄 MySQL 服務器啟動、運行或停止時出現的問題。

一般查詢日志（General Query Log）：記錄所有 MySQL 服務器的連接信息及所有的 SQL 語句，不論這些語句是否修改了數據。

redo log 和binlog 的區別及應用場景

redo log 是 InnoDB 引擎實現的日志，屬于物理日志，記錄了 Innodb 引擎對數據頁所做的修改操作，主要用于崩潰恢復，比如某個事務提交了，臟頁數據還沒有刷盤，如果 MySQL機器斷電了，臟頁的數據就丟失了，MySQL重啟后可以通過重做日志，可以將已提交事務的數據恢復回來
binlog 是 server 層實現的日志，對所有存儲引擎都可用，保存了所有對數據庫的增刪改操作，以及每個語句的執行時間，主要用于數據庫備份和歸檔，也用于主從復制。

binlog 有三種日志格式，日志的內容可能是 SQL 語句、數據本身或兩者的混合+

redo log 和 binlog 在恢復數據庫有什么區別?

redo log 是固定大小的，通常配置為一組文件，使用環形方式寫入，舊的日志會在空間需要時被覆蓋。

binlog 是追加寫入的，新的事件總是被添加到當前日志文件的末尾，當文件達到一定大小后，會創建新的 binlog 文件繼續記錄。

redo log 是怎么實現持久化的?

Redolog是MySQL中用于保證持久性的重要機制之一。它通過以下方式來保證持久性：

Write-ahead logging（WAL）：在事務提交之前，將事務所做的修改操作記錄到redo log中，然后再將臟頁中數據寫入磁盤。這樣即使在數據寫入磁盤之前發生了宕機，系統可以通過redo log中的記錄來恢復數據。
Redo log的順序寫入：redo log采用追加寫入的方式，將redo日志記錄追加到文件末尾，而不是隨機寫入。這樣可以減少磁盤的隨機I/O操作，提高寫入性能。
Checkpoint機制：MySQL會定期將內存中的數據刷新到磁盤，同時將最新的LSN（Log Sequence Number）記錄到磁盤中，這個LSN可以確保redo log中的操作是按順序執行的。在恢復數據時，系統會根據LSN來確定從哪個位置開始應用redo log。（環形寫入）

redo log除了崩潰恢復還有什么其他作用? （順序寫）

Redolog 日志是追加的形式，所以redolog 寫磁盤是一個順序寫的過程，而數據頁寫磁盤是一個隨機寫的過程,順序寫的性能是比隨機寫性能高的，事務在提交的時候，是先寫日志再寫數據的機制，相當于把 MySQL寫入磁盤的操作從磁盤隨機寫成了順序寫，所以 redo Log 還可以起到提升 MySQL寫入磁盤性能的作用。

redo log 怎么刷入磁盤的知道嗎？

redo log 的寫入不是直接落到磁盤，而是在內存中設置了一片稱之為redo log buffer的連續內存空間，也就是redo 日志緩沖區。 16mb

log buffer 空間不足時

如果當前寫入 log buffer 的 redo 日志量已經占滿了 log buffer 總容量的大約一半左右，就需要把這些日志刷新到磁盤

事務提交時

在事務提交時，為了保證持久性，會把 log buffer 中的日志全部刷到磁盤。注意，這時候，除了本事務的，可能還會刷入其它事務的日志。

后臺線程輸入

有一個后臺線程，大約每秒都會刷新一次log buffer中的redo log到磁盤。

正常關閉服務器時
觸發 checkpoint 規則：環形寫入寫入方式是從頭到尾開始寫，寫到末尾又回到開頭循環寫

其中有兩個標記位置：

write pos是當前記錄的位置，一邊寫一邊后移，寫到第 3 號文件末尾后就回到 0 號文件開頭。checkpoint是當前要擦除的位置，也是往后推移并且循環的，擦除記錄前要把記錄更新到磁盤。

當write_pos追上checkpoint時，表示 redo log 日志已經寫滿。這時候就不能接著往里寫數據了，需要執行checkpoint規則騰出可寫空間。

所謂的checkpoint 規則，就是 checkpoint 觸發后，將 buffer 中日志頁都刷到磁盤。

兩階段提交的過程

兩階段提交把事務的提交拆分成了 2 個階段，分別是準備階段和提交階段

準備階段會將 redo log 狀態設置為 prepare 狀態，然后將 redo log 刷入磁盤;
提交階段會將 binlog 刷入磁盤，然后設置 redo log 設置為 commit 狀態，

到這里兩階段就已經完成了

在兩階段提交中，是以 binlog 刷入磁盤時機作為事務提交成功的標志的:

如果 binlog 還沒刷入磁盤的時候，MySQL就發生了崩潰，MySQL重啟的時候就需要回滾事務；
如果 binlog 刷入磁盤，即使 redo log 沒有設置 commit 狀態，MySQL就發生了崩潰，MySqL重啟的時候就會提交事務。

為什么需要兩階段提交?(兩個狀態保證邏輯上的一致) 先備份再恢復（老數據）先恢復再備份（備份數據不是最新）

兩階段提交是為了保證 redo log 和 binlog 邏輯一致，從而保證主從復制的時候不會出現數據不一致的問題。

事務提交后，redo log 和 binlog 都要持久化到磁盤，但是這兩個是獨立的邏輯，可能出現半成功的狀態，比如在主從復制的場景下，如果在將 redo log 刷入到磁盤之后，MySQL突然宕機了，而 binlog 還沒有來得及寫入磁盤，這時候主庫是最新的數據，而從庫是舊數據，這樣就造成兩份日志之間的邏輯不一致。

SQL 優化

怎樣查看一條語句是否走了索引？

可以通過 explain 查看 SQL 的執行計劃，關注 type 字段，這個字段表明 SQL 掃描的方式

如果 type字段不是 all 或者 index(全索引掃描) 就代表是索引掃描的方式，這種情況就代表 SQL 走了索引，并且我們還可以通過 key 字段，看這條查詢用了哪個索引字段來走索引，如果 key 為 null，也代表沒有走索引。

explain的結果有哪些？有哪些信息去告訴你怎么優化？

explain 是 MySQL 提供的一個用于查看查詢執行計劃的工具，可以幫助我們分析查詢語句的性能瓶頸，找出慢 SQL 的原因。

對于執行計劃，參數有：

possible_keys 字段表示可能用到的索引；
key 字段表示實際用的索引，如果這一項為 NULL，說明沒有使用索引；
key_len 表示索引的長度；
rows 表示掃描的數據行數。
type 表示數據掃描類型，表示 MySQL 在表中找到所需行的方式，性能從最優到最差分別為：system > const > eq_ref > ref > range > index > ALL。

type 字段就是描述了找到所需數據時使用的掃描方式是什么，常見掃描類型的執行效率從低到高的順序為：

All（全表掃描）；all 是最壞的情況，因為采用了全表掃描的方式。
index（全索引掃描）；index 和 all 差不多，只不過 index 對索引表進行全掃描，這樣做的好處是不再需要對數據進行排序，但是開銷依然很大。所以，要盡量避免全表掃描和全索引掃描。
range（索引范圍掃描）：range 表示采用了索引范圍掃描，一般在 where 子句中使用 < 、>、in、between 等關鍵詞，只檢索給定范圍的行，屬于范圍查找。

從這一級別開始，索引的作用會越來越明顯，因此我們需要盡量讓 SQL 查詢可以使用到 range 這一級別及以上的 type 訪問方式。

ref（非唯一索引掃描）：ref 類型表示采用了非唯一索引，或者是唯一索引的非唯一性前綴，返回數據返回可能是多條。因為雖然使用了索引，但該索引列的值并不唯一，有重復。這樣即使使用索引快速查找到了第一條數據，仍然不能停止，要進行目標值附近的小范圍掃描。但它的好處是它并不需要掃全表，因為索引是有序的，即便有重復值，也是在一個非常小的范圍內掃描。
eq_ref（唯一索引掃描）：eq_ref 類型是使用主鍵或唯一索引時產生的訪問方式，通常使用在多表聯查中。比如，對兩張表進行聯查，關聯條件是兩張表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 進行執行計劃查看的時候，type 就會顯示 eq_ref。
const（結果只有一條的主鍵或唯一索引掃描）：const 類型表示使用了主鍵或者唯一索引與常量值進行比較，比如 select name from product where id=1。

除了關注 type，我們也要關注 extra 顯示的結果。

這里說幾個重要的參考指標：

Using filesort ：當查詢語句中包含 group by 操作，而且無法利用索引完成排序操作的時候，這時不得不選擇相應的排序算法進行，甚至可能會通過文件排序，效率是很低的，所以要避免這種問題的出現。
Using temporary：使用臨時表保存中間結果，MySQL 在對查詢結果排序時使用臨時表，常見于排序 order by 和分組查詢 group by。效率低，要避免這種問題的出現。
Using index：所需數據只需在索引即可全部獲得，不須要再到表中取數據，也就是使用了覆蓋索引，避免了回表操作，效率不錯。
using where 表示MySQL的存儲引擎返回給 server 層的數據并不一定滿足 where 子句的條件，所以MySQL從存儲引擎拿到的數據，還得在 server 層進行了where 子句的條件判斷，來過濾出最終 sql 所需要查詢的數據

慢 SQL

如何定位呢？

定位慢 SQL 主要通過兩種手段：

慢查詢日志：開啟 MySQL 慢查詢日志，再通過一些工具比如 mysqldumpslow 去分析對應的慢查詢日志，找出問題的根源。
服務監控：可以在業務的基建中加入對慢 SQL 的監控，常見的方案有字節碼插樁、連接池擴展、ORM 框架過程，對服務運行中的慢 SQL 進行監控和告警。

show variables like '%slow_query_log';show variables like 'long_query_time' 默認是10sshow processlist; 
查看當前正在執行的 SQL 語句，找出執行時間較長的 SQL。

可以開啟慢查詢日志，MySQL就會自動將執行比較慢的 SQL 語句記錄在慢查詢日志中(默認是10s)，具體多慢我們可以自己設置的，比如設置 3 秒，那么 MySQL就會將執行超過 3 秒的 SQL 語句記錄在慢查詢日志中。

SQL 執行過程中，優化器通過成本計算預估出執行效率最高的方式，基本的預估維度為：

IO 成本：從磁盤讀取數據到內存的開銷。
CPU 成本：CPU 處理內存中數據的開銷。

基于這兩個維度，可以得出影響 SQL 執行效率的因素有：

①、IO 成本

數據量：數據量越大，IO 成本越高。所以要避免 select *；盡量分頁查詢。
數據從哪讀取：盡量通過索引加快查詢。

②、CPU 成本

盡量避免復雜的查詢條件，如有必要，考慮對子查詢結果進行過濾。
盡量縮減計算成本，比如說為排序字段加上索引，提高排序效率；比如說使用 union all 替代 union，減少去重處理。

慢sql日志怎么開啟？

慢 SQL 日志的開啟方式有多種，

直接編輯 MySQL 的配置文件 my.cnf 或 my.ini，設置 slow_query_log 參數為 1，設置 slow_query_log_file 參數為慢查詢日志的路徑，設置 long_query_time 參數為慢查詢的時間閾值。然后重啟 MySQL 服務就好了

通過 set global 命令動態設置。

有哪些方式優化 SQL？

通過 explain 查看查詢執行計劃，查看執行結果，查看 sql 是否走索引，如果不走索引，考慮增加索引。
可以通過建立聯合索引，實現覆蓋索引優化，減少回表
聯合索引符合最左匹配原則，不然會索引失效
避免索引失效，比如不要用左模糊匹配、函數計算、表達式計算、避免使用 != 或者 <> 操作符等等。
聯表查詢最好要以小表驅動大表，并且被驅動表的字段要有索引，當然最好通過冗余字段的設計，避免聯表查詢。
針對 limit n,y 深分頁的查詢優化，可以把Limit查詢轉換成某個位置的查詢：select * from tb_sku where id>20000 limit 10;，該方案適用于主鍵自增的表，數據庫需要掃描OFFSET + LIMIT數量的行。
深分頁優化可以使用子查詢， 從多個表中獲取數據且主表行數較多的情況。它首先從索引表中檢索出需要的行 ID，然后再根據這些 ID 去關聯其他的表獲取詳細信息。
將字段多的表分解成多個表，有些字段使用頻率高，有些低，數據量大時，會由于使用頻率低的存在而變慢，可以考慮分開

如何優化慢 SQL? （優化數據查詢、拆分查詢、聯合索引進行覆蓋查詢、避免索引失效、對排序進行優化（相應字段建立索引））

常見 SQL 優化的方法:

優化數據訪問: limit 子句縮減數據行數、避免 select *
拆分查詢:分而治之的思想，將一個大查詢拆分多個小查詢，每個小查詢只返回一部分查詢結果。
覆蓋索引: 當索引中的列包含所有查詢中需要使用的列的時候，可以避免回表
避免索引失效: 檢查 Sql 是否因為寫的不合理，導致索引失效
分解聯表查詢: 讓業務層分多個查詢來聚合，或者增加冗余字段減少聯表查詢
排序優化:對于有排序場景，如果 extra 顯示 filesort，這時候就需要考慮對排序的字段建立索引，避免文件排序

回答

優化數據訪問: 要先確認這條查詢語句是否查詢了不必要的數據行，可以通過 limit 子句來縮減查詢返回的數據行數，如果查詢語句用了 select *，需要改進 SQL 語句，只返回需要查詢的列。
切分查詢:針對一個大查詢可以拆分多個小查詢，每個小查詢只返回一部分查詢數據。比如刪除一千萬行數據可以改進成分批刪除，每一次只刪除一批數據，然后睡眠一下，再刪除下一批，這樣可以將一次性的壓力分散到個很長的時間段中，不僅可以降低對服務器的性能影響，還可以大大減少刪除時鎖的持續時間。
覆蓋索引:如果沒有索引字段的話，就需要考慮建立索引，或者建立聯合索引，通過覆蓋索引的查詢，這樣就避免回表查詢，可以提高查詢性能。
避免索引失效:檢查 SQL 語句有沒有問題，比如對索引進行了計算和函數操作、聯合索引沒有遵循最左匹配原則等，這些場景都會導致索引失效，這時候就需要修改 SQL 避免索引失效的發生。
分解聯表查詢:針對聯表查詢的 SQL 語句，可以將聯表查詢分解成多個單表查詢的語句，然后在業務層來聚合數據，或者增加冗余字段減少聯表查詢。
排序優化: 針對 order by 排序操作，如果執行計劃的 extra 顯示了文件排序，這時候我們可以對排序字段和其他字段建立聯合索引，因為索引數據是天然有序的，這樣對索引字段進行排序操作的時候，就不需要文件排序了，提高了查詢性能。