面試題1：你對(duì)數(shù)據(jù)庫(kù)優(yōu)化有哪些了解呀？

正經(jīng)回答：

在高并發(fā)環(huán)境下，數(shù)據(jù)庫(kù)是最敏感的地方，nginx負(fù)載均衡、Server集群、MQ消息隊(duì)列、Redis緩存集群、數(shù)據(jù)庫(kù)主從集群所作的一切都是為了減輕數(shù)據(jù)庫(kù)訪問(wèn)壓力。但是！前提是要有健壯的數(shù)據(jù)庫(kù)和底層代碼，這樣才能使前期準(zhǔn)備不再是花架子。

性價(jià)比如上圖，我們針對(duì)數(shù)據(jù)庫(kù)的優(yōu)化優(yōu)先級(jí)大致如下：

• 高：從SQL優(yōu)化、索引優(yōu)化入手，優(yōu)化慢SQL、利用好索引，是重中之重；
• 中：SQL優(yōu)化之后，是對(duì)數(shù)據(jù)表結(jié)構(gòu)設(shè)計(jì)、橫縱分表分庫(kù)，對(duì)數(shù)據(jù)量級(jí)的處理；
• 低：通過(guò)修改數(shù)據(jù)庫(kù)系統(tǒng)配置，最大化里用服務(wù)器內(nèi)存等資源；
• 低：通過(guò)以上方式還不行，那就是服務(wù)器資源瓶頸了，加機(jī)器。

優(yōu)化成本：硬件 > 系統(tǒng)配置 > 數(shù)據(jù)庫(kù)表結(jié)構(gòu) > SQL及索引。優(yōu)化效果：硬件 < 系統(tǒng)配置 < 數(shù)據(jù)庫(kù)表結(jié)構(gòu) < SQL及索引。

深入追問(wèn)：

追問(wèn)1：那你對(duì)SQL優(yōu)化方面有哪些技巧呢？

簡(jiǎn)單說(shuō)對(duì)于SQL優(yōu)化，就三點(diǎn):

• 最大化利用索引；
• 盡可能避免全表掃描；
• 減少無(wú)效數(shù)據(jù)的查詢；

首先要清楚SELECT語(yǔ)句 - 執(zhí)行順序：

FROM <表名> # 選取表，將多個(gè)表數(shù)據(jù)通過(guò)笛卡爾積變成一個(gè)表。 ON <篩選條件> # 對(duì)笛卡爾積的虛表進(jìn)行篩選 JOIN <join, left join, right join…> <join表> # 指定join，用于添加數(shù)據(jù)到on之后的虛表中，例如left join會(huì)將左表的剩余數(shù)據(jù)添加到虛表中 WHERE <where條件> # 對(duì)上述虛表進(jìn)行篩選 GROUP BY <分組條件> # 分組 <SUM()等聚合函數(shù)> # 用于having子句進(jìn)行判斷，在書寫上這類聚合函數(shù)是寫在having判斷里面的 HAVING <分組篩選> # 對(duì)分組后的結(jié)果進(jìn)行聚合篩選 SELECT <返回?cái)?shù)據(jù)列表> # 返回的單列必須在group by子句中，聚合函數(shù)除外 DISTINCT #數(shù)據(jù)除重 ORDER BY <排序條件> # 排序 LIMIT <行數(shù)限制>

SQL優(yōu)化策略:

聲明：以下SQL優(yōu)化策略適用于數(shù)據(jù)量較大的場(chǎng)景下，如果數(shù)據(jù)量較小，沒(méi)必要以此為準(zhǔn)，以免畫蛇添足。

一、避免不走索引的場(chǎng)景

1.盡量避免在字段開頭模糊查詢，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

SELECT * FROM t WHERE username LIKE '%陳%'

優(yōu)化方式：盡量在字段后面使用模糊查詢。如下：（原因涉及B+Tree索引最左前綴原則，可以參考《MySQL最左匹配原則，道兒上兄弟都得知道的原則》）

