MySOL程序員面試分類真題4

MySOL程序員面試分類真題4一、簡(jiǎn)答題1.

MySQL有哪幾類物理文件?正確答案：MySQL數(shù)據(jù)庫(kù)的文件包括：

1)參數(shù)文件：f。

2)日志文件，包括錯(cuò)誤日志、查詢?nèi)罩?、慢（江南博哥）查詢?nèi)罩?、二進(jìn)制日志。

3)MySQL表文件：用來存放MySQL表結(jié)構(gòu)的文件，一般以.frm為后綴。

4)Socket文件：當(dāng)用Unix域套接字方式進(jìn)行連接時(shí)需要的文件。

5)pid文件：MySQL實(shí)例的進(jìn)程ID文件。

6)存儲(chǔ)引擎文件：每個(gè)存儲(chǔ)引擎都有自己的文件夾來保存各種數(shù)據(jù)，這些存儲(chǔ)引擎真正存儲(chǔ)了數(shù)據(jù)和索引等數(shù)據(jù)。

2.

MySQL如何實(shí)現(xiàn)插入時(shí)如果不存在則插入，如果存在則更新的操作?正確答案：在Oracle中有MERGEINTO來實(shí)現(xiàn)記錄已存在就更新的操作，mysql沒有MERGEINTO語法，但是有REPLACEINTO的寫法，同樣實(shí)現(xiàn)記錄已存在就更新的操作。

SQLServer中的實(shí)現(xiàn)方法是：

ifnotexists(select1fromtwhereid=1)

insertintot(id,update_time)values(1,getdate())

else

updatetsetupdate_time=getdate()whereid==1

MySQL的REPLACEINTO有3種形式：

其中，“INTO”關(guān)鍵字可以省略，不過最好加上“INTO”，這樣意思更加直觀。另外，對(duì)于那些沒有給予值的列，MySQL將自動(dòng)為這些列賦上默認(rèn)值。

3.

與Oracle相比，MySQL最明顯的優(yōu)勢(shì)是什么?正確答案：與Oracle這款昂貴的付費(fèi)軟件相比，MySQL是開源軟件，可以免費(fèi)使用。另外，相比Oracle而言，MySQL是非常輕巧的。

4.

如果MySQL密碼丟了，那么如何找回密碼?正確答案：步驟如下：

1)關(guān)閉MySQL，/data/3306/mysqlstop或pkillmysqld。

2)mysqld_safe--defaults-file=/data/3306/f--skip-grant-table&。

3)mysql-uroot-p-s/data/3306/mysql.sock,按＜ENTER＞鍵進(jìn)入。

4)修改密碼，UPDATEmysql.userSETpassword=PASSWORD("oldlhr123")WHEREuser='root'andhost='localhost';。

5.

如何區(qū)分FLOAT和DOUBLE數(shù)據(jù)類型?正確答案：FLOAT和DOUBLE都表示帶小數(shù)的數(shù)值類型。FLOAT表示單精度浮點(diǎn)數(shù)值，占用4個(gè)字節(jié)。DOUBLE表示雙精度浮點(diǎn)數(shù)值，占用8個(gè)字節(jié)。

6.

在MySQL中如何有效的刪除一個(gè)大表?正確答案：在Oracle中對(duì)于大表的刪除可以通過先TRUNCATE+REUSESTORAGE參數(shù)，再使用DEALLOCATE逐步縮小，最后DROP掉表。在MySQL中，對(duì)于大表的刪除，可以通過建立硬鏈接(HardLink)的方式來刪除。建立硬鏈接的方式如下所示：

Inbig_table.ibdbig_table.ibd.hdlk

建立硬鏈接之后就可以使用DROPTABLE刪除表了，最后在OS級(jí)別刪除硬鏈接的文件即可。

為什么通過這種方式可以快速刪除呢?當(dāng)多個(gè)文件名同時(shí)指向同一個(gè)INODE時(shí)，此時(shí)這個(gè)INODE的引用數(shù)N＞1，刪除其中任何一個(gè)文件都會(huì)很快。因?yàn)槠渲苯拥奈锢砦募K沒有被刪除，只是刪除了一個(gè)指針而已。當(dāng)INODE的引用數(shù)N=1時(shí)，刪除文件時(shí)需要把與這個(gè)文件相關(guān)的所有數(shù)據(jù)塊清除，所以會(huì)比較耗時(shí)。

7.

