分類: SQL

淺談MySQL與PostgreSQL是如何處理併發比較

SQL資料庫
Andy Wu

最近在公司支援另一個採用PostgreSQL當資料庫的專案，雖然從語句面或使用ORM來看的話差異與熟悉的MySQL不大，但是當從併發的角度來看待效能時，才發現其實底層實現鎖的機制大不相同。本篇，我們會拿一個在實際專案中發生的簡化版情境，來介紹MySQL與PostgreSQL併發時底層實現鎖的運作過程。

Read More

使用Sequelize進行資料庫migration的基本用法

SQL資料庫
Andy Wu

開發時大家都會使用git為程式碼做版控，好讓不同開發環境以及佈署環境的程式得以統一。今天我們來體驗一下一個叫Sequelize的DB migration工具來為我們的DB schema做版控。

把DB schema版控起來最大的好處是DB schema就可以像程式碼一樣在各個環境都是統一的，不會發生在stg環境驗完測完沒問題的程式因為上到prod環境時忘記加欄位導致prod環境的錯誤狂噴。再來是對於新加入專案的開發人員非常友好，因為開發人員只要使用migration工具下完up指令就可以將本機的資料庫建好很快就可以進入開發，甚至也可以指定版本建出幾天前的舊版DB schema。那接下來就進入我們今天的主題吧。

Read More

淺談MySQL隔離層級為RR(可重復讀)時不能避免Phantom Read(幻讀)

SQL資料庫
Andy Wu在〈淺談MySQL隔離層級為RR(可重復讀)時不能避免Phantom Read(幻讀)〉中有 1 則留言

情境模擬

我們先來破冰一下，幫大家再回憶一次什麼是Phantom Read(幻讀)。假設今天水果行採用了一個庫存系統，其中商品庫存資料表如下:

CREATE TABLE `items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(16)),
  `remaining` INT NOT NULL DEFAULT 0,
  `is_enabled` INT NOT NULL DEFAULT 0
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

插入幾筆水果庫存的測試資料:

INSERT INTO `items`
  (`name`, `remaining`, `is_enabled`)
VALUES
  ('apple', 20, 1),
  ('banana', 10, 0),
  ('cherry', 0, 0),
  ('guava', 0, 0),
  ('peach', 30, 1);

偷看一下資料表:

mysql> SELECT * FROM items;
+----+--------+-----------+------------+
| id | name   | remaining | is_enabled |
+----+--------+-----------+------------+
|  1 | apple  |        20 |          1 |
|  2 | banana |        10 |          0 |
|  3 | cherry |         0 |          0 |
|  4 | guava  |         0 |          0 |
|  5 | peach  |        30 |          1 |
+----+--------+-----------+------------+
5 rows in set (0.00 sec)

某天，老闆在貨車抵達門口進完貨後，走回自己辦公桌的電腦前面，想看看目前上架的水果狀態。

(水果行老闆為什麼會SQL不是本篇重點)

Read More

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

SQL資料庫
Andy Wu在〈[MySQL / MariaDB] 優化查詢語句OFFSET越大時間越久的問題〉中有 2 則留言

開發實務上，很常會遇到需要做分頁的需求，並且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越多查詢時間會呈現線性增長嘛@@?! 沒錯，你猜對了。

Read More

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

SQL資料庫
Andy Wu在〈[MySQL / MariaDB] 取得每個group的前N筆資料〉中有 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;
Read More