MySOL程序員面試分類真題1

MySOL程序員面試分類真題1一、選擇題1.

下列關(guān)于關(guān)系模型的術(shù)語中，所表達的概念與二維表中的“行”的概念最接近的術(shù)語是______A.屬性B.關(guān)系C.域D.元組正確答案：D[解析]二（江南博哥）維表中的“行”即關(guān)系模型中的“元組”，二維表中的“列”即關(guān)系模型中的“屬性”。

本題中，對于選項A，屬性作為表中的列的概念。所以，選項A錯誤。

對于選項B，關(guān)系代表的是表和表之間的聯(lián)系。所以，選項B錯誤。

對于選項C，域和選項A中的屬性是一致的。所以，選項C錯誤。

對于選項D，二維表中的“行”即關(guān)系模型中的“元組”。

所以，本題的答案為D。

2.

在一個關(guān)系R中，如果每個數(shù)據(jù)項都是不可再分割的，那么R一定屬于______A.第一范式B.第二范式C.第三范式D.第四范式正確答案：A[解析]例如，帖子表中只能出現(xiàn)發(fā)帖人的ID，不能同時出現(xiàn)發(fā)帖人的ID與發(fā)帖人的姓名，否則，只要出現(xiàn)同一發(fā)帖人ID的所有記錄，它們中的姓名部分都必須嚴格保持一致，這就是數(shù)據(jù)冗余。

本題中，在一個關(guān)系R中，若每個數(shù)據(jù)項都是不可再分割的，那么根據(jù)前面的解析應(yīng)該屬于第一范式。

所以，本題的答案為A。

3.

一個關(guān)系模式為Y(X1，X2，X3，X4)，假定該關(guān)系存在著如下函數(shù)依賴：(X1，X2)→X3，X2→X4，則該關(guān)系屬于______A.第一范式B.第二范式C.第三范式D.第四范式正確答案：A[解析]對于本題而言，這個關(guān)系模式的候選鍵為{X1，X2}，因為X2→X4，說明有非主屬性X4部分依賴于候選鍵{X1，X2}，所以，這個關(guān)系模式不為第二范式。

4.

4：如果關(guān)系模式R所有屬性的值域中每一個值都不可再分解，并且R中每一個非主屬性完全函數(shù)依賴于R的某個候選鍵，那么R屬于______

A．第一范式(INF)

B．第二范式(2NF)

C．第三范式(3NF)D．BCNF范式正確答案：B[解析]如果關(guān)系R中所有屬性的值域都是單純域，那么關(guān)系模式R是第一范式。符合第一范式的特點有：1)有主關(guān)鍵字；2)主鍵不能為空；3)主鍵不能重復(fù)；4)字段不可以再分。如果關(guān)系模式R是第一范式，而且關(guān)系中每一個非主屬性完全函數(shù)依賴于主鍵，那么稱關(guān)系模式R屬于第二范式。很顯然，本題中的關(guān)系模式R滿足第二范式的定義。所以，選項B正確。

5.

事務(wù)所具有的特性有______A.原子性B.一致性C.隔離性D.持久性正確答案：ABCD

6.

事務(wù)的持久性是指______A.事務(wù)中包括的所有操作要么都做，要么不做B.事務(wù)一旦提交，對數(shù)據(jù)庫的改變是永久的C.一個事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的D.事務(wù)必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)正確答案：B

7.

下列SQL語句中，哪個選項可為用戶ZHANGSAN分配數(shù)據(jù)庫USERDB中數(shù)據(jù)表USERINFO的查詢和插入數(shù)據(jù)權(quán)限?______A.GRANTSELECT,INSERTONUSERDB.USERINFOTO'ZHANGSAN'@'LOCALHOST';B.GRANT'ZHANGSAN'@'LOCALHOST'TOSELECT,INSERTFORUSERDB.USERINFO;C.GRANTSELECT,INSERTONUSERDB.USERINFOFOR'ZHANGSAN'@'LOCALHOST';D.GRANT'ZHANGSAN'@'LOCALHOST'TOUSERDB.USERINFOONSELECT,INSERT;正確答案：A[解析]賦予權(quán)限的SQL語句：GRANT[權(quán)限]ON[TABLE]TO'USERNAME'@'LOCALHOST';。

