數(shù)據(jù)庫程序員面試分類真題25

數(shù)據(jù)庫程序員面試分類真題25簡答題1.

在MySQL中，如何查看表的詳細(xì)信息，例如，存儲引擎、行數(shù)、更新時間等?正確答案：可以使用SHOWTABLESTATUS獲取表的詳細(xì)信息，語法為

（江南博哥）SHOWTABLESTATUS

[{FROM|IN}db_name]

[LIKE'pattern'|WHEREexpr]

例如：

(1)showtablestatusfromdb_name查詢db_name數(shù)據(jù)庫里所有表的信息

(2)showtablestatusfromdb_namelike'lhruse'\G;查詢db_name里lhruse表的信息

(3)showtablestatusfromdb_namelike'uc%'查詢db_name數(shù)據(jù)庫里表名以uc開頭的表的信息

下面的SQL語句查詢了mysql數(shù)據(jù)庫中的user表的詳細(xì)信息：

其中，每列的含義見下表。列名解釋Name表名Engine表的存儲引擎，在MySQL4.1.2之前，該列的名字為TypeVersion表的.frm文件的版本號Row_format行存儲格式(Fixed，Dynamic，Compressed，Redundant，Compact)。對于MyISAM引擎，可以是Dynalmc、Fixed或Compressed。動態(tài)行的行長度可變，例如Varchar或Blob類型字段。固定行是指行長度不變，例如Char和Integer類型字段Rows行的數(shù)目。對于非事務(wù)性表，這個值是精確的，對于事務(wù)性引擎，這個值通常是估算的。例如MyISAM，存儲精確的數(shù)目。對于其他存儲引擎，比如InnoDB，本值是一個大約的數(shù)，與實(shí)際值相差可達(dá)40～50%。在這些情況下，使用SELECTCOUNT(*)來獲得準(zhǔn)確的數(shù)目。對于在INFORMATION_SCHEMA數(shù)據(jù)庫中的表，Rows值為NULLAvg_row_length平均每行包括的字節(jié)數(shù)Data_length表數(shù)據(jù)的大小(和存儲引擎有關(guān))Max_data_length表可以容納的最大數(shù)據(jù)量(和存儲引擎有關(guān))Index_length索引的大小(和存儲引擎有關(guān))Data_free對于MyISAM引擎，標(biāo)識已分配，但現(xiàn)在未使用的空間，并且包含了已被刪除行的空間Auto_increment下一個Auto_increment的值Create_time表的創(chuàng)建時間Update_time表的最近更新時間Check_time使用checktable或myisamchk工具檢查表的最近時間Collation表的默認(rèn)字符集和字符排序規(guī)則Checksum如果啟用，則對整個表的內(nèi)容計算時的校驗(yàn)和Create_options指表創(chuàng)建時的其他所有選項(xiàng)Comment包含了其他額外信息，對于MyISAM引擎，包含了注釋。對于InnoDB引擎，則保存著InnoDB表空間的剩余空間信息。如果是一個視圖，那么注釋里面包含了VIEW字樣

也可以使用information_schema.tables表來查詢，如下：

SELECTtable_name,Engine,Version,Row_format,table_rows,Avg_row_length,

Data_length,Max_data_length,Index_length,Data_free,Auto_increment,

Create_time,Update_time,Check_time,table_collation,Checksum,

Create_options,table_comment

FROMinformation_schema.tables

WHERETable_Sehema='mysql'andtable_name='user\G;

2.

MySQL的企業(yè)版和社區(qū)版的區(qū)別有哪些?正確答案：用戶通?？梢缘焦俜骄W(wǎng)站下載最新版本的MySQL數(shù)據(jù)庫。按照用戶群分類，MySQL數(shù)據(jù)庫目前分為社區(qū)版(CommunityServer)和企業(yè)版(Enterprise)，它們最重要的區(qū)別在于：社區(qū)版是自由下載而且完全免費(fèi)的，但是官方不提供任何技術(shù)支持，適用于大多數(shù)普通用戶；企業(yè)版是收費(fèi)的，不能在線下載，但是，它提供了更多的功能和更完備的技術(shù)支持，更適合于對數(shù)據(jù)庫的功能和可靠性要求較高的企業(yè)客戶。[考點(diǎn)]維護(hù)

3.

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

[考點(diǎn)]維護(hù)

4.

如何查看和修改系統(tǒng)參數(shù)?正確答案：在MySQL里，參數(shù)也可以叫變量(Variables)，一般配置文件為/etc/f。當(dāng)MySQL實(shí)例啟動時，MySQL會先去讀一個配置參數(shù)文件，用來尋找數(shù)據(jù)庫的各種文件所在位置以及指定某些初始化參數(shù)，這些參數(shù)通常定義了某種內(nèi)存結(jié)構(gòu)有多大等設(shè)置。默認(rèn)情況下，MySQL實(shí)例會按照一定的次序去讀取所有參數(shù)文件，可以通過命令“mysql--help|grepf”來查找這些參數(shù)文件的位置。

在Linux下的次序?yàn)?etc/f-＞/etc/mysql/f-＞/usr/local/mysql/etc/f-＞～/.f;在Windows下的次序?yàn)镃:\WINDOWS\my.ini-＞C:\WINDOWS\f-＞C:\my.ini-＞C:\f-＞%MySQL安裝目錄%\my.ini-＞%MySQL安裝目錄%\f。如果這幾個配置文件中都有同一個參數(shù)，那么MySQL數(shù)據(jù)庫會以讀取到的最后一個配置文件中的參數(shù)為準(zhǔn)。在Linux環(huán)境下，配置文件一般為/etc/f。在數(shù)據(jù)庫啟動的時候可以加上從指定參數(shù)文件進(jìn)行啟動，如下：

mysqld_safe--defaults-file=/etc/f&

MySQL的變量可以分為系統(tǒng)變量和狀態(tài)變量。MySQL沒有類似于Oracle的隱含參數(shù)，也不需要隱含參數(shù)來設(shè)置。下面分別講解。

1.系統(tǒng)變量

系統(tǒng)變量用于配置MySQL服務(wù)器的運(yùn)行環(huán)境。系統(tǒng)變量按其作用域的不同可以分為兩種：①全局(GLOBAL)級，對整個MySQL服務(wù)器有效；②會話(SESSION或LOCAL)級，只影響當(dāng)前會話。有些變量同時擁有以上兩個級別，MySQL將在建立連接時用全局級變量初始化會話級變量，但一旦連接建立之后，全局級變量的改變不會影響到會話級變量?？梢杂胹howvariables查看系統(tǒng)變量的值，如下：

mysql＞showvariableslike'log%';

mysql＞showvariableswhereVariable_namelike'log%'andvalue='ON';

注意：showvariables優(yōu)先顯示會話級變量的值，若這個值不存在，則顯示全局級變量的值，當(dāng)然也可以加上GLOBAL或SESSION關(guān)鍵字進(jìn)行區(qū)別：

showglobalvariables;

showsession/localvariables;

在寫一些存儲過程時，可能需要引用系統(tǒng)變量的值，可以使用如下方法：

@@GLOBAL.var_name

@@SESSION.var_name

@@LOCAL.var_name

如果在變量名前沒有級別限定符，那么將優(yōu)先顯示會話級的值。

另外一種查看系統(tǒng)變量值的方法是直接查詢表。對于MySQL5.6可以從INFORMATION_SCHEMA.GLOBAL_VARIABLES和INFORMATION_SCHEMA.SESSION_VARIABLES表獲得；對于MySQL5.7可以從performance_schema.global_variables和performance_schema.session_variables表中查詢。需要注意的是，若要查詢INFORMATION_SCHEMA.GLOBAL_VARIABLES或INFORMATION_SCHEMA.SESSION_VARIABLES表，則需要設(shè)置參數(shù)show_compatibility_56的值為ON，否則會報錯：ERROR3167(HY000)：The'INFORMATION_SCHEMA.GLOBAL_STATUS'featureisdisabled;seethedocumentationfor'show_compatibility_56'。

在MySQL服務(wù)器啟動時，可以通過以下兩種方法設(shè)置系統(tǒng)變量的值：

1)命令行參數(shù)，例如，mysqld--max_connections=200。

2)選項(xiàng)文件(f)。在MySQL服務(wù)器啟動后，如果需要修改系統(tǒng)變量的值，那么可以通過SET語句：

SETGLOBALvar_name=value;

