mysql經(jīng)典面試題及答案

問題1：char、varchar的區(qū)別是什么？
varchar是變長而char的長度是固定的。如果你的內(nèi)容是固定大小的，你會得到更好的性能。

問題2: TRUNCATE和DELETE的區(qū)別是什么？
DELETE命令從一個(gè)表中刪除某一行，或多行，TRUNCATE命令永久地從表中刪除每一行。

問題3：什么是觸發(fā)器，MySQL中都有哪些觸發(fā)器？
觸發(fā)器是指一段代碼，當(dāng)觸發(fā)某個(gè)事件時(shí)，自動執(zhí)行這些代碼。在MySQL數(shù)據(jù)庫中有如下六種觸發(fā)器：

1、Before Insert

2、After Insert

3、Before Update

4、After Update

5、Before Delete

6、After Delete

問題4：FLOAT和DOUBLE的區(qū)別是什么？

FLOAT類型數(shù)據(jù)可以存儲至多8位十進(jìn)制數(shù)，并在內(nèi)存中占4字節(jié)。

DOUBLE類型數(shù)據(jù)可以存儲至多18位十進(jìn)制數(shù)，并在內(nèi)存中占8字節(jié)。

問題5：如何在MySQL種獲取當(dāng)前日期？

SELECTCURRENT_DATE();

問題6：如何查詢第n高的工資？

SELECTDISTINCT(salary)fromemployeeORDERBYsalaryDESCLIMITn-1,1

問題7：請寫出下面MySQL數(shù)據(jù)類型表達(dá)的意義（int(0)、char(16)、varchar(16)、datetime、text）

知識點(diǎn)分析

此題考察的是MySQL數(shù)據(jù)類型。MySQL數(shù)據(jù)類型屬于MySQL數(shù)據(jù)庫基礎(chǔ)，由此延伸出的知識點(diǎn)還包括如下內(nèi)容：

MySQL基礎(chǔ)操作

MySQL存儲引擎

MySQL鎖機(jī)制

MySQL事務(wù)處理、存儲過程、觸發(fā)器

數(shù)據(jù)類型考點(diǎn)：

1、整數(shù)類型，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示1字節(jié)、2字節(jié)、3字節(jié)、4字節(jié)、8字節(jié)整數(shù)。任何整數(shù)類型都可以加上UNSIGNED屬性，表示數(shù)據(jù)是無符號的，即非負(fù)整數(shù)。

長度：整數(shù)類型可以被指定長度，例如：INT(11)表示長度為11的INT類型。長度在大多數(shù)場景是沒有意義的，它不會限制值的合法范圍，只會影響顯示字符的個(gè)數(shù)，而且需要和UNSIGNED ZEROFILL屬性配合使用才有意義。

例子，假定類型設(shè)定為INT(5)，屬性為UNSIGNED ZEROFILL，如果用戶插入的數(shù)據(jù)為12的話，那么數(shù)據(jù)庫實(shí)際存儲數(shù)據(jù)為00012。

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

DECIMAL可以用于存儲比BIGINT還大的整型，能存儲精確的小數(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用于存儲可變長字符串，它比定長類型更節(jié)省空間。

VARCHAR使用額外1或2個(gè)字節(jié)存儲字符串長度。列長度小于255字節(jié)時(shí)，使用1字節(jié)表示，否則使用2字節(jié)表示。

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

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

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

CHAR適合存儲很短的字符串，或者所有值都接近同一個(gè)長度。

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

使用策略：

對于經(jīng)常變更的數(shù)據(jù)來說，CHAR比VARCHAR更好，因?yàn)镃HAR不容易產(chǎn)生碎片。

對于非常短的列，CHAR比VARCHAR在存儲空間上更有效率。

使用時(shí)要注意只分配需要的空間，更長的列排序時(shí)會消耗更多內(nèi)存。

盡量避免使用TEXT/BLOB類型，查詢時(shí)會使用臨時(shí)表，導(dǎo)致嚴(yán)重的性能開銷。

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

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

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

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

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

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

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

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

如果需要存儲微妙，可以使用bigint存儲。

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

答：int(0)表示數(shù)據(jù)是INT類型，長度是0、char(16)表示固定長度字符串，長度為16、varchar(16)表示可變長度字符串，長度為16、datetime表示時(shí)間類型、text表示字符串類型，能存儲大字符串，最多存儲65535字節(jié)數(shù)據(jù)）

MySQL基礎(chǔ)操作：

常見操作

MySQL的連接和關(guān)閉：mysql -u -p -h -P

-u：指定用戶名
-p：指定密碼
-h：主機(jī)
-P：端口

進(jìn)入MySQL命令行后：G、c、q、s、h、d

G：打印結(jié)果垂直顯示
c：取消當(dāng)前MySQL命令
q：退出MySQL連接
s：顯示服務(wù)器狀態(tài)
h：幫助信息
d：改變執(zhí)行符

MySQL存儲引擎：

1、InnoDB存儲引擎，

默認(rèn)事務(wù)型引擎，最重要最廣泛的存儲引擎，性能非常優(yōu)秀。

數(shù)據(jù)存儲在共享表空間，可以通過配置分開。也就是多個(gè)表和索引都存儲在一個(gè)表空間中，可以通過配置文件改變此配置。

對主鍵查詢的性能高于其他類型的存儲引擎。

內(nèi)部做了很多優(yōu)化，從磁盤讀取數(shù)據(jù)時(shí)會自動構(gòu)建hash索引，插入數(shù)據(jù)時(shí)自動構(gòu)建插入緩沖區(qū)。

