C# - MySQL 与 Microsoft SQL Server

C# - MySQL vs Microsoft SQL Server

提问by TheAJ

For the longest time, I've been using MySQL servers to handle data (in JAVA, and in C#). But lately, I've been hearing good things about LINQ and SQL Server. I've been thinking of converting, but I don't know much about the SQL Server.

长期以来，我一直在使用 MySQL 服务器来处理数据（在 JAVA 和 C# 中）。但是最近，我听到了有关 LINQ 和 SQL Server 的好消息。我一直在考虑转换，但我对 SQL Server 了解不多。

Could anyone who has used SQL Server before, please define how well it is compared to MySQL server in terms of performance and usability.

之前使用过 SQL Server 的任何人都可以定义它在性能和可用性方面与 MySQL 服务器相比有多好。

I've also heard that SQL Server would be better for C# as it's basically built in.

我还听说 SQL Server 更适合 C#，因为它基本上是内置的。

采纳答案by Justin Grant

I have used SQL Server for many years on C# projects large and small, but have been using mostly MySQL for the last year on various C# (but open-source-related and startup-related) projects which were already using MySQL.

我已经在大大小小的 C# 项目中使用 SQL Server 多年，但去年在已经使用 MySQL 的各种 C#（但与开源相关和启动相关）项目中主要使用 MySQL。

I miss SQL Server! In my experience, SQL Server is better in many ways:

我想念 SQL Server！根据我的经验，SQL Server 在很多方面都更好：