SELECT * FROM t WHERE username LIKE '陳%'

如果需求是要在前面使用模糊查詢，

使用MySQL內(nèi)置函數(shù)INSTR(str,substr) 來(lái)匹配，作用類似于java中的indexOf()，查詢字符串出現(xiàn)的角標(biāo)位.

使用FullText全文索引，用match against 檢索

數(shù)據(jù)量較大的情況，建議引用ElasticSearch、solr，億級(jí)數(shù)據(jù)量檢索速度秒級(jí)

當(dāng)表數(shù)據(jù)量較少（幾千條兒那種），別整花里胡哨的，直接用like ‘%xx%'。

2.盡量避免使用 or，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

優(yōu)化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

盡量避免進(jìn)行null值的判斷，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

SELECT * FROM t WHERE score IS NULL

優(yōu)化方式：可以給字段添加默認(rèn)值0，對(duì)0值進(jìn)行判斷。如下：

SELECT * FROM t WHERE score = 0

4.盡量避免在where條件中等號(hào)的左側(cè)進(jìn)行表達(dá)式、函數(shù)操作，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。

可以將表達(dá)式、函數(shù)操作移動(dòng)到等號(hào)右側(cè)。如下：

-- 全表掃描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

當(dāng)數(shù)據(jù)量大時(shí)，避免使用where 1=1的條件。通常為了方便拼裝查詢條件，我們會(huì)默認(rèn)使用該條件，數(shù)據(jù)庫(kù)引擎會(huì)放棄索引進(jìn)行全表掃描。如下：

SELECT username, age, sex FROM T WHERE 1=1

優(yōu)化方式：用代碼拼裝sql時(shí)進(jìn)行判斷，沒(méi) where 條件就去掉 where，有where條件就加 and。

6.查詢條件不要用 <> 或者 !=

使用索引列作為條件進(jìn)行查詢時(shí)，需要避免使用<>或者!=等判斷條件。如確實(shí)業(yè)務(wù)需要，使用到不等于符號(hào)，需要在重新評(píng)估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替。

7.where條件僅包含復(fù)合索引非前置列

如下：復(fù)合（聯(lián)合）索引包含key_part1，key_part2，key_part3三列，但SQL語(yǔ)句沒(méi)有包含索引前置列"key_part1"，按照MySQL聯(lián)合索引的最左匹配原則，不會(huì)走聯(lián)合索引。。

select col1 from table where key_part2=1 and key_part3=2

8.隱式類型轉(zhuǎn)換造成不使用索引

如下SQL語(yǔ)句由于索引對(duì)列類型為varchar，但給定的值為數(shù)值，涉及隱式類型轉(zhuǎn)換，造成不能正確走索引。

select col1 from table where col_varchar=123; 

9.order by 條件要與where中條件一致，否則order by不會(huì)利用索引進(jìn)行排序

-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;
對(duì)于上面的語(yǔ)句，數(shù)據(jù)庫(kù)的處理順序是：

• 第一步：根據(jù)where條件和統(tǒng)計(jì)信息生成執(zhí)行計(jì)劃，得到數(shù)據(jù)。
• 第二步：將得到的數(shù)據(jù)排序。當(dāng)執(zhí)行處理數(shù)據(jù)（order by）時(shí)，數(shù)據(jù)庫(kù)會(huì)先查看第一步的執(zhí)行計(jì)劃，看order by 的字段是否在執(zhí)行計(jì)劃中利用了索引。如果是，則可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。如果不是，則重新進(jìn)行排序操作。
• 第三步：返回排序后的數(shù)據(jù)。

當(dāng)order by 中的字段出現(xiàn)在where條件中時(shí)，才會(huì)利用索引而不再二次排序，更準(zhǔn)確的說(shuō)，order by 中的字段在執(zhí)行計(jì)劃中利用了索引時(shí)，不用排序操作。

這個(gè)結(jié)論不僅對(duì)order by有效，對(duì)其他需要排序的操作也有效。比如group by 、union 、distinct等。

