MySQL的架構

一、MySQL簡介

• 資料庫是一個以某種有組織的方式儲存的資料集合，它是一個按資料結構來儲存和管理資料的計算機軟體系統。理解資料庫的一種最簡單的辦法是將其想象為一個檔案櫃，這個檔案櫃只是一個儲存資料的空間，而資料庫就是這麼一個空間。
• 資料庫管理系統（Database Management System, DBMS）是用於建立、管理、和維護資料庫時所使用的軟體，用以管理資料庫，一定程度上，使用者都是在操作DBMS，所以日常的資料庫概念一般就是指DBMS。
• MySQL是一種DBMS，而且是一種一個小型的開源的關係型資料庫管理系統。2008年1月16日MySQL AB被Sun公司收購。而2009年，SUN又被Oracle收購。就這樣如同一個輪迴，MySQL成為了Oracle公司的另一個資料庫專案。
• MySQL支援大型資料庫，支援5000萬條記錄的資料倉儲，32位元系統表檔案最大可支援4GB，64位元系統支援最大的表檔案為8TB。

二、MySQL使用者組管理

1，使用者管理相關命令

　　a)建立使用者

#建立使用者:zs  密碼:123456
create user zs identified by '123456';

　　b)檢視使用者許可權

select host, user, password, select_priv, insert_priv,drop_priv from mysql.user;
host :表示連線型別
　　% 表示所有遠端通過 TCP 方式的連線
　　IP 地址 如 (192.168.1.2,127.0.0.1) 通過制定 ip 地址進行的 TCP 方式的連線
　　機器名 通過制定 i 網路中的機器名進行的 TCP 方式的連線
　　::1 IPv6 的本地 ip 地址 等同於 IPv4 的 127.0.0.1
　　localhost 本地方式通過命令列方式的連線 ， 比如 mysql -u xxx -p 123xxx 方式的連線。
user:表示使用者名稱
　　同一使用者通過不同方式連結的許可權是不一樣的。
password:密碼
　　所有密碼串通過 password(明文字串) 生成的密文字串。 加密演演算法為 MYSQLSHA1 ， 不可逆 。
　　mysql 5.7 的密碼儲存到 authentication_string 欄位中不再使用 password 欄位。
select_priv , insert_priv 等
　　為該使用者所擁有的許可權。

　　c)修改密碼

#MySQL5.6使用
#基本使用 修改lisi密碼為123456
update mysql.user set password=password('123456') where user='lisi';
#修改當前使用者
set password =password('123456');
#重新整理許可權生效
flush privileges;

#MySQL5.7推薦使用
#基本使用 修改testuser使用者密碼為123456
ALTER USER testuser IDENTIFIED BY '123456';
#修改當前登入使用者 密碼為123456
ALTER USER USER() IDENTIFIED BY '123456';

　　d)修改使用者名稱

update mysql.user set user='lisi' where user='wangwu';
flush privileges;

　　e)刪除使用者

drop user lisi

2，MySQL的許可權管理

　　a)授予許可權

#grant 許可權 1,許可權 2,…許可權 n on 資料庫名稱.表名稱 to 使用者名稱@使用者地址 identified by '連線口令'
#給 lisi 使用者用本地命令列方式下， 授予 test這個庫下的所有 表的插刪改查的許可權。
grant select,insert,delete,drop on test.* to lisi@'%' ;
#授予通過網路方式登入的的 jack 使用者，對所有庫所有表的全部權 限， 密碼設為 123
grant all privileges on *.* to jack@'%' identified by '123';

　　b)收回許可權

#檢視當前使用者許可權
show grants;
#收回許可權
#revoke [許可權 1,許可權 2,…許可權 n] on 庫名.表名 from 使用者名稱@使用者地址;
#收回全庫全表的所有許可權
REVOKE ALL PRIVILEGES ON *.* FROM jack@'%';
#收回 mysql 庫下的所有表的插刪改查 許可權
REVOKE select,insert,update,delete ON mysql.* FROM jack@'%';

三、MySQL組態檔

• 二進位制紀錄檔檔案 log-bin：用於主從複製
• 錯誤紀錄檔log-error：預設關閉，記錄嚴重的警告和錯誤資訊，每次啟動和關閉的詳細資訊等
• 查詢紀錄檔log：預設關閉，記錄查詢的sql語句，如果開啟會降低mysql的整體效能，因為記錄紀錄檔也是需要消耗系統資源的
• 資料檔案
  • 資料庫檔案儲存：預設為 /var/lib/mysql
  • frm檔案：存放表結構(都存在)
  • idb檔案：存放表的資料(InnoDB)
  • myd檔案：存放表資料(MyIsam)
  • myi檔案：存放表索引(MyIsam)

四、MySQL的邏輯架構

1，MySQL的分層思想

