Can anyone explain to me why there is a dramatic difference in performance between MySQL and SQL Server for this simple select statement?
任何人都可以向我解释为什么MySQL和SQL Server之间在这个简单的select语句中的性能有很大差异?
SELECT email from Users WHERE id=1
Currently the database has just one table with 3 users. MySQL time is on average 0.0003 while SQL Server is 0.05. Is this normal or the MSSQL server is not configured properly?
目前,数据库只有一个包含3个用户的表。 MySQL时间平均为0.0003,而SQL Server为0.05。这是正常的还是MSSQL服务器配置不正确?
EDIT:
Both tables have the same structure, primary key is set to id, MySQL engine type is InnoDB.
I tried the query with WITH(NOLOCK) but the result is the same.
编辑:两个表具有相同的结构,主键设置为id,MySQL引擎类型是InnoDB。我尝试使用WITH(NOLOCK)查询,但结果是一样的。
3 个解决方案
#1
2
Are the servers of the same level of power? Hardware makes a difference, too. And are there roughly the same number of people accessing the db at the same time? Are any other applications using the same hardware (databases in general should not share servers with other applications).
服务器的功率是否相同?硬件也有所不同。那些同时访问数据库的人数大致相同吗?是否有任何其他应用程序使用相同的硬件(数据库通常不应与其他应用程序共享服务器)。
Personally I wouldn't worry about this type of difference. If you want to see which is performing better, then add millions of records to the database and then test queries. Database in general all perform well with simple queries on tiny tables, even badly designed or incorrectly set up ones. To know if you will have a performance problem you need to test with large amounts of data and many simulataneous users on hardware similar to the one you will have in prod.
就个人而言,我不担心这种差异。如果要查看哪个表现更好,请将数百万条记录添加到数据库中,然后测试查询。通常,数据库在小型表上进行简单查询,即使设计不当或设置不正确也能很好地执行。要知道您是否会遇到性能问题,您需要在硬件上测试大量数据和许多同类用户,这类似于您将要生产的硬件。
#2
1
The issue with diagnosing low cost queries is that the fixed cost may swamp the variable costs. Not that I'm a MS-Fanboy, but I'm more familiar with MS-SQL, so I'll address that, primarily.
诊断低成本查询的问题在于固定成本可能会淹没可变成本。并不是说我是MS-Fanboy,但我对MS-SQL比较熟悉,所以我会主要解决这个问题。
MS-SQL probably has more overhead for optimization and query parsing, which adds a fixed cost to the query when decising whether to use the index, looking at statistics, etc. MS-SQL also logs a lot of stuff about the query plan when it executes, and stores a lot of data for future optimization that adds overhead
MS-SQL可能会有更多的优化和查询解析开销,这会在决定是否使用索引,查看统计信息等时为查询增加固定成本.MS-SQL还记录了很多关于查询计划的内容。执行并存储大量数据以供将来优化,从而增加开销
This would all be helpful when the query takes a long time, but when benchmarking a single query, seems to show a slower result.
当查询需要很长时间时,这将非常有用,但是当对单个查询进行基准测试时,似乎显示较慢的结果。
#3
0
There are several factors that might affect that benchmark but the most significant is probably the way MySQL caches queries.
有几个因素可能影响该基准测试,但最重要的可能是MySQL缓存查询的方式。
When you run a query, MySQL will cache the text of the query and the result. When the same query is issued again it will simply return the result from cache and not actually run the query.
当您运行查询时,MySQL将缓存查询的文本和结果。当再次发出相同的查询时,它将简单地从缓存返回结果,而不是实际运行查询。
Another important factor is the SQL Server metric is the total elapsed time, not just the time it takes to seek to that record, or pull it from cache. In SQL Server, turning on SET STATISTICS TIME ON will break it down a little bit more but you're still not really comparing like for like.
另一个重要因素是SQL Server指标是总耗用时间,而不仅仅是寻找该记录或从缓存中提取它所花费的时间。在SQL Server中,打开SET STATISTICS TIME ON会将其分解一点,但是你仍然没有真正比较喜欢。
Finally, I'm not sure what the goal of this benchmarking is since that is an overly simplistic query. Are you comparing the platforms for a new project? What are your criteria for selection?
最后,我不确定这个基准测试的目标是什么,因为这是一个过于简单的查询。您是否在比较新项目的平台?您的选择标准是什么?
#1
2
Are the servers of the same level of power? Hardware makes a difference, too. And are there roughly the same number of people accessing the db at the same time? Are any other applications using the same hardware (databases in general should not share servers with other applications).
服务器的功率是否相同?硬件也有所不同。那些同时访问数据库的人数大致相同吗?是否有任何其他应用程序使用相同的硬件(数据库通常不应与其他应用程序共享服务器)。
Personally I wouldn't worry about this type of difference. If you want to see which is performing better, then add millions of records to the database and then test queries. Database in general all perform well with simple queries on tiny tables, even badly designed or incorrectly set up ones. To know if you will have a performance problem you need to test with large amounts of data and many simulataneous users on hardware similar to the one you will have in prod.
就个人而言,我不担心这种差异。如果要查看哪个表现更好,请将数百万条记录添加到数据库中,然后测试查询。通常,数据库在小型表上进行简单查询,即使设计不当或设置不正确也能很好地执行。要知道您是否会遇到性能问题,您需要在硬件上测试大量数据和许多同类用户,这类似于您将要生产的硬件。
#2
1
The issue with diagnosing low cost queries is that the fixed cost may swamp the variable costs. Not that I'm a MS-Fanboy, but I'm more familiar with MS-SQL, so I'll address that, primarily.
诊断低成本查询的问题在于固定成本可能会淹没可变成本。并不是说我是MS-Fanboy,但我对MS-SQL比较熟悉,所以我会主要解决这个问题。
MS-SQL probably has more overhead for optimization and query parsing, which adds a fixed cost to the query when decising whether to use the index, looking at statistics, etc. MS-SQL also logs a lot of stuff about the query plan when it executes, and stores a lot of data for future optimization that adds overhead
MS-SQL可能会有更多的优化和查询解析开销,这会在决定是否使用索引,查看统计信息等时为查询增加固定成本.MS-SQL还记录了很多关于查询计划的内容。执行并存储大量数据以供将来优化,从而增加开销
This would all be helpful when the query takes a long time, but when benchmarking a single query, seems to show a slower result.
当查询需要很长时间时,这将非常有用,但是当对单个查询进行基准测试时,似乎显示较慢的结果。
#3
0
There are several factors that might affect that benchmark but the most significant is probably the way MySQL caches queries.
有几个因素可能影响该基准测试,但最重要的可能是MySQL缓存查询的方式。
When you run a query, MySQL will cache the text of the query and the result. When the same query is issued again it will simply return the result from cache and not actually run the query.
当您运行查询时,MySQL将缓存查询的文本和结果。当再次发出相同的查询时,它将简单地从缓存返回结果,而不是实际运行查询。
Another important factor is the SQL Server metric is the total elapsed time, not just the time it takes to seek to that record, or pull it from cache. In SQL Server, turning on SET STATISTICS TIME ON will break it down a little bit more but you're still not really comparing like for like.
另一个重要因素是SQL Server指标是总耗用时间,而不仅仅是寻找该记录或从缓存中提取它所花费的时间。在SQL Server中,打开SET STATISTICS TIME ON会将其分解一点,但是你仍然没有真正比较喜欢。
Finally, I'm not sure what the goal of this benchmarking is since that is an overly simplistic query. Are you comparing the platforms for a new project? What are your criteria for selection?
最后,我不确定这个基准测试的目标是什么,因为这是一个过于简单的查询。您是否在比较新项目的平台?您的选择标准是什么?