二、SELECT語(yǔ)句的一些其他優(yōu)化

1.避免出現(xiàn)select *

首先，select * 操作在任何類型數(shù)據(jù)庫(kù)中都不是一個(gè)好的SQL編寫習(xí)慣。

使用select * 取出全部列，會(huì)讓優(yōu)化器無(wú)法完成索引覆蓋掃描這類優(yōu)化，會(huì)影響優(yōu)化器對(duì)執(zhí)行計(jì)劃的選擇，也會(huì)增加網(wǎng)絡(luò)帶寬消耗，更會(huì)帶來(lái)額外的I/O,內(nèi)存和CPU消耗。

建議提出業(yè)務(wù)實(shí)際需要的列數(shù)，將指定列名以取代select *。

2.避免出現(xiàn)不確定結(jié)果的函數(shù)

特定針對(duì)主從復(fù)制這類業(yè)務(wù)場(chǎng)景。由于原理上從庫(kù)復(fù)制的是主庫(kù)執(zhí)行的語(yǔ)句，使用如now()、rand()、sysdate()、current_user()等不確定結(jié)果的函數(shù)很容易導(dǎo)致主庫(kù)與從庫(kù)相應(yīng)的數(shù)據(jù)不一致。另外不確定值的函數(shù),產(chǎn)生的SQL語(yǔ)句無(wú)法利用query cache。

3.多表關(guān)聯(lián)查詢時(shí)，小表在前，大表在后

在MySQL中，執(zhí)行 from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的（Oracle相反），第一張表會(huì)涉及到全表掃描，所以將小表放在前面，先掃小表，掃描快效率較高，在掃描后面的大表，或許只掃描大表的前100行就符合返回條件并return了。

例如：表1有50條數(shù)據(jù)，表2有30億條數(shù)據(jù)；如果全表掃描表2，你品，那就先去吃個(gè)飯?jiān)僬f(shuō)吧是吧。

4.使用表的別名

當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)列名上。這樣就可以減少解析的時(shí)間并減少哪些友列名歧義引起的語(yǔ)法錯(cuò)誤。

5.用where字句替換HAVING字句

避免使用HAVING字句，因?yàn)镠AVING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾，而where則是在聚合前刷選記錄，如果能通過(guò)where字句限制記錄的數(shù)目，那就能減少這方面的開銷。HAVING中的條件一般用于聚合函數(shù)的過(guò)濾，除此之外，應(yīng)該將條件寫在where字句中。

• where和having的區(qū)別：where后面不能使用組函數(shù)

6.調(diào)整Where字句中的連接順序

MySQL采用從左往右，自上而下的順序解析where子句。根據(jù)這個(gè)原理，應(yīng)將過(guò)濾數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。對(duì)了，聽說(shuō)5.7版的語(yǔ)法解析器已經(jīng)實(shí)現(xiàn)了where后條件的自動(dòng)調(diào)節(jié)工作。查詢條件很多的場(chǎng)景，建議不要做這種嘗試。

追問(wèn)2：嗯，那你說(shuō)一下為什么不建議用SELECT * 呢？

在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確寫出。

增加查詢分析器解析成本。

增減字段容易與 resultMap 配置不一致。

無(wú)用字段增加網(wǎng)絡(luò) 消耗，尤其是 text 類型的字段。

1. 不需要的列會(huì)增加數(shù)據(jù)傳輸時(shí)間和網(wǎng)絡(luò)開銷

用“SELECT * ”數(shù)據(jù)庫(kù)需要解析更多的對(duì)象、字段、權(quán)限、屬性等相關(guān)內(nèi)容，在 SQL 語(yǔ)句復(fù)雜，硬解析較多的情況下，會(huì)對(duì)數(shù)據(jù)庫(kù)造成沉重的負(fù)擔(dān)。

增大網(wǎng)絡(luò)開銷；* 有時(shí)會(huì)誤帶上如log、IconMD5之類的無(wú)用且大文本字段，數(shù)據(jù)傳輸size會(huì)幾何增漲。如果DB和應(yīng)用程序不在同一臺(tái)機(jī)器，這種開銷非常明顯。

