淺談MySQL與PostgreSQL是如何處理併發比較
最近在公司支援另一個採用PostgreSQL當資料庫的專案，雖然從語句面或使用ORM來看的話差異與熟悉的MySQL不大，但是當從併發的角度來看待效能時，才發現其實底層實現鎖的機制大不相同。本篇，我們會拿一個在實際專案中發生的簡化版情境，來介紹MySQL與PostgreSQL併發時底層實現鎖的運作過程。
淺談MySQL隔離層級為RR(可重復讀)時不能避免Phantom Read(幻讀)
情境模擬
我們先來破冰一下，幫大家再回憶一次什麼是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不是本篇重點)
[MySQL / MariaDB] 優化查詢語句OFFSET越大時間越久的問題
開發實務上，很常會遇到需要做分頁的需求，並且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資料後未被釋放的空間
最近發生公司的測試環境硬碟容量不足，導致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'; 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)
近期迴響