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

看板PHP作者DarkKiller (System hacked)時間11年前 (2012/11/05 05:46)推噓7(7推 0噓 10→)

留言17則, 5人參與討論串8/12 (看更多)

我居然認真寫完了，測試報告都沒寫這麼詳細... =_= ※ 引述《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

11/05 06:21, , 1^F

11/05 06:21, 1^F

→

carlcarl

11/05 06:23, , 2^F

11/05 06:23, 2^F

推

carlcarl

11/05 06:28, , 3^F

11/05 06:28, 3^F