SET@@GLOBAL.var_name=value;

SETSESSIONvar_name=value;

SET@@SESSION.var_name=value;

如果在變量名前沒有級別限定符，那么表示修改會話級變量。

MySQL的系統(tǒng)變量也可以分為動態(tài)(Dynamic)系統(tǒng)變量和靜態(tài)(Static)系統(tǒng)變量。動態(tài)系統(tǒng)變量意味著可以在MySQL實(shí)例運(yùn)行中進(jìn)行更改；靜態(tài)系統(tǒng)變量說明在整個實(shí)例生命周期內(nèi)都不得進(jìn)行更改，就好像是只讀(ReadOnly)的。

注意：和啟動時不一樣的是，在運(yùn)行時設(shè)置的變量不允許使用后綴字母'K'、'M'等，但可以用表達(dá)式來達(dá)到相同的效果，如：

SETGLOBALread_buffer_size=2*1024*1024

2.狀態(tài)變量

狀態(tài)變量用于監(jiān)控MySQL服務(wù)器的運(yùn)行狀態(tài)，可以用showstares查看。狀態(tài)變量和系統(tǒng)變量類似，也分為全局級和會話級，showstatus也支持like匹配查詢，不同之處在于，狀態(tài)變量只能由MySQL服務(wù)器本身設(shè)置和修改，對于用戶來說是只讀的，不可以通過SET語句設(shè)置和修改它們。另外，和系統(tǒng)變量類似，也可以通過表的方式來查詢狀態(tài)變量的值，MySQL5.6查詢INFORMATION_SCHEMA.GLOBAL_STATUS和INFORMATION_SCHEMA.SESSION_STATUS；MySQL5.7查詢performance_schema.session_status和performance_schema.session_status。[考點(diǎn)]參數(shù)

5.

MySQL查看當(dāng)前使用的配置文件f的方法有哪些?正確答案：MySQL實(shí)例在啟動時，會先讀取配置參數(shù)文件f一般會放在MySQL的安裝目錄中，用戶也可以放在其他目錄加載。在安裝MySQL后，系統(tǒng)中會有多個f文件，有些是用于測試的。使用“l(fā)ocatef”或“find/-namef”命令可以列出所有的f文件。

有時候，DBA會發(fā)現(xiàn)雖然嘗試修改了配置文件的一些變量，但是并沒有生效，這其實(shí)是因?yàn)樾薷牡奈募⒎荕ySQL服務(wù)器讀取的配置文件。在Linux環(huán)境中，MySQL服務(wù)器讀取的配置文件及路徑默認(rèn)為

/etc/my.enf

/etc/mysql/f

/usr/etc/my.enf

～/.f

如果不清楚MySQL當(dāng)前使用的配置文件路徑，那么可以按照如下步驟來查看：

(1)查看是否使用了指定目錄的f文件

在啟動MySQL后，可以通過查看MySQL的進(jìn)程，看是否有設(shè)置使用指定目錄的f文件，如果有則表示MySQL啟動時加載了這個配置文件。

命令：ps-ef|grepmysql|grep'f'

如果上面的命令沒有輸出，那么表示沒有設(shè)置使用指定目錄的f,若有輸出則表示使用的是輸出中的文件。

(2)查看MySQL默認(rèn)讀取f的目錄

如果沒有設(shè)置使用指定目錄的f，MySQL啟動時會讀取安裝目錄根目錄及默認(rèn)目錄下的f文件。

命令：mysql--help|grep'f'或mysqld--verbose--help|grep-A1'Defaultoptions'

一般情況下，“/etc/f、/etc/mysql/f、/usr/local/etc/f、～/.f”就是MySQL默認(rèn)會搜尋f的目錄，順序排前的優(yōu)先。

(3)啟動時沒有使用配置文件

如果沒有設(shè)置使用指定目錄f文件及默認(rèn)讀取目錄沒有f文件，那么表示MySQL啟動時并沒有加載配置文件，而是使用默認(rèn)配置。若需要修改配置，則可以在MySQL默認(rèn)讀取的目錄中，創(chuàng)建一個f文件(例如，/etc/f)，把需要修改的配置內(nèi)容寫入，重啟MySQL后即可生效。

如果是Windows安裝版，那么找到相關(guān)的Windows服務(wù)，會看到配置了一個文件地址，如下圖所示。

若此處沒有看到配置文件地址，則使用的是默認(rèn)目錄下的f文件。[考點(diǎn)]參數(shù)

6.

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

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

2)日志文件：包括錯誤日志、查詢?nèi)罩?、慢查詢?nèi)罩竞投M(jìn)制日志。

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

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

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

6)存儲引擎文件：每個存儲引擎都有自己的文件夾來保存各種數(shù)據(jù)，這些存儲引擎真正存儲了數(shù)據(jù)和索引等數(shù)據(jù)。[考點(diǎn)]文件

7.

什么是MySQL的套接字文件?正確答案：MySQL有兩種連接方式，常用的是TCP/IP方式，如下：

mysql-h59-uroot-plhr

還有一種是套接字方式。Unix系統(tǒng)下本地連接MySQL可以采用Unix套接字方式，這種方式需要一個套接字(Socket)文件。套接字文件就是當(dāng)用套接字方式進(jìn)行連接時需要的文件。套接字方式比用TCP/IP的方式更快，更安全，但只適用于MySQL和客戶端在同一臺PC上。套接字文件可由參數(shù)socket控制，一般在/tmp目錄下，名為mysql.sock，也可以放在其他目錄下，如下：

用套接字連接MySQL：

mysql-plhr-S/var/lib/mysq157/mysql.sock[考點(diǎn)]文件

8.

什么是MySQL的pid文件?正確答案：pid文件是MySQL實(shí)例的進(jìn)程ID文件。當(dāng)MySQL實(shí)例啟動時，會將自己的進(jìn)程ID寫入一個文件中，該文件即為pid文件。該文件可由參數(shù)pid_file控制，默認(rèn)路徑位于數(shù)據(jù)庫目錄下，文件名為主機(jī)名.pid，如下：

[考點(diǎn)]文件

9.

MySQL支持事務(wù)嗎?正確答案：在缺省模式下，MySQL是AUTOCOMMIT模式的，所有的數(shù)據(jù)庫更新操作都會即時提交。這就表示除非顯式地開始一個事務(wù)，否則每個查詢都被當(dāng)作一個單獨(dú)的事務(wù)自動執(zhí)行。但是，如果MySQL表類型是使用InnoDBTables(或其他支持事務(wù)的存儲引擎)，那么MySQL就可以使用事務(wù)處理，使用SETAUTOCOMMIT=0就可以使MySQL運(yùn)行在非AUTOCOMMIT模式下。在非AUTOCOMMIT模式下，必須使用COMMIT來提交更改，或者使用ROLLBACK來回滾更改。需要注意的是，在MySQL5.5以前，默認(rèn)的存儲引擎是MyISA2M(從MySQL5.5開始，默認(rèn)存儲引擎是InnoDB)，而MyISAM存儲引擎不支持事務(wù)處理，所以改變AUTOCOMMIT的值對數(shù)據(jù)庫沒有什么作用，但不會報錯。所以，若要使用事務(wù)處理，則一定要確定所操作的表是支持事務(wù)處理的，如InnoDB。如果不知道表的存儲引擎，那么可以通過查看建表語句來確定表的存儲引擎。[考點(diǎn)]存儲引擎

10.

InnoDB存儲引擎支持哪些事務(wù)類型?正確答案：對于InnoDB存儲引擎來說，其支持扁平事務(wù)、帶有保存點(diǎn)的扁平事務(wù)、鏈?zhǔn)聞?wù)和分布式事務(wù)。對于嵌套事務(wù)，其原生不支持。因此對有并發(fā)事務(wù)需求的用戶來說，MySQL數(shù)據(jù)庫或InnoDB存儲引擎就顯得無能為力，然而用戶仍可以通過帶保存點(diǎn)的事務(wù)來模擬串行的嵌套事務(wù)。[考點(diǎn)]存儲引擎

11.

InnoDB存儲引擎支持XA事務(wù)嗎?正確答案：XA事務(wù)即分布式事務(wù)，目前在MySQL的存儲引擎中，只有InnoDB存儲引擎才支持XA事務(wù)。需要注意的是，在使用分布式事務(wù)時，InnoDB存儲引擎的隔離級別必須設(shè)置為SERIALIZABLE。通過參數(shù)innodb_support_xa可以查看是否啟用了XA事務(wù)的支持(默認(rèn)為ON，表示啟用)：

