資料庫基礎知識整理與複習總結

1、資料庫底層

MySQL資料庫的底層是B+樹。說到B+樹，先說下B樹，B樹也叫多路平衡查找樹，所有的葉子節點位於同一層，具有以下特點：1）一個節點可以容納多個值；2）除非數據已滿，不會增加新的層，B樹追求最少的層數；3）子節點中的值與父節點的值有嚴格的大小對應關係。一般來說，如果父節點有a個值，那麼就有a+1個子節點；4）關鍵字集合分布在整棵樹中；5）任何一個關鍵字出現且只出現在一個節點中；6）搜索可能在葉子結點結束，其搜索性能等價於在關鍵字全集做一次二分查找。

B+樹是基於B樹和葉子節點順序訪問指針進行實現，它具有B樹的平衡性，並且通過順序訪問指針來提高區間查詢的性能，一個葉子節點中的key從左至右非遞減排列。特點在於：1）非葉子節點中含有n個關鍵字，關鍵字不保存數據，只作為索引，所有數據都保存在葉子結點；2）有的葉子節點中包含了全部關鍵字的資訊及只想這些關鍵字記錄的指針，即葉子節點包含鏈表結構，能夠方便進行區間查詢；3）所有的非葉子結點可以看成是索引部分，節點中僅包含其子樹中的最大（或最小）關鍵字；4）同一個數字會在不同節點中重複出現，根節點的最大元素就是B+樹的最大元素。

MySQL中的InnoDB引擎是以主鍵ID為索引的數據存儲引擎。InnoDB通過B+樹結構對ID建立索引，在葉子節點存儲數據。若建索引的欄位不是主鍵ID，則對該欄位建索引，然後再葉子節點中存儲的是該記錄的主鍵，然後通過主鍵索引找到對應的記錄。因為不再需要全表掃描，只需要對樹進行搜索即可，所以查找速度很快，還可以用於排序和分組。

InnoDB和MyISAM引擎都是基於B+樹，InnoDB是聚簇索引，數據域存放的是完整的數據記錄；MyISAM是非聚簇索引，數據域存放的是數據記錄的地址。InnoDB支援表鎖、行鎖、間隙鎖、外鍵以及事務，MyISAM僅支援表鎖，同時不支援外鍵和事務。InnoDB注重事務，MyISAM注重性能。

2、SQL語言之DQL、DML、DDL和DCL

DQL指的是Data Query Language，資料庫查詢語言，主要是select命令；

DML指的是Data Manipulation Language，資料庫操作語言，主要有insert、delete、update等命令；

DDL指的是Data Defined Language，資料庫定義語言，主要是對資料庫的某些對象，如database和table進行管理，主要有create、alter、drop等命令，比如創建資料庫和表格、更改表結構和設置約束、刪除表和資料庫；

DCL指的是Data Control Language，資料庫控制語言，主要是用於授予或回收訪問資料庫的某種特權，並控制資料庫操縱事務發生的時間及效果，主要有grant、rollback等命令。

DML和DDL的區別：DML操作可以手動控制事務的開啟、提交和回滾，而DDL是隱形提交不能回滾。

3、資料庫連接協議

JDBC驅動協議：默認TCP協議

客戶端和Oracle伺服器之間通訊協議是TCP的，但是一個資料庫連接也會其一個UDP埠。

MySQL的連接方式有兩種：Socket和TCP/IP

-- Socket連接方式
mysql -uroot -padmin -S/application/mysql/tmp/mysql.sock
-- TCP/IP連接方式
mysql -uoot -padmin -h192.1.1.20

4、索引和主鍵的區別

主鍵用於標識資料庫記錄的唯一性，不允許記錄重複且鍵值不能為空。主鍵是特殊索引，但索引不一定是主鍵。

索引可以提高查詢速度，可以不需要進行全表掃描而快速查詢到結果。

使用主鍵，資料庫會自動創建主鍵索引，同時也可以再非主鍵上創建索引。

