很用心的為你寫了 9 道 MySQL 面試題

來(lái)自公眾號(hào)：Java建設(shè)者
作者：cxuan

MySQL 一直是本人很薄弱的部分庸蔼，后面會(huì)多輸出 MySQL 的文章貢獻(xiàn)給大家吹由，畢竟 MySQL 涉及到數(shù)據(jù)存儲(chǔ)、鎖朱嘴、磁盤尋道倾鲫、分頁(yè)等操作系統(tǒng)概念，而且互聯(lián)網(wǎng)對(duì) MySQL 的注重程度是不言而喻的萍嬉，后面要加緊對(duì) MySQL 的研究乌昔。寫的如果不好，還請(qǐng)大家見諒壤追。

image

非關(guān)系型數(shù)據(jù)庫(kù)和關(guān)系型數(shù)據(jù)庫(kù)區(qū)別磕道，優(yōu)勢(shì)比較

image

非關(guān)系型數(shù)據(jù)庫(kù)（感覺翻譯不是很準(zhǔn)確）稱為 NoSQL，也就是 Not Only SQL行冰，不僅僅是 SQL溺蕉。非關(guān)系型數(shù)據(jù)庫(kù)不需要寫一些復(fù)雜的 SQL 語(yǔ)句伶丐，其內(nèi)部存儲(chǔ)方式是以 key-value 的形式存在可以把它想象成電話本的形式，每個(gè)人名（key）對(duì)應(yīng)電話（value）疯特。常見的非關(guān)系型數(shù)據(jù)庫(kù)主要有 Hbase哗魂、Redis、MongoDB 等漓雅。非關(guān)系型數(shù)據(jù)庫(kù)不需要經(jīng)過(guò) SQL 的重重解析录别，所以性能很高；非關(guān)系型數(shù)據(jù)庫(kù)的可擴(kuò)展性比較強(qiáng)邻吞，數(shù)據(jù)之間沒有耦合性组题，遇見需要新加字段的需求，就直接增加一個(gè) key-value 鍵值對(duì)即可抱冷。

image

關(guān)系型數(shù)據(jù)庫(kù)以表格的形式存在崔列，以行和列的形式存取數(shù)據(jù)，關(guān)系型數(shù)據(jù)庫(kù)這一系列的行和列被稱為表旺遮，無(wú)數(shù)張表組成了數(shù)據(jù)庫(kù)峻呕，常見的關(guān)系型數(shù)據(jù)庫(kù)有 Oracle、DB2趣效、Microsoft SQL Server、MySQL等猪贪。關(guān)系型數(shù)據(jù)庫(kù)能夠支持復(fù)雜的 SQL 查詢跷敬，能夠體現(xiàn)出數(shù)據(jù)之間、表之間的關(guān)聯(lián)關(guān)系热押；關(guān)系型數(shù)據(jù)庫(kù)也支持事務(wù)西傀，便于提交或者回滾。

它們之間的劣勢(shì)都是基于對(duì)方的優(yōu)勢(shì)來(lái)滿足的桶癣。

MySQL 事務(wù)四大特性

一說(shuō)到 MySQL 事務(wù)拥褂，你肯定能想起來(lái)四大特性：原子性、一致性牙寞、隔離性饺鹃、持久性，下面再對(duì)這事務(wù)的四大特性做一個(gè)描述

  • 原子性(Atomicity): 原子性指的就是 MySQL 中的包含事務(wù)的操作要么全部成功间雀、要么全部失敗回滾悔详，因此事務(wù)的操作如果成功就必須要全部應(yīng)用到數(shù)據(jù)庫(kù)，如果操作失敗則不能對(duì)數(shù)據(jù)庫(kù)有任何影響惹挟。

“

這里涉及到一個(gè)概念茄螃，什么是 MySQL 中的事務(wù)？

事務(wù)是一組操作连锯，組成這組操作的各個(gè)單元归苍，要不全都成功要不全都失敗用狱，這個(gè)特性就是事務(wù)。

在 MySQL 中拼弃，事務(wù)是在引擎層實(shí)現(xiàn)的夏伊，只有使用 innodb 引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)。

  • 一致性(Consistency)：一致性指的是一個(gè)事務(wù)在執(zhí)行前后其狀態(tài)一致肴敛。比如 A 和 B 加起來(lái)的錢一共是 1000 元署海，那么不管 A 和 B 之間如何轉(zhuǎn)賬，轉(zhuǎn)多少次，事務(wù)結(jié)束后兩個(gè)用戶的錢加起來(lái)還得是 1000，這就是事務(wù)的一致性荒勇。

  • 持久性(Durability): 持久性指的是一旦事務(wù)提交裹刮，那么發(fā)生的改變就是永久性的，即使數(shù)據(jù)庫(kù)遇到特殊情況比如故障的時(shí)候也不會(huì)產(chǎn)生干擾纳击。

  • 隔離性(Isolation)：隔離性需要重點(diǎn)說(shuō)一下，當(dāng)多個(gè)事務(wù)同時(shí)進(jìn)行時(shí)，就有可能出現(xiàn)臟讀(dirty read)研底、不可重復(fù)讀(non-repeatable read)、幻讀(phantom read) 的情況透罢，為了解決這些并發(fā)問題榜晦，提出了隔離性的概念。

“

臟讀：事務(wù) A 讀取了事務(wù) B 更新后的數(shù)據(jù)羽圃，但是事務(wù) B 沒有提交乾胶，然后事務(wù) B 執(zhí)行回滾操作，那么事務(wù) A 讀到的數(shù)據(jù)就是臟數(shù)據(jù)

