面試題1：有個需求需要快速刪除MySQL表中一億條數(shù)據(jù)，表中有2億數(shù)據(jù)，能說一下你的思路么？

我們知道MySQL刪除數(shù)據(jù)的方式有多種比如DELETE、TRUNCATE、DROP等，都屬于物理刪除，但在實際場景中，很多時候是不能直接用的。

比如咱們說的這張表中有2億條數(shù)據(jù)，要刪其中1億條，以InnoDB引擎為例，直接加WHERE條件DELETE是不現(xiàn)實的，因為DELETE是一條一條刪，要把操作記錄到binlog日志（前提開啟了binlog），刪除少量數(shù)據(jù)還可以，刪除1億條會非常慢，并且不會釋放出磁盤空間，還得用optimize或repair來壓縮數(shù)據(jù)表來釋放硬盤空間。如果字段內(nèi)容多，IO成本很高，CPU各種過高，耗時更長，不可行。

如果我用truncate刪數(shù)據(jù)，速度倒是很快，能直接釋放磁盤空間，但這是全表數(shù)據(jù)刪除，直接清空數(shù)據(jù)文件或分區(qū)磁盤空間，剩下不用刪的1億條又怎么辦呢？

因此我們要根據(jù)實際情況入手，比如表中有2億條數(shù)據(jù)，要刪其中1億條；

方法一：著急的話，可以臨時使用邏輯刪除，選一個字段或加一個字段如is_deleted，作為邏輯刪除標(biāo)志，然后通過該字段過濾后繼續(xù)處理數(shù)據(jù)，等到夜深人靜。。。

方法二：可以新建一張表結(jié)構(gòu)一致的表，把不用刪除的數(shù)據(jù)導(dǎo)進(jìn)去，然后drop原表，再把新表名稱改為原表名稱；這是一種比較常見的方式，要注意的是，如果索引較多或索引字段較大的話，記得先導(dǎo)數(shù)據(jù)，再加索引，別問我為什么。

我們簡單看一下方法二的操作流程：

1、復(fù)制表+刪除索引

create table new_T like T;
ALTER TABLE new_T DROP INDEX index_name1;
ALTER TABLE new_T DROP INDEX index_name2;

2、分批插入數(shù)據(jù)

建議為10w-50w一次，根據(jù)你的MySQL服務(wù)器性能來定，可以按ID查詢后插入，也可以null as id按新順序插入。

如：

insert into new_T (select null as id,col1,col2,col3 from T where id>500000 and id<=600000);
insert into new_T (select null as id,col1,col2,col3 from T where id>600000 and id<=700000);
...

3、drop刪除掉老表

drop table T;

4、重命名新表為new_T

alter table new_T rename to T;

面試題2：剛才你提到了邏輯刪除，你是怎么看邏輯刪除和物理刪除的？

我理解物理刪除指的將數(shù)據(jù)從磁盤中真實刪除，而邏輯刪除則是在代碼層面的，多是將刪除數(shù)據(jù)行的is_deleted字段置成1，后續(xù)只操作is_deleted=0的那些未被刪除的數(shù)據(jù)。

在嚴(yán)謹(jǐn)?shù)拈_發(fā)環(huán)境中，實際上是沒有刪除這一說的。訂單作廢，用戶禁用，優(yōu)惠券作廢都是狀態(tài)的變化，而不應(yīng)該讓數(shù)據(jù)從磁盤消失。所以 SQL 里面 DELETE 在真實生產(chǎn)環(huán)境里都不應(yīng)該出現(xiàn)，除DBA賬號外的其他用戶不應(yīng)有DELETE、TRUNCATE、DROP權(quán)限，而只有UPDATE權(quán)限。

我還見過有單位將MySQL的update命令封裝成刪除命令的~~然后用戶只能看到表中或視圖中is_deleted=0的數(shù)據(jù)，好像是實現(xiàn)了權(quán)限隔離，其實就是閑的慌。

當(dāng)然了，邏輯刪除其實也是有問題的，邏輯刪除的設(shè)計還會導(dǎo)致常用的unique key 失效；臟數(shù)據(jù)量大的時候研發(fā)人員可能會發(fā)現(xiàn)表中明明數(shù)據(jù)不多，但檢索速度很慢，可能會誤導(dǎo)研發(fā)人員的維護(hù)工作。

面試題3：大型項目中，mysql的主鍵需要全局唯一怎么辦？

在只使用單數(shù)據(jù)庫時，使用自增主鍵ID無疑是最適合的。但在集群、主從架構(gòu)上時就會有一些問題，比如怎么做到主鍵的全局唯一。

使用UUID

這時我們首先想到的解決方法可能是以UUID為主鍵，對于InnoDB這種聚集主鍵類型的引擎來說，數(shù)據(jù)會按照主鍵進(jìn)行排序，由于UUID的無序性，InnoDB會產(chǎn)生巨大的IO壓力，此時不適合使用UUID做物理主鍵，但可以把它作為邏輯主鍵，物理主鍵依然使用自增ID。

另外，innodb會對主鍵進(jìn)行物理排序，這對auto_increment_int是個好消息，因為后一次插入的主鍵位置總是在最后。但是對uuid來 說，這卻是個壞消息，因為uuid是雜亂無章的，每次插入的主鍵位置是不確定的，可能在開頭，也可能在中間，在進(jìn)行主鍵物理排序的時候，勢必會出現(xiàn)大量 IO操作影響效率。

使用自增ID

• 方法一：比如4臺數(shù)據(jù)庫，第一臺mysql主鍵從1開始每次加4，第二臺從2開始每次加4，以此類推。這里的4代表數(shù)據(jù)庫總量，我們叫它步長，從而保證主鍵的全局唯一，我們需要保證的就是確保自增 ID 起始點（auto_increment_offset）以及 ID 自增步長（auto_increment_increment）的正確性即可。

修改MySQL默認(rèn)自動增長的步長

-- 設(shè)置序列的增長值
set global auto_increment_increment=1; 

• 方法二：通過集群編號加集群內(nèi)的自增（auto_increment類型）兩個字段共同組成唯一主鍵。優(yōu)點是實現(xiàn)簡單，維護(hù)也比較簡單，對應(yīng)用透明。缺點是引用關(guān)聯(lián)操作相對比較復(fù)雜，需要兩個字段，主鍵占用空間較大，在使用 InnoDB 的時候這一點的副作用很明顯（但是這方式僅僅多了一個smallint兩個字節(jié)，100W也就多2M）。

總結(jié)

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

最新評論