91道MySQL面試題附答案解析

最全91道MySQL面試題|附答案解析

數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)

L為什么要使用數(shù)據(jù)庫(kù)

■數(shù)據(jù)保存在內(nèi)存

優(yōu)點(diǎn)：存取速度快

缺點(diǎn)：數(shù)據(jù)不能永久保存

■數(shù)據(jù)保存在文件

優(yōu)點(diǎn)：數(shù)據(jù)永久保存

缺點(diǎn)：1）速度比內(nèi)存操作慢，頻繁的10操作。2）查詢數(shù)據(jù)

不方便

■數(shù)據(jù)保存在數(shù)據(jù)庫(kù)

1）數(shù)據(jù)永久保存

2）使用SQL語(yǔ)句，查詢方便效率高。

3）管理數(shù)據(jù)方便

2.什么是SQL?

結(jié)構(gòu)化查詢語(yǔ)言(StructuredQueryLanguage)簡(jiǎn)稱SQL,是

一種數(shù)據(jù)庫(kù)查詢語(yǔ)言。

作用：用于存取數(shù)據(jù)、查詢、更新和管理關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)。

3.什么是MySQL?

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQLAB公

司開(kāi)發(fā)，屬于Oracle旗下產(chǎn)品。MySQL是最流行的關(guān)系型

數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在WEB應(yīng)用方面，MySQL是最好的

RDBMS(RelationalDatabaseManagementSystem,關(guān)系

數(shù)據(jù)庫(kù)管理系統(tǒng))應(yīng)用軟件之一。在Java企業(yè)級(jí)開(kāi)發(fā)中非常常

用，因?yàn)镸ySQL是開(kāi)源免費(fèi)的，并且方便擴(kuò)展。

4.數(shù)據(jù)庫(kù)三大范式是什么

第一范式：每個(gè)列都不可以再拆分。

第二范式：在第一范式的基礎(chǔ)上，非主鍵列完全依賴于主鍵，

而不能是依賴于主鍵的一部分。

第三范式：在第二范式的基礎(chǔ)上，非主鍵列只依賴于主鍵，不

依賴于其他非主鍵。

在設(shè)計(jì)數(shù)據(jù)庫(kù)結(jié)構(gòu)的時(shí)候，要盡量遵守三范式，如果不遵守，

必須有足夠的理由。比如性能。事實(shí)上我們經(jīng)常會(huì)為了性能而

妥協(xié)數(shù)據(jù)庫(kù)的設(shè)計(jì)。

5.mysql有關(guān)權(quán)限的表都有哪幾個(gè)

MySQL服務(wù)器通過(guò)權(quán)限表來(lái)控制用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，權(quán)限

表存放在mysql數(shù)據(jù)庫(kù)里,由mysql_install_db腳本初始

化。這些權(quán)限表分別user,db,table_priv,columns_priv

和下面分別介紹一下這些表的結(jié)構(gòu)和內(nèi)容：

hosto

?user權(quán)限表：記錄允許連接到服務(wù)器的用戶帳號(hào)信息，里面

的權(quán)限是全局級(jí)的。

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

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

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

?host權(quán)限表：配合db權(quán)限表對(duì)給定主機(jī)上數(shù)據(jù)庫(kù)級(jí)操作權(quán)

限作更細(xì)致的控制。這個(gè)權(quán)限表不受GRANT和REVOKE語(yǔ)

句的影響。

6.MySQL的binlog有有幾種錄入格式？分別有什么區(qū)別？

有三種格式,和

statement,rowmixedo

?statement模式下，每一條會(huì)修改數(shù)據(jù)的sqI都會(huì)記錄在

binlog中。不需要記錄每一行的變化，減少了binlog日志

量，節(jié)約了10,提高性能。由于sql的執(zhí)行是有上下文的，

因此在保存的時(shí)候需要保存相關(guān)的信息，同時(shí)還有一些使用了

函數(shù)之類的語(yǔ)句無(wú)法被記錄復(fù)制。

?row級(jí)別下，不記錄sql語(yǔ)句上下文相關(guān)信息，僅保存哪條記

錄被修改。記錄單元為每一行的改動(dòng)，基本是可以全部記下來(lái)

但是由于很多操作，會(huì)導(dǎo)致大量行的改動(dòng)（比如avertable）,

因此這種模式的文件保存的信息太多，日志量太大。

?mixed,一種折中的方案，普通操作使用statement記錄，

當(dāng)無(wú)法使用的時(shí)候使用

statementrowo

此外，新版的MySQL中對(duì)row級(jí)別也做了一些優(yōu)化，當(dāng)表

結(jié)構(gòu)發(fā)生變化的時(shí)候，會(huì)記錄語(yǔ)句而不是逐行記錄。

數(shù)據(jù)類型

mysql有哪些數(shù)據(jù)類型

分類類型名稱

說(shuō)明

整數(shù)

tinylnt很小的整數(shù)（8位二進(jìn)制）

類型

smallint小的整數(shù)Q6位二進(jìn)制）

mediumint中等大小的整數(shù)（24位二進(jìn)制）

int(integer)普通大小的整數(shù)（32位二進(jìn)制）

分類類型名稱

說(shuō)明

小數(shù)

float單精度浮點(diǎn)數(shù)

類型

double雙精度浮點(diǎn)數(shù)

decimal(m,d)壓縮嚴(yán)格的定點(diǎn)數(shù)

日期

yearYYYY1901-2155

類型

timeHH:MM:SS-838:59:59~838:59:59

dateYYYY-MM-DD1000-01-01-9999-12-3

YYYY-MM-DDHH:MM:SS1000-01-

datetime

0100:00:00-9999-12-3123:59:59

timestampYYYY-MM-

DDHH:MM:SS1970010100:00:01UTC~2038

分類類型名稱

說(shuō)明

01-1903:14:07UTC

文

本、

二進(jìn)CHAR(M)M為0~255之間的整數(shù)

制類

型

VARCHAR(M)M為0~65535之間的整數(shù)

TINYBLOB允許長(zhǎng)度0~255字書(shū)

BLOB允許長(zhǎng)度0~65535字書(shū)

MEDIUMBLOB允許長(zhǎng)度0~167772150字節(jié)

LONGBLOB允許長(zhǎng)度0-4294967295字節(jié)

TINYTEXT允許長(zhǎng)度0~255字書(shū)

分類類型名稱

說(shuō)明

TEXT允許長(zhǎng)度0~65535字書(shū)

MEDIUMTEXT允許長(zhǎng)度0~167772150字節(jié)

LONGTEXT允許長(zhǎng)度0-4294967295字節(jié)

VARBINARY(M)允許長(zhǎng)度0~M個(gè)字書(shū)的變長(zhǎng)字書(shū)字符串

BINARY(M)允許長(zhǎng)度0~M個(gè)字書(shū)的定長(zhǎng)字節(jié)字符串

?L整數(shù)類型，包括TINYINT、SMALLINKMEDIUMINT、

INT、BIGINT,分別表示1字節(jié)、2字節(jié)、3字節(jié)、4字節(jié)、

8字節(jié)整數(shù)。任何整數(shù)類型都可以加上UNSIGNED屬性，表

示數(shù)據(jù)是無(wú)符號(hào)的，即非負(fù)整數(shù)。

?長(zhǎng)度：整數(shù)類型可以被指定長(zhǎng)度，例如：INTQ1）表示長(zhǎng)度為

11的INT類型。長(zhǎng)度在大多數(shù)場(chǎng)景是沒(méi)有意義的，它不會(huì)限

制值的合法范圍，只會(huì)影響顯示字符的個(gè)數(shù)，而且需要和

UNSIGNEDZEROFILL屬性配合使用才有意義。

?例子，假定類型設(shè)定為INT(5),屬性為

UNSIGNEDZEROFILL,如果用戶插入的數(shù)據(jù)為12的話,那

么數(shù)據(jù)庫(kù)實(shí)際存儲(chǔ)數(shù)據(jù)為00012。

?2、實(shí)數(shù)類型，包括FLOAT、DOUBLE.DECIMAL

?DECIMAL可以用于存儲(chǔ)比BIGINT還大的整型，能存儲(chǔ)精確

的小數(shù)。

?而FLOAT和DOUBLE是有取值范圍的，并支持使用標(biāo)準(zhǔn)的

浮點(diǎn)進(jìn)行近似計(jì)算。

?計(jì)算時(shí)FLOAT和DOUBLE相比DECIMAL效率更高一些，

DECIMAL你可以理解成是用字符串進(jìn)行處理。

?3、字符串類型，包括VARCHAR、CHAR、TEXT、BLOB

