MySQL數(shù)據(jù)庫基礎知識點儲備（整理總結）

2615

本篇文章給大家?guī)砹岁P于mysql數(shù)據(jù)庫的相關知識，主要整理了數(shù)據(jù)庫基礎的一下知識點，包括索引、語法順序、執(zhí)行順序、存儲過程等等相關問題，希望對大家有幫助。

MySQL數(shù)據(jù)庫基礎知識點儲備（整理總結）

推薦學習：mysql

一、數(shù)據(jù)庫

1.1 事務

1.1.1 事務四個特性(ACID)

  • 原子性 （Atomicity）
    整個事務中的所要操作要么全部提交成功，要么全部失敗回滾。
  • 一致性（Consistency）
    保證數(shù)據(jù)庫中的數(shù)據(jù)操作之前和操作之后的一致性。（比如用戶多個賬戶之間的轉賬，但是用戶的總金額是不變的）
  • 隔離性（Isolation）
    隔離性要求一個事務對數(shù)據(jù)庫中數(shù)據(jù)的修改，在未提交完成前對于其它事務是不可見的。（即事務之間要串行執(zhí)行）
  • 持久性（Durability）
    持久性是指一個事務一旦被提交了，那么對數(shù)據(jù)庫中的數(shù)據(jù)改變就是永久性的，即便是在數(shù)據(jù)庫系統(tǒng)遇到故障的情況下也不會丟失提交事務的操作。

sql標準定義了四種隔離性：（下面隔離性是由低到高，并發(fā)性由高到低）

  • 未提交讀。
    最低的隔離等級，允許其他事務看到?jīng)]有提交的數(shù)據(jù)，會導致臟讀。
  • 已提交讀。
    由于數(shù)據(jù)庫是讀寫分離，事務讀取的時候獲取讀鎖，但是在讀完之后立即釋放，釋放讀鎖之后，就可能被其他事務修改數(shù)據(jù)，再進行讀是就發(fā)現(xiàn)前后讀取數(shù)據(jù)的結果不同，造成不可重復讀。（讀鎖不需要事務提交后釋放，而寫鎖需要事務提交后釋放。）
  • 可重復讀。
    所有被select獲取的數(shù)據(jù)都不能被修改，這樣就可以避免一個事務前后讀取不一致的情況。但是沒有辦法控制幻讀，因為這個時候其他事務不能更改所選的數(shù)據(jù)，但是可以增加數(shù)據(jù)；
  • 可串行化。
    所有事務一個接著一個執(zhí)行，這樣可以避免幻讀，對于基于鎖來實現(xiàn)并發(fā)控制的數(shù)據(jù)庫來說，串行化要求在執(zhí)行范圍查詢的時候，需要獲取范圍鎖，如果不是基于鎖實現(xiàn)并發(fā)控制的數(shù)據(jù)庫，則檢查到有違反串行操作的事務時，需回滾該事務。

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

隔離級別????????????臟讀??不可重復讀??幻讀
未提交讀（read uncommitted）?可能??可能?????可能
已提交讀（read committed）?不可能??可能?????可能
可重復讀（repeatable read）?不可能 ?不可能?????可能
可串行化（serializable）???不可能??不可能????不可能

總結：未提交讀會造成臟讀—>已提交讀解決臟讀，但會造成不可重復讀—>可重復讀解決讀取結果前后不一致的情況，但是造成幻讀（以前沒有，現(xiàn)在有）—>可串行化解決了幻讀，但是增加很多范圍鎖，可能會造成鎖超時；

1.1.2 臟讀、不可重復讀和幻讀

  • 臟讀（針對回滾的操作）：事務T1更新了一行記錄的內容，但是并沒有提交所做的修改，事務T2讀取更新后的行，然后T1執(zhí)行了回滾操作，取消了剛才所做的修改。現(xiàn)在T2讀取的行數(shù)就無效了（一個事務讀取了另一個事務）;
  • 不可重復讀（針對修改的操作）：事務T1讀取了一行記錄，緊接著T2修改了T1剛才讀取的那一行記錄，然后T1又再次讀取這行記錄，發(fā)現(xiàn)與剛才讀取的結果不同。
  • 幻讀（針對更新的操作）：事務T1讀取一條指定的where子句所返回的結果集，然后T2事務新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢條件。然后T1再次對表進行檢索，但又看到了T2插入的數(shù)據(jù)。（第一次沒看到，第二次看到了）

