說(shuō)一下 MySQL 執(zhí)行一條查詢語(yǔ)句的內(nèi)部執(zhí)行過(guò)程？

• 客戶端先通過(guò)連接器連接到 MySQL 服務(wù)器。
• 連接器權(quán)限驗(yàn)證通過(guò)之后，先查詢是否有查詢緩存，如果有緩存（之前執(zhí)行過(guò)此語(yǔ)句）則直接返回緩存數(shù)據(jù)，如果沒(méi)有緩存則進(jìn)入分析器。
• 分析器會(huì)對(duì)查詢語(yǔ)句進(jìn)行語(yǔ)法分析和詞法分析，判斷 SQL 語(yǔ)法是否正確，如果查詢語(yǔ)法錯(cuò)誤會(huì)直接返回給客戶端錯(cuò)誤信息，如果語(yǔ)法正確則進(jìn)入優(yōu)化器。
• 優(yōu)化器是對(duì)查詢語(yǔ)句進(jìn)行優(yōu)化處理，例如一個(gè)表里面有多個(gè)索引，優(yōu)化器會(huì)判別哪個(gè)索引性能更好。
• 優(yōu)化器執(zhí)行完就進(jìn)入執(zhí)行器，執(zhí)行器就開始執(zhí)行語(yǔ)句進(jìn)行查詢比對(duì)了，直到查詢到滿足條件的所有數(shù)據(jù)，然后進(jìn)行返回。

MySQL 提示“不存在此列”是執(zhí)行到哪個(gè)節(jié)點(diǎn)報(bào)出的？

此錯(cuò)誤是執(zhí)行到分析器階段報(bào)出的，因?yàn)?MySQL 會(huì)在分析器階段檢查 SQL 語(yǔ)句的正確性。

MySQL 查詢緩存的功能有何優(yōu)缺點(diǎn)？

MySQL 查詢緩存功能是在連接器之后發(fā)生的，它的優(yōu)點(diǎn)是效率高，如果已經(jīng)有緩存則會(huì)直接返回結(jié)果。
查詢緩存的缺點(diǎn)是失效太頻繁導(dǎo)致緩存命中率比較低，任何更新表操作都會(huì)清空查詢緩存，因此導(dǎo)致查詢緩存非常容易失效。

如何關(guān)閉 MySQL 的查詢緩存功能？

MySQL 查詢緩存默認(rèn)是開啟的，配置 query cache type 參數(shù)為 DEMAND（按需使用）關(guān)閉查詢緩存，MySQL 8.0
之后直接刪除了查詢緩存的功能。

MySQL 的常用引擎都有哪些？

MySQL 的常用引擎有 InnoDB、MyISAM、Memory 等，從 MySQL 5.5.5 版本開始 InnoDB 就成為了默認(rèn)的存儲(chǔ)引擎。

MySQL 可以針對(duì)表級(jí)別設(shè)置數(shù)據(jù)庫(kù)引擎嗎？怎么設(shè)置？

可以針對(duì)不同的表設(shè)置不同的引擎。在 create table 語(yǔ)句中使用 engine=引擎名（比如Memory）來(lái)設(shè)置此表的存儲(chǔ)引擎。完整代碼如下：

create table student(
   id int primary key auto_increment,
   username varchar(120),
   age int
) ENGINE=Memory

常用的存儲(chǔ)引擎 InnoDB 和 MyISAM 有什么區(qū)別？

InnoDB 和 MyISAM 最大的區(qū)別是 InnoDB 支持事務(wù)，而 MyISAM 不支持事務(wù)，它們主要區(qū)別如下：

• InnoDB 支持崩潰后安全恢復(fù)，MyISAM 不支持崩潰后安全恢復(fù)；
• InnoDB 支持行級(jí)鎖，MyISAM 不支持行級(jí)鎖，只支持到表鎖；
• InnoDB 支持外鍵，MyISAM 不支持外鍵；
• MyISAM 性能比 InnoDB 高；
• MyISAM 支持 FULLTEXT 類型的全文索引，InnoDB 不支持 FULLTEXT 類型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好；
• InnoDB 主鍵查詢性能高于 MyISAM。