?VARCHAR用于存儲(chǔ)可變長(zhǎng)字符串，它比定長(zhǎng)類型更節(jié)省空

間。

?VARCHAR使用額外1或2個(gè)字節(jié)存儲(chǔ)字符串長(zhǎng)度。列長(zhǎng)度

小于255字節(jié)時(shí)，使用1字節(jié)表示，否則使用2字節(jié)表示。

?VARCHAR存儲(chǔ)的內(nèi)容超出設(shè)置的長(zhǎng)度時(shí)，內(nèi)容會(huì)被截?cái)唷?/p>

?CHAR是定長(zhǎng)的，根據(jù)定義的字符串長(zhǎng)度分配足夠的空間。

?CHAR會(huì)根據(jù)需要使用空格進(jìn)行填充方便比較。

?CHAR適合存儲(chǔ)很短的字符串，或者所有值都接近同一個(gè)長(zhǎng)

度。

?CHAR存儲(chǔ)的內(nèi)容超出設(shè)置的長(zhǎng)度時(shí)，內(nèi)容同樣會(huì)被截?cái)唷?/p>

使用策略：

對(duì)于經(jīng)常變更的數(shù)據(jù)來(lái)說(shuō),CHAR比VARCHAR更好，因?yàn)?/p>

CHAR不容易產(chǎn)生碎片。

對(duì)于非常短的列，CHAR比VARCHAR在存儲(chǔ)空間上更有效

率。

使用時(shí)要注意只分配需要的空間，更長(zhǎng)的列排序時(shí)會(huì)消耗更多

內(nèi)存。

盡量避免使用TEXT/BLOB類型，查詢時(shí)會(huì)使用臨時(shí)表，導(dǎo)致

嚴(yán)重的性能開(kāi)銷。

?4、枚舉類型（ENUM），把不重復(fù)的數(shù)據(jù)存儲(chǔ)為一個(gè)預(yù)定義

的集合。

?有時(shí)可以使用ENUM代替常用的字符串類型。

?ENUM存儲(chǔ)非常緊湊,會(huì)把列表值壓縮到一個(gè)或兩個(gè)字節(jié)。

?ENUM在內(nèi)部存儲(chǔ)時(shí)，其實(shí)存的是整數(shù)。

?盡量避免使用數(shù)字作為ENUM枚舉的常量，因?yàn)槿菀谆靵y。

?排序是按照內(nèi)部存儲(chǔ)的整數(shù)

?5、日期和時(shí)間類型，盡量使用timestamp,空間效率高于

datetime,

?用整數(shù)保存時(shí)間戳通常不方便處理。

?如果需要存儲(chǔ)微妙，可以使用bigint存儲(chǔ)。

?看到這里，這道真題是不是就比較容易回答了。

引擎

1.MySQL存儲(chǔ)引擎MylSAM與InnoDB區(qū)別

存儲(chǔ)弓I擎Storageengine:MySQL中的數(shù)據(jù)、索引以及其

他對(duì)象是如何存儲(chǔ)的，是一套文件系統(tǒng)的實(shí)現(xiàn)。

常用的存儲(chǔ)引擎有以下：

?Innodb引擎:Innodb引擎提供了對(duì)數(shù)據(jù)庫(kù)ACID事務(wù)的支

持。并且還提供了行級(jí)鎖和外鍵的約束。它的設(shè)計(jì)的目標(biāo)就是

處理大數(shù)據(jù)容量的數(shù)據(jù)庫(kù)系統(tǒng)。

?MylASM引擎（原本Mysql的默認(rèn)引擎）：不提供事務(wù)的支

持，也不支持行級(jí)鎖和外鍵。

?MEMORY引擎：所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度

快，但是安全性不高。

2.MyISAM與InnoDB區(qū)別

MylSAMInnodb

存儲(chǔ)結(jié)構(gòu)每張表被存放在三所有的表都保存在

個(gè)文件：frm-表格同一個(gè)數(shù)據(jù)文件中

MylSAMInnodb

定義、(也可能是多個(gè)文

MYD(MYData)-數(shù)件，或者是獨(dú)立的

據(jù)文件、表空間文件)，

MYI(MYIndex)-索InnoDB表的大小

引文件只受限于操作系統(tǒng)

文件的大小，一般

為2GB

InnoDB的表需要

更多的內(nèi)存和存

MylSAM可被壓儲(chǔ)，它會(huì)在主內(nèi)存

存儲(chǔ)空間

縮，存儲(chǔ)空間較小中建立其專用的緩

沖池用于同)速緩沖

數(shù)據(jù)和索引

由于MylSAM的數(shù)免費(fèi)的方案可以是

據(jù)是以文件的形式拷貝數(shù)據(jù)文件、備

存儲(chǔ)，所以在跨平份binlog,或者用

可移植性、備份

臺(tái)的數(shù)據(jù)轉(zhuǎn)移中會(huì)mysqldump,在

及恢復(fù)

很方便。在備份和數(shù)據(jù)量達(dá)到幾十G

恢復(fù)時(shí)可單獨(dú)針對(duì)的時(shí)候就相對(duì)痛苦

某個(gè)表進(jìn)行操作了

MylSAMInnodb

數(shù)據(jù)和索引是分別

數(shù)據(jù)和索引是集中

文件格式存儲(chǔ)的，數(shù)

存儲(chǔ)的,.ibd

據(jù).MYD,索引.MYI

按記錄插入順序保按主鍵大小有序插

記錄存儲(chǔ)順序

存入

外鍵不支持支持

事務(wù)不支持支持

鎖支持（鎖是避

免資源爭(zhēng)用的一行級(jí)鎖定、表級(jí)鎖

個(gè)機(jī)制，MySQL表級(jí)鎖定定，鎖定力度小并

鎖對(duì)用戶幾乎是發(fā)能力高

透明的）

SELECTMylSAM更優(yōu)

INSERT,InnoDB更優(yōu)

UPDATE.

MylSAMInnodb

DELETE

myisam更快，因

為myisam內(nèi)部維

selectcount(*)

護(hù)了一個(gè)計(jì)數(shù)器，

可以直接調(diào)取。

B+樹(shù)索引，

B+樹(shù)索引，

索引的實(shí)現(xiàn)方■式Innodb是索引組

myisam是堆表

織表

哈希索引不支持支持

全文索引支持不支持

3.MyISAM索引與InnoDB索引的區(qū)別?

?IrmoDB索引是聚簇索引，MylSAM索引是非聚簇索引。

?InnoDB的主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)著行數(shù)據(jù)，因此主鍵索引

非常高效。

?MylSAM索引的葉子節(jié)點(diǎn)存儲(chǔ)的是行數(shù)據(jù)地址，需要再尋址

一次才能得到數(shù)據(jù)。

?InnoDB非主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和其他帶索引的

列數(shù)據(jù)，因此查詢時(shí)做到覆蓋索引會(huì)非常高效。

4.InnoDB引擎的4大特性

?插入緩沖(insertbuffer)

?二次寫(xiě)(doublewrite)

?自適應(yīng)哈希索引(ahi)

?預(yù)讀(readahead)

5.存儲(chǔ)引擎選擇

如果沒(méi)有特別的需求，使用默認(rèn)的Innodb即可。

MylSAM:以讀寫(xiě)插入為主的應(yīng)用程序，比如博客系統(tǒng)、新

聞門戶網(wǎng)站。

Innodb:更新(刪除)操作頻率也高，或者要保證數(shù)據(jù)的完

整性；并發(fā)量高，支持事務(wù)和外鍵。比如0A自動(dòng)化辦公系

統(tǒng)。

索引

1.什么是索引？

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的

一個(gè)組成部分),它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指

針。

索引是一種數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)庫(kù)索引，是數(shù)據(jù)庫(kù)管理系統(tǒng)中一個(gè)

排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫(kù)表中數(shù)據(jù)。索

引的實(shí)現(xiàn)通常使用B樹(shù)及其變種B+樹(shù)。

更通俗的說(shuō)，索引就相當(dāng)于目錄。為了方便查找書(shū)中的內(nèi)容，

通過(guò)對(duì)內(nèi)容建立索引形成目錄。索引是一個(gè)文件，它是要占據(jù)

物理空間的。

2.索引有哪些優(yōu)缺點(diǎn)？

索引的優(yōu)點(diǎn)

?可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原

因。

?通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高

系統(tǒng)的性能。

索引的缺點(diǎn)

?時(shí)間方面：創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，具體地，當(dāng)對(duì)表

中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維

護(hù)，會(huì)降低增/改/刪的執(zhí)行效率；

