MySQL面試題附答案-2019

　　 隨著技術的進步和發展,面試官們對面試者的要求越來越高,現在只要是後端開發的職位,面試肯定會問數據庫的相關知識,而mysql作為目前最為流行的免費的關係型資料庫管理技術，面試時問到與之相關的問題也就不足為奇了。那現在，我們就為大家手機了一些面試題目及答案。一起來看看吧。

推薦教學：MySQL入門影片

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(logdN)。一般實際應用中，出度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、数据库优化的思路

　　这个我借鉴了慕课上关于数据库优化的课程。

一、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 只会在检索出所有记录之后才对结果集进行过滤

二、索引优化

　　看上文索引

三、数据库结构优化

　　1）范式优化： 比如消除冗余（节省空间。。）

　　 2）反范式优化：比如适当加冗余等（减少join）

　　 3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
　　4）拆分其实又分垂直拆分和水平拆分： 案例： 简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺

　　方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

四、服务器硬件优化

　　这个么多花钱咯！

9、存储过程与触发器的区别

Triggers are very similar to stored procedures. Triggers are also sets of SQL statements. The only difference between the two is that triggers cannot be called with the EXECUTE statement, but are automatically triggered (activated) when the user executes a Transact-SQL statement. implement. A trigger is a stored procedure that is executed when data in a specified table is modified. Triggers are often created to enforce referential integrity and consistency of logically related data in different tables. Because users cannot bypass triggers, they can be used to enforce complex business rules to ensure data integrity. Triggers are different from stored procedures. Triggers are mainly executed through event execution triggers, while stored procedures can be called directly through the stored procedure name. When operations such as UPDATE, INSERT, and DELETE are performed on a certain table, SQLSERVER will automatically execute the SQL statements defined by the trigger, thereby ensuring that the data processing must comply with the rules defined by these SQL statements.

Original address: https://www.cnblogs.com/frankielf0921/p/5930743.html

以上是MySQL面試題附答案-2019的詳細內容。更多資訊請關注PHP中文網其他相關文章！