InnoDB 有哪些特性？

1）插入緩沖(insert buffer)
：對(duì)于非聚集索引的插入和更新，不是每一次直接插入索引頁(yè)中，而是首先判斷插入的非聚集索引頁(yè)是否在緩沖池中，如果在，則直接插入，否則，先放入一個(gè)插入緩沖區(qū)中。好似欺騙數(shù)據(jù)庫(kù)這個(gè)非聚集的索引已經(jīng)插入到葉子節(jié)點(diǎn)了，然后再以一定的頻率執(zhí)行插入緩沖和非聚集索引頁(yè)子節(jié)點(diǎn)的合并操作，這時(shí)通常能將多個(gè)插入合并到一個(gè)操作中，這就大大提高了對(duì)非聚集索引執(zhí)行插入和修改操作的性能。

2）兩次寫(double write) ：兩次寫給 InnoDB 帶來(lái)的是可靠性，主要用來(lái)解決部分寫失敗(partial page
write)。doublewrite 有兩部分組成，一部分是內(nèi)存中的 doublewrite buffer ，大小為
2M，另外一部分就是物理磁盤上的共享表空間中連續(xù)的 128 個(gè)頁(yè)，即兩個(gè)區(qū)，大小同樣為 2M。當(dāng)緩沖池的作業(yè)刷新時(shí)，并不直接寫硬盤，而是通過(guò) memcpy
函數(shù)將臟頁(yè)先拷貝到內(nèi)存中的 doublewrite buffer，之后通過(guò) doublewrite buffer 再分兩次寫，每次寫入 1M
到共享表空間的物理磁盤上，然后馬上調(diào)用 fsync 函數(shù)，同步磁盤。如下圖所示

avatar

3）自適應(yīng)哈希索引(adaptive hash index) ：由于 InnoDB 不支持 hash 索引，但在某些情況下 hash
索引的效率很高，于是出現(xiàn)了 adaptive hash index 功能， InnoDB 存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上索引的查找，如果觀察到建立 hash
索引可以提高性能的時(shí)候，則自動(dòng)建立 hash 索引。

一張自增表中有三條數(shù)據(jù)，刪除了最后兩條數(shù)據(jù)之后重啟數(shù)據(jù)庫(kù)，再新增一條數(shù)據(jù)，此時(shí)這條數(shù)據(jù)的 ID 是幾？

如果這張表的引擎是 MyISAM，那么 ID=4，如果是 InnoDB 那么 ID=2（MySQL 8 之前的版本）。8.0版本之后都是4.

MySQL 中什么情況會(huì)導(dǎo)致自增主鍵不能連續(xù)？

以下情況會(huì)導(dǎo)致 MySQL 自增主鍵不能連續(xù)：

• 唯一主鍵沖突會(huì)導(dǎo)致自增主鍵不連續(xù)；
• 事務(wù)回滾也會(huì)導(dǎo)致自增主鍵不連續(xù)。

InnoDB 中自增主鍵能不能被持久化？

自增主鍵能不能被持久化，說(shuō)的是 MySQL 重啟之后 InnoDB 能不能恢復(fù)重啟之前的自增列，InnoDB 在 8.0 之前是沒(méi)有持久化能力的，但
MySQL 8.0 之后就把自增主鍵保存到 redo log（一種日志類型，下文會(huì)詳細(xì)講）中，當(dāng) MySQL 重啟之后就會(huì)從 redo log 日志中恢復(fù)。

什么是獨(dú)立表空間和共享表空間？它們的區(qū)別是什么？

共享表空間 ：指的是數(shù)據(jù)庫(kù)的所有的表數(shù)據(jù)，索引文件全部放在一個(gè)文件中，默認(rèn)這個(gè)共享表空間的文件路徑在 data 目錄下。 獨(dú)立表空間
：每一個(gè)表都將會(huì)生成以獨(dú)立的文件方式來(lái)進(jìn)行存儲(chǔ)。
共享表空間和獨(dú)立表空間最大的區(qū)別是如果把表放再共享表空間，即使表刪除了空間也不會(huì)刪除，所以表依然很大，而獨(dú)立表空間如果刪除表就會(huì)清除空間。

