2- 面試篇-資料庫

標籤：二叉樹   取值   跟蹤   高效   har   叢集   相關   view   尋找   

mysql內建1、視圖、使用情境

視圖是一種虛擬表，具有和物理表相同的功能。可以對視圖進行增，改，查，操作，
視圖通常是有一個表或者多個表的行或列的子集。
對視圖的修改會影響基本表。
它使得我們擷取資料更容易，相比多表查詢。

視圖的優缺點
優點：
1）對資料庫的訪問，因為視圖可以有選擇性的選取資料庫裡的一部分。
2 ）使用者通過簡單的查詢可以從複雜查詢中得到結果。
3 ）維護資料的獨立性，試圖可從多個表檢索資料。
4 ）對於相同的資料可產生不同的視圖。

缺點：
1）效能：查詢檢視時，必須把視圖的查詢轉化成對基本表的查詢，
如果這個視圖是由一個複雜的多表查詢所定義，那麼，那麼就無法更改資料
2）強耦合 ：我們程式中使用的sql過分依賴資料庫中的視圖

如下兩種情境一般會使用到視圖：
（1）不希望訪問者擷取整個表的資訊，只暴露部分欄位給訪問者，所以就建一個虛表，就是視圖。
（2）查詢的資料來源於不同的表，而查詢者希望以統一的方式查詢，這樣也可以建立一個視圖，把多個表查詢結果聯合起來，查詢者只需要直接從視圖中擷取資料，不必考慮資料來源於不同表所帶來的差異。
註：這個視圖是在資料庫中建立的 而不是用代碼建立的。

SQL語句

#文法： CREATE VIEW 視圖名稱 AS  SQL語句create view course_view as select * from course;  #建立表course的視圖

2、遊標

遊標實際上是一種能從包括多條資料記錄的結果集中每次提取一條記錄進行處理的機制。
遊標是對查詢出來的結果集作為一個單元來有效處理。
遊標可以定在該單元中的特定行，從結果集的當前行檢索一行或多行。
可以對結果集當前行做修改。
一般不使用遊標，但是需要逐條處理資料的時候，遊標顯得十分重要。

3、什麼是預存程序？有哪些優缺點？

預存程序包含了一系列先行編譯可執行檔sql語句，
預存程序存放於MySQL中，通過調用它的名字可以執行其內部的一堆sql
更加直白的理解：
預存程序可以說是一個記錄集，它是由一些T-SQL語句組成的代碼塊，
這些T-SQL語句代碼像一個方法一樣實現一些功能（對單表或多表的增刪改查），
然後再給這個代碼塊取一個名字，在用到這個功能的時候調用他就行了。

使用預存程序的優點：

1. 一個預存程序替代大量T_SQL語句 ，實現程式與sql解耦
2. 預存程序是先行編譯過的一個代碼塊，執行效率高。
3. 基於網路傳輸，傳別名的資料量小，而直接傳sql資料量大
4. 一定程度上確保資料安全，執行預存程序需要有一定許可權的使用者。

缺點

1. 程式員擴充功能不方便
2. 移植性差

程式與資料庫結合使用的三種方式

#方式一：    MySQL：預存程序    程式：調用預存程序#方式二：    MySQL：    程式：純SQL語句#方式三：    MySQL:    程式：類和對象，即ORM（本質還是純SQL語句）

使用預存程序

# 建立預存程序delimiter //create procedure p3(    in n1 int,    out res int)BEGIN    select * from blog where id > n1;    set res = 1;END //delimiter ;#在mysql中調用set @res=0; #0代表假（執行失敗），1代表真（執行成功）call p3(3,@res);select @res;#在python中基於pymysql調用cursor.callproc('p3',(3,0)) #0相當於set @res=0print(cursor.fetchall())    #查詢select的查詢結果cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一個參數，@p3_1代表第二個參數，即傳回值print(cursor.fetchall())

4、什麼是觸發器？