數據表中只能由一個主鍵，但可以有多個索引。

5、資料庫四範式

範式：創建資料庫的過程中必須遵循的準則。其作用在於減少資料庫中的數據冗餘，以增加數據的一致性。

候選鍵：唯一識別該表的屬性或屬性表。

第一範式（1NF）：屬性不可拆分或無重複的列；

第二範式（2NF）：資料庫中的每一行必須被唯一地區分，即表中欄位必須完全依賴於全部主鍵而非部分主鍵；

第三範式（3NF）：消除傳遞依賴，資料庫非主鍵外的所有欄位僅能以來於候選鍵，不存在與其他非主鍵關聯；

第四範式（4NF）：一個表的主鍵只對應一個多值，即消除多值依賴。

6、SQL基礎

資料庫的創建與使用：

create database test;
use test;

創建表：

create table mytable (
id int not null auto_increment,
name varchar(20)
);

修改表：

alter table mytable add sex varchar(20);

增刪改：

insert into mytable(id,name,sex) values(1,'zhangsan','male');
delete from mytable where id = 1;
update mytable set name = 'lisi' where id = 1;

清空表：

truncate table mytable;

distinct：用於返回唯一不同的值

select distinct name from mytable;

limit：檢索記錄行

-- 檢索前5個記錄行
select * from mytable limit 5;
-- 檢索記錄行1-5行
select * from mytable limit 0, 5;
-- 檢索6-last行
select * from mytable limit 5, -1;

排序：

-- 默認升序
select * from mytable order by col1 DESC， col2 ASC;

like匹配：

-- %匹配大於等於1個任意字元
select * from mytable where name = 'z%';
-- _匹配1個任意字元
select * from mytable where name = 'l_s_';
-- []匹配集合內的字元
select * from mytable where name = '[zl]%';
-- [!]匹配除開集合內的字元
select * from mytable where name = '[!z]%';

concat()：用於將多個字元串連接成一個字元串

select concat(trim(col1), '(', trim(col2), ')') as new from mytable;

函數：

-- 均值
select avg(col) as col_avg from mytable;
-- 計數
select count(col) as col_count from mytable;
-- 類似的sum()、max()、min()等
-- 時間
select now();

分組：

select col, count(*) as num from mytable where col > 2 group by col having by num > 2;

子查詢中只能返回一個欄位的數據：

select * from mytable1 where col1 in (select col2 from mytable2);

組合查詢：

select * from mytable where col = 1 union select col from mytable where col = 2;

視圖：

create view as myview as select * from mytable where id > 2;

存儲過程：

create procedure myprocedure(out ret int)
begin
...
end

7、 刪除操作delete、truncate和drop

delete：直接刪除表中的某一行數據，並且同時將該行的刪除操作作為事務記錄在日誌中保存便於進行回滾，因此delete操作更加佔用資源，數據空間不釋放。delete可以對table和view對象進行操作。

delete from mytable where id = 1;

truncate：一次性從數據表中刪除所有數據（釋放存儲表數據所用的數據頁來刪除數據），因此不能回滾，佔用資源更加少，速度更快。數據空間釋放後，表和索引所佔用的空間會回復到初始大小。只能對沒有關聯視圖的table進行操作，對於外鍵約束引用的表，不能使用truncate，需要使用delete。

truncate mytable；

drop：刪除整個表，包括表的結構、數據、定義等。屬於永久抹去，空間會釋放，無法恢復，對table和view都能操作。

drop mytable;

總結：

在速度上，drop > truncate > delete；

在操作對象上，delete和drop可以對table和view操作，truncate只能對table操作；

在表和索引所佔空間上，delete操作不會減少表和索引佔用的空間，truncate操作後表和索引所佔用的空間會恢復至初始大小，drop將表所佔用的空間全部釋放；

在回滾上方面，delete操作為DML語句可以回滾，truncate和drop為DDL語句，隱式提交無法回滾；