二、索引

2.1 索引特點

  1. 可以加快數(shù)據(jù)庫檢索速度；
  2. 只能創(chuàng)建在表上，不能創(chuàng)建到視圖上；
  3. 既可以直接創(chuàng)建又可以間接創(chuàng)建；
  4. 可以在優(yōu)化隱藏中使用索引；
  5. 使用查詢處理器執(zhí)行sql語句，在一個表上，一次只能使用一個索引。

2.1.1 索引優(yōu)點

  1. 創(chuàng)建唯一性索引，保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性；
  2. 大大加快數(shù)據(jù)檢索速度，這是創(chuàng)建索引的最主要原因；
  3. 加速數(shù)據(jù)庫表之間的鏈接，特別是在實現(xiàn)數(shù)據(jù)庫參考完整性方面特別有意義；
  4. 在使用分組和排序子句進行檢索時，同樣可以顯著減少查詢中分組和排序的時間；
  5. 通過使用索引，可以在查詢中使用優(yōu)化隱藏器，提高系統(tǒng)性能；

2.1.2 索引缺點

  1. 創(chuàng)建和維護索引耗費時間，這種時間隨著數(shù)量的增加而增加；
  2. 索引需要占用物理空間，除了數(shù)據(jù)表占用數(shù)據(jù)空間之外，每一個索引還要占用一定的物理空間，如果建立聚集索引，那么需要的空間就會更大；
  3. 當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也需要維護，降低數(shù)據(jù)維護速度；

2.2 索引分類

（1）普通索引(它沒有任何限制。)
（2）唯一性索引(索引列的值必須唯一，但允許有空值。)
（3）主鍵索引(一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引。)
（4）組合索引
（5）聚集索引 按照每張表的主鍵構造一顆B+樹，并且葉節(jié)點中存放著整張表的行記錄數(shù)據(jù)，因此也讓聚集索引的葉節(jié)點成為數(shù)據(jù)頁。
（6）非聚集索引(輔助索引)(頁節(jié)點不存放一整行記錄)。

2.3 索引失效

（1）如果條件中有or，即使其中有條件帶索引，也不會使用（盡量少用or）;
（2）Like查詢是以%開頭，例如SELECT * FROM mytable WHEREt Name like’%admin’;
（3）如果列類型是字符串，那一定要在條件中使用引號引起來，否則不會使用索引;

2.4 各引擎支持索引

MyISAM，InnoDB,Memonry三個常用mysql引擎類型比較:
索引???MyISAM索引???InnoDB索引???Memonry索引
B-tree索引??支持??????支持??????支持
Hash索引 ??不支持?????不支持?????支持
R-Tree索引??支持?????不支持?????不支持
Full-text索引?不支持????暫不支持?????不支持

2.5 數(shù)據(jù)庫中的索引結構

因為在使用二叉樹的時候，由于二叉樹的深度過大而造成I/O讀寫過于頻繁，進而導致查詢效率低下。因此采用多路樹結構，B樹的各種操作能使B樹保持較低的高度。

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

  • 1.樹中每個結點最多含有m個孩子（m>=2）;
  • 2.除根結點和葉子結點外，其他每個結點至少有（ceil（m/2））個孩子（其中ceil（x）是一個取上限的函數(shù)）；
  • 3.根結點至少有2個孩子（除非B樹只包含一個結點：根結點）;
  • 4.所有葉子結點都出現(xiàn)在同一層，葉子結點不包含任何關鍵字信息（可以看做是外部結點或查詢失敗的結點，指向這些結點的指針都為NULL）;（注：葉子結點只是沒有孩子和指向孩子的指針，這些結點也存在，也有元素，類似紅黑樹中，每一個null指針即當做葉子結點，只是沒畫出來而已）
    MySQL數(shù)據(jù)庫基礎知識點儲備（整理總結）
    B+樹
    MySQL數(shù)據(jù)庫基礎知識點儲備（整理總結）
    在什么情況下適合建立索引?
    （1）為經(jīng)常出現(xiàn)在關鍵字order by, group by, distinct后面的字段，建立索引;
    （2）在union等集合操作的結果集字段上建立索引，其建立索引的目的同上;
    （3）為經(jīng)常用作查詢選擇的字段，建立索引;
    （4）在經(jīng)常用做表鏈接的屬性上，建立索引;
    （5）考慮使用索引覆蓋，對數(shù)據(jù)很少被更新的表，如果用戶經(jīng)常只查詢其中的幾個字段，可以考慮在這幾個字段上建立索引，從而將表的掃描改變?yōu)樗饕膾呙琛?/li>