通過一些機(jī)制和工具支持真正的熱備份。

支持崩潰后的安全恢復(fù)。

支持行級鎖。

支持外鍵。

2、MyISAM存儲引擎，

擁有全文索引、壓縮、空間函數(shù)。

不支持事務(wù)和行級鎖、不支持崩潰后的安全恢復(fù)。

表存儲在兩個(gè)文件，MYD和MYI。

設(shè)計(jì)簡單，某些場景下性能很好，例如獲取整個(gè)表有多少條數(shù)據(jù)，性能很高。

全文索引不是很常用，不如使用外部的ElasticSearch或Lucene。

3、其他表引擎，
Archive、Blackhole、CSV、Memory

使用策略
在大多數(shù)場景下建議使用InnoDB存儲引擎。

MySQL鎖機(jī)制

表鎖是日常開發(fā)中的常見問題，因此也是面試當(dāng)中最常見的考察點(diǎn)，當(dāng)多個(gè)查詢同一時(shí)刻進(jìn)行數(shù)據(jù)修改時(shí)，就會產(chǎn)生并發(fā)控制的問題。共享鎖和排他鎖，就是讀鎖和寫鎖。

共享鎖，不堵塞，多個(gè)用戶可以同時(shí)讀一個(gè)資源，互不干擾。

排他鎖，一個(gè)寫鎖會阻塞其他的讀鎖和寫鎖，這樣可以只允許一個(gè)用戶進(jìn)行寫入，防止其他用戶讀取正在寫入的資源。

鎖的粒度

表鎖，系統(tǒng)開銷最小，會鎖定整張表，MyIsam使用表鎖。

行鎖，最大程度的支持并發(fā)處理，但是也帶來了最大的鎖開銷，InnoDB使用行鎖。

MySQL事務(wù)處理

MySQL提供事務(wù)處理的表引擎，也就是InnoDB。

服務(wù)器層不管理事務(wù)，由下層的引擎實(shí)現(xiàn)，所以同一個(gè)事務(wù)中，使用多種引擎是不靠譜的。

需要注意，在非事務(wù)表上執(zhí)行事務(wù)操作，MySQL不會發(fā)出提醒，也不會報(bào)錯(cuò)。

存儲過程

為以后的使用保存的一條或多條MySQL語句的集合，因此也可以在存儲過程中加入業(yè)務(wù)邏輯和流程。

可以在存儲過程中創(chuàng)建表，更新數(shù)據(jù)，刪除數(shù)據(jù)等等。

使用策略

可以通過把SQL語句封裝在容易使用的單元中，簡化復(fù)雜的操作

可以保證數(shù)據(jù)的一致性

可以簡化對變動的管理

觸發(fā)器

提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程。
使用場景

可以通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級聯(lián)更改。

實(shí)時(shí)監(jiān)控某張表中的某個(gè)字段的更改而需要做出相應(yīng)的處理。

例如可以生成某些業(yè)務(wù)的編號。

注意不要濫用，否則會造成數(shù)據(jù)庫及應(yīng)用程序的維護(hù)困難。

大家需要牢記以上基礎(chǔ)知識點(diǎn)，重點(diǎn)是理解數(shù)據(jù)類型CHAR和VARCHAR的差異，表存儲引擎InnoDB和MyISAM的區(qū)別。

問題8：請說明InnoDB和MyISAM的區(qū)別

InnoDB支持事務(wù)，MyISAM不支持；

InnoDB數(shù)據(jù)存儲在共享表空間，MyISAM數(shù)據(jù)存儲在文件中；

InnoDB支持行級鎖，MyISAM只支持表鎖；

InnoDB支持崩潰后的恢復(fù)，MyISAM不支持；

InnoDB支持外鍵，MyISAM不支持；

InnoDB不支持全文索引，MyISAM支持全文索引；

問題9：innodb引擎的特性

插入緩沖（insert buffer)

二次寫(double write)

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

預(yù)讀(read ahead)

問題10：請列舉3個(gè)以上表引擎
InnoDB、MyISAM、Memory

問題11：請說明varchar和text的區(qū)別

varchar可指定字符數(shù)，text不能指定，內(nèi)部存儲varchar是存入的實(shí)際字符數(shù)+1個(gè)字節(jié)（n<=255）或2個(gè)字節(jié)(n>255)，text是實(shí)際字符數(shù)+2個(gè)字節(jié)。

text類型不能有默認(rèn)值。

varchar可直接創(chuàng)建索引，text創(chuàng)建索引要指定前多少個(gè)字符。varchar查詢速度快于text,在都創(chuàng)建索引的情況下，text的索引幾乎不起作用。

查詢text需要創(chuàng)建臨時(shí)表。

問題11：varchar(50)中50的含義
最多存放50個(gè)字符，varchar(50)和(200)存儲hello所占空間一樣，但后者在排序時(shí)會消耗更多內(nèi)存，因?yàn)閛rder by col采用fixed_length計(jì)算col長度(memory引擎也一樣)。

問題12：int(20)中20的含義
是指顯示字符的長度，不影響內(nèi)部存儲，只是當(dāng)定義了ZEROFILL時(shí)，前面補(bǔ)多少個(gè) 0

問題13：簡單描述MySQL中，索引，主鍵，唯一索引，聯(lián)合索引的區(qū)別，對數(shù)據(jù)庫的性能有什么影響？

知識點(diǎn)分析