• the query optimizer in SQL Server is smarter, meaning that you can often build queries and they'll produce optimal query plans. With MySQL, I find myself spending more time hand-tuning even relatively simple queries in order to produce good query plans.
• the underlying database engine in SQL Server can do a wider variety of things to boost performance. for example, all joins in MySQL are Nested Loop joins, while SQL Server can do Hash Joins or Merge Joins which can sometimes boost query performance 10x+. SQL Server can also parallelize queries which, for large data-warehouse workloads especially, can dramatically boost performance.
• the GUI tools are miles ahead. SQL Server's graphical plan query optimizer makes query optimization a snap-- you'll never want to go back to EXPLAIN EXTENDED. SQL Server 2008's graphical monitoring tools are so much easier than digging through the slow query log to figure out what's going wrong. And so on.
• As you mentioned, the .NET integration story (C#, Linq, Entity Framework, etc.) in SQL Server is better. I use C#, Entity Framework, and LINQ with MySQL too, so it's not an either-or thing, although performance is likely to be better with SQL Server in a .NET environemnt because the teams work together to boost performance and make integration work better.
• SQL Server's SQL-language support is richer than MySQL's, including some very cool features (in SQL 2008 especially) like ROW_NUMBER(), GROUPING_SETS, OPTIMIZE FOR, computed columns, etc.
• Backup is many times faster, especially in SQL 2008 with compressed backups
• There's no Oracle acquisition cloud hanging over the future of SQL Server.
• SQL Server (especially the expensive editions) come with other goodies, like an OLAP data warehouse (SSAS), a reporting solution (SSRS), an ETL tool (SSIS), a scheduler (SQL Agent), etc. You can get similar open-source tools, for free (e.g. Pentaho, BIRT, etc.) but integration tends to be better with SQL Server.

• SQL Server 中的查询优化器更智能，这意味着您可以经常构建查询，它们将生成最佳查询计划。使用 MySQL，我发现自己花更多的时间手动调整即使是相对简单的查询，以生成良好的查询计划。
• SQL Server 中的底层数据库引擎可以做更多的事情来提高性能。例如，MySQL 中的所有连接都是嵌套循环连接，而 SQL Server 可以执行哈希连接或合并连接，有时可以将查询性能提高 10 倍以上。SQL Server 还可以并行化查询，尤其是对于大型数据仓库工作负载，可以显着提高性能。
• GUI 工具遥遥领先。SQL Server 的图形计划查询优化器使查询优化变得轻而易举——您永远不想回到 EXPLAIN EXTENDED。SQL Server 2008 的图形监控工具比挖掘慢查询日志以找出问题所在要容易得多。等等。
• 正如您提到的，SQL Server 中的 .NET 集成故事（C#、Linq、实体框架等）更好。我也将 C#、实体框架和 LINQ 与 MySQL 一起使用，所以这不是一个非此即彼的事情，尽管在 .NET 环境中使用 SQL Server 的性能可能会更好，因为团队一起工作以提高性能并使集成工作得更好.
• SQL Server 的 SQL 语言支持比 MySQL 更丰富，包括一些非常酷的功能（尤其是在 SQL 2008 中），如ROW_NUMBER()、GROUPING_SETS、OPTIMIZE FOR、计算列等。
• 备份速度快很多倍，尤其是在带有压缩备份的 SQL 2008 中
• SQL Server 的未来没有 Oracle 收购云。
• SQL Server（尤其是昂贵的版本）带有其他好东西，如 OLAP 数据仓库 (SSAS)、报告解决方案 (SSRS)、ETL 工具 (SSIS)、调度程序 (SQL Agent) 等。您可以获得类似的开放-source 工具，免费（例如Pentaho、BIRT等），但与 SQL Server 的集成往往更好。

That said, there are significant drawbacks, which may or may not be deal-breakers for you:

也就是说，有一些明显的缺点，对你来说可能会或可能不会破坏交易：

• you're stuck using Windows Servers, with all the pluses and minuses this entails
• SQL Server, especially the higher-end editions, are expensive! For small DB's (<4GB I think), SQL Server Express is free, though, and is nearly as full-featured as the regular SQL Server-- if you know your data is going to be small and you know your boss is a cheapskate, Express is the way to go. Also, there's a new SQL Server 2008 Web Edition which, for internet-facing web apps, should theoretically offer cheap hosting since the cost to a hoster is only $15/month per processor.
• It's not open source. Some companies and development teams are very passionate about this, for good reasons (debugging, cost, philosophy, etc.) !
• related to above: if you want to get a bug fixed in MySQL, and you've got the skills, you can fix it yourself. With SQL Server, there are painful bugs in query processing, optimization, etc. that persist for years-- I've spent an absurd amount of time working around some of those.
• for very simple, read-only (or non-transactional) workloads (e.g. a DB-based cache access from a web app) where you can get away with using MyISAM instead of InnoDB, I hear that MySQL can be significantly faster.

• 你被困在使用 Windows Servers 上，所有的优点和缺点都需要
• SQL Server，尤其是高端版本，价格昂贵！不过，对于小型数据库（我认为 <4GB），SQL Server Express 是免费的，并且功能几乎与常规 SQL Server 一样全——如果你知道你的数据会很小并且你知道你的老板是个吝啬鬼，快递是要走的路。此外，还有一个新的 SQL Server 2008 Web Edition，对于面向 Internet 的 Web 应用程序，理论上应该提供廉价的托管，因为托管商的成本仅为每个处理器每月 15 美元。
• 它不是开源的。一些公司和开发团队对此非常热情，理由很充分（调试、成本、理念等）！
• 与上述相关：如果您想修复MySQL中的错误，并且您有技能，则可以自己修复。对于 SQL Server，在查询处理、优化等方面存在持续多年的令人痛苦的错误——我已经花费了大量时间来解决其中的一些问题。
• 对于非常简单的只读（或非事务性）工作负载（例如从 Web 应用程序访问基于数据库的缓存），您可以使用 MyISAM 而不是 InnoDB，我听说 MySQL 可以明显更快。

Caveat: I hear that MySQL 6.0 is supposed to address many of the gaps and differences above, but I admittednly haven't kept myself up to speed with how the Oracle thing, etc. will affect the schedule and/or featureset.

警告：我听说 MySQL 6.0 应该解决上面的许多差距和差异，但我承认我没有让自己跟上 Oracle 事物等将如何影响时间表和/或功能集的速度。

re: your "C# is built-in" note: yes, you can develop stored procedures, functions, aggregates, etc. using .NET languages, but IMHO in most scenarios this is more trouble than it's worth, including because deployment is harder and DBAs are less comfortable with .NET code on their servers. The real win for a C# + .NET + Visual Studio + SQL Server combination, IMHO, is that they have been designed in parallel over the last 10 years to all work well together, so you'll get ease of use and synergy that you may not get using MySQL. That said, as I noted above, this isn't a deal-breaker or deal-maker... it's just smoother using SQL Server with the rest of the Microsoft stack.

回复：您的“C# 是内置的”注意：是的，您可以使用 .NET 语言开发存储过程、函数、聚合等，但恕我直言，在大多数情况下，这比它的价值更麻烦，包括因为部署更难和DBA 不太适应他们服务器上的 .NET 代码。恕我直言，C# + .NET + Visual Studio + SQL Server 组合的真正胜利在于它们在过去 10 年中并行设计，可以很好地协同工作，因此您将获得易用性和协同作用可能不会使用 MySQL。也就是说，正如我上面提到的，这不是交易破坏者或交易制定者……只是将 SQL Server 与 Microsoft 堆栈的其余部分一起使用会更顺畅。

In summary, let me be clear that, for many DB workloads, MySQL is good enough-- it works, it's stable, it's fast, it has reasonably good tools, etc. And it's affordable! :-) I would never refuse a project simply because they're using MySQL. But the comparison is like driving a Honda vs. a BMW... the Honda gets you where you want to go, but if your wallet can take it, you'll enjoy the ride a lot more with the Bimmer. :-)

总之，让我明确一点，对于许多数据库工作负载，MySQL 已经足够好了——它有效、稳定、速度快、有相当好的工具等等。而且它是负担得起的！:-) 我绝不会仅仅因为他们使用 MySQL 就拒绝一个项目。但这种比较就像驾驶本田和宝马……本田带你去你想去的地方，但如果你的钱包可以承受，你会更享受 Bimmer 的骑行。:-)

