Java面試題沖刺第三十天--數(shù)據(jù)庫(6)
面試題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ù)表來釋放硬盤空間。如果字段內容多,IO成本很高,CPU各種過高,耗時更長,不可行。
如果我用truncate刪數(shù)據(jù),速度倒是很快,能直接釋放磁盤空間,但這是全表數(shù)據(jù)刪除,直接清空數(shù)據(jù)文件或分區(qū)磁盤空間,剩下不用刪的1億條又怎么辦呢?
因此我們要根據(jù)實際情況入手,比如表中有2億條數(shù)據(jù),要刪其中1億條;
方法一:著急的話,可以臨時使用邏輯刪除,選一個字段或加一個字段如is_deleted,作為邏輯刪除標志,然后通過該字段過濾后繼續(xù)處理數(shù)據(jù),等到夜深人靜。。。
方法二:可以新建一張表結構一致的表,把不用刪除的數(shù)據(jù)導進去,然后drop原表,再把新表名稱改為原表名稱;這是一種比較常見的方式,要注意的是,如果索引較多或索引字段較大的話,記得先導數(shù)據(jù),再加索引,別問我為什么。
我們簡單看一下方法二的操作流程:
1、復制表+刪除索引
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服務器性能來定,可以按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ù)。
在嚴謹?shù)拈_發(fā)環(huán)境中,實際上是沒有刪除這一說的。訂單作廢,用戶禁用,優(yōu)惠券作廢都是狀態(tài)的變化,而不應該讓數(shù)據(jù)從磁盤消失。所以 SQL 里面 DELETE 在真實生產環(huán)境里都不應該出現(xiàn),除DBA賬號外的其他用戶不應有DELETE、TRUNCATE、DROP權限,而只有UPDATE權限。
我還見過有單位將MySQL的update命令封裝成刪除命令的~~然后用戶只能看到表中或視圖中is_deleted=0的數(shù)據(jù),好像是實現(xiàn)了權限隔離,其實就是閑的慌。
當然了,邏輯刪除其實也是有問題的,邏輯刪除的設計還會導致常用的unique key 失效;臟數(shù)據(jù)量大的時候研發(fā)人員可能會發(fā)現(xiàn)表中明明數(shù)據(jù)不多,但檢索速度很慢,可能會誤導研發(fā)人員的維護工作。
面試題3:大型項目中,mysql的主鍵需要全局唯一怎么辦?
在只使用單數(shù)據(jù)庫時,使用自增主鍵ID無疑是最適合的。但在集群、主從架構上時就會有一些問題,比如怎么做到主鍵的全局唯一。
使用UUID
這時我們首先想到的解決方法可能是以UUID為主鍵,對于InnoDB這種聚集主鍵類型的引擎來說,數(shù)據(jù)會按照主鍵進行排序,由于UUID的無序性,InnoDB會產生巨大的IO壓力,此時不適合使用UUID做物理主鍵,但可以把它作為邏輯主鍵,物理主鍵依然使用自增ID。
另外,innodb會對主鍵進行物理排序,這對auto_increment_int是個好消息,因為后一次插入的主鍵位置總是在最后。但是對uuid來 說,這卻是個壞消息,因為uuid是雜亂無章的,每次插入的主鍵位置是不確定的,可能在開頭,也可能在中間,在進行主鍵物理排序的時候,勢必會出現(xiàn)大量 IO操作影響效率。
使用自增ID
- 方法一:比如4臺數(shù)據(jù)庫,第一臺mysql主鍵從1開始每次加4,第二臺從2開始每次加4,以此類推。這里的4代表數(shù)據(jù)庫總量,我們叫它步長,從而保證主鍵的全局唯一,我們需要保證的就是確保自增 ID 起始點(auto_increment_offset)以及 ID 自增步長(auto_increment_increment)的正確性即可。
修改MySQL默認自動增長的步長
-- 設置序列的增長值 set global auto_increment_increment=1;
- 方法二:通過集群編號加集群內的自增(auto_increment類型)兩個字段共同組成唯一主鍵。優(yōu)點是實現(xiàn)簡單,維護也比較簡單,對應用透明。缺點是引用關聯(lián)操作相對比較復雜,需要兩個字段,主鍵占用空間較大,在使用 InnoDB 的時候這一點的副作用很明顯(但是這方式僅僅多了一個smallint兩個字節(jié),100W也就多2M)。
總結
本篇文章就到這里了,希望能夠給你帶來幫助,也希望您能夠多多關注腳本之家的更多內容!
相關文章
IDEA2020.2.3 "reading maven projects"卡住的問題
這篇文章主要介紹了IDEA2020.2.3 "reading maven projects"卡住的問題及問題原因探究,通過多種方法給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2020-10-10springboot文件上傳時maxPostSize設置大小失效問題及解決
這篇文章主要介紹了springboot文件上傳時maxPostSize設置大小失效問題及解決方案,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-07-07Java使用WatchService監(jiān)控文件內容變化的示例
本篇文章主要介紹了Java使用WatchService監(jiān)控文件變化的示例,非常具有實用價值,需要的朋友可以參考下2017-10-10