Tag: join

用 GPT-3 直接產生對應的 SQL query

在 Hacker News Daily 上看到的東西，直接給 GPT-3 條件，叫 GPT-3 給出對應的 SQL query，這就有點誇張了...：「Automating My Job with GPT-3」。

這樣丟進去：

Instruction: Given an input question, respond with syntactically correct PostgreSQL. Be creative but the SQL must be correct.

Input: how many users signed up in the past month?

然後這樣出來：

GPT-3 Response: SELECT COUNT(*) FROM users
WHERE signup_time > now() - interval '1 month'

把欄位名稱替換掉就可以用了... 而且接下來就更誇張了，直接要求 GPT-3 也要照著設定的 table schema 給答案：

Instruction: Given an input question, respond with syntactically correct PostgreSQL. Be creative but the SQL must be correct. Only use tables called "users" and "charges". The "users" table has columns: id (integer), signup_dt (timestamp), email (character varying), and plan_type (character varying). The "charges" table has columns: amount (bigint), user_id (integer), and charge_dt (timestamp).

Input: how much revenue did we have in the past 7 days?

然後輸出了：

GPT-3 Response: SELECT SUM(amount) FROM charges WHERE charge_dt > now() - interval '7 days'

接下來是在同樣 instruction 下，跨表格的問題：

Input: how much revenue have we had from users that signed up in the last 6 months?

這時候 INNER JOIN 就跑出來了：

.8 Temperature GPT-3 Response: SELECT SUM(charges.amount) FROM users INNER JOIN charges ON users.id = charges.user_id WHERE signup_dt >= DATE_SUB(now(), INTERVAL '6 months')

後面的問題也很精彩，看起來之後可以接上 BI dashboard，直接丟句子進去，然後拉各種資料出來視覺化？

Amazon Aurora (MySQL) 推出的 Asynchronous Key Prefetch

Amazon Aurora (MySQL) 推出新的效能改善，可以改善 JOIN 時的效能：「Amazon Aurora (MySQL) Speeds Join Queries by More than 10x with Asynchronous Key Prefetch」。

看起來像是某個情況的 optimization，將可能的 random access 換成 sequential access 而得到大量的效能：

This feature applies to queries that require use of the Batched Key Access (BKA) join algorithm and Multi-Range Read (MRR) optimization, and improves performance when the underlying data set is not in the main memory buffer pool or query cache.

其實記憶體還是最好用的加速器，能加大硬拼就先硬拼... XD

SQL 的各種 JOIN

SQL 的各種 JOIN 其實是個很好玩的主題，不是很難，但沒有仔細練習過一次通常只能答出常用的那幾種... 而且過一陣子又會忘記 XD

這幾天不知道哪邊看到的舊文章「Say NO to Venn Diagrams When Explaining JOINs」，這篇作者大聲疾呼不要用 Venn diagram 解釋 SQL 的 JOIN，把比較特別的 JOIN 拿出來舉例...

因為 Venn diagram 是講集合交聯集這類的操作，但 JOIN 不僅僅是如此... 舉例來說，CROSS JOIN 對應到 cartesian product 就沒辦法用 Venn diagram 簡單的表示出來，而必須畫的更「具體」：

Relational Database System (RDBMS) 運作的方式

在「How does a relational database work」這篇文章用了很長的篇幅講「資料庫如何把 SQL query 轉換為實際的操作」：

I’ll focus on what I think is essential: the way a database handles an SQL query.

資料庫也是人寫出來的，資料結構與演算法也是人設計出來的。你現在手上有資料，要怎麼把 SQL query 變成有效率的查詢操作行為，就是這篇文章在描述的。

看起來連 JOIN 的機制也講了不少...

MySQL 在處理 GROUP BY 時選擇 index 的效率問題

Percona 的「Speed up GROUP BY queries with subselects in MySQL」這篇講了 MySQL 在處理 GROUP BY 的效率問題。

舉原文的例子，也就是這樣的 SQL query：(我把 command 都改成大寫，其他部份都改成小寫)

SELECT a.name, SUM(a.count) a_sum, AVG(a.position) a_avg, b.col1, c.col2, d.col3
FROM
a JOIN
b ON (a.bid = b.id) JOIN
c ON (a.cid = c.id) JOIN
d ON (a.did = d.id) GROUP BY a.name, b.id, c.id, d.id

其中 TABLE a 有 1.3M rows，而 b、c、d 都只有 5 rows。

由於會需要計算每組 (a.name, b.id, c.id, d.id) 的 SUM(a.count) 與 AVG(a.position)，不可避免的是需要對 TABLE a 完整的掃一次。所以效能的改善會在於可以減少 temporily table 的大小。

上面這組 SQL query 會先 JOIN 完後再 GROUP BY，也就是會全部先展開後再 GROUP BY。

由於 GROUP BY 所使用到的條件都可以在 TABLE a 裡面找到，所以這邊可以先 GROUP BY 後再 JOIN，降低 temporily table 的大小：

SELECT a.name, a_sum, a_avg, b.col1, c.col2, d.col3
FROM
(SELECT name, SUM(count) a_sum, AVG(position) a_avg, bid, cid, did
 FROM a
 GROUP BY name, bid, cid, did) a JOIN
 b ON (a.bid = b.id) JOIN
 c ON (a.cid = c.id) JOIN
 d ON (a.did = d.id)

原文測試出來的結果是從 2.3 秒降到 1.8 秒：

The first query took 2.3 sec avg and the optimized query took 1.8 sec average, half a second faster.

另外一個改善是再加上 covering index：

ALTER TABLE a ADD INDEX (name, bid, cid, did, count, position);

加上去之後，原來的 query 變成 1.9 秒，而改善後的變成 0.7 秒，速度快很多。不過這是 trade-off，多了這個 index 在寫入時的成本也會增加。

MySQL 的 SQL Query...

Percona 的 blog 上跑出一篇「What kind of queries are bad for MySQL?」，分析哪些 SQL query 對 MySQL 的效能不好...

內容相當偏激，然後在 comment 引起討論... (文章作者反而就跑去休息了)

舉例來說，他認為只要有 JOIN 就算是 bad query。作者舉了這樣的 SQL query 為例：

SELECT t2.value FROM t2 JOIN t1 ON (t1.id=t2.tid) WHERE t1.orderdate=NOW()

應該要被展開成：

SELECT t2.value FROM t2 WHERE t2.orderdate=NOW()

看得出來有重度 denormalize 與 cache 症狀... XD

MySQL 內 JOIN 的應用...

「Common use cases for the MySQL Join statement」這篇給的範例與把 MySQL 上常用到的幾種 JOIN 提出來分析，包括 index 與 explain。另外在「Managing hierarchical data with MySQL」也提到了要怎麼處理階層式資料。

對於 JOIN 大概分幾個階段：

  • 在使用 MyISAM 的時候會儘量避免 JOIN，因為當 SQL 執行時間久的時候會有好幾個 table 同時卡住無法寫入。
  • 改用 InnoDB 後一直用 JOIN，不論是報表或是 web SQL query，造成 CPU bound (雖然不是 table lock，但在 MVCC 架構下，讀取也還是有 lock 成本存在)。
  • 把 JOIN 形式的 web SQL query 拆成多個 SQL query 以降低 lock 成本；針對計算成本很高的結果 cache，再針對 cache 效果不佳的表格逆正規化。只有報表不受限使用 JOIN。

後面的階段則是 data sharding 與 NoSQL。不過每個階段的界線不是那麼明顯，有時候會重疊在一起...

說到逆正規化，MariaDB 的 Virtual Columns + PERSIST 好像是逆正規化的好工具，再來測仔細一點...