sql server XML数据类型存储，模糊搜索的性能如何提升
0
悬赏园豆：40
[已解决问题]
解决于 2015-03-23 17:24
最近有个项目，其中一个业务是客户输入的信息字段不固定（字段合集大概100多个，客户可能只会挑选其中5-15个填写），这里我使用的是动态XML存储客户数据，自然就想到了mssql里面的xml数据类型，研究了半天时间（其中xml主索引，xml辅助索引-path索引、value索引都加了），发现这个字段内容值模糊查找性能很差：1.测试了姓名节点模糊查找；2.测试了时间段的查找，运行结果如下（数据量测试环境下是200W条，实际项目上线运行数据量会以每月15W左右的量逐步增加）：
SELECT * FROM dbo.user_info_xml WHERE info_xml.value('(/customers/user_name)[1]','nvarchar(50)') LIKE '%孙%' --19秒 SELECT * FROM dbo.user_info_xml WHERE info_xml.exist('/customers/user_name/text()[contains(.,"孙")]') = 1 --14秒 SELECT * FROM dbo.user_info_xml WHERE info_xml.value('(/customers/play_date)[1]','datetime') > '2015-3-21' AND info_xml.value('(/customers/play_date)[1]','datetime') < '2015-3-24' --29秒
不知园内各位大虾有无其他办法，可以从mssql本身的xml数据类型来讨论，也可以以我这个业务来讨论抛弃mssql存储方式，望各位大虾救救急！！！！！
最佳答案
-1
想要提供查询的字段，最好单独字段，别放在XML里面，那个适合不用于查询的情况。
收获园豆：20
我不可能弄100个字段吧，要是后面客户信息再多点我还得加表字段，这个不可控啊
@破晓幽灵: 没有啥是不可控的。所有的需求都是可控的，比如我现在想一秒到达火星，你能办到吗？
这个你可以说技术达不到。
好，换一个，我想要买下上海市。这个没有技术含量了吧。
你会说，老是瞎扯，嘿嘿。
干货就是，傻逼客户才需要所有的字段可查询，通常最多10个字段可查询就行了，
万能查询是骗初级客户的。越是万能，意味着越是低效。
就好比手机大屏与好拿之间，你总得有所取舍。
然后你试试用手机上的键盘编程看看，你会发现不可爱的电脑和键盘现在变得好可爱了。
@爱编程的大叔: 确实想过吧关键查询的几个字段单独提出来，然后再把整个信息存入一个xml字段只做数据获取，但后面仔细考虑了下，这100多个字段的定义其实是这样的（可能其中某些10多个对于买了A产品的客户是很重要的，查询的时候也会很频繁，可能其中某些10多个对于买了B产品的客户是很重要的，查询也很频繁，总之这些字段只是针对某些产品来说相对重要，查询频繁），这个时候你可能会给建议说，为啥不按产品来分表存储客户信息，这样做的话我有几百个产品，然后客人信息查询以及跟第三方商户对接的时候又是多产品信息一起查询的，速度仍然会很慢，大虾说下你的其他看法
@破晓幽灵: 按照你提问的这个情况来看，大概又是做电商的吧。
1、这个问题价值最少好几十万，甚至上百万，当然，你们给客户的报价可能都没这多呢。
2、看情况你不是初级的编程人员，但是这个事情呢，这样问是问不出结果的，
必须得请高手现场咨询，甚至是考虑服务器集群、NOSQL、Redis，读写分离，各种东西的上。
3、最怕的就是接了一个项目100万，结果却发现这项目需要1000万才能搞定。
这是眼界问题啦，也没啥好办法。
@爱编程的大叔: 哈哈！大虾一看就是老鸟，我有些项目经验了，这个问题很棘手，研究好几天了，也研究出来了几个方案，但是性能上还是不行，所以你说的那些我也都在考虑，但是资金的问题很难搞定，毕竟我也是个打工的分量不是很重，这个项目投入预算也不是很多，所以只能透过一些方案的整合来实现了，感谢你的深层次的分析，受教了！！！！！
其他回答(5)
0
为什么要用xml呀，这个估计不好弄。用数据库表有什么问题呀？
收获园豆：8
我不可能弄100个字段吧，要是后面客户信息再多点我还得加表字段，这个不可控啊
我想了个办法，不知道你的详细需求仅供参考。把肯定有的字段设计一个表，比如T[tid,name]，然后再弄个字典表D[did，tid（外键）,key,value]。这样可以不
@会长: 这个是想过，但是查询不行，如果我的条件里面有多个的时候就不好查了，比如：我要查询user_name包含了‘孙’以及play_date在（2015-3-21到2015-3-24之间）的值，这时候，key跟value就不好写条件了
@破晓幽灵: 楼主有空去查一下“半结构化数据”的处理方式，可能会有帮助
@破晓幽灵: 楼主，你对NOSQL熟悉吗？我也不熟，但隐约感到可能会对这个问题有帮助，你们的数据貌似不是百分之百的结构化数据，而是不同情形下有不同的字段，是不是可以用像ONSQL这样的非关系型（kev-value）数据库解决问题。
@破晓幽灵: 另外楼主，我查了下MYSQL的字段数量限制，是1000多个，应该别的数据库也差不多，其实可以搞个100多个字段的表，可能会有些性能上的问题吧，但可以通过索引和分区等手段解决。当然，这样不是太优雅。楼主查查key-value型数据库的查询方式是否满足需求。
@会长: NOSQL我用的很少，xml结构存储的倒是有一种数据库，没有去研究，而且在我的项目里面加上这个感觉有点过了，我最后还是采用了 爱编程的大叔的建议，把一些必要会被频繁查询的字段提出来作为单独的字段，剩下的全部存在xml字段里面了只供读取、更新，不过还是很谢谢你的耐心帮助，后面有什么问题也可以一起讨论下，貌似我刚回答了你的一个关于多线程的问题，呵呵
0
你这个方案~~~用xml做索引查询，说真的，你就等着死吧，至少目前这样。
对于你的需求，以下几个方案供参考：
1、定义多个空闲字段备用
2、定义一个通用属性表，如genericattribute，用于存储实体的额外数据
3、定义一个属性扩展表，如option表，用于实现对实体的扩展。
2、3方案的具体实现你可以参考nopCommerce电商系统的实现。
收获园豆：7
好的，我去研究下
0
几个点：
1.一百多个字段的这种业务是不是可以拆分，产品是不是傻逼。
2.写入多还是读取多
3.用这种奇葩的xml数据类型，无论是以后的功能升级还是数据迁移，你都得要等死，等着被以后的人骂“那个傻逼怎么会这样设计的。”
4.热点数据在逻辑层考虑缓存优化吧。
关键字：拆分需求，缓存。
收获园豆：5
1.100多个客户填写的字段，并不是所有的客人任何时候都能看到所有的字段，是来自于几百个产品的综合体，每个产品可能客人只填写10来个，所以加起来不同的字段就100多了，如果还不清楚可以看第一个人我的回复
2.写入多，读取也多
3.xml数据类型并不奇葩，不知道你是否深入用过，如果没有，请先在你实际项目中用过之后再来回味你这句话
4.这个确实有考虑，但现在数据库这块还没确定好，逻辑层缓存的事情还没深入仔细的想过要处理哪些数据
补充下：现在能存储、能查询的方案有好几个了，但性能上都不行，我现在就是想按照我的这个业务（或者是你理解的业务）规划出性能能在接受范围内的一个方案
0
gggggg
0
一个办法解决，索引，曾经，700万数据，300毫秒模糊查询出来....
也是xml动态数据类型吗？
