MySQL的架構(gòu)
點(diǎn)擊上方藍(lán)色字體，選擇“標(biāo)星公眾號(hào)”
優(yōu)質(zhì)文章，第一時(shí)間送達(dá)
? 作者?|? MXC肖某某
來源 |? urlify.cn/bINBJr
76套java從入門到精通實(shí)戰(zhàn)課程分享
一、MySQL簡(jiǎn)介
數(shù)據(jù)庫(kù)是一個(gè)以某種有組織的方式存儲(chǔ)的數(shù)據(jù)集合，它是一個(gè)按數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)和管理數(shù)據(jù)的計(jì)算機(jī)軟件系統(tǒng)。理解數(shù)據(jù)庫(kù)的一種最簡(jiǎn)單的辦法是將其想象為一個(gè)文件柜，這個(gè)文件柜只是一個(gè)存儲(chǔ)數(shù)據(jù)的空間，而數(shù)據(jù)庫(kù)就是這么一個(gè)空間。
數(shù)據(jù)庫(kù)管理系統(tǒng)（Database Management System, DBMS）是用于創(chuàng)建、管理、和維護(hù)數(shù)據(jù)庫(kù)時(shí)所使用的軟件，用以管理數(shù)據(jù)庫(kù)，一定程度上，用戶都是在操作DBMS，所以日常的數(shù)據(jù)庫(kù)概念一般就是指DBMS。
MySQL是一種DBMS，而且是一種一個(gè)小型的開源的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)。2008年1月16日MySQL AB被Sun公司收購(gòu)。而2009年，SUN又被Oracle收購(gòu)。就這樣如同一個(gè)輪回，MySQL成為了Oracle公司的另一個(gè)數(shù)據(jù)庫(kù)項(xiàng)目。
MySQL支持大型數(shù)據(jù)庫(kù)，支持5000萬條記錄的數(shù)據(jù)倉(cāng)庫(kù)，32位系統(tǒng)表文件最大可支持4GB，64位系統(tǒng)支持最大的表文件為8TB。
二、MySQL用戶組管理
1，用戶管理相關(guān)命令
a)創(chuàng)建用戶
#創(chuàng)建用戶:zs??密碼:123456
create?user?zs?identified?by?'123456';
? ? ? ?b)查看用戶權(quán)限
select?host,?user,?password,?select_priv,?insert_priv,drop_priv?from?mysql.user;
host?:表示連接類型
??%?表示所有遠(yuǎn)程通過?TCP?方式的連接
??IP?地址?如?(192.168.1.2,127.0.0.1)?通過制定?ip?地址進(jìn)行的?TCP?方式的連接
??機(jī)器名?通過制定?i?網(wǎng)絡(luò)中的機(jī)器名進(jìn)行的?TCP?方式的連接
??::1?IPv6?的本地?ip?地址?等同于?IPv4?的?127.0.0.1
? localhost 本地方式通過命令行方式的連接?，?比如 mysql -u xxx -p 123xxx 方式的連接。
user:表示用戶名
??同一用戶通過不同方式鏈接的權(quán)限是不一樣的。
password:密碼
??所有密碼串通過 password(明文字符串)?生成的密文字符串。?加密算法為 MYSQLSHA1 ，?不可逆?。
? mysql 5.7 的密碼保存到 authentication_string 字段中不再使用 password 字段。
select_priv?,?insert_priv?等
??為該用戶所擁有的權(quán)限。
c)修改密碼
#MySQL5.6使用
#基本使用?修改lisi密碼為123456
update?mysql.user?set?password=password('123456')?where?user='lisi';
#修改當(dāng)前用戶
set?password?=password('123456');
#刷新權(quán)限生效
flush?privileges;
#MySQL5.7推薦使用
#基本使用?修改testuser用戶密碼為123456
ALTER?USER?testuser?IDENTIFIED?BY?'123456';
#修改當(dāng)前登錄用戶?密碼為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的權(quán)限管理
a)授予權(quán)限
#grant?權(quán)限?1,權(quán)限?2,…權(quán)限?n?on?數(shù)據(jù)庫(kù)名稱.表名稱?to?用戶名@用戶地址?identified?by?'連接口令'
#給 lisi 用戶用本地命令行方式下，?授予 test這個(gè)庫(kù)下的所有?表的插刪改查的權(quán)限。
grant?select,insert,delete,drop?on?test.*?to?lisi@'%'?;
#授予通過網(wǎng)絡(luò)方式登錄的的?jack?用戶，對(duì)所有庫(kù)所有表的全部權(quán)?限，?密碼設(shè)為?123
grant?all?privileges?on?*.*?to?jack@'%'?identified?by?'123';b)收回權(quán)限
#查看當(dāng)前用戶權(quán)限
show?grants;
#收回權(quán)限
#revoke?[權(quán)限?1,權(quán)限?2,…權(quán)限?n]?on?庫(kù)名.表名?from?用戶名@用戶地址;
#收回全庫(kù)全表的所有權(quán)限
REVOKE?ALL?PRIVILEGES?ON?*.*?FROM?jack@'%';
#收回?mysql?庫(kù)下的所有表的插刪改查?權(quán)限
REVOKE?select,insert,update,delete?ON?mysql.*?FROM?jack@'%';三、MySQL配置文件
二進(jìn)制日志文件 log-bin：用于主從復(fù)制
錯(cuò)誤日志log-error：默認(rèn)關(guān)閉，記錄嚴(yán)重的警告和錯(cuò)誤信息，每次啟動(dòng)和關(guān)閉的詳細(xì)信息等
查詢?nèi)罩緇og：默認(rèn)關(guān)閉，記錄查詢的sql語句，如果開啟會(huì)降低mysql的整體性能，因?yàn)橛涗浫罩疽彩切枰南到y(tǒng)資源的
數(shù)據(jù)文件
數(shù)據(jù)庫(kù)文件存儲(chǔ)：默認(rèn)為 /var/lib/mysql
frm文件：存放表結(jié)構(gòu)(都存在)
idb文件：存放表的數(shù)據(jù)(InnoDB)
myd文件：存放表數(shù)據(jù)(MyIsam)
myi文件：存放表索引(MyIsam)
四、MySQL的邏輯架構(gòu)
1，MySQL的分層思想
與其它數(shù)據(jù)庫(kù)相比，MySQL有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用。主要體現(xiàn)在存儲(chǔ)引擎的架構(gòu)上。
插件式的存儲(chǔ)引擎架構(gòu)將查詢處理和其它的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲(chǔ)提取相分離。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎。
? ? ? ?
2，MySQL的四層架構(gòu)
連接層：最上層是一些客戶端和連接服務(wù)，包含本地socket通信和大多數(shù)基于客戶端/服務(wù)端工具實(shí)現(xiàn)的類似于tcp/ip的通信。主要完成類似于連接處理、授權(quán)認(rèn)證、以及相關(guān)的安全方案。在該層上引入了線程池的概念，為通過認(rèn)證安全接入的客戶端提供線程。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。
服務(wù)層：第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能，比如SQL接口，并完成緩存的查詢，SQL的分析和優(yōu)化及部分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，如過程、函數(shù)等。在該層，服務(wù)器會(huì)解析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹，并對(duì)其完成相應(yīng)的優(yōu)化如確定查詢表的順序，是否利用索引等，最后生成相應(yīng)的執(zhí)行操作。如果是select語句，服務(wù)器還會(huì)查詢內(nèi)部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能。
| Management Serveices & Utilities | 系統(tǒng)管理和控制工具 |
|---|---|
| SQL Interface | SQL 接口。接受用戶的 SQL 命令， 并且返回用戶需要查詢的結(jié)果。比如 select from 就是調(diào)用 SQL Interface |
| Parser | 解析器。SQL 命令傳遞到解析器的時(shí)候會(huì)被解析器驗(yàn)證和解析 |
| Optimizer | 查詢優(yōu)化器。SQL 語句在查詢之前會(huì)使用查詢優(yōu)化器對(duì)查詢進(jìn)行優(yōu)化， 比如有 where 條件時(shí)， 優(yōu)化器來決定先投影還是先過濾。 |
| Cache 和 Buffer | 查詢緩存。如果查詢緩存有命中的查詢結(jié)果， 查詢語句就可以直接去查詢緩存中取 數(shù)據(jù)。這個(gè)緩存機(jī)制是由一系列小緩存組成的。比如表緩存， 記錄緩存， key 緩存， 權(quán)限緩存等 |
引擎層：存儲(chǔ)引擎層，存儲(chǔ)引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過API與存儲(chǔ)引擎進(jìn)行通信。不同的存儲(chǔ)引擎具有的功能不同，這樣我們可以根據(jù)自己的實(shí)際需要進(jìn)行選取。
存儲(chǔ)層：數(shù)據(jù)存儲(chǔ)層，主要是將數(shù)據(jù)存儲(chǔ)在運(yùn)行于裸設(shè)備的文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互。
3，MySQL的查詢流程
? ??
客戶端向MySQL服務(wù)器發(fā)送一條查詢請(qǐng)求
服務(wù)器首先檢查查詢緩存，如果命中緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一階段
服務(wù)器進(jìn)行SQL解析、預(yù)處理、再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃
MySQL根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢
將結(jié)果返回給客戶端，同時(shí)緩存查詢結(jié)果
五、MySQL的存儲(chǔ)引擎
1，查看MySQL的存儲(chǔ)引擎
#查看MySQL支持的存儲(chǔ)引擎
show?engines;
#查看當(dāng)前使用的存儲(chǔ)引擎
show?variables?like?'%storage_engine%';
關(guān)于當(dāng)前存儲(chǔ)引擎出現(xiàn)：disabled_storage_engines
2，各存儲(chǔ)引擎比較
a）InnoDB存儲(chǔ)引擎（默認(rèn)存儲(chǔ)引擎），支持事務(wù)安全表（ACID），支持行鎖定和外鍵
設(shè)計(jì)遵循ACID模型，支持事務(wù)，具有從服務(wù)崩潰中恢復(fù)數(shù)據(jù)的能力，能夠最大限度包含用戶的數(shù)據(jù)
支持行級(jí)所，可以提升多用戶并發(fā)時(shí)的讀寫性能
支持外鍵，保持?jǐn)?shù)據(jù)的一致性和完整性
innoDB擁有自己獨(dú)立的緩沖池，常用的數(shù)據(jù)和索引都在緩存中
b）MyISAM存儲(chǔ)引擎：MyISAM基于ISAM存儲(chǔ)引擎，并對(duì)其進(jìn)行擴(kuò)展。它是在Web、數(shù)據(jù)倉(cāng)儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一。MyISAM擁有較高的插入、查詢速度，但不支持事物。
默認(rèn)MyISAM的表會(huì)在磁盤中產(chǎn)生三個(gè)文件：.frm、.MYD和.MYI
MyISAM單表最大支持的數(shù)據(jù)量是2的64次方條記錄
每個(gè)表最多可以建立64個(gè)索引
如果是復(fù)合索引，每個(gè)復(fù)合索引最多包含16個(gè)列，索引值最大長(zhǎng)度是1000B
MyISAM引擎的存儲(chǔ)格式：定長(zhǎng)（FIXED 靜態(tài)）：是指字段中不包含VARCHAR\TEXTBLOB；動(dòng)態(tài)（DYNAMIC）：只要字段中包含VARCHAR\TEXTBLOB；壓縮（COMPRESSED）:myisampack創(chuàng)建
c）MEMORY存儲(chǔ)引擎：將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，未查詢和引用其他表數(shù)據(jù)提供快速訪問。
磁盤中產(chǎn)生一個(gè)以表名為名稱的.frm文件，只保存表結(jié)構(gòu)
如果關(guān)閉MySQL服務(wù)，此時(shí)數(shù)據(jù)會(huì)產(chǎn)生都是rr
max_head_table_size默認(rèn)16MB
d）ARCHIVE存儲(chǔ)引擎：
適合對(duì)于不經(jīng)常訪問又刪除不了的數(shù)據(jù)做歸檔儲(chǔ)存
.frm文件結(jié)構(gòu)文件，.arz數(shù)據(jù)文件
插入效率很高，而且占用空間小
ARCHIVE存儲(chǔ)引擎只支持INSERT和SELECT操作，不支持UPDATE/DELECT/
| 功 能 | MYISAM | Memory | InnoDB | Archive |
| 存儲(chǔ)限制 | 256TB | RAM | 64TB | None |
| 支持事物 | No | No | Yes | No |
| 支持全文索引 | Yes | No | No(5.7之后支持) | No |
| 支持?jǐn)?shù)索引 | Yes | Yes | Yes | No |
| 支持哈希索引 | No | Yes | No | No |
| 支持?jǐn)?shù)據(jù)緩存 | No | N/A | Yes | No |
| 支持外鍵 | No | No | Yes | No |
3，MyIsam與InnoDB的索引結(jié)構(gòu)
? ? ??
從圖中可以看出：由于MyIsam的存儲(chǔ)文件中多了一個(gè).MYI的文件,所以建立的索引為非聚集索引都會(huì)指向?qū)?yīng)的地址值；而Innodb建立的主鍵索引為聚合索引，所以當(dāng)以普通字段建索引時(shí)，也稱之為二級(jí)索引，其建立的索引必須關(guān)聯(lián)對(duì)應(yīng)的主鍵id，所以查詢會(huì)執(zhí)行兩次。
粉絲福利：Java從入門到入土學(xué)習(xí)路線圖
??????
??長(zhǎng)按上方微信二維碼?2 秒
感謝點(diǎn)贊支持下哈?