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

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

問題3：什么是觸發(fā)器，MySQL中都有哪些觸發(fā)器悦施？
觸發(fā)器是指一段代碼并扇，當(dāng)觸發(fā)某個事件時，自動執(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)前日期坯辩？

SELECT CURRENT_DATE();

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

SELECT DISTINCT(salary) from employee ORDER BY salary DESC LIMIT n-1,1

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

知識點分析

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

• MySQL基礎(chǔ)操作

• MySQL存儲引擎

• MySQL鎖機制

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

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

• 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ù)場景是沒有意義的，它不會限制值的合法范圍侦铜，只會影響顯示字符的個數(shù)，而且需要和UNSIGNED ZEROFILL屬性配合使用才有意義钟鸵。
  例子钉稍，假定類型設(shè)定為INT(5)，屬性為UNSIGNED ZEROFILL棺耍，如果用戶插入的數(shù)據(jù)為12的話贡未，那么數(shù)據(jù)庫實際存儲數(shù)據(jù)為00012。

• 2蒙袍、實數(shù)類型俊卤，包括FLOAT、DOUBLE害幅、DECIMAL消恍。
  DECIMAL可以用于存儲比BIGINT還大的整型，能存儲精確的小數(shù)以现。
  而FLOAT和DOUBLE是有取值范圍的狠怨，并支持使用標(biāo)準(zhǔn)的浮點進(jìn)行近似計算。
  計算時FLOAT和DOUBLE相比DECIMAL效率更高一些邑遏，DECIMAL你可以理解成是用字符串進(jìn)行處理佣赖。

• 3、字符串類型记盒，包括VARCHAR憎蛤、CHAR、TEXT纪吮、BLOB
  VARCHAR用于存儲可變長字符串俩檬，它比定長類型更節(jié)省空間萎胰。
  VARCHAR使用額外1或2個字節(jié)存儲字符串長度。列長度小于255字節(jié)時豆胸，使用1字節(jié)表示奥洼，否則使用2字節(jié)表示。
  VARCHAR存儲的內(nèi)容超出設(shè)置的長度時晚胡，內(nèi)容會被截斷灵奖。
  CHAR是定長的，根據(jù)定義的字符串長度分配足夠的空間估盘。
  CHAR會根據(jù)需要使用空格進(jìn)行填充方便比較湿蛔。
  CHAR適合存儲很短的字符串顿天，或者所有值都接近同一個長度。
  CHAR存儲的內(nèi)容超出設(shè)置的長度時，內(nèi)容同樣會被截斷藏否。

使用策略：
對于經(jīng)常變更的數(shù)據(jù)來說，CHAR比VARCHAR更好腹备，因為CHAR不容易產(chǎn)生碎片感憾。
對于非常短的列，CHAR比VARCHAR在存儲空間上更有效率境钟。
使用時要注意只分配需要的空間锦担，更長的列排序時會消耗更多內(nèi)存。
盡量避免使用TEXT/BLOB類型慨削，查詢時會使用臨時表洞渔，導(dǎo)致嚴(yán)重的性能開銷。

• 4缚态、枚舉類型（ENUM）磁椒，把不重復(fù)的數(shù)據(jù)存儲為一個預(yù)定義的集合。
  有時可以使用ENUM代替常用的字符串類型玫芦。
  ENUM存儲非常緊湊浆熔，會把列表值壓縮到一個或兩個字節(jié)。
  ENUM在內(nèi)部存儲時姨俩，其實存的是整數(shù)蘸拔。
  盡量避免使用數(shù)字作為ENUM枚舉的常量，因為容易混亂环葵。
  排序是按照內(nèi)部存儲的整數(shù)

• 5调窍、日期和時間類型，盡量使用timestamp张遭，空間效率高于datetime邓萨，
  用整數(shù)保存時間戳通常不方便處理。
  如果需要存儲微妙，可以使用bigint存儲缔恳。
  看到這里宝剖，這道真題是不是就比較容易回答了。

答：int(0)表示數(shù)據(jù)是INT類型歉甚，長度是0万细、char(16)表示固定長度字符串，長度為16纸泄、varchar(16)表示可變長度字符串赖钞，長度為16、datetime表示時間類型聘裁、text表示字符串類型雪营，能存儲大字符串，最多存儲65535字節(jié)數(shù)據(jù)）

MySQL基礎(chǔ)操作：

常見操作

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

-u：指定用戶名
-p：指定密碼
-h：主機
-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ù)存儲在共享表空間，可以通過配置分開善镰。也就是多個表和索引都存儲在一個表空間中妹萨，可以通過配置文件改變此配置。

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

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

• 通過一些機制和工具支持真正的熱備份树姨。

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

• 支持行級鎖桥状。

• 支持外鍵帽揪。

