一慢宗、背景
對于訂單這類具有時間屬性的數(shù)據(jù)奔穿,會隨時間累積,數(shù)據(jù)量越來越多贱田,為了提升查詢性能需要對數(shù)據(jù)進行拆分,首選的拆分方法是把舊數(shù)據(jù)歸檔到歷史表中去蔬墩。這種拆分方法能起到很好的效果,更重要的是對系統(tǒng)的改動小拇颅,升級成本低樟插。
很多大廠早年就是靠著此策略,撐了好幾年黄锤。還記得淘寶訂單搜索頁面”只能查詢僅此三個月訂單“么?
二勉吻、歸檔歷史數(shù)據(jù)
所謂歸檔旅赢,其實也是一種拆分數(shù)據(jù)的策略。簡單地說煮盼,就是把大量的歷史訂單移到另外一張歷史訂單表中。為什么這么做呢香到?因為像訂單這類具有時間屬性的數(shù)據(jù)报破,都存在熱尾效應(yīng)。大多數(shù)情況下訪問的都是最近的數(shù)據(jù)充易,但訂單表里面大量的數(shù)據(jù)都是不怎么常用的老數(shù)據(jù)。
因為新數(shù)據(jù)只占數(shù)據(jù)總量中很少的一部分炸茧,所以把新老數(shù)據(jù)分開之后稿静,新數(shù)據(jù)的數(shù)據(jù)量就會少很多,查詢速度也就會快很多控漠。老數(shù)據(jù)雖然和之前比起來沒少多少悬钳,查詢速度提升不明顯柬脸,但是毙驯,因為老數(shù)據(jù)訪很少會被訪問到灾测,所以慢一點兒也問題不大。
1.好處
拆分訂單時媳搪,2需要改動的代碼非常少
不需要修改的代碼
- 大部分對訂單表的操作都是在訂單完成之前,這些業(yè)務(wù)邏輯都是完全不用修改的序愚。
- 像退貨退款這類訂單完成后的操作等限,也是有時限的,那這些業(yè)務(wù)邏輯也不需要修改
需要修改的代碼
- 查詢統(tǒng)計類功能形娇,會查歷史訂單
按照時間筹误,選擇去訂單表還是歷史訂單表查詢就可以了。
2.具體操作方式
2.1不可以停服務(wù)
- 創(chuàng)建于訂單表結(jié)構(gòu)一致的歷史訂單表
- 把訂單表中數(shù)據(jù)分批查詢出來哄酝,插入歷史訂單表中祷膳。
技術(shù)手段不限,不過如果是數(shù)據(jù)庫是主從分離的钾唬。請在從庫中查詢訂單數(shù)據(jù),再寫到主庫歷史訂單表中去奕巍,可以減少一些主庫的壓力 - 不要著急刪除老訂單數(shù)據(jù)儒士,先驗證新代碼是否有bug,是否支持線上業(yè)務(wù)着撩。
- 不要著急刪除老訂單數(shù)據(jù)匾委,先驗證新代碼是否有bug氓润。
- 不要著急刪除老訂單數(shù)據(jù),先驗證新代碼是否有bug, 是否支持線上業(yè)務(wù)挨措。
如果有bug 可以理解回滾代碼崩溪,不至于影響線上業(yè)務(wù)。 - 等新版本代碼上線并驗證無誤之后觉既,就可以刪除訂單表中的歷史訂單數(shù)據(jù)了乳幸。
- 上線一個定時腳本或定時任務(wù),定期把過期訂單數(shù)據(jù)從訂單表搬到歷史訂單表中反惕。
與訂單表相關(guān)的訂單子表也作如上操作,畢竟是根據(jù)訂單表id關(guān)聯(lián)背亥,伴隨增長的悬赏。
2.2可以停服務(wù)
簡單粗暴一點,近乎于重建了一次訂單表闽颇,不需要漫長的刪除歷史訂單的過程了。
- 直接新建一張臨時訂單表
- 將當前訂單表數(shù)據(jù)復(fù)制到臨時訂單表中
- 把舊的訂單表改名尖啡,最后把臨時訂單表的表名改成正式訂單表
-- 新建一個臨時訂單表
create table orders_temp like orders;
-- 把當前訂單復(fù)制到臨時訂單表中
insert into orders_temp
select * from orders
where timestamp >= SUBDATE(CURDATE(),INTERVAL 3 month);
-- 修改替換表名
rename table orders to orders_to_be_droppd, orders_temp to orders;
-- 刪除舊表
drop table orders_to_be_dropp
三剩膘、 刪除大量數(shù)據(jù)
一次性刪除大量數(shù)據(jù),會提示刪除錯誤畏梆。所以需要分批刪除,以下以每1000條記錄刪除奠涌,建議執(zhí)行一次停頓一會,不要給數(shù)據(jù)庫造成太大壓力捏卓。
delete from orders
where timestamp < SUBDATE(CURDATE(),INTERVAL 3 month)
order by id limit 1000;
sql優(yōu)化 : 每次都要進行對大于時間戳的所有數(shù)據(jù)進行排序慈格,其實沒有必要
# 查詢符合的最大id
select max(id) from orders
where timestamp < SUBDATE(CURDATE(),INTERVAL 3 month);
# 符合小于id的數(shù)據(jù)進行排序,這數(shù)據(jù)量就減少很多
delete from orders
where id <= ?
order by id limit 1000;
為什么要排序呢?
為什么在刪除語句中非得加一個排序呢汤功?因為按 ID 排序后溜哮,我們每批刪除的記錄,基本都是 ID 連續(xù)的一批記錄茂嗓,由于 B+ 樹的有序性,這些 ID 相近的記錄忿族,在磁盤的物理文件上蝌矛,大致也是放在一起的,這樣刪除效率會比較高入撒,也便于 MySQL 回收頁。
四璃赡、 磁盤整理
大量的歷史訂單數(shù)據(jù)刪除完成之后献雅,如果你檢查一下 MySQL 占用的磁盤空間,你會發(fā)現(xiàn)它占用的磁盤空間并沒有變小豆励,這是什么原因呢?
只是指針標記為空閑良蒸,實際存儲單元未釋放。 一切為了速度
雖然邏輯上每個表是一顆 B+ 樹剿吻,但是物理上串纺,每條記錄都是存放在磁盤文件中的,這些記錄通過一些位置指針來組織成一顆 B+ 樹纺棺。當 MySQL 刪除一條記錄的時候,只能是找到記錄所在的文件中位置祷蝌,然后把文件的這塊區(qū)域標記為空閑,然后再修改 B+ 樹中相關(guān)的一些指針米丘,完成刪除糊啡。其實那條被刪除的記錄還是躺在那個文件的那個位置,所以并不會釋放磁盤空間棚蓄。
這么做也是沒有辦法的辦法,因為文件就是一段連續(xù)的二進制字節(jié)挣柬,類似于數(shù)組睛挚,它不支持從文件中間刪除一部分數(shù)據(jù)。如果非要這么刪除侧到,只能是把這個位置之后的所有數(shù)據(jù)往前挪淤击,這樣等于是要移動大量數(shù)據(jù),非常非常慢污抬。所以绳军,刪除的時候矢腻,只能是標記一下,并不真正刪除奶是,后續(xù)寫入新數(shù)據(jù)的時候再重用這塊兒空間竣灌。
對于InnoDB 釋放磁盤存儲
OPTIMIZE TABLE tablename
OPTIMIZE TABLE 注意點:
- 把這個表重建一遍,執(zhí)行過程中會一直鎖表及汉,也就是說這個時候下單都會被卡住
- MySQL 的配置必須是每個表獨立一個表空間(innodb_file_per_table = ON)屯烦,如果所有表都是放在一起的,執(zhí)行 OPTIMIZE TABLE 也不會釋放磁盤空間漫贞。
- 索引也會重建
數(shù)據(jù)和索引都會變得緊湊迅脐,不僅占用磁盤空間減少豪嗽,查詢效率還提升不少。