本題中，對于選項A，語法正確。所以，選項A正確。

對于選項B，權(quán)限應(yīng)該在GRANT之后。所以，選項B錯誤。

對于選項C，最后的關(guān)鍵詞是TO而不是FOR。所以，選項C錯誤。

對于選項D，最后的關(guān)鍵詞是TO而不是ON。所以，選項D錯誤。

所以，本題的答案為A。

8.

SQL語句應(yīng)該考慮哪些安全性?正確答案：SQLSQLROOTSQL

二、簡答題1.

完成以下MySQL的題目要求。

1)在數(shù)據(jù)庫job下創(chuàng)建workInfo表。創(chuàng)建表的同時在id字段上創(chuàng)建名為index_id的唯一性索引，而且以降序的格式排列。workInfo表內(nèi)容如下所示：字段名字段描述數(shù)據(jù)類型主鍵外鍵非空唯一自增id編號INT(10)是否是是是name職位名稱VARCHAR(20)否否是否否type職位類別VARCHAR(10)否否否否否address工作地址VARCHAR(50)否否否否否wages工資INT否否否否否contents工作內(nèi)容TINYTEXT否否否否否extra附加信息TEXT否否否否否

2)使用CREATEINDEX語句為name字段創(chuàng)建長度為10的索引index_name。

3)使用ALTERTABLE語句在type和address上創(chuàng)建名為index_t的索引。

4)將workinfo表的存儲引擎更改為MyiSAM類型。

5)使用ALTERTABLE語句在extra字段上創(chuàng)建名為index_ext的全文索引。

6)刪除workInfo表的唯一性索引index_id。正確答案：操作如下所示：

①先查看是否存在job數(shù)據(jù)庫。如果存在，用USE語句選擇job數(shù)據(jù)庫。如果不存在，用CREATEDATABASE語句創(chuàng)建該數(shù)據(jù)庫。然后，用CREATETABLE語句創(chuàng)建workInfo表，SQL代碼如下：

CREATETABLEworkinfo(idINT(10)NOTNULLUNIQUEPRIMARYKEYAUTO_INCREMENT,

nameVARCHAR(20)NOTNULL,

typeVARCHAR(10),

addressVARCHAR(50),

telVARCHAR(20),

wagesINT,

contentsTINYTEXT,

extraTEXT,

UNIQUEINDEXindex_id(idDESC)

);

②使用CREATEINDEX語句為name字段創(chuàng)建長度為10的索引index_name。代碼如下：

CREATEINDEXindex_nameONworkInfo(name(10));

③使用ALTERTABLE語句在type和address上創(chuàng)建名為index_t的索引。代碼如下：

ALTERTABLEworkInfoADDINDEXindex_t(type,address);

④使用ALTERTABLE語句將workInfo表的存儲引擎更改為MyISAM類型。代碼如下：

ALTERTABLEworkInfoENGINE=MyISAM;

⑤使用ALTERTABLE語句在extra字段上創(chuàng)建名為index_ext的全文索引。代碼如下：

ALTERTABLEworkInfoADD

FULLTEXTINDEXindex_ext(extra);

⑥使用DROP語句刪除workInfo表的唯一性索引index_id。代碼如下：

DROPINDEXindex_idONworkInfo;

2.

簡單描述在MySQL中，索引、唯一索引、主鍵、聯(lián)合索引的區(qū)別，它們對數(shù)據(jù)庫的性能有什么影響。正確答案：索引、唯一索引、主鍵、聯(lián)合索引的區(qū)別如下所示：

1)索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。普通索引(由關(guān)鍵字KEY或INDEX定義的索引)的唯一任務(wù)是加快對數(shù)據(jù)的訪問速度。

