MySQL資料庫基礎知識點儲備（整理總結）

這篇文章為大家帶來了關於mysql資料庫的相關知識，主要整理了資料庫基礎的一下知識點，包括索引、語法順序、執行順序、預存程序等等相關問題，希望對大家有幫助。

推薦學習：mysql教學

#一、資料庫

##1.1 交易

1.1.1 事務四個特性(ACID)

#原子性（
• Atomicity） 整個事務中的所要操作要麼全部提交成功，要麼全部失敗回滾。
  
一致性（
• Consistency） 保證資料庫中的資料操作之前和操作之後的一致性。 （例如用戶多個帳戶之間的轉賬，但是用戶的總金額是不變的）
  
隔離性（
• Isolation） 隔離性要求一個交易對資料庫中數據的修改，在未提交完成前對於其它事務是不可見的。 （即事務之間要串行執行）
  
持久性（
• Durability） 持久性是指一個事務一旦被提交了，那麼對資料庫中的資料改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會遺失提交交易的操作。
  

SQL標準定義了四個隔離：（下面隔離性是由低到高，並發性由高到低）

#未提交讀。
• 最低的隔離等級，允許其他交易看到沒有提交的數據，會導致髒讀。
  
已提交讀取。
• 由於資料庫是讀寫分離，事務讀取的時候獲取讀鎖，但是在讀完之後立即釋放，釋放讀鎖之後，就可能被其他事務修改數據，再進行讀是就發現前後讀取數據的結果不同，造成不可重複讀。 （讀鎖不需要交易提交後釋放，而寫鎖需要事務提交後釋放。）
  
可重複讀取。
• 所有被
  select取得的資料都不能被修改，這樣就可以避免一個事務前後讀取不一致的情況。但是沒有辦法控制幻讀，因為這個時候其他事務不能更改所選的數據，但是可以增加數據；
可串行化。
• 所有事務一個接著一個執行，這樣可以避免幻讀，對於基於鎖來實現並發控制的資料庫來說，串行化要求在執行範圍查詢的時候，需要獲取範圍鎖，如果不是基於鎖實現並發控制的資料庫，則檢查到有違反串行操作的事務時，需回滾該事務。
  

總結：四個層級逐漸增強，每個層級解決問題，事務層級越高，效能越差。

隔離等級      不可重複讀取  幻讀

未提交讀取（read uncommitted） _  幻讀
未提交讀取（read uncommitted） 檢測可能  幻讀
⃀⃀  可能     可能
可重複讀取（repeatable read） 不可能 不可能     可能
可串行化（serializable）   不可能  不可能    不可能被提交總結# ，但會造成不可重複讀—>可重複讀取解決讀取結果前後不一致的情況，但是造成幻讀（以前沒有，現在有）—>可串行化解決了幻讀，但是增加很多範圍鎖，可能會造成鎖定逾時；

1.1.2 髒讀、不可重複讀取和幻讀

• 髒讀（針對回滾的操作）：交易T1更新了一行記錄的內容，但是並沒有提交所做的修改，事務T2讀取更新後的行，然後T1執行了回滾操作，取消了剛才所做的修改。現在T2讀取的行數就無效了（一個事務讀取了另一個事務）;
• 不可重複讀取（針對修改的操作）：事務T1讀取了一行記錄，緊接著T2修改了T1剛才讀取的那一行記錄，然後T1又再讀這行記錄，發現與剛才讀取的結果不同。
• 幻讀（針對更新的操作）：事務T1讀取一條指定的where子句所傳回的結果集，然後T2事務新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢條件。然後T1再次對錶進行檢索，但又看到了T2插入的資料。 （第一次沒看到，第二次看到了）

二、索引

#2.1 索引特點

1. 可以加快資料庫檢索速度；
2. 只能創建在表上，不能創建到視圖上；
3. 既可以直接創建又可以間接創建；
4. 可以在優化隱藏中使用索引；
5. 使用查詢處理器執行sql語句，在一個表上，一次只能使用一個索引。

2.1.1 索引優點

