Tag: by

MySQL 8.0 將會實作「真正的」Descending Indexes

在「MySQL 8.0 Labs – Descending Indexes in MySQL」這邊看到 MySQL 打算在 8.0 時實作出真正的 Descending Indexes。在 5.7 以及之前的版本，可以從「14.1.14 CREATE INDEX Syntax」看到這個參數是～假～的～XDDD

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

所以當 8.0 建立了 a_desc_b_asc (a DESC, b ASC) 這樣的 index，可以看到對於不同 ORDER BY 時效能的差異：(一千萬筆資料)

有些變快可以理解，但有些結果不太清楚造成的原因...

Anyway，對於變慢的兩個 query，他提了一個不算解法的解法，就是加上對應的 index XDDD：

If user wants to avoid filesorts for Query 5 and Query 6, he/she can alter the table to add a key (a ASC, b ASC) . Further to this, if the user wants to avoid backward index scans too, he/she can add both ( a ASC, b DESC) and (a DESC, b DESC).

這樣就會變快，但寫入的 overhead 會增加啊... XD

但不管怎樣，總算是把這個功能生出來了...

CC BY-SA 4.0 的素材可用於 GPLv3 的作品裡

Creative Commons 在經過幾個月的分析後，認為 CC BY-SA 4.0 相容於 GPLv3 的要求：「CC BY-SA 4.0 now one-way compatible with GPLv3」。

In January we officially opened a public consultation (blog post) on CC BY-SA 4.0 unilateral compatibility with GPLv3, in accordance with our ShareAlike compatibility process and criteria. Following additional months of detailed analysis, discussion and deliberation with the Free Software Foundation and other stakeholders, we are very pleased to announce that we have added a declaration of one-way compatibility from CC BY-SA 4.0 to GPLv3 to our compatible licenses page!

也就是說，CC BY-SA 4.0 的素材可以放到 GPLv3 的作品裡使用並且散佈。不過要注意的是，這是 CC 的分析，而不是法院判決的結果...

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 在寫入時的成本也會增加。

PostgreSQL 9.5 的 GROUPING SETS 以及 CUBE 與 ROLLUP

在 Zite 上看到的「Postgres finally has CUBE / ROLLUP / GROUPING SETS !」。

直接看 PostgreSQL 的文件「7.2.4. GROUPING SETS, CUBE, and ROLLUP」就可以知道用法：

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

結果就是分次 GROUP BY 的聯集。而 CUBE 與 ROLLUP 則是提供列舉的方式。

ROLLUP 的部份：

ROLLUP ( e1, e2, e3, ... )

表示階層式的列舉：

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 )
    ( e1 )
    ( )
)

而 CUBE：

CUBE ( a, b, c )

則是表示 power set (所有的組合)：

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         ),
)

也有更複雜的 CUBE ( (a,b), (c,d) ) 與 GROUP BY a, CUBE(b,c), GROUPING SETS ((d), (e)) 可以用，參考文件裡的範例即可 :p

Percona 的「Advanced MySQL Query Tuning」...

先前在「Percona 要講進階的 MySQL Query Tuning...」提到 Percona 所辦的 Webniar「Advanced MySQL Query Tuning」的投影片放出來了：「Advanced MySQL Query Tuning」。

這份內容需要 B+Tree 操作的背景知識才能了解。裡面講了很多 GROUP BY 與 ORDER BY 混用時的 index 技巧，以及各種奇怪的 hack 方式。

內容很有用，能吸收多少就看個人造化 :p