首頁  >  文章  >  資料庫  >  MySQL資料庫基礎知識點儲備(整理總結)

MySQL資料庫基礎知識點儲備(整理總結)

WBOY
WBOY轉載
2022-03-02 17:43:293002瀏覽

這篇文章為大家帶來了關於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 各引擎支援索引

MyISAMInnoDB,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指針即當做葉子結點,只是沒畫出來而已)
    MySQL資料庫基礎知識點儲備(整理總結)
    B 樹
    MySQL資料庫基礎知識點儲備(整理總結)
    在什麼情況下適合建立索引?
    (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事務支援。如果應用程式中需要執行大量的insertupdate操作,則應該使用innodb,這樣可以提高多用戶並發操作的效能。

八、資料庫範式

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

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

推薦學習:mysql影片教學

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

陳述:
本文轉載於:csdn.net。如有侵權,請聯絡admin@php.cn刪除