此真題主要考察的是MySQL索引的基礎(chǔ)和類型，由此延伸出的知識點(diǎn)還包括如下內(nèi)容：

MySQL索引的創(chuàng)建原則

MySQL索引的注意事項(xiàng)

MySQL索引的原理

下面我們就來將這些知識一網(wǎng)打盡

索引的基礎(chǔ)

索引類似于書籍的目錄，要想找到一本數(shù)的某個(gè)特定主題，需要先查找書的目錄，定位對應(yīng)的頁碼

存儲引擎使用類似的方式進(jìn)行數(shù)據(jù)查詢，先去索引當(dāng)中找到對應(yīng)的值，然后根據(jù)匹配的索引找到對應(yīng)的數(shù)據(jù)行。

創(chuàng)建索引的語法：

首先創(chuàng)建一個(gè)表：create table t1 (id int primary key,username varchar(20),password varchar(20));

創(chuàng)建單個(gè)索引的語法：CREATE INDEX 索引名 on 表名（字段名）

索引名一般是：表名_字段名

給id創(chuàng)建索引：CREATE INDEX t1_id on t1(id);

創(chuàng)建聯(lián)合索引的語法：CREATE INDEX 索引名 on 表名（字段名1，字段名2）

給username和password創(chuàng)建聯(lián)合索引：CREATE index t1_username_password ON t1(username,password)

其中index還可以替換成unique，primary key，分別代表唯一索引和主鍵索引

刪除索引：DROP INDEX t1_username_password ON t1

索引對性能的影響：

大大減少服務(wù)器需要掃描的數(shù)據(jù)量。

幫助服務(wù)器避免排序和臨時(shí)表。

將隨機(jī)I/O變順序I/O。

大大提高查詢速度。

降低寫的速度（不良影響）。

磁盤占用（不良影響）。

索引的使用場景：

對于非常小的表，大部分情況下全表掃描效率更高。

中到大型表，索引非常有效。

特大型的表，建立和使用索引的代價(jià)會隨之增大，可以使用分區(qū)技術(shù)來解決。

索引的類型：
索引很多種類型，是在MySQL的存儲引擎實(shí)現(xiàn)的。

普通索引：最基本的索引，沒有任何約束限制。

唯一索引：和普通索引類似，但是具有唯一性約束。

主鍵索引：特殊的唯一索引，不允許有空值。

索引的區(qū)別：
-一個(gè)表只能有一個(gè)主鍵索引，但是可以有多個(gè)唯一索引。

主鍵索引一定是唯一索引，唯一索引不是主鍵索引。

主鍵可以與外鍵構(gòu)成參照完整性約束，防止數(shù)據(jù)不一致。

聯(lián)合索引：將多個(gè)列組合在一起創(chuàng)建索引，可以覆蓋多個(gè)列。（也叫復(fù)合索引，組合索引）

外鍵索引：只有InnoDB類型的表才可以使用外鍵索引，保證數(shù)據(jù)的一致性、完整性、和實(shí)現(xiàn)級聯(lián)操作（基本不用）。

全文索引：MySQL自帶的全文索引只能用于MyISAM，并且只能對英文進(jìn)行全文檢索 （基本不用）

MySQL索引的創(chuàng)建原則

最適合創(chuàng)建索引的列是出現(xiàn)在WHERE或ON子句中的列，或連接子句中的列而不是出現(xiàn)在SELECT關(guān)鍵字后的列。

索引列的基數(shù)越大，數(shù)據(jù)區(qū)分度越高，索引的效果越好。

對于字符串進(jìn)行索引，應(yīng)該制定一個(gè)前綴長度，可以節(jié)省大量的索引空間。

根據(jù)情況創(chuàng)建聯(lián)合索引，聯(lián)合索引可以提高查詢效率。

避免創(chuàng)建過多的索引，索引會額外占用磁盤空間，降低寫操作效率。

主鍵盡可能選擇較短的數(shù)據(jù)類型，可以有效減少索引的磁盤占用提高查詢效率。

MySQL索引的注意事項(xiàng)
1、聯(lián)合索引遵循前綴原則

KEY(a,b,c)
WHEREa=1ANDb=2ANDc=3
WHEREa=1ANDb=2
WHEREa=1
#以上SQL語句可以用到索引
WHEREb=2ANDc=3
WHEREa=1ANDc=3
#以上SQL語句用不到索引

2、LIKE查詢，%不能在前

WHEREnameLIKE"%wang%"

#以上語句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。

3、列值為空（NULL）時(shí)是可以使用索引的，但MySQL難以優(yōu)化引用了可空列的查詢,它會使索引、索引統(tǒng)計(jì)和值更加復(fù)雜。可空列需要更多的儲存空間，還需要在MySQL內(nèi)部進(jìn)行特殊處理。

4、如果MySQL估計(jì)使用索引比全表掃描更慢，會放棄使用索引，例如：
表中只有100條數(shù)據(jù)左右。對于SQL語句WHERE id > 1 AND id < 100，MySQL會優(yōu)先考慮全表掃描。

5、如果關(guān)鍵詞or前面的條件中的列有索引，后面的沒有，所有列的索引都不會被用到。

6、列類型是字符串，查詢時(shí)一定要給值加引號，否則索引失效，例如：
列name varchar(16)，存儲了字符串"100"
WHERE name = 100;
以上SQL語句能搜到，但無法用到索引。

MySQL索引的原理

MySQL索引是用一種叫做聚簇索引的數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)的，下面我們就來看一下什么是聚簇索引。