?空間方面：索引需要占物理空間。

3.索引使用場(chǎng)景（重點(diǎn)）

where

EXPLAINselect*frominnodblwhereid<20

真正用來(lái)檢索的

可選的索引索引

IB結(jié)果1概況狀態(tài)

idselecttypetablepartitionstypepossiblekeyd卜y|

■SIMPLEinnodblrange11PRIMARYIPRIMARY1

上圖中，根據(jù)id查詢記錄，因?yàn)閕d字段僅建立了主鍵索弓I,

因此此SQL執(zhí)行可選的索引只有主鍵索引，如果有多個(gè)，最

終會(huì)選一個(gè)較優(yōu)的作為檢索的依據(jù)。

一增加一個(gè)沒(méi)有建立索引的字段

altertableinnodbladdsexchar(l);

一按sex檢索時(shí)可選的索引為null

EXPLAINSELECT*frominnodblwheresex='男

可以嘗試在一個(gè)字段未建立索引時(shí)，根據(jù)該字段查詢的效

率，然后對(duì)該字段建立索引（altertable表名addindex（字

段名））,同樣的SQL執(zhí)行的效率，你會(huì)發(fā)現(xiàn)查詢效率會(huì)有

明顯的提升（數(shù)據(jù)量越大越明顯）。

orderby

當(dāng)我們使用orderby將查詢結(jié)果按照某個(gè)字段排序時(shí)，如果

該字段沒(méi)有建立索引，那么執(zhí)行計(jì)劃會(huì)將查詢出的所有數(shù)據(jù)使

用外部排序（將數(shù)據(jù)從硬盤(pán)分批讀取到內(nèi)存使用內(nèi)部排序，最

后合并排序結(jié)果），這個(gè)操作是很影響性能的，因?yàn)樾枰獙⒉?/p>

詢涉及到的所有數(shù)據(jù)從磁盤(pán)中讀到內(nèi)存（如果單條數(shù)據(jù)過(guò)大或

者數(shù)據(jù)量過(guò)多都會(huì)降低效率），更無(wú)論讀到內(nèi)存之后的排序

了。

但是如果我們對(duì)該字段建立索引avertable表名

addindex（字段名），那么由于索引本身是有序的，因此直接

按照索引的順序和映射關(guān)系逐條取出數(shù)據(jù)即可。而且如果分頁(yè)

的，那么只用取出索引表某個(gè)范圍內(nèi)的索引對(duì)應(yīng)的數(shù)據(jù)，而不

用像上述那取出所有數(shù)據(jù)進(jìn)行排序再返回某個(gè)范圍內(nèi)的數(shù)據(jù)。

（從磁盤(pán)取數(shù)據(jù)是最影響性能的）

join

對(duì)join語(yǔ)句匹配關(guān)系（on）涉及的字段建立索引能夠提高

效率

索引覆蓋

如果要查詢的字段都建立過(guò)索引，那么引擎會(huì)直接在索引表中

查詢而不會(huì)訪問(wèn)原始數(shù)據(jù)（否則只要有一個(gè)字段沒(méi)有建立索引

就會(huì)做全表掃描），這叫索引覆蓋。因此我們需要盡可能的在

select后只寫(xiě)必要的查詢字段，以增加索引覆蓋的幾率。

這里值得注意的是不要想著為每個(gè)字段建立索引，因?yàn)閮?yōu)先使

用索引的優(yōu)勢(shì)就在于其體積小。

4.索引有哪幾種類型？

主鍵索引:數(shù)據(jù)列不允許重復(fù)，不允許為NULL,一個(gè)表只能

有一個(gè)主鍵。

唯一索引:數(shù)據(jù)列不允許重復(fù)，允許為NULL值，一個(gè)表允許

多個(gè)列創(chuàng)建唯一索引。

?可以通過(guò)ALTERTABLEtable_nameADDUNIQUE(column);

創(chuàng)建唯一索引

?可以通過(guò)

ALTERTABLEtable_nameADDUNIQUE(columnl,column2

)；創(chuàng)建唯一組合索引

普通索引:基本的索引類型，沒(méi)有唯一性的限制，允許為

NULL值。

?可以通過(guò)

ALTERTABLEtable_nameADDINDEXindex_name(column

)；創(chuàng)建普通索引

?可以通過(guò)

ALTERTABLEtable_nameADDINDEXindex_name(column

Lcolumn2,column3);創(chuàng)建組合索引

全文索引：是目前搜索引擎使用的一種關(guān)鍵技術(shù)。

?可以通過(guò)

ALTERTABLEtable_nameADDFULLTEXT(column)滄(］建全

文索引

5.索引的數(shù)據(jù)結(jié)構(gòu)（b樹(shù)，hash）

索引的數(shù)據(jù)結(jié)構(gòu)和具體存儲(chǔ)引擎的實(shí)現(xiàn)有關(guān)，在MySQL中使

用較多的索引有Hash索引，B+樹(shù)索引等，而我們經(jīng)常使用

的InnoDB存儲(chǔ)引擎的默認(rèn)索引實(shí)現(xiàn)為：B+樹(shù)索引。對(duì)于哈

希索引來(lái)說(shuō)，底層的數(shù)據(jù)結(jié)構(gòu)就是哈希表，因此在絕大多數(shù)需

求為單條記錄查詢的時(shí)候，可以選擇哈希索引，查詢性能最

快；其余大部分場(chǎng)景，建議選擇BTree索引。

1）B樹(shù)索引

mysql通過(guò)存儲(chǔ)引擎取數(shù)據(jù)，基本上90%的人用的就是

InnoDB了，按照實(shí)現(xiàn)方式分，InnoDB的索引類型目前只有

兩種：BTREE（B樹(shù)）索引和HASH索弓|。B樹(shù)索引是

Mysql數(shù)據(jù)庫(kù)中使用最頻繁的索引類型，基本所有存儲(chǔ)引擎

都支持BTree索引。通常我們說(shuō)的索引不出意外指的就是（B

樹(shù)）索引（實(shí)際是用B+樹(shù)實(shí)現(xiàn)的，因?yàn)樵诓榭幢硭饕龝r(shí)，

mysql一律打印BTREE,所以簡(jiǎn)稱為B樹(shù)索引）

查詢方式：

主鍵索引區(qū):PI（關(guān)聯(lián)保存的時(shí)數(shù)據(jù)的地址）按主鍵查詢，

普通索引區(qū):si（關(guān)聯(lián)的id的地址，然后再到達(dá)上面的地址）。所

以按主鍵查詢，速度最快

B+tree性質(zhì):

1.）n棵子tree的節(jié)點(diǎn)包含n個(gè)關(guān)鍵字，不用來(lái)保存數(shù)據(jù)而

是保存數(shù)據(jù)的索引。

2.）所有的葉子結(jié)點(diǎn)中包含了全部關(guān)鍵字的信息，及指向含這

些關(guān)鍵字記錄的指針，且葉子結(jié)點(diǎn)本身依關(guān)鍵字的大小自小而

大順序鏈接。

3.）所有的非終端結(jié)點(diǎn)可以看成是索引部分，結(jié)點(diǎn)中僅含其子

樹(shù)中的最大（或最小）關(guān)鍵字。

4.）B+樹(shù)中，數(shù)據(jù)對(duì)象的插入和刪除僅在葉節(jié)點(diǎn)上進(jìn)行。

5.）B+樹(shù)有2個(gè)頭指針，一個(gè)是樹(shù)的根節(jié)點(diǎn)，一個(gè)是最小關(guān)

鍵碼的葉節(jié)點(diǎn)。

2）哈希索引

簡(jiǎn)要說(shuō)下，類似于數(shù)據(jù)結(jié)構(gòu)中簡(jiǎn)單實(shí)現(xiàn)的HASH表（散列

表）一樣，當(dāng)我們?cè)趍ysql中用哈希索引時(shí)，主要就是通過(guò)

Hash算法（常見(jiàn)的Hash算法有直接定址法、平方取中法、

折疊法、除數(shù)取余法、隨機(jī)數(shù)法），將數(shù)據(jù)庫(kù)字段數(shù)據(jù)轉(zhuǎn)換成

定長(zhǎng)的Hash值，與這條數(shù)據(jù)的行指針一并存入Hash表的對(duì)

應(yīng)位置；如果發(fā)生Hash碰撞（兩個(gè)不同關(guān)鍵字的Hash值相

同），則在對(duì)應(yīng)Hash鍵下以鏈表形式存儲(chǔ)。當(dāng)然這只是簡(jiǎn)略

模擬圖。

哈希索弓I

6.索引的基本原理