觸發器是一中特殊的預存程序，主要是通過事件來觸發而被執行的。
它可以強化約束，來維護資料的完整性和一致性，可以追蹤資料庫內的操作從而不允許未經許可的更新和變化。
可以聯級運算。如，某表上的觸發器上包含對另一個表的資料操作，而該操作又會導致該表觸發器被觸發。

使用觸發器可以定製使用者對錶進行【增、刪、改】操作時前後的行為，注意：沒有查詢
觸發器無法由使用者直接調用，而知由於對錶的【增/刪/改】操作被動引發的。

#建立觸發器delimiter //CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROWBEGIN    IF NEW.success = 'no' THEN #等值判斷只有一個等號            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必須加分號      END IF ; #必須加分號END//delimiter ;

觸發器的作用？
觸發器是一中特殊的預存程序，主要是通過事件來觸發而被執行的。
它可以強化約束，來維護資料的完整性和一致性，可以追蹤資料庫內的操作從而不允許未經許可的更新和變化。可以聯級運算。
如，某表上的觸發器上包含對另一個表的資料操作，而該操作又會導致該表觸發器被觸發。

5、事務：預存程序實現

事務（Transaction）是並發控制的基本單位。
所謂的事務，將某些操作的多個SQL作為原子性操作，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作單位。一旦有某一個出現錯誤，即可復原到原來的狀態，從而保證資料庫資料完整性。
比如銀行轉賬就是事務的典型情境。

四大特性

1. 原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗復原，
2. 一致性是指一個事務執行之前和執行之後都必須處於一致性狀態。 事務前後，資料總額一致
   
3. 隔離性是當多個使用者並發訪問資料庫時，比如操作同一張表時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作所幹擾，多個並發事務之間要相互隔離。
4. 持久性是指一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

據庫事務的三個常用命令
Begin Transaction、Commit Transaction、RollBack Transaction。

6、鎖：

在DBMS中，鎖是實現事務的關鍵，鎖可以保證事務的完整性和並發性。與現實生活中鎖一樣，它可以使某些資料的擁有者，在某段時間內不能使用某些資料或資料結構。當然鎖還分層級的。

樂觀鎖,自己實現，通過版本號碼
悲觀鎖：共用鎖定，多個事務，只能讀不能寫，加 lock in share mode
排它鎖，一個事務，只能寫，for update
行鎖

資料庫的樂觀鎖和悲觀鎖是什嗎？

資料庫管理系統（DBMS）中的並發控制的任務是確保在多個事務同時存取資料庫中同一資料時不破壞事務的隔離性和統一性以及資料庫的統一性。

開放式並行存取控制(樂觀鎖)和封閉式並行存取控制（悲觀鎖）是並發控制主要採用的技術手段。

• 悲觀鎖：假定會發生並發衝突，屏蔽一切可能違反資料完整性的操作
• 樂觀鎖：假設不會發生並發衝突，只在提交操作時檢查是否違反資料完整性。

7、索引

索引是什嗎？？
資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中資料。
索引相當於字典的音序表，如果要查某個字，如果不使用音序表，則需要從幾百頁中逐頁去查。
在資料之外，資料庫系統還維護著滿足特定尋找演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現進階尋找演算法。這種資料結構，就是索引。

索引有B+索引和hash索引，各自的區別
hash索引，等值查詢效率高，
不能排序
不能進行範圍查詢

B+索引
資料有序
範圍查詢

索引的實現通常使用B樹及其變種B+樹。

B+樹是通過二叉尋找樹，再由平衡二叉樹，B樹演化而來。

為表設定索引要付出代價的：
一是增加了資料庫的儲存空間，
二是在插入和修改資料時要花費較多的時間(因為索引也要隨之變動)。

建立索引可以大大提高系統的效能（優點）：

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

增加索引也有許多不利的方面：

• 建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
• 索引需要佔物理空間，除了資料表占資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。
• 當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，這樣就降低了資料的維護速度。

什麼時候【要】建立索引
-（1）表經常進行 SELECT 操作

