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語句，伺服器還會查詢內部的快取。如果快取空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。
Management Serveices & Utilities系統管理和控制工具
SQL Interface SQL 介面。 接受用戶的 SQL 命令， 並且返回用戶需要查詢的結果。 比如 select from 就是調用 SQL Interface
Parser 解析器。 SQL 命令傳遞到解析器的時候會被解析器驗證和解析
Optimizer 查詢優化器。 SQL 語句在查詢之前會使用查詢優化器對查詢進行優化， 比如有 where 條件時， 優化器來決定先投影還是先過濾。
Cache 和 Buffer 查詢快取。 如果查詢快取有命中的查詢結果， 查詢語句就可以直接去查詢快取中取 數據。 這個快取機制是由一系列小快取組成的。 比如錶快取， 記錄快取， key 快取， 許可權快取等
  • 引擎層：存儲引擎層，存儲引擎真正的負責了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 靜態）：是指欄位中不包含VARCHAR\TEXTBLOB；動態（DYNAMIC）：只要欄位中包含VARCHAR\TEXTBLOB；壓縮（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/
功 能 MYISAM Memory InnoDB Archive
存儲限制 256TB RAM 64TB None
支援事物 No No Yes No
支援全文索引 Yes No No(5.7之後支援) No
支援數索引 Yes Yes Yes No
支援哈希索引 No Yes No No
支援數據快取 No N/A Yes No
支援外鍵 No No Yes No

3，MyIsam與InnoDB的索引結構

      

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

 

 

Tags:

VirMach 便宜 VPS