索引用來(lái)快速地尋找那些具有特定值的記錄。如果沒(méi)有索引，

一般來(lái)說(shuō)執(zhí)行查詢時(shí)遍歷整張表。

索引的原理很簡(jiǎn)單，就是把無(wú)序的數(shù)據(jù)變成有序的查詢

1.把創(chuàng)建了索引的列的內(nèi)容進(jìn)行排序

2.對(duì)排序結(jié)果生成倒排表

3.在倒排表內(nèi)容上拼上數(shù)據(jù)地址鏈

4.在查詢的時(shí)候，先拿到倒排表內(nèi)容，再取出數(shù)據(jù)地址鏈，從而

拿到具體數(shù)據(jù)

7.索引算法有哪些？

索引算法有BTree算法和Hash算法

BTree算法

BTree是最常用的mysql數(shù)據(jù)庫(kù)索引算法，也是mysql默認(rèn)

的算法。因?yàn)樗粌H可以被用在=,>,>=,<,<=和between這

些比較操作符上，而且還可以用于like操作符，只要它的查

詢條件是一個(gè)不以通配符開(kāi)頭的常量，例如：

一只要它的查詢條件是一個(gè)不以通配符開(kāi)頭的常量

select*fromuserwherenamelike'jack%';

一如果一通配符開(kāi)頭，或者沒(méi)有使用常量，則不會(huì)使用索引，

例如：

select*fromuserwherenamelike'%jack';

Hash算法

HashHash索引只能用于對(duì)等比較，例如=,<=>（相當(dāng)于

=）操作符。由于是一次定位數(shù)據(jù)，不像BTree索引需要從根

節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次10訪問(wèn)，所

以檢索效率遠(yuǎn)高于BTree索引。

8.索引設(shè)計(jì)的原則？

1.適合索引的列是出現(xiàn)在where子句中的列，或者連接子句中

指定的列

2.基數(shù)較小的類，索引效果較差，沒(méi)有必要在此列建立索引

3.使用短索引，如果對(duì)長(zhǎng)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴

長(zhǎng)度，這樣能夠節(jié)省大量索引空間

4.不要過(guò)度索引。索引需要額外的磁盤(pán)空間，并降低寫(xiě)操作的性

能。在修改表內(nèi)容的時(shí)候，索引會(huì)進(jìn)行更新甚至重構(gòu)，索引列

越多，這個(gè)時(shí)間就會(huì)越長(zhǎng)。所以只保持需要的索引有利于查詢

即可。

9.創(chuàng)建索引的原則（重中之重）

索引雖好，但也不是無(wú)限制的使用，最好符合一下幾個(gè)原則

1）最左前綴匹配原則，組合索引非常重要的原則，mysql會(huì)

一直向右匹配直到遇到范圍查詢（＞、＜、between、like）就

停止匹配，比如a=landb=2andc＞3andd=4如果建立

（a,b,c,d）順序的索引，d是用不到索引的,如果建立（a,b,d,c）

的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

2）較頻繁作為查詢條件的字段才去創(chuàng)建索引

3）更新頻繁字段不適合創(chuàng)建索引

4）若是不能有效區(qū)分?jǐn)?shù)據(jù)的列不適合做索弓I列（如性別，男女

未知，最多也就三種，區(qū)分度實(shí)在太低）

5）盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索

弓I,現(xiàn)在要加（a,b）的索引，那么只需要修改原來(lái)的索引即

可。

6）定義有外鍵的數(shù)據(jù)列一定要建立索引。

7）對(duì)于那些查詢中很少涉及的列，重復(fù)值比較多的列不要建

立索引。

8)對(duì)于定義為text,image和bit的數(shù)據(jù)類型的列不要建立

索引。

10.創(chuàng)建索引的三種方式，刪除索引

第一種方式：在執(zhí)行CREATETABLE時(shí)創(chuàng)建索引

CREATETABLEuser_index2(

idINTauto_incrementPRIMARYKEY,

first_nameVARCHAR(16),

last_nameVARCHAR(16),

id_cardVARCHAR(18),

informationtext,

KEYname(first_name,last_name),

FULLTEXTKEY(information),

UNIQUEKEY(id_card)

)；

第二種方式：使用ALTERTABLE命令去增加索引

ALTERTABLEtable_nameADDINDEXindex_name(column

Jist);

?1

ALTERTABLE用來(lái)創(chuàng)建普通索引、UNIQUE索引或

PRIMARYKEY索引。

其中table_name是要增加索引的表名，column」ist指出對(duì)

哪些列進(jìn)行索引，多列時(shí)各列之間用逗號(hào)分隔。

索引名可自己命名，缺省時(shí)，MySQL將根據(jù)第

一個(gè)索引列賦一個(gè)名稱。另外，ALTERTABLE允許在單個(gè)語(yǔ)

句中更改多個(gè)表，因此可以在同時(shí)創(chuàng)建多個(gè)索引。

第三種方式：使用CREATEINDEX命令創(chuàng)建

CREATEINDEXindex_nameONtable_name（column_list）;

CREATEINDEX可對(duì)表增加普通索引或UNIQUE索引。（但

是，不能創(chuàng)建PRIMARYKEY索引）

刪除索引

根據(jù)索引名刪除普通索引、唯一索引、全文索引：altertable

表名dropKEY索引名

altertableuser_indexdropKEYname;

altertableuser_indexdropKEYid_card;

altertableuser_indexdropKEYinformation;

刪除主鍵索引：altertable表名dropprimarykey（因?yàn)橹麈I

只有一個(gè)）。這里值得注意的是，如果主鍵自增長(zhǎng)，那么不能

直接執(zhí)行此操作（自增長(zhǎng)依賴于主鍵索引）：

altertableuser__indexdropprimarykey

信息概況狀態(tài)

[SQL]aItertableuserjndexdropprimarykey

[Err]1075-Incorrecttabledefinition;therecanbeonlyoneautocolumnanditmustbedefinedasa

需要取消自增長(zhǎng)再行刪除：

altertableuser_index

--重新定義字段

MODIFYidint,

dropPRIMARYKEY

但通常不會(huì)刪除主鍵，因?yàn)樵O(shè)計(jì)主鍵一定與業(yè)務(wù)邏輯無(wú)關(guān)。

11.創(chuàng)建索引時(shí)需要注意什么？

?非空字段：應(yīng)該指定列為NOTNULL,除非你想存儲(chǔ)NULLO

在mysql中，含有空值的列很難進(jìn)行查詢優(yōu)化，因?yàn)樗鼈兪?/p>

得索引、索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用

0、一個(gè)特殊的值或者一個(gè)空串代替空值；

?取值離散大的字段：（變量各個(gè)取值之間的差異程度）的列放

到聯(lián)合索引的前面，可以通過(guò)count。函數(shù)查看字段的差異

值，返回值越大說(shuō)明字段的唯一值越多字段的離散程度高；

?索引字段越小越好：數(shù)據(jù)庫(kù)的數(shù)據(jù)存儲(chǔ)以頁(yè)為單位一頁(yè)存儲(chǔ)的

數(shù)據(jù)越多一次I。操作獲取的數(shù)據(jù)越大效率越高。

12.使用索引查詢一定能提高查詢的性能嗎？為什么

通常，通過(guò)索引查詢數(shù)據(jù)比全表掃描要快。但是我們也必須注

意到它的代價(jià)。

?索引需要空間來(lái)存儲(chǔ)，也需要定期維護(hù)，每當(dāng)有記錄在表中增

減或索引列被修改時(shí)，索引本身也會(huì)被修改。這意味著每條記

錄的INSERT,DELETE,UPDATE將為此多付出4,5次的

磁盤(pán)I/O。因?yàn)樗饕枰~外的存儲(chǔ)空間和處理，那些不必要

的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢。使用索引查詢不一定能提

高查詢性能，索引范圍查詢（INDEXRANGESCAN）適用于兩

種情況:

?基于一個(gè)范圍的檢索，一般查詢返回結(jié)果集小于表中記錄數(shù)的

30%

.基于非唯一性索引的檢索

13.百萬(wàn)級(jí)別或以上的數(shù)據(jù)如何刪除

關(guān)于索引：由于索引需要額外的維護(hù)成本，因?yàn)樗饕募菃?/p>

獨(dú)存在的文件，所以當(dāng)我們對(duì)數(shù)據(jù)的增加，修改，刪除，都會(huì)產(chǎn)生額

外的對(duì)索引文件的操作，這些操作需要消耗額外的10,會(huì)降低增

/改/刪的執(zhí)行效率。所以，在我們刪除數(shù)據(jù)庫(kù)百萬(wàn)級(jí)別數(shù)據(jù)的

