性能MySql Xml函数？ [英] Performance of MySql Xml functions?

问题描述

我对新的 Mysql XMl函数感到非常兴奋。

现在我可以在我的旧学习关系数据库中嵌入类似面向对象的文档。

例如，使用情况考虑使用facebook connect在您的网站上播放的用户。
您可以使用图形api为用户提取对象，并获取不错的信息。然而，这些信息可以有很大的不同。

如果你只是插入非常特殊的领域（例如朋友关系，性别，电影...），您可以将它们投影到关系数据库方案中。

但是，使用XMl函数可以将整个对象存储在一个字段中，然后您的不同模型可以使用 ExtractValue 函数。

但是效果会是什么呢？

例如，我有一个表，包含50 000个表示useres的条目。
我有一个枚举字段，说明male，female （或其他各种性别在政治上是正确的）。

例如抓取所有男性的表现会非常快。

• WHERE ExtractValue（userdata，'/ gender /'）='male'？




如果对象变大，性能会有所不同吗？

• 字段类型如何与此功能/性能配合使用。 Varchar / blob？




• 我需要全文索引吗？

总结我的问题：

Mysql XML functins看起来不错。如果你只是想存储结构化数据，并在你的应用程序中进一步分析，我相信他们真的很棒。

但是他们如何在程序中有没有对它们执行内部扫描/排序/比较/计算？

Mysql可以替换面向CouchDB / Sesame等面向文档的数据库吗？

XML函数的收益和折衷是什么？

它们如何以及为什么比存储各种数据作为属性的动态应用程序更好/更差？

例如，一个键/值表，其中xpath为键，值为连接到文档实体的值。

解决方案

我倾向于使用类似于Pekka的注释，但我认为我们不能笑这个的原因是你的声明这个信息，然而可以有很大的变化。这意味着计划将其解析并将其投影到数据库中是不现实的。

我无法回答所有的问题，但我可以回答其中的一些。

最值得注意的是我不能告诉你MySQL的性能。我在SQL Server中看到它，测试它，发现SQL Server在内存XML提取中执行非常缓慢，对我来说，它似乎是从磁盘读取，但这是一个夸张。

Mysql可以替换像CouchDB / Sesame这样面向文档的数据库吗？这个问题有点过于宽泛，但在你的情况下，使用MySQL允许你保持ACID符合这些XML块，假设你使用InnoDB，这不能自动对一些面向文档的数据库。

它们如何以及为什么比将各种数据作为属性存储的动态应用程序更好/更糟糕？我认为这是一个真正的风格问题。你给出的XML块是（可能）记录的，MySQL可以导航它们。如果你只是保持他们，这样你保存一步。

MySQL文档建议XML文件将进入clob字段。性能可能在较大的文档上受损。也许你会发现你想要定期打开并放入子表中的子文档。

同样，如果有特定的子文档知道你会想知道，你可以做一个子表，HasDocs，做一些预处理，并用子文档的名称填充它们的计数。

希望我可以说更多，希望这有帮助。

I am pretty excited about the new Mysql XMl Functions.

Now I can finally embed something like "object oriented" documents in my oldschool relational database.

For an example use-case consider a user who sings up at your website using facebook connect. You can fetch an object for the user using the graph api, and get nice information. This information however can vary vastly. Some fields may or may not be set, some may be added over time and so on.

Well if you are just intersted in very special fields (for example friends relations, gender, movies...), you can project them into your relational database scheme.

However using the XMl functions you could store the whole object inside a field and then your different models can access the data using the ExtractValue function. You can store everything right away without needing to worry what you will need later.

But what will the performance be?

For example I have a table with 50 000 entries which represent useres. I have an enum field that states "male", "female" (or various other genders to be politically correct).

The performance of for example fetching all males will be very fast.

• But what about something like WHERE ExtractValue(userdata, '/gender/') = 'male' ?

• How will the performance vary if the object gets bigger?

• Can I maby somehow put an Index on specified xpath selections?

• How do field types work together with this functions/performance. Varchar/blob?

• Do I need fulltext indexes?

To sum up my question:

Mysql XML functins look great. And I am sure they are really great if you just want to store structured data that you fetch and analyze further in your application.

But how will they stand battle in procedures where there are internal scans/sorting/comparision/calculations performed on them?

Can Mysql replace document oriented databases like CouchDB/Sesame?

What are the gains and trade offs of XML functions?

How and why are they better/worse than a dynamic application that stores various data as attributes?

For example a key/value table with an xpath as key and the value as value connected to the document entity.

Anyone made any other experiences with it or has noticed something mentionable?

解决方案

I tend to make comments similar to Pekka's, but I think the reason we cannot laugh this off is your statement "This information however can vary vastly." That means it is not realistic to plan to parse it all and project it into the database.

I cannot answer all of your questions, but I can answer some of them.

Most notably I cannot tell you about performance on MySQL. I have seen it in SQL Server, tested it, and found that SQL Server performs in memory XML extractions very slowly, to me it seemed as if it were reading from disk, but that is a bit of an exaggeration. Others may dispute this, but that is what I found.

"Can Mysql replace document oriented databases like CouchDB/Sesame?" This question is a bit over-broad but in your case using MySQL lets you keep ACID compliance for these XML chunks, assuming you are using InnoDB, which cannot be said automatically for some of those document oriented databases.

"How and why are they better/worse than a dynamic application that stores various data as attributes?" I think this is really a matter of style. You are given XML chunks that are (presumably) documented and MySQL can navigate them. If you just keep them as-such you save a step. What would be gained by converting them to something else?

The MySQL docs suggest that the XML file will go into a clob field. Performance may suffer on larger docs. Perhaps then you will identify sub-documents that you want to regularly break out and put into a child table.

Along these same lines, if there are particular sub-docs you know you will want to know about, you can make a child table, "HasDocs", do a little pre-processing, and populate it with names of sub-docs with their counts. This would make for faster statistical analysis and also make it faster to find docs that have certain sub-docs.

Wish I could say more, hope this helps.

这篇关于性能MySql Xml函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！