2)唯一索引：普通索引允許被索引的數(shù)據(jù)列包含重復(fù)的值，如果能確定某個數(shù)據(jù)列只包含彼此各不相同的值，在為這個數(shù)據(jù)索引創(chuàng)建索引的時候就應(yīng)該用關(guān)鍵字UNIQE把它定義為一個唯一索引，唯一索引可以保證數(shù)據(jù)記錄的唯一性。

3)主鍵，一種特殊的唯一索引，在一張表中只能定義一個主鍵索引，逐漸用于唯一標識一條記錄，是用關(guān)鍵字PRIMARYKEY來創(chuàng)建。

4)聯(lián)合索引：索引可以覆蓋多個數(shù)據(jù)列，例如INDEX索引，這就是聯(lián)合索引。

索引可以極大地提高數(shù)據(jù)的查詢速度，但是會降低插入刪除更新表的速度，因為在執(zhí)行這些寫操作時，還需要操作索引文件。

3.

在表中建立了索引以后，導(dǎo)入大量數(shù)據(jù)為什么會很慢?正確答案：對已經(jīng)建立了索引的表中插入數(shù)據(jù)時，插入一條數(shù)據(jù)就要對該記錄按索引排序。因此，當導(dǎo)入大量數(shù)據(jù)的時候，速度會很慢。解決這種情況的辦法是，在沒有任何索引的情況插入數(shù)據(jù)，然后建立索引。

4.

如何分析一條SQL語句的執(zhí)行性能?需要關(guān)注哪些信息?正確答案：使用EXPLAIN命令，通過觀察TYPE列，就可以知道是否使用了全表掃描，同時也可以知道索引的使用形式，通過觀察KEY可以知道使用了哪個索引，通過觀察KEY_LEN可以知道索引是否使用完成，通過觀察ROWS可以知道掃描的行數(shù)是否過多，通過觀察EXTRA可以知道是否使用了臨時表以及是否使用了額外的排序操作。

5.

有兩個復(fù)合索引(A,B)和(C,D)，以下語句會怎樣使用索引?可以做怎樣的優(yōu)化?

SELECT*FROMTABWHERE(A=?ANDB=?)OR(C=?ANDD=?)正確答案：根據(jù)MySQL的機制，只會使用到一個篩選效果好的復(fù)合索引，可以做如下優(yōu)化：

SELECT*FROMTABWHEREA=?ANDB=?

UNION

SELECT*FROMTABWHEREC=?ANDD=?;

6.

請簡述項目中優(yōu)化SQL語句執(zhí)行效率的方法。正確答案：可以從以下幾個方面進行優(yōu)化：

1)盡量選擇較小的列。

2)將WHERE中用的比較頻繁的字段建立索引。

3)SELECT子句中避免使用'*'。

4)避免在索引列上使用計算，NOT、IN和＜＞等操作。

5)當只需要一行數(shù)據(jù)的時候使用limit1。

6)保證表單數(shù)據(jù)不超過200W，適時分割表。

7)針對查詢較慢的語句，可以使用explain來分析該語句具體的執(zhí)行情況。

7.

如何提高INSERT的性能?正確答案：可以從如下幾方面考慮：

1)合并多條INSERT為一條，即：insertintotvalues(a,b,c),(d,e,f),,,

主要原因是多條INSERT合并后寫日志的數(shù)量(MySQL的binlog和innodb的事務(wù)讓日志)減少了，因此降低了日志刷盤的數(shù)據(jù)量和頻率，從而提高效率。通過合并SQL語句，同時也能減少SQL語句解析的次數(shù)，減少網(wǎng)絡(luò)傳輸?shù)腎/O。

2)修改參數(shù)bulk_insert_buffer_size，調(diào)大批量插入的緩存。

3)設(shè)置innodb_flush_log_at_trx_commit=0，相對于innodb_flush_log_at_trx_commit=1可以十分明顯的提升導(dǎo)入速度。需要注意的是，innodb_flush_log_at_trx_commit參數(shù)對InnoDBLog的寫入性能有非常關(guān)鍵的影響。該參數(shù)可以設(shè)置為0、1、2，解釋如下：