時(shí)候，查詢MySQL官方手冊(cè)得知?jiǎng)h除數(shù)據(jù)的速度和創(chuàng)建的索

引數(shù)量是成正比的。

1.所以我們想要?jiǎng)h除百萬(wàn)數(shù)據(jù)的時(shí)候可以先刪除索引（此時(shí)大概

耗時(shí)三分多鐘）

2.然后刪除其中無(wú)用數(shù)據(jù)（此過(guò)程需要不到兩分鐘）

3.刪除完成后重新創(chuàng)建索引(此時(shí)數(shù)據(jù)較少了)創(chuàng)建索引也非常

快，約十分鐘左右。

4.與之前的直接刪除絕對(duì)是要快速很多，更別說(shuō)萬(wàn)一刪除中斷,

一切刪除會(huì)回滾。那更是坑了。

14.前綴索引

語(yǔ)法:index(field(10))，使用字段值的前10個(gè)字符建立索

引，默認(rèn)是使用字段的全部?jī)?nèi)容建立索引。

前提：前綴的標(biāo)識(shí)度高。比如密碼就適合建立前綴索引，因?yàn)?/p>

密碼幾乎各不相同。

實(shí)操的難度：在于前綴截取的長(zhǎng)度。

我們可以利用

selectcount(*)/count(distinetleft(password,prefixLen));

,通過(guò)從調(diào)整prefixLen的值(從1自增)查看不同前綴長(zhǎng)

度的一個(gè)平均匹配度，接近1時(shí)就可以了(表示一個(gè)密碼的

前prefixLen個(gè)字符幾乎能確定唯一一條記錄)

15.什么是最左前綴原則？什么是最左匹配原則

?顧名思義，就是最左優(yōu)先，在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需

求，where子句中使用最頻繁的一列放在最左邊。

?最左前綴匹配原則，非常重要的原則，mysql會(huì)一直向右匹

配直到遇到范圍查詢(＞、＜、between、like)就停止匹配,

比如a=landb=2andc＞3andd=4如果建立(a,b,c,d)順序的

索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以

用到，a,b,d的順序可以任意調(diào)整。

?二和in可以亂序，比如a=landb=2andc=3建立(a,b,c)索引

可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以

識(shí)別的形式

16.B樹(shù)和B+樹(shù)的區(qū)別

?在B樹(shù)中，你可以將鍵和值存放在內(nèi)部節(jié)點(diǎn)和葉子節(jié)點(diǎn)；但

在B+樹(shù)中，內(nèi)部節(jié)點(diǎn)都是鍵，沒(méi)有值，葉子節(jié)點(diǎn)同時(shí)存放鍵

和值。

?B+樹(shù)的葉子節(jié)點(diǎn)有一條鏈相連，而B(niǎo)樹(shù)的葉子節(jié)點(diǎn)各自獨(dú)

立。

17.使用B樹(shù)的好處

B樹(shù)可以在內(nèi)部節(jié)點(diǎn)同時(shí)存儲(chǔ)鍵和值，因此，把頻繁訪問(wèn)的數(shù)

據(jù)放在靠近根節(jié)點(diǎn)的地方將會(huì)大大提高熱點(diǎn)數(shù)據(jù)的查詢效率。

這種特性使得B樹(shù)在特定數(shù)據(jù)重復(fù)多次查詢的場(chǎng)景中更加高

效。

18.使用B+樹(shù)的好處

由于B+樹(shù)的內(nèi)部節(jié)點(diǎn)只存放鍵，不存放值，因此，一次讀

取，可以在內(nèi)存頁(yè)中獲取更多的鍵，有利于更快地縮小查找范

圍。B+樹(shù)的葉節(jié)點(diǎn)由一條鏈相連，因此，當(dāng)需要進(jìn)行一次全

數(shù)據(jù)遍歷的時(shí)候，B+樹(shù)只需要使用O(logN)時(shí)間找到最小的

一個(gè)節(jié)點(diǎn)，然后通過(guò)鏈進(jìn)行0(N)的順序遍歷即可。而B(niǎo)樹(shù)則

需要對(duì)樹(shù)的每一層進(jìn)行遍歷，這會(huì)需要更多的內(nèi)存置換次數(shù)，

因此也就需要花費(fèi)更多的時(shí)間

19.Hash索引和B+樹(shù)所有有什么區(qū)別或者說(shuō)優(yōu)劣呢？

首先要知道Hash索引和B+樹(shù)索引的底層實(shí)現(xiàn)原理：

hash索引底層就是hash表，進(jìn)行查找時(shí)，調(diào)用一次hash

函數(shù)就可以獲取到相應(yīng)的鍵值，之后進(jìn)行回表查詢獲得實(shí)際數(shù)

據(jù)。B+樹(shù)底層實(shí)現(xiàn)是多路平衡查找樹(shù)。對(duì)于每一次的查詢都

是從根節(jié)點(diǎn)出發(fā)，查找到葉子節(jié)點(diǎn)方可以獲得所查鍵值，然后

根據(jù)查詢判斷是否需要回表查詢數(shù)據(jù)。

那么可以看出他們有以下的不同：

?hash索引進(jìn)行等值查詢更快(一般情況下)，但是卻無(wú)法進(jìn)行

范圍查詢。

因?yàn)樵趆ash索引中經(jīng)過(guò)hash函數(shù)建立索引之后，索引的順

序與原順序無(wú)法保持一致，不能支持范圍查詢。而B(niǎo)+樹(shù)的的

所有節(jié)點(diǎn)皆遵循(左節(jié)點(diǎn)小于父節(jié)點(diǎn)，右節(jié)點(diǎn)大于父節(jié)點(diǎn)，多

叉樹(shù)也類似)，天然支持范圍。

?hash索引不支持使用索引進(jìn)行排序，原理同上。

?hash索引不支持模糊查詢以及多列索引的最左前綴匹配。原

理也是因?yàn)閔ash函數(shù)的不可預(yù)測(cè)。AAAA和AAAAB的索引

沒(méi)有相關(guān)性。

?hash索引任何時(shí)候都避免不了回表查詢數(shù)據(jù)，而B(niǎo)+樹(shù)在符

合某些條件（聚簇索引，覆蓋索引等）的時(shí)候可以只通過(guò)索引完

成查詢。

?hash索引雖然在等值查詢上較快，但是不穩(wěn)定。性能不可預(yù)

測(cè)，當(dāng)某個(gè)鍵值存在大量重復(fù)的時(shí)候，發(fā)生hash碰撞，此時(shí)

效率可能極差。而B(niǎo)+樹(shù)的查詢效率比較穩(wěn)定，對(duì)于所有的查

詢都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)，且樹(shù)的高度較低。

因此，在大多數(shù)情況下，直接選擇B+樹(shù)索引可以獲得穩(wěn)定且

較好的查詢速度。而不需要使用hash索引。

20.數(shù)據(jù)庫(kù)為什么使用B+樹(shù)而不是B樹(shù)

?B樹(shù)只適合隨機(jī)檢索，而B(niǎo)+樹(shù)同時(shí)支持隨機(jī)檢索和順序檢

索；

?B+樹(shù)空間利用率更高，可減少I/O次數(shù)，磁盤(pán)讀寫(xiě)代價(jià)更

低。一般來(lái)說(shuō)，索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，

因此索引往往以索引文件的形式存儲(chǔ)的磁盤(pán)上。這樣的話，索

引查找過(guò)程中就要產(chǎn)生磁盤(pán)I/O消耗。B+樹(shù)的內(nèi)部結(jié)點(diǎn)并沒(méi)

有指向關(guān)鍵字具體信息的指針，只是作為索引使用，其內(nèi)部結(jié)

點(diǎn)比B樹(shù)小，盤(pán)塊能容納的結(jié)點(diǎn)中關(guān)鍵字?jǐn)?shù)量更多，一次性

讀入內(nèi)存中可以查找的關(guān)鍵字也就越多，相對(duì)的，10讀寫(xiě)次

數(shù)也就降低了。而10讀寫(xiě)次數(shù)是影響索引檢索效率的最大因

素；

?B+樹(shù)的查詢效率更加穩(wěn)定。B樹(shù)搜索有可能會(huì)在非葉子結(jié)點(diǎn)

結(jié)束，越靠近根節(jié)點(diǎn)的記錄查找時(shí)間越短，只要找到關(guān)鍵字即

可確定記錄的存在，其性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分

查找。而在B+樹(shù)中，順序檢索比較明顯，隨機(jī)檢索時(shí)，任何

