iT邦幫忙

0

SQL 執行問題

php

之前紀錄點閱率
是另外開某個 table 紀錄所有細節,結構大概是這樣

主 table
main_table
mt_id PK
....

紀錄 table
hits_table
ht_id PK
ht_date 留言時間
ht_ip 留言 IP
mt_id main_table 的 fk

所以以點閱率撈資料的話會是

<?php
$sql = "SELECT mt_label, COUNT(ht_id) AS totalHits FROM main_table LEFT JOIN hits_table ON main_table.mt_id = hits_table.mt_id GROUP BY hits_table.mt_id ORDER BY COUNT(ht_id) DESC";
?>

這樣可以根據點閱率排行,並且撈出點閱數量
但隨著時間的經過,點閱率資料已經有三千多萬筆了
因此上方的效能變得非常差
於是我在 main_table 開了一個 mt_hits 的欄位
想要跑一次循環把總結寫進這個欄位

<?php
$sql = "SELECT mt_id, COUNT(ht_id) as totalHits FROM hits_table GROUP BY mt_id";
$query = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($query);

do {
	$update = sprintf("UPDATE main_table SET mt_hits = %s WHERE mt_id = %s",
		$row['totalHist'],
		$row['mt_id']);
	myaql_query($update);
} while ($row = mysql_fetch_assoc($query));
?>

不過可能因為筆數太多,這隻 query 根本跑不完
請問該怎麼改善比較好

看更多先前的討論...收起先前的討論...
賽門 iT邦超人 1 級 ‧ 2011-07-27 11:24:58 檢舉
SELECT mt_label, COUNT(ht_id) AS totalHits FROM main_table 
LEFT JOIN hits_table ON main_table.mt_id = hits_table.mt_id 
GROUP BY hits_table.mt_id 
ORDER BY COUNT(ht_id) DESC

怎麼會是Group By hits_table.mt_id??應該Error了吧?!
charmmih iT邦研究生 5 級 ‧ 2011-07-27 13:15:26 檢舉
是啊....發問者寫錯了吧....
應是 Group By mt_table.mt_id
賽門 iT邦超人 1 級 ‧ 2011-07-27 13:56:57 檢舉
charmmih提到:
Group By mt_table.mt_id

Group By main_table.mt_label
charmmih iT邦研究生 5 級 ‧ 2011-07-27 18:00:26 檢舉
是啊....這才是正確的...
group by 欄位要在 select 欄位中...
^_^
chan15 iT邦新手 5 級 ‧ 2011-07-29 13:20:02 檢舉
沒有 error 耶
是用 hits_table 的 mt_id 做 group 然後用 id count 出筆數啊
其實要 count 哪個欄位都可以
mt_table.mt_id 是唯一值耶,用他當 group?
charmmih iT邦研究生 5 級 ‧ 2012-02-03 00:22:29 檢舉
所以是用
Group By main_table.mt_label
6
一級屠豬士
iT邦高手 1 級 ‧ 2011-07-27 17:36:15
最佳解答
<pre class="c" name="code">先建立原本的tables

CREATE TABLE main_table (
mt_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
mt_label VARCHAR(20)
) ENGINE=InnoDB;

CREATE TABLE hits_table (
ht_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ht_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
ht_ip VARCHAR(19) NOT NULL,
mt_id INT NOT NULL,
CONSTRAINT `FK__main_table` FOREIGN KEY (`mt_id`) REFERENCES `main_table` (`mt_id`)
) ENGINE=InnoDB;

--------------------------
插入一些測試資料

INSERT INTO main_table VALUES
(1,'test1'),(2,'test2'),(3,'test3'),(4,'test4');

INSERT INTO hits_table(ht_ip,mt_id) VALUES
('192.168.1.1',1),
('192.168.1.2',1),
('192.168.1.3',1),
('192.168.2.1',2),
('192.168.2.2',2),
('192.168.4.2',4),
('192.168.4.1',4),
('192.168.4.4',4),
('192.168.4.3',4);

原本撈資料的 
SELECT mt_label, COUNT(ht_id) AS totalHits 
FROM main_table LEFT JOIN hits_table 
ON main_table.mt_id = hits_table.mt_id 
GROUP BY hits_table.mt_id 
ORDER BY COUNT(ht_id) DESC;

+----------+-----------+
| mt_label | totalHits |
+----------+-----------+
| test4    |         4 |
| test1    |         3 |
| test2    |         2 |
| test3    |         0 |
+----------+-----------+
*******************************************
現在想要在main_table中增加一個欄位叫mt_hits,存放hits_table裡面的總和資料.

ALTER TABLE main_table ADD COLUMN mt_hits INT;

