知識布局-mysql-組內排序

04-07

問題描述：假設每個時間點有多篇文章，每篇文章有訪問量，我們需要求每個時間點的訪問量的前十名。

表結構如下：

欄位解釋：

times：博客的訪問次數

article_name：博客文章的名稱

monitor_time：系統採集時的時間

我寫的垃圾sql：

SELECT UNIX_TIMESTAMP(b.monitor_time) AS time_sec, b.times AS VALUE, b.article_name AS metricFROM blogs bWHERE (SELECT count(*) FROM blogs WHERE monitor_time=b.monitor_time AND times > b.times AND user_name=b.user_name )<10 ANDb.monitor_time>= 2017-12-17 21 ) AND b.user_name =zach ORDER BY b.monitor_time,b.times DESC;

dba幫忙優化後的sql：

SELECT time_sec,VALUE , metric, rnumFROM ( SELECT UNIX_TIMESTAMP(b.monitor_time) AS time_sec, times VALUE , b.article_name AS metric, CASE WHEN @mtime = b.monitor_time THEN @rn :=@rn + 1 ELSE @rn := 1 END rnum, @mtime := b.monitor_time FROM blogs b, ( SELECT @mtime := NULL ,@rn := NULL ) tb WHERE b.monitor_time >= 2017-12-17 21 ORDER BY b.monitor_time, b.times DESC ) taWHERE rnum <= 10;

總結：

我寫的sql數據量在2000直接就查不出來了，專業dba寫的速度在0.35秒左右。真是術業有專攻呀。