關(guān)鍵字的查找都必須走一條從根節(jié)點(diǎn)到葉節(jié)點(diǎn)的路，所有關(guān)鍵

字的查找路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)關(guān)鍵字的查詢效率相當(dāng)。

?B-樹(shù)在提高了磁盤(pán)10性能的同時(shí)并沒(méi)有解決元素遍歷的效率

低下的問(wèn)題。B+樹(shù)的葉子節(jié)點(diǎn)使用指針順序連接在一起，只

要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹(shù)的遍歷。而且在數(shù)據(jù)庫(kù)中基

于范圍的查詢是非常頻繁的，而B(niǎo)樹(shù)不支持這樣的操作。

?增刪文件（節(jié)點(diǎn)）時(shí)，效率更高。因?yàn)锽+樹(shù)的葉子節(jié)點(diǎn)包含

所有關(guān)鍵字，并以有序的鏈表結(jié)構(gòu)存儲(chǔ)，這樣可很好提高增刪

效率。

21.B+樹(shù)在滿足聚簇索引和覆蓋索引的時(shí)候不需要回表查詢數(shù)

據(jù)

在B+樹(shù)的索引中，葉子節(jié)點(diǎn)可能存儲(chǔ)了當(dāng)前的key值，也可

能存儲(chǔ)了當(dāng)前的key值以及整行的數(shù)據(jù)，這就是聚簇索引和

非聚簇索引。在InnoDB中，只有主鍵索引是聚簇索引，如

果沒(méi)有主鍵，則挑選一個(gè)唯一鍵建立聚簇索引。如果沒(méi)有唯一

鍵，則隱式的生成一個(gè)鍵來(lái)建立聚簇索引。

當(dāng)查詢使用聚簇索引時(shí)，在對(duì)應(yīng)的葉子節(jié)點(diǎn)，可以獲取到整行

數(shù)據(jù)，因此不用再次進(jìn)行回表查詢。

22.什么是聚簇索引？何時(shí)使用聚簇索引與非聚簇索引

?聚簇索引：將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，找到索引也就找到

了數(shù)據(jù)

?非聚簇索引：將數(shù)據(jù)存儲(chǔ)于索引分開(kāi)結(jié)構(gòu)，索引結(jié)構(gòu)的葉子節(jié)

點(diǎn)指向了數(shù)據(jù)的對(duì)應(yīng)行，myisam通過(guò)key_buffer把索引先

緩存到內(nèi)存中，當(dāng)需要訪問(wèn)數(shù)據(jù)時(shí)（通過(guò)索引訪問(wèn)數(shù)據(jù)），在

內(nèi)存中直接搜索索引，然后通過(guò)索引找到磁盤(pán)相應(yīng)數(shù)據(jù)，這也

就是為什么索引不在keybuffer命中時(shí)，速度慢的原因

澄清一個(gè)概念：innodb中，在聚簇索引之上創(chuàng)建的索引稱之

為輔助索引，輔助索引訪問(wèn)數(shù)據(jù)總是需要二次查找，非聚簇索

引都是輔助索引，像復(fù)合索引、前綴索引、唯一索引，輔助索

引葉子節(jié)點(diǎn)存儲(chǔ)的不再是行的物理位置，而是主鍵值

何時(shí)使用聚簇索引與非聚簇索引

動(dòng)作使用聚簇索引使用非聚簇索引

列經(jīng)常被分組排序應(yīng)應(yīng)

返回某范圍內(nèi)的數(shù)據(jù)應(yīng)不應(yīng)

一個(gè)蝴少不同值不應(yīng)不應(yīng)

小數(shù)目的不同值應(yīng)

大數(shù)目的不同值不應(yīng)應(yīng)

頻繁更新的列不應(yīng)應(yīng)

外鍵列應(yīng)應(yīng)

主鍵列應(yīng)應(yīng)

頻繁修改索引列不應(yīng)應(yīng)

23.非聚簇索引一定會(huì)回表查詢嗎？

不一定，這涉及到查詢語(yǔ)句所要求的字段是否全部命中了索

引，如果全部命中了索引，那么就不必再進(jìn)行回表查詢。

舉個(gè)簡(jiǎn)單的例子，假設(shè)我們?cè)趩T工表的年齡上建立了索引，那

么當(dāng)進(jìn)行selectagefromemployeewhereage<20的查詢

時(shí)，在索引的葉子節(jié)點(diǎn)上，已經(jīng)包含了age信息，不會(huì)再次

進(jìn)行回表查詢。

24.聯(lián)合索引是什么？為什么需要注意聯(lián)合索引中的順序？

MySQL可以使用多個(gè)字段同時(shí)建立一個(gè)索引，叫做聯(lián)合索

引。在聯(lián)合索引中，如果想要命中索引，需要按照建立索引時(shí)

的字段順序挨個(gè)使用，否則無(wú)法命中索引。

具體原因?yàn)椋?/p>

MySQL使用索引時(shí)需要索引有序，假設(shè)現(xiàn)在建立了"name,

age,school"的聯(lián)合索弓|,那么索引的排序?yàn)?先按照name

排序，如果name相同，則按照age排序，如果age的值也

相等，則按照school進(jìn)行排序。

當(dāng)進(jìn)行查詢時(shí)，此時(shí)索引僅僅按照name嚴(yán)格有序，因此必

須首先使用name字段進(jìn)行等值查詢，之后對(duì)于匹配到的列

而言，其按照age字段嚴(yán)格有序，此時(shí)可以使用age字段用

做索引查找，以此類推。因此在建立聯(lián)合索引的時(shí)候應(yīng)該注意

索引列的順序，一般情況下，將查詢需求頻繁或者字段選擇性

高的列放在前面。此外可以根據(jù)特例的查詢或者表結(jié)構(gòu)進(jìn)行單

獨(dú)的調(diào)整。

事務(wù)

1.什么是數(shù)據(jù)庫(kù)事務(wù)？

事務(wù)是一個(gè)不可分割的數(shù)據(jù)庫(kù)操作序列，也是數(shù)據(jù)庫(kù)并發(fā)控制

的基本單位，其執(zhí)行的結(jié)果必須使數(shù)據(jù)庫(kù)從一種一致性狀態(tài)變

到另一種一致性狀態(tài)。事務(wù)是邏輯上的一組操作，要么都執(zhí)

行，要么都不執(zhí)行。

事務(wù)最經(jīng)典也經(jīng)常被拿出來(lái)說(shuō)例子就是轉(zhuǎn)賬了。

假如小明要給小紅轉(zhuǎn)賬1000元，這個(gè)轉(zhuǎn)賬會(huì)涉及到兩個(gè)關(guān)鍵

操作就是：將小明的余額減少1000元，將小紅的余額增加

1000元。萬(wàn)一在這兩個(gè)操作之間突然出現(xiàn)錯(cuò)誤比如銀行系統(tǒng)

崩潰，導(dǎo)致小明余額減少而小紅的余額沒(méi)有增加，這樣就不對(duì)

To事務(wù)就是保證這兩個(gè)關(guān)鍵操作要么都成功，要么都要失

敗。

2.事物的四大特性(ACID)介紹一下？

關(guān)系性數(shù)據(jù)庫(kù)需要遵循AQD規(guī)則，具體內(nèi)容如下:

1.原子性：事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)的原子性

確保動(dòng)作要么全部完成，要么完全不起作用；

2.一致性：執(zhí)行事務(wù)前后，數(shù)據(jù)保持一致，多個(gè)事務(wù)對(duì)同一個(gè)數(shù)

據(jù)讀取的結(jié)果是相同的；

3.隔離性：并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，一個(gè)用戶的事務(wù)不被其他事務(wù)所

干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫(kù)是獨(dú)立的；

4.持久性：一個(gè)事務(wù)被提交之后。它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是持

久的，即使數(shù)據(jù)庫(kù)發(fā)生故障也不應(yīng)該對(duì)其有任何影響。

3.什么是臟讀？幻讀？不可重復(fù)讀？

?臟讀(DrityRead):某個(gè)事務(wù)已更新一份數(shù)據(jù)，另一個(gè)事務(wù)在

此時(shí)讀取了同一份數(shù)據(jù)，由于某些原因，前一個(gè)RollBack了

操作，則后一個(gè)事務(wù)所讀取的數(shù)據(jù)就會(huì)是不正確的。

?不可重復(fù)讀(Non-repeatableread):在一個(gè)事務(wù)的兩次查詢之

中數(shù)據(jù)不一致，這可能是兩次查詢過(guò)程中間插入了一個(gè)事務(wù)更

新的原有的數(shù)據(jù)。