聚簇索引是一種數(shù)據(jù)存儲方式，它實(shí)際上是在同一個(gè)結(jié)構(gòu)中保存了B+樹索引和數(shù)據(jù)行，InnoDB表是按照聚簇索引組織的（類似于Oracle的索引組織表）。

注：
B+樹是一種樹數(shù)據(jù)結(jié)構(gòu)，是一個(gè)n叉排序樹，每個(gè)節(jié)點(diǎn)通常有多個(gè)孩子，一棵B+樹包含根節(jié)點(diǎn)、內(nèi)部節(jié)點(diǎn)和葉子節(jié)點(diǎn)。根節(jié)點(diǎn)可能是一個(gè)葉子節(jié)點(diǎn)，也可能是一個(gè)包含兩個(gè)或兩個(gè)以上孩子節(jié)點(diǎn)的節(jié)點(diǎn)。
B+樹通常用于數(shù)據(jù)庫和操作系統(tǒng)的文件系統(tǒng)中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系統(tǒng)都在使用B+樹作為元數(shù)據(jù)索引。B+樹的特點(diǎn)是能夠保持?jǐn)?shù)據(jù)穩(wěn)定有序，其插入與修改擁有較穩(wěn)定的對數(shù)時(shí)間復(fù)雜度。B+樹元素自底向上插入。

InnoDB通過主鍵聚簇?cái)?shù)據(jù)，如果沒有定義主鍵，會選擇一個(gè)唯一的非空索引代替，如果沒有這樣的索引，會隱式定義個(gè)主鍵作為聚簇索引。

下圖形象說明了聚簇索引表(InnoDB)和普通的堆組織表(MyISAM)的區(qū)別：

最常問的MySQL面試題三——每個(gè)開發(fā)人員都應(yīng)該知道
對于普通的堆組織表來說（右圖），表數(shù)據(jù)和索引是分別存儲的，主鍵索引和二級索引存儲上沒有任何區(qū)別。
而對于聚簇索引表來說（左圖），表數(shù)據(jù)是和主鍵一起存儲的，主鍵索引的葉結(jié)點(diǎn)存儲行數(shù)據(jù)，二級索引的葉結(jié)點(diǎn)存儲行的主鍵值。
聚簇索引表最大限度地提高了I/O密集型應(yīng)用的性能，但它也有以下幾個(gè)限制：

1）插入速度嚴(yán)重依賴于插入順序，按照主鍵的順序插入是最快的方式，否則將會出現(xiàn)頁分裂，嚴(yán)重影響性能。因此，對于InnoDB表，我們一般都會定義一個(gè)自增的ID列為主鍵。

2）更新主鍵的代價(jià)很高，因?yàn)閷?dǎo)致被更新的行移動。因此，對于InnoDB表，我們一般定義主鍵為不可更新。

3）二級索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)。

二級索引的葉節(jié)點(diǎn)存儲的是主鍵值，而不是行指針，這是為了減少當(dāng)出現(xiàn)行移動或數(shù)據(jù)頁分裂時(shí)二級索引的維護(hù)工作，但會讓二級索引占用更多的空間。

解題方法

在一些MySQL索引基礎(chǔ)考題中，我們可以輕松的通過索引基礎(chǔ)和類型來解決此類問題，對于一些索引創(chuàng)建注意事項(xiàng)方面的考點(diǎn)，我們可以通過索引創(chuàng)建原則和注意事項(xiàng)來解決。

問題14：創(chuàng)建MySQL聯(lián)合索引應(yīng)該注意什么？
需遵循前綴原則

問題15：列值為NULL時(shí)，查詢是否會用到索引？
在MySQL里NULL值的列也是走索引的。當(dāng)然，如果計(jì)劃對列進(jìn)行索引，就要盡量避免把它設(shè)置為可空，MySQL難以優(yōu)化引用了可空列的查詢,它會使索引、索引統(tǒng)計(jì)和值更加復(fù)雜。

問題16：以下語句是否會應(yīng)用索引：SELECTFROM users WHERE YEAR(adddate) < 2007;*
不會，因?yàn)橹灰猩婕暗竭\(yùn)算，MySQL就不會使用索引。

問題17：MyISAM索引實(shí)現(xiàn)？
MyISAM存儲引擎使用B+Tree作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。MyISAM的索引方式也叫做非聚簇索引的，之所以這么稱呼是為了與InnoDB的聚簇索引區(qū)分。

問題17：MyISAM索引與InnoDB索引的區(qū)別？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

InnoDB的主鍵索引的葉子節(jié)點(diǎn)存儲著行數(shù)據(jù)，因此主鍵索引非常高效。

MyISAM索引的葉子節(jié)點(diǎn)存儲的是行數(shù)據(jù)地址，需要再尋址一次才能得到數(shù)據(jù)。

InnoDB非主鍵索引的葉子節(jié)點(diǎn)存儲的是主鍵和其他帶索引的列數(shù)據(jù)，因此查詢時(shí)做到覆蓋索引會非常高效。

問題18：以下三條sql 如何建索引，只建一條怎么建？

WHEREa=1ANDb=1
WHEREb=1
WHEREb=1ORDERBYtimeDESC

以順序b,a,time建立聯(lián)合索引，CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因?yàn)?a href="http://www.ttokpm.com/article/zt/" target="_blank">最新MySQL版本會優(yōu)化WHERE子句后面的列順序，以匹配聯(lián)合索引順序。

