標籤: 資料庫

能夠做到平行運算的 ClickHouse Distributed Table Engine

分散式系統資料庫
Andy Wu

公司原本只有使用 OLTP 的資料庫，直到這一年來資料量越來越多導致報表類的 query 越查越慢。於是開始研究起 OLAP 的資料庫，希望可以把報表類的 query 拆出去，讓 OLTP 的資料庫專注在 OLTP 的業務上。於是今天要來分享最近在研究的 OLAP 資料庫叫 clickhouse，它有一種能做到平行運算的 distributed table engine，並且測試他在撈上億筆的資料的性能如何。

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

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

資料庫
Andy Wu

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

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

資料庫
Andy Wu

當服務異常緩慢，想要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)
Read More