標籤: 資料庫

[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

[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

淺談資料庫正規化

資料庫
Andy Wu在〈淺談資料庫正規化〉中有 11 則留言

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

 

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

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

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

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

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

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

Read More

[MySQL / MariaDB] CSV檔匯入資料表

SQL資料庫
Andy Wu在〈[MySQL / MariaDB] CSV檔匯入資料表〉中有 2 則留言

最近有從網路上下載CSV檔案，並且匯入資料庫準備拿來做分析的需求

一開始嘗試用phpMyAdmin裡的匯入功能

但是因為要經過分析轉譯，匯入速度實在不敢恭維

加上登入的session又會timeout，好不容易跑了10萬筆就被中斷，功虧一簣Orz

嘗試了數種方法，最後還是決定回到正統用SQL語法操作的方式。

LOAD DATA LOCAL INFILE  '/path/to/example.csv'
INTO TABLE table_name
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

P.S.

  • 用terminal進mysql後，記得先use myDatabase;到正確的資料庫下再進行操作。

  • 換行符號因作業系統而異，也有可能是\r\n。

Read More

[資料庫] 關聯介紹 一對一、一對多、多對多

資料庫
Andy Wu在〈[資料庫] 關聯介紹 一對一、一對多、多對多〉中有 20 則留言

前言

會想要寫這篇是因為最近在摸php框架laravel

在ORM的地方間接學到了資料庫的Relationships

才發現以前好傻好天真，竟然規劃出這樣的table...

別懷疑我真的幹過這種蠢事@@

posts跟tags其實是多對多的關係

應該要建立一個intermediary table才是正確的做法((以下會詳細說明

因此，必須好好筆記一下最近學到的資料庫Relationships

Read More