在刪除限制上，delete可以操作帶有外鍵約束引用的表，而truncate不可以；

8、char和varchar的區別

char的長度是不可變的，而varchar的長度是可變的。例如，創建表時定義一個char[10]和varchar[10]，當存入一個字元串sql時，char所佔的長度依然為10，除了字元sql外後面跟7個空格，而varchar所佔的長度變為字元串的實際長度3。在取數據時，char類型的要用trim()函數去掉多餘的空格，而varchar類型不需要。

char類型的存取速度比varchar快得多，因為其長度固定方便存儲與查找；但char類型會付出空間的代價，是以空間換時間來爭取高的時間效率，而varchar是以空間效率為首。

char對英文字元（ASCII）佔用1個位元組，對漢字佔用2個位元組；varchar對英文字元和漢字都是佔用2個位元組。

9、 資料庫的冷備份與熱備份

冷備份（off，慢，時間點上恢復）：需要資料庫正常關閉，會提供一個完整的資料庫；將關鍵性文件拷貝到另外位置；對於備份資料庫資訊而言，冷備份是最快最安全的方法。

優點：易歸檔、能夠回復到某個時間點；

缺點：資料庫必須處於關閉狀態；

熱備份（on，塊）：資料庫運行的情況下，備份資料庫操作的sql語句，當資料庫發生問題時可以重新執行一遍備份的sql語句。

優點：備份時資料庫仍可以使用、快速恢復、屬於表或資料庫級別的備份，並且時間短

缺點：不能出錯

10、資料庫的事務及ACID屬性

事務：邏輯上的一組操作，要麼都執行，要麼都不執行。

四大特性：

A原子性：事務是最小的執行單位，不可分割，要麼全部執行，要麼都不執行；

C一致性：事務執行前後，資料庫的數據保持一致，多個事務對同一數據讀取的結果是相同的；

I隔離性：並發訪問資料庫時，事務之間互不打擾，各並發事務之間的資料庫是獨立的；

D持久性：一個事務被提交之後，該事務對資料庫所做的改變是持久的，不會被回滾。

11、並發事務的問題

臟讀：一個事務讀取到了另一個事務還未提交的修改數據，如果另一個事務進行了回滾，這個數據就是臟數據。

修改丟失：一個事務讀取數據並進行了修改，另一個事務頁讀取了該數據進行了修改，這樣第一個事務的修改結果就丟失了，也就是修改丟失問題。

不可重複讀：一個事務對同一數據進行多次修改，期間另一事務也讀取了該數據並進行了修改，這樣第一個事務讀取到的數據可能不一致，稱為不可重複讀。

幻讀：一個事務在讀取多行數據時，另一個並發事務插入了一些新數據，後續查詢中第一個事務會查找到一些原本不存在的數據記錄，稱為幻讀。在Mysql中利用MVCC解決了快照讀幻讀，利用間隙鎖解決了當前讀幻讀。

12、隔離級別

讀未提交：最低隔離級別，允許讀取尚未提交的數據變更，會導致臟讀、不可重複讀和幻讀；

讀已提交：允許讀取並發事務已經提交的數據，可以阻止臟讀，會導致不可重複讀和幻讀；

可重複讀：對同一欄位多次讀取的結果都是一樣的，可以阻止臟讀和不可重複讀，會導致幻讀；

串列化：最高隔離界別，可以阻止臟讀、不可重複讀和幻讀。

MySQL中InnoDB引擎默認支援的隔離級別是可重複讀，使用的是next-key Lock演算法，可以避免幻讀的產生，可以完全保證事務的隔離性要求

13、多表查詢

一對多關係：從表使用主表的主鍵作為外鍵；主表中有的數據，從表中可以沒有；主表必須有數據，才能向從表中添加數據；要先刪除從表的相應數據才能刪除主表的數據；

多對多關係：老師與學生，一個老師可以教多個學生，一個學生也可以從多個老師那裡學習知識。創建表格時，將多對多的關係拆分為多個一對多關係。

