瀏覽分類:

資料庫

[InnoDB] 要回刪除table資料後未被釋放的空間

最近發生公司的測試環境硬碟容量不足,導致MySQL寫入紀錄失敗。本以為是開發時不小心讓app server寫太多log導致,所以把年代久遠的log file清一清後就沒理它了,沒想到過幾天又出現硬碟容量不足的情況。繼續追查後發現原來MySQL也很肥大,於是也把一些不重要的免洗資料也砍一砍。

奇怪的是,砍完後硬碟使用率竟然沒有下降的跡象,原來這跟DB底層的運作有些關係。當我們下了DELETE FROM table WHERE ...語句後,DB其實只把這些紀錄的存檔註記刪除,並不會真的釋放這些空間還給系統,而是等待下次新增資料時,直接蓋掉舊的資料所佔的空間。

這樣做的好處是可以直接地節省硬碟I/O開銷,想想當我們刪除一筆記錄時,DB把空間還給系統,然後下一筆操作是個INSERT,DB又去把剛剛釋放的空間要回來,這樣在低速裝置一來一往的reclaim過程對要求高效的DB來說不值得。

可是面對今天只是測試環境,被分配到的硬體已經是低配了,我就是想要回那些資料被刪除的空間挪作他用怎麼辦呢。這時,可以執行以下語法:

OPTIMIZE TABLE my_huge_table;

閱讀更多

[MySQL / MariaDB] 使用Slow Query Log來偵錯

當服務異常緩慢,想要debug有沒有可能是哪些SQL拖慢了整體速度時,可以使用MySQL或MariaDB內建的Slow Query Log將執行很久的SQL語句給記錄下來。

啟用slow query log的機制

SET GLOBAL slow_query_log = 'ON';

設定slow query log機制的log儲存方式

SET GLOBAL log_output = 'FILE';

查看log存檔位置

SHOW VARIABLES LIKE '%log_file%';

設定會被當成slow query的門檻值 (單位: 秒)

SET GLOBAL long_query_time = XX;

好了以後,我們來測試看看當發生slow query時有沒有被資料庫正確記錄下來

SELECT SLEEP(YY);

(P.s. YY 秒必須大於上面設定的 XX 秒才會寫紀錄)

最後偵錯完畢時可以將它關閉

SET GLOBAL slow_query_log = 'OFF';

[MySQL / MariaDB] 取得每個group的前N筆資料

最近在工作上為了產報表,需要寫sql去撈每個group的top N筆紀錄。研究了一會兒後最後用了以下的解法。

假設今天有個payment的資料表記錄了每筆交易所涉及的用戶、幣別、金額。

交易id(id) 用戶id(uid) 交易幣別(currency) 金額(amount)
1 11 NTD 800
2 6 NTD 590
3 7 NTD 1020
4 55 USD 24
5 33 USD 27
6 2 USD 25
7 13 JPY 3870
8 10 JPY 2980
9 41 JPY 3780

我們想要取每個幣別的前2筆最大金額的交易記錄,我們可以這樣寫:

SELECT id, uid, currency, amount
FROM (
  SELECT id, uid, currency, amount,
    @counter := IF(@current_currency = currency, @counter + 1, 1) AS counter,
    @current_currency := currency AS current_currency
  FROM payment
  ORDER BY currency, amount DESC
) ordered_result
WHERE counter <= 2;

閱讀更多

select...for update再insert造成deadlock的陷阱

最近在寫購票系統,因而踩中了這個坑。這個坑其實對InnoDB的鎖的觀念不熟悉的人會很難發現造成deadlock的原因,因此想特別寫一篇記錄一下,幫助也在開發類似功能的大家少走一點冤望路。

本篇的講解順序會先帶大家重現我之前引發Deadlock的購票功能邏輯,再來說明資料表使用InnoDB引擎下產生如此Deadlock的原因。

 

環境

  • 本篇範例使用的DB是 MariaDB 10.3.7 (開源版的MySQL)
  • 資料表採用預設的 InnoDB 引擎,隔離層級也是預設的 Repeatable Read
  • 範例資料表ticket的schema如下:
CREATE TABLE `ticket` (
  `id`      int(11) NOT NULL AUTO_INCREMENT,
  `show_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ticket_show_id_IDX` (`show_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

id是主鍵(PK)、show_id為了之後查詢上的需要,有建非唯一索引(non-unique index)、user_id理論上是外鍵(FK),但這個欄位不是本篇的重點所以忽略他。

  閱讀更多

淺談資料庫正規化

把前段時間沒搞懂的第二正規化跟,第三正規化搞懂了,順便筆記一下

 

資料庫的發明,是為了快速讀取或寫入當下所需的資料

像是進入部落格的首頁,伺服器端就會向資料庫query出每篇文章的摘要,再送回用戶的瀏覽器

想像如果等10秒才載入完畢,那我的部落格豈還有人會等著載入完看嗎

因此,將所有資料塞在同個table顯然不一定是好的選擇

而將資料分門別類獨立出不同table儲存的動作即稱為資料庫正規化

適當的資料庫正規化有助於提升query效能
但是當過度得正規化則會讓邊際效益呈負的

閱讀更多

  • 1
  • 2