在Linux下安裝MySQL有哪幾種方式?它們的優(yōu)缺點(diǎn)各有哪些?正確答案：在Windows下可以使用NOINSTALL包和圖形化包來安裝，在Linux下可以使用如下3種方式來安裝：

1rowinset(0.00sec)

查詢結(jié)果顯示，id字段是主鍵，而且有唯一性約束。8.

在name字段創(chuàng)建名為indexname的單列索引，索引長(zhǎng)度為10。

正確答案：在name字段創(chuàng)建名為index_name的索引

使用CREATEINDEX語句創(chuàng)建index_name索引。代碼執(zhí)行如下：

mysql＞CREATEINDEXindex_nameONinformation(name(10));

QueryOK,0rowsaffected(0.02sec)

Records:0Duplicates:0Warnings:0

結(jié)果顯示index_name索引創(chuàng)建成功。

9.

在birthday和address字段是創(chuàng)建名為indexbir的多列索引，然后判斷索引的使用情況。

正確答案：創(chuàng)建名為index_bir的多列索引

使用CREATEINDEX語句創(chuàng)建index_bir索引。代碼執(zhí)行如下：

mysql＞CREATEINDEXindex_bitONinformation(birthday,address);

QueryOK,0rowsaffected(0.02sec)

Records:0Duplicates:0Warnings:0

結(jié)果顯示，index_bir索引創(chuàng)建成功。

10.

用ALTERTABLE語句在id字段上創(chuàng)建名為indexid的唯一性索引，而且以升序排列。

正確答案：用ALTERTABLE語句創(chuàng)建名為index_id的唯一性索引

使用ALTERTABLE語句創(chuàng)建index_id索引。代碼執(zhí)行如下：

mysql＞ALTERTABLEinformationADDINDEXindex_id(idASC);

QueryOK,0rowsaffected(0.02sec)

Records:0Duplicates:0Warnings:0

結(jié)果顯示index_id索引創(chuàng)建成功。執(zhí)行SHOWCREATETABLE語句來查看information表的結(jié)構(gòu)。執(zhí)行結(jié)果如下：

mysql＞SHOWCREATETABLEinformation\G

***************************1.row***************************

Table:information

CreateTable:CREATETABLE'information'(

'id'int(10)NOTNULLAUTO_INCREMENT,

'name'varchar(20)NOTNULL,

'sex'varchar(4)NOTNULL,

'birthday'datetimeDEFAULTNULL,

'address'varchar(50)DEFAULTNULL,

'tel'varchar(20)DEFAULTNULL,

'pic'blob,

PRIMARYKEY('id'),

UNIQUEKEY'id'('id')

KEY'index_name'('name'(10)),

KEY'index_bir'('birthday','address'),

KEY'index_id'('id')

)ENGINE=InnoDBDEFAULTCHARSET=utf8

1rowinset(0.00sec)

執(zhí)行結(jié)果顯示，information表中已經(jīng)存在index_name、index_bir和index_id等3個(gè)索引。

11.

刪除user表上的indexuser索引。

正確答案：刪除user表上的index_user索引

執(zhí)行DROP語句可以刪除user表上的索引。代碼執(zhí)行結(jié)果如下：

mysql＞DROPINDEXindex_userONuser;

QueryOK,0rowsaffected(0.14sec)

Records:0Duplicates:0Warnings:0

結(jié)果顯示刪除成功。執(zhí)行SHOWCREATETABLE語句來查看user表的結(jié)構(gòu)。執(zhí)行結(jié)果如下：

mysql＞SHOWCREATETABLEuser\G

***************************1.row***************************

Table:user

CreateTable:CREATETABLE'user'(

'userid'int(10)NOTNULLAUTO_INCREMENT,

'usemame'varchar(20)NOTNULL,

'passwd'varchar(20)NOTNULL,

'info'text,

PRIMARYKEY('usend'),

UNIQUEKEY'userid'('userid'),

UNIQUEKEY'index_uid'('userid'),

FULLTEXTKEY'index_info'('info')

)ENGINE=MyISAMDEFAULTCHARSET=utf8

1rowinset(0.00sec)

結(jié)果顯示，index_user索引已經(jīng)不存在了。

12.

刪除information表上的indexname索引。

正確答案：刪除information表上的index_name索引

執(zhí)行DROP語句可以刪除information表上的index_name索引。代碼執(zhí)行結(jié)果如下：

mysql＞DROPINDEXindex_nameONinformation;