問題19：有A(id,sex,par,c1,c2),B(id,age,c1,c2)兩張表，其中A.id與B.id關(guān)聯(lián)，現(xiàn)在要求寫出一條SQL語句，將B中age>50的記錄的c1,c2更新到A表中同一記錄中的c1,c2字段中
考點(diǎn)分析
這道題主要考察的是MySQL的關(guān)聯(lián)UPDATE語句
延伸考點(diǎn)：

MySQL的關(guān)聯(lián)查詢語句

MySQL的關(guān)聯(lián)UPDATE語句

針對剛才這道題，答案可以是如下兩種形式的寫法：

UPDATEA,BSETA.c1=B.c1,A.c2=B.c2WHEREA.id=B.id
UPDATEAINNERJOINBONA.id=B.idSETA.c1=B.c1,A.c2=B.c2
再加上B中age>50的條件：
UPDATEA,BsetA.c1=B.c1,A.c2=B.c2WHEREA.id=B.idandB.age>50;
UPDATEAINNERJOINBONA.id=B.idsetA.c1=B.c1,A.c2=B.c2WHEREB.age>50

MySQL的關(guān)聯(lián)查詢語句

六種關(guān)聯(lián)查詢

交叉連接（CROSS JOIN）

內(nèi)連接（INNER JOIN）

外連接（LEFT JOIN/RIGHT JOIN）

聯(lián)合查詢（UNION與UNION ALL）

全連接（FULL JOIN）

交叉連接（CROSS JOIN）

SELECT*FROMA,B(,C)或者
SELECT*FROMACROSSJOINB(CROSSJOINC)
#沒有任何關(guān)聯(lián)條件，結(jié)果是笛卡爾積，結(jié)果集會很大，沒有意義，很少使用
內(nèi)連接（INNERJOIN）
SELECT*FROMA,BWHEREA.id=B.id或者
SELECT*FROMAINNERJOINBONA.id=B.id
多表中同時(shí)符合某種條件的數(shù)據(jù)記錄的集合，INNERJOIN可以縮寫為JOIN

內(nèi)連接分為三類

等值連接：ON A.id=B.id

不等值連接：ON A.id > B.id

自連接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外連接（LEFT JOIN/RIGHT JOIN）

左外連接：LEFT OUTER JOIN, 以左表為主，先查詢出左表，按照ON后的關(guān)聯(lián)條件匹配右表，沒有匹配到的用NULL填充，可以簡寫成LEFT JOIN

右外連接：RIGHT OUTER JOIN, 以右表為主，先查詢出右表，按照ON后的關(guān)聯(lián)條件匹配左表，沒有匹配到的用NULL填充，可以簡寫成RIGHT JOIN

聯(lián)合查詢（UNION與UNION ALL）

SELECT*FROMAUNIONSELECT*FROMBUNION...

就是把多個(gè)結(jié)果集集中在一起，UNION前的結(jié)果為基準(zhǔn)，需要注意的是聯(lián)合查詢的列數(shù)要相等，相同的記錄行會合并

如果使用UNION ALL，不會合并重復(fù)的記錄行

效率 UNION 高于 UNION ALL

全連接（FULL JOIN）

MySQL不支持全連接

可以使用LEFT JOIN 和UNION和RIGHT JOIN聯(lián)合使用

SELECT*FROMALEFTJOINBONA.id=B.idUNION
SELECT*FROMARIGHTJOINBONA.id=B.id

嵌套查詢

用一條SQL語句得結(jié)果作為另外一條SQL語句得條件，效率不好把握
SELECT * FROM A WHERE id IN (SELECT id FROM B)

解題方法

根據(jù)考題要搞清楚表的結(jié)果和多表之間的關(guān)系，根據(jù)想要的結(jié)果思考使用那種關(guān)聯(lián)方式，通常把要查詢的列先寫出來，然后分析這些列都屬于哪些表，才考慮使用關(guān)聯(lián)查詢

問題20：
為了記錄足球比賽的結(jié)果，設(shè)計(jì)表如下：
team：參賽隊(duì)伍表
match：賽程表
其中，match賽程表中的hostTeamID與guestTeamID都和team表中的teamID關(guān)聯(lián)，查詢2006-6-1到2006-7-1之間舉行的所有比賽，并且用以下形式列出：拜仁 2:0 不萊梅 2006-6-21

首先列出需要查詢的列：

表team

teamID teamName

表match

match ID

hostTeamID

guestTeamID

matchTime matchResult

其次列出結(jié)果列：

主隊(duì) 結(jié)果 客對 時(shí)間

初步寫一個(gè)基礎(chǔ)的SQL：

SELECThostTeamID,matchResult,matchTimeguestTeamIDfrommatchwherematchTimebetween"2006-6-1"and"2006-7-1";

通過外鍵聯(lián)表，完成最終SQL：

selectt1.teamName,m.matchResult,t2.teamName,m.matchTimefrommatchasmleftjointeamast1onm.hostTeamID=t1.teamID,leftjointeamt2onm.guestTeamID=t2.guestTeamIDwherem.matchTimebetween"2006-6-1"and"2006-7-1"

問題21：UNION與UNION ALL的區(qū)別？

如果使用UNION ALL，不會合并重復(fù)的記錄行

效率 UNION 高于 UNION ALL

問題22：一個(gè)6億的表a，一個(gè)3億的表b，通過外鍵tid關(guān)聯(lián)，你如何最快的查詢出滿足條件的第50000到第50200中的這200條數(shù)據(jù)記錄。

1、如果A表TID是自增長,并且是連續(xù)的,B表的ID為索引

select*froma,bwherea.tid=b.idanda.tid>50000limit200;