現在Main_Table的情形如下:
SELECT * FROM main_table;
+-------+----------+---------+
| mt_id | mt_label | mt_hits |
+-------+----------+---------+
|     1 | test1    |    NULL |
|     2 | test2    |    NULL |
|     3 | test3    |    NULL |
|     4 | test4    |    NULL |
+-------+----------+---------+

樓主是想利用mt_hits來存放,但是現在需要初始化,所以用php包了兩道SQL Commands,
但因資料量大,所以跑很久,故上網求助.
----------------------------------------
我們採用以下語法:

UPDATE main_table a
LEFT JOIN (SELECT mt_id, COUNT(ht_id) AS hits
                  FROM hits_table
                  GROUP BY mt_id) b
ON a.mt_id = b.mt_id
SET a.mt_hits = b.hits;

執行結果:
Rows matched: 4  Changed: 3  Warnings: 0

查驗結果:
>SELECT * FROM main_table;
+-------+----------+---------+
| mt_id | mt_label | mt_hits |
+-------+----------+---------+
|     1 | test1    |       3 |
|     2 | test2    |       2 |
|     3 | test3    |    NULL |
|     4 | test4    |       4 |
+-------+----------+---------+
4 rows in set (0.00 sec)

結果正確! 這樣就能先作此表的新增欄位初始化.
以後若是有新增時,再增加,這個動作建議採用trigger.
這後面有需要,再討論,先把初始化確認.

嘆氣

10
pantc328
iT邦研究生 1 級 ‧ 2011-07-26 09:23:52

你在寫什麼?點越率不是這樣做
不是查的時候去做總匯
是人家點閱時就把該欄位的值加一

看更多先前的回應...收起先前的回應...
raytracy iT邦大神 1 級 ‧ 2011-07-26 12:36:00 檢舉

我看了也嚇一大跳.....哪有人這樣寫點閱率的? 又不是要做行為稽核......

給每個要計算點閱率的網頁一筆紀錄, 裡面只存一個數字欄位, 每次有人來到此網頁, 就將該欄位 +1. 要顯示點閱率, 就把那個欄位抓出來顯示就好了.

一個網頁一筆紀錄就解決了, 怎麼會搞到幾千萬筆紀錄出來...

chan15 iT邦新手 5 級 ‧ 2011-07-26 13:36:13 檢舉

相關欄位有,今日點閱率、本週點閱率、本月點閱率
單純用你們說得架構的話是做不出來的
但一開始沒想到量會爆這麼快沒有用一個欄位同步加一拉總量的確失算

sula3065408 iT邦研究生 1 級 ‧ 2011-07-27 13:29:34 檢舉

點閱事件:
日點閱Counter++;
周點閱Counter++;
月點閱Counter++;
日結束事件:
日點閱Counter=0;
周結束事件:
周點閱Counter=0;
月結束事件:
月點閱Counter=0;

為什麼要存?

賽門 iT邦超人 1 級 ‧ 2011-07-29 13:39:02 檢舉

raytracy提到:
哪有人這樣寫點閱率的?

看了以下幾位樓主的回答, 真的有點認為太過於考慮程式設計面的運用, 而沒有從Web本身的技術面來著手, 做點閱率並不需要到資料庫的使用.

6
charmmih
iT邦研究生 5 級 ‧ 2011-07-26 09:42:19

SELECT mt_label, COUNT(ht_id) AS totalHits
FROM main_table
LEFT JOIN hits_table ON main_table.mt_id = hits_table.mt_id
GROUP BY hits_table.mt_id
ORDER BY COUNT(ht_id) DESC

改成

SELECT a.mt_label, COUNT(b.mt_id) AS totalHits
FROM main_table a
LEFT JOIN hits_table b ON a.mt_id = b.mt_id
GROUP BY a.mt_id
ORDER BY COUNT(b.mt_id) DESC

記得要加一個mt_id的nonclustered index 做 index full scan ,
CREATE NONCLUSTERED IDNEX IX_HIT_TABLES_MT_ID ON HIT_TABLES(MT_ID);

看更多先前的回應...收起先前的回應...
chan15 iT邦新手 5 級 ‧ 2011-07-26 13:37:55 檢舉

用 ALIAS 會加速效能嗎?

Albert iT邦高手 1 級 ‧ 2011-07-26 22:54:33 檢舉

