## MySQL服務器構成 ### 1.1 客戶端與服務器端模型 MySQL是一個典型的C/S模式，單進程多線程的服務結構。MySQL自帶的客戶端程序在/usr/local/mysql/bin下，如：mysql、 mysqladmin、mysqldump等；服務端程序是mysqld（即守護進程，二進制的程序）  ### 1.2 應用程序連接MySQL的方式 #### 1.2.1 TCP/IP方式 - 通過網絡連接串： ```text [root@db02 ~]# mysql -uroot -p12345678 -h 127.0.0.1 ```  #### 1.2.2 Socket方式 - 通過套接字文件： ```text [root@db02 ~]# mysql -uroot -p12345678 -S /usr/local/mysql/tmp/mysql.sock ```  > 提示：服務器默認使用Socket方式連接數據庫。 ### 1.3 實例介紹 #### 1.3.1 什么是實例 MySQL在啟動過程中會啟動后臺守護進程，并生成工作線程，預分配內存結構供MySQL處理數據使用，這些MySQL的后臺進程+線程+預分配的內存結構就是實例。  ## MySQL的邏輯結構 ### 2.1 mysqld服務器程序構成 msyqld服務程序分為三層，分別為連接層、SQL層、存儲引擎層：  #### 2.1.1 連接層 所包含的服務并不是MySQL所獨有的技術。它們都是服務于C/S程序或者是這些程序所需要的 ：連接處理，身份驗證，安全性等等：  #### 2.1.2 SQL層 這是MySQL的核心部分，通常叫做 SQL Layer。在 MySQL據庫系統處理底層數據之前的所有工作都是在這一層完成的，包括權限判斷，sql解析，行計劃優化，query cache的處理以及所有內置的函數(如日期、時間、數學運算、加密)等等。各個存儲引擎提供的功能都集中在這一層，如存儲過程，觸發器，視圖等：  - SQL處理流程圖：  #### 2.1.3 存儲引擎層 通常叫做StorEngine Layer，也就是底層數據存取操作實現部分，由多種存儲引擎共同組成。它們負責存儲和獲取所有存儲在MySQL中的數據。就像Linux眾多的文件系統一樣。每個存儲引擎都有自己的優點和缺陷。服務器是通過存儲引擎API來與它們交互的。這個接口隱藏了各個存儲引擎不同的地方，對于查詢層盡可能的透明。這個API包含了很多底層的操作，如開始一個事務，或者取出有特定主鍵的行。存儲引擎不能解析SQL，互相之間也不能通信，僅僅是簡單的響應服務器 的請求：  ### 2.2 SQL的整個處理過程  #### 2.2.1 Connectors 指的是不同語言中與SQL的交互。 #### 2.2.2 Management Serveices & Utilities： 系統管理和控制工具。 #### 2.2.3 Connection Pool: 連接池 管理緩沖用戶連接，線程處理等需要緩存的需求。負責監聽對 MySQL Server 的各種請求，接收連接請求，轉發所有連接請求到線程管理模塊。每一個連接上 MySQL Server 的客戶端請求都會被分配（或創建）一個連接線程為其單獨服務。而連接線程的主要工作就是負責 MySQL Server 與客戶端的通信，接受客戶端的命令請求，傳遞 Server 端的結果信息等。線程管理模塊則負責管理維護這些連接線程。包括線程的創建，線程的 cache 等。 #### 2.2.4 SQL Interface: SQL接口 接受用戶的SQL命令，并且返回用戶需要查詢的結果。比如select from就是調用SQL Interface。 #### 2.2.5 Parser: 解析器 SQL命令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實現的，是一個很長的腳本。 在 MySQL中我們習慣將所有 Client 端發送給 Server 端的命令都稱為 query ，在 MySQL Server 里面，連接線程接收到客戶端的一個 Query 后，會直接將該 query 傳遞給專門負責將各種 Query 進行分類然后轉發給各個對應的處理模塊。 - 主要功能： 將SQL語句進行語義和語法的分析，分解成數據結構，然后按照不同的操作類型進行分類，然后做出針對性的轉發到后續步驟，以后SQL語句的傳遞和處理就是基于這個結構的。 如果在分解構成中遇到錯誤，那么就說明這個sql語句是不合理的 #### 2.2.6 Optimizer: 查詢優化器 SQL語句在查詢之前會使用查詢優化器對查詢進行優化。就是優化客戶端請求的 query（sql語句） ，根據客戶端請求的 query 語句，和數據庫中的一些統計信息，在一系列算法的基礎上進行分析，得出一個最優的策略，告訴后面的程序如何取得這個 query 語句的結果，他使用的是“選取-投影-聯接”策略進行查詢，用一個例子就可以理解： ```sql select uid,name from user where gender = 1; ``` - 解析方式： 這個select 查詢先根據where 語句進行選取，而不是先將表全部查詢出來以后再進行gender過濾 這個select查詢先根據uid和name進行屬性投影，而不是將屬性全部取出以后再進行過濾 將這兩個查詢條件聯接起來生成最終查詢結果 #### 2.2.7 Cache和Buffer：查詢緩存 他的主要功能是將客戶端提交 給MySQL 的Select類query請求的返回結果集cache到內存中，與該query的一個hash值做一個對應。該Query所取數據的基表發生任何數據的變化之后，MySQL會自動使該query的Cache失效。在讀寫比例非常高的應用系統中，Query Cache對性能的提高是非常顯著的。當然它對內存的消耗也是非常大的。 如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據。這個緩存機制是由一系列小緩存組成的：比如表緩存、記錄緩存、key緩存、權限緩存等 #### 2.2.8 存儲引擎接口 存儲引擎接口模塊可以說是MySQL數據庫中最有特色的一點了。目前各種數據庫產品中，基本上只有MySQL可以實現其底層數據存儲引擎的插件式管理。這個模塊實際上只是一個抽象類，但正是因為它成功地將各種數據處理高度抽象化，才成就了今天MySQL可插拔存儲引擎的特色。 從圖上還可以看出，MySQL區別于其他數據庫的最重要的特點就是其插件式的表存儲引擎。MySQL插件式的存儲引擎架構提供了一系列標準的管理和服務支持，這些標準與存儲引擎本身無關，可能是每個數據庫系統本身都必需的，如SQL分析器和優化器等，而存儲引擎是底層物理結構的實現，每個存儲引擎開發者都可以按照自己的意愿來進行開發。 注意：存儲引擎是基于表的，而不是數據庫。 ### 2.3 存儲引擎概覽 存儲引擎是充當不同表類型的處理程序的服務器組件。依賴于存儲引擎的功能 #### 2.3.1 存儲引擎用于 - 存儲數據 - 檢索數據 - 通過索引查找數據 #### 2.3.2 雙層處理 - 上層包括SQL解析器和優化器 - 下層包含一組存儲引擎 #### 2.3.3 SQL層不依賴于存儲引擎 - 引擎不影響SQL處理 - 有一些例外 #### 2.3.4 依賴于存儲引擎的功能 - 存儲介質 - 事務功能 - 鎖定 - 備份和恢復 - 優化 - 特殊功能：如全文搜索、引用完整性、空間數據處理等 ## MySQL“庫”的構成  ### 3.1 數據庫的邏輯結構 #### 3.1.1 庫 ```sql show databases use mysql ``` #### 3.1.2 表 ```sql show tables; ``` #### 3.1.3 記錄（行、列） ```sql select user,host,password from user; desc user ``` ### 3.2 數據庫的物理結構 對象存儲中的庫相當于目錄。表分為MyIASM和InnoDB方式： - MyIASM方式： ```text [root@db02 ~]# ll -h /usr/local/mysql/data/mysql/user* -rw-rw---- 1 mysql mysql 11K Nov 13 11:54 /usr/local/mysql/data/mysql/user.frm # 存放索引 -rw-rw---- 1 mysql mysql 488 Nov 13 12:33 /usr/local/mysql/data/mysql/user.MYD # 存放列結構 -rw-rw---- 1 mysql mysql 2.0K Nov 13 12:33 /usr/local/mysql/data/mysql/user.MYI # 存放行結構 ``` InnoDB方式： 共享表空間：ibdata1:ibdata2 獨立表空間：t1.frm t1.ibd - 創建一個數據庫和表，查看一下獨立表空間存儲的不同 ```text mysql> create database leon; Query OK, 1 row affected (0.00 sec) mysql> use leon; Database changed mysql> create table t1 (id int); Query OK, 0 rows affected (0.63 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.01 sec) mysql> select id from t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) [root@db02 leon]# ll -h t1* -rw-rw---- 1 mysql mysql 8.4K Nov 14 10:21 t1.frm # 存儲表結構定義 -rw-rw---- 1 mysql mysql 96K Nov 14 10:21 t1.ibd # 存儲行、列結構 ``` ### 3.3 MySQL使用磁盤方式  程序文件隨數據目錄一起存儲在服務器安裝目錄下。執行各種客戶機程序、管理程序和實用程序時將創建程序可執行文件和日志文件。首要使用磁盤空間的是數據目錄。 - 服務器日志文件和狀態文件：包含有關服務器處理的語句的信息。日志可用于進行故障排除、監視、復制和恢復。 - InnoDB 日志文件：（適用于所有數據庫）駐留在數據目錄級別。 - nnoDB 系統表空間：包含數據字典、撤消日志和緩沖區。 每個數據庫在數據目錄下均具有單一目錄（無論在數據庫中創建何種類型的表）。數據庫目錄存儲以下內容： - 數據文件：特定于存儲引擎的數據文件。這些文件也可能包含元數據或索引信息，具體取決于所使用的存儲引擎。 - 格式文件 (.frm)：包含每個表和/或視圖結構的說明，位于相應的數據庫目錄中。 - 觸發器：某個表關聯并在該表發生特定事件時激活的命名數據庫對象。 數據目錄的位置取決于配置、操作系統、安裝包和分發。典型位置是 /var/lib/mysql。MySQL在磁盤上存儲系統數據庫 (mysql)。mysql 包含諸如用戶、特權、插件、幫助列表、事件、時區實現和存儲例程之類的信息。