不可重復(fù)讀：事務(wù) A 進(jìn)行多次讀取操作朽寞，事務(wù) B 在事務(wù) A 多次讀取的過(guò)程中執(zhí)行更新操作并提交识窿，提交后事務(wù) A 讀到的數(shù)據(jù)不一致。

幻讀：事務(wù) A 將數(shù)據(jù)庫(kù)中所有學(xué)生的成績(jī)由 A -> B脑融，此時(shí)事務(wù) B 手動(dòng)插入了一條成績(jī)?yōu)?A 的記錄喻频，在事務(wù) A 更改完畢后，發(fā)現(xiàn)還有一條記錄沒有修改肘迎，那么這種情況就叫做出現(xiàn)了幻讀甥温。

SQL的隔離級(jí)別有四種，它們分別是讀未提交(read uncommitted)妓布、讀已提交(read committed)窿侈、可重復(fù)讀(repetable read) 和 串行化(serializable)。下面分別來(lái)解釋一下秋茫。

讀未提交：讀未提交指的是一個(gè)事務(wù)在提交之前史简，它所做的修改就能夠被其他事務(wù)所看到。

讀已提交：讀已提交指的是一個(gè)事務(wù)在提交之后，它所做的變更才能夠讓其他事務(wù)看到圆兵。

可重復(fù)讀：可重復(fù)讀指的是一個(gè)事務(wù)在執(zhí)行的過(guò)程中跺讯，看到的數(shù)據(jù)是和啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的。未提交的變更對(duì)其他事務(wù)不可見殉农。

串行化：顧名思義是對(duì)于同一行記錄刀脏，寫會(huì)加寫鎖，讀會(huì)加讀鎖超凳。當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候愈污，后訪問的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行轮傍。

這四個(gè)隔離級(jí)別可以解決臟讀暂雹、不可重復(fù)讀、幻象讀這三類問題创夜『脊颍總結(jié)如下

image

其中隔離級(jí)別由低到高是：讀未提交 < 讀已提交 < 可重復(fù)讀 < 串行化

隔離級(jí)別越高，越能夠保證數(shù)據(jù)的完整性和一致性驰吓，但是對(duì)并發(fā)的性能影響越大涧尿。大多數(shù)數(shù)據(jù)庫(kù)的默認(rèn)級(jí)別是讀已提交(Read committed)，比如 Sql Server檬贰、Oracle 姑廉，但是 MySQL 的默認(rèn)隔離級(jí)別是 可重復(fù)讀(repeatable-read)。

MySQL 常見存儲(chǔ)引擎的區(qū)別

MySQL 常見的存儲(chǔ)引擎翁涤，可以使用

SHOW ENGINES

命令桥言，來(lái)列出所有的存儲(chǔ)引擎

image

可以看到，InnoDB 是 MySQL 默認(rèn)支持的存儲(chǔ)引擎迷雪，支持事務(wù)、行級(jí)鎖定和外鍵虫蝶。

MyISAM 存儲(chǔ)引擎的特點(diǎn)

在 5.1 版本之前章咧，MyISAM 是 MySQL 的默認(rèn)存儲(chǔ)引擎，MyISAM 并發(fā)性比較差能真，使用的場(chǎng)景比較少赁严，主要特點(diǎn)是

  • 不支持事務(wù)操作，ACID 的特性也就不存在了粉铐，這一設(shè)計(jì)是為了性能和效率考慮的疼约。

  • 不支持外鍵操作，如果強(qiáng)行增加外鍵蝙泼，MySQL 不會(huì)報(bào)錯(cuò)程剥，只不過(guò)外鍵不起作用。

  • MyISAM 默認(rèn)的鎖粒度是表級(jí)鎖汤踏，所以并發(fā)性能比較差织鲸，加鎖比較快舔腾，鎖沖突比較少，不太容易發(fā)生死鎖的情況搂擦。

  • MyISAM 會(huì)在磁盤上存儲(chǔ)三個(gè)文件稳诚，文件名和表名相同，擴(kuò)展名分別是 .frm(存儲(chǔ)表定義)瀑踢、.MYD(MYData,存儲(chǔ)數(shù)據(jù))扳还、MYI(MyIndex,存儲(chǔ)索引)。這里需要特別注意的是 MyISAM 只緩存索引文件橱夭，并不緩存數(shù)據(jù)文件氨距。

  • MyISAM 支持的索引類型有 全局索引(Full-Text)、B-Tree 索引徘钥、R-Tree 索引

    Full-Text 索引：它的出現(xiàn)是為了解決針對(duì)文本的模糊查詢效率較低的問題衔蹲。

    B-Tree 索引：所有的索引節(jié)點(diǎn)都按照平衡樹的數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)，所有的索引數(shù)據(jù)節(jié)點(diǎn)都在葉節(jié)點(diǎn)

    R-Tree索引：它的存儲(chǔ)方式和 B-Tree 索引有一些區(qū)別呈础，主要設(shè)計(jì)用于存儲(chǔ)空間和多維數(shù)據(jù)的字段做索引,目前的 MySQL 版本僅支持 geometry 類型的字段作索引舆驶，相對(duì)于 BTREE，RTREE 的優(yōu)勢(shì)在于范圍查找而钞。

  • 數(shù)據(jù)庫(kù)所在主機(jī)如果宕機(jī)沙廉，MyISAM 的數(shù)據(jù)文件容易損壞，而且難以恢復(fù)臼节。

  • 增刪改查性能方面：SELECT 性能較高撬陵，適用于查詢較多的情況

InnoDB 存儲(chǔ)引擎的特點(diǎn)

