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

[sunlights (sunlights)]

※ 引述《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


謝謝你費心的回答..可是因為當初接手的資料表設計並不是你寫的那樣

而是

使用者表單
CREATE TABLE user (
        id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        realname VARCHAR(255),
        sex varchar(2) NOT NULL,
        city varchar(10) NOT NULL,
        .......

);


問卷a ,裡面有10幾個問題,現在假設是3個問題儲存的答案..

 a1~a20為複選題1,值是0(沒選)或1(有選)
 b為單選題2,值是A,B,C,D 其中一個
 c1~c33為複選題3,值是0(沒選)或1(有選)

 CREATE TABLE answers_a (
         id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
         user_id INT UNSIGNED,
         a1  INT UNSIGNED,
         a2  INT UNSIGNED,
         ...
         a20 INT UNSIGNED,
         b  char(5) NULL,
         c1  INT UNSIGNED,
         c2  INT UNSIGNED,
         ...
         c33 INT UNSIGNED

 );

 問卷b,裡面有10幾個問題..現在假設是4個問題儲存的答案..

    d1~d7是複選題1,值是0(沒選)或1(有選)
    e 是單選題2,值是 A,B,C,D其中一個
    f 是單選題3,值是 1,2,3,4,5,6,7 其中之一
    g1~g30是複選題4,值是0(沒選)或1(有選)

 CREATE TABLE answers_b (
         id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
         user_id INT UNSIGNED,
         d1  INT UNSIGNED,
         d2  INT UNSIGNED,
         ...
         d7  INT UNSIGNED,
         e   char(2) NULL,
         f   INT UNSIGNED,
         g1  INT UNSIGNED,
         g2  INT UNSIGNED,
         ...
         g30  INT UNSIGNED
 );

 還有問卷c..這裡暫時略過..
 為什麼表格是這樣的形式...那是客戶原來就規劃好的..就不討論了..
 現在要做交叉分析,分別要做
 1.使用者表單裡的sex和answers_a 裡的各項問題答案
 2.使用者表單裡的sex和answers_b 裡的各項問題答案
 3.使用者表單裡的city和answers_a 裡的各項問題答案
 4.使用者表單裡的city和answers_b 裡的各項問題答案
 5,answers_a和answers_b的各項問題答案..

 使用者大概20000多人..(因為那個客戶是很大的公司)
 answers_a,answers_b資料筆數大概20多萬(現在先以6萬來計算就好了)..


所以如果要在最短時間裡做出交叉分析..不用PHP跑..用sql跑..
如何做呢??


先前有人推文說要加index所以我在想是否所有欄位都要加下去呢??



--
※ 發信站: 批踢踢實業坊(ptt.cc) 
◆ From: 111.240.17.176
推 cjoe:看你願不願意空間換時間囉~
→ cjoe:不過這邊應該要正規劃才對，如果問題是固定20個 那就算了
→ tyf99:sql 弄個 index，效能會快非常多，我親身體驗，快幾百倍有吧
推 sadle:SELECT sex, SUM(a.a1) as a1, ..., SUM(a.a3) as a3,
→ sadle:  SUM(IF(a.b=='a',1,0)) as b_a, ...,
→ sadle:  SUM(IF(a.b=='d',1,0)) as b_d
→ sadle:FROM user u, answers_a a
→ sadle:WHERE u.id = a.id GROUP BY u.sex;
推 sadle:沒實際跑過 記得 u.id 和 a.id 要做 index.
→ sadle:複選 打勾用 a 方式統計 多答案的單選用 b 方式統計.
→ sunlights:謝謝sa大...我測測看..如果能解決我多年來的疑問..感激
→ sunlights:不盡了