我们有张表是存储用户自定义的类似tag的信息的,有一个功能是按照tag进行搜索,而且支持搜索多个tag查找同时使用这些tag的对象,最开始的实现就是使用的intersect,我感觉可能有问题,然后正好没有太多的事情,就试了下性能,发现那个SQL可以进行改造,变成group by 加 having的模式,一个查询搞定,弄了大概4万条数据,两个SQL(同时查4个Tag)的对比结果显示,前面的SQL会扫描整个表4次(和查询的tag的次数直接相关),并且有一次写入操作,而后面的只有一次扫描没有写入操作,时间上后面的SQL的性能是前者的两倍以上。所以对于同质的SQL的intersect可以转换为这样的group by加having的模式。

原来的SQL:

select ObjectID from ConfigObjectMetaData where KeyNameHash = -3023837279545376792 and ValueStrHash = -6420380264491338705 and ObjectType = 10

intersect select ObjectID from ConfigObjectMetaData where KeyNameHash = 6769857814803370866 and ValueInt32 = 2 and ObjectType = 10

intersect select ObjectID from ConfigObjectMetaData where KeyNameHash = 3984357063977881949 and ValueInt32 = 3 and ObjectType = 10

intersect select ObjectID from ConfigObjectMetaData where KeyNameHash = -3087541436254450506 and ValueStrHash = -3706752959682952160 and ObjectType = 10

修改后的:
select ObjectID from ConfigObjectMetaData where ObjectType = 10 and (
(KeyNameHash = -3023837279545376792 and ValueStrHash = -6420380264491338705)
or
(KeyNameHash = 6769857814803370866 and ValueInt32 = 2)
or
(KeyNameHash = 3984357063977881949 and ValueInt32 = 3)
or
(KeyNameHash = -3087541436254450506 and ValueStrHash = -3706752959682952160)
)
group by ObjectID
having count(ObjectID)=4