即使 mysql 服務(wù)器和客戶端是在同一臺(tái)機(jī)器上，使用的協(xié)議還是 tcp，通信也是需要額外的時(shí)間。

2. 對(duì)于無(wú)用的大字段，如 varchar、blob、text，會(huì)增加 io 操作

準(zhǔn)確來(lái)說(shuō)，長(zhǎng)度超過(guò) 728 字節(jié)的時(shí)候，會(huì)先把超出的數(shù)據(jù)序列化到另外一個(gè)地方，因此讀取這條記錄會(huì)增加一次 io 操作。（MySQL InnoDB）

3. 失去MySQL優(yōu)化器“覆蓋索引”策略優(yōu)化的可能性

SELECT * 杜絕了覆蓋索引的可能性，而基于MySQL優(yōu)化器的“覆蓋索引”策略又是速度極快，效率極高，業(yè)界極為推薦的查詢優(yōu)化方式。

面試題2：你對(duì)分庫(kù)分表是怎么看的呀？

正經(jīng)回答：

• 分庫(kù)：由單個(gè)數(shù)據(jù)庫(kù)實(shí)例拆分成多個(gè)數(shù)據(jù)庫(kù)實(shí)例，將數(shù)據(jù)分布到多個(gè)數(shù)據(jù)庫(kù)實(shí)例中。
• 分表：由單張表拆分成多張表，將數(shù)據(jù)劃分到多張表內(nèi)。

要知道，對(duì)于大型互聯(lián)網(wǎng)項(xiàng)目，數(shù)據(jù)量級(jí)可能不是我們能想到的，每日新增數(shù)據(jù)量過(guò)千萬(wàn)是常有的事兒，想靠單臺(tái)MySQL服務(wù)器是不現(xiàn)實(shí)的。你項(xiàng)羽在牛B，也頂不住四個(gè)隊(duì)友掛機(jī)啊?。№?xiàng)羽：？？？

隨著業(yè)務(wù)數(shù)據(jù)量和網(wǎng)站QPS日益增高，對(duì)數(shù)據(jù)庫(kù)壓力也越來(lái)越大，單機(jī)版數(shù)據(jù)庫(kù)很快會(huì)到達(dá)存儲(chǔ)和并發(fā)瓶頸，就需要做數(shù)據(jù)庫(kù)性能方面的優(yōu)化，分庫(kù)分表采取的是分而治之的策略，分庫(kù)目的是減輕單臺(tái)MySQL實(shí)例存儲(chǔ)壓力及可擴(kuò)展性，而分表是解決單張表數(shù)據(jù)過(guò)大以后查詢的瓶頸問(wèn)題，坦白說(shuō)，這些問(wèn)題也是所有關(guān)系型數(shù)據(jù)庫(kù)的“硬傷”。

常用策略包括：垂直分表、水平分表、垂直分庫(kù)、水平分庫(kù)。

1、垂直分表

垂直分表，或者叫豎著切表，是不是感受到該策略是以字段為依據(jù)的！主要按照字段的活躍性、字段長(zhǎng)度，將表中字段拆分到不同的表（主表和擴(kuò)展表）中。

特點(diǎn)：

• 每個(gè)表的結(jié)構(gòu)都不一樣；
• 每個(gè)表的數(shù)據(jù)也不一樣，
• 有一個(gè)關(guān)聯(lián)字段，一般是主鍵或外鍵，用于關(guān)聯(lián)兄弟表數(shù)據(jù)；
• 所有兄弟表的并集是該表的全量數(shù)據(jù)；

場(chǎng)景：

1.有幾個(gè)字段屬于熱點(diǎn)字段，更新頻率很高，要把這些字段單獨(dú)切到一張表里，不然innodb行鎖很惡心的，鎖死你呀~~如用戶表里的余額字段？不，我的余額就很穩(wěn)定，一直是0。。

