作者DarkKiller (System hacked)
看板PHP
標題Re: [請益] 用PHP做運算 還是用MYSQL做運算 ?
時間Mon Nov 5 05:46:57 2012
我居然認真寫完了，測試報告都沒寫這麼詳細... =_=
※ 引述《sunlights (sunlights)》之銘言：
: 當時的環境是mssql
: (mysql我沒有測過..但是當時資料筆數大概快5萬筆..現在己經10幾快20萬)
: A表放的是調查者的姓名和姓別,B放填寫統計表的項目(共有20筆複選)
: 分別以b1,b2,b3...b20個欄位代表,欄位型態tinyint,有選為1,沒有選為0
# 使用者：使用者可以建立問卷，也可以回答問卷
CREATE TABLE user (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
realname VARCHAR(255),
gender TINYINT UNSIGNED
);
# 問卷
CREATE TABLE report (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
user_id INT UNSIGNED
);
# 問題
CREATE TABLE question (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
description TEXT
);
# 一份問卷有很多問題
CREATE TABLE report_question (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
report_id INT UNSIGNED,
question_id INT UNSIGNED
);
# 一個使用者對於一個問題有一個答案
CREATE TABLE answer (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
question_id INT UNSIGNED,
user_id INT UNSIGNED,
answer TINYINT UNSIGNED
);
: 要做出
: b1 b2 b3............b20
: ---------------------------------------
: 男
: 女
SELECT question_id, gender, COUNT(*)
FROM answer
LEFT JOIN user ON answer.user_id = user.id
WHERE question_id IN (SELECT question_id FROM report_question WHERE report_id = ?)
GROUP BY question_id, gender;
寫得有點隨便，不過應該意思有到...
: 那時是用
: select count(*) from A left join b on A.pid=b.pid where A.sex='F' and b.b1=1
: select count(*) from A left join b on A.pid=b.pid where A.sex='M' and b.b1=1
: select count(*) from A left join b on A.pid=b.pid where A.sex='F' and b.b2=1
: select count(*) from A left join b on A.pid=b.pid where A.sex='M' and b.b2=1
: ......
: select count(*) from A left join b on A.pid=b.pid where A.sex='F' and b.b20=1
: select count(*) from A left join b on A.pid=b.pid where A.sex='M' and b.b20=1
這可以看
https://en.wikipedia.org/wiki/Database_index 這邊講到的 Index
architecture，這跟資料庫能提供的 index 架構有關。
: pid是帳號
: 結果跑到快吐血..
是效能太差，還是寫 40 條覺得很麻煩？
: 後來直接select index 欄位用php去run 才OK
: 我現在都找不到用sql解決的方法..
: 歡迎大家嘗試..
前面提到的十萬不知道是什麼，我就當作十萬個使用者，而且 user 都有參與吧。
塞十萬個 user：(跑百萬次)
INSERT INTO user (gender) VALUES (ROUND(RAND()));
產生一個報表：
INSERT INTO report SET id = 1, user_id = 1;
產生 20 個問題：(從 id = 1 跑到 20)
INSERT INTO question SET id = 1;
把這 20 個問題綁到報表上：(從 question_id = 1 跑到 20)
INSERT INTO report_question SET report_id = 1, question_id = 1;
對十萬個使用者、二十個問題灌資料：(兩百萬筆)
INSERT INTO answer SET question_id = 1, user_id = 1, answer = ROUND(RAND());
沒有其他 index 的情況下 (只有建立表格時提供的 primary key) 是 26 秒：
mysql> SELECT SQL_NO_CACHE question_id, gender, COUNT(*)
FROM answer
LEFT JOIN user ON answer.user_id = user.id
WHERE question_id IN (SELECT question_id FROM report_question WHERE report_id = 1)
GROUP BY question_id, gender;
+-------------+--------+----------+
| question_id | gender | COUNT(*) |
+-------------+--------+----------+
| 1 | 0 | 50172 |
| 1 | 1 | 49828 |
[...]
+-------------+--------+----------+
40 rows in set (26.43 sec)
這樣算快嗎？我不知道... 不過這是極限狀況，很少有一張問卷超過一萬人填 XD
接下來改 DB schema：
ALTER TABLE answer ADD COLUMN (gender TINYINT UNSIGNED);
把本來的 gender 塞進去：(跑十萬次，從 id = 1 到 id = 100000)
UPDATE answer SET gender = (SELECT gender FROM user WHERE id = 1) WHERE user_id = 1;
然後把 SELECT 改成：
SELECT SQL_NO_CACHE question_id, gender, COUNT(*)
FROM answer
WHERE question_id IN (SELECT question_id FROM report_question WHERE report_id = 1)
GROUP BY question_id, gender;
結果是：
40 rows in set (22.66 sec)
22 秒，快了一點點，然後補上 index：
CREATE INDEX question_id_gender ON answer (question_id, gender);
然後重跑：
40 rows in set (20.95 sec)
再快了一些...
然後這邊有一個 SQL issue (maybe MySQL only)，如果你把 subquery 拆出來自己做
就會很快：
SELECT SQL_NO_CACHE question_id, gender, COUNT(*)
FROM answer
WHERE question_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
GROUP BY question_id, gender;
速度是：
40 rows in set (1.09 sec)
這樣能解答你的問題嗎？XDDD
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 210.242.135.97
1F：推 carlcarl:我剛才也在作實驗 不過覺得他的schema好像沒那麼複雜XD 11/05 06:21
2F：→ carlcarl:IN subquery 這個我記得有看過 建議是千萬不要這樣作 11/05 06:23
3F：推 carlcarl:我看他的好像是1:1的關係 所以用10萬:10萬筆資料下去跑 11/05 06:28
4F：→ carlcarl:不過也才4.6秒 不知道他的為什麼可以跑那麼久= =a 11/05 06:28
5F：推 cjoe:用in應該還好吧? 前題是where條件裡面的東西最好都要建index 11/05 09:07
6F：→ cjoe:如果是index狀況下，我想都是log(n)的狀況吧 11/05 09:07
7F：推 cjoe:最後一個sql，gender怎麼來的? answer沒看到gender 11/05 09:24
8F：→ DarkKiller:後面有 ALTER TABLE 測試啊... 11/05 09:25
9F：推 cjoe:XD ~ 3Q 11/05 09:25
10F：→ cjoe:可以跟你借Data嗎 晚上我也想玩玩看 我想試看看我的方式 11/05 09:26
11F：→ cjoe:answer加上gender有點作弊了XD不過算空間換時間 但是違反正規 11/05 09:28
12F：推 carlcarl:嗯嗯 如果是IN一般固定的值 效能就比較沒問題了 11/05 10:09
13F：→ carlcarl:如果效能差的話 用點反正規化OK吧~~ 11/05 10:10
14F：→ carlcarl:我加上反正規化避開join 時間就從4.5加快到1.X秒了 11/05 10:11
15F：→ DarkKiller:產生資料的方式我有寫啊，就真的 loop 下去塞 XD 11/05 10:36
16F：→ arrack:反正規化是常有的事情:D 11/05 10:54
17F：推 knme:實測結果跟這篇幾乎一樣 XD 11/07 20:58