2、如果A表的TID不是連續(xù)的,那么就需要使用覆蓋索引.TID要么是主鍵,要么是輔助索引,B表ID也需要有索引。

select*fromb,(selecttidfromalimit50000,200)awhereb.id=a.tid;

問題23：拷貝表( 拷貝數(shù)據(jù), 源表名：a 目標(biāo)表名：b)

insertintob(a,b,c)selectd,e,ffroma;

問題24：Student(S#,Sname,Sage,Ssex) 學(xué)生表 Course(C#,Cname,T#) 課程表 SC(S#,C#,score) 成績表 Teacher(T#,Tname) 教師表 查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號、姓名

selectStudent.S#,Student.Sname
fromStudent
whereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname=’葉平’);

問題25：隨機(jī)取出10條數(shù)據(jù)

SELECT*FROMusersWHEREid>=((SELECTMAX(id)FROMusers)-(SELECTMIN(id)FROMusers))*RAND()+(SELECTMIN(id)FROMusers)LIMIT10
#此方法效率比直接用SELECT*FROMusersorderbyrand()LIMIT10高很多

問題26：請簡述項(xiàng)目中優(yōu)化SQL語句執(zhí)行效率的方法，從哪些方面，SQL語句性能如何分析？

考點(diǎn)分析：
這道題主要考察的是查找分析SQL語句查詢速度慢的方法
延伸考點(diǎn)：

優(yōu)化查詢過程中的數(shù)據(jù)訪問

優(yōu)化長難的查詢語句

優(yōu)化特定類型的查詢語句

如何查找查詢速度慢的原因
記錄慢查詢?nèi)罩?，分析查詢?nèi)罩?，不要直接打開慢查詢?nèi)罩具M(jìn)行分析，這樣比較浪費(fèi)時(shí)間和精力，可以使用pt-query-digest工具進(jìn)行分析

使用show profile

setprofiling=1;開啟，服務(wù)器上所有執(zhí)行語句會記錄執(zhí)行時(shí)間，存到臨時(shí)表中
showprofiles
showprofileforquery臨時(shí)表ID

使用show status

show status會返回一些計(jì)數(shù)器，show global status會查看所有服務(wù)器級別的所有計(jì)數(shù)
有時(shí)根據(jù)這些計(jì)數(shù)，可以推測出哪些操作代價(jià)較高或者消耗時(shí)間多

show processlist

觀察是否有大量線程處于不正常的狀態(tài)或特征

最常問的MySQL面試題五——每個(gè)開發(fā)人員都應(yīng)該知道

使用explain

分析單條SQL語句

優(yōu)化查詢過程中的數(shù)據(jù)訪問

訪問數(shù)據(jù)太多導(dǎo)致查詢性能下降

確定應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)，可能是太多行或列

確認(rèn)MySQL服務(wù)器是否在分析大量不必要的數(shù)據(jù)行

避免犯如下SQL語句錯(cuò)誤

查詢不需要的數(shù)據(jù)。解決辦法：使用limit解決

多表關(guān)聯(lián)返回全部列。解決辦法：指定列名

總是返回全部列。解決辦法：避免使用SELECT *

重復(fù)查詢相同的數(shù)據(jù)。解決辦法：可以緩存數(shù)據(jù)，下次直接讀取緩存

是否在掃描額外的記錄。解決辦法：

使用explain進(jìn)行分析，如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)，但只返回少數(shù)的行，可以通過如下技巧去優(yōu)化：

使用索引覆蓋掃描，把所有的列都放到索引中，這樣存儲引擎不需要回表獲取對應(yīng)行就可以返回結(jié)果。

改變數(shù)據(jù)庫和表的結(jié)構(gòu)，修改數(shù)據(jù)表范式

重寫SQL語句，讓優(yōu)化器可以以更優(yōu)的方式執(zhí)行查詢。

優(yōu)化長難的查詢語句

一個(gè)復(fù)雜查詢還是多個(gè)簡單查詢

MySQL內(nèi)部每秒能掃描內(nèi)存中上百萬行數(shù)據(jù)，相比之下，響應(yīng)數(shù)據(jù)給客戶端就要慢得多

使用盡可能小的查詢是好的，但是有時(shí)將一個(gè)大的查詢分解為多個(gè)小的查詢是很有必要的。

切分查詢

將一個(gè)大的查詢分為多個(gè)小的相同的查詢

一次性刪除1000萬的數(shù)據(jù)要比一次刪除1萬，暫停一會的方案更加損耗服務(wù)器開銷。

分解關(guān)聯(lián)查詢，讓緩存的效率更高。

執(zhí)行單個(gè)查詢可以減少鎖的競爭。

在應(yīng)用層做關(guān)聯(lián)更容易對數(shù)據(jù)庫進(jìn)行拆分。

查詢效率會有大幅提升。

較少冗余記錄的查詢。

優(yōu)化特定類型的查詢語句

count(*)會忽略所有的列，直接統(tǒng)計(jì)所有列數(shù)，不要使用count(列名)

MyISAM中，沒有任何where條件的count(*)非?？臁?/p>

當(dāng)有where條件時(shí)，MyISAM的count統(tǒng)計(jì)不一定比其它引擎快。

可以使用explain查詢近似值，用近似值替代count(*)

增加匯總表

使用緩存

優(yōu)化關(guān)聯(lián)查詢

確定ON或者USING子句中是否有索引。

確保GROUP BY和ORDER BY只有一個(gè)表中的列，這樣MySQL才有可能使用索引。