①0：logbuffer中的數(shù)據(jù)將以每秒一次的頻率寫入到logfile中，且同時會進行文件系統(tǒng)到磁盤的同步操作，但是每個事務(wù)的commit并不會觸發(fā)任何logbuffer到lognle的刷新或者文件系統(tǒng)到磁盤的刷新操作：

②1：在每次事務(wù)提交的時候?qū)ogbuffer中的數(shù)據(jù)都會寫入到logfile，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步：

③2：事務(wù)提交會觸發(fā)logbuffer到logfile的刷新，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外，每秒會有一次文件系統(tǒng)同步操作到磁盤。

4)手動使用事務(wù)。因為MySQL默認是autocommit的，這樣每插入一條數(shù)據(jù)，都會進行一次commit；所以，為了減少創(chuàng)建事務(wù)的消耗，可用手工使用事務(wù)，即：

STARTTRANSACTION;

insert...

insert...

commit;

即執(zhí)行多個INSERT后再一起提交；一般執(zhí)行1000條INSERT提交一次。

8.

max_connect_errors參數(shù)的作用是什么?正確答案：max_connect_errors參數(shù)表示如果MySQL服務(wù)器連續(xù)接收到了來自于同一個主機的請求，且這些連續(xù)的請求全部都沒有成功的建立連接就被斷開了，當這些連續(xù)的請求的累計值大于max_connect_errors的設(shè)定值時，MySQL服務(wù)器就會阻止這臺主機后續(xù)的所有請求。相關(guān)的登錄錯誤信息會記錄到performance_schema.host_cache表中。遇到這種情況，可以通過清空hostcache來解決，具體的清空方法是執(zhí)行flushhosts或者在MySQL服務(wù)器的shell里執(zhí)行mysqladminflush-hosts操作，其實就是清空performance_schema.host_cache表中的記錄。參數(shù)max_connect_errors從MySQL5.6.6開始默認值為100，小于該版本時默認值為10。當這一客戶端成功連接一次MySQL服務(wù)器后，針對此客戶端的max_connect_errors會清零。

若max_connect_errors的設(shè)置過小，則網(wǎng)頁可能提示無法連接數(shù)據(jù)庫服務(wù)器；而通過SSH的mysql命令連接數(shù)據(jù)庫，則會返回類似于下面的錯誤：

ERROR1129(00000):Host'gateway'isblockedbecauseofmanyconnectionerrors;unblockwith'mysqladminflush-hosts'

一般來說建議數(shù)據(jù)庫服務(wù)器不要監(jiān)聽來自網(wǎng)絡(luò)的連接，而是僅僅使用sock連接，這樣可以防止絕大多數(shù)針對MySQL的攻擊；如果必須要開啟MySQL的網(wǎng)絡(luò)連接，則最好設(shè)置此值，以防止窮舉密碼的攻擊手段。

9.

狀態(tài)變量Max_used_connections的作用是什么?正確答案：系統(tǒng)狀態(tài)變量Max_used_connections是指從這次MySQL服務(wù)啟動到現(xiàn)在，同一時刻并行連接數(shù)的最大值。它不是指當前的連接情況，而是一個比較值。如果在過去某一個時刻，MySQL服務(wù)同時有1000個請求連接過來，而之后再也沒有出現(xiàn)這么大的并發(fā)請求時，那么Max_used_connections=1000。

10.

參數(shù)wait_timeout和interactive_timeout的作用和區(qū)別是什么?正確答案：interactive_timeout表示MySQL服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù)；wait_timeout表示MySQL服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)。這2個參數(shù)的默認值都是28800，單位秒，即8個小時。需要注意的是，這2個參數(shù)需要同時設(shè)置才會生效。