14、資料庫表的連接方式

內連接：inner join……on…… ：取交集

外連接：

  • 1）left join……on…… ：以左表為準，查詢出左表的所有數據，右表中有對應的則顯示出來，沒有對應的則顯示為null；
  • 2）right join……on…… ：以右表為準，查詢出右表的所有數據，左表中有對應的則顯示出來，沒有對應的則顯示為null；
  • 3）full join……on…… ：left和right的集合，某表中某一行在另一表中無匹配行，則相應列的內容為null；

交叉連接：cross join…… ：笛卡爾積，相當於兩個表中的所有行進行排列組合。

15、存儲過程與存儲函數

存儲過程：為以後的使用而保存的一條或多條SQL語句的集合，相當於批處理。存儲過程被編譯後會被直接保存在資料庫中，成為資料庫的一部分，以後就可以反覆調用、運行速度快。

-- in表示輸入變數，out表示輸出變數，inout輸入輸出均可
create procedure myprocedure([IN|OUT|INOUT] 參數名 數據類型, [IN|OUT|INOUT] 參數名 數據類型, ...)
begin
...
end

存儲函數：

create function myfunction(參數名 數據類型, ...) returns 返回類型
begin
...
end

二者區別：

  • 存儲函數限制較多，例如不能使用臨時表，只能用表變數，而存儲過程限制較少；
  • 存儲過程可以實現複雜的功能，存儲函數針對性比較強；
  • 返回值不同，存儲過程可以沒有返回值，也可以返回單個或多個結果集，而存儲函數有且僅有一個返回值；
  • 調用不同。存儲過程通過call語句調用，存儲函數通過select調用；
  • 參數不同。存儲過程的參數類型可以是in、out、inout，而存儲函數的參數類型只有in類型。

16、觸發器

SQL觸發器是一種特殊類型的存儲過程，不由用戶調用。它在指定的表中的數據發生變化時自動生效。喚醒調用觸發器以響應Insert、Update和Delete語句。他可以查詢其它表，並可以包含複雜的Transact-SQL語句。將觸發器和觸發它的語句作為可在觸發器內回滾的單個事務對待。如果檢測到嚴重錯誤，整個事務自動回滾。

優點：

  • 觸發器可通過資料庫中的相關表實現級聯更改；
  • 觸發器可以強制比用check約束定義的約束更為複雜的約束；
  • 在約束所支援的功能無法滿足應用程式的功能要求時，觸發器就極為有用。
create trigger trigger_order after insert on orders for each row
begin
update product set pnum = pnum - new.onum where pid = new.pid;
end

17、資料庫的鎖機制

MyISAM和InnoDB存儲引擎使用的鎖：

MyISAM採用表級鎖（table-level locking），InnoDB支援行級鎖（row-level locking）和表級鎖，默認為行級鎖。

二者對比：

  • 表級鎖：MySQL中粒度最大的鎖，對當前操作的整張表加鎖，實現簡單，資源消耗少，加鎖快，不會出現死鎖。鎖衝突的概率高，並發度低。
  • 行級鎖：MySQL中粒度最小的鎖，只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突。加鎖慢，開銷大，並發度高，會出現死鎖。

InnoDB引擎的鎖演算法：

  • Record lock：記錄鎖。條件為精準匹配時，鎖住具體的索引項；

  • Gap lock：間隙鎖。鎖定一個範圍，不包括記錄本身。

  • Next-key lock：臨鍵鎖。鎖定一個範圍，包含記錄本身。

按思想分為樂觀鎖和悲觀鎖

  • 樂觀鎖：事務並發操作時認為不會發生衝突，對數據進行更新並提交，如果檢測到衝突就返回。
  • 悲觀鎖：事務並發操作使認為會發生衝突，先進行加鎖操作。

18、日誌

bin log：二進位日誌（服務層）：涉及到主從複製；