[考點(diǎn)]存儲引擎

12.

MySQL中的XA事務(wù)分為哪幾類?正確答案：MySQL從5.0.3版本開始支持XA事務(wù)，即分布式事務(wù)。在MySQL中，XA事務(wù)有兩種，內(nèi)部XA事務(wù)和外部XA事務(wù)，下面分別介紹。

(1)內(nèi)部XA事務(wù)

MySQL本身的插件式架構(gòu)導(dǎo)致在其內(nèi)部需要使用XA事務(wù)，此時MySQL既是協(xié)調(diào)者，也是參與者。內(nèi)部XA事務(wù)發(fā)生在存儲引擎與插件之間，或者存儲引擎與存儲引擎之間。例如，不同的存儲引擎之間是完全獨(dú)立的，因此當(dāng)一個事務(wù)涉及兩個不同的存儲引擎時，就必須使用內(nèi)部XA事務(wù)。由于只在單機(jī)上工作，所以被稱為內(nèi)部XA。

最為常見的內(nèi)部xA事務(wù)存在于二進(jìn)制日志(Binlog)和InnoDB存儲引擎之間。由于復(fù)制的需要，目前絕大多數(shù)的數(shù)據(jù)庫都開啟了Binlog功能。在事務(wù)提交時，先寫二進(jìn)制日志，再寫InnoDB存儲引擎的重做日志。對上述兩個操作的要求也是原子的，即二進(jìn)制日志和重做日志必須同時寫入。若二進(jìn)制日志先寫了，而在寫入InnoDB存儲引擎時發(fā)生了宕機(jī)，那么Slave可能會接收到Master傳過去的二進(jìn)制日志并執(zhí)行，最終導(dǎo)致了主從不一致的情況。為了解決這個問題，MySQL數(shù)據(jù)庫在Binlog與InnoDB存儲引擎之間采用XA事務(wù)。當(dāng)事務(wù)提交時，InnoDB存儲引擎會先做一個PREPARE操作，將事務(wù)的Xid寫入，接著進(jìn)行Binlog的寫入。如果在Binlog存儲引擎提交前，MYSQL數(shù)據(jù)庫宕機(jī)了，那么MySQL數(shù)據(jù)庫在重啟后會先檢查準(zhǔn)備的UXID事務(wù)是否已經(jīng)提交，若沒有，則在存儲引擎層再進(jìn)行一次提交操作。

(2)外部XA事務(wù)

外部XA事務(wù)就是一般談?wù)摰姆植际绞聞?wù)。MySQL支持XASTART/END/PREPARE/COMMIT這些SQL語句，通過使用這些命令可以完成分布式事務(wù)的狀態(tài)轉(zhuǎn)移。MySQL在執(zhí)行分布式事務(wù)(外部XA)的時候，MySQL服務(wù)器相當(dāng)于XA事務(wù)資源管理器，與MySQL鏈接的客戶端相當(dāng)于事務(wù)管理器。

內(nèi)部：XA事務(wù)用于同一實(shí)例下跨多引擎事務(wù)，而外部XA事務(wù)用于跨多MySQL實(shí)例的分布式事務(wù)，需要應(yīng)用層作為協(xié)調(diào)者。應(yīng)用層負(fù)責(zé)決定提交還是回滾。MySQL數(shù)據(jù)庫外部XA事務(wù)可以用在分布式數(shù)據(jù)庫代理層，實(shí)現(xiàn)對MySQL數(shù)據(jù)庫的分布式事務(wù)支持，例如開源的代理工具：網(wǎng)易的DDB、淘寶的TDDL等。[考點(diǎn)]存儲引擎

13.

什么是MySQL的存儲引擎?正確答案：MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件(或者內(nèi)存)中。這些技術(shù)中的每一種都使用不同的存儲機(jī)制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能。通過選擇不同的技術(shù)，能夠獲得額外的速度或者功能，從而改善應(yīng)用的整體功能。例如，研究大量的臨時數(shù)據(jù)，也許需要使用內(nèi)存存儲引擎。內(nèi)存存儲引擎能夠在內(nèi)存中存儲所有的表格數(shù)據(jù)。這些不同的技術(shù)以及配套的相關(guān)功能在MySQL中被稱作存儲引擎(StorageEngines，也稱作表類型)。MySQL默認(rèn)配置了許多不同的存儲引擎，可以預(yù)先設(shè)置或者在MySQL服務(wù)器中啟用。[考點(diǎn)]存儲引擎

14.

MySQL有幾種存儲引擎(表類型)?各自有什么區(qū)別?正確答案：MySQL官方有多種存儲引擎：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。第三方存儲引擎中比較有名的有TokuDB、Infobright、InnfiniDB、XtraDB(InnoDB增強(qiáng)版本)。其中，最常見的兩種存儲引擎是MyISAM和InnoDB。MyISAM是MySQL關(guān)系型數(shù)據(jù)庫管理系統(tǒng)的默認(rèn)存儲引擎(MySQL5.5以前)。這種MySQL表存儲結(jié)構(gòu)從舊ISAM代碼擴(kuò)展出許多有用的功能。從MySQL5.5開始，InnoDB引擎由于其對事務(wù)參照完整性，以及更高的并發(fā)性等優(yōu)點(diǎn)開始逐步地取代MyISAM，作為MySQL數(shù)據(jù)庫的默認(rèn)存儲引擎。下面逐一介紹各種存儲引擎。

1.MyISAM

MyISAM存儲引擎管理非事務(wù)表，提供高速存儲和檢索，以及全文搜索能力。該引擎插入數(shù)據(jù)快，空間和內(nèi)存使用比較低。

(1)存儲組成

每個MyISAM在磁盤上存儲成三個文件。每一個文件的名字就是表的名字，文件名都和表名相同，擴(kuò)展名指出了文件類型。這里特別要注意的是，MyISAM不緩存數(shù)據(jù)文件，只緩存索引文件。

1)表定義的擴(kuò)展名為.frm(frame，存儲表定義)。

2)數(shù)據(jù)文件的擴(kuò)展名為.MYD(MYData，存儲數(shù)據(jù))。

3)索引文件的擴(kuò)展名是.MYI(MYIndex，存儲索引)。

數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分布I/O，獲得更快的速度，而且其索引是壓縮的，能加載更多索引，這樣內(nèi)存使用率就對應(yīng)提高了不少，壓縮后的索引也能節(jié)約一些磁盤空間。

(2)特點(diǎn)

1)不支持事務(wù)，不支持外鍵約束，但支持全文索引，這可以極大地優(yōu)化LIKE查詢的效率。

2)表級鎖定(更新時鎖定整個表)：其鎖定機(jī)制是表級索引，這雖然可以讓鎖定的實(shí)現(xiàn)成本很小，但是也同時大大降低了其并發(fā)性能。MyISAM不支持行級鎖，只支持并發(fā)插入的表鎖，主要用于高負(fù)載的查詢。

3)讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身并不會阻塞另外的讀。

4)不緩存數(shù)據(jù)，只緩存索引：MyISAM可以通過key_buffer緩存，以大大提高訪問性能，減少磁盤I/O，但是這個緩存區(qū)只會緩存索引，而不會緩存數(shù)據(jù)。

[root@mysql]#grepkey_bufferf

key_buffer_size=16M

5)讀取速度較快，占用資源相對少。

6)MyISAM引擎是MySQL5.5之前版本缺省的存儲引擎。

7)并發(fā)量較小，不適合大量UPDATE。

(3)適用場景

如果表主要用于插入新記錄和讀出新記錄，那么選擇MyISAM存儲引擎能實(shí)現(xiàn)處理的高效率。如果應(yīng)用的完整性和并發(fā)性要求很低，那么也可以選擇MyISAM存儲引擎。它是在Web、數(shù)據(jù)倉儲和其他應(yīng)用環(huán)境下最常使用的存儲引擎之一。具體來說，適用于以下場景：

1)不需要事務(wù)支持的業(yè)務(wù)，一般為讀數(shù)據(jù)比較多的網(wǎng)站應(yīng)用。

2)并發(fā)相對較低的業(yè)務(wù)(純讀純寫高并發(fā)也可以)。

3)數(shù)據(jù)修改相對較少的業(yè)務(wù)。