自從 MySQL 5.1 之后，默認(rèn)的存儲(chǔ)引擎變成了 InnoDB 存儲(chǔ)引擎网缝，相對(duì)于 MyISAM巨税，InnoDB 存儲(chǔ)引擎有了較大的改變，它的主要特點(diǎn)是

  • 支持事務(wù)操作粉臊，具有事務(wù) ACID 隔離特性草添，默認(rèn)的隔離級(jí)別是可重復(fù)讀(repetable-read)、通過(guò)MVCC（并發(fā)版本控制）來(lái)實(shí)現(xiàn)的扼仲。能夠解決臟讀和不可重復(fù)讀的問題远寸。
  • InnoDB 支持外鍵操作。
  • InnoDB 默認(rèn)的鎖粒度行級(jí)鎖屠凶，并發(fā)性能比較好驰后，會(huì)發(fā)生死鎖的情況。
  • 和 MyISAM 一樣的是矗愧，InnoDB 存儲(chǔ)引擎也有 .frm文件存儲(chǔ)表結(jié)構(gòu) 定義灶芝，但是不同的是，InnoDB 的表數(shù)據(jù)與索引數(shù)據(jù)是存儲(chǔ)在一起的，都位于 B+ 數(shù)的葉子節(jié)點(diǎn)上监署，而 MyISAM 的表數(shù)據(jù)和索引數(shù)據(jù)是分開的颤专。
  • InnoDB 有安全的日志文件，這個(gè)日志文件用于恢復(fù)因數(shù)據(jù)庫(kù)崩潰或其他情況導(dǎo)致的數(shù)據(jù)丟失問題钠乏，保證數(shù)據(jù)的一致性栖秕。
  • InnoDB 和 MyISAM 支持的索引類型相同，但具體實(shí)現(xiàn)因?yàn)槲募Y(jié)構(gòu)的不同有很大差異晓避。
  • 增刪改查性能方面簇捍，如果執(zhí)行大量的增刪改操作，推薦使用 InnoDB 存儲(chǔ)引擎俏拱，它在刪除操作時(shí)是對(duì)行刪除暑塑，不會(huì)重建表。

MyISAM 和 InnoDB 存儲(chǔ)引擎的對(duì)比

  • 鎖粒度方面：由于鎖粒度不同锅必，InnoDB 比 MyISAM 支持更高的并發(fā)事格；InnoDB 的鎖粒度為行鎖、MyISAM 的鎖粒度為表鎖搞隐、行鎖需要對(duì)每一行進(jìn)行加鎖驹愚，所以鎖的開銷更大，但是能解決臟讀和不可重復(fù)讀的問題劣纲，相對(duì)來(lái)說(shuō)也更容易發(fā)生死鎖
  • 可恢復(fù)性上：由于 InnoDB 是有事務(wù)日志的逢捺，所以在產(chǎn)生由于數(shù)據(jù)庫(kù)崩潰等條件后，可以根據(jù)日志文件進(jìn)行恢復(fù)癞季。而 MyISAM 則沒有事務(wù)日志劫瞳。
  • 查詢性能上：MyISAM 要優(yōu)于 InnoDB，因?yàn)?InnoDB 在查詢過(guò)程中绷柒，是需要維護(hù)數(shù)據(jù)緩存志于，而且查詢過(guò)程是先定位到行所在的數(shù)據(jù)塊，然后在從數(shù)據(jù)塊中定位到要查找的行废睦；而 MyISAM 可以直接定位到數(shù)據(jù)所在的內(nèi)存地址伺绽，可以直接找到數(shù)據(jù)。
  • 表結(jié)構(gòu)文件上：MyISAM 的表結(jié)構(gòu)文件包括：.frm(表結(jié)構(gòu)定義),.MYI(索引),.MYD(數(shù)據(jù))郊楣；而 InnoDB 的表數(shù)據(jù)文件為:.ibd和.frm(表結(jié)構(gòu)定義)憔恳；

MySQL 基礎(chǔ)架構(gòu)

這道題應(yīng)該從 MySQL 架構(gòu)來(lái)理解瓤荔，我們可以把 MySQL 拆解成幾個(gè)零件净蚤，如下圖所示

image

大致上來(lái)說(shuō)，MySQL 可以分為 Server層和 存儲(chǔ)引擎層输硝。

Server 層包括連接器今瀑、查詢緩存、分析器、優(yōu)化器橘荠、執(zhí)行器屿附，包括大多數(shù) MySQL 中的核心功能，所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)哥童，包括 存儲(chǔ)過(guò)程挺份、觸發(fā)器、視圖等贮懈。

存儲(chǔ)引擎層包括 MySQL 常見的存儲(chǔ)引擎匀泊，包括 MyISAM、InnoDB 和 Memory 等朵你，最常用的是 InnoDB各聘，也是現(xiàn)在 MySQL 的默認(rèn)存儲(chǔ)引擎。存儲(chǔ)引擎也可以在創(chuàng)建表的時(shí)候手動(dòng)指定抡医，比如下面

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

然后我們就可以探討 MySQL 的執(zhí)行過(guò)程了

連接器

首先需要在 MySQL 客戶端登陸才能使用躲因，所以需要一個(gè)連接器來(lái)連接用戶和 MySQL 數(shù)據(jù)庫(kù)，我們一般是使用

mysql -u 用戶名 -p 密碼

