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