4)以讀為主的業(yè)務(wù)，例如，WWW、BLOG、圖片信息數(shù)據(jù)庫、用戶數(shù)據(jù)庫、商品數(shù)據(jù)庫等業(yè)務(wù)。

5)對數(shù)據(jù)一致性要求不是非常高的業(yè)務(wù)。

6)中小型網(wǎng)站的部分業(yè)務(wù)。

小結(jié)：單一對數(shù)據(jù)庫的操作都可以使用MyISAM，所謂單一就是盡量純讀，或純寫(INSERT，UPDATE，DELETE)等。生產(chǎn)建議：沒有特別需求，一律用InnoDB。

(4)MyISAM引擎調(diào)優(yōu)精要

1)盡量索引(緩存機(jī)制)。

2)調(diào)整讀寫優(yōu)先級，根據(jù)實(shí)際需要確保重要操作更優(yōu)先。

3)啟用延遲插入改善大批量寫入性能(降低寫入頻率，盡可能多條數(shù)據(jù)一次性寫入)。

4)盡量順序操作讓INSERT數(shù)據(jù)都寫入尾部，減少阻塞。

5)分解大的操作，降低單個操作的阻塞時間。

6)降低并發(fā)數(shù)，某些高并發(fā)場景通過應(yīng)用進(jìn)行排隊機(jī)制。

7)對于相對靜態(tài)的數(shù)據(jù)，充分利用QueryCache可以極大地提高訪問效率。

[root@mysql3307]#grepqueryf

query_cache_size=2M

query_cache_limit=1M

query_cache_min_res_unit=2k

這幾個參數(shù)都是MySQL自身緩存設(shè)置。

8)MyISAM的COUNT只有在全表掃描的時候特別高效，帶有其他條件的COUNT都需要進(jìn)行實(shí)際的數(shù)據(jù)訪問。

9)把主從同步的主庫使用InnoDB，從庫使用MyISAM引擎。

2.InnoDB

InnoDB用于事務(wù)處理應(yīng)用程序，主要面向OLTP方面的應(yīng)用。該引擎由InnoDB公司開發(fā)，其特點(diǎn)是行鎖設(shè)置，并支持類似于Oracle的非鎖定讀，即默認(rèn)情況下讀不產(chǎn)生鎖。InnoDB將數(shù)據(jù)放在一個邏輯表空間中。InnoDB通過多版本并發(fā)控制來獲得高并發(fā)性，實(shí)現(xiàn)了ANSI標(biāo)準(zhǔn)的4種隔離級別，默認(rèn)為Repeatable，使用一種被稱為next-keylocking的策略避免幻讀。對于表中數(shù)據(jù)的存儲，InnoDB采用類似Oracle索引組織表Clustered的方式進(jìn)行存儲。如果對事務(wù)的完整性要求比較高，要求實(shí)現(xiàn)并發(fā)控制，那么選擇InnoDB引擎有很大的優(yōu)勢。需要頻繁地進(jìn)行更新，刪除操作的數(shù)據(jù)庫，也可以選擇InnoDB存儲引擎。因?yàn)镮nnoDB存儲引擎提供了具有提交(COMMIT)、回滾(ROLLBACK)和崩潰恢復(fù)能力的事務(wù)安全。

InnoDB類型的表只有ibd文件，分為數(shù)據(jù)區(qū)和索引區(qū)，有較好的讀寫并發(fā)能力。物理文件有日志文件、數(shù)據(jù)文件和索引文件。其中，索引文件和數(shù)據(jù)文件是放在一個目錄下，可以設(shè)置共享文件、獨(dú)享文件兩種格式。

(1)特點(diǎn)

1)支持事務(wù)：包括ACID事務(wù)支持，支持4個事務(wù)隔離級別，支持多版本讀。

2)行級鎖定(更新時一般是鎖定當(dāng)前行)：通過索引實(shí)現(xiàn)，全表掃描仍然會是表鎖，注意間隙鎖的影響。

3)支持崩潰修復(fù)能力和MVCC。

4)讀寫阻塞與事務(wù)隔離級別相關(guān)。

5)具有非常高效的緩存特性：能緩存索引，也能緩存數(shù)據(jù)。

6)整個表和主鍵以CLUSTER方式存儲，組成一棵平衡樹。

7)所有SECONDARYINDEX都會保存主鍵信息。

8)支持分區(qū)、表空間，類似Oracle數(shù)據(jù)庫。

9)支持外鍵約束(ForeignKey)，外鍵所在的表稱為子表，而所依賴的表稱為父表。

10)InnoDB支持自增長列(AUTO_INCREMENT)，自增長列的值不能為空。

11)InnoDB是索引和數(shù)據(jù)緊密捆綁的，沒有使用壓縮，從而會造成InnoDB比MyISAM體積龐大得多。

(2)優(yōu)點(diǎn)

支持事務(wù)，用于事務(wù)處理應(yīng)用程序，具有眾多特性，包括ACID事務(wù)支持，支持外鍵，同時支持崩潰修復(fù)能力和并發(fā)控制。并發(fā)量較大，適合大量UPDATE。

(3)缺點(diǎn)

對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。相比MyISAM引擎，InnoDB引擎更消耗資源，速度沒有MyISAM引擎快。

(4)適用場景

如果對事務(wù)的完整性要求比較高，要求實(shí)現(xiàn)并發(fā)控制，那么選擇InnoDB引擎有很大的優(yōu)勢。需要頻繁地進(jìn)行更新，刪除操作的數(shù)據(jù)庫，也可以選擇InnoDB存儲引擎。具體分類如下：

1)需要事務(wù)支持(具有較好的事務(wù)特性)。

2)行級鎖定對高并發(fā)有很好的適應(yīng)能力，但需要確保查詢是通過索引完成。

3)數(shù)據(jù)更新較為頻繁的場景，例如，BBS(BulletinBoardSystem，電子公告牌系統(tǒng))、SNS(SocialNetworkSite，社交網(wǎng))等。

4)數(shù)據(jù)一致性要求較高的業(yè)務(wù)。例如，充值、銀行轉(zhuǎn)賬等。

5)硬件設(shè)備內(nèi)存較大，可以利用InnoDB較好的緩存能力來提高內(nèi)存利用率，盡可能減少磁盤I/O。

物理數(shù)據(jù)文件：

[root@mysql3307]#11data/ibdatal

-rw-rw----1mysqlmysql134217728May1508:31data/ibdata1

6)相比MyISAM引擎，InnoDB引擎更消耗資源，速度沒有MyISAM引擎快。

(5)InnoDB引擎調(diào)優(yōu)精要

1)主鍵盡可能小，避免給SECONDARYINDEX帶來過大的空間負(fù)擔(dān)。

2)避免全表掃描，因?yàn)闀褂帽礞i。

3)盡可能緩存所有的索引和數(shù)據(jù)，提高響應(yīng)速度，減少磁盤I/O消耗。

4)在執(zhí)行大量插入操作的時候，盡量自己控制事務(wù)而不要使用AUTOCOMMIT自動提交。有開關(guān)可以控制提交方式。

5)合理設(shè)置innodb_flush_log_at_trx_commit參數(shù)值，不要過度追求安全性。

6)避免主鍵更新，因?yàn)檫@會帶來大量的數(shù)據(jù)移動。

3.MEMORY(HEAP)

MEMORY存儲引擎(之前稱為HEAP)提供“內(nèi)存中”的表。如果需要很快的讀寫速度，對數(shù)據(jù)的安全性要求較低，那么可選擇MEMORY存儲引擎。MEMORY存儲引擎對表大小有要求，不能建太大的表。所以，這類數(shù)據(jù)庫只適用相對較小的數(shù)據(jù)庫表。如果mysqld進(jìn)程發(fā)生異常，那么數(shù)據(jù)庫就會重啟或崩潰，數(shù)據(jù)就會丟失，因此，MEMORY存儲引擎中的表的生命周期很短，一般只使用一次，非常適合存儲臨時數(shù)據(jù)。

(1)特點(diǎn)

1)MEMORY存儲引擎將所有數(shù)據(jù)保存在內(nèi)存(RAM)中，在需要快速查找引用和其他類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問速度。

2)每個基于MEMORY存儲引擎的表實(shí)際對應(yīng)一個磁盤文件，該文件的文件名和表名是相同的，類型為.frm。該文件只存儲表的結(jié)構(gòu)，而其數(shù)據(jù)文件都是存儲在內(nèi)存中，這樣有利于對數(shù)據(jù)的快速處理，提高整個表的處理能力。

