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

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

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

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

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

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

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

我們簡(jiǎn)單看一下方法二的操作流程：

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ù)器性能來(lái)定，可以按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í)刪除，而邏輯刪除則是在代碼層面的，多是將刪除數(shù)據(jù)行的is_deleted字段置成1，后續(xù)只操作is_deleted=0的那些未被刪除的數(shù)據(jù)。

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

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

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

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

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

使用UUID

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

另外，innodb會(huì)對(duì)主鍵進(jìn)行物理排序，這對(duì)auto_increment_int是個(gè)好消息，因?yàn)楹笠淮尾迦氲闹麈I位置總是在最后。但是對(duì)uuid來(lái) 說(shuō)，這卻是個(gè)壞消息，因?yàn)閡uid是雜亂無(wú)章的，每次插入的主鍵位置是不確定的，可能在開(kāi)頭，也可能在中間，在進(jìn)行主鍵物理排序的時(shí)候，勢(shì)必會(huì)出現(xiàn)大量 IO操作影響效率。

使用自增ID

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

修改MySQL默認(rèn)自動(dòng)增長(zhǎng)的步長(zhǎng)

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

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

總結(jié)

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

最新評(píng)論