如何設(shè)置獨(dú)立表空間？

獨(dú)立表空間是由參數(shù) innodb file per_table 控制的，把它設(shè)置成 ON 就是獨(dú)立表空間了，從 MySQL 5.6.6
版本之后，這個(gè)值就默認(rèn)是 ON 了。

如何進(jìn)行表空間收縮？

使用重建表的方式可以收縮表空間，重建表有以下三種方式：

• alter table t engine=InnoDB
• optmize table t
• truncate table t

說(shuō)一下重建表的執(zhí)行流程？

• 建立一個(gè)臨時(shí)文件，掃描表 t 主鍵的所有數(shù)據(jù)頁(yè)；
• 用數(shù)據(jù)頁(yè)中表 t 的記錄生成 B+ 樹，存儲(chǔ)到臨時(shí)文件中；
• 生成臨時(shí)文件的過(guò)程中，將所有對(duì) t 的操作記錄在一個(gè)日志文件（row log）中；
• 臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表 t相同的數(shù)據(jù)文件；
• 用臨時(shí)文件替換表 t 的數(shù)據(jù)文件。

表的結(jié)構(gòu)信息存在哪里？

表結(jié)構(gòu)定義占有的存儲(chǔ)空間比較小，在 MySQL 8 之前，表結(jié)構(gòu)的定義信息存在以 .frm 為后綴的文件里，在 MySQL 8
之后，則允許把表結(jié)構(gòu)的定義信息存在系統(tǒng)數(shù)據(jù)表之中。

什么是覆蓋索引？

覆蓋索引是指，索引上的信息足夠滿足查詢請(qǐng)求，不需要再回到主鍵上去取數(shù)據(jù)。

如果把一個(gè) InnoDB 表的主鍵刪掉，是不是就沒(méi)有主鍵，就沒(méi)辦法進(jìn)行回表查詢了？

可以回表查詢，如果把主鍵刪掉了，那么 InnoDB 會(huì)自己生成一個(gè)長(zhǎng)度為 6 字節(jié)的 rowid 作為主鍵。

執(zhí)行一個(gè) update 語(yǔ)句以后，我再去執(zhí)行 hexdump 命令直接查看 ibd 文件內(nèi)容，為什么沒(méi)有看到數(shù)據(jù)有改變呢？

可能是因?yàn)?update 語(yǔ)句執(zhí)行完成后，InnoDB 只保證寫完了 redo log、內(nèi)存，可能還沒(méi)來(lái)得及將數(shù)據(jù)寫到磁盤。

內(nèi)存表和臨時(shí)表有什么區(qū)別？

• 內(nèi)存表，指的是使用 Memory 引擎的表，建表語(yǔ)法是 create table … engine=memory。這種表的數(shù)據(jù)都保存在內(nèi)存里，系統(tǒng)重啟的時(shí)候會(huì)被清空，但是表結(jié)構(gòu)還在。除了這兩個(gè)特性看上去比較“奇怪”外，從其他的特征上看，它就是一個(gè)正常的表。
• 而臨時(shí)表，可以使用各種引擎類型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的臨時(shí)表，寫數(shù)據(jù)的時(shí)候是寫到磁盤上的。

并發(fā)事務(wù)會(huì)帶來(lái)哪些問(wèn)題？

• 臟讀
• 修改丟失
• 不可重復(fù)讀
• 幻讀

什么是臟讀和幻讀？

臟讀是一個(gè)事務(wù)在處理過(guò)程中讀取了另外一個(gè)事務(wù)未提交的數(shù)據(jù)；幻讀是指同一個(gè)事務(wù)內(nèi)多次查詢返回的結(jié)果集不一樣（比如增加了或者減少了行記錄）。

為什么會(huì)出現(xiàn)幻讀？幻讀會(huì)帶來(lái)什么問(wèn)題？

因?yàn)樾墟i只能鎖定存在的行，針對(duì)新插入的操作沒(méi)有限定，所以就有可能產(chǎn)生幻讀。 幻讀帶來(lái)的問(wèn)題如下：