3)MEMORY存儲引擎默認(rèn)使用哈希(HASH)索引，其速度比使用B-Tree型要快，但安全性不高。如果希望使用B-Tree型，那么在創(chuàng)建的時候可以引用。

(2)適用場景

如果需要很快的讀寫速度，那么在需要快速查找引用和其他類似數(shù)據(jù)的環(huán)境下，對數(shù)據(jù)的安全性要求較低，可選擇MEMORY存儲引擎。

(3)優(yōu)點(diǎn)

將所有數(shù)據(jù)保存在內(nèi)存(RAM)中，默認(rèn)使用HASH索引，數(shù)據(jù)的處理速度快。

(4)缺點(diǎn)

不支持事務(wù)，安全性不高；MEMORY存儲引擎對表大小有要求，不能建太大的表。

4.MERGE

MERGE存儲引擎允許將一組使用MyISAM存儲引擎的并且表結(jié)構(gòu)相同(即每張表的字段順序、字段名稱、字段類型、索引定義的順序及其定義的方式必須相同)的數(shù)據(jù)表合并為一個表，方便了數(shù)據(jù)的查詢。需要注意的是，使用MERGE“合并”起來的表結(jié)構(gòu)相同的表最好不要有主鍵，否則會出現(xiàn)這種情況：一共有兩個成員表，其主鍵在兩個表中存在相同情況，但是寫了一條按相同主鍵值查詢的SQL語句，這時只能查到UNION列表中第一個表中的數(shù)據(jù)。MERGE存儲引擎允許集合將被處理同樣的MyISAM表作為一個單獨(dú)的表。

適用場景：MERGE存儲引擎允許MySQLDBA或開發(fā)人員將一系列等同的MyISAM表以邏輯方式組合在一起，并作為1個對象引用它們。對于諸如數(shù)據(jù)倉庫等，VLDB(VeryLargeDataBase，超大型數(shù)據(jù)庫)環(huán)境十分適合。

優(yōu)點(diǎn)：便于同時引用多個數(shù)據(jù)表而無須發(fā)出多條查詢。

缺點(diǎn)：不支持事務(wù)。

5.BDB(BerkeleyDB)

BDB是事務(wù)型存儲引擎，支持COMMIT、ROLLBACK和其他事務(wù)特性，它由Sleepycat軟件公司()開發(fā)。BDB是一個高性能的嵌入式數(shù)據(jù)庫編程庫(引擎)，它可以用來保存任意類型的鍵/值對(Key/ValuePair)，而且可以為一個鍵保存多個數(shù)據(jù)。BDB可以支持?jǐn)?shù)干的并發(fā)線程同時操作數(shù)據(jù)庫，支持最大256TB的數(shù)據(jù)。BDB存儲引擎處理事務(wù)安全的表，并以哈希為基礎(chǔ)的存儲系統(tǒng)。

適用場景：BDB存儲引擎適合快速地讀寫某些數(shù)據(jù)，特別是不同KEY的數(shù)據(jù)。

優(yōu)點(diǎn)：支持事務(wù)。

缺點(diǎn)：在沒有索引的列上操作速度很慢。

6.EXAMPLE

EXAMPLE存儲引擎是一個“存根”引擎，可以用這個引擎創(chuàng)建表，但數(shù)據(jù)不能存儲在該引擎中。EXAMPLE存儲引擎可為快速創(chuàng)建定制的插件式存儲引擎提供幫助。

7.NDB

NDB存儲引擎是一個集群存儲引擎，是被MySQLCluster用來實(shí)現(xiàn)分割到多臺計算機(jī)上的表的存儲引擎，類似于Oracle的RAC，但它是ShareNothing的架構(gòu)，因此，能提供更高級別的高可用性和可擴(kuò)展性。NDB的特點(diǎn)是數(shù)據(jù)全部放在內(nèi)存中，因此，通過主鍵查找非?？?。它在MySQL-Max5.1二進(jìn)制分發(fā)版里提供。

(1)特性

1)分布式：分布式存儲引擎，可以由多個NDBCluster存儲引擎組成集群分別存放整體數(shù)據(jù)的一部分。

2)支持事務(wù)：和InnoDB一樣，支持事務(wù)。

3)可與mysqld不在一臺主機(jī)：可以和mysqld分開存在于獨(dú)立的主機(jī)上，然后通過網(wǎng)絡(luò)和mysqld通信交互。

4)內(nèi)存需求量巨大：新版本索引以及被索引的數(shù)據(jù)必須存放在內(nèi)存中，老版本所有數(shù)據(jù)和索引必須存在于內(nèi)存中。

(2)適用場景

1)具有非常高的并發(fā)需求。

2)對單個請求的響應(yīng)并不是非常嚴(yán)格。

3)查詢簡單，過濾條件較為固定，每次請求數(shù)據(jù)量較少。

4)具有高性能查找要求的應(yīng)用程序，這類查找需求還要求具有最高的正常工作時間和可用性。

(3)優(yōu)點(diǎn)

1)分布式：分布式存儲引擎，可以由多個NDBCluster存儲引擎組成集群分別存放整體數(shù)據(jù)的一部分。

2)支持事務(wù)：和InnoDB一樣，支持事務(wù)。

3)可與mysqld不在一臺主機(jī)：可以和mysqld分開存在于獨(dú)立的主機(jī)上，然后通過網(wǎng)絡(luò)和mysqld通信交互。

(4)缺點(diǎn)

內(nèi)存需求量巨大：新版本索引以及被索引的數(shù)據(jù)必須存放在內(nèi)存中，老版本所有數(shù)據(jù)和索引必須存在于內(nèi)存中。它的連接操作是在MySQL數(shù)據(jù)庫層完成，不是在存儲引擎層完成的，這意味著，復(fù)雜的連接操作需要巨大的網(wǎng)絡(luò)開銷，查詢速度會很慢。

8.ARCHIVE

ARCHIVE存儲引擎只支持INSERT和SELECT操作，其設(shè)計的主要目的是提供高速的插入和壓縮功能。

適用場景：ARCHIVE非常適合存儲歸檔數(shù)據(jù)，如日志信息。

優(yōu)點(diǎn)：ARCHIVE存儲引擎被用來無索引地、非常小地覆蓋存儲的大量數(shù)據(jù)。為大量很少引用的歷史、歸檔或安全審計信息的存儲和檢索提供了完美的解決方案。

缺點(diǎn)：不支持事務(wù)，只支持INSERT和SELECT操作。

9.CSV

CSV存儲引擎把數(shù)據(jù)以逗號分隔的格式存儲在文本文件中。

10.BLACKHOLE

BLACKHOLE存儲引擎接收但不存儲數(shù)據(jù)，并且檢索總是返回一個空集。用于臨時禁止對數(shù)據(jù)庫的應(yīng)用程序輸入。該存儲引擎支持事務(wù)，而且支持MVCC的行級鎖，主要用于日志記錄或同步歸檔。

11.FEDERATED

FEDERATED存儲引擎不存放數(shù)據(jù)，它至少指向一臺遠(yuǎn)程MySQL數(shù)據(jù)庫服務(wù)器上的表，該存儲引擎把數(shù)據(jù)存在遠(yuǎn)程數(shù)據(jù)庫中，非常類似于Oracle的透明網(wǎng)關(guān)。在MySQL5.1中，它只和MySQL一起工作，使用MySQLCClientAPI。在未來的分發(fā)版中，想要讓它使用其他驅(qū)動器或客戶端連接方法連接到另外的數(shù)據(jù)源。該存儲引擎能夠?qū)⒍鄠€分離的MySQL服務(wù)器連接起來，從多個物理服務(wù)器創(chuàng)建一個邏輯數(shù)據(jù)庫，十分適合于分布式環(huán)境或數(shù)據(jù)集市環(huán)境。

12.ISAM

最原始的存儲引擎就是ISAM，它管理著非事務(wù)性表，后來被MyISAM代替了，而且MyISAM是向后兼容的，因此可以忘記這個ISAM存儲引擎。

可以在MySQL中使用顯示引擎的命令得到一個可用引擎的列表，如下：

上面查詢結(jié)果顯示了可用的數(shù)據(jù)庫引擎的全部名單以及在當(dāng)前的數(shù)據(jù)庫服務(wù)器中是否支持這些引擎。