QueryOK,0rowsaffected(0.03sec)

Records:0Duplicates:0Warnings:0

結(jié)果顯示，刪除成功。執(zhí)行SHOWCREATETABLE語句來查看information表的結(jié)構(gòu)。執(zhí)行結(jié)果如下：

mysql＞SHOWCREATETABLEinformation\G

***************************1.row***************************

Table:infonnation

CreateTable:CREATETABLE'infonnation'(

'id'int(10)NOTNULLAUTO_INCREMENT,

'name'varchar(20)NOTNULL,

'sex'varchar(4)NOTNuLL.

'birthday'datetimeDEFAULTNULL,

'address'varchar(50)DEFAULTNULL,

'tel'verchar(20)DEFAULTNULL,

'pic'blob,

PRIMARYKEY('id'),

UNIQUEKEY'id'('id'),

KEY'index_bir'('birthday','address'),

KEY'index_id'('id')

)ENGINE=InNODBDEFAULTCHARSET=utf8

1rowinset(0.00sec)

結(jié)果顯示，index_name索引已經(jīng)不存在了。

13.

MySQL和Oracle如何修改命令提示符?正確答案：MySQL的默認(rèn)提示符為“mysql”，可以使用prompt命令來修改，如下所示：

全局：exportMYSQL_PS1="(\u@\h)[\d]＞"

當(dāng)前會(huì)話：prompt(\u@\h)[\d]\R:\m:\s＞\_

其中，“\u”代表用戶名，“\h”代表服務(wù)器地址，“\d”代表當(dāng)前數(shù)據(jù)庫(kù)，“\R:\m:\s”代表時(shí)分秒，例如：23:10:10。

Oracle的默認(rèn)命令提示符為“SQL”，可以使用“SETSQLPROMPT”命令來修改，如下所示：

SQL＞SHOWSQLPROMPT

sqlprompt"SQL＞"

SQL＞SETSQLPROMPT"_USER'@'_COYNECT_IDENTIFIER＞"

SYS@lhrdb＞

SYS@lhrdb＞SHOWSQLPROMPT

sqlprompt"_user'@'_connect_identifier＞"

在以上結(jié)果中，SYS表示用戶，lhrdb表示數(shù)據(jù)庫(kù)。注意，以上提示符的“＞”后有一個(gè)空格。

如果想全局生效，那么可以修改文件：$ORACLE_HOME/sqlplus/admin/glogin.sql。在glogin.sql文件中添加如下的內(nèi)容：

SETSQLPROMPT"_USER'@'_CONNECT_IDENTIFIER＞"

這樣，每次登錄SQL*Plus的時(shí)候，SQL提示符就會(huì)變?yōu)樵O(shè)置的內(nèi)容。

14.

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

15.

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

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

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

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

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

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

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

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

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

STARTTRANSACTION;

insert...

insert...

commit;

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

16.

如何從mysqldump工具中備份的全庫(kù)備份文件中恢復(fù)某個(gè)庫(kù)和某張表?正確答案：恢復(fù)某個(gè)庫(kù)可以使用--one-database(簡(jiǎn)寫-o)參數(shù)，如下所示：

全庫(kù)備份：

[root@rhel61hr～]#mysqldump-uroot-P--single-transaction-A--master-data=2＞dump.sql

只還原erp庫(kù)的內(nèi)容：

[root@rhel61hr～]#mysql-uroot-pMANAGERerp--one-database＜dump.sql

那么如何從全庫(kù)備份中抽取某張表呢，可以用全庫(kù)恢復(fù)，再恢復(fù)某張表即可。但是，對(duì)于小庫(kù)還可以，大庫(kù)就很麻煩了，所以，此時(shí)可以利用正則表達(dá)式來進(jìn)行快速抽取，具體實(shí)現(xiàn)方法如下：

從全庫(kù)備份中抽取出t表的表結(jié)構(gòu)：

[root@HEl～]#sed-e'/./{H;$!d;}'-e'x;/CREATETABLE't'/!d;q'dump.sql

DROPTABLEIFEXISTS't';

/*!40101SET@saved_cs_client=@@character_set_client*/;

/*!40101SETcharacter_set_client=utf8*/;

CREATETABLE't'(

'id'int(10)NOTNULLAUTO_INCREMENT,

'age'tinyint(4)NOTNULLDEFAULT'0',

'name'varchar(30)NOTNULLDEFAULT".

PRIMARYKEY('id')

)ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8;

