对X2.5版淘帖功能的一些思路整理

帖子质量很高 ，比那些刚开站 就 乱喷的 好的多 ，

继续学习

mark35 发表于 2012-6-4 13:33 我更多关心的是技术层的实现，你的出发点是应用的效果 mysql本质上是个数据仓库而不是关系型数据库，简单 ...

mysql处理单表的海量查询的效率还是不错的，而且比较特别的是一个表中可以放置n个字段而不怎么影响效率，这个要承认。
最简单的例子，查询千万级别的帖子表中某用户在某时段的发帖数，mysql查询效率就非常不错。以前我做过版主考核系统，不可避免的要搞类似的压力测试，ms表现还是比较出色的。这种情况就别管其他的了，能成功显示出查询结果，就赢了。

你有一点说的很对，正因为ms在关系约束处理上不完善，有时候搞多表连接，或者做多表参照完整性约束处理，那就真tmd叫郁闷了。

我对淘贴其实也很期待。只不过没有认真去想过。毕竟没有实际运营过该功能（我的站是2.0），也就只能静观其变吧。

枯心树 发表于 2012-6-5 00:07
我对淘贴其实也很期待。只不过没有认真去想过。毕竟没有实际运营过该功能（我的站是2.0），也就只能静观其变 ...

我现在考虑的是淘帖和专题功能二选一，做成插件移植到7.2上，还没决定到底是做淘帖还是做专题。

专题想做出来不难，难的是达到效果的话对数据库的考验度很高。淘帖只能人工操作，这一点让我望而生畏。

1111111111111111

sw08 发表于 2012-6-4 23:40
mysql处理单表的海量查询的效率还是不错的，而且比较特别的是一个表中可以放置n个字段而不怎么影响效率， ...

mysql处理单表的海量查询的效率还是不错的，而且比较特别的是一个表中可以放置n个字段而不怎么影响效率，这个要承认。

oracle，pgsql都有自己的行外存储方式把行内大尺寸（pg是>2KB）字段值保存到该表之外（相当于于人工纵向分表），并且可以同时压缩行外存储数据，极大地提升了主表的读写性能。mysql没发现有这种机制，如果一个表多个字段并且许多字段是超长类型(text,blob等)，效率不会好到哪儿去吧。

最简单的例子，查询千万级别的帖子表中某用户在某时段的发帖数，mysql查询效率就非常不错。以前我做过版主考核系统，不可避免的要搞类似的压力测试，ms表现还是比较出色的。这种情况就别管其他的了，能成功显示出查询结果，就赢了。

这有两个关键点：
1、mysql是索引组织表，所有行数据以及非主键索引的索引都是挂接在主键索引上，如果是对主键进行查询（此时mysql优化器基本上忽略使用SQL条件中包含的其他索引）那么效率非常高，并且索引组织表的特色的Coverage index scan扫描索引即可返回数据，使得查询速度相当快。 但如果查询不包含主键条件那么性能就急剧下降——mysql会先根据条件索引找到主键索引定位再去找数据。这也是mysql非主键索引性能低下的原因。

2、千万级别帖子听起来很大，但实际上一个用户的发帖数基本就几百，多点的上万，此时索引的效率相当高，换其他数据库都一样甚至更高。

DELL服务器8核心 E5320，8G内存。cdb_posts 400万行，在navicat lite中第一次执行

SELECT count(*) FROM cdb_posts WHERE authorid = 12742 AND dateline BETWEEN 1150141263 AND  1158141263;

pgsql9.1时间是63ms（explain analyze是30ms），mysql5.0是78ms（explain太简陋无更详细信息）。
对于

SELECT * FROM cdb_posts WHERE authorid = 40105 AND dateline between 1050000005 AND  1058000005;

pgsql是78ms(36ms), mysql是78ms.

你有一点说的很对，正因为ms在关系约束处理上不完善，有时候搞多表连接，或者做多表参照完整性约束处理，那就真tmd叫郁闷了。

