Mysql官網

mysql安裝教程

Navicat免費安裝親測有用

簡介

大體來說，MySQL 服務端可以分為 Server 層和存儲引擎層兩部分。Server 層包括連接器、查詢緩存、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。

而存儲引擎層負責數據的存儲和檢索。其架構模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎。現在最常用的存儲引擎是 InnoDB，它從 MySQL5.5.5 版本開始成為了默認存儲引擎。也就是說，你執行 create table 建表的時候，如果不指定引擎類型，默認使用的就是InnoDB。不過，你也可以通過指定存儲引擎的類型來選擇別的引擎，比如在 create table語句中使用 engine=memory,來指定使用內存引擎創建表。

具體流程如下：

1. 當客戶端的 SQL 發送到 MySQL 時，首先是到達 Server 層的連接器，連接器會對你此次發起的連接進行權限校驗，以此來獲取你這個賬號擁有的權限。當你的賬號或密碼不正確時，會報 Access denied for user 錯誤，相信大家對這個錯誤也并不陌生。連接成功如果后續沒有任何操作，那么這個連接就處于空閑狀態，到達一定時間后它便會斷開連接，這個時間一般是 8 小時，是由 wait_timeout 參數控制的。

2. 查詢緩存（在 MySQL 8.0 之后就被砍了）具體做法就是將一個查詢語句作為 key，將上一次請求的結果作為 value，存儲在緩存組件中，當同樣的語句來查詢的時候即可立馬返回結果，不需要經歷詞法、語法分析等以下的步驟。只要表有數據改動緩存就失效了，在我們常見的聯機事務處理（OLTP）場景下是個雞肋。

3. 接下來就到了分析器來進行語法分析、詞法分析。MySQL 會首先對你的語句進行“詞法分析”，來判斷你的語句是什么類型以及攜帶什么參數等。比如：MySQL 會將輸入語句的 select 提取出來，判斷出這是一條查詢語句、將 from 后面的 user 提取出來作為查詢的表名、把 id 提取出來作為列名等。做完這些 MySQL 將會進行“語法分析”來判斷你的語句的語法是否有誤、是否滿足 MySQL 的語法。如果語法有問題，那這個錯誤相信大家都不陌生：You have an error in your SQL syntax; check the manual……

4. 經過分析器就到了優化器，它會對你的語句進行優化判斷。比如你的表中有多個索引，優化器會幫你選擇使用哪個索引、你使用了 join 多表連接，優化器會幫你調整表的連接順序。我們平日里用的 explain 其實就是讓 MySQL 告訴我們它的優化決定策略是怎樣的。
   優化器怎么選擇執行計劃

5. 最后會到達執行器，它先會判斷你對這個 user 表是否有權限查詢，如果沒有權限它將會拒絕本次查詢，返回錯誤信息。如果有權限，它將會根據表的存儲引擎提供的接口進行數據查詢將重復遍歷表的行數據，判斷 id 字段是否等于 1。直到遍歷完整個表將符合條件的數據作為結果集返回給客戶端(連接工具（Navacat、SQLyog、JDBC）都歸納為MySQL客戶端(Client)，主要用于發送執行sql語句的請求)。

Server 層

• 負責處理 SQL 語句、解析、優化、緩存等。
• 負責權限管理、用戶認證等。 提供了各種 SQL 函數和存儲過程。
• 提供了復制、備份、恢復等高級功能。
• Server 層有自己的日志系統，稱為 binlog（歸檔日志）。binlog 記錄了所有修改數據庫數據的 SQL語句（如INSERT、UPDATE、DELETE 等）的信息，但不包括 SELECT 和 SHOW 這類查詢語句。binlog主要用于復制和恢復操作。

binlog,redo log, undolog 區別

連接器

第一步，你會先連接到這個數據庫上，這時候接待你的就是連接器。連接器負責跟客戶端建立連接、獲取權限、維持和管理連接。連接命令一般是這么寫的：

mysql -h$ip -P$port -u$user -p

輸完命令之后，你就需要在交互對話里面輸入密碼。
連接命令中的 mysql 是客戶端工具，用來跟服務端建立連接。在完成經典的 TCP 握手后，連接器就要開始認證你的身份，這個時候用的就是你輸入的用戶名和密碼。

• 如果用戶名或密碼不對，你就會收到一個"Access denied for user"的錯誤，然后客戶端程序結束執行。
• 如果用戶名密碼認證通過，連接器會到權限表里面查出你擁有的權限。之后，這個連接里面的權限判斷邏輯，都將依賴于此時讀到的權限。
  