/*!40101SETcharacter_set_client=@saved_cs_client*/;

再?gòu)娜珟?kù)備份中抽取出t表的內(nèi)容：

[root@HEl～]#grep'INSERTINTO't''dump.sql

INSERTINTO't'VALUES(0,0,"),(1,0,'aa'),(2,0,'bbb'),(3,25,'helei');

17.

MySQL的注釋符號(hào)有哪些?正確答案：MySQL注釋符有三種：

1)#...

2)"--..."(注意一后面有一個(gè)空格)

3)/*...*/

18.

MySQL有關(guān)權(quán)限的表有哪幾個(gè)?正確答案：MySQL服務(wù)器通過權(quán)限表來控制用戶對(duì)數(shù)據(jù)庫(kù)的訪問，權(quán)限表存放在mysql數(shù)據(jù)庫(kù)里，由mysql_install_db腳本初始化。這些權(quán)限表包括user、db、tables_priv、columns_priv、procs_priv和host。MySQL啟動(dòng)的時(shí)候讀取這些信息到內(nèi)存中去，或者在權(quán)限變更生效的時(shí)候，重新讀取到內(nèi)存中去。這些表的作用如下所示：

1)user：記錄允許連接到服務(wù)器的用戶賬號(hào)信息，里面的權(quán)限是全局級(jí)的。

2)db：記錄各個(gè)賬號(hào)在各個(gè)數(shù)據(jù)庫(kù)上的操作權(quán)限。

3)tables_priv：記錄數(shù)據(jù)表級(jí)的操作權(quán)限。

4)columns_priv：記錄數(shù)據(jù)列級(jí)的操作權(quán)限。

5)host：配合db權(quán)限表對(duì)給定主機(jī)上數(shù)據(jù)庫(kù)級(jí)操作權(quán)限作更細(xì)致的控制。這個(gè)權(quán)限表不受GRANT和REVOKE語句的影響。

6)procs_priv：規(guī)定誰可以執(zhí)行哪個(gè)存儲(chǔ)過程。

19.

在MySQL里如何執(zhí)行OS命令?正確答案：可以通過system或“\!”來執(zhí)行。

20.

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

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

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

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

F={隊(duì)員編號(hào)→球隊(duì)名，球隊(duì)名→隊(duì)長(zhǎng)名，(隊(duì)員編號(hào)，比賽場(chǎng)次)→進(jìn)球數(shù)}

其主鍵為(隊(duì)員編號(hào)，比賽場(chǎng)次)。

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

RI={隊(duì)員編號(hào)，球隊(duì)名，隊(duì)長(zhǎng)名}

R2={球隊(duì)名，比賽場(chǎng)次，進(jìn)球數(shù)}

2)由于在R1中，主鍵為隊(duì)員編號(hào)，所以隊(duì)員編號(hào)→隊(duì)長(zhǎng)名是一個(gè)傳遞函數(shù)依賴，將R分解成：

R11={隊(duì)員編號(hào)，球隊(duì)名}，R12={球隊(duì)名，隊(duì)長(zhǎng)名}

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

反范式

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

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

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

反范式的缺點(diǎn)：數(shù)據(jù)存在重復(fù)和冗余，存在部分空間浪費(fèi)。另外，為了保持?jǐn)?shù)據(jù)的一致性，則必須維護(hù)這部分冗余數(shù)據(jù)，因此增加了維護(hù)的復(fù)雜性。所以，在進(jìn)行范式設(shè)計(jì)時(shí)，要在數(shù)據(jù)一致性與查詢之間找到平衡點(diǎn)，因?yàn)榉蠘I(yè)務(wù)場(chǎng)景的設(shè)計(jì)才是好的設(shè)計(jì)。

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

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

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

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

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

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

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

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

那么，修改以后的語句可以簡(jiǎn)化為：

SELECTCODE,NAME,SUBJECTFROMCOURSECWHERECODD=?

范式和反范式的對(duì)比如下表所示：

模型優(yōu)點(diǎn)缺點(diǎn)范式化模型數(shù)據(jù)沒有冗余，更新容易當(dāng)表的數(shù)量比較多，查詢?cè)O(shè)計(jì)需要很多關(guān)聯(lián)模型(Join)時(shí)，會(huì)導(dǎo)致查詢性能低下反范式化模型數(shù)據(jù)冗余將帶來很好的讀取性能(因?yàn)椴恍枰狫oin很多表，而