Java面試題沖刺第三十天–數據庫(6)

Posted on by

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

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

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

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

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

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

方法二：可以新建一張表結構一致的表，把不用刪除的數據導進去，然後drop原表，再把新表名稱改為原表名稱；這是一種比較常見的方式，要註意的是，如果索引較多或索引字段較大的話，記得先導數據，再加索引，別問我為什麼。

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

1、復制表+刪除索引

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

2、分批插入數據

建議為10w-50w一次，根據你的MySQL服務器性能來定，可以按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：剛才你提到瞭邏輯刪除，你是怎麼看邏輯刪除和物理刪除的？

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

在嚴謹的開發環境中，實際上是沒有刪除這一說的。訂單作廢，用戶禁用，優惠券作廢都是狀態的變化，而不應該讓數據從磁盤消失。所以 SQL 裡面 DELETE 在真實生產環境裡都不應該出現，除DBA賬號外的其他用戶不應有DELETE、TRUNCATE、DROP權限，而隻有UPDATE權限。

我還見過有單位將MySQL的update命令封裝成刪除命令的~~然後用戶隻能看到表中或視圖中is_deleted=0的數據，好像是實現瞭權限隔離，其實就是閑的慌。

當然瞭，邏輯刪除其實也是有問題的，邏輯刪除的設計還會導致常用的unique key 失效；臟數據量大的時候研發人員可能會發現表中明明數據不多，但檢索速度很慢，可能會誤導研發人員的維護工作。

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

在隻使用單數據庫時，使用自增主鍵ID無疑是最適合的。但在集群、主從架構上時就會有一些問題，比如怎麼做到主鍵的全局唯一。

使用UUID

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

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

使用自增ID

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

修改MySQL默認自動增長的步長

-- 設置序列的增長值
set global auto_increment_increment=1;
  • 方法二：通過集群編號加集群內的自增（auto_increment類型）兩個字段共同組成唯一主鍵。優點是實現簡單，維護也比較簡單，對應用透明。缺點是引用關聯操作相對比較復雜，需要兩個字段，主鍵占用空間較大，在使用 InnoDB 的時候這一點的副作用很明顯（但是這方式僅僅多瞭一個smallint兩個字節，100W也就多2M）。

總結

本篇文章就到這裡瞭，希望能夠給你帶來幫助，也希望您能夠多多關註WalkonNet的更多內容！

推薦閱讀：