下面的表格列出了一些常見的比較重要的存儲引擎。

15.

如何設(shè)置MySQL的存儲引擎?正確答案：可以使用多種方法指定一個要使用的存儲引擎。如果想用一種能滿足大多數(shù)數(shù)據(jù)庫需求的存儲引擎，那么可以在MySQL的配置文件(f)中設(shè)置一個默認(rèn)的引擎類型(在[mysqld]組下，使用default-storage-engine=InnoDB)，或者在啟動數(shù)據(jù)庫服務(wù)器時，在命令行后面加上“--default-storage-engine”選項(xiàng)。

最直接的使用存儲引擎的方式是在創(chuàng)建表時指定存儲引擎的類型，例如：

CREATETABLEmytable(idint,titlechar(20))ENGINE=INNODB

還可以改變現(xiàn)有的表使用的存儲引擎，用以下語句：

ALTERTABLEmytableENGINE=MyISAM;

然而，當(dāng)用這種方式修改表類型的時候需要非常仔細(xì)，因?yàn)閷Σ恢С滞瑯拥乃饕?、字段類型或者表大小的一個類型進(jìn)行修改可能導(dǎo)致數(shù)據(jù)的丟失。[考點(diǎn)]存儲引擎

16.

MyISAM和InnoDB各有哪些特性?分別適用在怎樣的場景下?正確答案：MyISAM支持表鎖，不支持事務(wù)，表損壞率較高，主要面向OLAP的應(yīng)用；MyISAM讀寫并發(fā)不如InnoDB，適用于以SELECT和INSERT為主的場景，且支持直接復(fù)制文件，用以備份數(shù)據(jù)；只緩存索引文件，不緩存數(shù)據(jù)文件。InnoDB支持行鎖，支持事務(wù)，CRASH后具有RECOVER機(jī)制，其設(shè)計目標(biāo)主要面向OLTP的應(yīng)用。

它們之間其他的區(qū)別可以參考下表。比較項(xiàng)目MyISAMInnoDB構(gòu)成上的區(qū)別每個存儲引擎類型為MyISAM的表在磁盤上存儲成3個文件：文件擴(kuò)展名為.frm(frame)的文件存儲了表定義；文件擴(kuò)展名為.MYD(MYData)的文件存儲了表數(shù)據(jù)；文件擴(kuò)展名為MYI(MYIndex)的文件存儲了索引。數(shù)據(jù)文件和索引文件可以放置在不同的目錄下，平均分布I/O，以獲得更快的速度每個存儲引擎類型為InnoDB的表在磁盤上存儲成2個文件：.frm和ibd文件。.frm文件存儲了表定義。ibd文件分為數(shù)據(jù)區(qū)和索引區(qū)，有較好的讀寫并發(fā)能力事務(wù)處理MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行速度比InnoDB類型更快，但是不提供事務(wù)支持InnoDB提供事務(wù)支持、外鍵等高級數(shù)據(jù)庫功能。InnoDB存儲引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引適用場景如果執(zhí)行大量的SELECT，那么MyISAM是更好的選擇1)如果執(zhí)行大量的INSERT或UPDATE，那么出于性能方面的考慮，應(yīng)該使用InnoDB表2)當(dāng)執(zhí)行DELETEFROMtable時，InnoDB不會重建表，而是一行一一行地刪除3)LOADTABLEFROMMASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對于使用的額外的InnoDB特性(例如外鍵)的表不適用清空表MyISAM會重建表InnoDB是一行一行地刪除，效率非常慢對AUTO_INCREMENT列的操作1)MyISAM為INSERT和UPDATE操作自動更新這一列。AUTO_INCREMENT值可用ALTERTABLE來重置2)對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引如果為一個表指定AUTO_INCREMENT列，那么在數(shù)據(jù)字典里的InnoDB表句柄包含一個名為自動增長計數(shù)器的計數(shù)器，它被用在為該列賦新值，自動增長計數(shù)器僅被存儲在主內(nèi)存中，而不是存在磁盤上。InnoDB中必須包含只有該字段的索引表的行數(shù)當(dāng)執(zhí)行SQL語句“SELECTCOUNT(*)FROMTABLE”時，MyISAM只是簡單地讀出保存好的行數(shù)，需要注意的是，當(dāng)COUNT(*)語句包含WHERE條件時，MyISAM和InnoDB的操作是一樣的InnoDB中不保存表的具體行數(shù)，也就是說，當(dāng)執(zhí)行SELECTCOUNT(*)FROMTABLE時，InnoDB要掃描一遍整個表來計算行數(shù)，所以，InnoDB在做COUNT運(yùn)算時相當(dāng)消耗CPU鎖表級鎖定(更新時鎖定整個表)：其鎖定機(jī)制是表級索引，這雖然可以讓鎖定的實(shí)現(xiàn)成本很小，但是也同時大大降低了其并發(fā)性能。不支持行級鎖，只支持并發(fā)插入的表鎖，主要用于高負(fù)載的SELECT提供行鎖(LockingonRowLevel)，提供與Oracle類型一致的不加鎖讀取(Non-lockingRead)，另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，那么InnoDB表同樣會鎖全表，例如UPDATETABLET_TEST_LHRSETNUM=1WHERENAMELIKE"%LHR%"開發(fā)公司MySQL公司InnoDB公司是否默認(rèn)存儲引擎是(5.5.8以前)是(5.5.8及其以后)[考點(diǎn)]存儲引擎

17.

MySQLInnoDB引擎類型的表有哪兩類表空間模式?它們各有什么優(yōu)缺點(diǎn)?正確答案：InnoDB存儲表和索引有以下兩種方式：

1)使用共享表空間存儲，這種方式創(chuàng)建的表的表結(jié)構(gòu)保存在.frm文件中。InnoDB的所有數(shù)據(jù)和索引保存在一個單獨(dú)的表空間(由參數(shù)innodb_data_home_dir和innodb_data_file_path定義，若innodb_data_home_dir為空，則默認(rèn)存放在datadir下，初始化大小為10MB)里面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。

2)使用獨(dú)立表空間(多表空間)存儲，這種方式創(chuàng)建的表的表結(jié)構(gòu)仍然保存在.frm文件中，但是每個表的數(shù)據(jù)和索引單獨(dú)保存在.ibd中。如果是個分區(qū)表，那么每個分區(qū)對應(yīng)單獨(dú)的.ibd文件，文件名是“表名+分區(qū)名”，可以在創(chuàng)建分區(qū)的時候指定每個分區(qū)的數(shù)據(jù)文件的位置，以此來將表的I/O均勻分布在多個磁盤上。

若要使用獨(dú)立表空間的存儲方式，那么需要設(shè)置參數(shù)innodb_file_per_table為ON，并且重新啟動服務(wù)后才可以生效。修改innodb_file_per_table的參數(shù)值即可修改數(shù)據(jù)庫的默認(rèn)表空間管理方式，但是修改不會影響之前已經(jīng)使用過的共享表空間和獨(dú)立表空間。

ON代表獨(dú)立表空間管理，OFF代表共享表空間管理。若要查看單表的表空間管理方式，則需要查看每個表是否有單獨(dú)的數(shù)據(jù)文件。該參數(shù)從MySQL5.6.6開始默認(rèn)為ON(之前的版本均為OFF)，表示默認(rèn)為獨(dú)立表空間管理。

獨(dú)立表空間的數(shù)據(jù)文件沒有大小限制，不需要設(shè)置初始大小，也不需要設(shè)置文件的最大限制、擴(kuò)展大小等參數(shù)。對于使用多表空間特性的表，可以比較方便地進(jìn)行單表備份和恢復(fù)操作，但是直接復(fù)制.ibd文件是不行的，因?yàn)闆]有共享表空間的數(shù)據(jù)字典信息，直接復(fù)制的.ibd文件和.frm文件恢復(fù)時是不能被正確識別的，但可以通過命令：“ALTERTABLEtb_nameDISCARDTABLESPACE;”和“ALTERTABLEtb_nameIMPORTTABLESPACE;”將備份恢復(fù)到數(shù)據(jù)庫中，但是這樣的單表備份，只能恢復(fù)到表原來所在的數(shù)據(jù)庫中，而不能恢復(fù)到其他的數(shù)據(jù)庫中。如果要將單表恢復(fù)到目標(biāo)數(shù)據(jù)庫，那么需要通過mysqldump和mysqlimport來實(shí)現(xiàn)。