• （2）表很大(記錄超多)，記錄內容分布範圍很廣
• （3）列名經常在 WHERE 子句或串連條件中出現

什麼時候【不要】建立索引

• （1）表經常進行 INSERT/UPDATE/DELETE 操作
• （2）表很小(記錄超少)
• （3）列名不經常作為串連條件或出現在 WHERE 子句中

一般來說，應該在這些列上建立索引：
（1）在經常需要搜尋的列上，可以加快搜尋的速度；
（2）在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
（3）在經常用在串連的列上，這些列主要是一些外鍵，可以加快串連的速度；
（4）在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
（5）在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
（6）在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

有些列不應該建立索引：

1. 對於那些在查詢中很少使用或者參考的列不應該建立索引。
2. 對於那些只有很少資料值的列也不應該增加索引。
3. 對於那些定義為text, image和bit資料類型的列不應該增加索引
4. 當修改效能遠遠大於檢索效能時，不應該建立索引。

使用索引查詢一定能提高查詢的效能嗎？為什麼
通常,通過索引查詢資料比全表掃描要快.但是我們也必須注意到它的代價.
索引需要空間來儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時,索引本身也會被修改.
這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5 次的磁碟I/O.
因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
使用索引查詢不一定能提高查詢效能,

索引範圍查詢(INDEX RANGE SCAN)適用於兩種情況:

• 基於一個範圍的檢索,一般查詢返回結果集小於表中記錄數的30%
• 基於非唯一性索引的檢索

#方式一create table t1(    id int,    name char,    age int,    sex enum('male','female'),    unique key uni_id(id),    index ix_name(name) #index沒有key);#方式二create index ix_age on t1(age);#方式三alter table t1 add index ix_sex(sex);

sql語句1、說一說三個範式。

第一範式（1NF）：資料庫表中的欄位都是單一屬性的，不可再分。
學生資訊表，有姓名、年齡、性別、學號等資訊組成
第二範式（2NF）：滿足第一範式,表中的欄位必須完全依賴於全部主鍵而非部分主鍵。
要有主鍵，要求其他欄位都依賴於主鍵。
第三範式（3NF）：滿足第二範式，非主鍵外的所有欄位必須互不依賴
就是資料只在一個地方儲存，不重複出現在多張表中，可以認為就是消除傳遞依賴
消除傳遞依賴，方便理解，可以看做是“消除冗餘”。
所謂傳遞函數依賴，指的是如 果存在"A → B → C"的決定關係，則C傳遞函數依賴於A。

2、 drop、delete與 truncate

簡單說一說區別
SQL中的drop、delete、truncate都表示刪除，但是三者有一些差別

1. delete和truncate只刪除表的資料不刪除表的結構
2. 速度,一般來說: drop> truncate >delete
3. delete語句是dml,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發.
4. truncate,drop是ddl, 操作立即生效,原資料不放到rollback segment中,不能復原. 操作不觸發trigger.
5. 安全性:小心使用drop 和truncate,尤其沒有備份的時候 ,不可復原

分別在什麼情境之下使用？

• 不再需要一張表的時候，用drop
• 想刪除部分資料行時候，用delete，並且帶上where子句
• 保留表而刪除所有資料的時候用truncate

3、列舉幾種表串連方式,有什麼區別？

• 內聯結（Inner Join）：匹配2張表中相關聯的記錄。
• 左外聯結（Left Outer Join）：除了匹配2張表中相關聯的記錄外，還會匹配左表中剩餘的記錄，右表中未匹配到的欄位用NULL表示。
• 右外聯結（Right Outer Join）：除了匹配2張表中相關聯的記錄外，還會匹配右表中剩餘的記錄，左表中未匹配到的欄位用NULL表示。
• 全外串連：串連的表中不匹配的資料全部會顯示出來。
• 交叉串連： 笛卡爾效應，顯示的結果是連結資料表數的乘積。

4. 什麼是資料庫約束,常見的約束有哪幾種?

