mysql是如何工作的？

初學web開發。對mysql工作原理不是太理解。

資料庫管理基礎

關係型資料庫：存儲下來表現為表，但表中數據不能過度冗餘(由行和列組成的二維表），一個表可以沒有行但必須有一個列；最終提供的數據就是行，跟列關係不大，列只是說明其是什麼屬性

資料庫的模型：

·數據模型

·層次模型

·網狀模型

·關係模型

·非關係模型：（在某種特定場景當中能夠滿足某種特殊應用的數據模型機制）

DBMS：資料庫管理系統基本概念

比如：如果我們的數據只像某一個文件 ，比如文件/etc/passwd那麼早期使用文本文件存儲數據是沒有任何問題的，無可非議。

但是，如果用文件存儲數據的方式，存在很大的缺陷：

·數據可能導致大量的冗餘

·難以描述數據實體之間的關係，比如我們一個用戶屬於一個表這種關係是容易定義的，我們通過在用戶的庫中加以欄位調用其gid實現用戶和組之間的對應關係，但是更複雜的數據關聯性，文件將無法進行描述

·如果數據量非常大的時候，我們檢索出來符合條件的部分數據，將會變得非常困難 （比如以passwd為例，其文件增長到百G以上的時候，如果我們要求在此文件找出以字母r開頭的用戶）

·無法保存數值 在數值很多處理屬性上文本是無法滿足的

因此需要一種按需定義數據定義保存格式來完成數據存儲並且在必要的時候能夠完成數據處理，如果一個應用程序使用文本文件存儲數據，那麼跟資料庫打交道的介面無非就是文件系統的系統調用介面，既然應用程序必須通過文件系統的系統調用介面直接跟文件的數據流打交道，那麼就意味著程序本身必須完成文件描述符的管理，要自己維持打開的文件，文件內容的指針（比如讀到第幾行的指針）緩衝區是否需要同步等都需要使應用程序自身來完成，假如說這個應用程序需要訪問文件中的某一點文件內容而不是全部的，它需要在這個文件中所有內容載入到內存中才可以訪問篩取等操作，因為它無法判斷符合條件的內容存放在哪個位置，在文件系統這個層次上，本身也不是有更細層的邏輯組件的；

比如我們想通過應用程序查找一用戶名為root的賬戶，那麼root用戶具體在哪個數據塊中呢？

那麼再假如可以只載入部分數據，那麼也就意味著我們自己能夠管理一個block，但問題是指定r開頭的用戶在哪個block裡面嗎？很顯然是不行的，那麼這時它需要一個更高層的抽象層，在其介面之上又提供了一種管理工具-資料庫，它既可以跟文件系統打交道又可以使用文件系統的數據塊來存放數據，最終存在文件系統上的的數據一定是數據塊，但是它又可能將這些數據塊合併為更高級別的邏輯存儲單元

舉個例子：

1個數據塊的大小是1kb,那麼它將4個數據塊合併成一個更大的數據塊,並且通過這麼介面向硬碟存放數據的時候它會按照數據塊為單位，以盤區為單位（大方塊）存儲的,也就是我們存放的數據最多存放這個4個塊上，無論沾滿還是占不滿,如下圖所示

索引

還是以passwd文件為例

[root@localhost ~]# head -3 /etc/passwd

root:x:0:0:root:/root:/bin/bash

bin:x:1:1:bin:/bin:/sbin/nologin

daemon:x:2:2:daemon:/sbin:/sbin/nologin

我們可以看到，passwd文件是以行組成的，於是我們定義通過這些信息對比我們發現，一個數據塊只能存儲7行左右的信息，還多一點，因為我們可以看到其文件內容每個用戶的用戶名屬性信息長度是不等的，有時候其數據塊能夠存7行，有的可存6行等，但不管存儲多少行，這個抽象層次自己知道裡面到底存放了多少行，是有自知之明的。

每一行的行id 靠內部機制的映射關係，記錄了在某行至某行間存儲在某個數據塊中，以此類推

所以，如果我們希望想訪問第一行到第5行的時候，則不需要將所有的數據塊載入，則可以直接載入第一個數據塊，因為它有了更高級的映射關係，意味著數據塊比文件更高於一級別，但是可以作為獨立存儲單位的數據單元有了這個層次以後，就不需要為了訪問部分文件內容而載入整個文件了

那麼這時問題又來了：

假如找到以r開頭所有用戶的用戶名，很顯然圖中的所有塊中很可能都存在，所以將其所有的塊載入再去找，跟載入全部文件再去查找沒有任何區別了

假如r開頭的用戶在第一個塊上存在，第五個數據塊中也有，但問題是管理系統通過映射表只知道某行到某行存在，並不知道指定的實際標準在某個數據塊上，那麼只能載入所有數據

首先我們的文件屬性有很多，比如id 用戶名家目錄 默認shell 等等。但是我們的初衷標準是要查找用戶名，那麼意味著我們的查找標準是只查找其用戶名，跟其他幾個屬性沒有任何的關係。

所以在查找的時候只找到每個用戶的用戶名這些數據，那麼就可以找到符合的行了。

所以如果說我們把所有數據上的每一行的用戶名都抽取出來，那麼這樣再組合成一個文件或數據塊，那麼這個文件就變得非常的小，很顯然我們文件中只有7個欄位，那麼我們只用了6個塊，那麼在平均的情況下大致使用七分之六塊就能存儲出來所有用戶名。

1.映射

這樣以後再去查找以r開頭的用戶，先去找索引，查看其用戶的對應位置，每個用戶名與其用戶的對應關係都在一個塊上，找到所有符合用戶的用戶名，再去根據這個映射關係找到對應的數據塊將其載入進來就可以了

整個過程需消耗的代價：

首先載入搜索塊，找到用戶名，加入root 以及用戶 root1 ；

發現root在第一個塊上，而root1在第五個塊上，於是我們找到root用戶的默認的shell以及root1的默認shell，還必須根據這個默認映射把這兩個塊(第一個塊和第五個塊)都載入進來，所以它的額外代價還得再載入兩個塊，但是這兩個塊讀到內存中對我們並不是都有用，那麼還需要根據這兩個塊中的實際數據去找到真正用戶所在的行，並將行的信息讀出來找到對應的欄位shell

索引分級

索引必須是搜索標準，如果用戶的行非常的多，索引本身哪怕只保留一個欄位也非常的大，假如說我們有7W個數據塊，那麼平均（並不準確）就要保存1/7 也就是1w個塊，將其載入內存消耗IO也會非常的大，於是有了索引分級：

一級索引不夠的話則可以擴展為二級、三級 等 索引，都是可以實現的

當我們設置索引到多個的級別之後，那我們實現的查找標準則會變得非常非常的小，那麼這時一個索引內容只有幾十兆而已，對一個非常大的表來講，哪怕是四級索引也是非常常見的。

那麼設置完索引後，每一次查找將索引從磁碟載入到內存中，也會消耗相當大的IO，也會很慢，所以資料庫一啟動，則直接將索引載入到內存中去，而且索引的大小必須小到能放到內存中才可以，由此在內存中存儲的索引不只一個，由此在內存中存放的索引也不只一個

阻塞

對於mysql這種關係型資料庫而言，如果有大量用戶同時發起查詢請求的時候，一個用戶的請求就有可能大費周章，需要將索引載入內存並在內存中做匹配查找，再找到其對應的數據 再去找對應的數據塊，還要從磁碟載入數據塊到內存 最終將數據抽取出來以響應報文回給用戶，這個過程就需要消耗大量的時間

如果同時還有第二個用戶發起了查詢請求，甚至與第一個用戶使用了同一個資源的話，比如 第一個用戶請求修改第一張表，第二個用戶請求查詢這張表，因此只能等到修改完成操作之後，當數據達到一致性狀態之後，才可以返回給用戶，在此期間後面的查詢操作則被阻塞,這時對用戶的體驗會非常差的。這時，並發機制派上用場了

並發性

鎖的概念：

讀鎖：共享鎖

寫鎖：獨佔鎖

如果讀操作比較多，寫操作相對少，那麼這種並發也不會成為問題；

但讀寫請求幾乎接近，這時如果還以表為例度進行加鎖，並發性會非常的差；

早期資料庫鎖是以表為單位，意味著其中某個用戶的請求正在請求某張表會對整張表加鎖，如果是讀則加讀鎖，其他用戶可以繼續讀取內容；如果是寫鎖其他用戶則無法讀寫，如果核心數據都在同一表中，那麼任何用戶的操作都有可能阻塞其他用戶，由此讀請求可以更快得到滿足

減小鎖粒度

表鎖；

頁鎖；

假如第一個用戶正在讀取用戶root和root1其在第一塊和第五個塊上，而第二個用戶正在修改第二個用戶的信息bin 要將其shell改為/sbin/nologin ，而我們通過數據組織發現 root在第一個塊上root1在第五個塊上 bin也在第一個塊上,所以當第一個用戶的操作鎖定第一塊和第五塊的時候，第二個用戶的請求則不能進行，那我們發現他們的操作不在同一行，那麼如果把粒度降到更低那麼就有機會同時進行操作了，那麼有了行級鎖：

行鎖：第一個用戶的請求只鎖定其兩行，而第二個用戶則加鎖第二行

死鎖：

假如第一個用戶要鎖定第一行和第十行，而第二個用戶要鎖定第十行和第一行 這樣一來將第一行鎖死等待用戶處理完成，但是其用戶又不能修改第十行，一直等待

一般情況只發生鎖超時，就是一個進程需要訪問資料庫表或者欄位的時候，另外一個程序正在執行帶鎖的訪問（比如修改數據），那麼這個進程就會等待，當等了很久鎖還沒有解除的話就會鎖超時，報告一個系統錯誤，拒絕執行相應的SQL操作。

資料庫響應機制

通常mysql是以一個線程響應給一個用戶的請求，因為一個用戶所查詢的數據，有可能是敏感數據是不允許其他用戶訪問的，所以我們在同一個線程內部如果響應多個用戶的請求，這些處理機制都必須依靠著線程的內在邏輯來完成，相對來說是非常複雜的

mysql與web所不同的是在於：

mysql協議是有狀態的，而http是無狀態的 所以web不會始終在線，而mysql請求建立之後會始終在線，直到請求斷開 所以線程會長時間在線，只等到用戶請求退出，由此一個mysql伺服器就算具備了同樣硬體資源，那麼它的並發性比web小的多，因為內置處理邏輯非常複雜

加速Mysql - 復用技術：

用戶退出之後進程不會被銷毀，則將其放入線程池(thread pool)

線程池的作用：

1.限制連接請求個數

2.完成線程復用，為用戶的連接快速建立提供了保障

事物：ACID （一組DML語句）

事物的特性分為以下幾種：

·原子性： 一組DML語句或都執行，或都不執行，同進同出

·一致性： 從一狀態轉換為另一狀態後，數據保持不變

·隔離性：多個事物彼此之間如操作同一數據的時候不進行干擾

隔離級別：有四個隔離級別 隔離級別越高也就意味著隔離效果越高，並發性反而越低，彼此之間的影響度越小，所以又是一個折中

1.讀未提交：

2.讀提交

3.可重讀 (MYSQL默認級別)

4.可串列化

只有在事物引擎上，隔離才有意義

持久性

數據一旦提交後 不得丟失，數據如果未提交，那必須能夠撤銷事物

事物日誌：將隨機IO轉換至順序IO

為了保障持久性，事物一旦提交立即寫入磁碟

隨機IO：由於DML語句所操作的行，對其進行修改操作，改了某幾個行，而這些行恰好都不在同一數據塊上，資料庫只好依次找到對應塊對其進行修改，這叫做隨機IO 所以寫入速度非常慢，不但保證持久度降低還有可能導致系統性能降低

順序IO：將所有的操作本身直接記錄到日誌中，而這些操作能夠讀取出來再多次利用（冪等性）

Mysql日誌：

mysql的日誌類型分為以下（這裡只講事物日誌，其他放在後期來說）

·錯誤日誌

·查詢日誌

·慢查詢日誌

·事物日誌

事物日誌越大，並發性就越好，但是當在啟動資料庫則需要將提交上來的事物逐步同步到數據文件中去，將未提交的數據進行撤銷，通常用戶是不能手工參與的，所以事物日誌也不應該過大，並且一定要避免磁碟的損壞而導致數據的丟失，可以做raid本地做鏡像或手動做備份，當然前者方式更好。因為業務不會終止，日誌需要大量的寫還要不停的同步到資料庫中去，所以不建議將事務日誌與數據文件放入到一個分區上（單塊硬碟存儲的情況下）因為寫日誌需要IO同步數據也需要IO性能可想而知

·二進位日誌

·中繼日誌

介面類型

SQL主要是使用API介面

sql client ：能夠基於某種協議跟server端進行通信，而這種客戶端則不是通用的

mysql提供的連接介面：

linux系統：

·遠程通信

·本地間通信 （如果是linux通常使用mysql.sock來通信 但是要求必須都在同一台主機）

windows系統：

·PIPE

·MEMORY

數據的組織：

數據類型：變長、定長

比如varchar(20),當我們定義一個數據欄位varchar欄位的時候，如果存放了4個左右的字元，比如root，需要佔用5個位元組，所以存儲數據不同的時候，所佔據的空間數是不一樣的，如果使用cahr（20）來存儲root字元串，那麼則佔用20個位元組，就算後面的空閑空間沒有使用，依然是20個位元組。所以在數據設計的時候考慮數據增長的長度是至關重要的,所以一定要使用折中的方法，除非最長和最短相差太多，才考慮使用varchar，除此建議一律使用char

記錄的類型

定長記錄：行數是固定的

#在分配的每個數據塊內部所存儲數據條目的個數（行）是有限的，而且是固定的

變長記錄：行數是變化的

所以每個記錄到底在當前塊內占真正存放多少個記錄，以及每個記錄的地址從哪個地方開始到哪個位置結束，每個記錄都需要記錄否則很難追中一個條，同事還需要描述當前塊內有多大的空閑空間可用，於是存放行記錄，空閑的行為空閑位置

記錄

文件中對錶而言，所存儲的每一行數據都有一個唯一id號，以方便實現能讓當前資料庫存儲引擎等最終這一行被稱為RID ，其做為表本身存儲的額外開銷而存在，這樣就可以追蹤表了

一般來講要想實現在文件存儲記錄的話，要完成的操作包括 插入 刪除 修改 掃描整個表獲取符合條件的記錄等

因此掃描操作允許資料庫系統能夠訪問一個記錄的文件中的每一行進行移動，類似於指針 從第一行指到最後一行

如何獲取文件的時候需要按次序進行：

存儲的時候沒有按次序，用戶名或其他是隨機存放的，我們需要統計瀏覽網站次數進行排序，那就意味著要把每個用戶的所有信息都抽取出而後再通過某一機制進行排序，因此存儲在磁碟上的時候，如果本身有次序，檢索出來的時候就不需要進行排序，如果沒有次序，則必須得重新排序

堆文件：無序記錄的文件被稱為堆文件

有序文件：通常需要結合聚簇索引來完成排序

如果索引是有序的，那麼記錄也就是有序的；

如果數據存儲跟其索引存儲相同時，而索引本身是有序的記錄，我們被稱為有序記錄

聚簇索引：將數據直接存儲在所在數據文件塊上的索引上

所以，無論是檢索數據或插入數據，都對其性能有影響的，尤其是檢索數據

文件組織

順序文件組織：

·根據索引來快速完成文件檢索，而特意按照某種格式來完成的文件

·如果順序文件組織存儲本身是按照順序文件來存的話，這種方式也就叫做聚簇索引

散列文件組織：

·hash索引

索引類型

·主索引：通常數據文件記錄文件的時候可以按照索引次序存儲的，而文件存儲如果是有序的，只能跟主索引同一次序

·輔助索引：全都是無續的

·聚簇索引：索引是按照ID來存放的，索引本身沒有指針指向數據，而是直接將數據直接存放在同一個文件，而且是按照順序存放

·索引的數據結構，索引的類型：

·樹狀索引

·散列索引（散列索引只適合做比較）

資料庫存儲的文件

·數據文件

·索引文件

#對於聚簇索引來說，以上是存放在同一文件中

·日誌文件

MySQL常用存儲引擎：

負責將物理模型二進位代碼，轉換成更高一層的邏輯模型 數據塊，並且理解上層次其他組件對其的請求，而只載入某個數據塊並非全部的數據都是由存儲引擎來完成的。

簡單來講常用兩種存儲引擎的名稱和格式有：

·MyISAM

myisam引擎每個表都有三個文件：

數據文件：表名.MYD

索引文件: 表名.MYI

表定義:表名.frm

格式：

mydb:

test.frm

test.MYD

test.MYI

想複製資料庫的話，直接將目錄複製並遷移即可

·InnoDB

#相對來說支持事物的，支持聚簇索引，因此數據和索引不是分開存放的

表空間：多張表可放置於同一表空間，表空間表現為一個個空間，表空間能為多個資料庫存放數據

mysql支持使用獨立的表空間文件，建議必須這麼做

表定義文件：每張表的表定義文件在其資料庫目錄中，如下所示：

/mydata/data

ibdata1

mysql

test

infomation_schema

如果自己建立資料庫名為mydb,在其下創建了表test：

mydb:

test

那麼這裡如果是innodb存儲引擎那麼只能看到一個文件 test.frm ，那麼數據和索引都存放在ibadata1

這就是所謂的 "表空間"

在存儲引擎載入的數據要放入到內存裡面，我們要管理這些緩存到內存中的數據，這時我們需要用到緩衝區管理器

緩衝區管理器

其知道如何將那些數據放到哪些塊上，如何能夠高效載入內存，並且將來存儲的時候如何將內存同步到磁碟上去，主要作用就是加速數據的存取，能夠完成快速的數據查找

緩存置換：

·緩存轉換策略：

·LRU：最少使用演算法：

#資料庫會根據這種策略來查詢所有緩存對象哪些訪問的量最少，於是將其寫回到硬碟當中，而後，後續的緩存則繼續加入到緩衝區中去。

·MRU：最近最長使用演算法

#比LRU演算法更為優秀

·被釘住的塊(pinned block)：這樣的塊則不會被寫回磁碟

塊的強制寫出：按某時間固定寫回至磁碟中去，為了數據安全可靠性而設計的策略

緩存區只有innodb支持，所以innodb引擎調優就是這些參數

#查看當前有效變數 global為全局

mysql&>SHOW {global|session} VARIABLES;

#varibales可以動態調整的

#但是資料庫只能改配置文件並重啟資料庫才可以

&>SHOW {global|session} STATUS;

mysql的數據字典

mysql：

·用來保存資料庫的元數據 （也叫做系統目錄）

·一般來講保存的數據有：

·關係的名字

·每個表的屬性的名字

·屬性的定義

·數據類型和長度

·每個表的關係上的視圖名稱以及視圖定義

·約束

授權用戶的名稱：

用戶的授權和賬戶信息

infomation_schema:

統計數據：

每個關係中屬性的個數

每個關係中行的個數

每個關係的存儲方法

（比如每個行有多少表，每個表有多少行等）

performace_schema:

顯示性能相關信息 和統計數據信息