?幻讀(PhantomRead):在一個(gè)事務(wù)的兩次查詢中數(shù)據(jù)筆數(shù)不一

致，例如有一個(gè)事務(wù)查詢了幾列(Row)數(shù)據(jù)，而另一個(gè)事務(wù)卻

在此時(shí)插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來(lái)的查詢中，

就會(huì)發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒(méi)有的。

4.什么是事務(wù)的隔離級(jí)別？MySQL的默認(rèn)隔離級(jí)別是什么？

為了達(dá)到事務(wù)的四大特性，數(shù)據(jù)庫(kù)定義了4種不同的事務(wù)隔

離級(jí)別,由低到高依次為

Readuncommitteds

Readcommitted、Repeatableread、Serializable,這四個(gè)

級(jí)別可以逐個(gè)解決臟讀、不可重復(fù)讀、幻讀這幾類問(wèn)題。

隔離級(jí)別臟讀不可重復(fù)讀幻影讀

READ-UNCOMMITTEDVVV

READ-COMMITTEDXVV

REPEATABLE-READXXV

SERIALIZABLEXXX

SQL標(biāo)準(zhǔn)定義了四個(gè)隔離級(jí)別：

?READ-UNCOMMITTED（讀取未提交）：最低的隔離級(jí)別，允

許讀取尚未提交的數(shù)據(jù)變更，可能會(huì)導(dǎo)致臟讀、幻讀或不可重

復(fù)讀。

?READ-COMMITTED（讀取已提交）：允許讀取并發(fā)事務(wù)已經(jīng)

提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能

發(fā)生。

?REPEATABLE-READ（可重復(fù)讀）：對(duì)同一字段的多次讀取結(jié)果

都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟

讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。

?SERIALIZABLE（可串行化）：最高的隔離級(jí)別，完全服從

ACID的隔離級(jí)別。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間

就完全不可能產(chǎn)生干擾，也就是說(shuō)，該級(jí)別可以防止臟讀、不

可重復(fù)讀以及幻讀。

這里需要注意的是：Mysql默認(rèn)采用的REPEATABLE_READ

隔離級(jí)別Oracle默認(rèn)采用的READ_COMMITTED隔離級(jí)別

事務(wù)隔離機(jī)制的實(shí)現(xiàn)基于鎖機(jī)制和并發(fā)調(diào)度。其中并發(fā)調(diào)度使

用的是MVVC（多版本并發(fā)控制），通過(guò)保存修改的舊版本

信息來(lái)支持并發(fā)一致性讀和回滾等特性。

因?yàn)楦綦x級(jí)別越低，事務(wù)請(qǐng)求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫(kù)系

統(tǒng)的隔離級(jí)別都是READ-COMMITTED（讀取提交內(nèi)容）：，但

是你要知道的是InnoDB存儲(chǔ)引擎默認(rèn)使用**REPEATABLE-

READ（可重讀）**并不會(huì)有任何性能損失。

Inn。DB存儲(chǔ)引擎在分布式事務(wù)的情況下一般會(huì)用到

**SERIALIZABLE（可串行化）*邛鬲離級(jí)別。

鎖

1.對(duì)MySQL的鎖了解嗎

當(dāng)數(shù)據(jù)庫(kù)有并發(fā)事務(wù)的時(shí)候，可能會(huì)產(chǎn)生數(shù)據(jù)的不一致，這時(shí)

候需要一些機(jī)制來(lái)保證訪問(wèn)的次序，鎖機(jī)制就是這樣的一個(gè)機(jī)

制。

就像酒店的房間，如果大家隨意進(jìn)出，就會(huì)出現(xiàn)多人搶奪同一

個(gè)房間的情況，而在房間上裝上鎖，申請(qǐng)到鑰匙的人才可以入

住并且將房間鎖起來(lái)，其他人只有等他使用完畢才可以再次使

用。

2.隔離級(jí)別與鎖的關(guān)系

在ReadUncommitted級(jí)別下，讀取數(shù)據(jù)不需要加共享鎖，

這樣就不會(huì)跟被修改的數(shù)據(jù)上的排他鎖沖突

在ReadCommitted級(jí)別下，讀操作需要加共享鎖，但是在

語(yǔ)句執(zhí)行完以后釋放共享鎖；

在RepeatableRead級(jí)別下，讀操作需要加共享鎖，但是在

事務(wù)提交之前并不釋放共享鎖，也就是必須等待事務(wù)執(zhí)行完畢

以后才釋放共享鎖。

SERIALIZABLE是限制性最強(qiáng)的隔離級(jí)別，因?yàn)樵摷?jí)別鎖定整

個(gè)范圍的鍵，并一直持有鎖，直到事務(wù)完成。

3.按照鎖的粒度分?jǐn)?shù)據(jù)庫(kù)鎖有哪些？鎖機(jī)制與InnoDB鎖算法

在關(guān)系型數(shù)據(jù)庫(kù)中，可以按照鎖的粒度把數(shù)據(jù)庫(kù)鎖分為行級(jí)鎖

（INNODB引擎）、表級(jí)鎖（MYISAM引擎）和頁(yè)級(jí)鎖（BDB引

擎）。

MylSAM和InnoDB存儲(chǔ)引擎使用的鎖：

?MylSAM采用表級(jí)鎖（table-levellocking）。

?InnoDB支持行級(jí)鎖（row-levellocking）和表級(jí)鎖，默認(rèn)為行

級(jí)鎖

行級(jí)鎖，表級(jí)鎖和頁(yè)級(jí)鎖對(duì)比

行級(jí)鎖行級(jí)鎖是Mysql中鎖定粒度最細(xì)的一種鎖，表示只針

對(duì)當(dāng)前操作的行進(jìn)行加鎖。行級(jí)鎖能大大減少數(shù)據(jù)庫(kù)操作的沖

突。其加鎖粒度最小，但加鎖的開(kāi)銷也最大。行級(jí)鎖分為共享

鎖和排他鎖。

特點(diǎn)：開(kāi)銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖

沖突的概率最低，并發(fā)度也最高。

表級(jí)鎖表級(jí)鎖是MySQL中鎖定粒度最大的一種鎖，表示對(duì)當(dāng)

前操作的整張表加鎖，它實(shí)現(xiàn)簡(jiǎn)單，資源消耗較少，被大部分

MySQL引擎支持。最常使用的MYISAM與INNODB都支持

表級(jí)鎖定。表級(jí)鎖定分為表共享讀鎖（共享鎖）與表獨(dú)占寫(xiě)鎖

（排他鎖）。

特點(diǎn)：開(kāi)銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)出鎖

沖突的概率最高，并發(fā)度最低。

頁(yè)級(jí)鎖頁(yè)級(jí)鎖是MySQL中鎖定粒度介于行級(jí)鎖和表級(jí)鎖中間

的一種鎖。表級(jí)鎖速度快，但沖突多，行級(jí)沖突少，但速度

慢。所以取了折衷的頁(yè)級(jí)，一次鎖定相鄰的一組記錄。

特點(diǎn)：開(kāi)銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖

定粒度界于表鎖和行鎖之間，并發(fā)度一般

4.從鎖的類別上分MySQL都有哪些鎖呢？像上面那樣子進(jìn)行

鎖定豈不是有點(diǎn)阻礙并發(fā)效率了

從鎖的類別上來(lái)講，有共享鎖和排他鎖。

共享鎖:又叫做讀鎖。當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí)，對(duì)數(shù)據(jù)加

上共享鎖。共享鎖可以同時(shí)加上多個(gè)。

排他鎖:又叫做寫(xiě)鎖。當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫(xiě)入時(shí)，對(duì)數(shù)據(jù)加

上排他鎖。排他鎖只可以加一個(gè)，他和其他的排他鎖，共享鎖

都相斥。

用上面的例子來(lái)說(shuō)就是用戶的行為有兩種，一種是來(lái)看房，多

個(gè)用戶一起看房是可以接受的。一種是真正的入住一晚，在這

期間，無(wú)論是想入住的還是想看房的都不可以。

鎖的粒度取決于具體的存儲(chǔ)引擎，InnoDB實(shí)現(xiàn)了行級(jí)鎖，頁(yè)

級(jí)鎖，表級(jí)鎖。

他們的加鎖開(kāi)銷從大到小，并發(fā)能力也是從大到小。

5.MySQL中InnoDB引擎的行鎖是怎么實(shí)現(xiàn)的？

答:InnoDB是基于索引來(lái)完成行鎖

