高效的KV型profiles查询SQL

以前基于Clearspace二次开发的一个系统，有profile功能，管理员可以动态增减profile，jiveProfileField用来描述profile的元数据，jiveUserProfile以key-value的形式保存profile的值，也就是一个用户的profile由N条记录组成，这跟基于字段（列）的设计是相反的。

虽然这里讲的是profile，但是key-value的存储形式在RDBMS表设计当中还是比较多见的，尤其是需要灵活性的时候。下面描述一下思路，其他key-value存储形式可以参考。下面描述的SQL主要是用于制作报表，而不是查询单条记录。

表结构如下：
jiveProfileField - docs.jivesoftware.com
extManaged
extMapping
fieldID
fieldType
idx
isDefault
isEditable
isFilterable
isList
isRequired
isSearchable
isVisible
name
regIdx

jiveUserProfile - docs.jivesoftware.com
fieldID
primaryVal
userID
value

有一次，客户想要所有登陆过的用户的详细信息，包括profile。当时部署非常规范，如果我写程序去做的话，那每次查询都得让系统管理员部署，太麻烦；如果我写N条SQL，每条SQL查询一列，那DBA太麻烦；我准备写一条SQL解决问题。系统里有将近30个profile，直接用表连接查询应该是不行的（这是我看到的别人的经验）。我怀着侥幸心理写了表连接查询的SQL（用程序生成的），先在本地安装的MySQL数据库试试，我没做explain，直接执行，结果马上就死机了，RDBMS的表连接性能真的是很差。重启电脑，换一种做法。

解决方法是写嵌套的SQL，一次查询所有用户的一个profile，组成临时表的一列，然后是下一个profile，每次都是两个表进行连接（用的是LEFT JOIN），直到所有profile都加到临时表。继续嵌套临时表做查询，把其他需要的信息添加到新的列。完成。
字段跟临时表都做了适当的重命名，SQL是写程序生成的。为了减少SQL长度，新建了一个view用来表示所有登陆过的用户（或许对性能也有改进，毕竟不用那么多相同的临时表了）。最后的SQL还是很长，有几KB，生成的报表有将近50个列，但是性能不错。

很多存储的数据都是数字，还得写程序转换一下，比如，男女是用1/0表示的，还有省份什么的。

服务器上的数据库是Oracle，也可以写PL/SQL来查询，只是我很久没用过了，所以选择熟悉的方式尽快解决问题。

RDBMS的表连接性能：
这个我还真想不明白，应该可以做得更好的，避免所有的笛卡尔乘积一次性在内存展开，甚至避免每一次展开，尤其是当连接字段是整型的时候。

=文章版本=

20130511