• 與其它資料庫相比，MySQL有點與眾不同，它的架構可以在多種不同場景中應用並行揮良好作用。主要體現在儲存引擎的架構上。
• 外掛式的儲存引擎架構將查詢處理和其它的系統任務以及資料的儲存提取相分離。這種架構可以根據業務的需求和實際需要選擇合適的儲存引擎。

2，MySQL的四層架構

• 連線層：最上層是一些使用者端和連線服務，包含本地socket通訊和大多數基於使用者端/伺服器端工具實現的類似於tcp/ip的通訊。主要完成類似於連線處理、授權認證、以及相關的安全方案。在該層上引入了執行緒池的概念，為通過認證安全接入的使用者端提供執行緒。同樣在該層上可以實現基於SSL的安全連結。伺服器也會為安全接入的每個使用者端驗證它所具有的操作許可權。
• 服務層：第二層架構主要完成大多數的核心服務功能，比如SQL介面，並完成快取的查詢，SQL的分析和優化及部分內建函數的執行。所有跨儲存引擎的功能也在這一層實現，如過程、函數等。在該層，伺服器會解析查詢並建立相應的內部解析樹，並對其完成相應的優化如確定查詢表的順序，是否利用索引等，最後生成相應的執行操作。如果是select語句，伺服器還會查詢內部的快取。如果快取空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的效能。

• 引擎層：儲存引擎層，儲存引擎真正的負責了MySQL中資料的儲存和提取，伺服器通過API與儲存引擎進行通訊。不同的儲存引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。
• 儲存層：資料儲存層，主要是將資料儲存在執行於裸裝置的檔案系統之上，並完成與儲存引擎的互動。

3，MySQL的查詢流程

1. 使用者端向MySQL伺服器傳送一條查詢請求
2. 伺服器首先檢查查詢快取，如果命中快取，則立刻返回儲存在快取中的結果。否則進入下一階段
3. 伺服器進行SQL解析、預處理、再由優化器生成對應的執行計劃
4. MySQL根據執行計劃，呼叫儲存引擎的API來執行查詢
5. 將結果返回給使用者端，同時快取查詢結果

五、MySQL的儲存引擎

1，檢視MySQL的儲存引擎

#檢視MySQL支援的儲存引擎
show engines;
#檢視當前使用的儲存引擎
show variables like '%storage_engine%';

　　關於當前儲存引擎出現：disabled_storage_engines

2，各儲存引擎比較

a）InnoDB儲存引擎（預設儲存引擎），支援事務安全表（ACID），支援行鎖定和外來鍵

1. 設計遵循ACID模型，支援事務，具有從服務崩潰中恢復資料的能力，能夠最大限度包含使用者的資料
2. 支援行級所，可以提升多使用者並行時的讀寫效能
3. 支援外來鍵，保持資料的一致性和完整性
4. innoDB擁有自己獨立的緩衝池，常用的資料和索引都在快取中

b）MyISAM儲存引擎：MyISAM基於ISAM儲存引擎，並對其進行擴充套件。它是在Web、資料倉儲和其他應用環境下最常使用的儲存引擎之一。MyISAM擁有較高的插入、查詢速度，但不支援事物。

1. 預設MyISAM的表會在磁碟中產生三個檔案：.frm、.MYD和.MYI
2. MyISAM單表最大支援的資料量是2的64次方條記錄
3. 每個表最多可以建立64個索引
4. 如果是複合索引，每個複合索引最多包含16個列，索引值最大長度是1000B
5. MyISAM引擎的儲存格式：定長（FIXED 靜態）：是指欄位中不包含VARCHARTEXTBLOB；動態（DYNAMIC）：只要欄位中包含VARCHARTEXTBLOB；壓縮（COMPRESSED）:myisampack建立

c）MEMORY儲存引擎：將表中的資料儲存到記憶體中，未查詢和參照其他表資料提供快速存取。

1. 磁碟中產生一個以表名為名稱的.frm檔案，只儲存表結構
2. 如果關閉MySQL服務，此時資料會產生都是rr
3. max_head_table_size預設16MB

d）ARCHIVE儲存引擎：

1. 適合對於不經常存取又刪除不了的資料做歸檔儲存
2. .frm檔案結構檔案，.arz資料檔案
3. 插入效率很高，而且佔用空間小
4. ARCHIVE儲存引擎只支援INSERT和SELECT操作，不支援UPDATE/DELECT/

3，MyIsam與InnoDB的索引結構

　　從圖中可以看出：由於MyIsam的儲存檔案中多了一個.MYI的檔案,所以建立的索引為非聚集索引都會指向對應的地址值；而Innodb建立的主鍵索引為聚合索引，所以當以普通欄位建索引時，也稱之為二級索引，其建立的索引必須關聯對應的主鍵id，所以查詢會執行兩次。