2.有大字段，如text，存儲(chǔ)壓力很大，畢竟innodb數(shù)據(jù)和索引是同一個(gè)文件；同時(shí)，我又喜歡用SELECT *，你懂得，這磁盤IO消耗的，跟玩兒似的，誰(shuí)都扛不住的。

3.有明顯的業(yè)務(wù)區(qū)分，或表結(jié)構(gòu)設(shè)計(jì)時(shí)字段冗余；有些小伙伴看到第一點(diǎn)時(shí)，就發(fā)現(xiàn)陳哈哈是個(gè)菜雞，用戶表怎么會(huì)有余額字段？明顯有問(wèn)題?。≮s緊先到評(píng)論區(qū)噴陳哈哈一波~~然后笑嘻嘻的發(fā)現(xiàn)原來(lái)是個(gè)小尾巴，真不要臉是吧。。是的，因此不同業(yè)務(wù)我們要把具體字段拆開，這樣才有利于業(yè)務(wù)后續(xù)擴(kuò)展哦。

2、水平分表

水平分表，也叫“橫著切”。。以行數(shù)據(jù)為依據(jù)進(jìn)行切分，一般按照某列的自容進(jìn)行切分。

如手機(jī)號(hào)表，我們可以通過(guò)前兩位或前三位進(jìn)行切分，如131、132、133 → phone_131、phone_132、phone_133，手機(jī)號(hào)有11位（100億），量大是很正常的事兒，這年頭誰(shuí)家老頭老太太每個(gè)手機(jī)呢是吧。這樣切就把一張大表切成了好幾十張小表，數(shù)據(jù)量不就下來(lái)了。有同學(xué)就問(wèn)了那我怎么知道我這手機(jī)號(hào)查哪個(gè)表呢？一看你就沒(méi)認(rèn)真看前兩行標(biāo)紅的點(diǎn)，為啥標(biāo)紅嘞？比如我查13100001111，那我截取前三位，動(dòng)態(tài)拼接到查詢的表名上，就行了。

特點(diǎn)：

• 每個(gè)表的結(jié)構(gòu)都一樣；
• 每個(gè)表的數(shù)據(jù)都不一樣，沒(méi)有交集；
• 所有表的并集是該表的全量數(shù)據(jù)；

場(chǎng)景：單表的數(shù)據(jù)量過(guò)大或增長(zhǎng)速度很快，已經(jīng)影響或即將會(huì)影響SQL查詢效率，加重了CPU負(fù)擔(dān)，提前到達(dá)瓶頸。記得水平分表越早越好，別問(wèn)我為什么。。

分庫(kù)

需要你注意的是，傳統(tǒng)的分庫(kù)和我們熟悉的集群、主從復(fù)制可不是一個(gè)事兒；多節(jié)點(diǎn)集群是將一個(gè)庫(kù)復(fù)制成N個(gè)庫(kù)，從而通過(guò)讀寫分離實(shí)現(xiàn)多個(gè)MySQL服務(wù)的負(fù)載均衡，實(shí)際是圍繞一個(gè)庫(kù)來(lái)搞的，這個(gè)庫(kù)稱為Master主庫(kù)。而分庫(kù)就不同了，分庫(kù)是將這個(gè)主庫(kù)一分為N，比如一分為二，然后針對(duì)這兩個(gè)主庫(kù)，再配置2N個(gè)從庫(kù)節(jié)點(diǎn)。

3、垂直分庫(kù)

縱向切庫(kù)，太經(jīng)典的切分方式，基于表進(jìn)行切分，通常是把新的業(yè)務(wù)模塊或集成公共模塊拆分出去，比如我們最熟悉的單點(diǎn)登錄、鑒權(quán)模塊。熟悉的味道，記得有一次我把一些沒(méi)用的表切到一個(gè)性能很好的服務(wù)器中，這服務(wù)器我專門用來(lái)學(xué)習(xí)，后來(lái)也不知被哪個(gè)狗腿子告密了~ 我**你個(gè)**，有種站出來(lái)，你個(gè)**東西