<?php
$sql = "SELECT mt_id, COUNT(ht_id) as totalHits FROM hits_table GROUP BY mt_id";
$query = mysql_query($sql) or die(mysql_error()...(恕刪)

問題根本不是在寫法
是在讀入筆數太多
為何不寫 stored procedure
根本不需要將資料拉到 AP-server 去運算
幫你找出 3 秒方法

Albert iT邦高手 1 級 ‧ 2011-07-26 22:55:59 檢舉

還有千萬沒事別用 update 方式

chan15 iT邦新手 5 級 ‧ 2011-07-27 01:16:07 檢舉

albertachen 您好
我現在用 update 是想跑「一次」迴圈拉出總欄位補救
但連這一次迴圈都跑不動了

charmmih iT邦研究生 5 級 ‧ 2011-07-27 09:54:23 檢舉

chan15提到:
chan15 說:
用 ALIAS 會加速效能嗎?

重點不在用 ALIAS,
而是在 hitstable 原先用 ht_id,mt_id 2個欄位,
修改後僅用mt_id 1個欄位, 而以做 full index scan,
另一個技術想法是:
count 有值會加1, count null 則不會加1 ,
因此僅需關注欄位有沒有值, 並不需要關注欄位內容~~

Albert iT邦高手 1 級 ‧ 2011-07-27 10:10:01 檢舉

chan15提到:
但連這一次迴圈都跑不動了

怎麼不寫 stored procedure
呼叫 stored procedure
統計完用寫入 [統計寫入時::當時時間]
讀取[最後]統計
不要沒事就 update
大系統 除非必要 盡量不用 update
而是再度寫入
程式只讀最後寫入

pantc328 iT邦研究生 1 級 ‧ 2011-07-27 10:53:10 檢舉

阿伯大又在唬
這跟Stored procedure 有什麼差別?
跟update 又有和關
這跟你的設計跟Stored procedure 寫法有差
你去看統計報表,去Run SP 一周3000萬筆,一年後去看統計,不管你有沒有用SP都是一樣慢

建個Table去放統計資料,Index設一設,寫個View把資料切一切,寫個Add的SP,你有點的時候就把個統計值加1,看統計頁就把要的(以統計)值拉出來Show就好了

pantc328 iT邦研究生 1 級 ‧ 2011-07-27 11:12:46 檢舉

根本不需要將資料拉到 AP-server 去運算??
阿伯大又在唬
這是由Client下指令叫Database Server 去運算
DataBase Server 算完在把Result 傳回 Client
你把SQL Statement 寫在 Stored Procdure 不是一樣
Client 下指令說我要執行某SP(本來是SQL Command)
這二種是一樣的

不管任何一種,你用Select + Group by +大量資料
你Client 要執行命令去要結果就是很慢

你就在程式執行時就去把統計時更新,要用時直接拉結果就好了
不然在資料庫離峰時去做統計,不用Client 去觸發統計

2
davidliu9116
iT邦研究生 3 級 ‧ 2011-07-27 11:53:21

點閱時呼叫stored procedure
stored procedure對每個日期進行Count+1
要取得今日點閱率、本週點閱率、本月點閱率只需用SQL對每個日期的Count進行群組彙總查詢即可

charmmih iT邦研究生 5 級 ‧ 2011-07-27 13:27:46 檢舉

對啊....
昨天前的用群組彙總來算,
今天的用 hits_table 來自..

全部就用union 加起來....

2
ccutmis
iT邦新手 1 級 ‧ 2011-07-28 09:55:00

樓上大大們都提供了很具體的解決方案,在下也分享一下我的想法~ 資料量到三千萬筆這種等級,如果要透過JOIN TABLE再加UPDATE,應該是很恐佈的事情。所以其實Albert大大他說的拉到AP層處理或許也是一種可行方案,當然~我提的是自己想像的。

我的作法是玩程式接力賽:
1.先用SQL語法~SELECT ... OUTFILE ... 將三千萬筆的資料匯出成30個csv檔(這樣單檔也只有一百萬筆的csv檔),例如:

&lt;pre class="c" name="code">
SELECT * FROM `hits_table`  LIMIT 0 , 1000000  
INTO OUTFILE 'C:/testMe01.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',';
SELECT * FROM `hits_table`  LIMIT 1000001 , 1000000  
INTO OUTFILE 'C:/testMe02.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',';
......
......
SELECT * FROM `hits_table`  LIMIT 29000001 , 1000000  
INTO OUTFILE 'C:/testMe30.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',';

2.寫一個Windows Shell Script(AP端),批次讀入上一階段產生的testMe01.csv~testMe30.csv...再整理出 hits_table 裡面 mt_hits 欄的加總,輸出 update main_table 的語法到一個文字檔,例如: C:/testMeFinal.sql,最後再匯入這段MySQL語法到資料庫就搞定嘍。

以上是如果真的都沒有辦法中的辦法,畢竟可以一行SQL解決的話,沒有人會想再去多繞這些路,所以還是建議目前問題若解決後,發文版主要改一下網站計數的方式,例如 pantc328 大大講的那個方式,或許就可避免目前的問題發生了~ 希望有幫助~

我要發表回答

立即登入回答