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