• 對(duì)行鎖語(yǔ)義的破壞；
• 破壞了數(shù)據(jù)一致性。

如何避免幻讀？

使用間隙鎖的方式來(lái)避免出現(xiàn)幻讀。間隙鎖，是專門用于解決幻讀這種問(wèn)題的鎖，它鎖的了行與行之間的間隙，能夠阻塞新插入的操作
間隙鎖的引入也帶來(lái)了一些新的問(wèn)題，比如：降低并發(fā)度，可能導(dǎo)致死鎖。

如何查看 MySQL 的空閑連接？

在 MySQL 的命令行中使用 show processlist; 查看所有連接，其中 Command 列顯示為 Sleep
的表示空閑連接，如下圖所示：

avatar

MySQL 中的字符串類型都有哪些？

MySQL 的字符串類型和取值如下：

VARCHAR 和 CHAR 的區(qū)別是什么？分別適用的場(chǎng)景有哪些？

VARCHAR 和 CHAR 最大區(qū)別就是，VARCHAR 的長(zhǎng)度是可變的，而 CHAR 是固定長(zhǎng)度，CHAR 的取值范圍為1-255，因此 VARCHAR
可能會(huì)造成存儲(chǔ)碎片。由于它們的特性決定了 CHAR 比較適合長(zhǎng)度較短的字段和固定長(zhǎng)度的字段，如身份證號(hào)、手機(jī)號(hào)等，反之則適合使用 VARCHAR。

MySQL 存儲(chǔ)金額應(yīng)該使用哪種數(shù)據(jù)類型？為什么？

MySQL 存儲(chǔ)金額應(yīng)該使用 decimal ，因?yàn)槿绻鎯?chǔ)其他數(shù)據(jù)類型，比如 float 有導(dǎo)致小數(shù)點(diǎn)后數(shù)據(jù)丟失的風(fēng)險(xiǎn)。

limit 3,2 的含義是什么？

去除前三條數(shù)據(jù)之后查詢兩條信息。

now() 和 current_date() 有什么區(qū)別？

now() 返回當(dāng)前時(shí)間包含日期和時(shí)分秒，current_date() 只返回當(dāng)前時(shí)間，如下圖所示：

avatar

如何去重計(jì)算總條數(shù)？

使用 distinct 去重，使用 count 統(tǒng)計(jì)總條數(shù)，具體實(shí)現(xiàn)腳本如下：

select count(distinct f) from t

last insert id() 函數(shù)功能是什么？有什么特點(diǎn)？

last insert id() 用于查詢最后一次自增表的編號(hào)，它的特點(diǎn)是查詢時(shí)不需要不需要指定表名，使用 select last_insert_id() 即可查詢，因?yàn)椴恍枰付ū砻运冀K以最后一條自增編號(hào)為主，可以被其它表的自增編號(hào)覆蓋。比如 A 表的最大編號(hào)是
10，last insert id() 查詢出來(lái)的值為 10，這時(shí) B 表插入了一條數(shù)據(jù)，它的最大編號(hào)為 3，這個(gè)時(shí)候使用 last insert
id() 查詢的值就是 3。

刪除表的數(shù)據(jù)有幾種方式？它們有什么區(qū)別？

刪除數(shù)據(jù)有兩種方式：delete 和 truncate，它們的區(qū)別如下：

• delete 可以添加 where 條件刪除部分?jǐn)?shù)據(jù)，truncate 不能添加 where 條件只能刪除整張表；
• delete 的刪除信息會(huì)在 MySQL 的日志中記錄，而 truncate 的刪除信息不被記錄在 MySQL 的日志中，因此 detele 的信息可以被找回而 truncate 的信息無(wú)法被找回；
• truncate 因?yàn)椴挥涗浫罩舅詧?zhí)行效率比 delete 快。

delete 和 truncate 的使用腳本如下：

delete from t where username='redis'; truncate table t;

MySQL 中支持幾種模糊查詢？它們有什么區(qū)別？