優(yōu)化子查詢

用關(guān)聯(lián)查詢替代

優(yōu)化GROUP BY和DISTINCT

這兩種查詢據(jù)可以使用索引來優(yōu)化，是最有效的優(yōu)化方法

關(guān)聯(lián)查詢中，使用標(biāo)識列分組的效率更高

如果不需要ORDER BY，進(jìn)行GROUP BY時(shí)加ORDER BY NULL，MySQL不會再進(jìn)行文件排序。

WITH ROLLUP超級聚合，可以挪到應(yīng)用程序處理

優(yōu)化LIMIT分頁

LIMIT偏移量大的時(shí)候，查詢效率較低

可以記錄上次查詢的最大ID，下次查詢時(shí)直接根據(jù)該ID來查詢

優(yōu)化UNION查詢

UNION ALL的效率高于UNION

優(yōu)化WHERE子句

解題方法

對于此類考題，先說明如何定位低效SQL語句，然后根據(jù)SQL語句可能低效的原因做排查，先從索引著手，如果索引沒有問題，考慮以上幾個(gè)方面，數(shù)據(jù)訪問的問題，長難查詢句的問題還是一些特定類型優(yōu)化的問題，逐一回答。

SQL語句優(yōu)化的一些方法？

1.對查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

2.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

selectidfromtwherenumisnull可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：selectidfromtwherenum=

3.應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描。

4.應(yīng)盡量避免在 where 子句中使用or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

selectidfromtwherenum=10ornum=20可以這樣查詢：selectidfromtwherenum=10unionallselectidfromtwherenum=20

5.in 和 not in 也要慎用，否則會導(dǎo)致全表掃描，如：

selectidfromtwherenumin(1,2,3)對于連續(xù)的數(shù)值，能用between就不要用in了：selectidfromtwherenumbetween1and3

6.下面的查詢也將導(dǎo)致全表掃描：select id from t where name like ‘%李%’若要提高效率，可以考慮全文檢索。

7. 如果在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然 而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描：

selectidfromtwherenum=@num可以改為強(qiáng)制查詢使用索引：selectidfromtwith(index(索引名))wherenum=@num

8.應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

selectidfromtwherenum/2=100應(yīng)改為:selectidfromtwherenum=100*2

9.應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

selectidfromtwheresubstring(name,1,3)=’abc’，name以abc開頭的id應(yīng)改為:
selectidfromtwherenamelike‘a(chǎn)bc%’

10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。

問題27：簡述MySQL分表操作和分區(qū)操作的工作原理，分別說說分區(qū)和分表的使用場景和各自優(yōu)缺點(diǎn)。

考點(diǎn)分析

分區(qū)表的原理

分庫分表的原理

延伸：

MySQL的復(fù)制原理及負(fù)載均衡

分區(qū)表的工作原理

對用戶而言，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層MySQL將其分成了多個(gè)物理子表，這對用戶來說是透明的，每一個(gè)分區(qū)表都會使用一個(gè)獨(dú)立的表文件。

如圖所示：MySQL將表分成多個(gè)物理字表，但PHP客戶端并無感知，仍然認(rèn)為操作的是一個(gè)表。

創(chuàng)建表時(shí)使用partition by子句定義每個(gè)分區(qū)存放的數(shù)據(jù)，執(zhí)行查詢時(shí)，優(yōu)化器會根據(jù)分區(qū)定義過濾那些沒有需要的數(shù)據(jù)的分區(qū)，這樣只需要查詢數(shù)據(jù)所在分區(qū)即可。

這樣子表相對于未分區(qū)的表來說占用空間小，數(shù)據(jù)量更小，因此操作速度更快。

分區(qū)的主要目的是將數(shù)據(jù)按照一個(gè)較粗的粒度分在不同的表中，這樣可以將相關(guān)的數(shù)據(jù)存放在一起，而且如果想一次性的刪除整個(gè)分區(qū)的數(shù)據(jù)也和方便。

適用場景

1、表非常大，無法全部存在內(nèi)存，或者只在表的最后有熱點(diǎn)數(shù)據(jù)，其他都是歷史數(shù)據(jù)。

2、分區(qū)表的數(shù)據(jù)更易維護(hù)，可以對獨(dú)立的分區(qū)進(jìn)行獨(dú)立的操作。

3、分區(qū)表的數(shù)據(jù)可以分布在不同的機(jī)器上，從而高效適用資源。

4、可以使用分區(qū)表來避免某些特殊的瓶頸

5、可以備份和恢復(fù)獨(dú)立的分區(qū)

限制

1、一個(gè)表最多只能有1024個(gè)分區(qū)

2、5.1版本中，分區(qū)表表達(dá)式必須是整數(shù)，5.5可以使用列分區(qū)

3、分區(qū)表字段如果有主鍵和唯一索引列，那么主鍵列和唯一索引列都必須包含進(jìn)來

4、分區(qū)表中無法使用外鍵約束

5、需要對現(xiàn)有表的結(jié)構(gòu)進(jìn)行修改

6、所有分區(qū)都必須使用相同的存儲引擎

7、分區(qū)函數(shù)中可以使用的函數(shù)和表達(dá)式會有一些限制

8、某些存儲引擎不支持分區(qū)

9、對于MyISAM的分區(qū)表，不能使用load index into cache

10、對于MyISAM表，使用分區(qū)表時(shí)需要打開更多的文件描述符

分庫分表的工作原理

通過一些HASH算法或者工具實(shí)現(xiàn)將一張數(shù)據(jù)表垂直或者水平進(jìn)行物理切分