來(lái)進(jìn)行 MySQL 登陸忌傻，和服務(wù)端建立連接大脉。在完成 TCP 握手 后，連接器會(huì)根據(jù)你輸入的用戶名和密碼驗(yàn)證你的登錄身份芯勘。如果用戶名或者密碼錯(cuò)誤箱靴，MySQL 就會(huì)提示 Access denied for user，來(lái)結(jié)束執(zhí)行荷愕。如果登錄成功后衡怀，MySQL 會(huì)根據(jù)權(quán)限表中的記錄來(lái)判定你的權(quán)限。

查詢緩存

連接完成后安疗，你就可以執(zhí)行 SQL 語(yǔ)句了抛杨，這行邏輯就會(huì)來(lái)到第二步：查詢緩存。

MySQL 在得到一個(gè)執(zhí)行請(qǐng)求后荐类，會(huì)首先去 查詢緩存 中查找怖现，是否執(zhí)行過(guò)這條 SQL 語(yǔ)句，之前執(zhí)行過(guò)的語(yǔ)句以及結(jié)果會(huì)以 key-value 對(duì)的形式玉罐，被直接放在內(nèi)存中屈嗤。key 是查詢語(yǔ)句，value 是查詢的結(jié)果吊输。如果通過(guò) key 能夠查找到這條 SQL 語(yǔ)句饶号，就直接返回 SQL 的執(zhí)行結(jié)果。

如果語(yǔ)句不在查詢緩存中季蚂，就會(huì)繼續(xù)后面的執(zhí)行階段茫船。執(zhí)行完成后琅束，執(zhí)行結(jié)果就會(huì)被放入查詢緩存中∷闾福可以看到涩禀，如果查詢命中緩存，MySQL 不需要執(zhí)行后面的復(fù)雜操作然眼，就可以直接返回結(jié)果艾船，效率會(huì)很高。

image

但是查詢緩存不建議使用

為什么呢高每？因?yàn)橹灰?MySQL 中對(duì)某一張表執(zhí)行了更新操作丽声，那么所有的查詢緩存就會(huì)失效，對(duì)于更新頻繁的數(shù)據(jù)庫(kù)來(lái)說(shuō)觉义，查詢緩存的命中率很低雁社。

分析器

如果沒有命中查詢，就開始執(zhí)行真正的 SQL 語(yǔ)句晒骇。

  • 首先霉撵，MySQL 會(huì)根據(jù)你寫的 SQL 語(yǔ)句進(jìn)行解析，分析器會(huì)先做 詞法分析洪囤，你寫的 SQL 就是由多個(gè)字符串和空格組成的一條 SQL 語(yǔ)句徒坡，MySQL 需要識(shí)別出里面的字符串是什么，代表什么瘤缩。
  • 然后進(jìn)行 語(yǔ)法分析喇完，根據(jù)詞法分析的結(jié)果， 語(yǔ)法分析器會(huì)根據(jù)語(yǔ)法規(guī)則剥啤，判斷你輸入的這個(gè) SQL 語(yǔ)句是否滿足 MySQL 語(yǔ)法锦溪。如果 SQL 語(yǔ)句不正確，就會(huì)提示 You have an error in your SQL syntax

優(yōu)化器

經(jīng)過(guò)分析器的詞法分析和語(yǔ)法分析后府怯，你這條 SQL 就合法了刻诊，MySQL 就知道你要做什么了。但是在執(zhí)行前牺丙，還需要進(jìn)行優(yōu)化器的處理则涯，優(yōu)化器會(huì)判斷你使用了哪種索引，使用了何種連接冲簿，優(yōu)化器的作用就是確定效率最高的執(zhí)行方案粟判。

執(zhí)行器

MySQL 通過(guò)分析器知道了你的 SQL 語(yǔ)句是否合法，你想要做什么操作峦剔，通過(guò)優(yōu)化器知道了該怎么做效率最高档礁，然后就進(jìn)入了執(zhí)行階段，開始執(zhí)行這條 SQL 語(yǔ)句

在執(zhí)行階段羊异，MySQL 首先會(huì)判斷你有沒有執(zhí)行這條語(yǔ)句的權(quán)限事秀，沒有權(quán)限的話，就會(huì)返回沒有權(quán)限的錯(cuò)誤野舶。如果有權(quán)限易迹，就打開表繼續(xù)執(zhí)行。打開表的時(shí)候平道，執(zhí)行器就會(huì)根據(jù)表的引擎定義睹欲，去使用這個(gè)引擎提供的接口。對(duì)于有索引的表一屋，執(zhí)行的邏輯也差不多窘疮。

至此，MySQL 對(duì)于一條語(yǔ)句的執(zhí)行過(guò)程也就完成了冀墨。

SQL 的執(zhí)行順序

我們?cè)诰帉懸粋€(gè)查詢語(yǔ)句的時(shí)候

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

它的執(zhí)行順序你知道嗎闸衫？這道題就給你一個(gè)回答。

FROM 連接

首先诽嘉，對(duì) SELECT 語(yǔ)句執(zhí)行查詢時(shí)蔚出，對(duì)FROM 關(guān)鍵字兩邊的表執(zhí)行連接，會(huì)形成笛卡爾積虫腋，這時(shí)候會(huì)產(chǎn)生一個(gè)虛表VT1(virtual table)

“

首先先來(lái)解釋一下什么是笛卡爾積

現(xiàn)在我們有兩個(gè)集合 A = {0,1} , B = {2,3,4}

那么骄酗，集合 A * B 得到的結(jié)果就是

A * B = {(0,2)、(1,2)悦冀、(0,3)趋翻、(1,3)、(0,4)盒蟆、(1,4)};

B * A = {(2,0)踏烙、{2,1}、{3,0}历等、{3,1}宙帝、{4,0}、(4,1)};

