MariaDB – AndyWu's Notes

開發實務上，很常會遇到需要做分頁的需求，並且API提供page跟limit參數可以帶，接著再根據參數去DB撈取對應區段的資料。例如今天我們寫了個撈取卡片列表的API，在API請求進來後得到希望查詢以最新創建時間排序、在分頁第4頁(每頁顯示10筆)的卡片資訊，經過轉換後，後端程式送出了以下SQL語法給DB查詢。

SELECT
    id,
    content,
    createTime
FROM card
ORDER BY createTime DESC
LIMIT 10 OFFSET 30;

這是我們目標查詢的cardtable的結構：

CREATE TABLE card (
  id INT NOT NULL AUTO_INCREMENT,
  content TEXT,
  createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_createTime (createTime)
);

得到DB返回：

+--------+--------------+-------------+
| id     | content      | createTime  |
+--------+--------------+-------------+
| 999970 | (以下省略)... | (以下省略)... |
| 999969 | ...          | ...         |
| 999968 | ...          | ...         |
| 999967 | ...          | ...         |
| 999966 | ...          | ...         |
| 999965 | ...          | ...         |
| 999964 | ...          | ...         |
| 999963 | ...          | ...         |
| 999962 | ...          | ...         |
| 999961 | ...          | ...         |
+--------+--------------+-------------+
10 rows in set (0.01 sec)

撈出來的資料沒什麼問題，但如果今天卡片數量很多，例如範例中的table總共有100萬筆資料，而我們又剛好想撈取在中後面的分頁的卡片(就像你在凌晨四點把迷片網站翻到第10001頁一樣)：

SELECT
    id,
    content,
    createTime
FROM card
ORDER BY createTime DESC
LIMIT 10 OFFSET 100000;

+--------+--------------+-------------+
| id     | content      | createTime  |
+--------+--------------+-------------+
| 900000 | (以下省略)... | (以下省略)... |
| 899999 | ...          | ...         |
| 899998 | ...          | ...         |
| 899997 | ...          | ...         |
| 899996 | ...          | ...         |
| 899995 | ...          | ...         |
| 899994 | ...          | ...         |
| 899993 | ...          | ...         |
| 899992 | ...          | ...         |
| 899991 | ...          | ...         |
+--------+--------------+-------------+
10 rows in set (2.09 sec)

奇怪，怎麼查詢時間瞬間從0.01秒增加至2.09秒這麼多?!那如果我們再查詢更後面的分頁呢，例如第90001頁會發生什麼事：

SELECT
    id,
    content,
    createTime
FROM card
ORDER BY createTime DESC
LIMIT 10 OFFSET 900000;

+--------+--------------+-------------+
| id     | content      | createTime  |
+--------+--------------+-------------+
| 100000 | (以下省略)... | (以下省略)... |
|  99999 | ...          | ...         |
|  99998 | ...          | ...         |
|  99997 | ...          | ...         |
|  99996 | ...          | ...         |
|  99995 | ...          | ...         |
|  99994 | ...          | ...         |
|  99993 | ...          | ...         |
|  99992 | ...          | ...         |
|  99991 | ...          | ...         |
+--------+--------------+-------------+
10 rows in set (5.44 sec)

阿娘喂5.44秒是怎麼一回事，看起來OFFEST越多查詢時間會呈現線性增長嘛@@?! 沒錯，你猜對了。

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

資料庫

InnoDB MariaDB MySQL 資料庫

Andy Wu7 月 6, 2021有 1 則留言

最近發生公司的測試環境硬碟容量不足，導致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來偵錯

資料庫

MariaDB MySQL 資料庫

Andy Wu6 月 15, 2021

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

啟用slow query log的機制

> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.000 sec)

(此篇直接用SET GLOBAL VARIABLES的方式設定的變數在MySQL重新啟動後會回復預設值，如果想要永久修改必須帶在啟動參數或修改設定檔)

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

> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.000 sec)

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

> SET GLOBAL log_output = 'FILE';
Query OK, 0 rows affected (0.000 sec)

查看log存檔位置

> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log_file | ubuntu-slow.log |
+---------------------+-----------------+
1 row in set (0.001 sec)

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

> SELECT SLEEP(6);
+----------+
| SLEEP(6) |
+----------+
|        0 |
+----------+
1 row in set (6.000 sec)

打開slow query log的存檔 /var/lib/mysql/ubuntu-slow.log (根據作業系統每個人路徑不一定跟此篇一樣)，我們可以看到剛剛的slow query被正確的記錄了:)

...
# Time: 210615 17:56:55
# User@Host: root[root] @ localhost []
# Thread_id: 11694  Schema:   QC_hit: No
# Query_time: 6.000225  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1652003815;
SELECT SLEEP(6);

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

> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.000 sec)

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

SQL 資料庫

MariaDB MySQL 資料庫

Andy Wu3 月 14, 2021有 1 則留言

最近在工作上為了產報表，需要寫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的陷阱

SQL 資料庫

Deadlock Gap Lock InnoDB MariaDB MySQL Repeatable Read SELECT FOR UPDATE 間距鎖隔離層級

Andy Wu1 月 3, 2021有 11 則留言

最近side project在寫購票系統，因而踩中了這個坑。這個坑其實對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)，但這個欄位不是本篇的重點所以忽略他。

標籤: MariaDB

[MySQL / MariaDB] 優化查詢語句OFFSET越大時間越久的問題

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

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

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

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

環境

關於我

分類

近期文章

近期迴響