特點(diǎn)：

• 每個(gè)庫(kù)的表都不一樣；
• 表不一樣，數(shù)據(jù)就更不一樣了~ 沒(méi)有任何交集；
• 每個(gè)庫(kù)相對(duì)獨(dú)立，模塊化

場(chǎng)景：可以抽象出單獨(dú)的業(yè)務(wù)模塊時(shí)，可以抽象出公共區(qū)時(shí)（如字典、公共時(shí)間、公共配置等），或者想有一臺(tái)屬于自己的服務(wù)器時(shí)？

4、水平分庫(kù)

以行數(shù)據(jù)為依據(jù)，將一個(gè)庫(kù)中的數(shù)據(jù)拆分到多個(gè)庫(kù)中。大型分表體驗(yàn)一下？坦白說(shuō)這種策略并不實(shí)用，因?yàn)闀?huì)對(duì)后臺(tái)開發(fā)很不友好，有很多坑，不建議采用，理解即可。

特點(diǎn)：

• 每個(gè)庫(kù)的結(jié)構(gòu)都一樣；
• 每個(gè)庫(kù)的數(shù)據(jù)都不一樣，沒(méi)有交集；
• 所有庫(kù)的并集是全量數(shù)據(jù)；

場(chǎng)景：系統(tǒng)絕對(duì)并發(fā)量上來(lái)了，CPU內(nèi)存壓力大。分表難以根本上解決量的問(wèn)題，并且還沒(méi)有明顯的業(yè)務(wù)歸屬來(lái)垂直分庫(kù)，主庫(kù)磁盤接近飽和。

其實(shí)，在實(shí)際工作中，我們?cè)谶x擇分庫(kù)分表策略前，想到的應(yīng)該是從緩存、讀寫分離、SQL優(yōu)化等方面，因?yàn)檫@些能夠更直接、代價(jià)更小的解決問(wèn)題。要記住動(dòng)表就是動(dòng)根本，你永遠(yuǎn)不知道這張表后面會(huì)連帶多少歷史遺留問(wèn)題，如果是個(gè)很大型的項(xiàng)目，遇到些問(wèn)題你就跟經(jīng)理提議要分庫(kù)分表，小心被呼死~

深入追問(wèn)：

追問(wèn)1：毫無(wú)意義，我真的不想問(wèn)他MySQL問(wèn)題了

面試題3：MySQL刪除數(shù)據(jù)的方式都有哪些？

正經(jīng)回答：

咱們常用的三種刪除方式：通過(guò) delete、truncate、drop 關(guān)鍵字進(jìn)行刪除；這三種都可以用來(lái)刪除數(shù)據(jù)，但用于的場(chǎng)景不同。

深入追問(wèn)：

追問(wèn)1：說(shuō)一下 delete、truncate、drop的區(qū)別吧

一、從執(zhí)行速度上來(lái)說(shuō)

drop > truncate >> DELETE

二、從原理上講

DELETE

DELETE from TABLE_NAME where xxx

1.DELETE屬于數(shù)據(jù)庫(kù)DML操作語(yǔ)言，只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)，會(huì)走事務(wù)，執(zhí)行時(shí)會(huì)觸發(fā)trigger；

2.在 InnoDB 中，DELETE其實(shí)并不會(huì)真的把數(shù)據(jù)刪除，mysql 實(shí)際上只是給刪除的數(shù)據(jù)打了個(gè)標(biāo)記為已刪除，因此 delete 刪除表中的數(shù)據(jù)時(shí)，表文件在磁盤上所占空間不會(huì)變小，存儲(chǔ)空間不會(huì)被釋放，只是把刪除的數(shù)據(jù)行設(shè)置為不可見。雖然未釋放磁盤空間，但是下次插入數(shù)據(jù)的時(shí)候，仍然可以重用這部分空間（重用 → 覆蓋）。

3.DELETE執(zhí)行時(shí)，會(huì)先將所刪除數(shù)據(jù)緩存到rollback segement中，事務(wù)commit之后生效;

