Memcached与SQL Server缓存 [英] Memcached vs SQL Server cache

问题描述

我读过很多文章，建议将Memcached(或Velocity等)放在数据库前面比直接访问数据库更有效.通过在内存缓存中查找数据，将减少数据库的命中次数，这比命中数据库要快.

I've been reading a lot of articles that suggest putting a Memcached (or Velocity, etc) in front of a database is more efficient than hitting the database directly. It will reduce the number of hits on the database by looking up the data in a memory cache, which is faster than hitting the database.

但是，SQL Server为数据库中的对象拥有自己的内存缓存.检索数据后，SQL Server会维护其缓存，并会(如有必要)从其内存中拉出该行，而不会将其击中磁盘.

However, SQL Server has it's own memory cache for objects in the database. When data is retrieved, SQL Server maintains its cache and will (if necessary) pull the row from it's memory and not hit the disk.

因此，如果SQL Server具有自己的缓存，那么外部Memcached(或类似的)服务器的好处是什么?

我读过的大多数文章都围绕着社交网站，这些网站大多使用MySql.但是，关于MySpace的文章使用SQL Server，建议在该系统上也使用缓存.

Most of the articles I have been reading are around social networking sites, that mostly use MySql. However, an article about MySpace, that uses SQL Server, suggests caching is used on that system as well.

此

This article explains when caching should be used and this article is a counterpoint.

推荐答案

因此，如果SQL Server具有自己的缓存，那么外部Memcached(或类似的)服务器的好处是什么?

So if SQL Server has it's own cache, what is the benefit of an external Memcached (or similar) server?

是的，SQL Server有自己的缓存，但他仅缓存:
-查询计划
-数据库文件中的页面

Yes SQL Server has its own cache but he caches only:
- Query plans
- pages from the database files

但他不缓存:
-查询结果

but he does NOT cache:
- results from a query

例如您有一个复杂的查询，它对大量数据使用一些聚合 (想想:我们的客户数据库中有多少个不同的国家: 从客户分组(按国家/地区)中选择不同的国家/地区)

e.g. you have a complex query which uses some aggregation on a lot of data ( think of: how many different countries we have in our customer database : SELECT DISTINCT Country from Customers GROUP BY country )

SQL Server将扫描整个客户表，但是您的结果集只有几个条目长. 重新发出查询时，SQL Server将重新使用查询计划并重新扫描客户表， (如果幸运的话，页面仍在内存中)

SQL Server will scan th WHOLE customer table, but your resultset will only a few entries long. When you reissue your query, SQL Server will reuse the query plan and will rescan the customer table, ( and if you are lucky the pages are still in memory )

使用memcached时，您可以存储结果集的几行，并一遍又一遍地重用它们，而无需连接到数据库服务器.因此，它需要从数据库服务器中承担一些负载.
注意:如果您的数据在SQL Server上发生更改，请当心一些陈旧的数据！

When you use memcached you may store the few rows of your resultset and reuse them over and over again without connecting to the database server. So it takes some load from your database server.
NOTE: Beware of some stale data, if your data changes on the SQL server !!

这篇关于Memcached与SQL Server缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！