上面 A * B 和 B * A 的結(jié)果就可以稱為兩個(gè)集合相乘的 笛卡爾積

我們可以得出結(jié)論募闲，A 集合和 B 集合相乘步脓，包含了集合 A 中的元素和集合 B 中元素之和，也就是 A 元素的個(gè)數(shù) * B 元素的個(gè)數(shù)

再來(lái)解釋一下什么是虛表

“

在 MySQL 中浩螺，有三種類型的表

一種是永久表靴患，永久表就是創(chuàng)建以后用來(lái)長(zhǎng)期保存數(shù)據(jù)的表

一種是臨時(shí)表，臨時(shí)表也有兩類要出，一種是和永久表一樣鸳君，只保存臨時(shí)數(shù)據(jù)，但是能夠長(zhǎng)久存在的患蹂；還有一種是臨時(shí)創(chuàng)建的或颊，SQL 語(yǔ)句執(zhí)行完成就會(huì)刪除砸紊。

一種是虛表，虛表其實(shí)就是視圖囱挑，數(shù)據(jù)可能會(huì)來(lái)自多張表的執(zhí)行結(jié)果醉顽。

ON 過(guò)濾

然后對(duì) FROM 連接的結(jié)果進(jìn)行 ON 篩選，創(chuàng)建 VT2平挑，把符合記錄的條件存在 VT2 中游添。

JOIN 連接

第三步，如果是 OUTER JOIN(left join通熄、right join) 唆涝，那么這一步就將添加外部行，如果是 left join 就把 ON 過(guò)濾條件的左表添加進(jìn)來(lái)唇辨，如果是 right join 廊酣，就把右表添加進(jìn)來(lái)，從而生成新的虛擬表 VT3赏枚。

WHERE 過(guò)濾

第四步啰扛，是執(zhí)行 WHERE 過(guò)濾器，對(duì)上一步生產(chǎn)的虛擬表引用 WHERE 篩選嗡贺，生成虛擬表 VT4隐解。

WHERE 和 ON 的區(qū)別

  • 如果有外部列，ON 針對(duì)過(guò)濾的是關(guān)聯(lián)表诫睬，主表(保留表)會(huì)返回所有的列;
  • 如果沒有添加外部列煞茫，兩者的效果是一樣的;

應(yīng)用

  • 對(duì)主表的過(guò)濾應(yīng)該使用 WHERE;
  • 對(duì)于關(guān)聯(lián)表，先條件查詢后連接則用 ON摄凡，先連接后條件查詢則用 WHERE;

GROUP BY

根據(jù) group by 字句中的列续徽，會(huì)對(duì) VT4 中的記錄進(jìn)行分組操作，產(chǎn)生虛擬機(jī)表 VT5亲澡。果應(yīng)用了group by钦扭，那么后面的所有步驟都只能得到的 VT5 的列或者是聚合函數(shù)（count、sum床绪、avg等）客情。

HAVING

緊跟著 GROUP BY 字句后面的是 HAVING，使用 HAVING 過(guò)濾癞己，會(huì)把符合條件的放在 VT6

SELECT

第七步才會(huì)執(zhí)行 SELECT 語(yǔ)句膀斋，將 VT6 中的結(jié)果按照 SELECT 進(jìn)行刷選，生成 VT7

DISTINCT

在第八步中痹雅，會(huì)對(duì) TV7 生成的記錄進(jìn)行去重操作仰担，生成 VT8。事實(shí)上如果應(yīng)用了 group by 子句那么 distinct 是多余的绩社，原因同樣在于摔蓝，分組的時(shí)候是將列中唯一的值分成一組赂苗，同時(shí)只為每一組返回一行記錄，那么所以的記錄都將是不相同的贮尉。

ORDER BY

應(yīng)用 order by 子句拌滋。按照 order_by_condition 排序 VT8，此時(shí)返回的一個(gè)游標(biāo)绘盟，而不是虛擬表。sql 是基于集合的理論的悯仙，集合不會(huì)預(yù)先對(duì)他的行排序龄毡，它只是成員的邏輯集合，成員的順序是無(wú)關(guān)緊要的锡垄。

SQL 語(yǔ)句執(zhí)行的過(guò)程如下

image

什么是臨時(shí)表沦零，何時(shí)刪除臨時(shí)表

什么是臨時(shí)表？MySQL 在執(zhí)行 SQL 語(yǔ)句的過(guò)程中货岭，通常會(huì)臨時(shí)創(chuàng)建一些存儲(chǔ)中間結(jié)果集的表路操，臨時(shí)表只對(duì)當(dāng)前連接可見，在連接關(guān)閉時(shí)千贯，臨時(shí)表會(huì)被刪除并釋放所有表空間屯仗。

臨時(shí)表分為兩種：一種是內(nèi)存臨時(shí)表，一種是磁盤臨時(shí)表搔谴，什么區(qū)別呢魁袜？?jī)?nèi)存臨時(shí)表使用的是 MEMORY 存儲(chǔ)引擎，而臨時(shí)表采用的是 MyISAM 存儲(chǔ)引擎敦第。

“

