(mysql) 透過建 index 加速 group by

剛才遇到一個情況, 下 SQL 配合一些 where condition 過濾資料 (有 range query), 再 group by GROUP_COLUMN。資料量有些多, 但也沒多到會塞爆記憶體, 於是用 MEMORY engine, 全存在記憶體方便後續處理。

一開始試著對 where condition 用到的欄位建 index, 結果不夠快。看 profiling 的結果, 時間花在 "Copying to tmp table"。轉念一想, 若先建 index 排好 GROUP_COLUMN, 就能確保 mysql 只做一次 table scan, 別搬資料到 tmp table, 應該可以省下不少時間。於是對 GROUP_COLUMN 和 where condition 中用到的欄位建 covering index, 讓 GROUP_COLUMN 排第一個, 確保 mysql 不需用到 filesort 和 tmp table。

這個作法的特點在於, 不管 where condition 為何, 都是一次 table scan, 不用擔心不同輸入資料造成的變化, 可明確測出 worst case 的速度。實測結果, 果然快上不少, 到可以接受的速度。了解 mysql 運作方式後, 做這類設計並配合實測, 順手不少。

btw, 之前也遇過相反的情況, 不見得這樣做較好。招式是活的, 重點還是要看需求 (如是否看重 worst case, 以及 worst case 發生頻率, worst case 有無其它配套解法), 跑 profiling 實測, 再選適合的作法。

用 Ruby 寫碼為例, 說明思考和寫程式同步的樂趣

看到 Thinker 的這噗提到他最近寫程式, 關於思考順序和寫程式順序的影響, 讓我想到以前剛寫 Ruby 時, 發覺思考和寫程式的順序竟然能夠一致, 這種情況下, 寫起程式相當順手。剛換到 Python 時, 反而不怎麼習慣, 花了點時間調適。

以 Thinker 舉的例子為例, 「每行有兩個數字, 將它們相乘後, 加總各行的結果」, 試寫 Ruby 的程式如下:

>> $data = ["2 3", "4 5"]
=> ["2 3", "4 5"]
>> $data.map{ |line| line.split }
=> [["2", "3"], ["4", "5"]]
>> $data.map{ |line| line.split }.map{ |xs| xs.map{ |x| x.to_i } }
=> [[2, 3], [4, 5]]
>> $data.map{ |line| line.split }.map{ |xs| xs.map{ |x| x.to_i } }.map{ |x,y| x*y }
=> [6, 20]
>> $data.map{ |line| line.split }.map{ |xs| xs.map{ |x| x.to_i } }.map{ |x,y| x*y }.reduce { |sum,n| sum + n }
=> 26

懶得裝 Ruby/irb 的人可以用 tryruby.org 試試, 上面的程式碼是我在上面寫好後, 排版後貼過來的。習慣這種資料轉換的思維後 (functional programming?), 配合 Ruby 的 code block, 可以一直線地寫到底, 相當痛快。不過是否好讀, 和習慣和個人喜好有觀, 不在此篇的討論範圍。我自己是覺得寫起來很爽, 但不好讀, 少了用變數抽象化前面操作的結果, 得讀完全部細節才知道在做什麼。

2011-04-15 更新

避免沒寫過 Ruby 的人誤會 code block 像是塞成一行程式碼寫爽的功能, 將最後的結果排版了一下:

>> $data = ["2 3", "4 5"]
>> $data.map { |line|
..   line.split
.. }.map { |xs|
..   xs.map { |x|
..      x.to_i
..   }
.. }.map { |x, y|
..   x * y
.. }.reduce { |sum, n|
..   sum + n
.. }
=> 26

實際寫的時候, 常會要在一個 code block 寫個兩三行, 這時就排版拆行寫了。