作者DarkKiller (System hacked)
看板PHP
标题Re: [请益] 用PHP做运算 还是用MYSQL做运算 ?
时间Mon Nov 5 05:46:57 2012
我居然认真写完了，测试报告都没写这麽详细... =_=
※ 引述《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
1F：推 carlcarl:我刚才也在作实验 不过觉得他的schema好像没那麽复杂XD 11/05 06:21
2F：→ carlcarl:IN subquery 这个我记得有看过 建议是千万不要这样作 11/05 06:23
3F：推 carlcarl:我看他的好像是1:1的关系 所以用10万:10万笔资料下去跑 11/05 06:28
4F：→ carlcarl:不过也才4.6秒 不知道他的为什麽可以跑那麽久= =a 11/05 06:28
5F：推 cjoe:用in应该还好吧? 前题是where条件里面的东西最好都要建index 11/05 09:07
6F：→ cjoe:如果是index状况下，我想都是log(n)的状况吧 11/05 09:07
7F：推 cjoe:最後一个sql，gender怎麽来的? answer没看到gender 11/05 09:24
8F：→ DarkKiller:後面有 ALTER TABLE 测试啊... 11/05 09:25
9F：推 cjoe:XD ~ 3Q 11/05 09:25
10F：→ cjoe:可以跟你借Data吗 晚上我也想玩玩看 我想试看看我的方式 11/05 09:26
11F：→ cjoe:answer加上gender有点作弊了XD不过算空间换时间 但是违反正规 11/05 09:28
12F：推 carlcarl:嗯嗯 如果是IN一般固定的值 效能就比较没问题了 11/05 10:09
13F：→ carlcarl:如果效能差的话 用点反正规化OK吧~~ 11/05 10:10
14F：→ carlcarl:我加上反正规化避开join 时间就从4.5加快到1.X秒了 11/05 10:11
15F：→ DarkKiller:产生资料的方式我有写啊，就真的 loop 下去塞 XD 11/05 10:36
16F：→ arrack:反正规化是常有的事情:D 11/05 10:54
17F：推 knme:实测结果跟这篇几乎一样 XD 11/07 20:58