有一哥们维护一应用2G多数据（亦或2G行）试图把一些零散表合并为一大表，在测试合并数据时也就四五个表的星型连接mysql歇菜了——单独连接两个表每个sql也不超过1秒，但全部一连接就是几十秒（具体值记不清楚了），性能低下得令人发指～ 嘿嘿

mysql是快，但也就是快。因为很多功能开销是转嫁给应用程序了。如果把一些本该由数据库做的事情也交给mysql去做，那mysql就什么都干不了了。这还不算因mysql缺陷无法实现的功能，比如交集（INTERSECT）、差集（EXCEPT/MINUS）运算。

mark35 发表于 2012-6-5 10:43
oracle,pgsql都有自己的行外存储方式把行内大尺寸（pg是>8KB）字段值保存到该表之外（相当于于人工纵向 ...

看来不愧是专业人才啊……受教了，从来没有研究那么仔细，都是靠经验了。

不过有一点在写插件和开发的时候确实必须要注意，那就是尽量避免使用连接表的查询。

千万级帖量如果对实际使用中来说，基本是考虑的极限了。
除非搞实验室级别的测试，那肯定数据比这大得多了。

你说的Mysql没有事务和交集差集运算的问题，这个么……前者看人品（不过搞了那么多年的开发，这个情况并不是太严重，因为大部分情况处理的都是小数据），后者只能交给程序处理（或者简单的连接查询）。

Discuz!的默认机制是屏蔽并集查询和嵌套查询的，所以一般也不考虑这类东西了。

sw08 发表于 2012-6-5 17:15
看来不愧是专业人才啊……受教了，从来没有研究那么仔细，都是靠经验了。

不过有一点在写插件和开发 ...

不过有一点在写插件和开发的时候确实必须要注意，那就是尽量避免使用连接表的查询。

对于mysql来说尽量避免超过2个表的连接查询。这也是X2.5新构架切分连表SQL为独立查询的原因之一（但也带来了额外开销）。

千万级帖量如果对实际使用中来说，基本是考虑的极限了。
除非搞实验室级别的测试，那肯定数据比这大得多了。

千万贴级别不分表会很惨，无论DZ还是其他的论坛。某车友论坛两千万多贴，没分表之前时常宕机，发帖超时不断刷新结果成了复读机。

你说的Mysql没有事务和交集差集运算的问题，这个么……前者看人品（不过搞了那么多年的开发，这个情况并不是太严重，因为大部分情况处理的都是小数据），后者只能交给程序处理（或者简单的连接查询）。

所以本来由数据库可以高效完成的交给应用程序去做结果效率下降不少，性能的损失转嫁到应用层就不容易让管理员发现了～

Discuz!的默认机制是屏蔽并集查询和嵌套查询的，所以一般也不考虑这类东西了。

mysql 4.x不支持子查询，即便新版5支持子查询也存在问题（SQL标准上以及性能上）。所以DZ肯定要屏蔽的。
对于屏蔽UNION另外一个重要原因是防止SQL注入——应用层对于敏感字符的处理不可靠（尤其是插件带入的），并且db类也没采用mysql_real_escape()来脱敏或者用prepare/execute来彻底封堵。光用addslashes()不安全，对于用双字节字符构造注入的方式存在防范漏洞。

mark35 发表于 2012-6-5 17:45
对于mysql来说尽量避免超过2个表的连接查询。这也是X2.5新构架切分连表SQL为独立查询的原因之一（但也带 ...

最后的那个就是防注入的设计，不过搞的很多东西不方便。

嵌套查询一向是我比较爱用的。

sw08 发表于 2012-6-5 19:23
最后的那个就是防注入的设计，不过搞的很多东西不方便。

嵌套查询一向是我比较爱用的。

大部分情况JOIN连接比子查询效率高。但有时候是相反的，比如在个大表中查询取多字段数据，如果直接ORDER BY那么资源消耗多，效率也低。这时候可以先在个子查询里面做ORDER BY以及分页计算然后返回PK值，外层用
SELECT * FROM WHERE  pk IN （subquery） ORDER BY the same, 可以有相当提升。