三、Mysql語法順序

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

select[distinct]、from、join（如left join）、on、where、group
by、having、union、order by、limit;

四、Mysql執(zhí)行順序

即在執(zhí)行時sql按照下面的順序進行執(zhí)行：

from、on、join、where、group by、having、select、distinct、union、order by
?group by要和聚合函數(shù)一起使用，

例如:

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

實現(xiàn)多表查詢(內連接)

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

使用select from where同樣可以實現(xiàn)

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';

六、建立多對多數(shù)據(jù)表關系

在數(shù)據(jù)庫中，如果兩個表的之間的關系為多對多的關系，如：“學生表和課程表”，一個學生可以選多門課，一門課也可以被多個學生選;根據(jù)數(shù)據(jù)庫的設計原則，應當形成第三張關聯(lián)表。
步驟1:創(chuàng)建三張數(shù)據(jù)表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) ) /**學生課程關聯(lián)表*/ CREATE TABLE Stu_Cour( sc_id INT AUTO_INCREMENT, stu_id INT , cour_id INT, PRIMARY KEY(sc_id) )

第二步:為Stu_Cour關聯(lián)表添加外鍵

/*添加外鍵約束*/ 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);

完成創(chuàng)建！

  • 注:為已經(jīng)添加好的數(shù)據(jù)表添加外鍵:
    -語法: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);

七、數(shù)據(jù)庫引擎（存儲引擎）

當你訪問數(shù)據(jù)庫時，不管是手工訪問，還是程序訪問，都不是直接讀寫數(shù)據(jù)庫文件，而是通過數(shù)據(jù)庫引擎去訪問數(shù)據(jù)庫文件。

以關系型數(shù)據(jù)庫為例，發(fā)SQL語句給數(shù)據(jù)庫引擎，數(shù)據(jù)庫引擎解釋SQL語句，提取出你需要的數(shù)據(jù)返回給你。因此，對訪問者來說，數(shù)據(jù)庫引擎就是SQL語句的解釋器。

7.1 MYISAM和InnoDB引擎的區(qū)別

主要區(qū)別：

  • MYISAM 是非事務安全型的，而InnoDB是事務安全型；
  • NYISAM鎖的粒度是表級鎖，而InnoDB支持行級鎖；
  • MYISAM支持全文本索引，而InnoDB不支持全文索引
  • MYISAM相對簡單，所以在效率上要優(yōu)于InnoDB,小型應用可以考慮使用MYISAM;
  • MYISAM表是保存成文件的形式，在跨平臺的數(shù)據(jù)轉移中使用MYISAM存儲會省去不少的麻煩;
  • （6）InnoDB表比MYISAM表更安全，可以在保證數(shù)據(jù)不丟失的情況下，切換非事務表到事務表；

應用場景：

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

八、數(shù)據(jù)庫范式

目前關系數(shù)據(jù)庫有6種范式：第一范式{1NF}，第二范式{2NF}，第三范式{3NF}，巴斯—科德范式{BCNF}，第四范式{4NF}，第五范式{5NF，又稱完美范式}。滿足最低要求的范式是第一范式。在第一范式的基礎上進一步滿足更多規(guī)范要求的稱為第二范式{2NF},其余范式依次類推，一般來說，數(shù)據(jù)庫只需滿足第三范式（3NF）就OK了。
范式：

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

推薦學習：mysql

? 版權聲明
THE END
數(shù)據(jù)庫
# 數(shù)據(jù)庫# mysql# sql# 字符串# 并發(fā)# select# table# NULL# 指針# union# r-tree
喜歡就支持一下吧
點贊15 分享