參數(shù)interactive_timeout針對交互式連接，wait_timeout針對非交互式連接。所謂的交互式連接，即在mysql_real_connect()函數(shù)中使用了CLIENT_INTERACTIVE選項。說得直白一點就是，通過MySQL客戶端連接數(shù)據(jù)庫是交互式連接，通過jdbc連接數(shù)據(jù)庫是非交互式連接。其實，針對Client，真正生效的是會話級別的wait_timeout，空閑連接(交互和非交互)超過其會話級別的wait_timeout時間就會被回收掉。在客戶端連接啟動的時候，根據(jù)連接的類型，來確認會話變量wait_timeout的值是繼承于全局變量wait_timeout，還是interactive_timeout。對于非交互式連接，類似于jdbc連接，wait_timeout的值繼承自服務(wù)器端全局變量wait_timeout。對于交互式連接，類似于MySQL客戶單連接，wait_timeout的值繼承自服務(wù)器端全局變量interactive_timeout。

可通過查看showprocesslist輸出中Sleep狀態(tài)的時間來判斷一個連接的空閑時間。若超過wait_timeout的值，則會報類似于如下的錯誤：

mysql＞setsessionWAIT_TIMEOUT=3;

QueryOK,0rowsaffected(0.00sec)

mysql＞showprocesslist;

ERROR2013(N000):LostconnectiontoMySQLserverduringquery

mysql＞

mysql＞showprocesslist;

ERROR2006(HY000):MySQLserverhasgoneaway

Noconnection.Tryingtoreconnect...

Connectionid:

50

Currentdatabase:lhrdb

11.

設(shè)有一個記錄各個球隊隊員每場比賽進球數(shù)的關(guān)系模式R(隊員編號，比賽場次，進球數(shù)，球隊名，隊長名)。如果規(guī)定，每個隊員只能屬于一個球隊，每個球隊只有一個隊長。

1)試寫出關(guān)系模式R的基本函數(shù)依賴和主碼。

2)說明R不是2NF模式的理由，并把R分解成2NF。

3)進而將R分解成3NF，并說明理由。正確答案：關(guān)系模式R的基本函數(shù)依賴F如下：

F={隊員編號→球隊名，球隊名→隊長名，(隊員編號，比賽場次)→進球數(shù)}

其主鍵為(隊員編號，比賽場次)。

1)R不是2NF模式的原因是隊員編號→球隊名，所以(隊員編號，比賽場次)→球隊名是一個部分函數(shù)依賴關(guān)系，將R分解成2NF如下：

RI={隊員編號，球隊名，隊長名}

R2={球隊名，比賽場次，進球數(shù)}

2)由于在R1中，主鍵為隊員編號，所以隊員編號→隊長名是一個傳遞函數(shù)依賴，將R分解成：

R11={隊員編號，球隊名}，R12={球隊名，隊長名}

則將R分解為R11，R12，R2后均為3NF的關(guān)系模式。

反范式

數(shù)據(jù)庫設(shè)計要嚴格遵守范式，這樣設(shè)計出來的數(shù)據(jù)庫，雖然思路很清晰，結(jié)構(gòu)也很合理，但是，有時候卻要在一定程度上打破范式設(shè)計。因為范式越高，設(shè)計出來的表可能越多，關(guān)系可能越復(fù)雜，但是性能卻不一定會很好，因為表一多，就增加了關(guān)聯(lián)性。特別是在高可用的OLTP數(shù)據(jù)庫中，這一點表現(xiàn)得很明顯，所以就引入了反范式。

不滿足范式的模型，就是反范式模型。反范式跟范式所要求的正好相反，在反范式的設(shè)計模式中，可以允許適當?shù)臄?shù)據(jù)冗余，用這個冗余可以縮短查詢獲取數(shù)據(jù)的時間。反范式其本質(zhì)上就是用空間來換取時間，把數(shù)據(jù)冗余在多個表中，當查詢時就可以減少或者避免表之間的關(guān)聯(lián)。反范式技術(shù)也可以稱為反規(guī)范化技術(shù)。