2、MyISAM存儲引擎辅斟，

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

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

• 表存儲在兩個文件，MYD和MYI扰藕。

• 設(shè)計簡單缓苛，某些場景下性能很好，例如獲取整個表有多少條數(shù)據(jù)邓深，性能很高未桥。

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

3钢属、其他表引擎，
Archive门躯、Blackhole淆党、CSV、Memory

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

MySQL鎖機制

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

• 共享鎖勒庄，不堵塞，多個用戶可以同時讀一個資源瘫里，互不干擾实蔽。

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

鎖的粒度

• 表鎖劳殖，系統(tǒng)開銷最小铐尚，會鎖定整張表，MyIsam使用表鎖哆姻。

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

MySQL事務(wù)處理

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

• 服務(wù)器層不管理事務(wù)誉简，由下層的引擎實現(xiàn)碉就，所以同一個事務(wù)中，使用多種引擎是不靠譜的闷串。

• 需要注意瓮钥，在非事務(wù)表上執(zhí)行事務(wù)操作，MySQL不會發(fā)出提醒烹吵，也不會報錯碉熄。

存儲過程

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

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

使用策略

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

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

• 可以簡化對變動的管理

觸發(fā)器

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

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

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

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

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

• 大家需要牢記以上基礎(chǔ)知識點轧邪，重點是理解數(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個以上表引擎
InnoDB胶逢、MyISAM厅瞎、Memory

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

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

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

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

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

問題11：varchar(50)中50的含義
最多存放50個字符者填，varchar(50)和(200)存儲hello所占空間一樣浩村，但后者在排序時會消耗更多內(nèi)存，因為order by col采用fixed_length計算col長度(memory引擎也一樣)占哟。

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

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

知識點分析

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

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

• MySQL索引的注意事項

• MySQL索引的原理

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

索引的基礎(chǔ)

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

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

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

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

• 創(chuàng)建單個索引的語法：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ù)器避免排序和臨時表诡延。

• 將隨機I/O變順序I/O滞欠。

• 大大提高查詢速度。

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

• 磁盤占用（不良影響）筛璧。

索引的使用場景：

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

• 中到大型表夭谤，索引非常有效棺牧。

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

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

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

• 唯一索引：和普通索引類似榆鼠，但是具有唯一性約束纲爸。

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

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

• 主鍵索引一定是唯一索引神妹，唯一索引不是主鍵索引颓哮。

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

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

• 外鍵索引：只有InnoDB類型的表才可以使用外鍵索引姨伤，保證數(shù)據(jù)的一致性、完整性庸疾、和實現(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)該制定一個前綴長度串绩，可以節(jié)省大量的索引空間。

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

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

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

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

KEY(a,b,c)WHERE a = 1 AND b = 2 AND c = 3WHERE a = 1 AND b = 2WHERE a = 1#以上SQL語句可以用到索引WHERE b = 2 AND c = 3WHERE a = 1 AND c = 3#以上SQL語句用不到索引

2罪裹、LIKE查詢，%不能在前

WHERE name LIKE "%wang%"#以上語句用不到索引运挫，可以用外部的ElasticSearch状共、Lucene等全文搜索引擎替代。

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

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

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

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

MySQL索引的原理

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

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

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

InnoDB通過主鍵聚簇數(shù)據(jù)苦丁，如果沒有定義主鍵，會選擇一個唯一的非空索引代替物臂，如果沒有這樣的索引旺拉，會隱式定義個主鍵作為聚簇索引。
下圖形象說明了聚簇索引表(InnoDB)和普通的堆組織表(MyISAM)的區(qū)別：

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

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

• 2）更新主鍵的代價很高窍霞，因為將會導(dǎo)致被更新的行移動匠题。因此，對于InnoDB表但金，我們一般定義主鍵為不可更新韭山。

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

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

解題方法

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

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

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

****問題16：以下語句是否會應(yīng)用索引：SELECT* FROM users WHERE YEAR(adddate) < 2007;****
不會吠谢，因為只要列涉及到運算，MySQL就不會使用索引诗茎。

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

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

• InnoDB索引是聚簇索引昭齐，MyISAM索引是非聚簇索引。

• InnoDB的主鍵索引的葉子節(jié)點存儲著行數(shù)據(jù)醒叁，因此主鍵索引非常高效司浪。

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

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

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

WHERE a=1 AND b=1WHERE b=1WHERE b=1 ORDER BY time DESC

以順序b,a,time建立聯(lián)合索引，CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因為最新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字段中
考點分析
這道題主要考察的是MySQL的關(guān)聯(lián)UPDATE語句
延伸考點：

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

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

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

