Re: [請益] 用PHP做運算 還是用MYSQL做運算 ?

[DarkKiller (System hacked)]

我居然認真寫完了，測試報告都沒寫這麼詳細... =_=

※ 引述《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
推 carlcarl:我剛才也在作實驗 不過覺得他的schema好像沒那麼複雜XD
→ carlcarl:IN subquery 這個我記得有看過 建議是千萬不要這樣作
推 carlcarl:我看他的好像是1:1的關係 所以用10萬:10萬筆資料下去跑
→ carlcarl:不過也才4.6秒 不知道他的為什麼可以跑那麼久= =a
推 cjoe:用in應該還好吧? 前題是where條件裡面的東西最好都要建index
→ cjoe:如果是index狀況下，我想都是log(n)的狀況吧
推 cjoe:最後一個sql，gender怎麼來的? answer沒看到gender
→ DarkKiller:後面有 ALTER TABLE 測試啊...
推 cjoe:XD ~ 3Q
→ cjoe:可以跟你借Data嗎 晚上我也想玩玩看 我想試看看我的方式
→ cjoe:answer加上gender有點作弊了XD不過算空間換時間 但是違反正規
推 carlcarl:嗯嗯 如果是IN一般固定的值 效能就比較沒問題了
→ carlcarl:如果效能差的話 用點反正規化OK吧~~
→ carlcarl:我加上反正規化避開join 時間就從4.5加快到1.X秒了
→ DarkKiller:產生資料的方式我有寫啊，就真的 loop 下去塞 XD
→ arrack:反正規化是常有的事情:D
推 knme:實測結果跟這篇幾乎一樣 XD