反范式的優(yōu)點：減少了數(shù)據(jù)庫查詢時表之間的連接次數(shù)，可以更好地利用索引進行篩選和排序，從而減少了I/O數(shù)據(jù)量，提高了查詢效率。

反范式的缺點：數(shù)據(jù)存在重復(fù)和冗余，存在部分空間浪費。另外，為了保持數(shù)據(jù)的一致性，則必須維護這部分冗余數(shù)據(jù)，因此增加了維護的復(fù)雜性。所以，在進行范式設(shè)計時，要在數(shù)據(jù)一致性與查詢之間找到平衡點，因為符合業(yè)務(wù)場景的設(shè)計才是好的設(shè)計。

在RDBMS模型設(shè)計過程中，常常使用范式來約束模型，但在NoSQL模型中則大量采用反范式。常見的數(shù)據(jù)庫反范式技術(shù)包括：

●增加冗余列：在多個表中保留相同的列，以減少表連接的次數(shù)。冗余法以空間換取時間，把數(shù)據(jù)冗余在多個表中，當查詢時可以減少或者是避免表之間的關(guān)聯(lián)。

●增加派生列：表中增加可以由本表或其他表中數(shù)據(jù)計算生成的列，減少查詢時的連接操作并避免計算或使用集合函數(shù)。

●表水平分割：根據(jù)一列或多列的值將數(shù)據(jù)放到多個獨立的表中，主要用于表的規(guī)模很大、表中數(shù)據(jù)相對獨立或數(shù)據(jù)需要存放到多個介質(zhì)的情況。

●表垂直分割：對表按列進行分割，將主鍵和一部分列放到一個表中，主鍵與其他列放到另一個表中，在查詢時減少I/O次數(shù)。

舉例，有學(xué)生表與課程表，假定課程表要經(jīng)常被查詢，而且在查詢中要顯示學(xué)生的姓名，則查詢語句為：

SELECTCODE,NAME,SUBJECTFROMCOURSEC,STUDENTSWHERES.ID=C.CODEWHERECODE=?

如果這個語句被大范圍、高頻率執(zhí)行，那么可能會因為表關(guān)聯(lián)造成一定程度的影響，現(xiàn)在，假定評估到學(xué)生改名的需求是非常少的，那么，就可以把學(xué)生姓名冗余到課程表中。注意：這里并沒有省略學(xué)生表，只不過是把學(xué)生姓名冗余在了課程表中，如果萬一有很少的改名需求，只要保證在課程表中改名正確即可。

那么，修改以后的語句可以簡化為：

SELECTCODE,NAME,SUBJECTFROMCOURSECWHERECODD=?

范式和反范式的對比如下表所示：模型優(yōu)點缺點范式化模型數(shù)據(jù)沒有冗余，更新容易當表的數(shù)量比較多，查詢設(shè)計需要很多關(guān)聯(lián)模型(Join)時，會導(dǎo)致查詢性能低下反范式化模型數(shù)據(jù)冗余將帶來很好的讀取性能(因為不需要Join很多表，而且通常反范式模型很少做更新操作)需要維護冗余數(shù)據(jù)，從目前NoSQL的發(fā)展可以看到，對磁盤空間的消耗是可以接受的

12.

CAP定理和一般事務(wù)中的ACID特性中的一致性有什么區(qū)別?正確答案：一般事務(wù)ACID中的一致性是有關(guān)數(shù)據(jù)庫規(guī)則的描述，如果數(shù)據(jù)表結(jié)構(gòu)定義一個字段值是唯一的，那么一致性系統(tǒng)將解決所有操作中導(dǎo)致這個字段值非唯一性的情況，如果帶有一個外鍵的一行記錄被刪除，那么其外鍵相關(guān)記錄也應(yīng)該被刪除，這就是ACID一致性意思。

CAP理論的一致性是保證同一個數(shù)據(jù)在所有不同服務(wù)器上的拷貝都是相同的，這是一種邏輯保證，而不是物理，因為網(wǎng)絡(luò)速度限制，在不同服務(wù)器上這種復(fù)制是需要時間的，集群通過阻止客