1. #建立唯一性索引，保證資料庫表中每一行資料的唯一性；
2. 大大加快資料檢索速度，這是創建索引的最主要原因；
3. 加速資料庫表之間的鏈接，特別是在實現資料庫參考完整性方面特別有意義；
4. 在使用分組和排序子句進行檢索時，同樣可以顯著減少查詢中分組和排序的時間；
5. 透過使用索引，可以在查詢中使用最佳化隱藏器，提高系統效能；

#2.1.2 索引缺點

1. 建立和維護索引耗費時間，這種時間隨著數量的增加而增加；
2. #索引需要佔用實體空間，除了資料表佔用資料空間之外，每一個索引還要佔用一定的實體空間，如果建立聚集索引，那麼需要的空間就會更大；
3. #當對資料表中的資料進行增加、刪除和修改的時候，索引也需要維護，降低資料維護速度；

#2.2 索引分類

##（1）普通索引(它沒有任何限制。)

（2）唯一性索引(索引列的值必須唯一，但允許有空值。)
（3）主鍵索引(一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引。)
（4）組合索引
（5）聚集索引按照每張表的主鍵構造一顆B 樹，並且葉節點中存放著整張表的行記錄數據，因此也讓聚集索引的葉節點成為數據頁。
（6）非聚集索引(輔助索引)(頁節點不存放一整行記錄)。

2.3 索引失效

（1）如果條件中有or，即使其中有條件帶索引，也不會使用（盡量少用or）;

（2）Like查詢是以%開頭，例如SELECT * FROM mytable WHEREt Name like'�min';
（3）如果列類型是字串，那一定要在條件中使用引號引起來，否則不會使用索引;

2.4 各引擎支援索引

MyISAM，InnoDB,Memonry三個常用MySQL引擎類型比較: 索引   MyISAM索引   InnoDB索引   Memonry索引
B-tree索引  支援   ⃀指標⃀⃀⃀支援     不支援     支援
R-Tree索引  支援     不支援     不支援
Full-text索引 不支援    暫不支援        暫不支援        暫不支援#pac ，由於二元樹的深度過大而造成I/O讀寫過於頻繁，進而導致查詢效率低。因此採用多路樹結構，B樹的各種操作能使B樹保持在較低的高度。

B樹又叫平衡多路查找樹，一棵m階的B樹特性如下：

• 1.樹中每個結點最多含有m個孩子（m>=2）;
• 2.除根結點和葉子結點外，其他每個結點至少有（ceil（m/2））個孩子（其中ceil（x）是一個取上限的函數）；
• 3.根結點至少有2個孩子（除非B樹只包含一個結點：根結點）;
• 4.所有葉子結點都出現在同一層，葉子結點不包含任何關鍵字資訊（可以看做是外部結點或查詢失敗的結點，指向這些結點的指針都為null）;（註：葉子結點只是沒有孩子和指向孩子的指針，這些結點也存在，也有元素，類似紅黑樹中，每一個null指針即當做葉子結點，只是沒畫出來而已）
  
  B 樹
  
  在什麼情況下適合建立索引?
  （1）為經常出現在關鍵字order by, group by, distinct後面的字段，建立索引;
  （2）在union等集合操作的結果集字段上建立索引，其建立索引的目的同上;
  （3）為經常用作查詢選擇的字段，建立索引;
  （4）在經常用做表鏈接的屬性上，建立索引;
  （5）考慮使用索引覆蓋，對數據很少被更新的表，如果用戶經常只查詢其中的幾個字段，可以考慮在這幾個字段上建立索引，從而將表的掃描更改為索引的掃描。

三、Mysql語法順序

即當sql中存在下面的關鍵字時，它們要保持這樣的順序：

##select[distinct ]、from、join（如left join）、on、where、group

by、having、union、order by、limit;

四、Mysql執行順序

即在執行時sql依照下面的順序執行：

from、on、join、where、group by、having、select、distinct、union、order by

 group by要和聚合函數一起使用，