資料庫約束用於保證資料庫表資料的完整性（正確性和一致性）。可以通過定義約束\索引\觸發器來保證資料的完整性。

主鍵約束：primary key；
外鍵約束：foreign key；
唯一約束：unique；
檢查約束：check；
空值約束：not null；
預設值約束：default；

5、如何描述多對多的關係?

在關係型資料庫中描述多對多的關係,需要建立第三張資料表。
比如學生選課,需要在學生資訊表和課程資訊表的基礎上,再建立選課資訊表,該表中存放學生Id和課程Id。

6、 列舉幾種常用的彙總函式?

Sum:求和?Avg:求平均數?Max:求最大值?Min:求最小值?Count:求記錄數

7、 超鍵、候選索引鍵、主鍵、外鍵分別是什嗎？

超鍵：在關係中能唯一標識元組的屬性集稱為關係模式的超鍵。
一個屬性可以為作為一個超鍵，多個屬性群組合在一起也可以作為一個超鍵。
超鍵包含候選索引鍵和主鍵。
候選索引鍵：是最小超鍵，即沒有冗餘元素的超鍵。
主鍵：資料庫表中對儲存資料對象予以唯一和完整標識的資料列或屬性的組合。
一個資料列只能有一個主鍵，且主鍵的取值不能缺失，即不可為空值（Null）。
外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

8、 SELECT語句關鍵字順序定義順序

SELECT DISTINCT <select_list>FROM <left_table><join_type> JOIN <right_table>ON <join_condition>WHERE <where_condition>GROUP BY <group_by_list>HAVING <having_condition>ORDER BY <order_by_condition>LIMIT <limit_number>

執行順序

(7)     SELECT (8)     DISTINCT <select_list>(1)     FROM <left_table>(3)     <join_type> JOIN <right_table>(2)     ON <join_condition>(4)     WHERE <where_condition>(5)     GROUP BY <group_by_list>(6)     HAVING <having_condition>(9)     ORDER BY <order_by_condition>(10)    LIMIT <limit_number>

查詢來自杭州，並且訂單數少於2的客戶。

select a.customer_id, count(b.order_id) as total_ordersfrom table1 as a left join table2 as bon a.customer_id = b.customer_idwhere a.city = 'hangzhou'group by a.customer_idhaving count(b.order_id) < 2order by total_orders desclimit 1;

資料庫最佳化的思路1、在資料庫中查詢語句速度很慢，如何最佳化？

1.建索引
2.減少表之間的關聯
3.最佳化sql，盡量讓sql很快定位元據，不要讓sql做全表查詢，應該走索引,把資料 量大的表排在前面
4.簡化查詢欄位，沒用的欄位不要，盡量返回少量資料
5.盡量用PreparedStatement來查詢，不要用Statement

2、資料庫最佳化的思路

1.SQL語句最佳化
1）應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
2）應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描， 如：select id from t where num is null
可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢：
select id from t where num=0
3）很多時候用 exists 代替 in 是一個好的選擇
4）用Where子句替換HAVING 子句 因為HAVING 只會在檢索出所有記錄之後才對結果集進行過濾

2.索引最佳化
看上文索引

3.資料庫結構最佳化
? 1）範式最佳化： 比如消除冗餘（節省空間的。。）

? 2）反範式最佳化：比如適當加冗餘等（減少join）

? 3）拆分表： 分區將資料在物理上分隔開，不同分區的資料可以制定儲存在處於不同磁碟上的資料檔案裡。這樣，當對這個表進行查詢時，只需要在表分區中進行掃描，而不必進行全表掃描，明顯縮短了查詢時間，另外處於不同磁碟的分區也將對這個表的資料轉送分散在不同的磁碟I/O，一個精心設定的分區可以將資料轉送對磁碟I/O競爭均勻地分散開。對資料量大的時時表可採取此方法。可按月自動建表分區。
4）拆分其實又分垂直分割和水平分割：

4.伺服器硬體最佳化

3、面試回答資料庫最佳化問題從以下幾個層面入手