客戶端如果太長時間沒動靜，連接器就會自動將它斷開。這個時間是由參數 wait_timeout控制的，默認值是 8 小時。如果在連接被斷開之后，客戶端再次發送請求的話，就會收到一個錯誤提醒： Lost connection to MySQL server during query。這時候如果你要繼續，就需要重連，然后再執行請求了。

 # 查看數據庫的連接狀態show processlist;#查看當前的wait_timeout參數值SHOW VARIABLES LIKE 'wait_timeout';

注意：建立連接的過程通常是比較復雜的，建議在使用中要盡量減少建立連接的動作，盡量使用長連接。為了提升數據庫并發性，可以建立一個數據庫連接池。

長連接：連接成功后，如果客戶端持續有請求，則一直使用同一個連接。
短連接：每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個。

連接器常見的問題：
全部使用長連接后，有時候 MySQL 占用內存漲得特別快，因為 MySQL 在執行過程中臨時使用的內存是管理在連接對象里面的，這些資源會在連接斷開的時候才釋放，所以如果長連接累積下來，可能導致內存占用太大，被系統強行殺掉（OOM）。從現象看就是 MySQL 異常重啟了

解決方案：

• 定期斷開長連接。使用一段時間，或者程序里面判斷執行過一個占用內存的大查詢后，斷開連接，之后要查詢再 重連。
• MySQL 5.7 以上版本，可以在每次執行一個比較大的操作后，通過執行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做權限驗證，但是會將連接恢復到剛剛創建完時的狀態。

在Java 中與 MySQL 數據庫交互通常使用 JDBC (Java Database Connectivity) API，它提供了自己的連接管理和錯誤處理機制。請注意，頻繁地創建和關閉連接可能會對性能產生負面影響，特別是在高負載的情況下。因此，在生產環境中，通常會使用連接池來管理數據庫連接，這樣可以復用現有的連接而不是頻繁地創建和銷毀它們

查詢緩存

在MySQL5.7版本，連接完成后就會直接查詢緩存，查詢此語句是否執行過。執行邏輯就會來到第二步：查詢緩存。

MySQL 拿到一個查詢請求后，會先到查詢緩存看看，之前是不是執行過這條語句。之前執行過的語句。及其結果可能會以 key-value 對的形式，被直接緩存在內存中。key 是查詢的語句，value 是查詢的結果。如果你的查詢能夠直接在這個緩存中找到 key，那么這個value 就會被直接返回給客戶端。如果語句不在查詢緩存中，就會繼續后面的執行階段。執行完成后，執行結果會被存入查詢緩存中。你可以看到，如果查詢命中緩存，MySQL 不需要執行后面的復雜操作，就可以直接返回結果，這個效率會很高。

注意，MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了。
MySQL 為什么在 8.0 版本中移除了查詢緩存功能

分析器

若查詢緩存未命中，則會執行分析器，來分析查詢語句是否合法。
分析器先會做“詞法分析”。MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字符串“user”識別成“表名 user”，把字符串“id”識別成“列 id”。

詞法分析：

• 主要負責從 SQL 語句中提取關鍵字，比如：查詢的表，字段名，查詢條件等等。
• 詞法分析階段是從 information_schema 里面獲得table表的結構信息的
  
  做完了這些識別以后，就要做“語法分析”。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

語法分析：

• 判斷輸入的SQL 語句是否滿足 MySQL 語法
• 如果 SQL 語句不對，就會返回 You have an error in your SQL syntax的錯誤提醒，一般語法錯誤會提示第一個 出現錯誤的位置，所以你要關注的是緊接“use near”的內容
  

優化器

經過了分析器，若語句正確，就會進入優化器。優化器的作用是在基于同一個查詢語句的多個查詢方案中找出效率最高的。比如，在表里面有多個索引的時候，決定使用哪個索引；在一個語句有多表關聯（join）的時候，決定各個表的連接順序。

這里如果優化器發現語句用了聯合索引但是不符合最左前綴，會自動補齊在
如何選擇執行計劃

執行器

執行器根據優化器生成的計劃執行操作，同時與存儲引擎協作，但不直接負責數據存儲

當執行器需要執行一個SQL語句時，它會通過Handler接口調用存儲引擎的方法，如read_row()讀取數據、update_row()更新數據等。存儲引擎根據自身特性實現這些方法，完成具體的數據操作后，將結果返回給執行器。這種協作機制使得MySQL能夠靈活地支持多種存儲引擎。

開始執行的時候，要先判斷一下你對這個表有沒有執行查詢的權限，如果沒有，就會返回沒有權限的錯誤。如果有權限，就打開表繼續執行。這是一種安全機制，確保只有被授權的用戶才能訪問和操作數據。

注意：

