收藏！面試會用到的，MySQL常見的面試題
- php是最好的语言原創
- 2018-07-26 17:21:563015瀏覽
以下無論是面試或學習中都常會遇到的一些MySQL問題。範例SQL語句最佳化:1）應盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進行全表掃描。 2）應盡量避免在where 子句中對欄位進行null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：select id from t where num is null
#【相關專題推薦：mysql面試題（2020）】
1.主鍵超鍵候選鍵外鍵
#主鍵：
資料庫表中對儲存資料物件給予唯一且完整標識的資料列或屬性的組合。 一個資料列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。
超 鍵：
在關係中能唯一標識元組的屬性集稱為關係模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。 超鍵包含候選鍵和主鍵。
候選鍵：
是最小超鍵，即沒有冗餘元素的超鍵。
外 鍵：
在一個表中存在的另一個表的主鍵稱此表的外鍵。
2.資料庫事務的四個特性及意義
資料庫事務transanction正確執行的四個基本要素。 ACID,原子性(Atomicity)、一致性(Correspondence)、隔離性(Isolation)、持久性(Durability)。
原子性:整個事務中的所有操作，要麼全部完成，要麼全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
一致性:在交易開始之前和交易結束以後，資料庫的完整性限制沒有被破壞。
隔離性:隔離狀態執行事務，使它們好像是系統在給定時間內執行的唯一操作。如果有兩個事務，運作在相同的時間內，執行 相同的功能，事務的隔離性將確保每一事務在系統中認為只有該事務在使用系統。這種屬性有時稱為串列化，為了防止交易操作間的混淆，必須串行化或序列化請 求，使得在同一時間僅有一個請求用於同一資料。
持久性:在交易完成以後，該事務所對資料庫所做的更改便持久的保存在資料庫之中，並不會被回滾。
3.視圖的作用，視圖可以改變麼？
視圖是虛擬的表，與包含資料的表不一樣，視圖只包含使用時動態檢索資料的查詢；不包含任何列或資料。使用視圖可以簡化複雜的sql操作，隱藏具體的細節，保護資料；視圖建立後，可以使用與表相同的方式利用它們。
視圖不能被索引，也不能有關聯的觸發器或預設值，如果視圖本身內有order by 則對視圖再次order by將被覆寫。
建立視圖：create view XXX as XXXXXXXXXXXXXX;
對於某些視圖例如未使用聯結子查詢分組聚集函數Distinct Union等，是可以對其更新的，對視圖的更新將對基底表進行更新；但是視圖主要用於簡化檢索，保護數據，並不用於更新，大部分視圖都不可以更新。
4.drop,delete與truncate的區別
drop直接刪掉表truncate刪除表中數據，再插入時自增長id又從1開始delete刪除表中數據，可以加where字句。
（1） DELETE語句執行刪除的程序是每次從表中刪除一行，並且同時將該行的刪除操作作為交易記錄在日誌中保存以便進行回滾操作。 TRUNCATE TABLE 則一次性地從表中刪除所有的資料並不會把單獨的刪除操作記錄記入日誌保存，刪除行是不能恢復的。並且在刪除的過程中不會啟動與表格相關的刪除觸發器。執行速度快。
（2） 表和索引所佔空間。當表被TRUNCATE 後，這個表和索引所佔用的空間會恢復到初始大小，而DELETE操作不會減少表或索引所佔用的空間。 drop語句將表格所佔用的空間全釋放掉。
（3） 一般而言，drop > truncate > delete
（4） 應用範圍。 TRUNCATE 只能對TABLE；DELETE可以是table和view
（5） TRUNCATE 和DELETE只刪除數據，而DROP則刪除整個表（結構和數據）。
（6） truncate與不帶where的delete ：只刪除數據，而不刪除表的結構（定義）drop語句將刪除表的結構被依賴的約束（constrain),觸發器（trigger)索引（index );依賴該表的預存程序/函數將被保留，但其狀態會變成：invalid。
（7） delete語句為DML（data maintain Language),這個操作會被放到 rollback segment中,交易提交後才生效。如果有相應的 tigger,執行的時候將被觸發。
（8） truncate、drop是DLL（data define language),操作立即生效，原始資料不放到rollback segment中，不能回滾
（9） 在沒有備份情況下，謹慎使用drop 與truncate。要刪除部分資料行採用delete且注意結合where來約束影響範圍。回滾段要夠大。要刪除表用drop;若想保留表而將表中資料刪除，如果於交易無關，用truncate即可實現。如果和事務有關，或老師想觸發trigger,還是用delete。
（10） Truncate table 表名速度快,而且效率高,因為:
truncate table 在功能上與不帶WHERE 子句的DELETE 語句相同：二者都刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和交易日誌資源少。 DELETE 語句每次刪除一行，並在交易日誌中為所刪除的每行記錄一項。 TRUNCATE TABLE 透過釋放儲存表資料所使用的資料頁來刪除數據，並且只在交易記錄中記錄頁的釋放。
（11） TRUNCATE TABLE 刪除表中的所有行，但表格結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留識別計數值，請改用 DELETE。如果要刪除表定義及其數據，請使用 DROP TABLE 語句。
（12） 對於由 FOREIGN KEY 約束所引用的表，不能使用 TRUNCATE TABLE，而應使用不含 WHERE 子句的 DELETE 語句。由於 TRUNCATE TABLE 不記錄在日誌中，所以它不能啟動觸發器。
5.索引的工作原理及其種類
資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中數據。 索引的實作通常使用B樹及其變種B 樹。
在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找演算法。這種資料結構，就是索引。
為表格設定索引要付出代價的：一是增加了資料庫的儲存空間，二是在插入和修改資料時要花費較多的時間(因為索引也要隨之變動)。
圖展示了一個可能的索引方式。左邊是資料表，一共有兩列七筆記錄，最左邊的是資料記錄的實體位址（注意邏輯上相鄰的記錄在磁碟上也並不是一定物理相鄰的）。為了加快Col2的查找，可以維護一個右邊所示的二元查找樹，每個節點分別包含索引鍵值和一個指向對應資料記錄物理位址的指針，這樣就可以運用二元查找在O(log2n)的複雜度內取得到對應資料。
建立索引可以大幅提高系統的效能。
第一，透過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
第二，可以大幅加快資料的檢索速度，這也是建立索引的最主要的原因。
第三，可以加速表和表之間的連接，特別是在實現資料的參考完整性方面特別有意義。
第四，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五，透過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。
也許會有人要問：增加索引有這麼多的優點，為什麼不對表中的每一個欄位建立一個索引呢？因為，增加索引也有許多不利的面向。
第一，建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
第二，索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的實體空間，如果要建立叢集索引，那麼需要的空間就會更大。
第三，當表格中的資料增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度。
索引是建立在資料庫表中的某些欄位的上面。在建立索引的時候，應該考慮在哪些欄位上可以建立索引，在哪些欄位上不能建立索引。 一般來說，應該在這些列上建立索引：在經常需要搜尋的列上，可以加快搜尋的速度；在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。
同樣，對於有些列不應該建立索引。 一般來說，不應該建立索引的的這些欄位具有下列特點：
第一，對於那些在查詢中很少使用或參考的資料列不應該建立索引。這是因為，既然這些列很少使用到，因此有索引或無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增加了空間需求。
第二，對於那些只有很少資料值的資料列也不應該增加索引。這是因為，由於這些列的值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的數據行的比例很大。增加索引，並不能明顯加快檢索速度。
第三，對於那些定義為text, image和bit資料類型的欄位不應該增加索引。這是因為，這些列的資料量不是相當大，就是取值很少。
第四，當修改效能遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當修改效能遠大於檢索效能時，不應該建立索引。
根據資料庫的功能，可以在資料庫設計器中建立三種索引：唯一索引、主鍵索引和聚集索引。
唯一索引
唯一索引是不允許其中任何兩行具有相同索引值的索引。
當現有資料中存在重複的鍵值時，大多數資料庫不允許將新建立的唯一索引與資料表一起儲存。資料庫還可能防止新增將在表中建立重複鍵值的新資料。例如，如果在employee表中職員的姓(lname)上建立了唯一索引，則任何兩個員工都不能同姓。 主鍵索引 資料庫表常有一列或列組合，其值唯一標識表中的每一行。此列稱為表的主鍵。在資料庫關係圖中為表定義主鍵將自動建立主鍵索引，主鍵索引是唯一索引的特定類型。此索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時，它還允許對資料的快速存取。 聚集索引 在聚集索引中，表中行的物理順序與鍵值的邏輯（索引）順序相同。 一個表格只能包含一個聚集索引。
如果某索引不是聚集索引，則表格中行的物理順序與鍵值的邏輯順序不符。 與非聚集索引相比，聚集索引通常提供更快的資料存取速度。
局部性原理與磁碟預讀
由於儲存媒體的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的百分之分之一，因此為了提高效率，要盡量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論基礎是計算機科學中著名的局部性原理：當一個數據被用到時，其附近的數據也通常會馬上被使用。程式運行期間所需的資料通常比較集中。
由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有局部性的程式來說，預讀可以提高I/ O效率。
預讀的長度一般為頁（page）的整數倍數。頁是電腦管理記憶體的邏輯區塊，硬體及作業系統往往將主記憶體和磁碟儲存區分割為連續的大小相等的區塊，每個儲存區塊稱為一頁（在許多作業系統中，頁得大小通常為4k），主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主記憶體時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續運行。
B-/ Tree索引的效能分析
到這裡終於可以分析B-/ Tree索引的效能了。
上文說過一般使用磁碟I/O次數評價索引結構的優劣。先從B-Tree分析，根據B-Tree的定義，可知檢索一次最多需要存取h個節點。資料庫系統的設計者巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。為了達到這個目的，在實際實現B-Tree還需要使用以下技巧：
每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也儲存在一個頁裡，加之電腦儲存分配都是按頁對齊的，就實作了一個node只需一次I/O。
B-Tree中一次檢索最多需要h-1次I/O（根節點常駐記憶體），漸進複雜度為O(h)=O(logd N)。 一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常小（通常不超過3）。
而紅黑樹這種結構，h明顯要深的多。由於邏輯上很近的節點（父子）物理上可能很遠，無法利用局部性，所以紅黑樹的I/O漸進複雜度也為O(h)，效率明顯比B-Tree差很多。
綜上所述，以B-Tree作為索引結構效率是非常高的。
6.連接的種類
查詢分析器中執行：
–建表table1,table2：
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
如表
————————————————-
table1 | table2 |
————————————————-
id name |id score |
1 lee |1 90|
2 zhang| 2 100|
4 wang| 3 70|
————————————————-
以下皆在查詢分析器中執行
一、外連結
1.概念：包括左向外聯結、右向外聯結或完整外部聯結
2.左連接：left join 或left outer join
(1)左向外聯接的結果集包括LEFT OUTER 子句中指定的左表的所有行，而不僅僅是聯結列所符合的行。如果左表的某行在右表中沒有符合行，則在相關聯的結果集行中右表的所有選擇清單列均為空值(null)。
(2)sql 語句
select * from table1 left join table2 on table1.id=table2.id
————-結果————-
idnameidscore
——— ———————
1lee190
2zhang2100
4wangNULLNULL
——————————
註解：包含table1的所有子句，根據指定條件傳回table2相應的字段，不符合的以null顯示
3.右連接：right join 或right outer join
(1)右向外聯接是左向外聯接的反向聯接。將返回右表的所有行。如果右表的某行在左表中沒有符合行，則將為左表傳回空值。
(2)sql 語句
select * from table1 right join table2 on table1.id=table2.id
————-結果————-##idnameidscore
——— ———————
1lee190
2zhang2100
NULLNULL370
——————————
註解：包含table2的所有子句，根據指定條件傳回table1相應的字段，不符合的以null顯示
4.完整外部聯接:full join 或full outer join
(1)完整外部聯接返回左表和右表中的所有行。當某行在另一個表中沒有符合行時，則另一個表的選擇清單列包含空值。
如果表之間有匹配行，則整個結果集行包含基底表的資料值。 (2)sql 語句
select * from table1 full join table2 on table1.id=table2.id
————-結果————-
idnameidscore
——————————
1lee190
2zhang2100
4wangNULLNULL
NULLNULL370
——————————
#註解：傳回左右連接的和（見上左、右連接）
二、內連接
1.概念：內聯是用比較運算子比較要聯接列的值的聯結
2.內部連結：join 或inner join
3.sql 語句
select * from table1 join table2 on table1.id=table2.id
————-結果———— -
idnameidscore
——————————
1lee190
2zhang2100
——————————
註解：
只回傳符合條件的table1和table2的列
4.等價（與下列執行效果相同）
A:select a.*,b.* from table1 a,table2 b where a.id =b.id
B:select * from table1 cross join table2 where table1.id=table2.id (
註：cross join後加條件只能用where,不能用on)
三、交叉連結
(完全)
1.概念：沒有WHERE 子句的交叉連結將產生連結所涉及的表的笛卡爾積。第一個表格的行數乘以第二個表格的行數等於笛卡爾積結果集的大小。 （table1與table2交叉連接產生3*3=9筆記錄）
2.交叉連接：
cross join (不含條件where…)
3.sql語句
select * from table1 cross join table2
————-結果————-
idnameidscore
——————————
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370
——————————
註釋：回傳3*3 =9筆記錄，即笛卡兒積
4.等價（與下列執行效果相同）
A:select * from table1,table2
7.資料庫範式
1 第一範式（1NF）
在任何一個關係資料庫中，第一範式（1NF）是對關係模式的基本要求，不符合第一範式（1NF）的資料庫就不是關聯式資料庫。
所謂第一範式（1NF）是指資料庫表的每一列都是不可分割的基本資料項，同一列中不能有多個值，即實體中的某個屬性不能有多個值或不能有重複的屬性。如果出現重複的屬性，就可能需要定義一個新的實體，新的實體由重複的屬性構成，新實體與原實體之間為一對多關係。在第一範式（1NF）中表格的每一行只包含一個實例的資訊。簡而言之，第一範式就是無重複的列。
2 第二範式（2NF）
第二範式（2NF）是在第一範式（1NF）的基礎上建立起來的，即滿足第二範式（2NF）必須先滿足第一範式（1NF）。第二範式（2NF）要求資料庫表中的每個實例或行必須可以被惟一地區分。為實現區分通常需要為表格加上一個列，以儲存各個實例的惟一標識。這個惟一屬性列稱為主關鍵字或主鍵、主碼。
第二範式（2NF）要求實體的屬性完全依賴主關鍵字。所謂完全依賴是指不能存在只依賴主關鍵字一部分的屬性，如果存在，那麼這個屬性和主關鍵字的這一部分應該分離出來形成一個新的實體，新實體與原實體之間是一對多的關係。為實現區分通常需要為表格加上一個列，以儲存各個實例的惟一標識。簡而言之，第二範式就是非主屬性非部分依賴主關鍵字。
3 第三範式（3NF）
滿足第三範式（3NF）必須先滿足第二範式（2NF）。簡而言之，第三範式（3NF）要求一個資料庫表中不包含已在其它表中已包含的非主關鍵字資訊。例如，存在一個部門資訊表，其中每個部門有部門編號（dept_id）、部門名稱、部門簡介等資訊。那麼在員工資料表中列出部門編號後就不能再將部門名稱、部門簡介等與部門有關的資訊再加入員工資訊表中。如果不存在部門資訊表，則根據第三範式（3NF）也應該建立它，否則就會有大量的資料冗餘。簡而言之，第三範式就是屬性不依賴其它非主屬性。 （我的理解是消除冗餘）
8.資料庫優化的想法
這個我借鑒了慕課上關於資料庫優化的課程。
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）拆分其實又分垂直拆分和水平拆分： 案例： 簡單購物系統暫設涉及如下表： 1.產品表（資料量10w，穩定） 2.訂單表（資料量200w，且有成長趨勢） 3.使用者表（資料量100w，且有成長趨勢） 以mysql為例敘述下水平拆分和垂直拆分，mysql能容忍的數量級在百萬靜態資料可以到千萬 垂直拆分：解決問題：表與表之間的io競爭不解決問題：單表中資料量增長出現的壓力方案： 把產品表和用戶表放到一個server上訂單表單獨放到一個server上 水平分割： 解決問題：單表中資料量成長出現的壓力無法解決問題：表格與表格之間的io爭奪
方案： 使用者表格透過性別分割為男性使用者表和女用戶表訂單表透過已完成和完成中拆分為已完成訂單和未完成訂單產品表未完成訂單放一個server上已完成訂單錶盒男用戶表放一個server上女用戶表放一個server上(女的愛購物哈哈)
4.伺服器硬體優化
這個麼多花錢咯！
9.預存程序與觸發器的差異
觸發器與預存程序非常相似，觸發器也是SQL語句集，兩者唯一的差別是觸發器不能用EXECUTE語句調用，而是在使用者執行Transact-SQL語句時自動觸發（啟動）執行。觸發器是在一個修改了指定表中的資料時執行的預存程序。 通經常透過建立觸發器來強制實現不同表中的邏輯相關資料的參考完整性和一致性。 由於使用者無法繞過觸發器，所以可以用它來強制實施複雜的業務規則，以確保資料的完整性。觸發器不同於預存程序，觸發器主要是透過事件執行觸發而被執行的，而儲存過程可以透過儲存程序名稱名字而直接呼叫。當某一表進行諸如UPDATE、INSERT、DELETE這些操作時，SQLSERVER就會自動執行觸發器所定義的SQL語句，從而確保資料的處理必須符合這些SQL語句所定義的規則。
相關文章：
以上是收藏！面試會用到的，MySQL常見的面試題的詳細內容。更多資訊請關注PHP中文網其他相關文章！