需要注意的是，即便在獨(dú)立表空間的存儲方式下，共享表空間仍然是必需的。InnoDB會把內(nèi)部數(shù)據(jù)字典、在線重做日志、Undo信息、插入緩沖索引頁、二次寫緩沖(DoubleWriteBuffer)等內(nèi)容放在這個文件中。

共享表空間和獨(dú)立表空間的優(yōu)缺點(diǎn)見下表。

共享表空間(SharedTablespaces)獨(dú)立表空間(File-Per-TableTablespaces)優(yōu)點(diǎn)1)表空間可以分成多個文件存放到各個磁盤，所以表也就可以分成多個文件存放在磁盤上，表的大小不受磁盤大小的限制2)數(shù)據(jù)和文件放在一起方便管理1)當(dāng)truncate或者drop一個表時可以釋放磁盤空間。如果不是獨(dú)立表空間，truncate或drop一個表只是在ibdata文件內(nèi)部釋放，實(shí)際ibdata文件并不會縮小，釋放出來的空間也只能讓其似InnoDB引擎的表使用2)獨(dú)立表空間下，truncatetable操作會更快3)獨(dú)立表空間下，可以自定義表的存儲位置，通過CREATETABLE...DATADIRECTDRY=absolute_path_to_directory命令實(shí)現(xiàn)(有時將部分熱表放在不同的磁盤可有效地提升I/O性能)4)獨(dú)立表空間下，可以回收表空間碎片(比如一個非常大的DELETE操作之后釋放的空間)5)可以移動單獨(dú)的InnoDB表，而不是整個數(shù)據(jù)庫6)可以copy單獨(dú)的InnoDB表從一個實(shí)例到另外一個實(shí)例(也就是transportabletablespace特色)7)獨(dú)立表空間模式下，可以使用Barracuda的文件格式，這個文件格式有壓縮和動態(tài)行模式(DynamicRowFormat)的特色。當(dāng)表中有blob或者text字段時，動態(tài)行模式可以發(fā)揮出更高彭的存儲8)獨(dú)立表空間模式下，可以更好地改善故障恢復(fù)，比如更加節(jié)約時間或者增加崩潰后正?；謴?fù)的概率9)單獨(dú)備份和恢復(fù)某張表時會更快10)可以從一個備份中單獨(dú)分離出表，比如一個lvm的快照備份11)可以在不訪問MySQL的情況下方便地得知一個表的大小，即在文件系統(tǒng)的角度上查看12)在大部分的linux文件系統(tǒng)中，如果InnoDB_flush_method為O_DIRECT，通常是不允許針對同一個文件做并發(fā)寫操作的。這時如果為獨(dú)立表空間模式，那么應(yīng)該會有較大的性能提升13)如果沒有使用獨(dú)立表空間模式，那么所有的表都在共享表空間，最大為64TB，如果使用innodb_file_per_table，那么每個表可以達(dá)到64TB14)運(yùn)行OPTIMEIZETABLE，壓縮或者重新創(chuàng)建表空間。運(yùn)行OPTIMIZETABLEInnoDB會創(chuàng)建一個新的ibd文件。當(dāng)完成時，老的表空間會被新的代替缺點(diǎn)1)所有的數(shù)據(jù)和索引存放到一個文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，當(dāng)數(shù)據(jù)量非常大的時候，表做了火量刪除操作后表空間中將會有火量的空隙，特別是對于統(tǒng)計分析，對于經(jīng)常刪除操作的這類應(yīng)用最不適合用共享表空間2)共享表空間分配后不能回縮：當(dāng)臨時建索引或創(chuàng)建一個臨時表后，表空間被擴(kuò)大后，就是刪除相關(guān)的表也沒辦法回縮那部分空間3)進(jìn)行數(shù)據(jù)庫的冷備很慢1)獨(dú)立表空間模式下，每個表或許會有很多沒用到的磁盤空間。如果沒做好管理，可能會造成較大的空間浪費(fèi)。表空間中的空間只能被當(dāng)前表使用2)fsync操作必須運(yùn)行在每一個單一的文件上，獨(dú)立表空間模式下，多個表的寫操作就無法臺并為一個單一的I/O，這樣就添加許多額外的fsync操作3)mysqld必須保證每個表都有一個openfile，獨(dú)立表空間模式下，這樣就需要很多打開文付數(shù)，可能會影響性能4)當(dāng)drop一個表空間時，bufferpool會被掃描，如果bufferpool有幾十GB那么大，或許要花費(fèi)幾秒鐘時間。這個掃描操作還會產(chǎn)生一個內(nèi)部鎖，可能會延遲其他操作，共享表空間模式下不會有這個問題5)如果許多表都增長迅速，那么可能會產(chǎn)生更多的分裂操作(應(yīng)該指的是表空間大小的擴(kuò)充)，這個操作會損害droptable和tablescan的性能6)InnoDB_autoextend_increment參數(shù)對獨(dú)立表空間無效，這個參數(shù)指的是當(dāng)系統(tǒng)表空間滿了以后，它再次預(yù)先申請的磁盤空間大小，單位為MB7)單表增加過大，當(dāng)單表占用空間過大時，存儲空間不足，只能從操作系統(tǒng)層面思考解決方法[考點(diǎn)]存儲引擎

18.