• 如果命中查詢緩存，會在查詢緩存返回結果的時候，做權限驗證。
• 在語法分析過程中，解析器會進行一些初步的權限檢查 precheck，例如驗證用戶是否有權訪問指定的數據庫和 表。
• 有些時候，SQL語句要操作的表不只是SQL字面上那些。SQL執行過程中可能會有觸發器這種在運行時才能確定的過程，precheck是不能對這種運行時涉及到的表進行權限校驗的，所以需要在執行器階段進行權限檢查。

打開表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的接口：

1.調用引擎接口取這個表的第一行，判斷是否滿足條件，如果不是則跳過，如果是則將這行存在結果集中
2.調用引擎接口取下一行，重復相同的判斷邏輯，直到取到這個表的最后一行
3.執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端

至此，這個語句就執行完成了

存儲引擎層

• 負責數據的存儲和檢索。
• MySQL 支持多種存儲引擎，如 InnoDB、MyISAM、Memory 等，每種引擎都有其特點和適用場景。
• InnoDB 是 MySQL 的默認存儲引擎，它支持事務、行級鎖定和外鍵約束。InnoDB 有自己的日志系統，稱 為 redo log（重做日志） 和 undo log（撤銷日志）。redo log 用于保證事務的持久性，在數據庫崩潰后可以用來恢復數據；undo log 用于支持事務的原子性和多版本并發控制（MVCC）。

更新語句是怎么執行的

如果是走update的流程，在查詢緩存的流程上，在一個表上有更新的時候，跟這個表有關的查詢緩存會失效，會把所有緩存結果都清空。這也就是我們一般不建議使用查詢緩存的原因

更新涉及到了redo和undo log，這是在執行引擎做的

例子

mysql> update T set c=c+1 where ID=2;

1.執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果ID=2 這一行所在的數據頁本來就在內存中，就直接返回給執行器；否則，需要先從磁盤讀入內存，然后再返回。
2.執行器拿到引擎給的行數據，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行數據，再調用引擎接口寫入這行新數據。
3.引擎將這行新數據更新到內存中，同時將這個更新操作記錄到 redo log 里面，此時redo log 處于 prepare 狀態。
然后告知執行器執行完成了，隨時可以提交事務。
4.執行器生成這個操作的 binlog，并把 binlog 寫入磁盤。
5.執行器調用引擎的提交事務接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。

日志

redo log

MySQL 里經常說到的 WAL 技術（預寫式日志），WAL 的全稱是 Write-Ahead Logging，它的關鍵點就是先寫日志，再寫磁盤。

具體來說，當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log里面，并更新內存，這個時候更新就算完成了。同時，InnoDB 引擎會在適當的時候，將這個操作記錄更新到磁盤里面，而這個更新往往是在系統比較空閑的時候做

InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個文件，每個文件的大小是1GB，那么這塊“粉板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭循環寫

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

write pos 和 checkpoint 之間的是空著的部分，可以用來記錄新的操作。如果 write pos 追上checkpoint，表示滿了，這時候不能再執行新的更新，刷到磁盤，然后把checkpoint推進一下。

有了 redo log，InnoDB 就可以保證即使數據庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。

下圖時候存儲文件夾的位置

binlog

在server層的
這兩種日志有以下三點不同。

1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。
2.redo log 是物理日志，記錄的是“在某個數據頁上做了什么修改”；binlog 是邏輯日志，記錄的是這個語句的原始邏
輯，比如“給 ID=2 這一行的 c 字段加 1 ”。redo log 是從數據頁的角度來看的，它關心的是數據在磁盤上的物理布局和如何高效地修改這些數據。binlog 是從 SQL 語句的角度來看的，它關心的是執行了哪些操作以及這些操作的內容。
3.redo log 是循環寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指binlog 文件寫到一定大小后會切
換到下一個，并不會覆蓋以前的日志

日志	binlog	redo log	undo log
作用層級	Server層	InnoDB存儲引擎層	InnoDB存儲引擎層
作用	支持備份恢復和主從復制，記錄所有數據變更操作。	保證事務的持久性（Crash-Safe），支持故障恢復。	支持事務的原子性和多版本并發控制（MVCC）。
記錄內容	記錄邏輯操作（如 SQL 語句或行數據的變化）	記錄物理修改（數據頁的具體更改）	記錄事務修改前的數據，用于回滾和 MVCC
寫入方式	追加寫入：文件寫滿后創建新文件，不覆蓋舊日志。	循環寫入：固定大小，寫滿后從頭開始覆蓋。	隨事務變化按需生成，形成版本鏈。
主要用途	數據恢復到指定時間點；主從復制同步。	宕機后恢復已提交的事務，保證數據一致性。	支持事務回滾；基于 MVCC 實現快照讀和隔離性。

mysql事務的二段提交

二段提交