例如:

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer=’Bush’ or a.Customer = ‘Adams’ group by a.Customer;

實作多表查詢(內連接)

select u.uname,a.addr from lm_user u inner join lm_addr a on u.uid = a.uid;

使用

select from where同樣可以實現

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

五、預存程序

delimiter $$
create procedure procedure_bill()
comment '查询所有销售情况'
begin
select billid, tx_time, amt from lm_bill;
end $$
delimiter ;

呼叫預存程序

call procedure_bill();

查看預存程序

show procedure status like 'procedure_bill';

六、建立多對多資料表關係

在資料庫中，如果兩個表的之間的關係為多對多的關係，如：“學生表和課程表”，一個學生可以選多門課，一門課也可以被多個學生選;根據資料庫的設計原則，應形成第三張關聯表。

步驟1:建立三張資料表Student ,Course,Stu_Cour

/**学生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
CREATE TABLE Course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**学生课程关联表*/
CREATE TABLE Stu_Cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)

第二步:為Stu_Cour關聯表新增外鍵

/*添加外键约束*/
ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES Student(stu_id);
ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES Course(cour_id);

完成建立！

註:為已經新增好的資料表新增外鍵:
• -語法:
  alter table 表名add constraint FK_ID foreign key(你的外鍵欄位名稱) REFERENCES 外表表名(對應的表的主鍵字段名);

例:

alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);

七、資料庫引擎（儲存引擎）

當你訪問資料庫時，不管是手動訪問，還是程式訪問，都不是直接讀寫資料庫文件，而是透過資料庫引擎去存取資料庫文件。

以關聯式資料庫為例，發SQL語句給資料庫引擎，資料庫引擎解釋SQL語句，提取出你需要的資料回傳給你。因此，對訪客來說，資料庫引擎就是SQL語句的解釋器。

7.1 MYISAM和InnoDB引擎的區別

#主要區別：

• MYISAM 是非事務安全型的，而InnoDB是事務安全型；
• NYISAM鎖定的粒度是表格級鎖定，而InnoDB支援行級鎖定；
• MYISAM支援全文本索引，而InnoDB不支援全文索引
• MYISAM相對簡單，所以在效率上優於InnoDB,小型應用可以考慮使用MYISAM;
• #MYISAM表是保存成檔案的形式，在跨平台的資料轉移中使用MYISAM儲存會省去不少的麻煩;
（6）
• InnoDB表比MYISAM表更安全，可以在保證資料不在遺失的情況下，切換非交易表到交易表；

應用場景：

• MYISAM管理非事務表，它提供高速儲存和檢索，以及全文搜尋能力，如果應用程式中需要執行大量的select查詢，那麼 MYISAM是更好的選擇。
• InnoDB用於事務處理應用程序，具有眾多特性，包括ACID事務支援。如果應用程式中需要執行大量的insert或update操作，則應該使用innodb，這樣可以提高多用戶並發操作的效能。

八、資料庫範式

目前關聯式資料庫有6種範式：第一個範式{1NF}，第二範式{2NF}，第三範式{3NF}，巴斯—科德範式{BCNF}，第四範式{4NF}，第五範式{5NF，又稱完美範式}。滿足最低要求的範式是第一個範式。在第一範式的基礎上進一步滿足更多規範要求的稱為第二範式{2NF},其餘範式依次類推，一般來說，資料庫只需滿足第三範式（3NF）就OK了。
範式：

• 1NF:確保每列保持原子性；
• 2NF:確保表中的每列都和主鍵相關(聯合主鍵);
• #3NF:確保表中的每列都和主鍵直接相關(外鍵)；
• BCNF:在1NF基礎上，任何非主屬性不能對主鍵子集依賴（在3NF基礎上消除對主碼子集的依賴）;
• 4NF:要求把同一表內的多對多關係刪除；
• 5NF:從最終結構重新建立原始結構；

推薦學習：mysql影片教學

以上是MySQL資料庫基礎知識點儲備（整理總結）的詳細內容。更多資訊請關注PHP中文網其他相關文章！