什么是間隙(Next-Key)鎖?正確答案：當(dāng)使用范圍條件而不是相等條件檢索數(shù)據(jù)的時候，并請求共享或排它鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫作“間隙(GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙(Next-Key)鎖。間隙鎖是InnoDB中行鎖的一種，但是這種鎖鎖住的不止一行數(shù)據(jù)，它鎖住的是多行，是一個數(shù)據(jù)范圍。間隙鎖的主要作用是為了防止出現(xiàn)幻讀(PhantomRead)，用在Repeated-Read(簡稱RR)隔離級別下。在Read-commited(簡稱RC)下，一般沒有間隙鎖(有外鍵情況下例外，此處不考慮)。間隙鎖還用于恢復(fù)和復(fù)制。

間隙鎖的出現(xiàn)主要集中在同一個事務(wù)中先DELETE后INSERT的情況，當(dāng)通過一個條件刪除一條記錄的時候，如果條件在數(shù)據(jù)庫中已經(jīng)存在，那么這個時候產(chǎn)生的是普通行鎖，鎖住這個記錄，然后刪除，最后釋放鎖。如果這條記錄不存在，那么問題就來了，數(shù)據(jù)庫會掃描索引，發(fā)現(xiàn)這個記錄不存在，這個時候的DELETE語句獲取到的就是一個間隙鎖，然后數(shù)據(jù)庫會向左掃描，掃到第一個比給定參數(shù)小的值，向右掃描，掃描到第一個比給定參數(shù)大的值，然后以此為界，構(gòu)建一個區(qū)間，鎖住整個區(qū)間內(nèi)的數(shù)據(jù)，一個特別容易出現(xiàn)死鎖的間隙鎖就誕生了。

在MySQL的InnoDB存儲引擎中，如果更新操作是針對一個區(qū)間的，那么它會鎖住這個區(qū)間內(nèi)所有的記錄，例如，UPDATEXXXXWHEREIDBETWEENAANDB，那么它會鎖住A到B之間所有記錄，注意是所有記錄，甚至這個記錄并不存在也會被鎖住，這個時候，如果另外一個連接需要插入一條記錄到A與B之間，那么它就必須等到上一個事務(wù)結(jié)束。典型的例子就是使用AUTO_INCREMENTID，由于這個ID是一直往上分配的，因此，當(dāng)兩個事務(wù)都INSERT時，會得到兩個不同的ID，但是這兩條記錄還沒有被提交，因此，也就不存在，如果這個時候有一個事務(wù)進(jìn)行范圍操作，而且恰好要鎖住不存在的ID，就是觸發(fā)間隙鎖問題。所以，MySQL中盡量不要使用區(qū)間更新。InnoDB除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，那么InnoDB也會使用間隙鎖!

間隙鎖也存在副作用，它會把鎖定范圍擴(kuò)大，有時候也會帶來麻煩。如果要關(guān)閉，那么一是將會話隔離級別改到RC下，或者開啟innodb_locks_unsafe_for_binlog(默認(rèn)是OFF)。間隙鎖只會出現(xiàn)在輔助索引上，唯一索引和主鍵索引是沒有間隙鎖。間隙鎖(無論是S還是X)只會阻塞INSERT操作。

在MySQL數(shù)據(jù)庫參數(shù)中，控制間隙鎖的參數(shù)是innodb_locks_unsafe_for_binlog，這個參數(shù)的默認(rèn)值是OFF，也就是啟用間隙鎖，它是一個布爾值，當(dāng)值為TRUE時，表示DISABLE間隙鎖。[考點(diǎn)]鎖

19.

MySQL有哪些命令可以查看鎖?正確答案：有如下幾個命令：

(1)showprocesslist

“showprocesslist;”顯示哪些線程正在運(yùn)行。如果有SUPER權(quán)限，那么就可以看到所有線程。如果有線程在UPDATE或者INSERT某個表，此時進(jìn)程的status為updating或者sendingdata?！皊howprocesslist;”只列出前100條，如果想全列出，那么可以使用“showfullprocesslist;”。

下面是一些常見的狀態(tài)。狀態(tài)含義Checkingtable正在檢查數(shù)據(jù)表(這是自動的)Closingtables正在將表中修改的數(shù)據(jù)刷新到磁盤中，同時正在關(guān)閉已經(jīng)用完的表。這是一個很快的操作，如果不是這樣的話，那么就應(yīng)該確認(rèn)磁盤空間是否已經(jīng)滿了或者磁盤是甭正處于重負(fù)中Connectout復(fù)制從服務(wù)器正在連接主服務(wù)器Copyingtotmptableondisk由于臨時結(jié)果集大于map_table_size，正在將臨時表從內(nèi)存存儲轉(zhuǎn)為磁盤存儲，以此節(jié)省內(nèi)存Creatingtmptable正在創(chuàng)建臨時表以存放部分查詢結(jié)果Deletingfrommaintable服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個表Deletingfromreferencetables服務(wù)器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄Flushingtables正在執(zhí)行FLUSHTABLES，等待其他線程關(guān)閉數(shù)據(jù)表Kilied發(fā)送了一個kill請求給某線程，那么這個線程將會檢查kill標(biāo)志位，同時會放棄下一個kill請求。MySQL會在每次的主循環(huán)中檢查kill標(biāo)志位，不過有些情況下該線程可能會過一小段才能死掉。如果該線程被其他線程鎖住了，那么kill請求會在鎖釋放時馬上生效Locked被其他查詢鎖住了Sendingdata正在處理SELECT查詢的記錄，同時正在把結(jié)果發(fā)送給客戶端Sortingforgroup正在為GROUPBY做排序Sortingfororder正在為ORDERBY做排序Openingtables這個過程應(yīng)該會很快，除非受到其他因素的干擾。例如，在執(zhí)行完ALTERTABLE或LOCKTABLE語句以前，數(shù)據(jù)表無法被其他線程打開。正嘗試打開一個表Removingduplicates正在執(zhí)行一個SELECTDISTINCT方式的查詢，但是MySQL無法在前一個階段優(yōu)化掉那些重復(fù)的記錄。因此，MySQL需要再次去掉重復(fù)的記錄，然后再把結(jié)果發(fā)送給客戶端Reopentable獲得了對一個表的鎖，但是必須在表結(jié)構(gòu)修改之后才能獲得這個鎖。已經(jīng)釋放鎖，關(guān)閉數(shù)據(jù)表，正嘗試重新打開數(shù)據(jù)表Repairbysorting修復(fù)指令正在排序以創(chuàng)建索引Repairwithkeycache修復(fù)指令正在利用索引緩存一個一個地創(chuàng)建新索引。它會比Repairbysorting慢些Searchingrowsforupdate正在將符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關(guān)的記錄之前就完成了Sleeping正在等待客戶端發(fā)送新請求Systemlock正在等待取得一個外部的系統(tǒng)鎖。如果當(dāng)前沒有運(yùn)行多個mysqld服務(wù)器同時請求同一個表，那么可以通過增加--skip-extemal-locking參數(shù)來禁止外部系統(tǒng)鎖UpgradinglockINSERTDELAYED正在嘗試取得一個鎖表以插入新記錄Updating正在搜索匹配的記錄，并且修改它們Userlock正在等待GET_LOCK()Waitingfortables該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能重新打開數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個表。以下幾種情況下會產(chǎn)生這個通知：FLUSHTABLEStb1_name，ALTERTABLE，RENAMETABLE，REPAIRTABLE，ANALYZETABLE或OPTIMIZETABLEWaitingforhandlerinsertINSERTDELAYED已經(jīng)處理完了所有待處理的插入操作，正在等待新的請求

(2)showopentables

這條命令能夠查看當(dāng)前有哪些表是打開的。in_use列表示有多少線程正在使用某張表，name_locked表示表名是否被鎖，這一般發(fā)生在DROP或RENAME命令操作這張表時。所以這條命令不能查詢到當(dāng)前某張表是否有死鎖，誰擁有表上的這個鎖等。常用命令如下：

showopentablefromdb_name;

showopentableswherein_use＞0;

(3)showengineinnodbstatus\G;

這條命令查詢innodb引擎的運(yùn)行時信息。

(4)查看服務(wù)器的狀態(tài)

showstatuslike'%lock%';

(5)查詢INFORMATION_SCHEMA用戶下的表

通過INFORMATION_SHCEMA下的INNODB_LOCKS、INNODB_LOCK_WAITS和INNODB_TRX這三張表可以更新監(jiān)控當(dāng)前事務(wù)并且分析存在的鎖問題。

查看當(dāng)前狀態(tài)產(chǎn)生的innodb鎖，僅在有鎖等待時有結(jié)果輸出：

SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;

查看當(dāng)前狀態(tài)產(chǎn)生的innodb鎖等待，僅在有鎖等待時有結(jié)果輸出：

SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS;

當(dāng)前innodb內(nèi)核中的活躍(ACTIVE)事務(wù)：

SELECT*FROMINFORMATION_SCHEMA.INNODB_TRX;[考點(diǎn)]鎖

20.

MySQL如何查看執(zhí)行計劃?執(zhí)行計劃中每列的含義分別是什么?正確答案：執(zhí)行計劃的查看是進(jìn)行SQL語句調(diào)優(yōu)時的一個重要依據(jù)，MySQL的執(zhí)行計劃查看相對Oracle簡便很多，功能也相對簡單。MySQL的EXPLAIN命令用于SQL語句的查詢執(zhí)行計劃(QEP)。從這條命令的輸出結(jié)果中能夠了解MySQL優(yōu)化器是如何執(zhí)行SQL語句的。這條命令并沒有提供任何調(diào)整建議，但它能夠提供重要的信息用來幫助做出調(diào)優(yōu)決策。

MySQL的EXPLAIN語法可以運(yùn)行在SELECT語句或者特定表上。如果作用在表上，那么此命令等同于DESC表命令。在MySQL5.6.10版本里面，可以直接對DML語句進(jìn)行EXPLAIN分析操作。MySQL優(yōu)化器是基于開銷來工作的，它并不提供任何的QEP的位置。這意味著QEP是在每條SQL語句執(zhí)行的時候動態(tài)地計算出來的。在MySQL存儲過程中的SQL語句也是在每次執(zhí)行時計算QEP的。存儲過程緩存僅僅解析查詢樹。

下面給出一個MySQL執(zhí)行計劃的示例：

下面介紹每種指標(biāo)的含義：

(1)id

id包含一組數(shù)字，表示查詢中執(zhí)行SELECT子句或操作表的順序；執(zhí)行順序從大到小執(zhí)行；當(dāng)id值一樣的時候，執(zhí)行順序由上往下。

(2)select_type

select_type表示查詢中每個SELECT子句的類型，最常見的值包括SIMPLE、PRIMARY、DERIVED和UNION。其他可能的值還有UNIONRESULT、DEPENDENTSUBQUERY、DEPENDENTUNION、UNCACHEABLEUNION以及UNCACHEABLEQUERY。

1)SIMPLE：查詢中不包含子查詢、表連接或者UNION其他復(fù)雜語法的簡單查詢，這是一個常見的類型。

2)PRIMARY：查詢中若包含任何復(fù)雜的子查詢，最外層查詢則被標(biāo)記為PRIMARY。這個類型通?？梢栽贒ERIVED和UNION類型混合使用時見到。

3)SUBQUERY：在SELECT或WHERE列表中包含了子查詢，該子查詢被標(biāo)記為SUBQUERY。

4)DE