MySQL 中支持兩種模糊查詢：regexp 和 like，like 是對(duì)任意多字符匹配或任意單字符進(jìn)行模糊匹配，而 regexp
則支持正則表達(dá)式的匹配方式，提供比 like 更多的匹配方式。 regexp 和 like 的使用示例如下： select * from person
where uname like '%SQL%';> select from person where uname regexp '.SQL*.';

MySQL 支持枚舉嗎？如何實(shí)現(xiàn)？它的用途是什么？

MySQL 支持枚舉，它的實(shí)現(xiàn)方式如下：

create table t(
    sex enum('boy','grid') default 'unknown'
);

枚舉的作用是預(yù)定義結(jié)果值，當(dāng)插入數(shù)據(jù)不在枚舉值范圍內(nèi)，則插入失敗，提示錯(cuò)誤 Data truncated for column 'xxx' at row n 。

count(column) 和 count(*) 有什么區(qū)別？

count(column) 和 count() 最大區(qū)別是統(tǒng)計(jì)結(jié)果可能不一致，count(column) 統(tǒng)計(jì)不會(huì)統(tǒng)計(jì)列值為 null 的數(shù)據(jù)，而
count() 則會(huì)統(tǒng)計(jì)所有信息，所以最終的統(tǒng)計(jì)結(jié)果可能會(huì)不同。

以下關(guān)于 count 說(shuō)法正確的是？

A. count 的查詢性能在各種存儲(chǔ)引擎下的性能都是一樣的。 B. count 在 MyISAM 比 InnoDB 的性能要低。 C. count 在
InnoDB 中是一行一行讀取，然后累計(jì)計(jì)數(shù)的。 D. count 在 InnoDB 中存儲(chǔ)了總條數(shù)，查詢的時(shí)候直接取出。

答：C

為什么 InnoDB 不把總條數(shù)記錄下來(lái)，查詢的時(shí)候直接返回呢？

因?yàn)?InnoDB 使用了事務(wù)實(shí)現(xiàn)，而事務(wù)的設(shè)計(jì)使用了多版本并發(fā)控制，即使是在同一時(shí)間進(jìn)行查詢，得到的結(jié)果也可能不相同，所以 InnoDB
不能把結(jié)果直接保存下來(lái)，因?yàn)檫@樣是不準(zhǔn)確的。

能否使用 show table status 中的表行數(shù)作為表的總行數(shù)直接使用？為什么？

不能，因?yàn)?show table status 是通過(guò)采樣統(tǒng)計(jì)估算出來(lái)的，官方文檔說(shuō)誤差可能在 40% 左右，所以 show table status
中的表行數(shù)不能直接使用。

以下哪個(gè) SQL 的查詢性能最高？

A. select count(*) from t where time>1000 and time<4500 B. show table status
where name='t' C. select count(id) from t where time>1000 and time<4500 D.
select count(name) from t where time>1000 and time<4500

答：B 題目解析：因?yàn)?show table status 的表行數(shù)是估算出來(lái)，而其他的查詢因?yàn)樘砑恿?where 條件，即使是 MyISAM
引擎也不能直接使用已經(jīng)存儲(chǔ)的總條數(shù)，所以 show table status 的查詢性能最高。

InnoDB 和 MyISAM 執(zhí)行 select count(*) from t，哪個(gè)效率更高？為什么？

MyISAM 效率最高，因?yàn)?MyISAM 內(nèi)部維護(hù)了一個(gè)計(jì)數(shù)器，直接返回總條數(shù)，而 InnoDB 要逐行統(tǒng)計(jì)。

在 MySQL 中有對(duì) count(*) 做優(yōu)化嗎？做了哪些優(yōu)化？

count(*) 在不同的 MySQL 引擎中的實(shí)現(xiàn)方式是不相同的，在沒(méi)有 where 條件的情況下：

• MyISAM 引擎會(huì)把表的總行數(shù)存儲(chǔ)在磁盤上，因此在執(zhí)行 count(*) 的時(shí)候會(huì)直接返回這個(gè)這個(gè)行數(shù)，執(zhí)行效率很高；
• InnoDB 引擎中 count(*) 就比較麻煩了，需要把數(shù)據(jù)一行一行的從引擎中讀出來(lái)，然后累計(jì)基數(shù)。