MEMORY 存儲(chǔ)引擎：memory 是 MySQL 中一類特殊的存儲(chǔ)引擎峰弹，它使用存儲(chǔ)在內(nèi)容中的內(nèi)容來(lái)創(chuàng)建表，而且數(shù)據(jù)全部放在內(nèi)存中芜果。每個(gè)基于 MEMORY 存儲(chǔ)引擎的表實(shí)際對(duì)應(yīng)一個(gè)磁盤文件鞠呈。該文件的文件名與表名相同，類型為 frm 類型右钾。而其數(shù)據(jù)文件蚁吝，都是存儲(chǔ)在內(nèi)存中，這樣有利于數(shù)據(jù)的快速處理舀射，提高整個(gè)表的效率灭将。MEMORY 用到的很少，因?yàn)樗前褦?shù)據(jù)存到內(nèi)存中后控，如果內(nèi)存出現(xiàn)異常就會(huì)影響數(shù)據(jù)庙曙。如果重啟或者關(guān)機(jī)，所有數(shù)據(jù)都會(huì)消失浩淘。因此捌朴，基于 MEMORY 的表的生命周期很短吴攒，一般是一次性的。

MySQL 會(huì)在下面這幾種情況產(chǎn)生臨時(shí)表

  • 使用 UNION 查詢：UNION 有兩種砂蔽，一種是UNION 洼怔，一種是 UNION ALL ，它們都用于聯(lián)合查詢左驾；區(qū)別是 使用 UNION 會(huì)去掉兩個(gè)表中的重復(fù)數(shù)據(jù)镣隶，相當(dāng)于對(duì)結(jié)果集做了一下去重(distinct)。使用 UNION ALL诡右，則不會(huì)排重安岂，返回所有的行。使用 UNION 查詢會(huì)產(chǎn)生臨時(shí)表帆吻。

  • 使用 TEMPTABLE 算法或者是 UNION 查詢中的視圖域那。TEMPTABLE 算法是一種創(chuàng)建臨時(shí)表的算法，它是將結(jié)果放置到臨時(shí)表中猜煮，意味這要 MySQL 要先創(chuàng)建好一個(gè)臨時(shí)表次员，然后將結(jié)果放到臨時(shí)表中去，然后再使用這個(gè)臨時(shí)表進(jìn)行相應(yīng)的查詢王带。

  • ORDER BY 和 GROUP BY 的子句不一樣時(shí)也會(huì)產(chǎn)生臨時(shí)表淑蔚。

  • DISTINCT 查詢并且加上 ORDER BY 時(shí)；

  • SQL 用到 SQL_SMALL_RESULT 選項(xiàng)時(shí)愕撰；如果查詢結(jié)果比較小的時(shí)候束倍，可以加上 SQL_SMALL_RESULT 來(lái)優(yōu)化，產(chǎn)生臨時(shí)表

  • FROM 中的子查詢盟戏；

  • EXPLAIN 查看執(zhí)行計(jì)劃結(jié)果的 Extra 列中绪妹，如果使用 Using Temporary 就表示會(huì)用到臨時(shí)表。

MySQL 常見索引類型

索引是存儲(chǔ)在一張表中特定列上的數(shù)據(jù)結(jié)構(gòu)柿究，索引是在列上創(chuàng)建的邮旷。并且，索引是一種數(shù)據(jù)結(jié)構(gòu)蝇摸。

在 MySQL 中婶肩，主要有下面這幾種索引

  • 全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引貌夕，它的出現(xiàn)是為了解決針對(duì)文本的模糊查詢效率較低的問題律歼。
  • 哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 鍵值對(duì)的數(shù)據(jù)結(jié)構(gòu)，很適合作為索引啡专。HASH 索引具有一次定位的好處险毁，不需要像樹那樣逐個(gè)節(jié)點(diǎn)查找，但是這種查找適合應(yīng)用于查找單個(gè)鍵的情況，對(duì)于范圍查找畔况，HASH 索引的性能就會(huì)很低鲸鹦。
  • B-Tree 索引：B 就是 Balance 的意思，BTree 是一種平衡樹跷跪，它有很多變種馋嗜，最常見的就是 B+ Tree，它被 MySQL 廣泛使用吵瞻。
  • R-Tree 索引：R-Tree 在 MySQL 很少使用葛菇，僅支持 geometry 數(shù)據(jù)類型，支持該類型的存儲(chǔ)引擎只有MyISAM橡羞、BDb眯停、InnoDb、NDb尉姨、Archive幾種庵朝，相對(duì)于 B-Tree 來(lái)說(shuō)吗冤，R-Tree 的優(yōu)勢(shì)在于范圍查找又厉。

varchar 和 char 的區(qū)別和使用場(chǎng)景

MySQL 中沒有 nvarchar 數(shù)據(jù)類型，所以直接比較的是 varchar 和 char 的區(qū)別

char ：表示的是定長(zhǎng)的字符串椎瘟，當(dāng)你輸入小于指定的數(shù)目覆致，比如你指定的數(shù)目是 char(6)，當(dāng)你輸入小于 6 個(gè)字符的時(shí)候肺蔚，char 會(huì)在你最后一個(gè)字符后面補(bǔ)空值煌妈。當(dāng)你輸入超過(guò)指定允許最大長(zhǎng)度后，MySQL 會(huì)報(bào)錯(cuò)

image

varchar：varchar 指的是長(zhǎng)度為 n 個(gè)字節(jié)的可變長(zhǎng)度宣羊，并且是非Unicode的字符數(shù)據(jù)璧诵。n 值是介于 1 - 8000 之間的數(shù)值。存儲(chǔ)大小為實(shí)際大小仇冯。

“

Unicode 是一種字符編碼方案之宿，它為每種語(yǔ)言中的每個(gè)字符都設(shè)定了統(tǒng)一唯一的二進(jìn)制編碼，以實(shí)現(xiàn)跨語(yǔ)言苛坚、跨平臺(tái)進(jìn)行文本轉(zhuǎn)換比被、處理的要求

