php資料庫基礎

事務

事務的四個特性(ACID)：

  原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）
- （1）原子性
整個事務中的所要操作要麼全部提交成功，要麼全部失敗復原。
- （2）一致性
保證資料庫中的資料操作之前和操作之後的一致性。（比如使用者多個賬戶之間的轉賬，但是使用者的總金額是不變的）
- （3）隔離性
隔離性要求一個事務對資料庫中資料的修改，在未提交完成前對於其它事務是不可見的。（即事務之間要串列執行）

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

1）未提交讀 最低的隔離等級，允許其他事務看到沒有提交的資料，會導致髒讀。

2）已提交讀
由於資料庫是讀寫分離，事務讀取的時候擷取讀鎖，但是在讀完之後立即釋放，釋放讀鎖之後，就可能被其他事務修改資料，再進行讀是就發現前後讀取資料的結果不同，造成不可重複讀取。（讀鎖不需要事務提交後釋放，而寫鎖需要事務提交後釋放）

3）可重複讀
所有被select擷取的資料都不能被修改，這樣就可以避免一個事務前後讀取不一致的情況。但是沒有辦法控制幻讀，因為這個時候其他事務不能更改所選的資料，但是可以增加資料；

4）可序列化
所有事務一個接著一個執行，這樣可以避免幻讀，對於基於鎖來實現並發控制的資料庫來說，序列化要求在執行範圍查詢的時候，需要擷取範圍鎖，如果不是基於鎖實現並發控制的資料庫，則檢查到有違反串列操作的事務時，需復原該事務。

總結：四個層級逐漸增強，每個層級解決問題，事務層級越高，效能越差，大多數環境下（read committed就可以用了）

隔離等級            髒讀  不可重複讀取  幻讀
未提交讀（read uncommitted） 可能  可能     可能
已提交讀（read committed） 不可能  可能     可能
可重複讀（repeatable read） 不可能  不可能     可能
可序列化（serializable）   不可能  不可能    不可能

總結：未提交讀會造成髒讀—>已提交讀解決髒讀，但會造成不可重複讀取—>可重複讀解決讀取結果前後不一致的情況，但是造成幻讀（以前沒有，現在有）—>可序列化解決了幻讀，但是增加很多範圍鎖，可能會造成鎖逾時；

（4）持久性 一旦事務提交，則其所做的修改就會永久儲存到資料庫中，此時即使系統崩潰，已提交的修改資料也不會丟失。

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

（1）髒讀（針對復原的操作）：事務T1更新了一行記錄的內容，但是並沒有提交所做的修改，事務T2讀取更新後的行，然後T1執行了復原操作，取消了剛才所做的修改。現在T2讀取的行數就無效了（一個事務讀取了另一個事務）;

（2）不可重複讀取（針對修改的操作）：事務T1讀取了一行記錄，緊接著T2修改了T1剛才讀取的那一行記錄，然後T1又再次讀取這行記錄，發現與剛才讀取的結果不同。

（3）幻讀（針對更新的操作）：事務T1讀取一條指定的where子句所返回的結果集，然後T2事務新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢條件。然後T1再次對錶進行檢索，但又看到了T2插入的資料。（第一次沒看到，第二次看到了）

索引

2.1 資料庫索引的優缺點以及什麼時候資料庫索引失效？

索引的特點

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

索引的優點

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

索引的缺點

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

索引分類

（1）普通索引(它沒有任何限制。)
（2）唯一性索引(索引列的值必須唯一，但允許有空值。)
（3）主鍵索引(一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時建立主鍵索引。)
（4）複合式索引
（5）叢集索引 按照每張表的主鍵構造一顆B+樹，並且分葉節點中存放著整張表的行記錄資料，因此也讓叢集索引的分葉節點成為資料頁。
（6）非叢集索引(輔助索引)(頁節點不存放一整行記錄)。

索引失效

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

各引擎支援索引：（核心弄懂B-Tree索引）

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

資料庫中的索引結構？什麼情況下適合建索引？

資料庫中的索引結構？
  因為在使用二叉樹的時候，由於二叉樹的深度過大而造成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

createprocedureprocedurebill()comment′查詢所有銷售情況′beginselectbillid,txtime,amtfromlmbill;end

delimiter ;
調用預存程序
call procedure_bill();
查看預存程序
show procedure status like ‘procedure_bill’;

在MySQL資料庫建立多對多的資料表關係

  在資料庫中，如果兩個表的之間的關係為多對多的關係，如：“學生表和課程表”，一個學生可以選多門課，一門課也可以被多個學生選;根據資料庫的設計原則，應當形成第三張關聯表。
步驟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最佳化

NO SQL 資料庫

常用的資料庫有哪些？Redis用過嗎？

常用的關聯式資料庫：
Mysql、SQLServer、Oracle
常用的無模式資料庫：
MongoDB, Merncached，Redis……
Redis
（1）Redis是一個速度非常快的非關聯式資料庫，可以儲存鍵（Key）與5種不同類型的值（value）之間的映射，可以將儲存在記憶體中的索引值對資料持久化到硬碟中。
（2）與Merncached相比
1）兩者都可以用於儲存索引值映射，彼此效能也相差無幾；
2）redis能夠自動以兩種不同的方式將資料寫入硬碟；
3）redis除了能儲存普通的字串鍵之外，還可以儲存其他4種資料結構，merncached只能儲存字串鍵；
4）redis既能作主要資料庫，也可以作為其他儲存系統的次要資料庫；

資料庫引擎（儲存引擎）

引擎是什嗎？
  當你訪問資料庫時，不管是手工訪問，還是程式訪問，都不是直接讀寫資料庫檔案，而是通過資料庫引擎去訪問資料庫檔案。
  以關係型資料庫為例，你發SQL語句給資料庫引擎，資料庫引擎解釋SQL語句，提取出你需要的資料返回給你。因此，對訪問者來說，資料庫引擎就是SQL語句的解譯器。

MYISAM和InnoDB引擎的區別

主要區別：
- （1）MYISAM 是非事務安全型的，而InnoDB是事務安全型；
- （2）NYISAM鎖的粒度是表級鎖，而InnoDB支援行級鎖；
- （3）MYISAM支援全文本索引，而InnoDB不支援全文索引
- （4）MYISAM相對簡單，所以在效率上要優於InnoDB,小型應用可以考慮使用MYISAM;
- （5）MYISAM表是儲存成檔案的形式，在跨平台的資料轉移中使用MYISAM儲存會省去不少的麻煩;
- （6）InnoDB表比MYISAM表更安全，可以在保證資料不丟失的情況下，切換非事務表到事務表；
應用情境：
- （1）MYISAM管理非事務表，它提供高速儲存和檢索，以及全文檢索搜尋能力，如果應用中需要執行大量的select查詢，那麼MYISAM是更好的選擇。
- （2）InnoDB用於交易處理應用程式，具有眾多特性，包括ACID事務支援。如果應用中需要執行大量的insert或update操作，則應該使用innodb，這樣可以提高多使用者並行作業的效能。

其他

資料庫中的範式有哪些

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

（1）1NF:確保每列保持原子性；

（2）2NF:確保表中的每列都和主鍵相關(聯合主鍵)；

（3）3NF:確保表中的每列都和主鍵直接相關(外鍵)；

（4）BCNF:在1NF基礎上，任何非主屬性不能對主鍵子集依賴（在3NF基礎上消除對主碼子集的依賴）；

（5）4NF:要求把同一表內的多對多關係刪除；

（6）5NF:從最終結構重建立立原始結構；