適用場景

1、單表記錄條數(shù)達(dá)到百萬或千萬級別時(shí)

2、解決表鎖的問題

分表方式

水平分表：

表很大，分割后可以降低在查詢時(shí)需要讀的數(shù)據(jù)和索引的頁數(shù)，同時(shí)也降低了索引的層數(shù)，提高查詢次數(shù)

適用場景

1、表中的數(shù)據(jù)本身就有獨(dú)立性，例如表中分表記錄各個(gè)地區(qū)的數(shù)據(jù)或者不同時(shí)期的數(shù)據(jù)，特別是有些數(shù)據(jù)常用，有些不常用。

2、需要把數(shù)據(jù)存放在多個(gè)介質(zhì)上。

水平切分的缺點(diǎn)

1、給應(yīng)用增加復(fù)雜度，通常查詢時(shí)需要多個(gè)表名，查詢所有數(shù)據(jù)都需UNION操作

2、在許多數(shù)據(jù)庫應(yīng)用中，這種復(fù)雜度會超過它帶來的優(yōu)點(diǎn)，查詢時(shí)會增加讀一個(gè)索引層的磁盤次數(shù)

垂直分表

把主鍵和一些列放在一個(gè)表，然后把主鍵和另外的列放在另一個(gè)表中

適用場景

1、如果一個(gè)表中某些列常用，另外一些列不常用

2、可以使數(shù)據(jù)行變小，一個(gè)數(shù)據(jù)頁能存儲更多數(shù)據(jù)，查詢時(shí)減少I/O次數(shù)

缺點(diǎn)

管理冗余列，查詢所有數(shù)據(jù)需要join操作

分表缺點(diǎn)

有些分表的策略基于應(yīng)用層的邏輯算法，一旦邏輯算法改變，整個(gè)分表邏輯都會改變，擴(kuò)展性較差

對于應(yīng)用層來說，邏輯算法增加開發(fā)成本

MySQL的復(fù)制原理及負(fù)載均衡

MySQL主從復(fù)制工作原理

在主庫上把數(shù)據(jù)更高記錄到二進(jìn)制日志

從庫將主庫的日志復(fù)制到自己的中繼日志

從庫讀取中繼日志的事件，將其重放到從庫數(shù)據(jù)中

MySQL主從復(fù)制解決的問題

數(shù)據(jù)分布：隨意開始或停止復(fù)制，并在不同地理位置分布數(shù)據(jù)備份

負(fù)載均衡：降低單個(gè)服務(wù)器的壓力

高可用和故障切換：幫助應(yīng)用程序避免單點(diǎn)失敗

升級測試：可以用更高版本的MySQL作為從庫

解題方法

充分掌握分區(qū)分表的工作原理和適用場景，在面試中，此類題通常比較靈活，會給一些現(xiàn)有公司遇到問題的場景，大家可以根據(jù)分區(qū)分表，MySQL復(fù)制、負(fù)載均衡的適用場景來根據(jù)情況進(jìn)行回答

問:28：設(shè)定網(wǎng)站用戶數(shù)量在千萬級，但是活躍用戶數(shù)量只有1%，如何通過優(yōu)化數(shù)據(jù)庫提高活躍用戶訪問速度？

可以使用MySQL的分區(qū)，把活躍用戶分在一個(gè)區(qū)，不活躍用戶分在另外一個(gè)區(qū)，本身活躍用戶區(qū)數(shù)據(jù)量比較少，因此可以提高活躍用戶訪問速度。

還可以水平分表，把活躍用戶分在一張表，不活躍用戶分在另一張表，可以提高活躍用戶訪問速度。

問題29：SQL語句應(yīng)該考慮哪些安全性？

考點(diǎn)分析

SQL查詢的安全方案

延伸：

MySQL的其它安全設(shè)置

SQL查詢的安全方案

1、使用預(yù)處理語句防止SQL注入

deleteuserwhereid=?

2、寫入數(shù)據(jù)庫的數(shù)據(jù)一定要進(jìn)行特殊字符轉(zhuǎn)義

3、查詢錯(cuò)誤信息不要返回給用戶，將錯(cuò)誤記錄到日志

注意：

PHP端盡量使用PDO對數(shù)據(jù)庫進(jìn)行操作，PDO擁有對預(yù)處理語句很好的支持的方法，MySQLi也有，但是可擴(kuò)展性不如PDO，MySQL函數(shù)在新版中已經(jīng)趨向于淘汰，所以不建議使用，而且它沒有很好的支持預(yù)處理方法。

1、定期做數(shù)據(jù)備份

2、不給查詢用戶root權(quán)限，合理分配權(quán)限

3、關(guān)閉遠(yuǎn)程訪問數(shù)據(jù)庫權(quán)限

4、修改root口令，不用默認(rèn)口令，使用較復(fù)雜的口令

5、刪除多余的用戶

6、改變r(jià)oot用戶的名稱

7、限制一般用戶瀏覽其它庫

8、限制用戶對數(shù)據(jù)文件的訪問權(quán)限

解題方法

通常情況下，SQL安全的考點(diǎn)都在防SQL注入的問題，因此只要遇到此類考點(diǎn)，優(yōu)先考慮SQL注入的防護(hù)手段。

問題30：為什么使用mysqli和PDO連接數(shù)據(jù)庫會比mysql連接數(shù)據(jù)庫更安全? mysqli和PDO支持預(yù)處理，可以防止SQL注入，mysql不支持預(yù)處理。

編輯：黃飛