使用 char 存儲(chǔ)定長(zhǎng)的數(shù)據(jù)非常方便、char 檢索效率高泼舱，無(wú)論你存儲(chǔ)的數(shù)據(jù)是否到了 10 個(gè)字節(jié)等缀，都要去占用 10 字節(jié)的空間

使用 varchar 可以存儲(chǔ)變長(zhǎng)的數(shù)據(jù)，但存儲(chǔ)效率沒有 char 高娇昙。

什么是 內(nèi)連接尺迂、外連接、交叉連接、笛卡爾積

連接的方式主要有三種：外連接枪狂、內(nèi)鏈接危喉、交叉連接

  • 外連接(OUTER JOIN)：外連接分為三種，分別是左外連接(LEFT OUTER JOIN 或 LEFT JOIN)州疾、右外連接(RIGHT OUTER JOIN 或 RIGHT JOIN) 辜限、全外連接(FULL OUTER JOIN 或 FULL JOIN)

    左外連接：又稱為左連接，這種連接方式會(huì)顯示左表不符合條件的數(shù)據(jù)行严蓖，右邊不符合條件的數(shù)據(jù)行直接顯示 NULL

image
右外連接：也被稱為右連接薄嫡，他與左連接相對(duì)，這種連接方式會(huì)顯示右表不符合條件的數(shù)據(jù)行颗胡，左表不符合條件的數(shù)據(jù)行直接顯示 NULL
image

** MySQL 暫不支持全外連接**

  • 內(nèi)連接(INNER JOIN)：結(jié)合兩個(gè)表中相同的字段毫深，返回關(guān)聯(lián)字段相符的記錄。
image
  • 笛卡爾積(Cartesian product)：我在上面提到了笛卡爾積毒姨，為了方便哑蔫，下面再列出來(lái)一下。

“

現(xiàn)在我們有兩個(gè)集合 A = {0,1} , B = {2,3,4}

那么弧呐，集合 A * B 得到的結(jié)果就是

A * B = {(0,2)闸迷、(1,2)、(0,3)俘枫、(1,3)腥沽、(0,4)、(1,4)};

B * A = {(2,0)鸠蚪、{2,1}今阳、{3,0}、{3,1}茅信、{4,0}盾舌、(4,1)};

上面 A * B 和 B * A 的結(jié)果就可以稱為兩個(gè)集合相乘的 笛卡爾積

我們可以得出結(jié)論，A 集合和 B 集合相乘蘸鲸，包含了集合 A 中的元素和集合 B 中元素之和妖谴，也就是 A 元素的個(gè)數(shù) * B 元素的個(gè)數(shù)

  • 交叉連接的原文是Cross join ，就是笛卡爾積在 SQL 中的實(shí)現(xiàn)棚贾，SQL中使用關(guān)鍵字CROSS JOIN來(lái)表示交叉連接窖维，在交叉連接中，隨便增加一個(gè)表的字段妙痹，都會(huì)對(duì)結(jié)果造成很大的影響铸史。

    SELECT * FROM t_Class a CROSS JOIN t_Student b WHERE a.classid=b.classid

    或者不用 CROSS JOIN，直接用 FROM 也能表示交叉連接的效果

    SELECT * FROM t_Class a ,t_Student b WHERE a.classid=b.classid

    如果表中字段比較多怯伊，不適宜用交叉連接琳轿，交叉連接的效率比較差。

  • 全連接：全連接也就是 full join，MySQL 中不支持全連接崭篡，但是可以使用其他連接查詢來(lái)模擬全連接挪哄，可以使用 UNION 和 UNION ALL 進(jìn)行模擬。例如

    (select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )



或 
(select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB )琉闪；

    使用 UNION 和 UNION ALL 的注意事項(xiàng)

    “

    通過(guò) union 連接的 SQL 分別單獨(dú)取出的列數(shù)必須相同

    使用 union 時(shí)迹炼，多個(gè)相等的行將會(huì)被合并，由于合并比較耗時(shí)颠毙，一般不直接使用 union 進(jìn)行合并斯入，而是通常采用 union all 進(jìn)行合并

談?wù)?SQL 優(yōu)化的經(jīng)驗(yàn)

  • 查詢語(yǔ)句無(wú)論是使用哪種判斷條件 等于、小于蛀蜜、大于刻两， WHERE 左側(cè)的條件查詢字段不要使用函數(shù)或者表達(dá)式
  • 使用 EXPLAIN 命令優(yōu)化你的 SELECT 查詢，對(duì)于復(fù)雜滴某、效率低的 sql 語(yǔ)句磅摹，我們通常是使用 explain sql 來(lái)分析這條 sql 語(yǔ)句，這樣方便我們分析霎奢，進(jìn)行優(yōu)化户誓。
  • 當(dāng)你的 SELECT 查詢語(yǔ)句只需要使用一條記錄時(shí)，要使用 LIMIT 1
  • 不要直接使用 SELECT *椰憋，而應(yīng)該使用具體需要查詢的表字段厅克，因?yàn)槭褂?EXPLAIN 進(jìn)行分析時(shí)赔退，SELECT * 使用的是全表掃描橙依，也就是 type = all。
  • 為每一張表設(shè)置一個(gè) ID 屬性
  • 避免在 WHERE 字句中對(duì)字段進(jìn)行 NULL 判斷
  • 避免在 WHERE 中使用 != 或 <> 操作符
  • 使用 BETWEEN AND 替代 IN
  • 為搜索字段創(chuàng)建索引
  • 選擇正確的存儲(chǔ)引擎硕旗，InnoDB 窗骑、MyISAM 、MEMORY 等
  • 使用 LIKE %abc% 不會(huì)走索引漆枚，而使用 LIKE abc% 會(huì)走索引
  • 對(duì)于枚舉類型的字段(即有固定羅列值的字段)创译，建議使用ENUM而不是VARCHAR，如性別墙基、星期软族、類型、類別等
  • 拆分大的 DELETE 或 INSERT 語(yǔ)句
  • 選擇合適的字段類型残制，選擇標(biāo)準(zhǔn)是 盡可能小立砸、盡可能定長(zhǎng)、盡可能使用整數(shù)初茶。
  • 字段設(shè)計(jì)盡可能使用 NOT NULL
  • 進(jìn)行水平切割或者垂直分割