4.delete from table_name刪除表的全部數(shù)據(jù),對(duì)于MyISAM 會(huì)立刻釋放磁盤空間，InnoDB 不會(huì)釋放磁盤空間;

5.對(duì)于delete from table_name where xxx 帶條件的刪除, 不管是InnoDB還是MyISAM都不會(huì)釋放磁盤空間;

6.delete操作以后使用 optimize table table_name 會(huì)立刻釋放磁盤空間。不管是InnoDB還是MyISAM 。所以要想達(dá)到釋放磁盤空間的目的，delete以后執(zhí)行optimize table 操作。

7.delete 操作是一行一行執(zhí)行刪除的，并且同時(shí)將該行的的刪除操作日志記錄在redo和undo表空間中以便進(jìn)行回滾（rollback）和重做操作，生成的大量日志也會(huì)占用磁盤空間。

• truncate

Truncate table TABLE_NAME

1.truncate：屬于數(shù)據(jù)庫(kù)DDL定義語(yǔ)言，不走事務(wù)，原數(shù)據(jù)不放到 rollback segment 中，操作不觸發(fā) trigger。

執(zhí)行后立即生效，無(wú)法找回

執(zhí)行后立即生效，無(wú)法找回

執(zhí)行后立即生效，無(wú)法找回

2.truncate table table_name 立刻釋放磁盤空間 ，不管是 InnoDB和MyISAM 。truncate table其實(shí)有點(diǎn)類似于drop table 然后creat,只不過(guò)這個(gè)create table 的過(guò)程做了優(yōu)化，比如表結(jié)構(gòu)文件之前已經(jīng)有了等等。所以速度上應(yīng)該是接近drop table的速度;

3.truncate能夠快速清空一個(gè)表。并且重置auto_increment的值。

但對(duì)于不同的類型存儲(chǔ)引擎需要注意的地方是： 對(duì)于MyISAM，truncate會(huì)重置auto_increment（自增序列）的值為1。而delete后表仍然保持auto_increment。

對(duì)于InnoDB，truncate會(huì)重置auto_increment的值為1。delete后表仍然保持auto_increment。但是在做delete整個(gè)表之后重啟MySQL的話，則重啟后的auto_increment會(huì)被置為1。

也就是說(shuō)，InnoDB的表本身是無(wú)法持久保存auto_increment。delete表之后auto_increment仍然保存在內(nèi)存，但是重啟后就丟失了，只能從1開始。實(shí)質(zhì)上重啟后的auto_increment會(huì)從 SELECT 1+MAX(ai_col) FROM t 開始。

4.小心使用 truncate，尤其沒(méi)有備份的時(shí)候，如果誤刪除線上的表，記得及時(shí)聯(lián)系中國(guó)民航，訂票電話：400-806-9553

• drop

Drop table Tablename

1.drop：屬于數(shù)據(jù)庫(kù)DDL定義語(yǔ)言，同Truncate；

執(zhí)行后立即生效，無(wú)法找回

執(zhí)行后立即生效，無(wú)法找回

執(zhí)行后立即生效，無(wú)法找回

2.drop table table_name 立刻釋放磁盤空間 ，不管是 InnoDB 和 MyISAM; drop 語(yǔ)句將刪除表的結(jié)構(gòu)被依賴的約束(constrain)、觸發(fā)器(trigger)、索引(index); 依賴于該表的存儲(chǔ)過(guò)程/函數(shù)將保留,但是變?yōu)?invalid 狀態(tài)。

3.小心使用 drop ，要?jiǎng)h表跑路的兄弟，請(qǐng)?jiān)谟喥背晒笤趫?zhí)行操作！訂票電話：400-806-9553

可以這么理解，一本書，delete是把目錄撕了，truncate是把書的內(nèi)容撕下來(lái)燒了，drop是把書燒了

總結(jié)

本篇文章就到這里了，希望能給你帶來(lái)幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容！

最新評(píng)論