redo log：重做日誌（引擎層）：數據的災後重新提交，物理日誌。包括兩部分，記憶體中的日誌快取（redo log buffer）（易失性）和磁碟上的重做日誌（redo log file）（持久性），需要將redo log buffer通過作業系統內核空間的OS buffer刷到磁碟上的log file中。

undo log：回滾日誌（引擎層）：主要用於數據修改的回滾，邏輯日誌。會給予與操作相反的語句，當事務回滾時從undo log中反向讀取內容。

MVCC：Multiversion concurrency control，多版本並發控制，實現並發和回滾的重要功能。它指的是資料庫中的每一條數據，會存在多個版本。對同一條數據而言，MySQL 會通過一定的手段（ReadView 機制）控制每一個事務看到不同版本的數據，這樣也就解決了不可重複讀的問題。

19、解決幻讀

在Innodb引擎中，每條聚集索引都會有兩個隱藏欄位：trx_id和roll_pointer，每次事務對一條記錄進行改動時，就會將事務id賦值給trx_id，並且會將舊數據寫入一條undo日誌，每條undo日誌都有roll_pointer屬性，可以將這些undo日誌都連起來，串成一個鏈表，undo日誌的寫入採用頭插法，新數據在前。

  • 快照讀： MVCC中有一個ReadView的概念，其中記錄了生成ReadView時的活躍事務id列表:m_ids、最小事務id:min_trx_id、將要分配給下一個事務的id:max_trx_id、生成ReadView的事務id:creator_trx_id。如果被訪問版本的trx_id與creator_trx_id相同或者小於min_trx_id，則可以訪問；如果被訪問版本的trx_id大於等於max_trx_id，則不能訪問；如果被訪問版本的trx_id在min_trx_id和max_trx_id之間，則當trx_id不在m_ids中時才能訪問。
  • 當前讀： InnoDB存儲引擎有三種鎖：Record lock：單個行記錄上的鎖；Gap lock：間隙鎖，鎖定一個範圍，不包括記錄本身；Next-key lock：record+gap 鎖定一個範圍，包含記錄本身。innodb對於行的查詢使用next-key lock，當查詢的索引含有唯一屬性時，將next-key lock降級為record key。

20、資料庫的索引機制

需要創建索引的情況：

  • 主鍵自動建立唯一索引；
  • 頻繁作為查詢條件的欄位；
  • 查詢與其它表關聯的欄位，外鍵關係建立索引；
  • 查詢中排序的欄位（通過索引訪問將大大提高排序速度）；
  • 查詢中統計或分組的欄位。

不需要創建索引的情況：

  • 表記錄太少；
  • 經常增刪改的表；
  • 頻繁更新的欄位；
  • where條件里用不到的欄位；
  • 數據重複且分布平均的欄位

21、explain關鍵字

id：選擇標識符；select_type：查詢的類型；table：輸出結果集的表；type：表的連接類型；possible_keys：可能使用的索引；key：實際使用的索引；key_len：索引欄位的長度；ref：列與索引的比較；rows：掃描出的行數；extra：執行情況的描述和說明。

22、索引優化思路

開啟慢查詢日誌設置閾值；explain做慢SQL分析。

查詢截取分析：using filesort效率低，using index效率高；最左前綴原則

23、索引創建規則

最左前綴匹配原則：mysql會一直向右匹配指導遇到範圍查詢（betwee、like）就停止查詢；選擇區分度高的列作為索引；選擇唯一性索引；盡量使用數據量少的索引；盡量使用前綴來索引；索引列不能參與計算；盡量的擴展索引不要新建索引；限制索引的數目。

24、索引過多的問題

一般一個表對應5個索引左右，索引過多會導致：查找數據變慢；對insert語句影響很大，尤其是無序插入；刪除數據多的情況下索引也需要更新；索引文件過大，佔用存儲空間，定址的查詢時間長；mysql優化器需要評估更多的組合。

Tags:

VirMach 便宜 VPS

QNews

QNews