回答by stimms

LINQ is a language in its own right and isn't tied to a specific database or even to a database. Really it is just a tool for the manipulation of structured data collections. The underlying language in which the DBMS is written should not matter in terms of what language you use it with.

LINQ 本身就是一种语言，与特定数据库甚至数据库无关。实际上，它只是用于操作结构化数据集合的工具。编写 DBMS 的底层语言与您使用的语言无关。

回答by Cade Roux

C# would access MySQL and SQLServer basically identically using ADO.NET (or LINQ - LINQ to SQL is tied to SQL Server, but it doesn't appear to have a long life ahead of it), so that wouldn't be a factor in my mind, although IDE integration might be.

C# 将使用 ADO.NET 基本上相同地访问 MySQL 和 SQLServer（或 LINQ - LINQ to SQL 与 SQL Server 相关联，但它似乎没有很长的生命周期），所以这不会成为影响我的想法，虽然IDE集成可能是。

The benefits of SQL Server would include the more complete gamut of development support within SQL Server, the Agent and scheduling, the security model, and indexing, tuning and other features like Integration Services, Reporting Services and Analysis Services which make it relatively easy to manage large system infrastructures.

SQL Server 的好处将包括 SQL Server 中更完整的开发支持范围、代理和调度、安全模型以及索引、调整和其他功能，如集成服务、报告服务和分析服务，这些功能使其相对易于管理大型系统基础设施。

I don't think you've given enough information about your system to make the choice between them obvious at this point.

我认为你没有提供足够的关于你的系统的信息来使它们之间的选择在这一点上显而易见。

回答by womp

I use both pretty regularly, and am currently studying for my Sql Server MCTS, so I can probably throw a few useful comments in here.

我经常使用这两种方法，目前正在为我的 Sql Server MCTS 学习，所以我可以在这里发表一些有用的评论。

SQL Server is a much, much, much more fully-featured database, especially the 2008 version. MySQL doesn't even support column constraints (for example, creating an int column and then constraining the value to between 1 and 1000 - can't enforce that automatically in MySQL). SQL Server offers full-text indexing, native XML columns and manipulation, multiple transaction modes, complex security offerings, replication and distributed features, and a great management suite.

SQL Server 是一个功能非常丰富的数据库，尤其是 2008 版本。MySQL 甚至不支持列约束（例如，创建一个 int 列，然后将值限制在 1 到 1000 之间 - 不能在 MySQL 中自动强制执行）。SQL Server 提供全文索引、本机 XML 列和操作、多种事务模式、复杂的安全产品、复制和分布式功能以及出色的管理套件。