“

水平分割：通過(guò)建立結(jié)構(gòu)相同的幾張表分別存儲(chǔ)數(shù)據(jù)

垂直分割：將經(jīng)常一起使用的字段放在一個(gè)單獨(dú)的表中颗祝，分割后的表記錄之間是一一對(duì)應(yīng)關(guān)系。

文章參考：

https://www.cnblogs.com/sharpest/p/10390035.html

https://blog.csdn.net/yl2isoft/article/details/17205413

https://www.cnblogs.com/jinianjun/archive/2011/11/08/2240525.html

https://www.cnblogs.com/huihuixi/p/12155165.html

https://www.php.cn/faq/418056.html

https://blog.csdn.net/w516162189/article/details/78914035

https://baike.baidu.com/item/聚集索引/11041381?fr=aladdin

https://blog.csdn.net/riemann_/article/details/90324846

https://blog.csdn.net/qq_39101581/article/details/82461076

https://blog.csdn.net/csdn_hklm/article/details/78394412

https://zhidao.baidu.com/question/307471035920165604.html

https://www.zhihu.com/question/24225007

https://baike.baidu.com/item/索引/5716853

https://www.cnblogs.com/ghostwu/p/8544333.html

https://www.cnblogs.com/yuxiuyan/p/6511837.html

https://www.jb51.net/article/147261.htm

https://www.cnblogs.com/zhangchaocoming/p/11380724.html

https://baike.baidu.com/item/myisam/8970102?fr=aladdin

https://segmentfault.com/a/1190000019400925

https://www.csdn.net/gather_2e/MtTaEg4sNDk5MC1ibG9n.html

《極客時(shí)間》- MySQL實(shí)戰(zhàn)45講

https://www.cnblogs.com/wyaokai/p/10921323.html

https://www.cnblogs.com/hhhhuanzi/p/12296776.html

https://zhidao.baidu.com/question/55127810.html

https://www.cnblogs.com/limuzi1994/p/9684083.html

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市螺戳，隨后出現(xiàn)的幾起案子搁宾，更是在濱河造成了極大的恐慌，老刑警劉巖倔幼，帶你破解...
    沈念sama閱讀 210,978評(píng)論 6贊 490
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件盖腿，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡损同，警方通過(guò)查閱死者的電腦和手機(jī)奸忽，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,954評(píng)論 2贊 384
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)揖庄，“玉大人栗菜，你說(shuō)我怎么就攤上這事芬萍”用＃” “怎么了？”我有些...
    開封第一講書人閱讀 156,623評(píng)論 0贊 345
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵缕题，是天一觀的道長(zhǎng)禁炒。 經(jīng)常有香客問我而咆，道長(zhǎng)，這世上最難降的妖魔是什么幕袱？ 我笑而不...
    開封第一講書人閱讀 56,324評(píng)論 1贊 282
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任暴备，我火速辦了婚禮，結(jié)果婚禮上们豌，老公的妹妹穿的比我還像新娘涯捻。我一直安慰自己，他們只是感情好望迎，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,390評(píng)論 5贊 384
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開白布障癌。 她就那樣靜靜地躺著，像睡著了一般辩尊。 火紅的嫁衣襯著肌膚如雪涛浙。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 49,741評(píng)論 1贊 289
  • 城市分裂傳說(shuō)
    那天摄欲，我揣著相機(jī)與錄音轿亮，去河邊找鬼。 笑死胸墙，一個(gè)胖子當(dāng)著我的面吹牛我注，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播劳秋，決...
    沈念sama閱讀 38,892評(píng)論 3贊 405
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼仓手，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼胖齐！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起嗽冒，我...
    開封第一講書人閱讀 37,655評(píng)論 0贊 266
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤呀伙，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后添坊，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體剿另，經(jīng)...
    沈念sama閱讀 44,104評(píng)論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,451評(píng)論 2贊 325
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年贬蛙，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了雨女。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,569評(píng)論 1贊 340
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡阳准，死狀恐怖氛堕，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情野蝇，我是刑警寧澤讼稚，帶...
    沈念sama閱讀 34,254評(píng)論 4贊 328
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站绕沈，受9級(jí)特大地震影響锐想，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜乍狐，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,834評(píng)論 3贊 312
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一赠摇、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧浅蚪，春花似錦藕帜、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,725評(píng)論 0贊 21
  • 一樁弒父案耘戚，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)嗡髓。三九已至操漠，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間饿这，已是汗流浹背浊伙。 一陣腳步聲響...
    開封第一講書人閱讀 31,950評(píng)論 1贊 264
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留长捧，地道東北人嚣鄙。 一個(gè)月前我還...
    沈念sama閱讀 46,260評(píng)論 2贊 360
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長(zhǎng)得像串结，于是被迫代替她去往敵國(guó)和親哑子。 傳聞我的和親對(duì)象是個(gè)殘疾皇子舅列，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,446評(píng)論 2贊 348