（1）、根據服務層面：配置mysql效能最佳化參數；
（2）、從系統層面增強mysql的效能：最佳化資料表結構、欄位類型、欄位索引、分表，分庫、讀寫分離等等。
（3）、從資料庫層面增強效能：最佳化SQL語句，合理使用欄位索引。
（4）、從代碼層面增強效能：使用緩衝和NoSQL資料庫方式儲存，如MongoDB/Memcached/Redis來緩解高並發下資料庫查詢的壓力。
（5）、減少資料庫操作次數，盡量使用資料庫訪問驅動的批處理方法。
（6）、不常使用的資料移轉備份，避免每次都在海量資料中去檢索。
（7）、提升資料庫伺服器硬體設定，或者搭建資料庫叢集。
（8）、編程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查詢；Regex過濾（非法字串過濾）；

### 4、SQL常用命令：

CREATE TABLE Student( ID NUMBER PRIMARY KEY, NAME VARCHAR2(50) NOT NULL);    //建表 CREATE VIEW view_name AS Select * FROM Table_name;  //建視圖 Create UNIQUE INDEX index_name ON TableName(col_name);  //建索引 INSERT INTO tablename {column1,column2,…} values(exp1,exp2,…);  //插入 INSERT INTO Viewname {column1,column2,…} values(exp1,exp2,…);   //插入視圖實際影響表 UPDATE tablename SET name='zang 3' condition;   //更新資料 DELETE FROM Tablename WHERE condition;  //刪除 GRANT (Select,delete,…) ON (對象) TO USER_NAME [WITH GRANT OPTION];   //授權 REVOKE (許可權表) ON(對象) FROM USER_NAME [WITH REVOKE OPTION]     //撤權

5、關係型非關係型

? 比如 有一個學生的資料：
? 姓名：張三，性別：男，學號：12345，班級：二年級一班
? 還有一個班級的資料：
? 班級：二年級一班，班主任：李四

資料庫 類型	特性	優點	缺點
關係型資料庫 SQLite、Oracle、mysql	1、關係型資料庫，是指採用了關聯式模式來組織 資料的資料庫； 2、關係型資料庫的最大特點就是事務的一致性； 3、簡單來說，關聯式模式指的就是二維表格模型， 而一個關係型資料庫就是由二維表及其之間的聯絡所組成的一個資料群組織。	1、容易理解：二維表結構是非常貼近邏輯世界一個概念，關聯式模式相對網狀、層次等其他模型來說更容易理解； 2、使用方便：通用的SQL語言使得操作關係型資料庫非常方便； 3、易於維護：豐富的完整性(實體完整性、參照完整性和使用者定義的完整性)大大減低了資料冗餘和資料不一致的機率； 4、支援SQL，可用於複雜的查詢。	1、為了維護一致性所付出的巨大代價就是其讀寫效能比較差； 2、固定的表結構； 3、高並發讀寫需求； 4、海量資料的高效率讀寫；
非關係型資料庫 MongoDb、redis、HBase	1、使用索引值對儲存資料； 2、分布式； 3、一般不支援ACID特性； 4、非關係型資料庫嚴格上不是一種資料庫，應該是一種資料結構化儲存方法的集合。	1、無需經過sql層的解析，讀寫效能很高； 2、基於索引值對，資料沒有耦合性，容易擴充； 3、儲存資料的格式：nosql的儲存格式是key,value形式、文檔形式、圖片形式等等，文檔形式、圖片形式等等，而關係型資料庫則只支援基礎類型。	1、不提供sql支援，學習和使用成本較高； 2、無交易處理，附加功能bi和報表等支援也不好；

6、MYSQL的兩種儲存引擎區別（事務、鎖層級等等），各自的適用情境

MYISAM 不支援事務，不支援外鍵，表鎖，插入資料時，鎖定整個表，查表總行數時，不需要全表掃描
INNODB 支援事務，支援外鍵，行鎖，查表總行數時，全表掃描

2- 面試篇-資料庫