In regards to your C# comment, yes, SQL Server has the ability to import CLR objects, meaning that you can compile some .Net code and drop it into SQL Server and have database functions that use it. This is especially useful for creating new aggregate functions, as you can bypass cursors and use faster CLR code loops.

关于您的 C# 注释，是的，SQL Server 具有导入 CLR 对象的能力，这意味着您可以编译一些 .Net 代码并将其放入 SQL Server 并拥有使用它的数据库函数。这对于创建新的聚合函数特别有用，因为您可以绕过游标并使用更快的 CLR 代码循环。

You can also optimize SQL Server to the hilt, down to the locking and isolation modes that it uses, and the CPU/memory that a given thread can use.

您还可以最大限度地优化 SQL Server，优化到它使用的锁定和隔离模式，以及给定线程可以使用的 CPU/内存。

MySQL, on the other hand, is free and relatively easy to set up, with enough options that most website owners are pretty happy using it for their basic CRUD apps.

另一方面，MySQL 是免费且相对容易设置的，它有足够的选项，大多数网站所有者都非常乐意将它用于他们的基本 CRUD 应用程序。

If you wish to use Linq2Sql, then you need SQL Server. If you need tons of advanced features, SQL Server is the way to go for sure.

如果您希望使用 Linq2Sql，那么您需要 SQL Server。如果您需要大量高级功能，SQL Server 无疑是您的最佳选择。

回答by Ryan Elkins

From a development (coding) standpoint I have worked with both as a Java developer and a C# developer (connecting to both on various projects, so Java to SQLServer, Java to MySQL, C# to MySQL, C# to SQLServer). Personally I didn't really notice that much of a difference although I wasn't doing anything too complex. I don't think there's too much to worry about if you're thinking there will be a steep learning curve unless you want to get into some of real nitty gritty server specific stuff. There are some minor differences, but nothing that someone wouldn't pick up on quickly.

从开发（编码）的角度来看，我曾作为 Java 开发人员和 C# 开发人员（在各种项目中连接到两者，例如 Java 到 SQLServer、Java 到 MySQL、C# 到 MySQL、C# 到 SQLServer）。就我个人而言，虽然我没有做任何太复杂的事情，但我并没有真正注意到有多大区别。如果您认为学习曲线会很陡峭，除非您想了解一些真正的、具体的服务器特定内容，否则我认为不必担心太多。有一些细微的差异，但没有什么是有人不会很快接受的。

回答by Rob Ferrante

I have worked with both, though far more with SqlServer. In a large shop, especially one where many application architectural decisions arise from DB Architects, SQL Server offers more. Replication, SSIS, etc. But, those add a LOT of complexity, and in my experience have been the source of a good portion of operational outages.

我与两者都使用过，但更多的是使用 SqlServer。在大型商店中，尤其是在许多应用程序架构决策来自 DB 架构师的商店中，SQL Server 提供了更多。复制、SSIS 等。但是，这些增加了很多复杂性，根据我的经验，它们是很大一部分操作中断的根源。

But as a datastore for a software application, MySql does what you need and is straightforward, reliable, fast and simple.

但作为软件应用程序的数据存储，MySql 可以满足您的需求，并且简单、可靠、快速且简单。

Speaking strictly philosophically, you really want your application logic in your application code, not in complex SQL and constraint definitions. Go for the basics in your datastore, and put the time you save into the program itself.

严格地说，您确实希望在您的应用程序代码中使用您的应用程序逻辑，而不是在复杂的 SQL 和约束定义中。在您的数据存储中了解基础知识，并将您节省的时间投入到程序本身中。

回答by Kuberchaun

Have you considered a more full featured open source database like PostgreSQL. There's nothing wrong with SQL Server, but it does start to hook you into a licensed piece of software that you can stay away from if you remain with an open source counter part. It all depends if that is ok with you.

您是否考虑过像 PostgreSQL 这样功能更齐全的开源数据库。SQL Server 没有任何问题，但它确实开始将您吸引到获得许可的软件中，如果您继续使用开源的对应部分，您就可以远离它。这完全取决于你是否同意。