但即使這樣，在 InnoDB 中，MySQL 還是做了優(yōu)化的，我們知道對(duì)于 count( )
這樣的操作，遍歷任意索引樹得到的結(jié)果，在邏輯上都是一樣的，因此，MySQL
優(yōu)化器會(huì)找到最小的那顆索引樹來(lái)遍歷，這樣就能在保證邏輯正確的前提下，盡量少掃描數(shù)據(jù)量，從而優(yōu)化了 count( ) 的執(zhí)行效率。

在 InnoDB 引擎中 count(*)、count(1)、count(主鍵)、count(字段) 哪個(gè)性能最高？

count(字段)<count(主鍵 id)<count(1)≈count(*) 題目解析：

• 對(duì)于 count(主鍵 id) 來(lái)說(shuō)，InnoDB 引擎會(huì)遍歷整張表，把每一行的 id 值都取出來(lái)，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。
• 對(duì)于 count(1) 來(lái)說(shuō)，InnoDB 引擎遍歷整張表，但不取值。server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1”進(jìn)去，判斷是不可能為空的，按行累加。
• 對(duì)于 count(字段) 來(lái)說(shuō)，如果這個(gè)“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個(gè)字段，判斷不能為 null，按行累加；如果這個(gè)“字段”定義允許為 null，那么執(zhí)行的時(shí)候，判斷到有可能是 null，還要把值取出來(lái)再判斷一下，不是 null 才累加。
• 對(duì)于 count(*) 來(lái)說(shuō)，并不會(huì)把全部字段取出來(lái)，而是專門做了優(yōu)化，不取值，直接按行累加。

所以最后得出的結(jié)果是：count(字段)<count(主鍵 id)<count(1)≈count(*)。

MySQL 中內(nèi)連接、左連接、右連接有什么區(qū)別？

• 內(nèi)連（inner join）— 把匹配的關(guān)聯(lián)數(shù)據(jù)顯示出來(lái)；
• 左連接（left join）— 把左邊的表全部顯示出來(lái)，右邊的表顯示出符合條件的數(shù)據(jù)；
• 右連接（right join）— 把右邊的表全部顯示出來(lái)，左邊的表顯示出符合條件的數(shù)據(jù)；

什么是視圖？如何創(chuàng)建視圖？

視圖是一種虛擬的表，具有和物理表相同的功能，可以對(duì)視圖進(jìn)行增、改、查操作。視圖通常是一個(gè)表或者多個(gè)表的行或列的子集。 視圖創(chuàng)建腳本如下：

create view vname as
select column_names
from table_name
where condition

視圖有哪些優(yōu)點(diǎn)？

• 獲取數(shù)據(jù)更容易，相對(duì)于多表查詢來(lái)說(shuō)；
• 視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)；
• 視圖的修改不會(huì)影響基本表，提供了獨(dú)立的操作單元，比較輕量。

MySQL 中“視圖”的概念有幾個(gè)？分別代表什么含義？

MySQL 中的“視圖”概念有兩個(gè)，它們分別是：

• MySQL 中的普通視圖也是我們最常用的 view，創(chuàng)建語(yǔ)法是 create view …,它的查詢和普通表一樣；
• InnoDB 實(shí)現(xiàn) MVCC（Multi-Version Concurrency Control）多版本并發(fā)控制時(shí)用到的一致性讀視圖，它沒(méi)有物理結(jié)構(gòu)，作用是事務(wù)執(zhí)行期間定于可以看到的數(shù)據(jù)。

使用 delete 誤刪數(shù)據(jù)怎么找回？

可以用 Flashback 工具通過(guò)閃回把數(shù)據(jù)恢復(fù)回來(lái)。

Flashback 恢復(fù)數(shù)據(jù)的原理是什么？

Flashback 恢復(fù)數(shù)據(jù)的原理是是修改 binlog 的內(nèi)容，拿回原庫(kù)重放，從而實(shí)現(xiàn)數(shù)據(jù)找回。