{^:select*fromtab_with_indexwhereid=lforupclate;

forupdate可以根據(jù)條件來(lái)完成行鎖鎖定，并且id是有索引

鍵的列，如果id不是索引鍵那么InnoDB將完成表鎖，并發(fā)

將無(wú)從談起

6.InnoDB存儲(chǔ)引擎的鎖的算法有三種

?Recordlock:單個(gè)行記錄上的鎖

?Gaplock:間隙鎖，鎖定一個(gè)范圍，不包括記錄本身

?Next-keylock:record+gap鎖定一個(gè)范圍，包含記錄本身

相關(guān)知識(shí)點(diǎn)：

l.innodb對(duì)于行的查詢使用next-keylock

2.Next-lockingkeying為了解決PhantomProblem幻讀問(wèn)題

3.當(dāng)查詢的索引含有唯一屬性時(shí)，將next-keylock降級(jí)為

recordkey

4.Gap鎖設(shè)計(jì)的目的是為了阻止多個(gè)事務(wù)將記錄插入到同一范

圍內(nèi)，而這會(huì)導(dǎo)致幻讀問(wèn)題的產(chǎn)生

5.有兩種方式顯式關(guān)閉gap鎖：（除了外鍵約束和唯一性檢查

外，其余情況僅使用recordlock）A.將事務(wù)隔離級(jí)別設(shè)置為

RCB.將參數(shù)innodb_locks_unsafe_for_binlog設(shè)置為1

7.什么是死鎖？怎么解決？

死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用，并請(qǐng)求鎖定

對(duì)方的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。

常見(jiàn)的解決死鎖的方法

1、如果不同程序會(huì)并發(fā)存取多個(gè)表，盡量約定以相同的順序

訪問(wèn)表，可以大大降低死鎖機(jī)會(huì)。

2、在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資

源，減少死鎖產(chǎn)生概率；

3、對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級(jí)鎖

定顆粒度，通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率；

如果業(yè)務(wù)處理不好可以用分布式事務(wù)鎖或者使用樂(lè)觀鎖

8.數(shù)據(jù)庫(kù)的樂(lè)觀鎖和悲觀鎖是什么？怎么實(shí)現(xiàn)的？

數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）中的并發(fā)控制的任務(wù)是確保在多個(gè)

事務(wù)同時(shí)存取數(shù)據(jù)庫(kù)中同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性和統(tǒng)一

性以及數(shù)據(jù)庫(kù)的統(tǒng)一性。樂(lè)觀并發(fā)控制（樂(lè)觀鎖）和悲觀并發(fā)

控制（悲觀鎖）是并發(fā)控制主要采用的技術(shù)手段。

悲觀鎖：假定會(huì)發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性

的操作。在查詢完數(shù)據(jù)的時(shí)候就把事務(wù)鎖起來(lái)，直到提交事

務(wù)。實(shí)現(xiàn)方式：使用數(shù)據(jù)庫(kù)中的鎖機(jī)制

樂(lè)觀鎖：假設(shè)不會(huì)發(fā)生并發(fā)沖突，只在提交操作時(shí)檢查是否違

反數(shù)據(jù)完整性。在修改數(shù)據(jù)的時(shí)候把事務(wù)鎖起來(lái)，通過(guò)

version的方式來(lái)進(jìn)行鎖定。實(shí)現(xiàn)方式：樂(lè)一般會(huì)使用版本號(hào)

機(jī)制或CAS算法實(shí)現(xiàn)。

兩種鎖的使用場(chǎng)景

從上面對(duì)兩種鎖的介紹，我們知道兩種鎖各有優(yōu)缺點(diǎn)，不可認(rèn)

為一種好于另一種，像樂(lè)觀鎖適用于寫(xiě)比較少的情況下（多讀

場(chǎng)景），即沖突真的很少發(fā)生的時(shí)候，這樣可以省去了鎖的開(kāi)

銷，加大了系統(tǒng)的整個(gè)吞吐量。

但如果是多寫(xiě)的情況，一般會(huì)經(jīng)常產(chǎn)生沖突，這就會(huì)導(dǎo)致上層

應(yīng)用會(huì)不斷的進(jìn)行retry,這樣反倒是降低了性能，所以一般

多寫(xiě)的場(chǎng)景下用悲觀鎖就比較合適。

視圖

1.為什么要使用視圖？什么是視圖？

為了提高復(fù)雜SQL語(yǔ)句的復(fù)用性和表操作的安全性，MySQL

數(shù)據(jù)庫(kù)管理系統(tǒng)提供了視圖特性。所謂視圖，本質(zhì)上是一種虛

擬表，在物理上是不存在的，其內(nèi)容與真實(shí)的表相似，包含一

系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫(kù)中以儲(chǔ)

存的數(shù)據(jù)值形式存在。行和列數(shù)據(jù)來(lái)自定義視圖的查詢所引用

基本表，并且在具體引用視圖時(shí)動(dòng)態(tài)生成。

視圖使開(kāi)發(fā)者只關(guān)心感興趣的某些特定數(shù)據(jù)和所負(fù)責(zé)的特定任

務(wù)，只能看到視圖中所定義的數(shù)據(jù)，而不是視圖所引用表中的

數(shù)據(jù)，從而提高了數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全性。

2.視圖有哪些特點(diǎn)？

視圖的特點(diǎn)如下：

?視圖的列可以來(lái)自不同的表，是表的抽象和在邏輯意義上建立

的新關(guān)系。

?視圖是由基本表（實(shí)表）產(chǎn)生的表（虛表）。

?視圖的建立和刪除不影響基本表。

?對(duì)視圖內(nèi)容的更新（添加，刪除和修改）直接影響基本表。

?當(dāng)視圖來(lái)自多個(gè)基本表時(shí)，不允許添加和刪除數(shù)據(jù)。

視圖的操作包括創(chuàng)建視圖，查看視圖，刪除視圖和修改視圖。

3.視圖的使用場(chǎng)景有哪些？

視圖根本用途：簡(jiǎn)化sql查詢，提高開(kāi)發(fā)效率。如果說(shuō)還有另

外一個(gè)用途那就是兼容老的表結(jié)構(gòu)。

下面是視圖的常見(jiàn)使用場(chǎng)景：

?重用SQL語(yǔ)句；

?簡(jiǎn)化復(fù)雜的SQL操作。在編寫(xiě)查詢后，可以方便的重用它而

不必知道它的基本查詢細(xì)節(jié)；

?使用表的組成部分而不是整個(gè)表；

?保護(hù)數(shù)據(jù)?？梢越o用戶授予表的特定部分的訪問(wèn)權(quán)限而不是整

個(gè)表的訪問(wèn)權(quán)限；

?更改數(shù)據(jù)格式和表示。視圖可返回與底層表的表示和格式不同

的數(shù)據(jù)。

4.視圖的優(yōu)點(diǎn)

L查詢簡(jiǎn)單化。視圖能簡(jiǎn)化用戶的操作

2.數(shù)據(jù)安全性。視圖使用戶能以多種角度看待同一數(shù)據(jù)，能夠?qū)?/p>

機(jī)密數(shù)據(jù)提供安全保護(hù)

3.邏輯數(shù)據(jù)獨(dú)立性。視圖對(duì)重構(gòu)數(shù)據(jù)庫(kù)提供了一定程度的邏輯獨(dú)

立性

5.視圖的缺點(diǎn)

1.性能。數(shù)據(jù)庫(kù)必須把視圖的查詢轉(zhuǎn)化成對(duì)基本表的查詢，如果

這個(gè)視圖是由一個(gè)復(fù)雜的多表查詢所定義，那么，即使是視圖

的一個(gè)簡(jiǎn)單查詢，數(shù)據(jù)庫(kù)也把它變成一個(gè)復(fù)雜的結(jié)合體，需要

花費(fèi)一定的時(shí)間。

2.修改限制。當(dāng)用戶試圖修改視圖的某些行時(shí)，數(shù)據(jù)庫(kù)必須把它

轉(zhuǎn)化為對(duì)基本表的某些行的修改。事實(shí)上，當(dāng)從視圖中插入或

者刪除時(shí)，情況也是這樣。對(duì)于簡(jiǎn)單視圖來(lái)說(shuō)，這是很方便

的，但是，對(duì)于比較復(fù)雜的視圖，可能是不可修改的

這些視圖有如下特征：1.有UNIQUE等集合操作符的視圖。2.

有GROUPBY子句的視圖。3.有諸如AVG\SUM\MAX等聚

合函數(shù)的視圖。4.使用DISTINCT關(guān)鍵字的視圖。5.連接表的

視圖（其中有些例外）

6.什么是游標(biāo)？

游標(biāo)是系統(tǒng)為用戶開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的

執(zhí)行結(jié)