UPDATE A,B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.idUPDATE A INNER JOIN B ON A.id=B.id SET A.c1 = B.c1,A.c2=B.c2再加上B中age>50的條件：UPDATE A,B set A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id and B.age > 50;UPDATE A INNER JOIN B ON A.id = B.id set A.c1 = B.c1,A.c2 = B.c2 WHERE B.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 * FROM A,B(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN C)#沒有任何關(guān)聯(lián)條件咖城，結(jié)果是笛卡爾積茬腿，結(jié)果集會很大，沒有意義宜雀，很少使用內(nèi)連接（INNER JOIN）SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同時符合某種條件的數(shù)據(jù)記錄的集合切平，INNER JOIN可以縮寫為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 * FROM A UNION SELECT * FROM B UNION ...

• 就是把多個結(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 * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.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è)計表如下：
team：參賽隊伍表
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é)果列：

• 主隊 結(jié)果 客對 時間

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

SELECT hostTeamID,matchResult,matchTime guestTeamID from match where matchTime between "2006-6-1" and "2006-7-1";

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

select t1.teamName,m.matchResult,t2.teamName,m.matchTime from match as m left join team as t1 on m.hostTeamID = t1.teamID, left join team t2 on m.guestTeamID=t2.guestTeamID where m.matchTime between "2006-6-1" and "2006-7-1"

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

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

• 效率 UNION 高于 UNION ALL

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

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

select * from a,b where a.tid = b.id and a.tid>50000 limit 200;

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

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

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

insert into b(a, b, c) select d,e,f from a;

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

select Student.S#,Student.Snamefrom Studentwhere S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’葉平’);

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

SELECT * FROM users WHERE id >= ((SELECT MAX(id) FROM users)-(SELECT MIN(id) FROM users)) * RAND() + (SELECT MIN(id) FROM users) LIMIT 10#此方法效率比直接用SELECT * FROM users order by rand() LIMIT 10高很多

問題26：請簡述項目中優(yōu)化SQL語句執(zhí)行效率的方法晴圾，從哪些方面颂砸，SQL語句性能如何分析？
考點分析：
這道題主要考察的是查找分析SQL語句查詢速度慢的方法
延伸考點：

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

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

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

如何查找查詢速度慢的原因
記錄慢查詢?nèi)罩舅酪Γ治霾樵內(nèi)罩菊雌啵灰苯哟蜷_慢查詢?nèi)罩具M(jìn)行分析，這樣比較浪費時間和精力知允，可以使用pt-query-digest工具進(jìn)行分析

使用show profile

set profiling=1;開啟，服務(wù)器上所有執(zhí)行語句會記錄執(zhí)行時間叙谨，存到臨時表中show profilesshow profile for query 臨時表ID

使用show status

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

show processlist

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

image

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

使用explain

分析單條SQL語句

image

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

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

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

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

• 避免犯如下SQL語句錯誤

• 查詢不需要的數(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)化長難的查詢語句

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

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

• 使用盡可能小的查詢是好的恨豁，但是有時將一個大的查詢分解為多個小的查詢是很有必要的嚣镜。

• 切分查詢

• 將一個大的查詢分為多個小的相同的查詢

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

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

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

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

• 查詢效率會有大幅提升凡涩。

• 較少冗余記錄的查詢。

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

• count(*)會忽略所有的列疹蛉，直接統(tǒng)計所有列數(shù)活箕，不要使用count(列名)

• MyISAM中，沒有任何where條件的count(*)非晨煽睿快育韩。

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

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

• 增加匯總表

• 使用緩存

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

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

• 確保GROUP BY和ORDER BY只有一個表中的列摸恍，這樣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時加ORDER BY NULL，MySQL不會再進(jìn)行文件排序媚媒。

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

優(yōu)化LIMIT分頁

• LIMIT偏移量大的時候，查詢效率較低

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

優(yōu)化UNION查詢

• UNION ALL的效率高于UNION

優(yōu)化WHERE子句

解題方法

對于此類考題栈顷，先說明如何定位低效SQL語句，然后根據(jù)SQL語句可能低效的原因做排查嵌巷，先從索引著手妨蛹，如果索引沒有問題，考慮以上幾個方面晴竞，數(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)行全表掃描垛耳，如：

select id from t where num is null可以在num上設(shè)置默認(rèn)值0栅屏，確保表中num列沒有null值飘千，然后這樣查詢：select id from t where num=

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

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

select id from t where num=10 or num=20可以這樣查詢：select id from t where num=10 union all select id from t where num=20

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

select id from t where num in(1,2,3) 對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

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

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

select id from t where num=@num可以改為強制查詢使用索引：select id from t with(index(索引名)) where num=@num

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

select id from t where num/2=100應(yīng)改為:select id from t where num=100*2

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

select id from t where substring(name,1,3)=’abc’ 苛让，name以abc開頭的id應(yīng)改為:select id from t where name like ‘a(chǎn)bc%’

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